一个完整的idea spring boot 项目及坑---9.mapper

实体类的变量名不一定与sql语句中的完全对应,要在resultMap 中规范

column为sql语句中查询数据的查询名或者别名.

property为对应的实体类名称.

例:

bean中变量名为 student;

数据库表中为table.student或者 table.student'学生'

这时resultMap中就是

 <result column="student" property="schoolName" jdbcType="VARCHAR"/>

或者

 <result column="学生" property="schoolName" jdbcType="VARCHAR"/>

<?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" >
<mapper namespace="com.example.demo.mapper.PaymentMapper">
    <resultMap id="BaseResultMap" type="com.example.demo.bean.Payment">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="schoolName" property="schoolName" jdbcType="VARCHAR"/>
        <result column="sesssion" property="sesssion" jdbcType="VARCHAR"/>
        <result column="studentClass" property="sclass" jdbcType="VARCHAR"/>
        <result column="counts" property="counts" jdbcType="INTEGER"/>
        <result column="sname" property="sname" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, schoolName, studentName, studentSsession, studentClass, uid, goods, orderid
    </sql>

    <select id="findPayment" resultMap="BaseResultMap">
           SELECT
         t1.学校 AS schoolName ,
         t1.年级 as sesssion,
         t1.班级 as studentClass,
         count(t1.姓名) as counts,
         group_concat( t1.姓名 ) as sname
      FROM
         (
      SELECT
         u.tel "监护人手机号",
         u.id,
         si.学校,
         si.姓名,
         si.识别码,
         si.年级,
         si.班级
      FROM
         (
      SELECT
         s.NAME "学校",
         stu.studentName "姓名",
         stu.studentId "识别码",
      CASE
            WHEN stu.studentSession = 2017 THEN
         '1年级'
         WHEN stu.studentSession = 2016 THEN
         '2年级'
         WHEN stu.studentSession = 2015 THEN
         '3年级'
         WHEN stu.studentSession = 2014 THEN
         '4年级'
         WHEN stu.studentSession = 2013 THEN
         '5年级'
         WHEN stu.studentSession = 2012 THEN
         '6年级' ELSE '错误'
         END "年级",
         stu.studentClass "班级"
      FROM
         met_student stu,
         met_school s
      WHERE
         SUBSTRING( stu.studentId, 1, 6 ) = s.activation_code
         AND s.NAME LIKE '%尚义%'
         ) AS si
         LEFT JOIN met_user u ON si.识别码 = u.studentid
         ) AS t1
         LEFT JOIN met_shopv2_order so ON t1.id = so.uid
         AND so.search LIKE '%五月份%'
         AND so.state IN ( 2, 4 )
      WHERE
         isnull( so.search )
      GROUP BY
        t1.`学校`,
         t1.年级,
         t1.班级
      ORDER BY
         t1.年级,
         t1.班级
    </select>
</mapper>