Spring jdbcTemplate 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 JdbcTemplate for implementing our database operations. We will look at how to create JdbcTemplate bean and inject into out DAO. The data source should always be created as a Spring bean and provided to our JdbcTemplate.

										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 JdbcTemplate

													Load the Spring application context and access the DAO bean to invoke DAO methods

											Let's get started.

												Create a maven project of artifact "maven-archetype-quickstart" as shown HERE. Remember to select the correct artifact "maven-archetype-quickstart" as shown below.

										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 EmployeeDAOImplWithJDBCTemplate

										EmployeeDAOImplWithJDBCTemplate

package com.javavision.SpringJDBC.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

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

import com.javavision.SpringJDBC.model.Employee;


public class EmployeeDAOImplWithJDBCTemplate implements EmployeeDAO {
	
	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

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

	public Employee getEmployee(long employeeId) {
		Employee emp;
		try {
			emp = jdbcTemplate.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;
	}

	public int updateEmployee(long employeeId, String newName) {
		String update_employee = "UPDATE employee set name =? where employee_id = ?;";
		int count = jdbcTemplate.update( update_employee,
						new Object[] { newName, employeeId });
		return count;
	}

	public int deleteEmployee(long employeeId) {
		String delete_employee = "DELETE from employee where employee_id = ?;";
		int count = jdbcTemplate.update( delete_employee,
						new Object[] { employeeId });
		return count;
	}
	
}

										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="employeeDAOWithJDBCTemplate" class="com.javavision.SpringJDBC.dao.EmployeeDAOImplWithJDBCTemplate">
        <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>

										NOTE :

										
												When the employeeDAOWithJDBCTemplate bean is created, the property "jdbcTemplate" is injected with the JdbcTemplate Spring bean that's created which in turn uses the DataSource Spring bean.

										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 MainCallerForJDBCTemplate {

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

										Project Structure: