In my previous post I mentioned that I was going to be spending some time experimenting with MongoDB. So why would I be dealing with MySQL at this time? Good question! The reason for it is that I want to store, among different things, Java objects in MongoDB. I am interested in comparing how the same Java object may be stored and retrieved using a SQL and a NoSQL databases. I could have used a different SQL database (e.g., SQL Server), but decided on MySQL. It happens that I have a few databases installed on my Windows 10 computer. With that out of the way; let’s experiment with MySQL.
As a side note, if you have issues with MySQL not being FOSS (Free Open Source Software) you could instead select MariaDB. MariaDB is quite compatible to MySQL. Maria and My are both daughters of Michael Widenius, one of the founders of MariaDB. For additional information on MariaDB, visit Wikipedia .
To download and install MySQL follow the instructions on how to 
do so for MySQL Community Server. You can find the Windows installer and instructions in this link. In addition there are multiple YouTube videos showing how to go about this task. To find them I entered the following search in YouTube: “install mysql on windows 10”.
Please make sure you have installed MySQL before proceeding. In my Windows 1o machine I have installed the MySQL database engine and MySQL Workbench. I use the latter to verify the Java code is working as expected.
I will be writing Java code using the Eclipse IDE. It seems to me that Eclipse Oxygen 0.3 is a good choice to develop Java code.
In order to access the MySQL engine from a Java program you need to make use of the proper ‘connector’ which is provided by Oracle (owns the rights to MySQL). If you would be using a flavor of a C programming language (e.g., C#), or a different database (e.g., SQL Server) you would still have to look for the proper connector. A long time ago while working on a storage server we provided support for SQL Server and MySQL database engines. The core software for performance reasons was written using the C programming language. The software was accessing the MySQL database via the ODBC driver provided by Oracle.
I downloaded the database connector from the following URL:
https://dev.mysql.com/downloads/connector/j/
Downloaded the zip file to the following local folder in my computer:
c:\temp\mysql-connector-java-5.1.46.zip
From the zip file I just needed the following JAR:
mysql-connector-java-5.1.21-bin.jar
You need to place it in the proper Java folder. To do so you must check which version of the Java JDK you are using. There are many ways to determine which version you are using. You can try the following URL:
URL: javatester.org/version.html
It brings up among other text the following info:
If Java is working, you will see a pink rectangle above with one line of text that says something like: Java Version 1.8.0_25 from Oracle Corporation or Java Version 1.7.0_67 from Oracle Corporation or Java Version 1.6.0_45 from Sun Microsystems Inc. or Java Version 1.6.0_33 from Apple Inc. Version number translation: 1.6.0_34 is, in English, Java 6 Update 34 The initial "1" is ignored as is the third digit. Ask Oracle why.
You could also check the version that you have installed by looking for it in your file system using File Explorer (C:\Program Files\Java), checking the value of the JAVA_HOME environment variable using the Control Panel, or simply displaying the value associated with the JAVA_HOME system environment variable from a command prompt:
C:\Users\John>set JAVA_HOME JAVA_HOME=C:\ProgramData\Java\jdk1.8.0_112
In general, when you face a task, there tend to be several approaches to perform it. Try different ones and decide which one suits you best.
Expand the zip archive and extract the connector of inteters. In my case it was:
C:\Temp\mysql-connector-java-5.1.46\mysql-connector-java-5.1.46
You need to copy the connector to the lib\ext folder in the Java release folder. In my case that was:
C:\Program Files\Java\jre1.8.0_171\lib\ext
To verify that the connector is working I wrote a simple utility. The Java code follows:
import java.sql.*;
/*
*
*/
public class Solution {
/*
*
*/
public static void main(String[] args) {
// **** ****
Driver d = null;
// **** ****
try {
d = (Driver)Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (IllegalAccessException ex) {
System.err.println("<<< EXCEPTION - " + ex.toString() + " " + ex.getMessage());
System.exit(-1);
} catch (InstantiationException ex) {
System.err.println("<<< EXCEPTION - " + ex.toString() + " " + ex.getMessage());
System.exit(-1);
} catch (ExceptionInInitializerError ex) {
System.err.println("<<< EXCEPTION - " + ex.toString() + " " + ex.getMessage());
System.exit(-1);
} catch (SecurityException ex) {
System.err.println("<<< EXCEPTION - " + ex.toString() + " " + ex.getMessage());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< EXCEPTION - " + ex.toString() + " " + ex.getMessage());
System.exit(-1);
} finally {
System.out.println("<<< able to instantiate the MySQL driver for java !!!");
}
}
}
I listed a set of exceptions that could be thrown while testing different issues that I could come up with (e.g., missing driver name, missing Jar, etc). It always makes sense to start any software development process using a test driven approach. Start with a unit test and you will be able to produce better and higher quality software. Yes, I know, this is almost like writing a “Hello World” program but you might be surprised with the new things you could learn.
Following is console output from the IDE:
<<< args.length: 0 <<< database connection open <<< Bill Brown bill.brown@foo.com Engineering $50000 <<< Carl Adams carl.adams@foo.com HR $50000 <<< David Waters david.waters@foo.com Legal $90000 <<< David Williams david.williams@foo.com HR $120000 <<< James Bond james.bond1@mi6.gov Double O $200000 <<< James Bond II james.bond2@mi6.gov Double O $200000 <<< James Bond III james.bond3@mi6.gov Double O $200000 <<< John Davis john.davis@foo.com HR $45000 <<< John Doe john.doe@foo.com HR $55000 <<< Lisa Johnson lisa.johnson@foo.com Engineering $50000 <<< Mary Fowler mary.fowler@foo.com Engineering $65000 <<< Mary Public mary.public@foo.com Engineering $75000 <<< Paul Smith paul.smith@foo.com Legal $100000 <<< Susan Queue susan.queue@foo.com Legal $130000 <<< Susan Thomas susan.thomas@foo.com Legal $80000 <<< inserting records ... <<< records inserted >>> percentage (e.g., 0.2): .1 <<< line ==>.1<== <<< percent: 0.1 <<< update database records ... <<< records updated <<< undoing database update ... <<< database update undone <<< database connection closed
All the records from a database table are selected and displayed.
A new database table is created and some records are inserted.
A field in new database table is updated by the specified value.
The same field is restored by using a different query using a different approach.
Finally the database connection is closed.
The following code illustrates some basic database operations. Instead of segmenting operations into different classes or methods all was done in a single executable:
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
/*
*
*/
public class Solution {
/*
*
*/
public static void main(String[] args) {
// **** ****
final String MY_SQL_DRIVER = "com.mysql.jdbc.Driver";
// **** ****
BufferedReader input = null;
Connection conn = null;
Double percent = 0.0;
PreparedStatement prepared = null;
String userName = "John";
String password = "4Veng@nz@!";
String dbURL = "jdbc:mysql://localhost/demo";
String line = "";
// **** extract program arguments (if needed) ****
System.out.println("<<< args.length: " + args.length);
switch (args.length)
{
case 0: // use encoded arguments
break;
case 1: // user name only
userName = args[0];
break;
case 2: // user name and password
userName = args[0];
password = args[1];
break;
case 3: // user name, password and database URL
userName = args[0];
password = args[1];
dbURL = args[2];
break;
default:
System.err.println("<<< unexpected number of arguments args.length: " + args.length);
System.exit(-1);
break;
}
// **** open a database connection ****
try {
Class.forName(MY_SQL_DRIVER).newInstance();
conn = DriverManager.getConnection(dbURL, userName, password);
System.out.println("<<< database connection open");
} catch (SQLTimeoutException ex) {
System.err.println("<<< SQLTimeoutException " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (SQLException ex) {
System.err.println("<<< SQLException " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
// **** query the employee table ****
Statement statement = null;
ResultSet resultSet = null;
String query = "select first_name, last_name, email, department, salary "
+ "from employee order by first_name, last_name asc;--";
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(query);
System.out.println();
// **** loop once per record in the result set ****
while (resultSet.next()) {
// **** extract the fields from this record ****
String firstName = resultSet.getString("first_name");
String lastName = resultSet.getString("last_name");
String email = resultSet.getString("email");
String department = resultSet.getString("department");
int salary = (int)resultSet.getDouble("salary");
// **** display the record ****
System.out.println("<<< " + firstName + " " + lastName + " " + email + " " + department + " $" + salary);
}
System.out.println();
// **** close the result set and statement ****
resultSet.close();
statement.close();
} catch (SQLException ex) {
System.err.println("<<< SQLException query ==>" + query + "<=== " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception query ==>" + query + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
// **** drop table (in case it is already in the database) ****
try {
statement = conn.createStatement();
query = "drop table if exists projects;--";
statement.executeUpdate(query);
} catch (SQLException ex) {
System.err.println("<<< SQLException query ==>" + query + "<=== " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception query ==>" + query + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
// **** create a new database table ****
try {
query = "create table projects ( "
+ "id int NOT NULL AUTO_INCREMENT PRIMARY KEY,"
+ "name char(16) NOT NULL,"
+ "start_date date, "
+ "end_date date,"
+ "cost int);--" ;
statement.executeUpdate(query);
} catch (SQLException ex) {
System.err.println("<<< SQLException query ==>" + query + "<=== " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception query ==>" + query + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
// **** insert records into the new database table ****
System.out.println("<<< inserting records ...");
try {
query = "insert into projects"
+ "(name, start_date, end_date, cost) "
+ "values "
+ "('NoSQL store', '2018-05-01', '2018-07-31', 130000), "
+ "('SQL store', '2018-05-01', '2018-07-31', 100000), "
+ "('Microservices', '2018-07-01', '2018-08-30', 90000), "
+ "('Kubernetes', '2018-07-01', '2018-08-30', 80000);--";
statement.executeUpdate(query);
} catch (SQLException ex) {
System.err.println("<<< SQLException query ==>" + query + "<=== " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception query ==>" + query + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
System.out.println("<<< records inserted"); System.out.println(); // **** prompt user for percentage to apply **** input = new BufferedReader(new InputStreamReader(System.in)); System.out.print(">>> percentage (e.g., 0.2): ");
try {
line = input.readLine();
} catch (IOException ex) {
System.err.println("<<< IOException " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception line ==>" + line + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
System.out.println("<<< line ==>" + line + "<==");
// **** extract percent from string ****
try {
percent = Double.valueOf(line);
System.out.println("<<< percent: " + percent);
} catch (NumberFormatException ex) {
System.err.println("<<< NumberFormatException line ==>" + line + "<== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception line ==>" + line + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
System.out.println();
// **** update database records ****
System.out.println("<<< update database records ...");
try {
query = "update projects";
query += " set cost = cost + (cost * " + percent + ")";
query += " where cost != 0;--";
statement.executeUpdate(query);
} catch (SQLException ex) {
System.err.println("<<< SQLException query ==>" + query + "<=== " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception query ==>" + query + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
System.out.println("<<< records updated");
System.out.println();
// **** using prepared statement to undo last update ****
String preparedString = "update projects set cost = (cost / (1 + ?)) where cost != 0;--";
System.out.println("<<< undoing database update ...");
try {
prepared = conn.prepareStatement(preparedString);
prepared.setDouble(1, percent);
prepared.executeUpdate();
} catch (SQLException ex) {
System.err.println("<<< SQLException preparedString ==>" + preparedString + "<=== " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception preparedString ==>" + preparedString + "<=== " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
System.out.println("<<< database update undone");
System.out.println();
// **** close the database connection ****
try {
conn.close();
System.out.println("<<< database connection closed");
} catch (SQLException ex) {
System.err.println("<<< SQLException " + ex.getMessage() + " " + ex.getSQLState());
System.exit(-1);
} catch (Exception ex) {
System.err.println("<<< Exception " + ex.getMessage() + " " + ex.toString());
System.exit(-1);
} finally {}
}
}
Hope you find this post interesting. Will get to storing some objects from two or three classes in MySQL.
If you have comments or questions regarding this or any other entry in this post, please leave me a message. I will respond as soon as possible.
Enjoy;
John
john.canessa@gmail.com
@john_canessa