JDBC Examples Introduction

Please go through this post (for beginners) before referring our JDBC examples to understand better.

Download and Install Databases

Depending on your requirement you may use any database. We show the mostly used ones.

Oracle

If you are going to use Oracle database refer this link on how to install and setup Oracle database for JDBC.

MySQL

Install MySQL

• MySQL (Refer this link to install MySQL)
  • Examples uses MySQL as sample database. Refer this tutorial on MySQL for creating database and tables, inserting data into tables, etc.

Using MySQL database

To view the results and access MySQL database tables you can use one of the following options;

Accessing MySQL database from Command prompt (terminal):

• Open command prompt (Windows) or Terminal(Linux) and type 
  

  mysql –u [your-username] –p

  and press enter and type the password.

• If you are using Windows, you can also use MySQL command line client which will be available in All programs menu.
• For creating a new database, refer this page. In this example, the database name is ‘jdbcdb‘.
• After creating the database type the command “use <database_name>;”
• For creating a new table, refer this page.
• Insert some records in the table. Refer this page for inserting rows using MySQL.

Accessing MySQL database from Eclipse’s Data Source Explorer:
Refer this link, to configure MySQL datasource in Eclipse and use Data Source Explorer to connect to, navigate, and interact with datasource resources.

Download MySQL connector

• MySQL Connector/J is the official JDBC driver for MySQL.
• The connector can be downloaded from: http://dev.mysql.com/downloads/connector/j/. This tutorial uses 5.1 version. Unzip the connector to a safe location on your computer which contains MySQL Connector J JAR.

H2 Database

Install H2 Database

• H2 database can be downloaded from: http://www.h2database.com/html/download.html either installer or zip file.
• To install the software as Server (remote connections using JDBC or ODBC over TCP/IP) or Mixed (local and remote connections at the same time) mode, run the installer or unzip it to a directory of your choice.

Download H2 database connector

• If you have decided to run this in Embedded mode (local connections using JDBC) then the above step is optional and just the JAR file is enough which can be downloaded from the above link (http://www.h2database.com/html/download.html) under “JAR File” section.

Configure JDBC driver in Eclipse IDE

You need to add the downloaded Connector JAR in client project’s classpath or buildpath.
Right click on your Java Project -> Properties -> Buildpath -> Libraries -> Add External JAR and select this JAR file.

You can follow the same procedure to add H2 database jar file as well.

If you fail to add this driver and run your program, it will throw a runtime exception mentioning Driver class not found as shown below (for mysql)

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

Sample Database

Mostly our JDBC examples use a sample database “jdbcdb” which contains the following tables;

Department Table:

Field	Type	Key	Extra
dept_id	int	Primary Key	auto_increment
dept_name	varchar(50)
location	varchar(50)

Employee Table:

Field	Type	Key	Extra
emp_id	int	Primary Key	auto_increment
emp_name	varchar(50)
dob	date
salary	double
dept_id	int	Foreign key references department(dept_id)

Design Patterns Used

As a best practice, almost all the JDBC examples use the following design patterns,

Singleton design pattern:

We write a class (ConnectionFactory) which implements singleton pattern defining database connection configuration statements and methods to make connection to the database. Reason for making this class as singleton is, we can create one object of this class and can create many Connection objects (one factory, many objects).

package com.theopentutorials.jdbc.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionFactory {
	//static reference to itself
	private static ConnectionFactory instance = new ConnectionFactory();
	public static final String URL = "jdbc:mysql://localhost/jdbcdb";
	public static final String USER = "YOUR_DATABASE_USERNAME";
	public static final String PASSWORD = " YOUR_DATABASE_PASSWORD";
	public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"; 
	
	//private constructor
	private ConnectionFactory() {
		try {
			Class.forName(DRIVER_CLASS);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private Connection createConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			System.out.println("ERROR: Unable to Connect to Database.");
		}
		return connection;
	}	
	
	public static Connection getConnection() {
		return instance.createConnection();
	}
}

Transfer Object (TO) design pattern:

Transfer Object pattern encapsulates business data. To implement this pattern, we write a class with properties defining the table attributes. For example, to implement Employee TO class, we write the following code;

package com.theopentutorials.jdbc.to;

import java.util.Date;

public class Employee {
	
	private int empId;
	private String empName;
	private Date dob;
	private double salary;
	private int deptId;
	
	//getters and setters omitted for brevity
}

Data Access Object design pattern:

DAO classes implements Data Access Object (DAO) pattern which encapsulates access to the data source.
This class defines methods for each query, where we first create connection object using ConnectionFactory (by calling ConnectionFactory.getConnection()). Using this connection object we create statement and execute queries.

package com.theopentutorials.jdbc.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.theopentutorials.jdbc.db.ConnectionFactory;
import com.theopentutorials.jdbc.db.DbUtil;
import com.theopentutorials.jdbc.to.Employee;

public class EmployeeDAO {
	private Connection connection;
	private Statement statement;

	public EmployeeDAO() { }
	
	public Employee getEmployee(int employeeId) throws SQLException {
		String query = "SELECT * FROM employee WHERE emp_id=" + employeeId;
		ResultSet rs = null;
		Employee employee = null;
		try {
			connection = ConnectionFactory.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(query);
			. . . .
			. . . .
		} finally {
			DbUtil.close(rs);
			DbUtil.close(statement);
			DbUtil.close(connection);
		}
		return employee;
}

Closing database connection:

Safe method to close database ResultSet, Statement and Connection is to write a finally block and release all the resources. We check for non-null reference before calling the close() instance method to avoid NullPointerException.

finally {
	if (connection != null) {
		try {
			connection.close();
		} catch (SQLException e) {
			/* Ignore */
		}
	}
	if (statement != null) {
		. . .
	}
	if (resultSet != null) {
		. . .
	}
}

Instead of repeating these statements in all methods, we write a helper class (DbUtil.java) with methods to close these resources.

package com.theopentutorials.jdbc.db;

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

public class DbUtil {

	public static void close(Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				/*log or print or ignore*/
			}
		}
	}

	public static void close(Statement statement) {
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				/*log or print or ignore*/
			}
		}
	}

	public static void close(ResultSet resultSet) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				/*log or print or ignore*/
			}
		}
	}
}

These methods can be called from finally block.

try {
	connection = ConnectionFactory.getConnection();
	statement = connection.createStatement();
	rs = statement.executeQuery(query);
} finally {
	DbUtil.close(rs);
	DbUtil.close(statement);
	DbUtil.close(connection);
}

Writing own Exception class

We write custom exception class which is used by DAO methods. SQL or any other exception is caught in catch block where we create instance of custom exception with exception message and cause (Throwable instance) and throw this exception. Some DAO methods throw this ApplicationException.

package com.theopentutorials.jdbc.exception;

public class ApplicationException extends Exception {
	private static final long serialVersionUID = 1L;

	public ApplicationException() {	}
	
	public ApplicationException(String message) {
		super(message);		
	}

	public ApplicationException(String message, Throwable cause) {
		super(message, cause);		
	}	
}

Program Control Flow:

The program control flow logic is as follows;

Steps:

1. [Optional] Java application client creates Transfer Object. This is required if the DAO method accepts TO as parameter.
2. Client creates DAO object.
3. Client uses DAO object to call the method, optionally passing the TO object created in step-1.
4. DAO accesses the underlying data source (in our case, it is database) and performs query operations, optionally using TO passed by the client.
5. DAO uses/creates TO object and sets the data read from the data source and returns this object to the client.
6. Client obtains the TO object returned by DAO and either modifies it or gets the data and displays it to the user.