Java Database Connectivity (JDBC) with MySQL Tutorial

By Java Database Connectivity (JDBC) with MySQL Tutorial

Hi, and welcome to this tutorial. We are going to learn what is JDBC and how to connect to the database with Java.

At some point, you will need to store data for your applications. You can save the data in a file system or use a database system. There is numerous reason why you should consider using a database system.

Why use Databases?

  1. You can query data in a database.
  2. You can look up data from a database relatively rapidly.
  3. You can relate data from two different tables together using JOINs.
  4. You can create meaningful reports from data in a database.
  5. Your data has a built-in structure for it.
  6. Information on a given type is always stored only once.
  7. Databases are ACID, i.e. (atomicity, consistency, isolation, durability)
  8. Databases are fault-tolerant.
  9. Databases can handle enormous data sets.
  10. Databases are concurrent i.e., multiple users can use them at the same time without corrupting the data.
  11. Databases scale well.

So what is JDBC?

JDBC stands for Java Database Connectivity. It is an API that manages connections to a database. It issues queries and commands and also handles results from the database. 

JDBC was developed as an alternative to the C-based ODBC (Open Database Connectivity). The JDBC interface has two layers:

  1. The JDBC API – that supports communication between Java applications and JDBC manager.
  2. The JDBC driver – that supports communication between JDBC manager and the database driver.

This tutorial offers a good explanation of how JDBC works.

Using JDBC to connect to a database with five steps

There are several types of DBMS (Database Management Systems) available. These include SQLite, MySQL, Oracle, Postgres, etc. Each of these DBMS has their own JDBC database connector available. In this tutorial, we shall see how to connect to the MySQL database.

The following are the six steps to connect any java application with a database using JDBC: 

  1. Download and import the JDBC driver class into your java application.
  2. Load the database driver.
  3. Establish a connection using the JDBC library.
  4. Create a statement
  5. Issue commands using the connection.
  6. Close the connection.

Let’s go through each step together.

Step 1: Download and import the JDBC driver class into your java application.

Do a simple web search for your database and JDBC to find a driver for the database you want to use. In this case, search “mysql jdbc driver” and you will get the driver for MySQL.

MySQL is a relational DBMS, and the driver class for the MySQL database is com.mysql.jdbc.Driver.

Before we continue, let’s create a simple database schema that we shall use.

Create a database with the name example and create a table with the name users. The following is the structure of the table.

CREATE TABLE `users` (
`firstname` VARCHAR(50) NULL DEFAULT NULL,
`lastname` VARCHAR(50) NULL DEFAULT NULL,
`age` INT(11) NULL DEFAULT NULL
);

Let’s now create a new project in NetBeans IDE and add MySQL JDBC Driver. Right click on the Libraries node and select MySQL JDBC Driver and click OK.


Now create a new java class with the name JDBC_Example.java. This will be the initial code for our class:

package jdbc_example;
public class JDBC_Example {
    public static void main(String[] args) {
        System.out.println("JDBC Example");
    }
}

Run it, and if you don't encounter any errors, the program will print:

JDBC Example

Now let’s import the required JDBC imports. Paste the following lines to do the necessary imports.

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

Let me explain the above lines one by one.

Connection – this class represents the connection to the database.

DriverManger – this class obtains the connection to the database.

SQLException – this class is used to handle any SQL errors between your java application and the database.

ResultSet and Statement – These classes model the data result sets and SQL statements, respectively.  

Step 2: Loading the database Driver

Before connecting to the database, you will need to load the driver and register it to use the driver in your java application. Driver registration is done once in your application, and you can do driver registration in two ways.

Class.forName(): Here we load the driver;s class file into memory at the run-time like this

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

There is no need of using keyword new or creating an object.

DriverManager.registerDriver(); DriverManager class has a static member called register. Here we call the constructor of the driver class at compile time like this.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

Step 3: Establish a connection using the JDBC library

After loading the driver, we now make a connection to the database like this

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/example", "root", "");

Driver manager class has a method called getConnection() which returns connection and expects three arguments, ie url, user and password to establish connection to MySQL database.

When MySQL is used, we use jdbc:mysql driver. Localhost is the IP address where the database is located, 3306 is the port number for MySQL database.

You need to catch errors that might occur by use of try-catch statements or throws exception statement.

This is the content of our java class at this point. Please run the code to ensure you successfully connect to the database.

package jdbc_example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JDBC_Example {
    public static void main(String[] args) {
        try {
            System.out.println("JDBC Example");
            Class.forName("com.mysql.jdbc.Driver");
            //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/example", "root", "");
            System.out.println("Connected successfully");
        } catch (ClassNotFoundException ex) {
            System.err.println("Class not found");
            Logger.getLogger(JDBC_Example.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            System.err.println("Not connected to the database!");
            Logger.getLogger(JDBC_Example.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

The following is the output

JDBC Example
Connected successfully

If you see any error, ensure your MySQL server is running and started. Then also ensure your added MySQL JDBC library in your project.

Step 4: Create a statement

After a successful connection has been established, you can now interact with the database. Statement and PreparedStatement interfaces provide methods for sending queries and receiving data from your database. 

Let us create a statement like this

Statement stmt = conn.createStatement();

Here, conn is our database connection reference.

Step 5: Issue commands using the connection.

Once a statement has been created, you can now execute it (SQL Query). This s the most important part. There are queries for updating, inserting, inserting, and selecting data from databases. I hope you are familiar with basic SQL statements like INSERT, UPDATE, DELETE, and SELECT.

The executeQuery() method of Statement interface is used to issue commands for getting data from the database. This method returns an object of ResultSet that is used to get all records of a table.

The executeUpdate(sql) method is used to issue commands for updating and deleting.

Example of inserting one row in the users table

String sql = "INSERT INTO users (firstname, lastname, age) VALUES ('eric', 'muchenah', 24)";
int m = st.executeUpdate(sql);
if (m == 1) {
    System.out.println("Inserted successfully");
} else {
    System.out.println("Insertion failed");
}

Output

JDBC Example
Connected successfully
Inserted successfully

Step 6: Close the connection

So after completing our work, we close our connection. We use the close() method of the Connection interface to close the connection. Objects of ResultSet and Statements are closed automatically when we close the connection.

Example:

Conn.close();


Final Java Database Connection Implemetation

package jdbc_example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JDBC_Example {
    public static void main(String[] args) {
        try {
            System.out.println("JDBC Example");
            Class.forName("com.mysql.jdbc.Driver");
            //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/example", "root", "");
            System.out.println("Connected successfully");
            Statement st = conn.createStatement();
            String sql = "INSERT INTO users (firstname, lastname, age) VALUES ('eric', 'muchenah', 24)";
            int m = st.executeUpdate(sql);
            if (m == 1) {
                System.out.println("Inserted successfully");
            } else {
                System.out.println("Insertion failed");
            }
        } catch (ClassNotFoundException ex) {
            System.err.println("Class not found");
            Logger.getLogger(JDBC_Example.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            System.err.println("Not connected to the database!");
            Logger.getLogger(JDBC_Example.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

Java connection with other DBMS

This tutorial explains Java database connection with SQLite. It is a good tutorial that you can read.

This other tutorial explains Java database connection with Oracle.

Conclusion

In Java ecosystem, you will most likely find yourself interacting with the database. The good news is that there is a stable JDBC database connector for all databases that you may use.

JDBC provides an easy-to-use solution in Java application with a database. Once you understand JDBC, you can connect to any database there is. JDBC is sufficient for small applications. Some big applications will call for Java Persistence API (JPA) so as to develop a more formal data access layer.

Thank you for reading this article. Please vote below and share it with your friends and fellow android developers.

Was this article helpful?
Donate with PayPal: https://www.paypal.com/donate

Bessy
Eric Murithi Muchenah

Life is beautiful, time is precious. Make the most out of it.