使用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