UEHrGs4L

1// connect to mysql
2public class SqlConnector {
3 
4    private static final String CONNECTION_URL = "jdbc:mysql://localhost:3306/";
5 
6    public SqlConnector() {
7    }
8 
9    public Connection getConnection(
10            final String databaseName,
11            final String username,
12            final String password
13    ) {
14        try {
15            return DriverManager.getConnection(CONNECTION_URL + databaseName + "?autoReconnect=true&useSSL=false", username, password);
16        } catch (SQLException ex) {
17            System.out.println("DATABASE_CONNECTION_ERR");
18            System.out.println("Message: " + ex.getLocalizedMessage());
19        }
20 
21        return null;
22    }
23 
24}
25 
26// repository class for managing database
27public class Repository {
28 
29    private final Connection conn;
30    private PreparedStatement preparedStatement;
31    private ResultSet resultSet;
32 
33    public Repository() {
34        conn = new SqlConnector().getConnection("student_record", "root", "root");
35        System.out.println("Connection Established!");
36        createAuthTableIfNotExists();
37    }
38 
39    public final void createAuthTableIfNotExists() {
40        final String mAuthTable = "CREATE TABLE IF NOT EXISTS auth (uuid BINARY(16), username VARCHAR(50) PRIMARY KEY, password VARCHAR(100));";
41        try {
42            Statement statement = conn.createStatement();
43            statement.executeUpdate(mAuthTable);
44        } catch (SQLException e) {
45            System.out.println("Error to create auth table. " + e.getLocalizedMessage());
46        }
47    }
48 
49    public boolean selectStudentRecord(String username, String password) {
50        final String select = "SELECT BIN_TO_UUID(uuid) uuid FROM auth WHERE username = '"
51                + username
52                + "' AND password = '"
53                + password
54                + "';";
55 
56        try {
57            preparedStatement = conn.prepareStatement(select);
58            resultSet = preparedStatement.executeQuery();
59 
60            int i = 0;
61            String uuid;
62            while (resultSet.next()) {
63                i++;
64                uuid = resultSet.getString("uuid");
65                System.out.println("UUID: " + uuid);
66            }
67            System.out.println("Result Set Count: " + i);
68 
69            preparedStatement.close();
70            resultSet.close();
71 
72            if (i > 0) {
73                return true;
74            } else {
75                return false;
76            }
77        } catch (SQLException ex) {
78            Logger.getLogger(Repository.class.getName()).log(Level.SEVERE, null, ex);
79            return false;
80        }
81    }
82 
83    public boolean insertStudentRecord(String username, String password) {
84        final String insert = "INSERT INTO auth (uuid,username,password) VALUES (UUID_TO_BIN(UUID()),'"
85                + username
86                + "','"
87                + password
88                + "');";
89 
90        try {
91            preparedStatement = conn.prepareStatement(insert);
92            preparedStatement.execute();
93 
94            preparedStatement.close();
95            resultSet.close();
96 
97            return true;
98        } catch (SQLException ex) {
99            System.out.println("Failed to create new account. Error :" + ex.getLocalizedMessage());
100            return false;
101        }
102    }
103 
104    public boolean deleteStudentRecord(String username) {
105        final String delete = "DELETE FROM  auth WHERE username = '"
106                + username
107                + "';";
108 
109        try {
110            preparedStatement = conn.prepareStatement(delete);
111            preparedStatement.execute();
112 
113            preparedStatement.close();
114            resultSet.close();
115 
116            return true;
117        } catch (SQLException ex) {
118            System.out.println("Failed to create new account. Error :" + ex.getLocalizedMessage());
119            return false;
120        }
121    }
122 
123}
124