QueryForObject and queryForList returned ing objects in spring MVC jdbcTemplate

QueryForObject and queryForList returned ing objects in spring MVC jdbcTemplate

When using spring MVC to build a project, I plan to directly use spring mvc jdbc. If I introduce MySQL, Hibernate, and other ORM, it is too troublesome, so I will directly use Spring framework. jdbc. SpringMVCJDBC provides two data jdbc operation classes: jdbcTemplate and namedParameterJdbcTemplate. They all provide a lot of methods and I will not write them (see the source code ). Now, the question is, how can I map objects returned from the database?

Now that I have a table named "user", I need to return the UserInfo object and List .

The UserInfo object is as follows:

 

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;}}

Here we can use the following method:

 

1. Add a Map conversion method to UserInfo in UserInfo. java.

In the preceding UserInfo, add the conversion method:

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 userInfo;}public static List
  
    toObject(List
   
    > lists){List
    
      userInfos = new ArrayList
     
      ();for (Map
      
        map : lists) {UserInfo userInfo = UserInfo.toObject(map);if (userInfo != null) {userInfos.add(userInfo);}}return userInfos;}
      
     
    
   
  
 

Call JdbcTemplate to return the Map set as follows:

 

Public Map QueryForMap (String SQL, Object... args) throws DataAccessException

Public List > QueryForList (String SQL) throws DataAccessException

Call the preceding conversion.

 

@Autowiredprivate JdbcTemplate jdbcTemplate;@Autowiredprivate NamedParameterJdbcTemplate namedParameterJdbcTemplate;@Overridepublic UserInfo getById(Integer id) {String sql = SELECT * FROM user_info WHERE id = ?;    Map
 
   map = jdbcTemplate.queryForMap(sql, new Object[]{1});return UserInfo.toObject(map);}@Overridepublic List
  
    findAll() {String sql = SELECT * FROM user_info;List
   
    > lists = jdbcTemplate.queryForList(sql);return UserInfo.toObject(lists);}
   
  
 

Summary: This method can be implemented, but the speed is relatively slow.

 

 

2. Use RowMapper to implement the interface

To view the source code of Spring JDBC, we will release it first and provide the following methods:

Public T queryForObject (String SQL, RowMapper RowMapper, Object... args) throws DataAccessException

Public List Query (String SQL, RowMapper RowMapper) throws DataAccessException

In this example, You need to upload a Mapper class that implements RowMapper to return objects. Okay, let's transform UserInfo to implement the RowMapper interface and implement the mapRow method in the interface.

Modify the UserInfo object as follows:

 

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;/*** ing data tables and fields ** @ author andy **/public class UserInfo implements RowMapper
 
  
, 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 = 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;} @ Overridepublic 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 ;}}
 

We can implement the following in the Dao layer:

 

 

@Autowiredprivate JdbcTemplate jdbcTemplate;@Autowiredprivate NamedParameterJdbcTemplate namedParameterJdbcTemplate;@Overridepublic UserInfo getById(Integer id) {String sql = SELECT * FROM user_info WHERE id = ?;UserInfo userInfo = jdbcTemplate.queryForObject(sql, new UserInfo(),new Object[] { id });return userInfo;}@Overridepublic List
 
   findAll() {String sql = SELECT * FROM user_info;List
  
    userInfos = jdbcTemplate.query(sql, new UserInfo());return userInfos;}
  
 

This method is faster than the previous method.

 

However, when viewing the JDBC source code, we also see the following method:

Public T queryForObject (String SQL, Class RequiredType, Object... args) throws DataAccessException

Public List QueryForList (String SQL, Class ElementType) throws DataAccessException

Can we separate the following calls?

 

UserInfo userInfo = jdbcTemplate. queryForObject (SQL, UserInfo. class,
New Object [] {id });

List UserInfos = jdbcTemplate. queryForList (SQL, UserInfo. class );

 

Run the JUnit test and the results are as follows:

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.exe cute (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)

This indicates that the expected result is 1 and the result is 4 columns. (The database contains one row of data and four columns)

Why? Let's continue to check the JDBC source code,

Public T queryForObject (String SQL, Class RequiredType, Object... args) throws DataAccessException source code is as follows:

 

        @Overridepublic 
 
   T queryForObject(String sql, Class
  
    requiredType, Object... args) throws DataAccessException {return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));}
  
 

 

GetSingleColumnRowMapper only obtains UserInfo through reflection, and finally executes the ing method in it, because we implement the RowMapper Method for UserInfo.

This eventually calls the public T queryForObject (String SQL, RowMapper RowMapper, Object... args) throws DataAccessException method. And why is the error reported when the above Code can be obtained? after reading the source code, we can find that:

Public T queryForObject (String SQL, Class RequiredType, Object... args) throws DataAccessException this reflection type is to return each data column to the T type, so an error occurs. Therefore, this method only supports this type of data (such as String ), you cannot define Bean entities of complex types. Therefore, you can return a single column (such as count (*) and a column value ).

However, it has not been explained in the document, and I have tested it for a long time. What is the best practice?