分類  >  資料庫 >

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

tags:    時間:2013-12-23 21:49:09
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() {     } }

 

推薦閱讀文章

Bookmark the permalink ,來源:互聯網