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

package util;

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.Statement;
import java.sql.Time;

import java.time.LocalDate;
import java.time.LocalTime;

import java.util.Properties;

import java.util.List;


/*
 * https://www.javatpoint.com/example-to-connect-to-the-mysql-database
 * http://www.vogella.com/tutorials/MySQLJava/article.html
 *
 * 1. tikauka:[119]/Scratch/wjkw1>mkdir sql
 * 2. tikauka:[120]/Scratch/wjkw1>mv MySQLAccess.java sql/.
 * 3. tikauka:[122]/Scratch/wjkw1>javac sql/MySQLAccess.java *
 * 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
 *    (If no classpath, then: tikauka:[123]/Scratch/wjkw1>java sql.MySQLAccess)
 *    Note that /MYSQL.PROPS/PARENTFOLDER is the parent folder of file mysql.properties, it should have been automatically
 *    created in macronizer's web/WEB-INF/CLASSES after the Macroniser's build.xml has run.
 *    In mysql.properties:
 *    -the db.password has to be changed from CHANGEME. (Can be empty string if your mysql account uses no pwd.)
 *    - can set db.username too if you don't want it to default to "root".
 */
public class MySQLAccess {
    //Tuple to hold data stored in database
    public static class Tuple {
        public final String word;
        public final LocalDate date;
        public final LocalTime time;

        public Tuple(String line, LocalDate d, LocalTime t) {
            word = line;
            date = d;
            time = t;
        }
    }

    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    private final String DB_NAME = "Macroniser";
    // obtained from Properties file:
    private String USERNAME; //= "root"; by default
    private String PASSWORD; //= "pinky";

    private final String PROPS_FILENAME = "properties/mysql.properties";

    //Makes a connection to the server
    public boolean makeConnection() throws Exception {
        boolean success = false;
        try {
            // Read in the username and password from our props file
            Properties props = new Properties();
            try(BufferedReader reader = new BufferedReader(new FileReader(PROPS_FILENAME));

            ) {

                props.load(reader);

            } catch(Exception e) {
                e.printStackTrace();
            }


            USERNAME = props.getProperty("db.username", "");
            if(USERNAME.equals("")) {
                USERNAME = "root";
                System.err.println("WARNING: No sensible value for db.username specified in " + PROPS_FILENAME + " defaulting to: " + USERNAME);
            }
            PASSWORD = props.getProperty("db.password");

            if(PASSWORD != null && PASSWORD.equals("CHANGEME")) {
                success = false;
                throw new Exception("************ FATAL ERROR: Change DB password in properties file " + PROPS_FILENAME);

            }
            //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");
            // Setup the connection with the DB
            connect = DriverManager.getConnection("jdbc:mysql://localhost/"+DB_NAME, USERNAME, PASSWORD);
            statement = connect.createStatement();

            int result = statement.executeUpdate("set names utf8mb4"); // should return 0 for SQL stmts that return nothing

            success = true;
        } catch (SQLException e) {
            //connect may be non-null if we've already made the connection but "set names utf8mb4" failed
            //closeConnection(); // overkill, due to how we call makeConnection + closeConnection ALWAYS TOGETHER
            success = false;
            throw e;
        }
        return success;
    }



    // You need to close the resultSet
    public void closeConnection() {

        System.out.println("Closing connection...");

        closeResource(resultSet);
        closeResource(statement);
        closeResource(preparedStatement);

        // Finally close the actual connection
        closeResource(connect);

        System.out.println("Connection successfully closed");
    }

    /**
     * mysql> set names utf8mb4;
     * <p>
     * mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
     * +--------------------------+--------------------+
     * | Variable_name            | Value              |
     * +--------------------------+--------------------+
     * | character_set_client     | utf8mb4            |
     * | character_set_connection | utf8mb4            |
     * | character_set_database   | utf8mb4            |
     * | character_set_filesystem | binary             |
     * | character_set_results    | utf8mb4            |
     * | character_set_server     | utf8mb4            |
     * | character_set_system     | utf8               |
     * | collation_connection     | utf8mb4_general_ci |
     * | collation_database       | utf8mb4_general_ci |
     * | collation_server         | utf8mb4_general_ci |
     * +--------------------------+--------------------+
     * <p>
     * The values of the final 2 lines are different from https://medium.com/@manish_demblani/breaking-out-from-the-mysql-character-set-hell-24c6a306e1e5
     * (in case this matters) but both utf8mb4_general_ci and utf8mb4_unicode_ci perform case-INsensitive comparisons
     * see https://stackoverflow.com/questions/2344118/utf-8-general-bin-unicode
     * which is fine by us, because we only care about the macrons.
     */
    /*
      Select mw.marked_word, ed.entry_date, et.entry_time
      FROM Occurrence o, MarkedWords mw, EntryDates ed, EntryTimes et
      WHERE o.word_id = mw.word_id
      AND o.date_id = ed.date_id
      AND o.time_id = et.time_id;

      SELECT word_id FROM MarkedWords WHERE marked_word like ?

    */
    public boolean addNewEntry(Tuple t) throws SQLException {
        int word_id = -1, date_id = -1, time_id = -1;
        boolean success = false;
        try {

            // Commits happen on a per-Tuple bases: either all update together (commit)
            // or none of them happen (rollback)
            //sets a transaction so if an exception happens the partially data entered is not saved
            //must remember to commit prepared statement after executeUpdate to save data
            connect.setAutoCommit(false);

            //get ID, if not available then insert word
            word_id = getMarkedWordID(t.word);
            if (word_id == -1) {
                word_id = insertMarkedWord(t.word);
            }
            //get ID, if not available then insert date
            date_id = getEntryDateID(t.date);
            if (date_id == -1) {
                date_id = insertEntryDate(t.date);
            }
            //get ID, if not available then insert time
            time_id = getEntryTimeID(t.time);
            if (time_id == -1) {
                time_id = insertEntryTime(t.time);
            }

            insertOccurrence(word_id, time_id, date_id);

            if(MacroniserLogFileProcessor.debug) {
                System.out.println("The IDs are:(word, date, time) (" + word_id + "," + date_id + "," + time_id + ")");
            }

            connect.commit();
            success = true;

        } catch (Exception e) {
            e.printStackTrace();
            connect.rollback();
            throw e;

        } finally {
            connect.setAutoCommit(true);
        }
        return success;
    }

    //Occurrence into
    private int insertOccurrence(int wordID, int timeID, int dateID) throws SQLException {
        String query = "INSERT INTO Occurrences (word_id, time_id, date_id) VALUES (?,?,?)";
        preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setInt(1, wordID);
        preparedStatement.setInt(2, timeID);
        preparedStatement.setInt(3, dateID);

        int occurrence_id = -1;
        try {
            int result = preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();

            if (resultSet.next()) {
                occurrence_id = resultSet.getInt(1);
            }

        } catch (SQLException e) {
            throw e; // any other SQLException is a bad case, so we'll handle it in the caller
        } finally {
            return occurrence_id;
        }
    }

    //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
        // See https://stackoverflow.com/questions/7604082/mysql-exact-match
        String query = "SELECT word_id FROM MarkedWords WHERE BINARY marked_word = ?";
        preparedStatement = connect.prepareStatement(query);
        preparedStatement.setString(1, marked_word);

        resultSet = preparedStatement.executeQuery();

        int word_id = -1;
        while (resultSet.next()) {
            word_id = resultSet.getInt("word_id");
        }
        return word_id;
    }

    //inserts the marked word into the database, uses transactions to rollback if necessary
    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;
        try {
            int result = preparedStatement.executeUpdate();

            resultSet = preparedStatement.getGeneratedKeys();

            if (resultSet.next()) {
                word_id = resultSet.getInt(1);
            }
            //System.err.println("Word ID: "+word_id);

        } catch (SQLIntegrityConstraintViolationException e) {
            //This exception happens when an insert statement breaks UNIQUE constraint
            //Should not be called, but added for robustness
            // this exception is fine, it means the word was already in the MarkedWords table
            System.err.println("The word " + word + " was already in MarkedWords table");
            // this error is not fatal
        } catch (SQLException e) {
            throw e; // any other SQLException is a bad case, so we'll handle it in the caller
        } finally {
            return word_id;
        }
    }

    //gets the specified date's id
    private int getEntryDateID(LocalDate date) throws SQLException {
        String query = "SELECT date_id FROM EntryDates WHERE entry_date = ?";
        preparedStatement = connect.prepareStatement(query);
        Date sqlDate = Date.valueOf(date);
        preparedStatement.setDate(1, sqlDate);

        resultSet = preparedStatement.executeQuery();

        int date_id = -1;
        while (resultSet.next()) {
            date_id = resultSet.getInt("date_id");
            if(MacroniserLogFileProcessor.debug) System.out.println(date_id + " - " + date.toString());
        }
        return date_id;
    }

    //inserts the entry time date into the database
    private int insertEntryDate(LocalDate date) throws SQLException {

        String query = "INSERT INTO EntryDates (entry_date) VALUES (?)";
        preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        Date sqlDate = Date.valueOf(date);
        preparedStatement.setDate(1, sqlDate);

        int date_id = -1;
        try {
            int result = preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();

            if (resultSet.next()) {
                date_id = resultSet.getInt(1);
            }
            //System.err.println("Date ID: "+ date_id);

        } catch (SQLIntegrityConstraintViolationException e) {
            // this exception is fine, it means the time was already in the EntryTimes table
            System.err.println("The date " + date + " was already in EntryDates table");
            // this error is not fatal
        } catch (SQLException e) {
            throw e; // any other SQLException is a bad case, so we'll handle it in the caller
        } finally {
            return date_id;
        }
    }

    //gets the specified time's id
    private int getEntryTimeID(LocalTime time) throws SQLException {
        String query = "SELECT time_id FROM EntryTimes WHERE entry_time = ?";
        preparedStatement = connect.prepareStatement(query);
        Time sqlTime = Time.valueOf(time);
        preparedStatement.setTime(1, sqlTime);

        resultSet = preparedStatement.executeQuery();

        int time_id = -1;
        while (resultSet.next()) {
            time_id = resultSet.getInt("time_id");
        }
        return time_id;
    }

    //inserts the entry time into the database
    private int insertEntryTime(LocalTime time) throws SQLException {

        String query = "INSERT INTO EntryTimes (entry_time) VALUES (?)";
        preparedStatement = connect.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        Time sqlTime = Time.valueOf(time);
        preparedStatement.setTime(1, sqlTime);

        int time_id = -1;
        try {
            int result = preparedStatement.executeUpdate();
            resultSet = preparedStatement.getGeneratedKeys();

            if (resultSet.next()) {
                time_id = resultSet.getInt(1);
            }
            //System.err.println("Time ID: "+ time_id);

        } catch (SQLIntegrityConstraintViolationException e) {
            // this exception is fine, it means the time was already in the EntryTimes table
            System.err.println("The time " + time + " was already in EntryTimes table");
            // this error is not fatal
        } catch (SQLException e) {
            throw e; // any other SQLException is a bad case, so we'll handle it in the caller
        } finally {
            return time_id;
        }
    }


    public void closeResource(AutoCloseable resource) {
        try {
            if(resource != null) resource.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        MySQLAccess mysql = new MySQLAccess();

        try {

            mysql.makeConnection();
            String s = "Hello World";
            LocalTime time = LocalTime.of(10, 10, 15);
            LocalDate date = LocalDate.of(2018, 04, 21);
            Tuple t = new Tuple(s, date, time);

            mysql.addNewEntry(t);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            mysql.closeConnection();
        }
    }

}