Changeset 32745 for other-projects/the-macronizer/trunk/src/java/util/MySQLAccess.java

Timestamp:

2019-02-05T23:03:16+13:00 (5 years ago)

Author:

ak19

Message:

More Western Wilson stuff. 1. Major changes to fix handling of utf8 stuff in db so uniqueness actually works: so finding (selecting) exact matches works and insert unique violations don't happen from code. Inserting is now made lowercase since only macrons matter and case doesn't. 2. The SQL db's MarkedWords table needs to specify the uniqueness of its utf8 marked_word column differently for the utf8-ness to work.

File:

• other-projects/the-macronizer/trunk/src/java/util/MySQLAccess.java (modified) (8 diffs)

other-projects/the-macronizer/trunk/src/java/util/MySQLAccess.java

@@ -56 +56 @@
 
     private final String DB_NAME = "Macroniser";
-    // TODO: from Properties file
+    // obtained from Properties file:
     private String USERNAME; //= "root"; by default
     private String PASSWORD; //= "pinky";
@@ -91 +91 @@
 
             }
-            System.err.println("*** Found user: |" + USERNAME + "|");
-            System.err.println("*** Found pwd: " + PASSWORD);
+            //System.out.println("*** Found user: |" + USERNAME + "|");
+            //System.out.println("*** Found pwd: " + PASSWORD);
 
             // This will load the MySQL driver, each DB has its own driver Class.forName("com.mysql.jdbc.Driver");
@@ -99 +99 @@
             statement = connect.createStatement();
 
-            // TODO: can we use preparedStatement here instead of statement?
-            int result = statement.executeUpdate("set names utf8mb4");
-            System.err.println("Was set utf8 a success? " + result); // should return 0 for SQL stmts that return nothing
+            int result = statement.executeUpdate("set names utf8mb4"); // should return 0 for SQL stmts that return nothing
+
             success = true;
         } catch (SQLException e) {
@@ -192 +191 @@
             insertOccurrence(word_id, time_id, date_id);
 
-            System.err.println("The ID's are:(word, date, time) (" + word_id + "," + date_id + "," + time_id + ")");
+            if(MacroniserLogFileProcessor.debug) {
+                System.out.println("The IDs are:(word, date, time) (" + word_id + "," + date_id + "," + time_id + ")");
+            }
 
             connect.commit();
@@ -234 +235 @@
     //gets the specified marked word's id
     private int getMarkedWordID(String marked_word) throws SQLException {
+        // 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"
+        if(marked_word == null) {
+            marked_word = "NULL";
+        }
+
         // Warning: "select * from table WHERE str_field LIKE ?" does not work when we want exact matches (or matches featuring macrons)
         // Use "WHERE BINARY str_filed = ?" instead
@@ -253 +259 @@
     private int insertMarkedWord(String word) throws SQLException {
 
+        if(word == null) {
+            word = "NULL";
+        }
+
         String query = "INSERT INTO MarkedWords (marked_word) VALUES (?)";
         preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
         preparedStatement.setString(1, word);
+
 
         int word_id = -1;
@@ -293 +304 @@
         while (resultSet.next()) {
             date_id = resultSet.getInt("date_id");
-            System.err.println(date_id + " - " + date.toString());
+            if(MacroniserLogFileProcessor.debug) System.out.println(date_id + " - " + date.toString());
         }
         return date_id;
@@ -382 +393 @@
 
     public static void main(String[] args) {
-        System.err.println("Hello pinky!");
         MySQLAccess mysql = new MySQLAccess();