As Java developers, we work with data all the time and develop almost all the applications that interact with some sort of database and most of the times it's relational. Generally, the application needs to interact with a database in order to get data from it. And the typical way for connecting a Java application to a database would be through JDBC.
Java Database Connectivity (JDBC) is a standard Java API. It is used for database connectivity between the Java programming language and a great variety of databases. JDBC is an application programming interface that allows a Java programmer to access the database from a Java code using sets of standard interfaces and classes written in a Java programming language.
JDBC provides several methods for querying, updating, and deleting data in RDBMS, such as SQL, Oracle, and so on. The JDBC library provides APIs for tasks such as:
- Making a connection to a database
- Creating the SQL statements
- Executing the SQL queries in the database
- Viewing and modifying the resulting records
- Closing a database connection
It is generally considered a pain to write a code to get JDBC to work. We need to write a boilerplate code to open a connection and to handle the data. Another problem with JDBC is that of poor exception hierarchy, such as SQLException, DataTruncation, SQLWarning, and BatchUpdateException. These require less explanation and a major problem is that all of these exceptions are deployed as checked exceptions, which mandate the developer to go ahead to implement a try block. It's very difficult to recover from a catch block, when an exception is thrown even during the statement execution, and most of the time these catch blocks are used for generating log messages for those exceptions.
Here, we will take the example of JdbcHrPayrollSystem, which connects to the Apache Derby database that we saw in the previous section. We will write a query to retrieve the record, we will look at the code required to run this query, and then we will print out the retrieved record.
Whenever we need to write a code to access the database, we have to make sure that the drivers for the database that we are trying to connect to are available for the project. For Apache Derby, we need to include a driver so that the project can connect to the database, as shown here:
project > properties > Libraries > Add External jars > (navigate to the derby folder) > lib folder > select (derby.jar and derbyclient.jar) > ok
The final directory structure of the application is shown in the following screenshot:
It is a good practice to design DAO using the program to an interface principle, which states that concrete implementations must implement the interface that is used in the program that wants to use the implementation rather than the implementation class itself. Following this principle, we will first define an interface for EmployeeDao and declare some data access methods that include the methods for creating new employee details, or getting employee details using the employee ID, and then inserting the employee details into the table.
We have package org.packt.Spring.chapter5.JDBC.model that contains the class named employee, which is a simple model class containing the employee ID, name, and its corresponding getter and setter. This employee class also has a parameterized constructor with parameters, such as id and name that set the instance variable:
package org.packt.Spring.chapter5.JDBC.model;
public class Employee {
private int id;
private String name;
public Employee(int id, String name) {
setId(id);
setName(name);
}
// setter and getter
}We have package org.packt.Spring.chapter5.JDBC.dao that has the interface EmployeeDao and the class EmployeeDaoImp. This interface contains the method for creating the Employee table, inserting the values into the table, and fetching the employee data from the table based on the employee ID, as shown here:
package org.packt.Spring.chapter5.JDBC.dao;
import org.packt.Spring.chapter5.JDBC.model.Employee;
public interface EmployeeDao {
// get employee data based on employee id
Employee getEmployeeById(int id);
// create employee table
void createEmployee();
// insert values to employee table
void insertEmployee(Employee employee);
}Now, we will provide an implementation for the EmployeeDao interface. The EmployeeDaoImpl class is responsible for connecting to the database and getting or setting the values. The complexity lies in the JDBC code that goes inside the methods that connect to the database. First, we need to have a connection object, and then we need to initialize ClientDriver, which in our case, is specific to the Apache Derby driver. Now, we need to open a connection using the database URL. Then, based on the functionality, we need to prepare and execute a query:
package org.packt.Spring.chapter5.JDBC.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.packt.Spring.chapter5.JDBC.model.Employee;
public class EmployeeDaoImpl implements EmployeeDao {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
static final String DB_URL = "jdbc:derby://localhost:1527/db";
private void registerDriver() {
try {
Class.forName(JDBC_DRIVER).newInstance();
} catch (InstantiationException e) {
} catch (IllegalAccessException e) {
} catch (ClassNotFoundException e) {
}
}Here, the getEmployeeById(int id) method will fetch the employee information based on the employee ID:
@Override
public Employee getEmployeeById(int id) {
Connection conn = null;
Employee employee = null;
try {
// register apache derby driver
registerDriver();
// open a connection using DB url
conn = DriverManager.getConnection(DB_URL);
// Creates a PreparedStatement object for sending parameterized SQL
// statements to the database
PreparedStatement ps = conn
.prepareStatement("select * from employee where id = ?");
// Sets the designated parameter to the given Java int value
ps.setInt(1, id);
// Executes the SQL query in this PreparedStatement object and
// returns the ResultSet object
ResultSet rs = ps.executeQuery();
if (rs.next()) {
employee = new Employee(id, rs.getString("name"));
}
rs.close();
ps.close();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
return employee;
}The createEmployee() method creates an Employee table with the column ID and name, as shown in the following code snippet:
@Override
public void createEmployee() {
Connection conn = null;
try {
// register apache derby driver
registerDriver();
// open a connection using DB url
conn = DriverManager.getConnection(DB_URL);
Statement stmt = conn.createStatement();
stmt.executeUpdate("create table employee (id integer, name char(30))");
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}In the following code snippet, the insertEmployee(Employee employee) method will insert the employee information into the Employee table:
@Override
public void insertEmployee(Employee employee) {
Connection conn = null;
try {
// register apache derby driver
registerDriver();
// open a connection using DB url
conn = DriverManager.getConnection(DB_URL);
Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into employee values ("
+ employee.getId() + ",'" + employee.getName() + "')");
stmt.close();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}We have package org.packt.Spring.chapter5.JDBC.main that contains the class HrPayrollSystem with the main() method. In the main() method, we will initialize DAO and call the methods of DAO to create a table, insert the data, and then fetch the data from the table, as shown here:
package org.packt.Spring.chapter5.JDBC.main;
import org.packt.Spring.chapter5.JDBC.dao.EmployeeDao;
import org.packt.Spring.chapter5.JDBC.dao.EmployeeDaoImpl;
import org.packt.Spring.chapter5.JDBC.model.Employee;
public class HrPayrollSystem {
public static void main(String[] args) {
EmployeeDao employeeDao = new EmployeeDaoImpl();
// create employee table
employeeDao.createEmployee();
// insert into employee table
employeeDao.insertEmployee(new Employee(1, "Ravi"));
// get employee based on id
Employee employee = employeeDao.getEmployeeById(1);
System.out.println("Employee name: " + employee.getName());
}
}Having shown the trouble in using JDBC, in the next section, we will be discussing the DAO support in the Spring Framework to remove the troubling points one after the other.