The mybatis query statement returns operations on objects and generic collections

Time:2021-10-21

Mybatis query statements return objects and generic collections

Empmapper mapping interface:

package cn.et.mybatis.lesson03; 
import java.util.List; 
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select; 
public interface EmpMapper { 
 
 /**
  *Query a single piece of data,
  *The column name of each column will match the corresponding attribute in the EMP entity class
  *When matching, both sides will be turned into small letters for matching
  *If the match is successful, the set method of the object in the EMP entity class will be called
  * 
  *If the column name does not match the property of EMP,
  *1. Set an alias for the column of query results
  *2. Establish a relationship between column name ename and attribute ename1, and establish a relationship with a single attribute
  * 
  *Column is case insensitive and property is case sensitive
  * @return
  */
 @Results(
   {
    @Result(column="ename",property="ename1"),
    @Result(column="empNo",property="empNo1"),
    @Result(column="sal",property="sal1"),
   }
 )
 @Select("select * from emp where empno=#{0}")
 public Emp queryEmpByEmpNo(String empNo); 
 
 /**
  *Multiple pieces of data are queried, and each piece of data is an EMP object
  *The column name of each column will match the corresponding attribute in the EMP entity class
  *When matching, both sides will be turned into small letters for matching
  *If the match is successful, the set method of the object in the EMP entity class will be called
  *If none of the data matches successfully, the EMP object will not be created
  * @param empNo
  * @return
  */
 @Results(
   {
    @Result(column="ename",property="ename1"),
    @Result(column="empNo",property="empNo1"),
    @Result(column="sal",property="sal1"),
   }
 )
 @Select("select * from emp")
 public List<Emp> queryEmp(); 
}

Test class:

package cn.et.mybatis.lesson03; 
import java.io.InputStream;
import java.util.List; 
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
 
public class TestMybatis { 
 public static SqlSession getSession(){
  String resource = "/cn/et/mybatis/lesson03/mybatis.xml";
  InputStream inputStream = TestMybatis.class.getResourceAsStream(resource);
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  //Open session
  SqlSession session = sqlSessionFactory.openSession();
  return session;
 }
 
 public static void main(String[] args) {
  SqlSession session = getSession();
  EmpMapper emp = session.getMapper(EmpMapper.class);
  Emp obj = emp.queryEmpByEmpNo("8000");
  System.out.println(obj);
 } 
 
 @Test
 public void test(){
  SqlSession session = getSession();
  EmpMapper emp = session.getMapper(EmpMapper.class);
  List<Emp> result = emp.queryEmp();
  for (Emp emp2 : result) {
   System.out.println(emp2);
  }
 }
}

XML Mapping———–

dept_mapper.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<!-- 
 Interface mapping
 The namespace must be consistent with the full name of the interface
 -->
<mapper namespace="cn.et.mybatis.lesson03.resultEntityXml.DeptMapper">
 
 <!--  Column is case insensitive, and property is case sensitive -- >
  <resultMap type="cn.et.mybatis.lesson03.resultEntityXml.Dept">
    <result column="deptno" property="deptno1"/>
    <result column="dname" property="dname1"/>
    <result column="loc" property="loc1"/>
  </resultMap>
  <select resultMap="myDept">
 select * from dept where deptno=#{0}
  </select>  
</mapper>

Test class:

package cn.et.mybatis.lesson03.resultEntityXml; 
import java.io.InputStream; 
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
public class TestMybatis { 
 public static SqlSession getSession(){
  String resource = "/cn/et/mybatis/lesson03/mybatis.xml";
  InputStream inputStream = TestMybatis.class.getResourceAsStream(resource);
  SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  //Open session
  SqlSession session = sqlSessionFactory.openSession();
  return session;
 }
 
 public static void main(String[] args) {
  SqlSession session = getSession();
  DeptMapper dept = session.getMapper(DeptMapper.class);
  Dept result = dept.queryDept("10");
  System.out.println(result);  
 } 
}

Possible problems when mybatis query result set has generic properties

Question:

When the received result is map or the attribute of the object is generic:


@Data
public class GenericKeyValueVo<K,V> {
    private K key;
    private V value;
}

At this time, there may be a problem if the resulttype is directly pointed to the fully qualified name of the object. Because if a field in the query result is greater than 1000, “,” will appear, such as 1000.56. Mybatis will not report an error because the attribute of this object is generic and can be received. After obtaining the result, even if the received variable type is defined as:

在这里插入图片描述

The second property also stores a value of type string. In subsequent processing, there may be a conversion error from string to double data type.

resolvent:

Define a resultmap indicating the javatype


    <resultMap type="com.meinergy.mkting.commons.entity.wholesale.vo.GenericKeyValueVo">
        <result column="key" property="key" javaType="java.lang.String"/>
        <result column="value" property="value" javaType="java.lang.Double"/>
    </resultMap>

Then a convert function is used to standardize the query result format


convert(FORMAT(queryResult, decimal(12, 2))

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer.