Implement SpringJDBC

Overview

In previous chapter we implemented Traditional JDBC in Spring. We also implemented the Programming to Interface concept in previous chapter. In this chapter we will implement Spring JDBC .The design will be as follows.

Lets Begin

We will create Eclipse Maven project as follows-

First modify the pom.xml to include dependency spring-jdbc
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/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.javainuse</groupId> <artifactId>employee-management-system</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>employee-management-system Maven Webapp</name> <url>http://maven.apache.org</url> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.0.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>1.8.0.10</version> </dependency> </dependencies> <build> <finalName>employee-management-system</finalName> </build> </project>
Next we create Spring JDBCTemplate in the configuration file. This template is inserted in our new DAO class EmployeeDAOImplUsingSpringJDBC using constructor dependency injection. Our Configuration class and the new DAO implementation class will be as follows-
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd"> <beans> <bean id="employeeService" class="com.javainuse.service.EmployeeService"> <property name="empDAO" ref="employeeDAO" /> </bean> <bean id="employeeDAO" class="com.javainuse.dao.EmployeeDAOImplUsingSpringJDBC"> <constructor-arg ref="jdbcTemplate" /> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource"> <property name="driverClass" value="org.hsqldb.jdbcDriver" /> <property name="url" value="jdbc:hsqldb:file:database.dat;shutdown=true" /> <property name="username" value="sa" /> <property name="password" value="" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg ref="dataSource" /> </bean> </beans>

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 org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.javainuse.domain.Employee;

public class EmployeeDAOImplUsingSpringJDBC implements EmployeeDAO {

    JdbcTemplate jdbcTemplate;

	//SpringJdbcTemplate inserted using constructor injection.
    public EmployeeDAOImplUsingSpringJDBC(JdbcTemplate jdbcTemplate) {
        try {
            this.jdbcTemplate = jdbcTemplate;
            Class.forName("org.hsqldb.jdbcDriver");
            createEmployeeTable();
        }
        catch (ClassNotFoundException e) {
            System.out.println("Exception occured in DAO constructor");
        }

    }

    private void createEmployeeTable() {
        try {
            jdbcTemplate.update(
                "create table Employee(empId VARCHAR(20), name VARCHAR(50), designation VARCHAR(50),salary VARCHAR(50))");
        }
        catch (Exception e) {
            System.out.println("Employee table has already been created...");
        }
    }

	@Override
    public void addNewEmployee(Employee employee) {
        jdbcTemplate.update("insert into Employee (empid, name, designation) values (?, ?, ?)",
            employee.getEmpId(), employee.getName(), employee.getDesignation());
    }

	@Override
    public List getAllEmployees() {
        return jdbcTemplate.query("select * from Employee", new EmployeeMapper());
    }

}

//implement Spring RowMapper.
class EmployeeMapper implements RowMapper {

    public Employee mapRow(ResultSet rs, int rowNumber) throws SQLException {
        String empId = rs.getString(1);
        String name = rs.getString(2);
        String designation = rs.getString(3);
        double salary = 0;
        Employee emp = new Employee(empId, name, designation, salary);
        return emp;
    }

}

Now we are done with the coding changes. If we run the MainApplication.java we get the output.