import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MariaDBTestUUID
{
    private Connection connect;
    private Statement statement;
    private ResultSet resultSet;

    public void updateUUID() throws Exception
    {
        try
        {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("org.mariadb.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager
                    // AWS Aurora (MySQL server 5.6.10) - found with SQL "show variables like '%version%';"
//                    .getConnection("jdbc:mariadb://<hostname>:3306/<DB name>?user=<user>&password=<password>");
//                     // local MySQL server (5.7.22)
                    .getConnection("jdbc:mariadb://localhost:3306/licenceserver4?user=root&password=<password>");
//                     // docker container MySQL server (5.7.30)
//                    .getConnection("jdbc:mariadb://localhost:3307/testDB?user=root&password=<password>");

            // PreparedStatements can use variables and are more efficient
            PreparedStatement preparedStatement =
                connect.prepareStatement("UPDATE testUUID SET new_id = uuid(), new_id2 = REPLACE(uuid(), '-', ''), new_id3 = UNHEX(REPLACE(uuid(), '-', ''))");
            preparedStatement.executeUpdate();

            queryStrings("select ID, new_ID, new_ID2, hex(new_ID3) as new_ID3 from testUUID");
            queryStrings("select count(distinct(new_ID)) as distinct_rows from testUUID");
            queryStrings("select count(distinct(new_ID2)) as distinct_rows2 from testUUID");
            queryStrings("select count(distinct(new_ID3)) as distinct_rows3 from testUUID");

            System.out.println("Debugging info for Diego Dupin:");
            queryStrings("SHOW VARIABLES LIKE '%COLLATION%'");
            queryStrings("SHOW VARIABLES LIKE '%CHARACTER%'");
            queryStrings("SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,\n" +
                    "                information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA\n" +
                    "            WHERE CCSA.collation_name = T.table_collation\n" +
                    "            AND T.table_name = \"testuuid\"");
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            close();
        }
    }

    private void queryStrings(String sql) throws SQLException
    {
        System.out.println("Running \"" + sql + "\"... ");
        statement = connect.createStatement();
        // Result set get the result of the SQL query
        resultSet = statement
            .executeQuery(sql);
        writeResultSet(resultSet);
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException
    {
        // ResultSet is initially before the first data set
        while (resultSet.next())
        {
            // we can only do this because we know they are all strings
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
            {
                String value = resultSet.getString(i);
                System.out.print(resultSet.getMetaData().getColumnName(i) + ": " + value + ", ");
            }
            System.out.println();
        }
    }

    // You need to close the resultSet
    private void close()
    {
        try
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connect != null)
            {
                connect.close();
            }
        }
        catch (Exception e)
        {
            System.out.println("Error closing:");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception
    {
        new MariaDBTestUUID().updateUUID();
    }
}