JDBC & MySQL Database Connection by Example

JDBC Connection Basics

Java database connectivity (JDBC) is a standard application programming interface (API) specification that is implemented by different database vendors to allow Java programs to access their database management systems. The JDBC API consists of a set of interfaces and classes written in the Java programming language. Interestingly, JDBC is not an acronym and thus stands for nothing but popular as Java Database Connectivity. According to Sun (now merged in Oracle) JDBC is a trademark term and not an acronym. It was named to be redolent of ODBC.

JDBC implementation comes in form of a database driver for a particular DBMS vendor. A Java program that uses the JDBC API loads the specified driver for a particular DBMS before it actually connects to a database.

JDBC Connection - JDBC Design

JDBC --a trademark, comes bundled with Java SE as a set of APIs that facilitates Java programs to access data stored in a database management system, particularly in a Relational Database. At the time of designing JDBC (Java database connectivity), following goals were kept in mind:

  • JDBC (Java database connectivity) should be an SQL level API; therefore, it should allow user to construct SQL statements and embed them into Java API calls to run those statements on a database. And then results from the database are returned as Java objects (ResultSets) and access-problems as exceptions.
  • JDBC (Java database connectivity) should provide a driver manager to allow third party drivers to connect to specific databases, where database vendors could provide their own drivers to plug into the driver manager.
    For example MySQL provides its driver in form of a JAR archive (e.g., mysql-connector-java-***-bin.jar). This driver first should be loaded into memory in order to connect to a MySQL database, and then driver manager creates a connection with help of the loaded driver. Whole process will be explained step by step later in this article.
  • JDBC (Java database connectivity) should be simple; there would then be a simple mechanism to register third party drivers with the driver manager. Finally, JDBC came into existence as a result of above goals, and two APIs were created. Application programmers use the JDBC API, and database vendors use the JDBC Driver API.

JDBC Connection - Create Database for Use

To start using JDBC (Java database connectivity) first you would need a database system, and then a JDBC (Java database connectivity) driver for your database. During this article JDBC (Java database connectivity) will be explained with MySQL, but you can use it with any database system (such as Microsoft SQL, Oracle, IBM DB2, PostgresSQL), provided that you have a JDBC (Java database connectivity) driver for your database system.

Suppose you have MySQL and a functional Java environment installed on your system, where you write and execute Java programs. For MySQL, download MySQL Connector/J the official JDBC (Java database connectivity) driver for MySQL. You will get a JDBC driver mysql-connector-java-***-bin.jar in zipped driver bundle. Place this JAR in Java's classpath because this JAR is the JDBC driver for MySQL and will be used by JDBC (Java database connectivity).

After having environment ready to experiment JDBC connectivity you need to create a database and at least one table in the database containing a few records, we name the example database EXPDB, and table inside EXPDB is EXPTABLE. I create this database with root privileges, if you do so it's fine but if you create a database with different user then ensure that you have permissions to create, update and drop tables in the database you created (to perform above stated operations by JDBC - Java database connectivity). Now, let's create EXPDB database, a table EXPTABLE in EXPDB, and insert a few records as follows. Following steps we are executing manually, later we will connect to this database with the help of JDBC - Java database connectivity.

1. Connect to MySQL

To experiment with JDBC (Java database connectivity) you have to create a database and connect to it. On successful connection you get MySQL command prompt mysql> as follows: 

C:\> mysql -h localhost -u root 
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.46-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2. Create Database

To create a database you have to supply CREATE DATABASE command followed by the database name and then semicolon. 

mysql> CREATE DATABASE EXPDB;
Query OK, 1 row affected (0.08 sec)

mysql>

3. Use Database

Once you have created the database then you have to select it for use to perform operations on it. Command USE <DATABASE-NAME> begins a mysql (The MySQL Command-line Tool) session and lets you perform database operations. Note that, you need to create database only once but have to use it each time you start a mysql session. 

mysql> USE EXPDB;
Database changed

mysql>

4. Create a table

The EXPTABLE, example table to demonstrate JDBC (Java database connectivity) is created by issuing CREATE TABLE command as shown below: 

mysql> CREATE TABLE EXPTABLE (
    -> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> NAME VARCHAR (50)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql>

5. Insert Records

Just for illustration, two records into EXPTABLE are being inserted, you can insert more if you like. Later we will perform select and edit operations on these records using JDBC (Java database connectivity). 

			
mysql> INSERT INTO EXPTABLE (NAME) VALUES ("ANUSHKA K");
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO EXPTABLE (NAME) VALUES ("GARVITA K");
Query OK, 1 row affected (0.00 sec)

mysql>

6. List Records

Select records from EXPTABLE to see that both records have been inserted correctly in previous step. 

mysql> SELECT * FROM EXPTABLE;
+----+-----------+
| ID | NAME      |
+----+-----------+
|  1 | ANUSHKA K |
|  2 | GARVITA K |
+----+-----------+
2 rows in set (0.03 sec)

mysql>

Up to now you have created a database (EXPDB) and a table (EXPTABLE) within it, inserted two records in the table, and listed those records to be sure that everything went fine so far. Now, we will access EXPDB through Java program and JDBC (Java database connectivity), insert and list records of EXPTABLE within from the program through JDBC.

JDBC Connection - Using JDBC

So far we have gone through basic JDBC (Java database connectivity) concepts and created a trivial MySQL database to connect to it through JDBC within from a Java program. To access the database through Java program and JDBC, you would require following items in addition.

1. JDBC Connection - DataBase URL or String

Database URL for JDBC connection or JDBC connection string follows more or less similar syntax to that of ordinary URLs. It tells the protocol used to connect to database, subprotocol, location of database, port number on which database listens client requests, and database name. The example syntax may look like jdbc:mysql://localhost:3306/EXPDB. Aforementioned URL specifies a MySQL database named EXPDB running on localhost on port 3306.

2. JDBC Connection - Driver Class

As we have obtained the JDBC driver in form of a JAR file (mysql-connector-java-***-bin.jar) in which the driver for MySQL database is located. This driver needs to be registered in order to access EXPDB. Driver file name for MySQL is com.mysql.jdbc.Driver. This file has to be loaded into memory before you get connected to database, else you will result into java.sql.SQLException: No suitable driver exception.

3. JDBC Connection - Database User Name and Password

To get a JDBC connection to the database you would require the username and password, it is the same username and password which we used, while connecting to MySQL.

Java Program for JDBC Connection

Now we will start writing Java program to connect to EXPDB (our example database) through JDBC (Java database Connectivity) and perform INSERT and SELECT operations for demonstration. To illustrate this piece of work, we would take following steps, and finally collect all pieces of code to assemble the complete program.

1. Register JDBC Driver Class

Registering JDBC driver class with the DriverManager means loading JDBC driver class in memory. You can load JDBC driver class in two ways. One way is to load the JDBC driver class in Java program is as follows: 

try
{
    // loads com.mysql.jdbc.Driver into memory
    Class.forName("com.mysql.jdbc.Driver");
} 
catch (ClassNotFoundException cnf) 
{
    System.out.println("Driver could not be loaded: " + cnf);
}

If you look at above piece of code you will see that Class.forName takes a string, fully qualified class name of the JDBC driver class file, as an argument and loads the corresponding class into memory. It throws a ClassNotFoundException if fails to locate the driver file. That's the reason it is surrounded by try block.

Alternatively, you can load the JDBC driver class by setting jdbc.drivers property. Then at run time you specify the property with a command-line argument as follows: 

C:\>java -D jdbc.drivers=com.mysql.jdbc.Driver <Program Name>

You can also set the system property within from Java code as follows: 

System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");

2. Connect to Database through JDBC Driver

In order to connect to example database EXPDB you need to open a database connection in Java program that you can do as follows by using JDBC driver: 

//jdbc driver connection string, db username and password
private String connectionUrl = "jdbc:mysql://localhost:3306/EXPDB";
private String dbUser = "root";
private String dbPwd = "mysql";
private Connection conn;
try
{
    conn = DriverManager.getConnection(connectionUrl, dbUser, dbPwd);
}
catch (SQLException sqle) 
{
    System.out.println("SQL Exception thrown: " + sqle);
}

Above piece of code using DriverManager gets you a connection to the database specified by connectionUrl. When above code fragment is executed, the DriverManager iterates through the registered JDBC drivers to find a driver that can use the subprotocol specified in the connectionUrl. Don't forget to surround getConnection() code by try block, because it can throw an SQLException.

3. Execute SQL Statements through JDBC Connection

Now that you have a JDBC Connection object conn, you would like to execute SQL statements through the conn (JDBC connection) object. A connection in JDBC is a session with a specific database, where SQL statements are executed and results are returned within the context of a connection. To execute SQL statements you would need a Statement object that you would acquire by invoking createStatement() method on conn as illustrated below. 

try
{
    Statement stmt = conn.createStatement();
}
catch (SQLException sqle) 
{
    System.out.println("SQL Exception thrown: " + sqle);
}

You would get a Statement object stmt by executing above piece of code. By definition, createStatement() throws an SQLException so the code line Statement stmt = conn.createStatement(); should either surrounded by try block or throw the exception further. On successful creation of stmt you can send SQL queries to your database with help of executeQuery(), and executeUpdate() methods. Also the Statement has many more useful methods.

Next, form a query that you would like to execute on database. For an instance, we will select all records from EXPDB, our example database. Method executeQuery() will get you a ResultSet object that contains the query results. You can think a ResultSet object as two dimensional array object, where each row of an array represents one record. And, all rows have identical number of columns; some columns may contain null values. It is all depend upon what is stored in database. 

String queryString = "SELECT * FROM EXPTABLE"
try
{
    ResultSet rs = stmt.executeQuery(queryString);
}
catch (SQLException sqle) 
{
    System.out.println("SQL Exception thrown: " + sqle);
}

Here again, method executeQuery() throws an SQLException, so it should either be surrounded by try block or further throw the exception. After getting a ResultSet object rs you may like to process records for further use. Here, for illustration we would print them on screen. 

System.out.println("ID \tNAME");
System.out.println("============");
while(rs.next())
{
   System.out.print(rs.getInt("id") + "\t" + rs.getString("name"));
   System.out.println();
}

In above code snippet we process one record at a time or you say one row at a time. ResultSet's next() method helps us to move one record forward in one iteration, it returns true until reaches to last record, false if there are no more records to process. We access ResultSet's columns by supplying column headers to getXxx() methods as they are in EXPTABLE e.g., rs.getInt("id"). You can also access those columns by supplying column indices to getXxx() methods e.g., rs.getInt(1) will return you the first column of current row pointed by rs.

Note that the first column in a ResultSet row has index 1 not zero, as it is usually done in Java.

4. Close JDBC Connection

JDBC connection to database is a session; it has been mentioned earlier. As soon as you close the session you are no longer connected to database; therefore, you would not be able to perform any operation on database. Closing connection must be the very last step when you are done with all database operations. You can close a connection to database as follows: 

try
{
    if (conn != null)
    {
        conn.close();
        conn = null;
    }
}
catch (SQLException sqle) 
{
    System.out.println("SQL Exception thrown: " + sqle);
}

Method close() too, throws SQLException as other methods those perform database operations do, so it should also be surrounded by try block.

JDBC Connection - Java Code

Let's assemble above JDBC code fragments explained from step 1. Registering JDBC Driver Class to 4. Closing JDBC Connection steps and get a working program. 

/* JDBC_Connection_Demo.java */
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBC_Connection_Demo
{
  /* static block is executed when a class is loaded into memory 
   * this block loads MySQL's JDBC driver
   */
  static
  {
    try
    {
      // loads com.mysql.jdbc.Driver into memory
      Class.forName("com.mysql.jdbc.Driver");
    } 
    catch (ClassNotFoundException cnf) 
    {
      System.out.println("Driver could not be loaded: " + cnf);
    }
  }
 
  public static void main(String[] args)
  {
    String connectionUrl = "jdbc:mysql://localhost:3306/EXPDB";
    String dbUser = "root";
    String dbPwd = "mysql";
    Connection conn;
    ResultSet rs;
    String queryString = "SELECT ID, NAME FROM EXPTABLE";
 
    try
    {
      conn = DriverManager.getConnection(connectionUrl, dbUser, dbPwd);
      Statement stmt = conn.createStatement();
 
      // INSERT A RECORD
      stmt.executeUpdate("INSERT INTO EXPTABLE (NAME) VALUES (\"TINU K\")");
 
      // SELECT ALL RECORDS FROM EXPTABLE
      rs = stmt.executeQuery(queryString);
 
      System.out.println("ID \tNAME");
      System.out.println("============");
      while(rs.next())
      {
        System.out.print(rs.getInt("id") + ".\t" + rs.getString("name"));
        System.out.println();
      }
      if (conn != null)
      {
        conn.close();
        conn = null;
      }
    }
    catch (SQLException sqle) 
    {
      System.out.println("SQL Exception thrown: " + sqle);
    }
  }
} //JDBC_Connection_Demo ends here
 
---------------------------------------
OUTPUT
------
ID 	NAME
============
1.	ANUSHKA K
2.	GARVITA K
3.	TINU K

In order to execute above program you have to include MySQL JDBC driver JAR in Java's classpath. We used mysql-connector-java-5.1.13-bin.jar JDBC driver, it maybe a different version in your case.

Last Word

This tutorial explained JDBC & MySQL connection steps through an example Java program. Hope you have enjoyed reading this tutorial. Please do write us if you have any suggestion/comment or come across any error on this page. Thanks for reading!

References

  1. JDBC Introduction
  2. Establishing a Connection
  3. What is JDBC?
  4. Core Java, Vol. 2: Advanced Features, 8th Edition


Share this page on WhatsApp

Get Free Tutorials by Email

About the Author

is the founder and main contributor for cs-fundamentals.com. He is a software professional (post graduated from BITS-Pilani) and loves writing technical articles on programming and data structures.