Saturday, January 12, 2013

Data Access With Spring JDBC (Note 1)

When developing enterprise applications, we have lots of options when it comes to the implementation of the DAO (Data access Object) layer. Either we may use an ORM framework like Hibernate, iBatis or pure JDBC. 

JDBC fulfills almost all the necessary data access requirements. and provides low level access to the data access operations. But we have to handle connections and lots of other things. 

Spring Data Access framework provides a nice way to access data while the framework take cares of managing connections and so on. So we can focus on business requirement thoroughly rather than worrying about JDBC nuts and bolts :) .

A major advantage of using Spring JDBC over hibernate is we can effectively fine tune the data access layer. so if the application is time critical Spring JDBC is a good choice.

OK. sorry for the long story. lets have a look at our cup of Spring JDBC :)

In order to preserve the simplicity, I have selected a very simple scenario where we are going to apply Spring JDBC. It is a many to many relationship between Employee and Project(Please see below EER diagram).
 OK, lets create model classes to represent this scenario.
Employee.java

package model;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Kanishka
 */
public class Employee {
    private int empID;
    private String empName;
    private String empTel;
    private float salary;
    private List<Project> projects;

    public Employee() {
        projects=new ArrayList<Project>();
    }

    public int getEmpID() {
        return empID;
    }

    public void setEmpID(int empID) {
        this.empID = empID;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public String getEmpTel() {
        return empTel;
    }

    public void setEmpTel(String empTel) {
        this.empTel = empTel;
    }

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }

    public List<Project> getProjects() {
        return projects;
    }

    public void setProjects(List<Project> projects) {
        this.projects = projects;
    }

    @Override
    public String toString() {
        return "Employee{" + "empID=" + empID + ", empName=" + empName + ", empTel=" + empTel + ", salary=" + salary + ", projects=" + projects + '}';
    }
}

Project.java
package model;

import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Kanishka
 */
public class Project {
    private int projID;
    private String projName;
    private String projCode;
    private int projDuration;
    private List employeeList;

    public Project() {
        employeeList=new ArrayList();
    }

    public int getProjID() {
        return projID;
    }

    public void setProjID(int projID) {
        this.projID = projID;
    }

    public String getProjName() {
        return projName;
    }

    public void setProjName(String projName) {
        this.projName = projName;
    }

    public String getProjCode() {
        return projCode;
    }

    public void setProjCode(String projCode) {
        this.projCode = projCode;
    }

    public int getProjDuration() {
        return projDuration;
    }

    public void setProjDuration(int projDuration) {
        this.projDuration = projDuration;
    }

    public List getEmployeeList() {
        return employeeList;
    }

    public void setEmployeeList(List employeeList) {
        this.employeeList = employeeList;
    }

    @Override
    public String toString() {
        return "Project{" + "projID=" + projID + ", projName=" + projName + ", projCode=" + projCode + ", projDuration=" + projDuration + ", employeeList=" + employeeList + '}';
    }
}

Now we are ready to configure the project. I am using Maven in this sample project. Maven dependencies are
  • Spring context
  • Spring JDBC
  • MySql JDBC connector
  • Commons DBCP (for the data source)
Pom.xml

<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.kani</groupId>
    <artifactId>JDBCTemplateDemo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>JDBCTemplateDemo</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
  
    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>
      
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>3.2.0.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>3.2.0.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
    </dependency>
</project>
The next plan is implementing DAO layer. Here I am using programming to an interface(P2I) in my DAO layer as I am planning to use DAO design pattern in my sample solution. 

Spring declares that they will take care of following actions.
  • Opening connections.
  • Prepare and execute the statement.
  • Set up the loop to iterate through the results (if any).
  • Process any exception.
  • Handle transactions.
  • Close the connection, statement and resultset.
So we can focus on queries, parameters and entity mapping stuff.

In my DAO implementation I will consider only basic operations that will sufficient to demonstrate JDBC template.

Interfaces.
  • IEmployeeDAO
  • IProjectDAO
IProjectDAO.java
package dao;

import java.util.List;
import model.Employee;
import model.Project;

/**
 *
 * @author Kanishka
 */
public interface IProjectDAO {
    public String QUERY_CREATE_NEW_PROJECT="INSERT INTO project(projName,projCode,projDuration) VALUES (?,?,?)";
    public String QUERY_GET_PROJECTS_OF_EMPLOYEE="SELECT  proj.projID AS `projID`,  proj.projName AS `projName`,  proj.projCode AS `projCode`,  proj.projDuration AS `projDuration`,  ehp.Employee_empID AS `empID` FROM  employee_has_project ehp  INNER JOIN project proj ON ehp.Project_projID = proj.projID WHERE  ehp.Employee_empID = ?";
    
    public List<Project> getProjectsOfEmployee(Employee emp);
    public boolean createNewProject(Project proj);
}
IEmployeeDAO.java
package dao;

import java.util.List;
import model.Employee;

/**
 *
 * @author Kanishka
 */
public interface IEmployeeDAO {
    public String QUERY_GET_ALL_EMPLOYEES="SELECT empID,empName,empTel,salary FROM employee";
    public String QUERY_FIND_EMPLOYEE_BY_ID="SELECT empID,empName,empTel,salary FROM employee e WHERE e.empID=?";
    public String QUERY_ADD_NEW_EMPLOYEE="INSERT INTO employee(empName,empTel,salary) VALUES (?,?,?)";
    
    public List<Employee> getAllEmployees();
    public Employee findEmployeeByID(int id);
    public boolean createNewEmployee(Employee empl);
}

Lets see the implementation classes for IEmployeeDAO and IProjectDAO. 
When it comes to the implementation of above interfaces, we can use capabilities of the spring framework. In this case I have injected configured datasource into my DAO Implementations using Spring framework dependency injection support. Then I have used Spring JDBC template provided by the Spring framework in my data access logic.
ProjectDAOImpl.java


package dao;

import dao.mappers.ProjectMapper;
import java.util.List;
import javax.sql.DataSource;
import model.Employee;
import model.Project;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Kanishka
 */
@Repository
public class ProjectDAOImpl implements IProjectDAO{
    private JdbcTemplate jdbcTemplate;

    @Autowired( required=true)
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    public List<Project> getProjectsOfEmployee(Employee emp) {
        System.out.println("Executing : " + QUERY_GET_PROJECTS_OF_EMPLOYEE);
        
        List<Project> projectList=
                this.jdbcTemplate.query(QUERY_GET_PROJECTS_OF_EMPLOYEE,
                new ProjectMapper(),emp.getEmpID());
        
        return projectList;
    }

    public boolean createNewProject(Project proj) {
        System.out.println("Executing : " + QUERY_CREATE_NEW_PROJECT);
        
        int affectedRows=jdbcTemplate.update(QUERY_CREATE_NEW_PROJECT, 
                proj.getProjName(),proj.getProjCode(),proj.getProjDuration() );
        
        return (affectedRows == 1);
    }
    
}
EmployeeDAOImpl.java
package dao;

import dao.mappers.EmployeeMapper;
import java.util.List;
import javax.sql.DataSource;
import model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Kanishka
 */
@Repository
// Indicates that an annotated class is a "Repository" (or "DAO"). 
public class EmployeeDAOImpl implements IEmployeeDAO{
    private JdbcTemplate jdbcTemplate;

    @Autowired(required=true)
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    public List<employee> getAllEmployees() {
        System.out.println("Executing : " + QUERY_GET_ALL_EMPLOYEES);
        
        List<employee> employees=
                this.jdbcTemplate.query(QUERY_GET_ALL_EMPLOYEES,
                new EmployeeMapper());
        
        return employees;
    }

    public Employee findEmployeeByID(int id) {
        System.out.println("Executing : " + QUERY_FIND_EMPLOYEE_BY_ID);
        
        Employee employee=new Employee();
        try{
            employee=
                    this.jdbcTemplate.queryForObject(QUERY_FIND_EMPLOYEE_BY_ID,
                    new EmployeeMapper(),id);
            
        }catch(DataAccessException de){
            System.err.println(de.getMessage());
        }
        return employee;
    }

    public boolean createNewEmployee(Employee empl) {
        System.out.println("Executing : " + QUERY_ADD_NEW_EMPLOYEE);
        
        int affectedrows=
                this.jdbcTemplate.update(QUERY_ADD_NEW_EMPLOYEE,
                empl.getEmpName(),empl.getEmpTel(),empl.getSalary());
        
        return (affectedrows == 1);
    }
    
}
Mapping between model classes and relational data is achieved by implementing the RowMapper<T> interface. As our scenario has 2 POJO classes, we have to implement 2 RowMappers for each class type.
  • RowMapper<Employee>
EmployeeMapper.java


package dao.mappers;

import java.sql.ResultSet;
import java.sql.SQLException;
import model.Employee;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author Kanishka
 */
public final class EmployeeMapper implements RowMapper<Employee> {

    /*implement abstract method for declaring mapping
     *between POJO attributes and relational table attributes
     */
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee employee=new Employee();
        employee.setEmpID(rs.getInt("empID"));
        employee.setEmpName(rs.getString("empName"));
        employee.setEmpTel(rs.getString("empTel"));
        employee.setSalary(rs.getFloat("salary"));
        return employee;
    } 
}
  • RowMapper<Project>
 ProjectMapper.java
package dao.mappers;

import java.sql.ResultSet;
import java.sql.SQLException;
import model.Project;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author Kanishka
 */
public class ProjectMapper implements RowMapper<Project>{

     /*implement abstract method for declaring mapping
     *between POJO attributes and relational table attributes
     */
    public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
        Project proj=new Project();
        proj.setProjCode(rs.getString("projCode"));
        proj.setProjDuration(rs.getInt("projDuration"));
        proj.setProjID(rs.getInt("projID"));
        proj.setProjName(rs.getString("projName"));
        return proj;
    }
}
RowMapper<T> implementation will be reused in each iteration if our query returns a list of results. 

Now it is time to have a look at our spring configuration.
Beans.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    
    <bean id="employeeDAOImpl" class="dao.EmployeeDAOImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <bean id="projectDAOImpl" class="dao.ProjectDAOImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>
</beans>

DBCP  data source is configured and referred as a property of both DAO implementations. We can replace Apache commons DBCP with any other compatible data source easily with this configuration. JDBC template is constructed when it injects the configured data source into our DAO implementation.

Finally it is time to invoke our DAO layer :) Actually we don't need to wait for the implementation of the DAO layer. If we have 2 development teams where one team is working on BLL (Business logic layer) and other team is working on DAO (Data access object layer) it is possible for those teams to work in parallel.

App.java
package com.kani.jdbctemplatedemo;

import dao.EmployeeDAOImpl;
import dao.ProjectDAOImpl;
import java.util.List;
import model.Employee;
import model.Project;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * @author Kanishka
 */
public class App 
{
    public static void main( String[] args )
    {
        ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
        
        ProjectDAOImpl proDaoImplBean = context.getBean("projectDAOImpl", ProjectDAOImpl.class);
        EmployeeDAOImpl emplDaoImplBean = context.getBean("employeeDAOImpl",EmployeeDAOImpl.class);
        
        //Operation 1 : creating a new roject
        Project proj=new Project();
        proj.setProjCode("P11");
        proj.setProjDuration(23);
        proj.setProjName("MACYS");
        System.out.println(proDaoImplBean.createNewProject(proj));
        
        //operation 2 : create new employee
        Employee newEmpl=new Employee();
        newEmpl.setEmpName("Madhuranga Sampath");
        newEmpl.setEmpTel("0773284329");
        newEmpl.setSalary(2000);
        System.out.println(emplDaoImplBean.createNewEmployee(newEmpl));
        
        //operation 3 : get employee by id
        Employee empl=emplDaoImplBean.findEmployeeByID(2);

        //operation 4 : load employee's projects
        List<Project> projs=proDaoImplBean.getProjectsOfEmployee(empl);
        empl.setProjects(projs);
        System.out.println(empl);
        
        //operation 5 : get all employees
        List<Employee> employees=emplDaoImplBean.getAllEmployees();
        for (Employee employee : employees) {
            System.out.println(employee);
        }
    }
}
Output
Executing : INSERT INTO project(projName,projCode,projDuration) VALUES (?,?,?)
true
Executing : INSERT INTO employee(empName,empTel,salary) VALUES (?,?,?)
true
Executing : SELECT empID,empName,empTel,salary FROM employee e WHERE e.empID=?
Executing : SELECT  proj.projID AS `projID`,  proj.projName AS `projName`,  proj.projCode AS `projCode`,  proj.projDuration AS `projDuration`,  ehp.Employee_empID AS `empID` FROM  employee_has_project ehp  INNER JOIN project proj ON ehp.Project_projID = proj.projID WHERE  ehp.Employee_empID = ?
Employee{empID=2, empName=Sajith Athukorala, empTel=0777345678, salary=2000.0, projects=[Project{projID=2, projName=Proj2, projCode=PD452, projDuration=12, employeeList=[]}]}
Executing : SELECT empID,empName,empTel,salary FROM employee
Employee{empID=1, empName=Kanishka Dilshan, empTel=0777123123, salary=1000.0, projects=[]}
Employee{empID=2, empName=Sajith Athukorala, empTel=0777345678, salary=2000.0, projects=[]}
Employee{empID=3, empName=Ishan Primal, empTel=07123588, salary=1500.0, projects=[]}
Employee{empID=4, empName=Madhuranga Sampath, empTel=0773284329, salary=2000.0, projects=[]}

The demo project can be downloaded from the following location.
Thank you for your time!

References : 
Springsource Documentation
http://static.springsource.org/spring/docs/3.1.x/spring-framework-reference/html/jdbc.html 
Kaushik's youtube channel
http://www.youtube.com/playlist?list=PL1A506B159E5BD13E

1 comments:

Ruchira Bandara said...

nice article..i noticed a few things though..once you decorate your classes with spring annotations such as @Repository,@Service,@Controller or the generic @Component, they are automatically registered in the Spring Application Context, hence you don't need to manually register them..and also you can use named queries with spring jdbc template..IMHO I would rather use hibernate session to interact with the database than going through the template..

Post a Comment

 
© Copyright 2035 kani.stack.notez
Theme by Yusuf Fikri