Spring JDBC: Introduction to JdbcTemplate (II)--API Usage
1. queryForMap
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
// ResultSetExtractor public <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); // RowMapper public <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); // RowCallBackHandler public 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
@Deprecated public int queryForInt(String sql); @Deprecated public int queryForInt(String sql, Object... args); @Deprecated public int queryForInt(String sql, Object[] args, int[] argTypes) @Deprecated public long queryForLong(String sql); @Deprecated public long queryForLong(String sql, Object... args); @Deprecated public 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 statements public 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://*.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.