Spring JdbcDaoSupport with rowmapper Example

 

                            NOTE: Source Code available for download at the bottom of the page

 
What are we going to learn?
   We will learn how to use Spring JdbcDaoSupport for implementing our database operations. 
 
What's needed?
  • Maven
  • JDK 1.5 or higher
  • eclipse 
  • mySQL database
Overall design
  • We will create a employee table to store the employee information.
  • Create DataSource Spring bean with required db parameters
  • Inject the DataSource in to jdbctemplate
  • Create Insert and Select operations on employee with the help of JdbcDaoSupport
  • Load the Spring application context and access the DAO bean to invoke DAO methods
 
Let's get started.
 
employee table
CREATE TABLE `employeestore`.`employee` (
  `employee_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `age` VARCHAR(45) NULL,
  PRIMARY KEY (`employee_id`));
 
 
 
Create a maven project of artifact "maven-archetype-quickstart" as shown here. Remember to select the correct artifact "maven-archetype-quickstart" as shown below.
 
artificat selection
 
 
Update the dependencies in pom file.
 
For Spring context jars 
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-context</artifactId>
	<version>4.0.0.RELEASE</version>
</dependency>
 
For Spring JDBC jars
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>4.0.0.RELEASE</version>
</dependency>
 
For mySQl connector/driver
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.28</version>
</dependency>
 
 
Employee Model Class
package com.javavision.SpringJDBC.model;

public class Employee {
	public String name;
	public long employeeId;
	public int age;
	
	public Employee(){
		
	}
	
	public Employee(String name, long employeeId, int age) {
		super();
		this.name = name;
		this.employeeId = employeeId;
		this.age = age;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public long getEmployeeId() {
		return employeeId;
	}
	public void setEmployeeId(long employeeId) {
		this.employeeId = employeeId;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
	
}
 
This will act as our model object for communication with DAO.
 
 
Create an interface EmployeeDAO to define the dao operations.
 
EmployeeDAO 
package com.javavision.SpringJDBC.dao;

import com.javavision.SpringJDBC.model.Employee;

public interface EmployeeDAO {
	
	public int insertEmployee(Employee employee);
	public Employee getEmployee( long employeeId );

}
 
 
Create implementation class EmployeeDAOImpl
 
 
EmployeeDAOImpl 
package com.javavision.SpringJDBC.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.javavision.SpringJDBC.model.Employee;

public class EmployeeDAOImpl extends JdbcDaoSupport implements EmployeeDAO {

	public int insertEmployee(Employee employee) {
		
 		String insert_employee = "INSERT INTO employee(employee_id,name,age)VALUES(?,?,?);";
		int count = getJdbcTemplate().update( insert_employee,
				new Object[] { employee.getEmployeeId(), employee.getName(),employee.getAge() });
		return count;
	}

	public Employee getEmployee(long employeeId) {
		Employee emp;
		try {
			emp = getJdbcTemplate().queryForObject(
					"select * from employee where employee_id = " + employeeId,
					new RowMapper<Employee>() {
						public Employee mapRow(ResultSet rs, int rownumber)
								throws SQLException {
							Employee e = new Employee();
							e.setEmployeeId(rs.getLong("employee_id"));
							e.setName(rs.getString("name"));
							e.setAge(rs.getInt("age"));
							return e;
						}
					});
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
		return emp;
	}
}
                            NOTE: There is no need to define JdbcTemplate
 
 
Create the application context.
 
application-context.xml
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
 
	 <bean id="employeeDAO" class="com.javavision.SpringJDBC.dao.EmployeeDAOImpl">
        <property name="jdbcTemplate" ref="myJdbcTemplate"/>
    </bean>   
    
     <bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource"/>
    </bean>
    
    <bean id="dataSource" 
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/employeestore" />
		<property name="username" value="root" />
		<property name="password" value="admin" />
	</bean>
</beans>
 
 
 
Let's put everything together.
 
Main class to load the spring application context and call the DAO methods.
 
package com.javavision.SpringJDBC;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.javavision.SpringJDBC.dao.EmployeeDAO;
import com.javavision.SpringJDBC.model.Employee;


public class MainCaller {

	public static void main(String[] args) {
		
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext(
				"application-context.xml");
		EmployeeDAO employeeDAO = (EmployeeDAO)applicationContext.getBean("employeeDAO");
		
		Employee e1 = new Employee("Dan", 123, 20);
		int count = employeeDAO.insertEmployee(e1);
			
		Employee retrievedEmployee = employeeDAO.getEmployee(123);
		System.out.println("Retrieved Employee :: " + retrievedEmployee.getEmployeeId() + " ,"
				+ retrievedEmployee.getName() + " ,"
				+ retrievedEmployee.getAge());

	}

}
 
 
 
 
Project Structure:
 
Project Structure
 
 
 
Source Code : SpringJDBC.rar
Web Analytics