SpringMVC jdbcTemplate中queryForObject以及queryForList返回映射实体使用
使用SpringMVC搭建项目时,我打算直接使用SpringMVC的JDBC,如果引入Mybatis和Hibernate等ORM是感觉太过的麻烦,所以直接使用springframework.jdbc。SpringMVCJDBC提供了两个数据jdbc操作类,分别是:jdbcTemplate和namedParameterJdbcTemplate。他们都提供了非常多的方法,我就不写了(看源码)。现在问题来了,我想从数据库中返回映射到实体该如何办呢?
现在我有表user表,需返回UserInfo实体,以及List<UserInfo>时。
UserInfo实体如下:
package org.andy.shop.entity; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; public class UserInfo { private Integer id; private String uname; private Integer unumber; private Date uRegisterTime; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUname() { return uname; } public void setUname(String uname) { this.uname = uname == null ? null : uname.trim(); } public Integer getUnumber() { return unumber; } public void setUnumber(Integer unumber) { this.unumber = unumber; } public Date getuRegisterTime() { return uRegisterTime; } public void setuRegisterTime(Date uRegisterTime) { this.uRegisterTime = uRegisterTime; } }
这是我们可以有一下方法:
1、在UserInfo.java中添加一个Map转换为UserInfo的方法
在上面的UserInfo中添加转换的方法:
public static UserInfo toObject(Map<String, Object> 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 userInfo; } public static List<UserInfo> toObject(List<Map<String, Object>> lists){ List<UserInfo> userInfos = new ArrayList<UserInfo>(); for (Map<String, Object> map : lists) { UserInfo userInfo = UserInfo.toObject(map); if (userInfo != null) { userInfos.add(userInfo); } } return userInfos; }
然后调用JdbcTemplate的返回Map集合的如下方法:
public Map<String, Object> queryForMap(String sql, Object... args) throws DataAccessException
public List<Map<String, Object>> queryForList(String sql) throws DataAccessException
在调用上述的转换。
@Autowired private JdbcTemplate jdbcTemplate; @Autowired private NamedParameterJdbcTemplate namedParameterJdbcTemplate; @Override public UserInfo getById(Integer id) { String sql = "SELECT * FROM user_info WHERE id = ?"; Map<String, Object> map = jdbcTemplate.queryForMap(sql, new Object[]{1}); return UserInfo.toObject(map); } @Override public List<UserInfo> findAll() { String sql = "SELECT * FROM user_info"; List<Map<String, Object>> lists = jdbcTemplate.queryForList(sql); return UserInfo.toObject(lists); }
总结:这种方法能够实现,但是速度相比很慢。
2、 使用RowMapper实现接口方式
查看Spring JDBC的源码,我们会发先,还提供了如下的方法:
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException
public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException
这里面需要传过去一个返回实体的实现RowMapper的Mapper类。好吧,我们改造UserInfo,实现RowMapper接口,实现接口里的mapRow方法。
UserInfo实体修改如下:
package org.andy.shop.entity; import java.io.Serializable; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; import org.springframework.jdbc.core.RowMapper; /** * 实现数据表与字段的映射 * * @author andy * */ public class UserInfo implements RowMapper<UserInfo>, Serializable { /** * */ private static final long serialVersionUID = -8823504831198719837L; private Integer id; private String uname; private Integer unumber; private Date uRegisterTime; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUname() { return uname; } public void setUname(String uname) { this.uname = uname == null ? null : uname.trim(); } public Integer getUnumber() { return unumber; } public void setUnumber(Integer unumber) { this.unumber = unumber; } public Date getuRegisterTime() { return uRegisterTime; } public void setuRegisterTime(Date uRegisterTime) { this.uRegisterTime = uRegisterTime; } @Override 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 userInfo; } }
那么我们可以在Dao层如下的实现:
@Autowired private JdbcTemplate jdbcTemplate; @Autowired private NamedParameterJdbcTemplate namedParameterJdbcTemplate; @Override public UserInfo getById(Integer id) { String sql = "SELECT * FROM user_info WHERE id = ?"; UserInfo userInfo = jdbcTemplate.queryForObject(sql, new UserInfo(), new Object[] { id }); return userInfo; } @Override public List<UserInfo> findAll() { String sql = "SELECT * FROM user_info"; List<UserInfo> userInfos = jdbcTemplate.query(sql, new UserInfo()); return userInfos; }
这种方式相比上一种方法处理速度更快。
但是我们查看JDBC源码时,我们还看到了如下的方法
public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException
public <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException
那我们能否分别如下的调用呢?
UserInfo userInfo = jdbcTemplate.queryForObject(sql, UserInfo.class,
new Object[] { id });
List<UserInfo> 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源码,
public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException源码如下:
@Override public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException { return queryForObject(sql, args, getSingleColumnRowMapper(requiredType)); }
getSingleColumnRowMapper只是通过反射获取了UserInfo,并最终是要执行它里面的的映射方法,因为我们给UserInfo实现了RowMapper的方法。
这个最终调用了public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException方法。和为什么上面的能获取这个就报错呢,看源码后可以发现:
public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException 这种反射类型的是将每一个数据列返回为T类型,故出错,因此此方法只支持这种数据类型的(如String等等),不支持自己定义复杂类型的Bean实体。因此可以返回单列的(例如count(*) 和 某一列值等等)。
但是它在文档上未做任何的说明,害得我测试了半天,真是节操何在?
博客来源:http://blog.csdn.net/fengshizty?viewmode=list
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。