source: other-projects/the-macronizer/trunk/src/java/util/MySQLAccess.java@ 32742

Last change on this file since 32742 was 32742, checked in by ak19, 5 years ago

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