Spring JDBC: Introduction to JdbcTemplate (I)

1. Why do we need jdbc template as we already have plain JDBC?

1) Exception Handling

2) Opening & Closing Connections

3) Transaction Handling

Operation listed above are cumbersome and repeating in old JDBC.

Spring JDBC will take care of all those.

 

2. An example for Spring JDBC usage

1) bean

package edu.xmu.jdbc.bean;

public class Student {
    private int id;
    private String name;
    private int age;

    public Student() {
	super();
    }

    public Student(int id, String name, int age) {
	super();
	this.id = id;
	this.name = name;
	this.age = age;
    }

    public Student(String name, int age) {
	super();
	this.name = name;
	this.age = age;
    }

    public String getName() {
	return name;
    }

    public void setName(String name) {
	this.name = name;
    }

    public int getAge() {
	return age;
    }

    public void setAge(int age) {
	this.age = age;
    }

    public int getId() {
	return id;
    }

    public void setId(int id) {
	this.id = id;
    }

    @Override
    public String toString() {
	return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }

    @Override
    public int hashCode() {
	final int prime = 31;
	int result = 1;
	result = prime * result + age;
	result = prime * result + id;
	result = prime * result + ((name == null) ? 0 : name.hashCode());
	return result;
    }

    @Override
    public boolean equals(Object obj) {
	if (this == obj)
	    return true;
	if (obj == null)
	    return false;
	if (getClass() != obj.getClass())
	    return false;
	Student other = (Student) obj;
	if (age != other.age)
	    return false;
	if (id != other.id)
	    return false;
	if (name == null) {
	    if (other.name != null)
		return false;
	} else if (!name.equals(other.name))
	    return false;
	return true;
    }

}

 

2) DAO

package edu.xmu.jdbc.dao;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import edu.xmu.jdbc.bean.Student;

public interface StudentDao {
    public void setDataSource(DataSource dataSource);

    /**
     * Truncate table
     */
    public void clearAllStudents();

    /**
     * Insert student into db
     * 
     * @param name
     * @param age
     */
    public void createStudent(String name, Integer age);

    /**
     * Get student info by student id
     * 
     * @param id
     * @return
     */
    public Student retrieveStudent(int id);

    /**
     * Update student info by student id
     * 
     * @param id
     * @param student
     */
    public void updateStudent(int id, Student student);

    /**
     * Delete student info by student id
     * 
     * @param id
     */
    public void deleteStudent(int id);

    /**
     * List all students info
     * 
     * @return
     */
    public List<Student> listStudent();

    /**
     * Get number of student
     * 
     * @return
     */
    public int getStudentCount();

    /**
     * Get student info map whose id <= threshold
     * 
     * @param threshold
     *            : The id threshold
     * @return key: Student ID <br/>
     *         value: Student
     * 
     */
    public Map<Integer, Student> getStudentMap(int threshold);

    /**
     * Get all students' info map
     * 
     * @return key: Student ID <br/>
     *         value: Student
     */
    public Map<Integer, Student> getStudentMap();
}

3) DAO Impl

package edu.xmu.jdbc.dao;

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

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import edu.xmu.jdbc.bean.Student;

public class StudentDaoImpl implements StudentDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    private static Logger logger = Logger.getLogger(StudentDaoImpl.class);

    public JdbcTemplate getJdbcTemplate() {
	return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
	this.jdbcTemplate = jdbcTemplate;
    }

    public DataSource getDataSource() {
	return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
	this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void clearAllStudents() {
	String sql = "truncate table student";
	jdbcTemplate.execute(sql);
    }

    public void createStudent(String name, Integer age) {
	String sql = "insert into student(name, age) values(?, ?)";

	// int rowCount = jdbcTemplate.update(sql, name, age);
	// int rowCount = jdbcTemplate.update(sql, new Object[] { name, age });
	int rowCount = jdbcTemplate.update(sql, new Object[] { name, age },
		new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER });

	// We do not even need to log here, cause spring jdbc will do logging
	logger.debug(rowCount + " rows affected.");
    }

    public Student retrieveStudent(int id) {
	String sql = "select id, name, age from student where id=?";
	Student student = jdbcTemplate.queryForObject(sql, new Object[] { id },
		new RowMapper<Student>() {

		    public Student mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			logger.debug("Extracting row[" + rowNum + "].");

			int id = rs.getInt(1);
			String name = rs.getString(2);
			int age = rs.getInt(3);

			return new Student(id, name, age);
		    }

		});

	return student;
    }

    public void updateStudent(int id, Student student) {
	String name = student.getName();
	int age = student.getAge();

	String sql = "update student set name=?, age=? where id=?";

	int rowCount = jdbcTemplate.update(sql, new Object[] { name, age, id },
		new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER,
			java.sql.Types.INTEGER });

	logger.debug(rowCount + " rows affected.");
    }

    public void deleteStudent(int id) {
	String sql = "delete from student where id=?";

	int rowCount = jdbcTemplate.update(sql, new Object[] { id },
		new int[] { java.sql.Types.INTEGER });

	logger.debug(rowCount + " rows affected.");
    }

    public List<Student> listStudent() {
	String sql = "select id, name, age from student";

	List<Student> studentList = jdbcTemplate.query(sql,
		new RowMapper<Student>() {

		    public Student mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			logger.debug("Extracting row [" + rowNum + "].");
			// int id = rs.getInt("id");
			int id = rs.getInt(1);
			// String name = rs.getString("name");
			String name = rs.getString(2);
			// int age = rs.getInt("age");
			int age = rs.getInt(3);
			return new Student(id, name, age);
		    }
		});
	return studentList;
    }

    public int getStudentCount() {
	String sql = "select count(id) from student";
	// queryForInt(sql) is depreciated
	// int count = jdbcTemplate.queryForInt(sql);
	int count = jdbcTemplate.queryForObject(sql, Integer.class);

	return count;
    }

    public Map<Integer, Student> getStudentMap(int threshold) {
	String sql = "select id, name, age from student where id<=?";

	Map<Integer, Student> studentMap = jdbcTemplate.query(sql,
		new Object[] { threshold },
		new int[] { java.sql.Types.INTEGER },
		new ResultSetExtractor<Map<Integer, Student>>() {

		    public Map<Integer, Student> extractData(ResultSet rs)
			    throws SQLException, DataAccessException {
			Map<Integer, Student> studentMap = new HashMap<Integer, Student>();
			while (rs.next()) {
			    int id = rs.getInt("id");
			    String name = rs.getString("name");
			    int age = rs.getInt("age");

			    Student student = new Student(id, name, age);
			    studentMap.put(id, student);
			}
			return studentMap;
		    }
		});

	return studentMap;
    }

    /*
     * <p> In most of the cases developers convert a retrieved list to their
     * desired Map later by manipulating the list.</p> <p> This method provided
     * a more convenient way to exetract map from result set </p>
     * 
     * @see edu.xmu.jdbc.dao.StudentDao#getStudentMap()
     */
    public Map<Integer, Student> getStudentMap() {
	String sql = "select id, name, age from student";

	Map<Integer, Student> studentMap = jdbcTemplate.query(sql,
		new ResultSetExtractor<Map<Integer, Student>>() {

		    public Map<Integer, Student> extractData(ResultSet rs)
			    throws SQLException, DataAccessException {
			Map<Integer, Student> studentMap = new HashMap<Integer, Student>();
			while (rs.next()) {
			    int id = rs.getInt("id");
			    String name = rs.getString("name");
			    int age = rs.getInt("age");

			    Student student = new Student(id, name, age);
			    studentMap.put(id, student);
			}
			return studentMap;
		    }
		});

	return studentMap;
    }
}

4) Test case

package edu.xmu.jdbc.dao;

import static org.junit.Assert.assertEquals;

import java.util.List;
import java.util.Map;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import edu.xmu.jdbc.bean.Student;

public class StudentDaoTest {

    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private StudentDao studentDao;

    @Before
    public void setUp() {
	dataSource = new DriverManagerDataSource(url, username, password);
	dataSource.setDriverClassName("com.mysql.jdbc.Driver");

	studentDao = new StudentDaoImpl();
	studentDao.setDataSource(dataSource);
    }

    @Test
    public void createStudentTest() {
	studentDao.createStudent("Davy", 24);
	studentDao.createStudent("Jones", 25);
	studentDao.createStudent("Cal", 22);

	int count = studentDao.getStudentCount();
	assertEquals(3, count);
    }

    @Test
    public void retrieveStudentTest() {
	studentDao.createStudent("Davy", 24);
	Student student = studentDao.retrieveStudent(1);

	assertEquals(1, student.getId());
	assertEquals("Davy", student.getName());
	assertEquals(24, student.getAge());
    }

    @Test
    public void updateStudentTest() {
	studentDao.createStudent("Davy", 24);
	Student student = new Student("Jones", 25);
	studentDao.updateStudent(1, student);
    }

    @Test
    public void deleteStudentTest() {
	studentDao.createStudent("Davy", 24);
	studentDao.deleteStudent(1);

	int count = studentDao.getStudentCount();
	assertEquals(0, count);
    }

    @Test
    public void listStudentTest() {
	Student student = new Student(1, "Davy", 24);
	Student student2 = new Student(2, "Jones", 25);
	Student student3 = new Student(3, "Caly", 22);

	studentDao.createStudent("Davy", 24);
	studentDao.createStudent("Jones", 25);
	studentDao.createStudent("Caly", 22);

	List<Student> studentList = studentDao.listStudent();

	assertEquals(3, studentList.size());
	assertEquals(student, studentList.get(0));
	assertEquals(student2, studentList.get(1));
	assertEquals(student3, studentList.get(2));
    }

    @Test
    public void getStudentCountTest() {
	studentDao.createStudent("Davy", 24);
	studentDao.createStudent("Jones", 25);
	studentDao.createStudent("Caly", 22);

	int count = studentDao.getStudentCount();
	assertEquals(3, count);
    }

    @Test
    public void getStudentMapTest1() {
	studentDao.createStudent("Davy", 24);
	studentDao.createStudent("Jones", 25);
	studentDao.createStudent("Caly", 22);

	Map<Integer, Student> studentMap = studentDao.getStudentMap();

	assertEquals(3, studentMap.size());
    }

    @Test
    public void getStudentMapTest2() {
	studentDao.createStudent("Davy", 24);
	studentDao.createStudent("Jones", 25);
	studentDao.createStudent("Caly", 22);

	Map<Integer, Student> studentMap = studentDao.getStudentMap(2);

	assertEquals(2, studentMap.size());
    }

    @After
    public void tearDown() {
	studentDao.clearAllStudents();
    }
}

 

 

 

 

Reference Links:

1) http://www.tutorialspoint.com/spring/spring_jdbc_example.htm listed detailed example for jdbctemplate usage

2) http://www.mkyong.com/spring/spring-jdbctemplate-jdbcdaosupport-examples/

3) http://docs.spring.io/spring/docs/3.0.x/reference/jdbc.html official documents for Spring-JDBC DAO support

4) http://stackoverflow.com/questions/15661313/jdbctemplate-queryforint-long-is-deprecated-in-spring-3-2-2-what-should-it-be-r discussed why queryForInt/Long is depreciated.

5) http://docs.spring.io/spring/docs/current/javadoc-api/deprecated-list.html official list for depreciated methods and prefered substitution methods