Implement Traditional JDBC
Overview
In previous chapter we implemented Dependency injection using Spring. Till now we had mocked the DAO class to represent Employee data. We will now fetch this employee information from the Database. In this chapter we implement the traditional JDBC in EmployeeDAO.java to store and fetch Employee information. In future chapters we will implement the Spring JDBC to understand its advantages over the Traditional JDBC approach. We make use of HSQLDB for database.The design will be as follows.
Lets Begin
We will create Eclipse Maven project as follows-
First modify the pom.xml to include dependency
for HSQLDB. So our POM will be as follows
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.javainuse</groupId> <artifactId>employee-management-system</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.0.5.RELEASE</version> </dependency> <dependency> <groupId>hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>1.8.0.10</version> </dependency> </dependencies> </project>Next we modify the EmployeeDAO.java to implement the traditional JDBC for various employee operations like saving Employee Information, retrieving employee information from DB etc. In the EmployeeDAO.java we have added a constructor. This constructor calls a method CreateEmpoyeeTable. The CreateEmpoyeeTable method creates a table named Employee in the DB. This table is created only the first time EmployeeDAO is loaded. If the table already exists in the DB then no new table is created.For adding and retrieving Employee information we have used traditional JDBC. The remaining classes will be the same as in previous chapter.
We will now modify the EmployeeDAO.java to make DataBase connections to fetch data from the DataBase.
package com.javainuse.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 java.util.ArrayList;
import java.util.List;
import com.javainuse.domain.Employee;
public class EmployeeDAO {
//Create Employee Table in constructor.
public EmployeeDAO() {
try {
Class.forName("org.hsqldb.jdbcDriver");
CreateEmpoyeeTable();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static void CreateEmpoyeeTable() {
try {
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:hsqldb:file:database.dat;shutdown=true", "sa", "");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"create table Employee(empId VARCHAR(20), name VARCHAR(50), designation VARCHAR(50),salary VARCHAR(50))");
System.out.println("Created new table Employee");
}
finally {
if (con != null)
con.close();
}
}
catch (SQLException e) {
System.out.println("Employee table has already been created...");
}
}
public void addNewEmployee(Employee employee) {
try {
Connection con = null;
PreparedStatement insertEmployee = null;
try {
con = DriverManager.getConnection("jdbc:hsqldb:file:database.dat;shutdown=true", "sa", "");
insertEmployee =
con.prepareStatement("insert into Employee (empid, name, designation) values (?, ?, ?)");
insertEmployee.setString(1, employee.getEmpId());
insertEmployee.setString(2, employee.getName());
insertEmployee.setString(3, employee.getDesignation());
insertEmployee.executeUpdate();
System.out.println("Created new Employee");
}
finally {
if (con != null)
con.close();
}
}
catch (SQLException e) {
System.out.println("Exception Occured");
}
}
public List<Employee> getAllEmployees() {
List<Employee> results = new ArrayList<Employee>();
try {
Connection con = null;
PreparedStatement retrieveBooks = null;
ResultSet rs = null;
try {
con = DriverManager
.getConnection(
"jdbc:hsqldb:file:database.dat;shutdown=true",
"sa", "");
retrieveBooks = con.prepareStatement("select * from Employee");
rs = retrieveBooks.executeQuery();
while (rs.next()) {
String empId = rs.getString(1);
String name = rs.getString(2);
String designation = rs.getString(3);
double salary = 0;
Employee nextBook = new Employee(empId, name, designation,
salary);
results.add(nextBook);
}
} finally {
if (rs != null)
rs.close();
if (con != null)
con.close();
}
} catch (SQLException e) {
System.out.println("Exception Occured");
}
return results;
}
}
When we run the program we get the output as
Disadvantages of Traditional JDBC-
- The main disadvantage of this approach is the amount of repetitive code we have to write to accomplish DB connection. Such repetetive code is known as boilerplate code. For example for both addEmployee and getAllEmployees method we have to write the boilerplate code to get the connection. If in future another method suppose deleteEmployee is added then this boilerplate code has to be repeated
- The error handling is more repetitive and complex. JDBC uses the rather Uninformative SQLException. JDBC has no exception hierarchy.
- Care has to be taken to release the resources after the DB function has been performed. We handle this code in the final block.
Download Source Code
Download it - Employee Management System Using Traditional JDBCWhat Next?
In the next chapter will implement Programming to Interface concept in Spring.