JDBC : How to Connect Java to MySQL Database

Cover Photo

In this tutorial, I’m going to teach you how to connect your MySQL database to a simple java Program. This tutorial is mostly aimed at beginners so I’ve tried as much I can to make it as simple as possible. Though you don’t need to be an expert in java programming or database administration, you do need to :-

1. Have a basic understanding of java programming language and OOP concepts, such as Classes and Objects, Access Modifiers, Polymorphism e.t.c, as used in java programming language.
2. Be farmiliar with MySQL and basic Realtional Database Management System(RDBMS) concepts.
3. Know how to work with a Java IDE (I will be using IntelliJ IDEA community edition for this Tutorial)

Prerequisite

1. IntelliJ IDEA IDE
2. MySQL installed on your computer. (I’m using mysql 8.0.22)
3. MySQL Workbench (or any other mysql tool e.g PHPmyAdmin)
4. An Internet connection.

What is JDBC ?

JDBC stand for Java Database Connectivity. It is an application programming interface that acts as a means in which a java program can communicate with a database. Through JDBC data can be inserted, retreived, updated or deleted from the database through your custom java program or application. JDBC allows java to be connected to various database systems such as Oracle, PostgreSQL and in our case MySQL.

Great, now that you have an idea of what JDBC is, let’s go ahead start our program. For this tutorial, I’ll be using intelliJ IDEA Community Edition 2020.3.

1. Create the java program

Open the IDE and and create a new project.

In the New Project tab select Java and choose your project’s SDK. If you don’t have an SDK in your computer, you can always download one through intelliJ. For this project I am going to use OpenJDK-15 as my project’s SDK. Click Next when done.

You can choose a template If you want to but I’m not going to. Click Next.

Give your project a name and specify the path where you want to place the project.For the sake of the tutorial, I’m going to call this project MyJDBCProgram. Click finish to create the project.

Once the project has been created, it will look something like the image below. Expand the project directory and right click on the src folder. On the pop up window Click New > Java Class as shown below.

On the popup window that shows up, enter com.myjdbcprogram.Main onto the text box to create the Main class. com.myjdbcprogram will be the package while Main will be the Main class that will be created by the IDE. Also ensure that Class has been selected on the pop up and not Interface or Enum or Annotation as shown below.

Inside the Main class create the main() method. As you may already know, a java program cannot really work without this method. It is the starting point of excecution of java programs. In the end, your application should look a little something like this :

package com.myjdbcprogram;

public class Main {
    public static void main(String[] args) {

    }
}

Congratulations, your program has been created. Now we just need add the MySQL Connector Library to our application.

2. Add MySQL Connector Library to the project

To do this, we are going to fetch it from the maven repository. Open your Project Structure and navigate to the Libraries Tab.

Click on File > Project Structure

The project structure window will be displayed. Click Libraries > + > From maven.

Enter mysql:mysql-connector-java:8.0.22 on the window and press the search button. You can also click on the dropdown button next to the textbox to see other repositories found during the search. Click OK

Click OK

Click Apply then OK on the Project Structure window to complete the set up. If there was no error encountered, then the library was successfully added.

3. Create your database

If you already have an existing database, you can just skip this step and move to the next step. If you don’t, well let’s get started. Remeber this is not a MySQL tutorial so I assume you have some understanding of mysql.

Launch MySQL. For this tutorial I am going to use MySQL WorkBench but you are free to use whatever works for you feel is right with you. Connect to your server and proceed to creating your database.

I’m going to call my database myjdbcprogram_db. Below is the SQL Command to create the database.

CREATE SCHEMA `myjdbcprogram_db` ;

Aternatively, you can just take advantage of Workbench’s GUI.

We are only going to create one table. I’ll call this table users.

CREATE TABLE `myjdbcprogram_db`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(60) NOT NULL,
  `last_name` VARCHAR(60) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE);

Let’s insert some data into our table. I’m only going to create one record but you can enter as much as you want.

INSERT INTO users(first_name,last_name)
VALUES("Ngonyoku", "Ngonyoku");

The database is looking good so far. You are free to add as many tables as you want.

4. Connecting our database to our java program.

Now that everything is set, let’s head back to our IDE and do what is necessary.

1. Import the Java.sql package :

import java.sql.*;

2. Register the Driver : This is only done once in your application. We use the forName() method in the Class class to load the driver’s class file into memory during runtime. In the example below Class.forName() has been used to load our MySQL Driver. The forName() method throws a ClassNotFoundException hence will need to be surrounded with a try catch block to catch the exception.

Class.forName("com.mysql.cj.jdbc.Driver");

The com.mysql.cj.jdbc.Driver is provided to us by the MySQL Connector we added to our project.

3. Establish the Connection : Once the driver has been loaded, you will need to initialize the Connection interface which is part of the java.sql package. Initialization of the Connection interface will be done by calling the getConnection() method through the DriverManger class. The method will take in 3 parameter i. e the URL to the database, The username from which the sql command prompt can be accessed and the password from which the sql command prompt can be accessed respectively.

private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/myjdbcprogram_db";
private static final String USER = "root";
private static final String PASSWORD = "";
Connection conn = DriverManager.getConnection(DATABASE_URL, USER, PASSWORD);

The database url is written as follows : “jdbc:mysql://localhost:3306/myjdbcprogram_db” where localhost is the name of the server, 3306 is the port number and myjdbcprogram_db is the name of your database(remeber myjdbcprogram_db is the name we gave our database is part 3 of this tutorial).jdbc:mysql://[servername]:[portname]/[database name]

If you don’t have a password, then just set the password constant to an empty string.

You can Easily get the above information when working with MySQL workBench.

The getConnection() method with throw a SQLException.

By this point, your code should a little bit like this :

5. Interacting with the Database

After establishing a successfull connection, we are now ready execute SQL commands to our database through the program. We do this by creating statements and excecuting queries.

Creating Statements : Statements define the methods in which SQL commands can be sent to the database. The following are types of statement interfaces in JDBC.

• Statement : implements simple SQL statements with no paramenters.
• PreparedStatement : pre-compiles SQL statements that might contain parameters.
• CallableStatement : Excecute stored procedures that might contain both input and output paramenters.

For this tutorial we are only going to use the Statement interface

Make a call to the createStatement() method to create a Statement through the Connection object.

Statement statement = conn.createStatement();

Where conn is the Connection that was established in Part 4 of this tutorial.

Executing SQL Queries : We have different types of queries in SQL language. Depending on the type of query used we can insert, retrieve, update or delete data from our database. Through the JDBC Statements mentioned earlier, the JDBC API has provided a few methods that are used to execute these queries.

• executeQuery() : The method executes queries that are used to retrieve data from the database. This query belongs to the Statement interface and returns an object of the ResultSet interface.

ResultSet resultSet = statement.executeQuery("SELECT * FROM users;");

• excecuteUpdate() : This method takes in an SQL query as a string parameter. The method belongs to the Statement interface and executes queries that delete, update or inserts data to the database. The method will return an integer representing the number of rows affected by the SQL statement.

int rowCount = statement.executeUpdate("INSERT INTO users(first_name, last_name) VALUES('Morio', 'Fulani');");

• execute() : The method returns true if a ResultSet object is the first to be returned after the query has been executed.

For this tutorial, I am only going to work with executeQuery()

As stated earlier, executeQuery() method returns an object of ResultSet which contains the data fetched from the database. This data will be accessed through a Cursor. The cursor is initially positioned before the first row, we need to make use of the next() method provided to us through the ResultSet interface to move the cursor. Every time the next() method is called, it returns data in the row that it is currently pointing at.

ResultSet resultSet = statement.executeQuery("SELECT * FROM users;");
resultSet.next();

Since most of the time you don’t know how many tuples will exist in the relation you are going to query, it makes sense to loop through every record to retrieve all the data. We are going to make use of the while() loop to move our cursor through every tuple in our relation after which we can extract the data stored whichever cell of the relation we want.

while (resultSet.next()) { //Loop through the data in the resultset

}

Now we are going to make use of the ResultSet getter methods to get the values of the realtion. I’m only going to make use just the getString() and getInt() methods since our relation is composed of values of values of type VARCHAR [first_name, last_name] and INT [id]. Both methods will take in the Column name as the parameters. We will the store these values inside variable of the same data type.

Note : These are not the only methods, you can check out the documentation for more.

while (resultSet.next()) { 
    String firstName = resultSet.getString("first_name");
    String lastName = resultSet.getString("last_name");
    int userId = resultSet.getInt("id");
}

Ensure that these column name match the ones in your relation.

Finally, you can now print out the data in your console.

while (resultSet.next()) { //Loop through the data in the resultset
    String firstName = resultSet.getString("first_name");
    String lastName = resultSet.getString("last_name");
    int userId = resultSet.getInt("id");

    System.out.println(userId + " : " +firstName + " " + lastName);
}

If you followed along correctly, your code should look something like this :

package com.myjdbcprogram;

import java.sql.*; //1. Import the java.sql package

public class Main {
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/myjdbcprogram_db"; // URL to database
    private static final String USER = "root"; // username of the database
    private static final String PASSWORD = "password"; // Enter your Database Password

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver"); // 2. Load and register the Driver
            Connection conn = DriverManager.getConnection(DATABASE_URL, USER, PASSWORD); // 3. Establish the Connection

            //Create a Statement
            Statement statement = conn.createStatement();

            //Execute SQL Queries
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users;");
            while (resultSet.next()) { //Loop through the data in the resultset
                String firstName = resultSet.getString("first_name");
                String lastName = resultSet.getString("last_name");
                int userId = resultSet.getInt("id");

                System.out.println(userId + " : " + firstName + " " + lastName);// Print out the data
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

Click Run on the intelliJ Toolbar and then Click Run ‘Main’ to run your program.

Provided everything ran correctly, the data in your table should be displayed on the console.

As you can see, this is the same data that is present in our relation.

6. Close the Connection

We now need to call the close() method to close our connection. This will also release the resources that are currently being used by the objects.

conn.close();

Conclusion

We have come to the end of the tutorial. Congratulations to all those who have successfully made it to these point.

Note that this is just an brief introduction to get you started with JDBC with MySQL. There are a lot of concepts out there that you are yet to uncover.

Thank you for your time and have a blessed day.

Ngonyoku out!