Use of JdbcTemplate in Spring

In a recent job, for the sake of simplicity and convenience, I used Spring’s own JdbcTemplate to access the database. I thought I was very proficient in it before, but later I found out that I was too naive and stepped on many pitfalls.

Basic methods

JdbcTemplate comes with many methods to execute SQL statements. I will mainly list the more commonly used methods below.

//Execute SQL and return an object
@Override
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args)
throws DataAccessException {
List<T> results = query(sql, args,
new RowMapperResultSetExtractor<T>(rowMapper, 1));
return DataAccessUtils.requiredSingleResult(results);
}

//Same as above, but you need to pass in the Class of the object that returns the value.
@Override
public <T> T queryForObject(String sql, Class<T> requiredType)
throws DataAccessException {
return queryForObject(sql, getSingleColumnRowMapper(requiredType));
}

//Execute SQL and return a Map object
@Override
public Map<String, Object> queryForMap(String sql, Object... args)
throws DataAccessException {
return queryForObject(sql, args, getColumnMapRowMapper());
}

//Execute SQL and return a List object
@Override
public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper)
throws DataAccessException {
return query(sql, args, new RowMapperResultSetExtractor<T>(rowMapper));
}

//Execute SQL and return a List object
@Override
@Override
public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args)
throws DataAccessException {
return query(sql, args, getSingleColumnRowMapper(elementType));
}

//Execute a SQL, mainly used to update and add data
@Override
public int update(String sql, Object... args) throws DataAccessException {
return update(sql, newArgPreparedStatementSetter(args));
}

//Execute SQL and return a List object, which is a Map object
@Override
public List<Map<String, Object>> queryForList(String sql, Object... args)
throws DataAccessException {
return query(sql, args, getColumnMapRowMapper());
}

Notes

Return at least one object

@Override
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args)
throws DataAccessException {
List<T> results = query(sql, args,
new RowMapperResultSetExtractor<T>(rowMapper, 1));
return DataAccessUtils.requiredSingleResult(results);
}

public RowMapperResultSetExtractor(RowMapper<T> rowMapper, int rowsExpected) {
Assert.notNull(rowMapper, "RowMapper is required");
this.rowMapper = rowMapper;
this.rowsExpected = rowsExpected;
}

As you can know from the above code, an object must be returned and null cannot be returned. If no piece of information is found to match the database search, an error will be reported and the following error will be reported.

org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 0

So if you are not sure whether there is information, use query, queryForList to avoid errors. Returning a single object generally has such restrictions. If you are not sure that you can use this method, take a look at the source code. Setting 1 means that there must be a value.

Return the specified object

The method in JdbcTemplate can pass in the Class value of an object and return the value of the object. However, it should be noted that it only supports basic types.

//For example, it supports the following writing methods
public Integer getCourseCount(String sql){
return (Integer) jdbcTemplate.queryForObject(sql,java.lang.Integer.class);
}

Through the source code, we found that the parameter Class requiredType supports the following types. The source code is to find out whether it is the following type from primitiveWrapperTypeMap:

primitiveWrapperTypeMap.put(Boolean.class, boolean.class);
primitiveWrapperTypeMap.put(Byte.class, byte.class);
primitiveWrapperTypeMap.put(Character.class, char.class);
primitiveWrapperTypeMap.put(Double.class, double.class);
primitiveWrapperTypeMap.put(Float.class, float.class);
primitiveWrapperTypeMap.put(Integer.class, int.class);
primitiveWrapperTypeMap.put(Long.class, long.class);
primitiveWrapperTypeMap.put(Short.class, short.class);

If you need to return a custom object, you need another method:

If List is returned, as in the following case

public List<Course> getCourseList(String sql){
return jdbcTemplate.query(sql,new RowMapper<Course>(){
@Override
public Course mapRow(ResultSet rs, int rowNum) throws SQLException {
Integer id=rs.getInt("id");
String coursename=rs.getString("coursename");
//Encapsulate data into objects
Course course=new Course();
course.setId(id);
course.setCoursename(coursename.trim());
return course;
}
});
}

Or use List>

@Override
public List<MyScoreDto> listMyScore(Integer studentId) {
  String sql = "select g.score,c.className from grade g"
       + " left join teacher t on t.id=g.teacherId"
       + " left join student s on s.id=g.studentId"
       + " left join classname c on c.id=t.classNameId"
       + " where s.id=" + studentId;
  List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
  if(list!=null & amp; & amp; list.size()>0){
    List<MyScoreDto> ls = new ArrayList<MyScoreDto>();
    for(int i=0;i<list.size();i + + ){
      MyScoreDto c = new MyScoreDto();

        try {
          BeanUtils.populate(c, list.get(i));
        } catch (IllegalAccessException | InvocationTargetException e) {
          e.printStackTrace();
        }

      ls.add(c);
    }
    return ls;
  }

  return null;
}

Used org.apache.commons.beanutils.BeanUtils to convert Map objects into custom objects.

Later, for convenience, I wrote a RowMapper myself to simplify the operation.

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;

import org.springframework.jdbc.core.RowMapper;

import com.lsb.exam.utils.StringUtils;

public class MyRowMapper<T> implements RowMapper<T> {

Class<T> cls;

public MyRowMapper(Class<T> cls) {
this.cls = cls;
}

@Override
public T mapRow(ResultSet rs, int rowNum) {
try {
Field[] fields = cls.getDeclaredFields();

T obj = cls.newInstance();

// Get all attributes
for (Field field : fields) {
field.setAccessible(true);
if (field.getGenericType().toString().equals("class java.lang.Integer")) {
Method m = obj.getClass().getDeclaredMethod(
"set" + StringUtils.firstChar2UpperCase(field.getName()), java.lang.Integer.class);

m.invoke(obj, rs.getInt(field.getName()));
}
if (field.getGenericType().toString().equals("class java.lang.String")) {
Method m = obj.getClass().getDeclaredMethod(
"set" + StringUtils.firstChar2UpperCase(field.getName()), java.lang.String.class);
m.invoke(obj, rs.getString(field.getName()));
}
if (field.getGenericType().toString().equals("class java.util.Date")) {
Method m = obj.getClass().getDeclaredMethod(
"set" + StringUtils.firstChar2UpperCase(field.getName()), java.util.Date.class);
m.invoke(obj, rs.getDate(field.getName()));
}
}
return obj;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
}

There is an error above, that is, if the object inherits the parent class, the value cannot be injected into the properties of the parent class, because cls.getDeclaredFields() cannot obtain the properties of the parent class, so I changed it. a method

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.lsb.exam.utils.StringUtils;

public class MyRowMapper<T> implements RowMapper<T> {

Class<T> cls;

public MyRowMapper(Class<T> cls) {
this.cls = cls;
}

@Override
public T mapRow(ResultSet rs, int rowNum) {
try {

T obj = cls.newInstance();
\t\t\t
//This can only get the common private fields of the current class
//Field[] fields = cls.getDeclaredFields();
List<Field> list = new ArrayList<>();
\t\t\t
Class<?> c = cls;
\t\t\t
//Loop to get
while(c != null){
list.addAll(Arrays.asList(c.getDeclaredFields()));
c = c.getSuperclass();
}
\t\t\t
// Get all attributes
for (Field field : list) {
field.setAccessible(true);
if (field.getGenericType().toString().equals("class java.lang.Integer")) {
Method m = obj.getClass().getMethod(
"set" + StringUtils.firstChar2UpperCase(field.getName()), java.lang.Integer.class);

m.invoke(obj, rs.getInt(field.getName()));
}
if (field.getGenericType().toString().equals("class java.lang.String")) {
Method m = obj.getClass().getMethod(
"set" + StringUtils.firstChar2UpperCase(field.getName()), java.lang.String.class);
m.invoke(obj, rs.getString(field.getName()));
}
if (field.getGenericType().toString().equals("class java.util.Date")) {
Method m = obj.getClass().getMethod(
"set" + StringUtils.firstChar2UpperCase(field.getName()), java.util.Date.class);
m.invoke(obj, rs.getDate(field.getName()));
}
}
return obj;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
}

In fact, in the process, it is very important to have a knowledge of reflection. An introduction to the method:

1. Get class

Method	Description
object.getClass()	Get the class object to which this instance belongs
T.class	Get the class object to which this instance belongs
Class.forName()	Get the class object through the class path
Class.getSuperclass()	Get The class object of the parent class
Class.getClasses()	Gets all public classes, interfaces, enumeration members, and inherited Members (especially classes)
Class.getDeclaredClasses()	Display declared classes and interfaces within the class
Class.getEnclosingClass()	Get the closure class

2. Get attributes

Method	Description
getDeclaredField(String name)	Get the specified field (public, private), excluding parent class fields
getField(String name)	Get the specified field (public) , including parent class fields
getDelaredFields()	Get all declared fields (public, private) in the class, excluding parent class fields
getFields()	Get all fields (public), including parent class fields

3. Obtaining method

Method	Description
getDeclaredMethod(String name, Class … paramType)	Get the specified method (public, private), excluding parent class methods
getMethod(String name, Class … paramType )	Get the specified method (public), including parent class methods
getDeclaredMethods()	Get all declared methods (public, Private), excluding parent class methods
getMethods()	Get all methods (public), including parent class methods

The above information can access the reflection information from the Oracle website.