1 | package util;
|
---|
2 |
|
---|
3 | import java.io.BufferedReader;
|
---|
4 | import java.io.FileReader;
|
---|
5 | import java.sql.Connection;
|
---|
6 | import java.sql.Date;
|
---|
7 | import java.sql.DriverManager;
|
---|
8 | import java.sql.PreparedStatement;
|
---|
9 | import java.sql.ResultSet;
|
---|
10 | import java.sql.SQLException;
|
---|
11 | import java.sql.SQLIntegrityConstraintViolationException;
|
---|
12 | import java.sql.Statement;
|
---|
13 | import java.sql.Time;
|
---|
14 |
|
---|
15 | import java.time.LocalDate;
|
---|
16 | import java.time.LocalTime;
|
---|
17 |
|
---|
18 | import java.util.Properties;
|
---|
19 |
|
---|
20 | import java.util.List;
|
---|
21 |
|
---|
22 |
|
---|
23 | /*
|
---|
24 | * https://www.javatpoint.com/example-to-connect-to-the-mysql-database
|
---|
25 | * http://www.vogella.com/tutorials/MySQLJava/article.html
|
---|
26 | *
|
---|
27 | * 1. tikauka:[119]/Scratch/wjkw1>mkdir sql
|
---|
28 | * 2. tikauka:[120]/Scratch/wjkw1>mv MySQLAccess.java sql/.
|
---|
29 | * 3. tikauka:[122]/Scratch/wjkw1>javac sql/MySQLAccess.java *
|
---|
30 | * 4. tikauka:[144]/Scratch/wjkw1>java -cp "/PATH/TO/mysql-connector-java-8.0.14/mysql-connector-java-8.0.14.jar:/MYSQL.PROPS/PARENTFOLDER:." sql.MySQLAccess
|
---|
31 | * (If no classpath, then: tikauka:[123]/Scratch/wjkw1>java sql.MySQLAccess)
|
---|
32 | * Note that /MYSQL.PROPS/PARENTFOLDER is the parent folder of file mysql.properties, it should have been automatically
|
---|
33 | * created in macronizer's web/WEB-INF/CLASSES after the Macroniser's build.xml has run.
|
---|
34 | * In mysql.properties:
|
---|
35 | * -the db.password has to be changed from CHANGEME. (Can be empty string if your mysql account uses no pwd.)
|
---|
36 | * - can set db.username too if you don't want it to default to "root".
|
---|
37 | */
|
---|
38 | public class MySQLAccess {
|
---|
39 | //Tuple to hold data stored in database
|
---|
40 | public static class Tuple {
|
---|
41 | public final String word;
|
---|
42 | public final LocalDate date;
|
---|
43 | public final LocalTime time;
|
---|
44 |
|
---|
45 | public Tuple(String line, LocalDate d, LocalTime t) {
|
---|
46 | word = line;
|
---|
47 | date = d;
|
---|
48 | time = t;
|
---|
49 | }
|
---|
50 | }
|
---|
51 |
|
---|
52 | private Connection connect = null;
|
---|
53 | private Statement statement = null;
|
---|
54 | private PreparedStatement preparedStatement = null;
|
---|
55 | private ResultSet resultSet = null;
|
---|
56 |
|
---|
57 | private final String DB_NAME = "Macroniser";
|
---|
58 | // obtained from Properties file:
|
---|
59 | private String USERNAME; //= "root"; by default
|
---|
60 | private String PASSWORD; //= "pinky";
|
---|
61 |
|
---|
62 | private final String PROPS_FILENAME = "mysql.properties"; // set to "properties/mysql.properties" for IntelliJ
|
---|
63 |
|
---|
64 | //Makes a connection to the server
|
---|
65 | public boolean makeConnection() throws Exception {
|
---|
66 | boolean success = false;
|
---|
67 | try {
|
---|
68 | // Read in the username and password from our props file
|
---|
69 | Properties props = new Properties();
|
---|
70 | try(BufferedReader reader = new BufferedReader(new FileReader(PROPS_FILENAME));
|
---|
71 |
|
---|
72 | ) {
|
---|
73 |
|
---|
74 | props.load(reader);
|
---|
75 |
|
---|
76 | } catch(Exception e) {
|
---|
77 | e.printStackTrace();
|
---|
78 | }
|
---|
79 |
|
---|
80 |
|
---|
81 | USERNAME = props.getProperty("db.username", "");
|
---|
82 | if(USERNAME.equals("")) {
|
---|
83 | USERNAME = "root";
|
---|
84 | System.err.println("WARNING: No sensible value for db.username specified in " + PROPS_FILENAME + " defaulting to: " + USERNAME);
|
---|
85 | }
|
---|
86 | PASSWORD = props.getProperty("db.password");
|
---|
87 |
|
---|
88 | if(PASSWORD != null && PASSWORD.equals("CHANGEME")) {
|
---|
89 | success = false;
|
---|
90 | throw new Exception("************ FATAL ERROR: Change DB password in properties file " + PROPS_FILENAME);
|
---|
91 |
|
---|
92 | }
|
---|
93 | //System.out.println("*** Found user: |" + USERNAME + "|");
|
---|
94 | //System.out.println("*** Found pwd: " + PASSWORD);
|
---|
95 |
|
---|
96 | // This will load the MySQL driver, each DB has its own driver Class.forName("com.mysql.jdbc.Driver");
|
---|
97 | // Setup the connection with the DB
|
---|
98 | connect = DriverManager.getConnection("jdbc:mysql://localhost/"+DB_NAME, USERNAME, PASSWORD);
|
---|
99 | statement = connect.createStatement();
|
---|
100 |
|
---|
101 | int result = statement.executeUpdate("set names utf8mb4"); // should return 0 for SQL stmts that return nothing
|
---|
102 | System.out.println("Connected to MySQL DB " + DB_NAME);
|
---|
103 | success = true;
|
---|
104 | } catch (SQLException e) {
|
---|
105 | //connect may be non-null if we've already made the connection but "set names utf8mb4" failed
|
---|
106 | //closeConnection(); // overkill, due to how we call makeConnection + closeConnection ALWAYS TOGETHER
|
---|
107 | success = false;
|
---|
108 | throw e;
|
---|
109 | }
|
---|
110 | return success;
|
---|
111 | }
|
---|
112 |
|
---|
113 |
|
---|
114 |
|
---|
115 | // You need to close the resultSet
|
---|
116 | public void closeConnection() {
|
---|
117 |
|
---|
118 | System.out.println("Closing connection...");
|
---|
119 |
|
---|
120 | closeResource(resultSet);
|
---|
121 | closeResource(statement);
|
---|
122 | closeResource(preparedStatement);
|
---|
123 |
|
---|
124 | // Finally close the actual connection
|
---|
125 | closeResource(connect);
|
---|
126 |
|
---|
127 | System.out.println("Connection successfully closed");
|
---|
128 | }
|
---|
129 |
|
---|
130 | /**
|
---|
131 | * mysql> set names utf8mb4;
|
---|
132 | * <p>
|
---|
133 | * mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
|
---|
134 | * +--------------------------+--------------------+
|
---|
135 | * | Variable_name | Value |
|
---|
136 | * +--------------------------+--------------------+
|
---|
137 | * | character_set_client | utf8mb4 |
|
---|
138 | * | character_set_connection | utf8mb4 |
|
---|
139 | * | character_set_database | utf8mb4 |
|
---|
140 | * | character_set_filesystem | binary |
|
---|
141 | * | character_set_results | utf8mb4 |
|
---|
142 | * | character_set_server | utf8mb4 |
|
---|
143 | * | character_set_system | utf8 |
|
---|
144 | * | collation_connection | utf8mb4_general_ci |
|
---|
145 | * | collation_database | utf8mb4_general_ci |
|
---|
146 | * | collation_server | utf8mb4_general_ci |
|
---|
147 | * +--------------------------+--------------------+
|
---|
148 | * <p>
|
---|
149 | * The values of the final 2 lines are different from https://medium.com/@manish_demblani/breaking-out-from-the-mysql-character-set-hell-24c6a306e1e5
|
---|
150 | * (in case this matters) but both utf8mb4_general_ci and utf8mb4_unicode_ci perform case-INsensitive comparisons
|
---|
151 | * see https://stackoverflow.com/questions/2344118/utf-8-general-bin-unicode
|
---|
152 | * which is fine by us, because we only care about the macrons.
|
---|
153 | */
|
---|
154 | /*
|
---|
155 | Select mw.marked_word, ed.entry_date, et.entry_time
|
---|
156 | FROM Occurrence o, MarkedWords mw, EntryDates ed, EntryTimes et
|
---|
157 | WHERE o.word_id = mw.word_id
|
---|
158 | AND o.date_id = ed.date_id
|
---|
159 | AND o.time_id = et.time_id;
|
---|
160 |
|
---|
161 | SELECT word_id FROM MarkedWords WHERE marked_word like ?
|
---|
162 |
|
---|
163 | */
|
---|
164 | public boolean addNewEntry(Tuple t) throws SQLException {
|
---|
165 | int word_id = -1, date_id = -1, time_id = -1;
|
---|
166 | boolean success = false;
|
---|
167 | try {
|
---|
168 |
|
---|
169 | // Commits happen on a per-Tuple bases: either all update together (commit)
|
---|
170 | // or none of them happen (rollback)
|
---|
171 | //sets a transaction so if an exception happens the partially data entered is not saved
|
---|
172 | //must remember to commit prepared statement after executeUpdate to save data
|
---|
173 | connect.setAutoCommit(false);
|
---|
174 |
|
---|
175 | //get ID, if not available then insert word
|
---|
176 | word_id = getMarkedWordID(t.word);
|
---|
177 | if (word_id == -1) {
|
---|
178 | word_id = insertMarkedWord(t.word);
|
---|
179 | }
|
---|
180 | //get ID, if not available then insert date
|
---|
181 | date_id = getEntryDateID(t.date);
|
---|
182 | if (date_id == -1) {
|
---|
183 | date_id = insertEntryDate(t.date);
|
---|
184 | }
|
---|
185 | //get ID, if not available then insert time
|
---|
186 | time_id = getEntryTimeID(t.time);
|
---|
187 | if (time_id == -1) {
|
---|
188 | time_id = insertEntryTime(t.time);
|
---|
189 | }
|
---|
190 |
|
---|
191 | insertOccurrence(word_id, time_id, date_id);
|
---|
192 |
|
---|
193 | if(MacroniserLogFileProcessor.debug) {
|
---|
194 | System.out.println("The IDs are:(word, date, time) (" + word_id + "," + date_id + "," + time_id + ")");
|
---|
195 | }
|
---|
196 |
|
---|
197 | connect.commit();
|
---|
198 | success = true;
|
---|
199 |
|
---|
200 | } catch (Exception e) {
|
---|
201 | e.printStackTrace();
|
---|
202 | connect.rollback();
|
---|
203 | throw e;
|
---|
204 |
|
---|
205 | } finally {
|
---|
206 | connect.setAutoCommit(true);
|
---|
207 | }
|
---|
208 | return success;
|
---|
209 | }
|
---|
210 |
|
---|
211 | //Occurrence into
|
---|
212 | private int insertOccurrence(int wordID, int timeID, int dateID) throws SQLException {
|
---|
213 | String query = "INSERT INTO Occurrences (word_id, time_id, date_id) VALUES (?,?,?)";
|
---|
214 | preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
|
---|
215 | preparedStatement.setInt(1, wordID);
|
---|
216 | preparedStatement.setInt(2, timeID);
|
---|
217 | preparedStatement.setInt(3, dateID);
|
---|
218 |
|
---|
219 | int occurrence_id = -1;
|
---|
220 | try {
|
---|
221 | int result = preparedStatement.executeUpdate();
|
---|
222 | resultSet = preparedStatement.getGeneratedKeys();
|
---|
223 |
|
---|
224 | if (resultSet.next()) {
|
---|
225 | occurrence_id = resultSet.getInt(1);
|
---|
226 | }
|
---|
227 |
|
---|
228 | } catch (SQLException e) {
|
---|
229 | throw e; // any other SQLException is a bad case, so we'll handle it in the caller
|
---|
230 | } finally {
|
---|
231 | return occurrence_id;
|
---|
232 | }
|
---|
233 | }
|
---|
234 |
|
---|
235 | //gets the specified marked word's id
|
---|
236 | private int getMarkedWordID(String marked_word) throws SQLException {
|
---|
237 | // in cases where the user didn't enter any input str, marked_word will be null. Still of interest, store to db as string "NULL"
|
---|
238 | if(marked_word == null) {
|
---|
239 | marked_word = "NULL";
|
---|
240 | }
|
---|
241 |
|
---|
242 | // Warning: "select * from table WHERE str_field LIKE ?" does not work when we want exact matches (or matches featuring macrons)
|
---|
243 | // Use "WHERE BINARY str_filed = ?" instead
|
---|
244 | // See https://stackoverflow.com/questions/7604082/mysql-exact-match
|
---|
245 | String query = "SELECT word_id FROM MarkedWords WHERE BINARY marked_word = ?";
|
---|
246 | preparedStatement = connect.prepareStatement(query);
|
---|
247 | preparedStatement.setString(1, marked_word);
|
---|
248 |
|
---|
249 | resultSet = preparedStatement.executeQuery();
|
---|
250 |
|
---|
251 | int word_id = -1;
|
---|
252 | while (resultSet.next()) {
|
---|
253 | word_id = resultSet.getInt("word_id");
|
---|
254 | }
|
---|
255 | return word_id;
|
---|
256 | }
|
---|
257 |
|
---|
258 | //inserts the marked word into the database, uses transactions to rollback if necessary
|
---|
259 | private int insertMarkedWord(String word) throws SQLException {
|
---|
260 |
|
---|
261 | if(word == null) {
|
---|
262 | word = "NULL";
|
---|
263 | }
|
---|
264 |
|
---|
265 | String query = "INSERT INTO MarkedWords (marked_word) VALUES (?)";
|
---|
266 | preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
|
---|
267 | preparedStatement.setString(1, word);
|
---|
268 |
|
---|
269 |
|
---|
270 | int word_id = -1;
|
---|
271 | try {
|
---|
272 | int result = preparedStatement.executeUpdate();
|
---|
273 |
|
---|
274 | resultSet = preparedStatement.getGeneratedKeys();
|
---|
275 |
|
---|
276 | if (resultSet.next()) {
|
---|
277 | word_id = resultSet.getInt(1);
|
---|
278 | }
|
---|
279 | //System.err.println("Word ID: "+word_id);
|
---|
280 |
|
---|
281 | } catch (SQLIntegrityConstraintViolationException e) {
|
---|
282 | //This exception happens when an insert statement breaks UNIQUE constraint
|
---|
283 | //Should not be called, but added for robustness
|
---|
284 | // this exception is fine, it means the word was already in the MarkedWords table
|
---|
285 | System.err.println("The word " + word + " was already in MarkedWords table");
|
---|
286 | // this error is not fatal
|
---|
287 | } catch (SQLException e) {
|
---|
288 | throw e; // any other SQLException is a bad case, so we'll handle it in the caller
|
---|
289 | } finally {
|
---|
290 | return word_id;
|
---|
291 | }
|
---|
292 | }
|
---|
293 |
|
---|
294 | //gets the specified date's id
|
---|
295 | private int getEntryDateID(LocalDate date) throws SQLException {
|
---|
296 | String query = "SELECT date_id FROM EntryDates WHERE entry_date = ?";
|
---|
297 | preparedStatement = connect.prepareStatement(query);
|
---|
298 | Date sqlDate = Date.valueOf(date);
|
---|
299 | preparedStatement.setDate(1, sqlDate);
|
---|
300 |
|
---|
301 | resultSet = preparedStatement.executeQuery();
|
---|
302 |
|
---|
303 | int date_id = -1;
|
---|
304 | while (resultSet.next()) {
|
---|
305 | date_id = resultSet.getInt("date_id");
|
---|
306 | if(MacroniserLogFileProcessor.debug) System.out.println(date_id + " - " + date.toString());
|
---|
307 | }
|
---|
308 | return date_id;
|
---|
309 | }
|
---|
310 |
|
---|
311 | //inserts the entry time date into the database
|
---|
312 | private int insertEntryDate(LocalDate date) throws SQLException {
|
---|
313 |
|
---|
314 | String query = "INSERT INTO EntryDates (entry_date) VALUES (?)";
|
---|
315 | preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
|
---|
316 | Date sqlDate = Date.valueOf(date);
|
---|
317 | preparedStatement.setDate(1, sqlDate);
|
---|
318 |
|
---|
319 | int date_id = -1;
|
---|
320 | try {
|
---|
321 | int result = preparedStatement.executeUpdate();
|
---|
322 | resultSet = preparedStatement.getGeneratedKeys();
|
---|
323 |
|
---|
324 | if (resultSet.next()) {
|
---|
325 | date_id = resultSet.getInt(1);
|
---|
326 | }
|
---|
327 | //System.err.println("Date ID: "+ date_id);
|
---|
328 |
|
---|
329 | } catch (SQLIntegrityConstraintViolationException e) {
|
---|
330 | // this exception is fine, it means the time was already in the EntryTimes table
|
---|
331 | System.err.println("The date " + date + " was already in EntryDates table");
|
---|
332 | // this error is not fatal
|
---|
333 | } catch (SQLException e) {
|
---|
334 | throw e; // any other SQLException is a bad case, so we'll handle it in the caller
|
---|
335 | } finally {
|
---|
336 | return date_id;
|
---|
337 | }
|
---|
338 | }
|
---|
339 |
|
---|
340 | //gets the specified time's id
|
---|
341 | private int getEntryTimeID(LocalTime time) throws SQLException {
|
---|
342 | String query = "SELECT time_id FROM EntryTimes WHERE entry_time = ?";
|
---|
343 | preparedStatement = connect.prepareStatement(query);
|
---|
344 | Time sqlTime = Time.valueOf(time);
|
---|
345 | preparedStatement.setTime(1, sqlTime);
|
---|
346 |
|
---|
347 | resultSet = preparedStatement.executeQuery();
|
---|
348 |
|
---|
349 | int time_id = -1;
|
---|
350 | while (resultSet.next()) {
|
---|
351 | time_id = resultSet.getInt("time_id");
|
---|
352 | }
|
---|
353 | return time_id;
|
---|
354 | }
|
---|
355 |
|
---|
356 | //inserts the entry time into the database
|
---|
357 | private int insertEntryTime(LocalTime time) throws SQLException {
|
---|
358 |
|
---|
359 | String query = "INSERT INTO EntryTimes (entry_time) VALUES (?)";
|
---|
360 | preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
|
---|
361 | Time sqlTime = Time.valueOf(time);
|
---|
362 | preparedStatement.setTime(1, sqlTime);
|
---|
363 |
|
---|
364 | int time_id = -1;
|
---|
365 | try {
|
---|
366 | int result = preparedStatement.executeUpdate();
|
---|
367 | resultSet = preparedStatement.getGeneratedKeys();
|
---|
368 |
|
---|
369 | if (resultSet.next()) {
|
---|
370 | time_id = resultSet.getInt(1);
|
---|
371 | }
|
---|
372 | //System.err.println("Time ID: "+ time_id);
|
---|
373 |
|
---|
374 | } catch (SQLIntegrityConstraintViolationException e) {
|
---|
375 | // this exception is fine, it means the time was already in the EntryTimes table
|
---|
376 | System.err.println("The time " + time + " was already in EntryTimes table");
|
---|
377 | // this error is not fatal
|
---|
378 | } catch (SQLException e) {
|
---|
379 | throw e; // any other SQLException is a bad case, so we'll handle it in the caller
|
---|
380 | } finally {
|
---|
381 | return time_id;
|
---|
382 | }
|
---|
383 | }
|
---|
384 |
|
---|
385 |
|
---|
386 | public void closeResource(AutoCloseable resource) {
|
---|
387 | try {
|
---|
388 | if(resource != null) resource.close();
|
---|
389 | } catch(Exception e) {
|
---|
390 | e.printStackTrace();
|
---|
391 | }
|
---|
392 | }
|
---|
393 |
|
---|
394 | public static void main(String[] args) {
|
---|
395 | MySQLAccess mysql = new MySQLAccess();
|
---|
396 |
|
---|
397 | try {
|
---|
398 |
|
---|
399 | mysql.makeConnection();
|
---|
400 | String s = "Hello World";
|
---|
401 | LocalTime time = LocalTime.of(10, 10, 15);
|
---|
402 | LocalDate date = LocalDate.of(2018, 04, 21);
|
---|
403 | Tuple t = new Tuple(s, date, time);
|
---|
404 |
|
---|
405 | mysql.addNewEntry(t);
|
---|
406 |
|
---|
407 | } catch (Exception e) {
|
---|
408 | e.printStackTrace();
|
---|
409 | } finally {
|
---|
410 | mysql.closeConnection();
|
---|
411 | }
|
---|
412 | }
|
---|
413 |
|
---|
414 | }
|
---|