Spring JDBC: Introduction to JdbcTemplate (III)--JdbcTemplate Derivatives

1. JdbcDaoSupport

public abstract class JdbcDaoSupport extends DaoSupport {
	private JdbcTemplate jdbcTemplate;
	public final void setDataSource(DataSource dataSource) {
		if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
			this.jdbcTemplate = createJdbcTemplate(dataSource);
			initTemplateConfig();
		}
	}
	public final JdbcTemplate getJdbcTemplate() {
	  return this.jdbcTemplate;
	}
}

As we can see from the source code that DaoSupport simply added a JdbcTemplate property.

And when we extends JdbcDaoSupport, we don't need to write the redundancy code of setDataSource().

When we need get jdbcTemplate, we simply call getJdbcTemplate() method.

package edu.xmu.jdbc.dao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import edu.xmu.jdbc.bean.Student;

public class JdbcSupportDao extends JdbcDaoSupport {

    public void createStudent(Student student) {
	String sql = "insert into student(name, age) value(?, ?)";
	JdbcTemplate jdbcTemplate = getJdbcTemplate();
	jdbcTemplate.update(sql, student.getName(), student.getAge());
    }
}

 

2. NamedParameterDaoSupport

public class NamedParameterJdbcDaoSupport extends JdbcDaoSupport {

	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
	  return namedParameterJdbcTemplate;
	}
}

As we can see, when our dao need NamedParameterJdbcTemplate instead of JdbcTemplate,

we can extends NamedParameterDaoSupport.

3. NamedParameterJdbcTemplate

When we execute preparedStatements, we need to use ? as place holder.

But NamedParameterJdbcTemplate offers a mechanism that we can use specific name as holder.

package edu.xmu.jdbc.dao;

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

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import edu.xmu.jdbc.bean.Student;

public class NamedParameterJdbcTemplateDao extends NamedParameterJdbcDaoSupport {

    /**
     * In this method, we use MapSqlParameterSource for placeholder value
     * mapping
     * 
     * @param student
     */
    public void createStudent(Student student) {
	String sql = "insert into student(name, age) value(:name, :age)";
	NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate();

	MapSqlParameterSource namedParameters = new MapSqlParameterSource(
		"name", student.getName());
	namedParameters.addValue("age", student.getAge());
	jdbcTemplate.update(sql, namedParameters);
    }

    /**
     * In this method, we use BeanPropertySqlParameterSource for placeholder
     * value mapping
     * 
     * @param student
     */
    public void createStudent2(Student student) {
	String sql = "insert into student(name, age) value(:name, :age)";
	NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate();

	SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(
		student);
	jdbcTemplate.update(sql, parameterSource);
    }

    /**
     * In this method, we use HashMap for placeholder value mapping
     * 
     * @param student
     */
    public void createStudent3(Student student) {
	String sql = "insert into student(name, age) value(:name, :age)";
	NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate();

	Map<String, Object> map = new HashMap<String, Object>();
	map.put("name", student.getName());
	map.put("age", student.getAge());

	jdbcTemplate.update(sql, map);
    }

    public Student retrieveStudent(int id) {
	String sql = "select id, name, age from student where id=:id";
	NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate();

	SqlParameterSource parameters = new MapSqlParameterSource("id", id);
	return jdbcTemplate.queryForObject(sql, parameters,
		new RowMapper<Student>() {

		    public Student mapRow(ResultSet rs, int rowNum)
			    throws SQLException {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			int age = rs.getInt("age");
			return new Student(id, name, age);
		    }

		});
    }

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

package edu.xmu.jdbc.dao;

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 NamedParameterJdbcTemplateDaoTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private NamedParameterJdbcTemplateDao dao;

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

	dao = new NamedParameterJdbcTemplateDao();
	dao.setDataSource(dataSource);
    }

    @Test
    public void createStudentTest() {
	Student student = new Student("Davy", 24);
	dao.createStudent(student);
	Student returnStudent = dao.retrieveStudent(1);
	System.out.println(returnStudent);
    }

    @Test
    public void createStudent2Test() {
	Student student = new Student("Davy", 24);
	dao.createStudent2(student);
	Student returnStudent = dao.retrieveStudent(1);
	System.out.println(returnStudent);
    }

    @Test
    public void createStudent3Test() {
	Student student = new Student("Davy", 24);
	dao.createStudent3(student);
	Student returnStudent = dao.retrieveStudent(1);
	System.out.println(returnStudent);
    }

    @After
    public void tearDown() {
	dao.clearTable();
    }
}

There are three approaches by which we can substitute placeholdes.

1> Use MapSqlParameterSource

2> Use BeanPropertySqlParameterSource

3> Use simple Map

4. SimpleJdbcTemplate --> Depreciated

1> Enables uncertain query parameters with the technology provided since Java 1.5.

     But this function has been added to JdbcTemplate as well.

2> Enables named placeholder, which is the main function of NamedParameterJdbcTemplate.

3> This class is now depreciated as all its function provided are also provided by other classes.

/**
 * @deprecated since Spring 3.1 in favor of {@link org.springframework.jdbc.core.JdbcTemplate} and
 * {@link org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate}. The JdbcTemplate and
 * NamedParameterJdbcTemplate now provide all the functionality of the SimpleJdbcTemplate.
 */
@Deprecated
public class SimpleJdbcTemplate implements SimpleJdbcOperations{
...
}

 

5. Retrieve auto-generated keys

1) When we use auto-generated policy in db primary key generation,

    after we execute CUD operation, we need to get the new ID.

    That would be redundancy if we execute a query after that.

2) Spring JDBC offers a class KeyHolder for this special purpose.

1> Method in JdbcTemplate

public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder);

2> Method in NamedParameterJdbcTemplate

public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder);
public int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames);

 

Example for JdbcTemplate:

package edu.xmu.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import edu.xmu.jdbc.bean.Student;

public class JdbcTemplateKey extends JdbcDaoSupport {

    public int createStudent(final Student student) {
	final String sql = "insert into student(name, age) values(?, ?)";

	JdbcTemplate jdbcTemplate = getJdbcTemplate();
	KeyHolder keyHolder = new GeneratedKeyHolder();

	jdbcTemplate.update(new PreparedStatementCreator() {
	    public PreparedStatement createPreparedStatement(Connection con)
		    throws SQLException {
		PreparedStatement ps = con.prepareStatement(sql,
			new String[] { "id" });
		ps.setString(1, student.getName());
		ps.setInt(2, student.getAge());
		return ps;
	    }
	}, keyHolder);

	return keyHolder.getKey().intValue();
    }
}

Example for NamedParameterJdbcTemplate

package edu.xmu.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import edu.xmu.jdbc.bean.Student;

public class NamedParameterJdbcTemplateKey extends NamedParameterJdbcDaoSupport {

    public int createStudent(final Student student) {
	final String sql = "insert into student(name, age) values(?, ?)";

	JdbcTemplate jdbcTemplate = getJdbcTemplate();
	KeyHolder keyHolder = new GeneratedKeyHolder();

	jdbcTemplate.update(new PreparedStatementCreator() {
	    public PreparedStatement createPreparedStatement(Connection con)
		    throws SQLException {
		PreparedStatement ps = con.prepareStatement(sql,
			new String[] { "id" });
		ps.setString(1, student.getName());
		ps.setInt(2, student.getAge());
		return ps;
	    }
	}, keyHolder);

	return keyHolder.getKey().intValue();
    }

    public int createStudent2(Student student) {
	String sql = "insert into student(name, age) values(:name, :age)";

	NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate();
	KeyHolder keyHolder = new GeneratedKeyHolder();

	SqlParameterSource paramSource = new BeanPropertySqlParameterSource(
		student);

	int rowCount = jdbcTemplate.update(sql, paramSource, keyHolder);
	System.out.println(rowCount + " row affected.");

	return keyHolder.getKey().intValue();
    }

    public int createStudent3(Student student) {
	String sql = "insert into student(name, age) values(:name, :age)";

	NamedParameterJdbcTemplate jdbcTemplate = getNamedParameterJdbcTemplate();
	KeyHolder keyHolder = new GeneratedKeyHolder();

	SqlParameterSource paramSource = new BeanPropertySqlParameterSource(
		student);

	int rowCount = jdbcTemplate.update(sql, paramSource, keyHolder,
		new String[] { "id" });

	System.out.println(rowCount + " row affected.");

	return keyHolder.getKey().intValue();
    }
}

package edu.xmu.jdbc.dao;

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 NamedParameterJdbcTemplateKeyTest {
    private DriverManagerDataSource dataSource;
    private String url = "jdbc:mysql://localhost:3306/jdbctest";
    private String username = "root";
    private String password = "root";

    private NamedParameterJdbcTemplateKey dao;

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

	dao = new NamedParameterJdbcTemplateKey();
	dao.setDataSource(dataSource);
    }

    @Test
    public void createStudentTest() {
	Student student = new Student("Davy", 24);
	int id = dao.createStudent(student);

	System.out.println("Generated id: " + id);
    }

    @Test
    public void createStudent2Test() {
	Student student = new Student("Davy", 24);
	int id = dao.createStudent2(student);

	System.out.println("Generated id: " + id);
    }

    @Test
    public void createStudent3Test() {
	Student student = new Student("Davy", 24);
	int id = dao.createStudent3(student);

	System.out.println("Generated id: " + id);
    }

    @After
    public void tearDown() {
    }
}