Connecting to MySQL using JDBC

In this blog, I explain the steps required to connect to MySQL database from Java using JDBC.

Install MySQL

First ensure you have MySQL installed and running either in your system or a system to which you are connected. In order to obtain a free copy of MySQl, go to MySQL Community Server and download it. Choose your platform and then select the kind of download. I have selected Microsoft Windows and Windows (x86, 32-bit), MSI Installer. You choose according to your platform.

At the time of downloading you are prompted to login or register as the case may be. However you have option to skip that and directly go to download.

Once you obtain the software, install MySQL into your system. The installation process depends on the platform and kind of download you have chosen. In case of MSI for Microsoft Windows, just double click on .MSI file and installation begins.

Install MySQL Workbench

MySQL Workbench is a GUI tool that allows you to work with MySQL. You need to download it from here. Choose your platform and the kind of download you want. After download is complete, install it and connect to database using it.

Obtain JDBC Driver

In order to connect to MySQL from Java we need to use JDBC Driver for MySQL. Connector/J is the name of JDBC driver for MySQL. Go to Connector/j and download it. Once download is complete, unzip the download to a folder. The folder into which you extracted .zip file contains a folder called mysql-connector-java-5.1.15. It contains mysql-connector-java-5.1.15.bin.jar (JDBC driver) and docs folder that provides information regarding how to use Connector/j.

MySQl Connection/j 5.15 supports features of JDBC 4.0 and it provides a JDBC type 4 driver.

Program to Connect to MySQL

Write the following Java program to connect to MySQL using Connector/j 5.15. In order to use Connector/j, we need to include mysql-connection-java.5.1.15.bin.jar in the classpath.

import java.sql.Connection;
import java.sql.DriverManager;

public class TestConnection {
    public static void main(String[] args) {
         try {
                Class.forName("com.mysql.jdbc.Driver");
                System.out.println("Loaded driver");
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mysql?user=root&password=srikanth");
                System.out.println("Connected to MySQL");
                con.close();
         } 
         catch (Exception ex) {
                ex.printStackTrace();
         }
    }
}

The first thing to do is to load driver com.mysql.jdbc.Driver, which is present in .jar file supplied by Connector/j.

In the above program we are trying to connect to mysql database of MySQL running on the current system (localhost). We use user root with its password.

Once you are connected to MySQl you can start using the rest of the JDBC API in the same manner as you use with any other database.

The following program shows how to list titles from Books table (to be created by user) in MySQL database.

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

public class BooksList {
     public static void main(String[] args) {
         try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mysql?user=root&password=srikanth");
                Statement st = con.createStatement();
                ResultSet rs = st.executeQuery("select * from books");

                while(rs.next()) {
                    System.out.println( rs.getString("title"));
                }
                st.close();
                rs.close();
                con.close();
        } catch (Exception ex) {
                ex.printStackTrace();
        }
    }
}

That's all you need to do to access MySQL using JDBC from Java.