1、org.springframework.jdbc.core.ResultSetExtractor.&
　　基本上属于JdbcTemplate内部使用的Callback接口，相对于下面两个Callback接口来说，ResultSetExtractor拥有更多的控制权，因为使用它，你需要自行处理ResultSet：
package org.springframework.jdbc.
import java.sql.ResultS
import java.sql.SQLE
import org.springframework.dao.DataAccessE
public interface ResultSetExtractor {
Object extractData(ResultSet rs) throws SQLException, DataAccessE
在直接处理完ResultSet之后，你可以将处理后的结果以任何你想要的形式包装后返回。&
2、org.springframework.jdbc.core.RowCallbackHandler.&
　　RowCallbackHandler相对于ResultSetExtractor来说，仅仅关注单行结果的处理，处理后的结果可以根据需要存放到当前RowCallbackHandler对象内或者使用JdbcTemplate的程序上下文中，当然，这个完全是看个人爱好了。
　　RowCallbackHandler的定义如下：&
package org.springframework.jdbc.
import java.sql.ResultS
import java.sql.SQLE
public interface RowCallbackHandler {
void processRow(ResultSet rs) throws SQLE
3、org.springframework.jdbc.core.RowMapper.
　　它是&ResultSetExtractor的精简版，功能类似于&RowCallbackHandler，也是只关注当行结果的处理。不过它的返回的结果会有&ResultSetExtractor实现类进行组合。
　　RowMapper的接口定义如下：
package org.springframework.jdbc.
import java.sql.ResultS
import java.sql.SQLE
public interface RowMapper {
Object mapRow(ResultSet rs, int rowNum) throws SQLE
为了说明这三种回调接口的使用方法，我们暂时设置如下的场景： 假设我们有一表users,里面有userid,username,userpwd三个字段，我们为此建立了一个JavaBean:
package com.google.spring.
public class UserBean{
private Integer userId;
public Integer getUserId(){
return userId;
public void setUserId(Integer userId){
this.userId = userId;
public String getUsername(){
public void setUsername(String username){
this.username =
public String getUserpwd(){
public void setUserpwd(String userpwd){
this.userpwd =
使用自定义的ResultSetExtractor，可以如下进行处理：
List users = (List)jdbcTemplate.query("SELECT * FROM USERS WHERE USERNAME LIKE '%n%'", new ResultSetExtractor()
public Object extractData(ResultSet rs) throws SQLException,
DataAccessException
List users = new ArrayList();
while(rs.next())
UserBean userBean = new UserBean();
userBean.setUserId(rs.getInt("userId"));
userBean.setUsername(rs.getString("username"));
userBean.setUserpwd(rs.getString("userpwd"));
users.add(userBean);
System.out.println(users);
使用RowCallbackHandler可进行如下的处理：
final List users = new ArrayList();
jdbcTemplate.query("SELECT * FROM USERS WHERE USERNAME LIKE '%n%'", new RowCallbackHandler()
public void processRow(ResultSet rs) throws SQLException
UserBean userBean = new UserBean();
userBean.setUserId(rs.getInt("userId"));
userBean.setUsername(rs.getString("username"));
userBean.setUserpwd(rs.getString("userpwd"));
users.add(userBean);
System.out.println(users.size());
使用RowMapper，可进行如下的处理：
List users = jdbcTemplate.query("SELECT * FROM USERS WHERE USERNAME LIKE '%n%'", new RowMapper()
public Object mapRow(ResultSet rs, int rowNum) throws SQLException
UserBean userBean = new UserBean();
userBean.setUserId(rs.getInt("userId"));
userBean.setUsername(rs.getString("username"));
userBean.setUserpwd(rs.getString("userpwd"));
return userB
System.out.println(users.size());
以上是以jdbcTemplate为例，介绍了3种回调接口的用法，其实还可以扩展到hbaseTemplate上
阅读(...) 评论()SpringMVC jdbcTemplate中queryForObject以及queryForList返回映射实体使用
使用SpringMVC搭建项目时，我打算直接使用SpringMVC的JDBC，如果引入Mybatis和Hibernate等ORM是感觉太过的麻烦，所以直接使用springframework.jdbc。SpringMVCJDBC提供了两个数据jdbc操作类，分别是：jdbcTemplate和namedParameterJdbcTemplate。他们都提供了非常多的方法，我就不写了（看）。现在问题来了，我想从中返回映射到实体该如何办呢？
现在我有表user表，需返回UserInfo实体，以及List时。
UserInfo实体如下：
package org.andy.shop.
import java.sql.ResultS
import java.sql.SQLE
import java.util.D
public class UserInfo {
private Date uRegisterT
public Integer getId() {
public void setId(Integer id) {
public String getUname() {
public void setUname(String uname) {
this.uname = uname == null ? null : uname.trim();
public Integer getUnumber() {
public void setUnumber(Integer unumber) {
this.unumber =
public Date getuRegisterTime() {
return uRegisterT
public void setuRegisterTime(Date uRegisterTime) {
this.uRegisterTime = uRegisterT
这是我们可以有一下方法：
1、在UserInfo.java中添加一个Map转换为UserInfo的方法
在上面的UserInfo中添加转换的方法：
public static UserInfo toObject(Map map) {
UserInfo userInfo = new UserInfo();
userInfo.setId((Integer) map.get(id));
userInfo.setUname((String) map.get(uname));
userInfo.setUnumber((Integer) map.get(unumber));
userInfo.setuRegisterTime((Date) map.get(uregister_time));
return userI
public static List toObject(List<map& lists){
List userInfos = new ArrayList();
for (Map map : lists) {
UserInfo userInfo =
UserInfo.toObject(map);
if (userInfo != null) {
userInfos.add(userInfo);
return userI
然后调用JdbcTemplate的返回Map集合的如下方法：
public Map queryForMap(String sql, Object... args) throws DataAccessException
public List<map& queryForList(String sql) throws DataAccessException</map
在调用上述的转换。
@Autowired
private JdbcTemplate jdbcT
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcT
public UserInfo getById(Integer id) {
String sql = SELECT * FROM user_info WHERE id = ?;
Map map = jdbcTemplate.queryForMap(sql, new Object[]{1});
return UserInfo.toObject(map);
public List findAll() {
String sql = SELECT * FROM user_
List<map& lists = jdbcTemplate.queryForList(sql);
return UserInfo.toObject(lists);
总结：这种方法能够实现，但是速度相比很慢。
2、 使用RowMapper实现接口方式
查看Spring JDBC的源码，我们会发先，还提供了如下的方法：
T queryForObject(String sql, RowMapper rowMapper, Object... args) throws DataAccessException
List query(String sql, RowMapper rowMapper) throws DataAccessException
这里面需要传过去一个返回实体的实现RowMapper的Mapper类。好吧，我们改造UserInfo，实现RowMapper接口，实现接口里的mapRow方法。
UserInfo实体修改如下：
package org.andy.shop.
import java.io.S
import java.sql.ResultS
import java.sql.SQLE
import java.util.D
import org.springframework.jdbc.core.RowM
* 实现数据表与字段的映射
* @author andy
public class UserInfo implements RowMapper, Serializable {
private static final long serialVersionUID = -8719837L;
private Date uRegisterT
public Integer getId() {
public void setId(Integer id) {
public String getUname() {
public void setUname(String uname) {
this.uname = uname == null ? null : uname.trim();
public Integer getUnumber() {
public void setUnumber(Integer unumber) {
this.unumber =
public Date getuRegisterTime() {
return uRegisterT
public void setuRegisterTime(Date uRegisterTime) {
this.uRegisterTime = uRegisterT
public UserInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
UserInfo userInfo = new UserInfo();
userInfo.setId(rs.getInt(id));
userInfo.setUname(rs.getString(uname));
userInfo.setUnumber(rs.getInt(unumber));
userInfo.setuRegisterTime(rs.getDate(uregister_time));
return userI
那么我们可以在Dao层如下的实现：
@Autowired
private JdbcTemplate jdbcT
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcT
public UserInfo getById(Integer id) {
String sql = SELECT * FROM user_info WHERE id = ?;
UserInfo userInfo = jdbcTemplate.queryForObject(sql, new UserInfo(),
new Object[] { id });
return userI
public List findAll() {
String sql = SELECT * FROM user_
List userInfos = jdbcTemplate.query(sql, new UserInfo());
return userI
这种方式相比上一种方法处理速度更快。
但是我们查看JDBC源码时，我们还看到了如下的方法
T queryForObject(String sql, Class requiredType, Object... args) throws DataAccessException
List queryForList(String sql, Class elementType) throws DataAccessException
那我们能否分别如下的调用呢？
UserInfo userInfo = jdbcTemplate.queryForObject(sql, UserInfo.class,
new Object[] { id });
List userInfos = jdbcTemplate.queryForList(sql, UserInfo.class);
执行JUnit测试，结果如下：
org.springframework.jdbc.IncorrectResultSetColumnCountException: Incorrect column count: expected 1, actual 4
at org.springframework.jdbc.core.SingleColumnRowMapper.mapRow(SingleColumnRowMapper.java:88)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:460)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:471)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:481)
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:516)
at org.andy.shop.dao.Impl.UserInfoDaoImpl.findAll(UserInfoDaoImpl.java:43)
at org.andy.shop.service.Impl.UserInfoServiceImpl.findAll(UserInfoServiceImpl.java:31)
at org.andy.shop.test.service.TestUserInfoService.testFindAll(TestUserInfoService.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:217)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
意思是返回结果期望是1，结果是4列。（数据库中是一行数据，四个列）
这是为什么呢？我们继续查看JDBC源码，
T queryForObject(String sql, Class requiredType, Object... args) throws DataAccessException源码如下：
T queryForObject(String sql, Class requiredType, Object... args) throws DataAccessException {
return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
getSingleColumnRowMapper只是通过反射获取了UserInfo，并最终是要执行它里面的的映射方法，因为我们给UserInfo实现了RowMapper的方法。
这个最终调用了public
T queryForObject(String sql, RowMapper rowMapper, Object... args) throws DataAccessException方法。和为什么上面的能获取这个就报错呢，看源码后可以发现：
T queryForObject(String sql, Class requiredType, Object... args) throws DataAccessException 这种反射类型的是将每一个数据列返回为T类型，故出错，因此此方法只支持这种数据类型的（如String等等），不支持自己定义复杂类型的Bean实体。因此可以返回单列的（例如count(*) 和 某一列值等等）。
但是它在文档上未做任何的说明，害得我测试了半天，真是节操何在？
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'RowMapper (Spring Framework 4.3.0.RELEASE API)
JavaScript is disabled on your browser.
Spring Framework
Interface RowMapper&T&
All Known Implementing Classes:
public interface RowMapper&T&
An interface used by
for mapping rows of a
on a per-row basis. Implementations of this
interface perform the actual work of mapping each row to a result object,
but don't need to worry about exception handling.
will be caught and handled
by the calling JdbcTemplate.
Typically used either for 's query methods
or for out parameters of stored procedures. RowMapper objects are
typically statele they are an ideal choice for
implementing row-mapping logic in a single place.
Alternatively, consider subclassing
jdbc.object package: Instead of working with separate
JdbcTemplate and RowMapper objects, you can build executable query
objects (containing row-mapping logic) in that style.
Thomas Risberg, Juergen Hoeller
Method Summary
All Methods&&&
Modifier and Type
Method and Description
int&rowNum)
Implementations must implement this method to map each row of data
in the ResultSet.
Method Detail
&mapRow(&rs,
int&rowNum)
Implementations must implement this method to map each row of data
in the ResultSet. This method should not call next() on
the ResultS it is only supposed to map values of the current row.
Parameters:
rs - the ResultSet to map (pre-initialized for the current row)
rowNum - the number of the current row
the result object for the current row
- if a SQLException is encountered getting
column values (that is, there's no need to catch SQLException)
Spring FrameworkSpring-JDBC 操作SQL数据库
Spring-JDBC 操作SQL数据库1.关于org.springframework.jdbcJava操作关系数据库，例如MySQL，可以使用数据库对应的JDBC驱动程序(如：mysql-connector-java)提供的的方法。但由于JDBC驱动提供的API使用起来每次都要编写连接、操作、关闭数据库和异常处理的模板（Template）代码，代码显得非常冗长重复，不利于集中精力处理实际问题。org.springframework.jdbc包中提供了经典类JdbcTemplate来简化数据库操作，它实现了JdbcOperations接口。其实现是基于callback（回调）来实现的。并且能把JDBC的SQLException转换为更为容易理解的springframework包org.springframework.dao中定义的异常类型。NamedParameterJdbcTemplate包装了JdbcTemplate，提供JdbcTemplate的所有功能，由于增加了命名参数，便于编写和修改sql，使用更为方便。一般都是在xml中定义template的bean然后直接在dao中注入来使用。在spring的application-context.xml配置文件中配置相关beans:&context:property-placeholder location="jdbc.properties"/&&bean id="dataSource" class="mons.dbcp.BasicDataSource" destroy-method="close"&
&property name="driverClassName" value="${jdbc.driverClassName}"/&
&property name="url" value="${jdbc.url}"/&
&property name="username" value="${jdbc.username}"/&
&property name="password" value="${jdbc.password}"/&&/bean&&bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"&
&property name="dataSource" ref="dataSource" /&
&/bean&使用注解注入到DAO的实现类中：@Resourceprivate NamedParameterJdbcTemplate namedJdbcT或者使用代码直接创建相关beans:MysqlDataSource dataSource = new MysqlDataSource();dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8");dataSource.setPassword("123");dataSource.setUser("mysql");NamedParameterJdbcTemplate
namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);2. NamedParameterJdbcTemplate的接口分析现在看看template提供的方法：excute &T& T execute(String sql, SqlParameterSource paramSource, PreparedStatementCallback&T& action)&T& T execute(String sql, Map&String, ?& paramMap, PreparedStatementCallback&T& action)&T& T execute(String sql, PreparedStatementCallback&T& action)query&T& T query(String sql, SqlParameterSource paramSource, ResultSetExtractor&T& rse)&T& T query(String sql, Map&String, ?& paramMap, ResultSetExtractor&T& rse)&T& T query(String sql, ResultSetExtractor&T& rse)void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch)void query(String sql, Map&String, ?& paramMap, RowCallbackHandler rch)void query(String sql, RowCallbackHandler rch)&T& List&T& query(String sql, SqlParameterSource paramSource, RowMapper&T& rowMapper)&T& List&T& query(String sql, Map&String, ?& paramMap, RowMapper&T& rowMapper&T& List&T& query(String sql, RowMapper&T& rowMapper)&T& T queryForObject(String sql, SqlParameterSource paramSource, RowMapper&T& rowMapper)&T& T queryForObject(String sql, Map&String, ?& paramMap, RowMapper&T&rowMapper)&T& T queryForObject(String sql, SqlParameterSource paramSource, Class&T& requiredType)&T& T queryForObject(String sql, Map&String, ?& paramMap, Class&T& requiredType)Map&String, Object& queryForMap(String sql, SqlParameterSource paramSource)Map&String, Object& queryForMap(String sql, Map&String, ?& paramMap)&T& List&T& queryForList(String sql, SqlParameterSource paramSource, Class&T& elementType)&T& List&T& queryForList(String sql, Map&String, ?& paramMap, Class&T& elementType)List&Map&String, Object&& queryForList(String sql, SqlParameterSource paramSource)List&Map&String, Object&& queryForList(String sql, Map&String, ?& paramMap)SqlRowSet queryForRowSet(String sql, SqlParameterSource paramSource)SqlRowSet queryForRowSet(String sql, Map&String, ?& paramMap)updateint update(String sql, SqlParameterSource paramSource)int update(String sql, Map&String, ?& paramMap)int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder)int update(
String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder, String[] keyColumnNames)int[] batchUpdate(String sql, Map&String, ?&[] batchValues)int[] batchUpdate(String sql, SqlParameterSource[] batchArgs)查看接口参数很容易发现，所有方法的参数都包含sql，大部分包含param、callback:
类型和名称
String sql
SqlParameterSource paramSource、Map&String, ?& paramMap
SQL语句中的参数
PreparedStatementCallback&T& action,ResultSetExtractor&T& rse,RowCallbackHandler rch,RowMapper&T& rowMapper,
用于对一行数据进行映射
elementType
Class&T& requiredType
返回单列数据时，指定数据类型
generatedKeyHolder
KeyHolder generatedKeyHolder
返回生成的key其中，SqlParameterSource 的实现类有：MapSqlParameterSource：通过Map构造。BeanPropertySqlParameterSource：通过Bean的class构造。RowMapper的实现类：BeanPropertyRowMapper：直接从Bean的class生成mapper。ColumnMapRowMapper：直接把ResultSet映射为Map.最后看一下返回值：void: 没有返回值。T: query时返回一行数据，excute时T可以是集合类型。List&?&, SqlRowSet: 返回多行数据。 int, int[]: 返回执行一条SQL受影响的行数。3. 使用示例使用MySQL,先创建表：CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
`age` int(10) unsigned DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`area_id` bigint(20) DEFAULT NULL,
`mobile` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;创建对应的PO(使用lombok简化setter,getter)：@Datapublic class User {
private Date createT
private Long areaId;
private S}3.1 excute使用示例excute-1,excute-2,excute-3: 注意返回值T可以是集合哦。String sql = "select * from user where id = :id";Map&String, Object& params = Maps.newHashMap();params.put("id", 1L);User user1 = namedJdbcTemplate.execute(sql, new MapSqlParameterSource(params),
new PreparedStatementCallback&User&() {
public User doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
ResultSet rs = ps.executeQuery();
rs.next();
return buildUser(rs);
}});User user2 = namedJdbcTemplate.execute(sql, params, new PreparedStatementCallback&User&() {
public User doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
ResultSet rs = ps.executeQuery();
rs.next();
return buildUser(rs);
}});List&User& users = namedJdbcTemplate.execute("select * from user", new PreparedStatementCallback&List&User&&() {
public List&User& doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
ResultSet rs = ps.executeQuery();
List&User& users = Lists.newArrayList();
while (rs.next()) {
users.add(buildUser(rs));
}});如果excute不需要任何参数，也不返回值，怎么办呢？ 可以返回namedJdbcTemplate内部的jdbcTemplate，它有相应的方法：
String sql = "create table mytable (id integer, name varchar(100))";
namedJdbcTemplate.getJdbcOperations().execute(sql);3.2 query使用示例Sql语句和参数：String sql = "select * from user";query-1~3：用于返回整个结果集，在excute上简单封装。1,2和3只有参数的区别，只举例3：List&User& list = namedJdbcTemplate.query(sql, new ResultSetExtractor&List&User&&() {
public List&User& extractData(ResultSet rs) throws SQLException, DataAccessException {
List&User& users = Lists.newArrayList();
while (rs.next()) {
users.add(buildUser(rs));
}});query-4~6类似,都是用RowCallbackHandler，只用处理一行即可。举例6: List&User& list =
namedParameterJdbcTemplate.query(sql, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
users.add(buildUser(rs));
});query-7~9类似,都是用RowMapper，只用处理一行即可,注意：可以获取到rowNum。举例9: List&User& list =
namedJdbcTemplate.query(sql, new RowMapper&User&() {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
return buildUser(rs);
});query-10~15，作用一样，都是返回一行数据，只是参数和返回值类型不一样而已，注意11，12的requiredType参数， 当使用11，12时，sql只能返回简单的一列数据,例如(select count(*) from user）,否则报错,查看源码其内部是调用query-10实现，并且使用的是SingleColumnRowMapper,只支持一列数据。 query-10:使用BeanPropertySqlParameterSource, BeanPropertyRowMapper可极大简化编码。
两者都是用了反射实现映射。BeanPropertyRowMapper支持直接映射或者驼峰(camel),即: sql语句中的name直接映射到User类中的name, area_id映射到areaId。
BeanPropertySqlParameterSource则要求User类中的参数和sql中的冒号后的参数一致：areaId 对应 :areaId。User user = new User();user.setId(1L);List&User& listUser =namedParameterJdbcTemplate.query("select * from user where id = :id",
new BeanPropertySqlParameterSource(user4), new BeanPropertyRowMapper&User&(User.class)
);query-11: 只能返回基本数据。try {
List&User& users10 =
namedParameterJdbcTemplate.queryForList(sql, new BeanPropertySqlParameterSource(user4), User.class);} catch (org.springframework.jdbc.IncorrectResultSetColumnCountException e) {
e.printStackTrace();
// 注意此处会发生异常,只允许返回一列数据，所以只能用来返回基本数据，比如Integer,String,Long等的List，见SingleColumnRowMapper}// 只能用来返回基本数据Integer count2 =
namedParameterJdbcTemplate.queryForObject("select count(1) from user where id = :id", paramMap,
Integer.class);List&Long& ids =
namedParameterJdbcTemplate.queryForList(sql, new BeanPropertySqlParameterSource(user4), Long.class);// 由此可见，queryForList只能用来返回基本数据哦update-1~2比较简单，传入相应参数和sql即可返回影响的数据行数。略过。用于UPDATE,INSERT语句。 INSERT时如果设定了自动生成主键ID,update-3~4可以返回ID值。update-3用于一个自动生成ID，update-4可以用于多个，需要指定key的名称。update-3:String sql = "insert into user (name,age,area_id,mobile) values (:name,:age,:areaId,:mobile)";User user = new User();user.setAge(11);user.setAreaId(110L);user.setMobile("");user.setName("cookies");GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();// 获取自动生成的idint count =
namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(user), generatedKeyHolder);logger.debug("count = {}, key = {}", count, generatedKeyHolder.getKey());update-5~6: batch update，批量操作，两个方法类似:String sql = "insert into user (name,age,area_id,mobile) values (:name,:age,:areaId,:mobile)";@SuppressWarnings("rawtypes")Map[] batchValues = new HashMap[10];for (int i = 0; i & batchValues. i++) {
Map v = new HashMap();
batchValues[i] =
batchValues[i].put("name", "哈哈" + i);
batchValues[i].put("age", 11 + i);
batchValues[i].put("areaId", 123 + i);
batchValues[i].put("mobile", "" + i);}// update-4:int[] c1 = namedParameterJdbcTemplate.batchUpdate(sql, batchValues);logger.debug("counts = {}", counts);// update-5:SqlParameterSource[] batchArgs = new SqlParameterSource[10];for (int i = 0; i & batchA i++) {
User user = new User();
user.setAge(11 + i);
user.setAreaId(110L + i);
user.setMobile("" + i);
user.setName("cookies" + i);
batchArgs[i] = new BeanPropertySqlParameterSource(user);}int[] c2 = namedParameterJdbcTemplate.batchUpdate(sql, batchArgs);logger.debug("counts = {}", c2);参考1.Data access with JDBC
最新教程周点击榜
微信扫一扫