Spring JDBC: Introduction to JdbcTemplate (II)--API Usage
public Map<String, Object> queryForMap(String sql);public Map<String, Object> queryForMap(String sql, Object... args);public Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes);// The methods above are meant to be s single row query.
As we can see from API Doc:
1) Return type is Map<String, Object>. One entry for each column, using the column name as the key.
2) As we cannot have ?duplicate keys, this query is meant to be a single row query.
3) This methods is appropriate when you don't have a domain model,?
? ? Otherwise, consider using one of the queryForObject() methods.
4) If the row count that matches the sql is bigger than one, IncorrectResultSizeDataAccessException will be thrown.
package edu.xmu.jdbc.dao;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import edu.xmu.jdbc.bean.Student;public class JdbcTemplateDao { private DataSource dataSource; private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;this.jdbcTemplate = new JdbcTemplate(this.dataSource); } public void prepareData(List<Student> studentList) {String sql = "insert into student(name, age) values (?, ?)";for (Student student : studentList) { jdbcTemplate .update(sql, new Object[] { student.getName(), student.getAge() }, new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER });} } public Map<String, Object> queryForMap() {String sql = "select id, name, age from student";return jdbcTemplate.queryForMap(sql); } public Map<String, Object> queryForMap2(int id) {String sql = "select id, name, age from student where id=?";return jdbcTemplate.queryForMap(sql, new Object[] { id }); } public Map<String, Object> queryForMap3(int id) {String sql = "select id, name, age from student where id<=?";return jdbcTemplate.queryForMap(sql, new Object[] { id },new int[] { java.sql.Types.INTEGER }); } public void truncateTable() {String sql = "truncate table student";jdbcTemplate.execute(sql); }}package edu.xmu.jdbc.dao;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.Set;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 JdbcTemplateTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private JdbcTemplateDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new JdbcTemplateDao();dao.setDataSource(dataSource);List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);dao.prepareData(studentList); } @Test public void queryForMapTest() {Map<String, Object> resultMap = dao.queryForMap();Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet();for (Entry<String, Object> entryMap : entrySet) { System.out.println(entryMap.getKey() + "=" + entryMap.getValue());} } @Test public void queryForMap2Test() {Map<String, Object> resultMap = dao.queryForMap2(1);Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet();for (Entry<String, Object> entryMap : entrySet) { System.out.println(entryMap.getKey() + "=" + entryMap.getValue());} } @Test public void queryForMap3Test() {Map<String, Object> resultMap = dao.queryForMap3(2);Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet();for (Entry<String, Object> entryMap : entrySet) { System.out.println(entryMap.getKey() + "=" + entryMap.getValue());} } @After public void tearDown() {dao.truncateTable(); }}?2. queryForObject
public <T> T queryForObject(String sql, Class<T> requiredType);public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType);public <T> T queryForObject(String sql, Class<T> requiredType, Object... args);// The methods above are meant to be a single row and single column query.public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType);public <T> T queryForObject(String sql, RowMapper<T> rowMapper);public <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper);public <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper);//The methods above are meant to be a single row query.
As we can see from API Doc:
1) The methods without rowMapper params are meant to be a single row and single column query.
? ? Because the requiredType can only be java embedded type.
2) The methods with rowMapper params are meant to be a single row query.
package edu.xmu.jdbc.dao;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import edu.xmu.jdbc.bean.Student;public class QueryForObjectDao { private DataSource dataSource; private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;this.jdbcTemplate = new JdbcTemplate(this.dataSource); } public void prepareData(List<Student> studentList) {String sql = "insert into student(name, age) values (?, ?)";for (Student student : studentList) { jdbcTemplate .update(sql, new Object[] { student.getName(), student.getAge() }, new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER });} } public Student queryForObject() {String sql = "select id from student";int id = jdbcTemplate.queryForObject(sql, Integer.class);sql = "select name from student";String name = jdbcTemplate.queryForObject(sql, String.class);sql = "select age from student";int age = jdbcTemplate.queryForObject(sql, Integer.class);return new Student(id, name, age); } public Student queryForObject2(int id) {String sql = "select name from student where id=?";String name = jdbcTemplate.queryForObject(sql, new Object[] { id },String.class);sql = "select age from student where id=?";int age = jdbcTemplate.queryForObject(sql, new Object[] { id },Integer.class);return new Student(id, name, age); } public Student queryForObject3(int id) {String sql = "select name from student where id=?";String name = jdbcTemplate.queryForObject(sql, new Object[] { id },new int[] { java.sql.Types.INTEGER }, String.class);sql = "select age from student where id=?";int age = jdbcTemplate.queryForObject(sql, new Object[] { id },new int[] { java.sql.Types.INTEGER }, Integer.class);return new Student(id, name, age); } /** * This sql is meant to be a single row result set. <br/> * If result set size is bigger than 1, * IncorrectResultSizeDataAccessException will be thrown. * * @return */ public Student queryForObject4() {String sql = "select id, name, age from student";Student student = jdbcTemplate.queryForObject(sql,new RowMapper<Student>() { public Student mapRow(ResultSet rs, int rowNum) throws SQLException {// 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 student; } public Student queryForObject5(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 {int id = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);return new Student(id, name, age); }});return student; } public Student queryForObject6(int id) {String sql = "select id, name, age from student where id=?";Student student = jdbcTemplate.queryForObject(sql, new Object[] { id },new int[] { java.sql.Types.INTEGER }, new RowMapper<Student>() { public Student mapRow(ResultSet rs, int rowNum) throws SQLException {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 truncateTable() {String sql = "truncate table student";jdbcTemplate.execute(sql); }}package edu.xmu.jdbc.dao;import java.util.ArrayList;import java.util.List;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 QueryForObjectTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private QueryForObjectDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new QueryForObjectDao();dao.setDataSource(dataSource);List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);dao.prepareData(studentList); } @Test public void queryForObjectTest() {Student student = dao.queryForObject();System.out.println(student); } @Test public void queryForObject2Test() {Student student = dao.queryForObject2(1);System.out.println(student); } @Test public void queryForObject3Test() {Student student = dao.queryForObject3(1);System.out.println(student); } @Test public void queryForObject4Test() {Student student = dao.queryForObject4();System.out.println(student); } @Test public void queryForObject5Test() {Student student = dao.queryForObject5(1);System.out.println(student); } @Test public void queryForObject6Test() {Student student = dao.queryForObject6(1);System.out.println(student); } @After public void tearDown() {dao.truncateTable(); }}?
3. queryForList
// Return a list of Map, with the key of column name, and value of column value.// The methods below are especially userful when we don't have a domain model.public List<Map<String, Object>> queryForList(String sql);public List<Map<String, Object>> queryForList(String sql, Object... args);public List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes);// Return a list of Object, with the type of elementType.// This method is meant to be a single column query.// And the elementType can be only java embedded type.public <T> List<T> queryForList(String sql, Class<T> elementType);public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType);public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args);public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType);// Although this method is not queryForList, but it is meant for quering for a // list of customized element.public <T> List<T> query(String sql, RowMapper<T> rowMapper);
As we can see from API Doc:
1) If we want to return a list of primary element, we can use queryForList.
? ? But if we need return a list of customized element, we can use query(sql, rowMapper) instead.
2)?The first segment of methods are appropriate when you don't have a domain model.
? ? They can be seen as an enhancement for queryForMap().
package edu.xmu.jdbc.dao;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import edu.xmu.jdbc.bean.Student;public class QueryForListDao { private DataSource dataSource; private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;this.jdbcTemplate = new JdbcTemplate(this.dataSource); } public void prepareData(List<Student> studentList) {String sql = "insert into student(name, age) values (?, ?)";for (Student student : studentList) { jdbcTemplate .update(sql, new Object[] { student.getName(), student.getAge() }, new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER });} } public List<Map<String, Object>> queryForList(int id) {String sql = "select id, name, age from student where id<=?";List<Map<String, Object>> returnList = jdbcTemplate.queryForList(sql,new Object[] { id });return returnList; } public List<String> queryForList2(int id) {String sql = "select name from student where id<=?";List<String> returnList = jdbcTemplate.queryForList(sql,new Object[] { id }, String.class);return returnList; } /** * Although query is not query for list, <br/> * here it performs just like queryForList. <br/> * And we use queryForList can never get a list of customized bean. * * @param id * @return */ public List<Student> query(int id) {String sql = "select id, name, age from student where id<=?";List<Student> returnList = jdbcTemplate.query(sql, new Object[] { id },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); }});return returnList; } public void truncateTable() {String sql = "truncate table student";jdbcTemplate.execute(sql); }}package edu.xmu.jdbc.dao;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.Set;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 QueryForListTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private QueryForListDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new QueryForListDao();dao.setDataSource(dataSource);List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);dao.prepareData(studentList); } @Test public void queryForListTest() {List<Map<String, Object>> resultList = dao.queryForList(2);for (Map<String, Object> resultMap : resultList) { Set<Map.Entry<String, Object>> entrySet = resultMap.entrySet(); for (Map.Entry<String, Object> entry : entrySet) {System.out.println(entry.getKey() + " = " + entry.getValue()); }} } @Test public void queryForList2Test() {List<String> nameList = dao.queryForList2(2);for (String name : nameList) { System.out.println("name = " + name);} } @Test public void queryForList3Test() {List<Student> studentList = dao.query(2);for (Student student : studentList) { System.out.println(student);} } @After public void tearDown() {dao.truncateTable(); }}?
4. query
// ResultSetExtractorpublic <T> T query(final String sql, final ResultSetExtractor<T> rse);public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse);public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse);// RowMapperpublic <T> List<T> query(String sql, RowMapper<T> rowMapper)public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper);public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper);// RowCallBackHandlerpublic void query(String sql, RowCallbackHandler rch);public void query(String sql, Object[] args, RowCallbackHandler rch);public void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch);
We need to know the different representation of ResultSetExtractor & RowMapper & RowCallBackHandler
1> Example for ResultSetExtractor
2> Example for RowMapper
3> Example for RowCallbackHandler
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.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 QueryDao { private DataSource dataSource; private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;this.jdbcTemplate = new JdbcTemplate(this.dataSource); } public void prepareData(List<Student> studentList) {String sql = "insert into student(name, age) values (?, ?)";for (Student student : studentList) { jdbcTemplate .update(sql, new Object[] { student.getName(), student.getAge() }, new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER });} } /** * If we want to get a Map<Integer, Student> <br/> * Key: StudentId, as it is identical <br/> * Value: Student <br/> * * The problem is that if we use query(), <br/> * we will get List<Map<Integer, Student>> <br/> * Then we have to convert this structure to single map. <br/> * * With ResultSetExtractor, we can avoid convertion <br/> * * @param id */ public Map<Integer, Student> queryWithResultSetExtractor(int id) {String sql = "select id, name, age from student where id <= ?";return jdbcTemplate.query(sql, new Object[] { id },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; }}); } /** * Here we user RowMapper as a comparator with previous one. <br/> * We are sure that every map inside the list have only one entry. <br/> * * @param id * @return */ public List<Map<Integer, Student>> queryWithRowMapper(int id) {String sql = "select id, name, age from student where id<=?";return jdbcTemplate.query(sql, new Object[] { id },new RowMapper<Map<Integer, Student>>() { public Map<Integer, Student> mapRow(ResultSet rs, int rowNum) throws SQLException {Map<Integer, Student> map = new HashMap<Integer, Student>();int id = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);Student student = new Student(id, name, age);map.put(id, student);return map; }}); } public void truncateTable() {String sql = "truncate table student";jdbcTemplate.execute(sql); }}package edu.xmu.jdbc.dao;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.Set;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 QueryDaoTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private QueryDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new QueryDao();dao.setDataSource(dataSource);List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);dao.prepareData(studentList); } @Test public void queryWithResultSetExtractorTest() {Map<Integer, Student> studentMap = dao.queryWithResultSetExtractor(2);Set<Entry<Integer, Student>> entrySet = studentMap.entrySet();for (Entry<Integer, Student> entry : entrySet) { int id = entry.getKey(); Student student = entry.getValue(); System.out.println("ID: " + id + ", Student: " + student);} } @Test public void queryWithRowMapperTest() {List<Map<Integer, Student>> list = dao.queryWithRowMapper(2);for (Map<Integer, Student> map : list) { Set<Entry<Integer, Student>> set = map.entrySet(); for (Entry<Integer, Student> entry : set) {int id = entry.getKey();Student student = entry.getValue();System.out.println("ID: " + id + ", Student: " + student); }} } @After public void tearDown() {dao.truncateTable(); }}?
5. update
public int update(final String sql);public int update(String sql, Object... args);public int update(String sql, Object[] args, int[] argTypes);
1) Update can be used for CUD operation.
2) Return int represents?the number of rows affected
package edu.xmu.jdbc.dao;import java.util.List;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;import edu.xmu.jdbc.bean.Student;public class UpdateDao { private DataSource dataSource; private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;this.jdbcTemplate = new JdbcTemplate(this.dataSource); } public void prepareData(List<Student> studentList) {String sql = "insert into student(name, age) values (?, ?)";for (Student student : studentList) { jdbcTemplate .update(sql, new Object[] { student.getName(), student.getAge() }, new int[] { java.sql.Types.VARCHAR, java.sql.Types.INTEGER });} } public void updateForUpdate(int id, Student student) {String name = student.getName();int age = student.getAge();String sql = "update student set name=?, age=? where id=?";jdbcTemplate.update(sql, new Object[] { name, age, id }); } public void updateForDelete(int id) {String sql = "delete from student where id=?";jdbcTemplate.update(sql, new Object[] { id }); } public void truncateTable() {String sql = "truncate table student";jdbcTemplate.execute(sql); }}package edu.xmu.jdbc.dao;import java.util.ArrayList;import java.util.List;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 UpdateDaoTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private UpdateDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new UpdateDao();dao.setDataSource(dataSource);List<Student> studentList = new ArrayList<Student>();Student student = new Student("Davy", 24);studentList.add(student);student = new Student("Jones", 25);studentList.add(student);dao.prepareData(studentList); } @Test public void updateForUpdateTest() {int id = 1;String updatedName = "Caly";int updatedAge = 22;Student student = new Student(updatedName, updatedAge);dao.updateForUpdate(id, student); } @Test public void updateForDeleteTest() {dao.updateForDelete(2); } @After public void tearDown() {dao.truncateTable(); }}?
6. Depreciated methods
@Deprecatedpublic int queryForInt(String sql);@Deprecatedpublic int queryForInt(String sql, Object... args);@Deprecatedpublic int queryForInt(String sql, Object[] args, int[] argTypes)@Deprecatedpublic long queryForLong(String sql);@Deprecatedpublic long queryForLong(String sql, Object... args);@Deprecatedpublic long queryForLong(String sql, Object[] args, int[] argTypes)
We can find the reason why they are depreciated by following url listed below.
?
7. DDL Execution
// This method is often used for DDL statementspublic void execute(final String sql);
As we can infer from the official document that as convention, we use execute(String) method to execute ddl.
But executing DDL programatically is depreciated.
It is a good practice to only execute CRUD in program and left DDL to be executed by DBA.
package edu.xmu.jdbc.dao;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;public class DDLDao { private DataSource dataSource; private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;this.jdbcTemplate = new JdbcTemplate(this.dataSource); } public void createTable() {String sql = "create table student_bak (id int primary key auto_increment, name varchar(100), age int)";jdbcTemplate.execute(sql); } public void deleteTable() {String sql = "drop table student_bak";jdbcTemplate.execute(sql); } public void alterTable() {String sql = "alter table student_bak add score decimal, add gender varchar(100)";jdbcTemplate.execute(sql); }}package edu.xmu.jdbc.dao;import org.junit.Before;import org.junit.Ignore;import org.junit.Test;import org.springframework.jdbc.datasource.DriverManagerDataSource;public class DDLDaoTest { private DriverManagerDataSource dataSource; private String url = "jdbc:mysql://localhost:3306/jdbctest"; private String username = "root"; private String password = "root"; private DDLDao dao; @Before public void setUp() {dataSource = new DriverManagerDataSource(url, username, password);dataSource.setDriverClassName("com.mysql.jdbc.Driver");dao = new DDLDao();dao.setDataSource(dataSource); } @Test public void createTableTest() {dao.createTable(); } @Test @Ignore public void deleteTableTest() {dao.deleteTable(); } @Test public void alterTableTest() {dao.alterTable(); }}?
?
Reference Links:
1)?http://stackoverflow.com/questions/15661313/jdbctemplate-queryforint-long-is-deprecated-in-spring-3-2-2-what-should-it-be-r?Why queryForInt/Long are depreciated?
2)?http://docs.spring.io/spring/docs/3.1.x/spring-framework-reference/html/jdbc.html?Spring JDBC Template official document.