MySQL Community Edition is an open-source database management system that runs on multiple platforms (operating systems such as Windows, Linux, and macOS). Java provides a specific API for communicating with different database management systems: The Java Database Connectivity API. A JDBC driver enables a Java application to connect to a database, typically a relational database management system (like Oracle, MySQL, PostreSQL, Informix, SQL Server, etc.).
A relational database is a logical representation of a collection of data. This logical representation is a Table. A Table is composed of rows and each row is composed of columns. This allows the data to be accessed without considering its physical structure.
In the following step by step tutorial, you’ll learn how to: Install and setup MySQL on your computer, install the JDBC Driver to connect to MySQL (Connector/J) and, code a simple program that will communicate and query a table from your local MySQL server (localhost).
DISCLAIMER: The current MySQL and Connector/J version as this post is being written is 8.0.21. You may want to download the latest/current version by the time you’re reading this tutorial. Also, I’m assuming you already have a recent version of the JDK installed on your machine.
Installing MySQL on your computer
If you have a Windows Computer (7/8/10)
Navigate to https://dev.mysql.com/downloads/installer/. Your Windows operating system will be identified and you will see something like this
Download the MSI installer: mysql-installer-community-8.0.21.0.msi. You will then be prompted to either log in or register a new account. If you already have an account, just authenticate and the download will begin; else, create a new account.
After that, execute the MSI installer and follow the instructions displayed. When you’re asked to select the setup type, you can choose Developer Default, which is enough for developing software applications. During the Configuration step you will be required to create a MySQL root username and password. This user will have all the database administration privileges enabled so don’t forget what you’re typing in.
Default’s installation path is C:\Program Files\MySQL\MySQL Server 8.X. If you want to be able to run the mysql commands on the Windows CLI (cmd.exe) you should add the bin folder to your PATH variable. To do this, copy the full path to your bin folder, most likely C:\Program Files\MySQL\MySQL Server 8.0\bin. Then, open up the advanced system settings on Windows (you can press the Widows key and search for Advanced System Settings). Under the Advanced tab you’ll find at the bottom the Environment Variables… button; click on it, and locate the Path variable under the first list; click on Edit and then New. Paste the copied path, open a new command line (cmd.exe). Try this out by typing in mysql --version. If you see the actual information displayed, you’re done.
If you have a macOS Computer
Search on Google for MySQL community server download and go for the first result. The page you should be headed to is https://dev.mysql.com/downloads/mysql/. Select macOS as your operating system and choose the DMG archive.
Enter your credentials or register with a new account for the DMG archive to download. Once downloaded, double click on the file and you will see the archive contains a .pkg file. Double click on it to start up the installation wizard:
Once the installation is finished you will be able to locate the binaries folder (bin) under /usr/local/mysql/bin. Open up a Terminal window and type cd /user/local/mysql/bin. Then, enter ./mysql --version. If the version info is displayed, then have successfully installed MySQL onto your computer.
Installing Connector/J
This is the required driver for connecting the MySQL system to your Java application. To download the driver, navigate to https://www.mysql.com/products/connector/. Locate Connector/J from the list and click on Download. Then, choose Platform Independent as the Operating system from the dropdown menu and download the corresponding ZIP file mysql-connector-java-8.0.21.zip.
Locate the extensions folder inside your Java installation path. Grab the jar file and drop it into said folder. Typical installation paths to ext folder for Windows is C:\Program Files\Java\jdk1.8.0_161\jre\lib\ext; whereas for macOS should be /Library/Java/JavaVirtualMachines/jdk1.8.0_231.jdk/Contents/Home/jre/lib/ext.
At this point you should be ready to write a simple program to test communication.
Connecting and communicating with MySQL
Now, let’s start coding.
import java.sql.*;
public class ConnectionTest {
public static void main (String... args){
String user = "gone";
String password = "fishing";
String url = "jdbc:mysql://localhost:3305/example";
try{
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
try(Connection conn = DriverManager.getConnection(url, user, password)){
System.out.println("Connection succeeded");
}catch (SQLException sqle){
System.err.println("Connection Failed: " + sqle.getMessage());
}
}catch(Exception e){
System.err.println("Unexpected exception " + e.getMessage() );
}
}
}First, we define the user, password and the Connection URL. For MySQL, the database URL format is jdbc:mysql://hostname:portNumber/databaseName. Next, we create an instance of the driver using the Java Class loader on line 13. The Driver Manager enables the connection opening by passing in the previously defined parameters. The program will automatically close the connection when the inner try-with-resources block (line 14) finishes executing. If everything worked fine, the program should output: Connection succeeded.
Querying data off a table
Let’s modify our main method to create a statement off the connection instance and sore the result of a predefined query in a ResultSet object. A resultset object maintains a cursor pointing to its current data row. Initially the cursor sets its position before the first row; meaning, the first call of next()will move the cursor up to the first row of data.
Assuming there is a database named example and a table carrierservice running on MySQL Port 3305 (MySQL default’s port is 3306).
public static void main (String... args){
String user = "gone";
String password = "fishing";
String url = "jdbc:mysql://localhost:3305/example";
String query = "SELECT id, code, name FROM carrierservice";
try{
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
try(
Connection conn = DriverManager.getConnection(url, user, password);
Statement stm = conn.createStatement();
ResultSet rsSet = stm.executeQuery(query) ) {
while(rsSet.next()){
int id = rsSet.getInt("id");
String code = rsSet.getString("code");
String name = rsSet.getString("name");
System.out.println(String.format("| %d | %s | %s", id, code, name));
}
}catch (SQLException sqle){
System.err.println("Connection Failed: " + sqle.getMessage());
}
}catch(Exception e){
System.err.println("Unexpected exception " + e.getMessage() );
}
}
Using PreparedStatements
A Prepared Statement is a compiled SQL statement that executes more efficiently that the standard SQL Statement. They allow parametrization of statements by using the ? placeholder. Have a look at the following example using the same database and table:
public static void main (String... args){
String user = "gone";
String password = "fishing";
String url = "jdbc:mysql://localhost:3305/example";
String query = "SELECT id, code, name FROM carrierservice where name = ?";
try{
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
try(Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement prepStm = conn.prepareStatement(query) ) {
prepStm.setString(1, "Ground");
try(ResultSet rsSet = prepStm.executeQuery()){
while(rsSet.next()){
int id = rsSet.getInt("id");
String code = rsSet.getString("code");
String name = rsSet.getString("name");
System.out.println(String.format("%d | %s | %s", id, code, name));
}
}
}catch (SQLException sqle){
System.err.println("Connection Failed: " + sqle.getMessage());
}
}catch(Exception e){
System.err.println("Unexpected exception " + e.getMessage() );
}
}Once we set the query string to execute we need to set the string parameters to replace the placeholder (line 17). The query will return the rows that comply with the WHERE condition.
That’s pretty much all about the basics for getting you started with Java and MySQL, there is still a lot of valuable information around the Web that you can look up for to know more about Java and databases.