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() {
}
}