TKmybatis的框架介绍和原理分析及Mybatis新特性演示

Love The Way You Lie 2022-05-13 07:10 275阅读 0赞

tkmybatis是在mybatis框架的基础上提供了很多工具，让开发更加高效，下面来看看这个框架的基本使用，后面会对相关源码进行分析，感兴趣的同学可以看一下，挺不错的一个工具

实现对员工表的增删改查的代码   
java的dao层接口

public interface WorkerMapper extends Mapper<Worker> {
    }

*  1
 *  2

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">
    <mapper namespace="com.jjs.kaiwen.dao.WorkerMapper">
      <resultMap id="BaseResultMap" type="com.jjs.kaiwen.model.Worker">
        
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="worker_id" jdbcType="VARCHAR" property="workerId" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="org_id" jdbcType="INTEGER" property="orgId" />
        <result column="status" jdbcType="VARCHAR" property="status" />
        <result column="role_id" property="roleId" jdbcType="INTEGER" />
      </resultMap>
    </mapper>

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15

实体对象

@Table(name = "worker")
    public class Worker {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
    
        @Column(name = "worker_id")
        private String workerId;
    
        private String name;
    
        @Column(name = "org_id")
        private Integer orgId;
    
        private String status;
    
        @Column(name = "role_id")
        private Integer roleId;
    
        // getters and setters ...
        }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21

以上就是实现对Worker进行增删改查的所有代码，包括选择性更新、插入、删除等，所有的方法列表如下

![这里写图片描述][20160909101938116]

以后对表字段的添加或修改只需要更改实体对象的注解，不需要修改xml映射文件，如将worker\_id改成worker\_no

@Column(name = "worker_no")
    private String workerNo;

*  1
 *  2

数据源的配置，只需要将org.mybatis.spring.mapper.MapperScannerConfigurer改成tk.mybatis.spring.mapper.MapperScannerConfigurer，然后加一个属性   
，也可不加，因为框架提供了默认实现

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9

用这个库之后写代码感觉在飞…….如果只是简单的了解此框架到这里就可以了，下面是对框架实现原理的分析

## 原理的简单分析 ##

此框架为我们实现这些功能所有的改动都在Mapper层面，所有的Mapper都继承了tk.mybatis.mapper.common.Mapper

public interface WorkerMapper extends Mapper<Worker> {}

*  1

Mapper接口的声明如下，可以看到Mapper接口实现了所有常用的方法

public interface Mapper<T> extends
            BaseMapper<T>,
            ExampleMapper<T>,
            RowBoundsMapper<T>,
            Marker {
    
    }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7

看一下完整的UML图，太大了，可以用新窗口打开，放大之后再看   
![这里写图片描述][20160909103937243]

这里选择一个接口：SelectOneMapper接口，对于源码进行简单分析，此接口声明如下：

public interface SelectOneMapper<T> {
    
        /**
         * 根据实体中的属性进行查询，只能有一个返回值，有多个结果是抛出异常，查询条件使用等号
         *
         * @param record
         * @return
         */
        @SelectProvider(type = BaseSelectProvider.class, method = "dynamicSQL")
        T selectOne(T record);
    
    }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12

@SelectProvider是mybatis3之后提供的，用于灵活的设置sql来源，这里设置了服务提供类和方法，但这个库并没有直接用method指定的方法来返回sql，而是在运行时进行解析的，代码如下

public class BaseSelectProvider extends MapperTemplate {
    
        public String selectOne(MappedStatement ms) {
            Class<?> entityClass = getEntityClass(ms);
            //修改返回值类型为实体类型
            setResultType(ms, entityClass);
            StringBuilder sql = new StringBuilder();
            sql.append(SqlHelper.selectAllColumns(entityClass));
            sql.append(SqlHelper.fromTable(entityClass, tableName(entityClass)));
            sql.append(SqlHelper.whereAllIfColumns(entityClass, isNotEmpty()));
            return sql.toString();
        }
    }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13

到这里我们就大概知道了这个库为我们提供便利的原理了，总的来说就是这个库帮我们提供了对表的基本操作的sql，帮我们省了很多工作量，而且维护起来也很方便，否则我们的xml文件动不动就几百行甚至上千行

对源码的探索不能到这里停止，最起码要分析到与另一个框架的整合点

我们知道，mybatis的mapper接口是在启动的时候被框架以JdkProxy的形式封装了的，具体对应的类是MapperFactoryBean，这个类中有一个checkDaoConfig()方法，是从父类继承并重写了该方法，继承结构如下

MapperFactoryBean -> SqlSessionDaoSupport -> DaoSupport

*  1

这里的DaoSupport就是spring提供的Dao的抽象，代码如下

public abstract class DaoSupport implements InitializingBean {
    
    // spring 完成属性设置后会调用此方法
        @Override
        public final void afterPropertiesSet() throws IllegalArgumentException, BeanInitializationException {
            // 这里提供了接口供子类去实现
            checkDaoConfig();
    
            // Let concrete implementations initialize themselves.
            try {
                initDao();
            }
            catch (Exception ex) {
                throw new BeanInitializationException("Initialization of DAO failed", ex);
            }
        }
    
        protected abstract void checkDaoConfig() throws IllegalArgumentException;
    
        protected void initDao() throws Exception {
        }
    
    }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23

框架自定义的MapperFactoryBean重写了checkDaoConfig()方法，完成对所有sql语句的设置，代码如下

@Override
        protected void checkDaoConfig() {
            super.checkDaoConfig();
            //通用Mapper
            if (mapperHelper.isExtendCommonMapper(getObjectType())) {
            //这里去处理该类所对应的MappedStatement，封装在helper类中处理
                mapperHelper.processConfiguration(getSqlSession().getConfiguration(), getObjectType());
            }
        }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9

MapperHelper的processConfiguration方法如下

public void processConfiguration(Configuration configuration, Class<?> mapperInterface) {
            String prefix;
            if (mapperInterface != null) {
                prefix = mapperInterface.getCanonicalName();
            } else {
                prefix = "";
            }
            for (Object object : new ArrayList<Object>(configuration.getMappedStatements())) {
                if (object instanceof MappedStatement) {
                    MappedStatement ms = (MappedStatement) object;
                    //检查这个MappedStatement是否属于此映射对象
                    if (ms.getId().startsWith(prefix) && isMapperMethod(ms.getId())) {
                        if (ms.getSqlSource() instanceof ProviderSqlSource) {
                           //去设置该statement的sql语句
                            setSqlSource(ms);
                        }
                    }
                }
            }
        }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20

设置sql的逻辑，提供了几种不同类型的sqlsource

public void setSqlSource(MappedStatement ms) throws Exception {
            if (this.mapperClass == getMapperClass(ms.getId())) {
                throw new RuntimeException("请不要配置或扫描通用Mapper接口类：" + this.mapperClass);
            }
            Method method = methodMap.get(getMethodName(ms));
            try {
                //第一种，直接操作ms，不需要返回值
                if (method.getReturnType() == Void.TYPE) {
                    method.invoke(this, ms);
                }
                //第二种，返回SqlNode
                else if (SqlNode.class.isAssignableFrom(method.getReturnType())) {
                    SqlNode sqlNode = (SqlNode) method.invoke(this, ms);
                    DynamicSqlSource dynamicSqlSource = new DynamicSqlSource(ms.getConfiguration(), sqlNode);
                    setSqlSource(ms, dynamicSqlSource);
                }
                //第三种，返回xml形式的sql字符串
                else if (String.class.equals(method.getReturnType())) {
                    String xmlSql = (String) method.invoke(this, ms);
                    SqlSource sqlSource = createSqlSource(ms, xmlSql);
                    //替换原有的SqlSource
                    setSqlSource(ms, sqlSource);

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22

到这里整个sql的获取流程就分析完了，本人用这个库写过一个小项目，确实节省了开发的工作量，而且DAO层的结构更加清晰简洁了

## 关于mybatis新特性 ##

从3.4.0开始，mybatis提供对外部表的alias引用方法，多表联合查询就方便多了，我们先看原始的方式是怎样做的

select a.id,a.name,b.bid,b.bname .....
    from user a 
    left join room b

*  1
 *  2
 *  3

原始的方式是将所有的表字段列出来，再来看用新特性怎样做

select id="selectUsers" resultType="map">
      select
        <include refid="user_col_sql_id"><property name="alias" value="t1"/>,
        <include refid="room_col_sql_id"><property name="alias" value="t2"/>
      from user t1
        left join room t2
    </select>

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7

这里主要就是对基本的sql进行了复用，如果对表进行了修改只要在原始的sql节点修改就可以了，就算是5个表的联合查询，sql也是清晰易懂，维护起来会更轻松

新版本的mybatis对于对象映射也提供了更友好的方式，直接使用外部的ResultMap再加上查询语句中的别名就映射完成了

*  1
 *  2
 *  3

## 更进一步 ##

敏锐的程序员可能会提出问题，如当多表查询的时候可能会存在字段名称相同的情况，这里的解决方案是给include添加另一个属性

*  1
 *  2
 *  3
 *  4

包含prefix的sqlNode如下

<sql id="base_column_with_alias">
            ${alias}.ID as ${prefix}ID,
            ${alias}.WORKER_ID as ${prefix}WORKER_ID,
            ${alias}.NAME as ${prefix}NAME,
            ${alias}.ZB_ROLE_ID as ${prefix}ZB_ROLE_ID,
            ${alias}.ORG_ID as ${prefix}ORG_ID,
            ${alias}.STATUS as ${prefix}STATUS
        </sql>

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8

如果说觉得手动写包含alias和prefix的字段麻烦，可以用，mybatis代码生成器的插件的方式实现，我自己写了一个生成器的插件，可以[代码再这里，仅供参考][Link 1]

### 通用Service类 ###

/**
     * Created by Kaiwen
     */
    @Service
    public abstract class CommonServiceImpl<T,PK extends Serializable> implements CommonService<T,PK> {
        /**
         * 泛型注入
         */
        @Autowired
        private Mapper<T> mapper;
    
        public T selectByPrimaryKey(PK entityId) {
    
            return mapper.selectByPrimaryKey(entityId);
        }
    
        public int deleteByPrimaryKey(PK entityId) {
            return mapper.deleteByPrimaryKey(entityId);
        }
    
        public int insert(T record) {
            return mapper.insert(record);
        }
    
        public int insertSelective(T record) {
            return mapper.insertSelective(record);
        }
    
        public int updateByPrimaryKeySelective(T record) {
            return mapper.updateByPrimaryKeySelective(record);
        }
    
        public int updateByPrimaryKey(T record) {
            return mapper.updateByPrimaryKey(record);
        }
    
        public List<T> selectByExample(Example example) {
            return mapper.selectByExample(example);
        }
    }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23
 *  24
 *  25
 *  26
 *  27
 *  28
 *  29
 *  30
 *  31
 *  32
 *  33
 *  34
 *  35
 *  36
 *  37
 *  38
 *  39
 *  40

### 注入方式区别 ###

<bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
            <property name="basePackage" value="com.jjshome.esf.core.dao.school" />
            <property name="properties">
                <value>
                    mappers=tk.mybatis.mapper.common.Mapper
                </value>
            </property>
        </bean>
    
    
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.jjshome.esf.core.dao.community,com.jjshome.esf.core.dao.hsl"/>
        </bean>

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14

实体类

package com.jjshome.esf.common.entity.school;
    
    import java.util.Date;
    import javax.persistence.*;
    
    @Table(name = "XQ_SCHOOL_AREA")
    public class SchoolArea {
        /**
         * 主键ID
         */
        @Id
        @Column(name = "ID")
        private Integer id;
    
        /**
         * 城市编码
         */
        @Column(name = "CITY_CODE")
        private String cityCode;
    
        /**
         * 学区名称
         */
        @Column(name = "NAME")
        private String name;
    
        /**
         * 学区名称拼音
         */
        @Column(name = "NAME_SPELL")
        private String nameSpell;
    
        /**
         * 状态,1:正常,0:删除
         */
        @Column(name = "STATUS")
        private Byte status;
    
        /**
         * 添加人
         */
        @Column(name = "CREATE_ID")
        private String createId;
    
    
        @Transient
        private Integer primaryCount; //小学数量
        @Transient
        private Integer middleCount; //初中数量
        @Transient
        private Integer highCount;//高中数量

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23
 *  24
 *  25
 *  26
 *  27
 *  28
 *  29
 *  30
 *  31
 *  32
 *  33
 *  34
 *  35
 *  36
 *  37
 *  38
 *  39
 *  40
 *  41
 *  42
 *  43
 *  44
 *  45
 *  46
 *  47
 *  48
 *  49
 *  50
 *  51
 *  52

### TK mybatis Mapper文件内容 ###

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23
 *  24
 *  25
 *  26
 *  27
 *  28
 *  29
 *  30
 *  31
 *  32
 *  33
 *  34
 *  35
 *  36
 *  37
 *  38
 *  39
 *  40
 *  41
 *  42
 *  43
 *  44
 *  45
 *  46
 *  47
 *  48
 *  49
 *  50
 *  51
 *  52
 *  53
 *  54
 *  55
 *  56
 *  57
 *  58
 *  59
 *  60
 *  61
 *  62
 *  63
 *  64
 *  65
 *  66
 *  67
 *  68
 *  69
 *  70
 *  71
 *  72
 *  73
 *  74
 *  75
 *  76
 *  77
 *  78
 *  79
 *  80
 *  81
 *  82
 *  83
 *  84
 *  85
 *  86
 *  87
 *  88

### 普通mybatisMapper文件 ###

<?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.jjshome.esf.core.dao.school.ISchoolInfoDAO">
        <resultMap id="SchoolInfo" type="com.jjshome.esf.common.entity.school.SchoolInfo">
            <id column="ID" property="id"/>
            <result column="NAME" property="name"/>
            <result column="NAME_SPELL" property="nameSpell"/>
            <result column="ALIAS" property="alias"/>
            <result column="ALIAS_SPELL" property="aliasSpell"/>
            <result column="TYPE" property="type" typeHandler="com.jjshome.esf.core.component.handler.DictValueTypeHandler"/>
            <result column="AREA_ID" property="areaId"/>
            <result column="CITY_CODE" property="cityCode"/>
            <result column="AREA_CODE" property="areaCode"/>
            <result column="ADDR" property="addr"/>
            <result column="START_TIME" property="startTime"/>
            <result column="MOTTO" property="motto"/>
            <result column="WEB_SITE" property="webSite"/>
            <result column="PHONE" property="phone"/>
            <result column="FEATURE" property="feature" typeHandler="com.jjshome.esf.core.component.handler.DictValueListTypeHandler"/>
            <result column="LNG" property="lng"/>
            <result column="LAT" property="lat"/>
            <result column="UNIT_PRICE" property="unitPrice"/>
            <result column="SALE_PRICE" property="salePrice"/>
            <result column="NATURE_TYPE" property="natureType" typeHandler="com.jjshome.esf.core.component.handler.DictValueTypeHandler"/>
            <result column="NATURE_CITY" property="natureCity" typeHandler="com.jjshome.esf.core.component.handler.DictValueTypeHandler"/>
            <result column="SCHOOL_DEGREE" property="schoolDegree"/>
            <result column="ENROL_DEGREE" property="enrolDegree"/>
            <result column="IMG_DEGREE" property="imgDegree"/>
            <result column="STATUS" property="status"/>
            <result column="CREATE_ID" property="createId"/>
            <result column="CREATE_DATE" property="createDate"/>
            <result column="UPDATE_ID" property="updateId"/>
            <result column="UPDATE_DATE" property="updateDate"/>
    
            <result column="CITY_NAME" property="cityName" />
            <result column="AREA_NAME" property="areaName" />
            <result column="SCHOOL_DISTRICT_NAME" property="schoolDistrictName" />
            <result column="SALE_COUNT" property="saleCount" />
        </resultMap>
    
        <sql id="Base_Column_List">
            ID,
            NAME,
            NAME_SPELL,
            ALIAS,
            ALIAS_SPELL,
            TYPE,
            AREA_ID,
            CITY_CODE,
            AREA_CODE,
            ADDR,
            START_TIME,
            MOTTO,
            WEB_SITE,
            PHONE,
            FEATURE,
            LNG,
            LAT,
            UNIT_PRICE,
            SALE_PRICE,
            NATURE_TYPE,
            NATURE_CITY,
            SCHOOL_DEGREE,
            ENROL_DEGREE,
            IMG_DEGREE,
            STATUS,
            CREATE_ID,
            CREATE_DATE,
            UPDATE_ID,
            UPDATE_DATE,
            SALE_COUNT,
            SALE_COUNT
        </sql>
    
        <select id="selectById" resultMap="SchoolInfo" parameterType="java.lang.Integer">
            SELECT
                i.*,
                yc.NAME as 'CITY_NAME',
                ya.NAME as 'AREA_NAME',
                xq.NAME as 'SCHOOL_DISTRICT_NAME'
            FROM
                XQ_SCHOOL_INFO i
                LEFT JOIN YW_CITY_SETTING yc ON i.CITY_CODE = yc.CODE
                LEFT JOIN YW_CITY_SETTING ya ON i.AREA_CODE = ya.CODE
                LEFT JOIN XQ_SCHOOL_AREA xq ON i.AREA_ID = xq.ID
            WHERE
                i.ID = #{id,jdbcType=INTEGER}
        </select>
    
        <delete id="deleteById" parameterType="java.util.Map">
            UPDATE
                XQ_SCHOOL_INFO
            SET
                STATUS = 0,
                UPDATE_ID = #{updateId},
                UPDATE_DATE = NOW()
            WHERE
                ID = #{id,jdbcType=INTEGER}
        </delete>
    
        <delete id="batchDeleteByIds" parameterType="java.util.Map">
            UPDATE
                XQ_SCHOOL_INFO
            SET
                STATUS = 0,
                UPDATE_ID = #{updateId},
                UPDATE_DATE = NOW()
            WHERE
            ID IN (${ids})
        </delete>
    
        <update id="deleteAreaRelation" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
            update XQ_SCHOOL_INFO
                SET AREA_ID = NULL,
                UPDATE_DATE = NOW()
            WHERE
            ID = #{id}
        </update>
    
        <insert id="insert" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
            <selectKey resultType="Integer" keyProperty="id">
                SELECT LAST_INSERT_ID()
            </selectKey>
            INSERT INTO XQ_SCHOOL_INFO
                (NAME,
                NAME_SPELL,
                ALIAS,
                ALIAS_SPELL,
                TYPE,
                AREA_ID,
                CITY_CODE,
                AREA_CODE,
                ADDR,
                START_TIME,
                MOTTO,
                WEB_SITE,
                PHONE,
                FEATURE,
                LNG,
                LAT,
                UNIT_PRICE,
                SALE_PRICE,
                NATURE_TYPE,
                NATURE_CITY,
                SCHOOL_DEGREE,
                ENROL_DEGREE,
                IMG_DEGREE,
                STATUS,
                CREATE_ID,
                CREATE_DATE,
                UPDATE_ID,
                UPDATE_DATE)
            VALUES
                (#{name,jdbcType=VARCHAR},
                #{nameSpell,jdbcType=VARCHAR},
                #{alias,jdbcType=VARCHAR},
                #{aliasSpell,jdbcType=VARCHAR},
                #{type,jdbcType=INTEGER},
                #{areaId,jdbcType=INTEGER},
                #{cityCode,jdbcType=VARCHAR},
                #{areaCode,jdbcType=VARCHAR},
                #{addr,jdbcType=VARCHAR},
                #{startTime,jdbcType=DATE},
                #{motto,jdbcType=VARCHAR},
                #{webSite,jdbcType=VARCHAR},
                #{phone,jdbcType=VARCHAR},
                #{feature,jdbcType=VARCHAR},
                #{lng,jdbcType=DECIMAL},
                #{lat,jdbcType=DECIMAL},
                #{unitPrice},
                #{salePrice},
                #{natureType,jdbcType=INTEGER},
                #{natureCity,jdbcType=INTEGER},
                #{schoolDegree,jdbcType=INTEGER},
                #{enrolDegree,jdbcType=INTEGER},
                #{imgDegree,jdbcType=INTEGER},
                #{status,jdbcType=TINYINT},
                #{createId,jdbcType=VARCHAR},
                #{createDate,jdbcType=DATE},
                #{updateId,jdbcType=VARCHAR},
                #{updateDate,jdbcType=DATE})
        </insert>
        <insert id="insertSelective" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
            <selectKey resultType="Integer" keyProperty="id">
                SELECT LAST_INSERT_ID()
            </selectKey>
            INSERT INTO XQ_SCHOOL_INFO
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="name != null">
                    NAME,
                </if>
                <if test="nameSpell != null">
                    NAME_SPELL,
                </if>
                <if test="alias != null">
                    ALIAS,
                </if>
                <if test="aliasSpell != null">
                    ALIAS_SPELL,
                </if>
                <if test="type != null">
                    TYPE,
                </if>
                <if test="areaId != null">
                    AREA_ID,
                </if>
                <if test="cityCode != null">
                    CITY_CODE,
                </if>
                <if test="areaCode != null">
                    AREA_CODE,
                </if>
                <if test="addr != null">
                    ADDR,
                </if>
                <if test="startTime != null">
                    START_TIME,
                </if>
                <if test="motto != null">
                    MOTTO,
                </if>
                <if test="webSite != null">
                    WEB_SITE,
                </if>
                <if test="phone != null">
                    PHONE,
                </if>
                <if test="feature != null">
                    FEATURE,
                </if>
                <if test="lng != null">
                    LNG,
                </if>
                <if test="lat != null">
                    LAT,
                </if>
                <if test="UNIT_PRICE != null">
                    UNIT_PRICE,
                </if>
                <if test="SALE_PRICE != null ">
                    SALE_PRICE,
                </if>
                <if test="natureType != null">
                    NATURE_TYPE,
                </if>
                <if test="natureCity != null">
                    NATURE_CITY,
                </if>
                <if test="schoolDegree != null">
                    SCHOOL_DEGREE,
                </if>
                <if test="enrolDegree != null">
                    ENROL_DEGREE,
                </if>
                <if test="imgDegree != null">
                    IMG_DEGREE,
                </if>
                <if test="status != null">
                    STATUS,
                </if>
                <if test="createId != null">
                    CREATE_ID,
                </if>
                <if test="createDate != null">
                    CREATE_DATE,
                </if>
                <if test="updateId != null">
                    UPDATE_ID,
                </if>
                <if test="updateDate != null">
                    UPDATE_DATE,
                </if>
            </trim>
            <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
                <if test="name != null">
                    #{name,jdbcType=VARCHAR},
                </if>
                <if test="nameSpell != null">
                    #{nameSpell,jdbcType=VARCHAR},
                </if>
                <if test="alias != null">
                    #{alias,jdbcType=VARCHAR},
                </if>
                <if test="aliasSpell != null">
                    #{aliasSpell,jdbcType=VARCHAR},
                </if>
                <if test="type != null">
                    #{type,jdbcType=INTEGER},
                </if>
                <if test="areaId != null">
                    #{areaId,jdbcType=INTEGER},
                </if>
                <if test="cityCode != null">
                    #{cityCode,jdbcType=VARCHAR},
                </if>
                <if test="areaCode != null">
                    #{areaCode,jdbcType=VARCHAR},
                </if>
                <if test="addr != null">
                    #{addr,jdbcType=VARCHAR},
                </if>
                <if test="startTime != null">
                    #{startTime,jdbcType=DATE},
                </if>
                <if test="motto != null">
                    #{motto,jdbcType=VARCHAR},
                </if>
                <if test="webSite != null">
                    #{webSite,jdbcType=VARCHAR},
                </if>
                <if test="phone != null">
                    #{phone,jdbcType=VARCHAR},
                </if>
                <if test="feature != null">
                    #{feature,jdbcType=VARCHAR},
                </if>
                <if test="lng != null">
                    #{lng,jdbcType=DECIMAL},
                </if>
                <if test="lat != null">
                    #{lat,jdbcType=DECIMAL},
                </if>
                <if test="unitPrice ! =null">
                    #{unitPrice},
                </if>
                <if test="salePrice">
                    #{salePrice},
                </if>
                <if test="natureType != null">
                    #{natureType,jdbcType=INTEGER},
                </if>
                <if test="natureCity != null">
                    #{natureCity,jdbcType=INTEGER},
                </if>
                <if test="schoolDegree != null">
                    #{schoolDegree,jdbcType=INTEGER},
                </if>
                <if test="enrolDegree != null">
                    #{enrolDegree,jdbcType=INTEGER},
                </if>
                <if test="imgDegree != null">
                    #{imgDegree,jdbcType=INTEGER},
                </if>
                <if test="status != null">
                    #{status,jdbcType=TINYINT},
                </if>
                <if test="createId != null">
                    #{createId,jdbcType=VARCHAR},
                </if>
                <if test="createDate != null">
                    #{createDate,jdbcType=DATE},
                </if>
                <if test="updateId != null">
                    #{updateId,jdbcType=VARCHAR},
                </if>
                <if test="updateDate != null">
                    #{updateDate,jdbcType=DATE},
                </if>
            </trim>
        </insert>
        <update id="updateSelective" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
            UPDATE XQ_SCHOOL_INFO
            <set>
                <if test="name != null">
                    NAME=#{name,jdbcType=VARCHAR},
                </if>
                <if test="nameSpell != null">
                    NAME_SPELL=#{nameSpell,jdbcType=VARCHAR},
                </if>
                <if test="alias != null">
                    ALIAS=#{alias,jdbcType=VARCHAR},
                </if>
                <if test="aliasSpell != null">
                    ALIAS_SPELL=#{aliasSpell,jdbcType=VARCHAR},
                </if>
                <if test="type != null">
                    TYPE=#{type,jdbcType=INTEGER},
                </if>
                <if test="type != null">
                    AREA_ID=#{areaId,jdbcType=INTEGER},
                </if>
    
                <if test="cityCode != null">
                    CITY_CODE=#{cityCode,jdbcType=VARCHAR},
                </if>
                <if test="areaCode != null">
                    AREA_CODE=#{areaCode,jdbcType=VARCHAR},
                </if>
                <if test="addr != null">
                    ADDR=#{addr,jdbcType=VARCHAR},
                </if>
                <if test="startTime != null">
                    START_TIME=#{startTime,jdbcType=DATE},
                </if>
                <if test="motto != null">
                    MOTTO=#{motto,jdbcType=VARCHAR},
                </if>
                <if test="webSite != null">
                    WEB_SITE=#{webSite,jdbcType=VARCHAR},
                </if>
                <if test="phone != null">
                    PHONE=#{phone,jdbcType=VARCHAR},
                </if>
                <if test="feature != null">
                    FEATURE=#{feature,jdbcType=VARCHAR},
                </if>
                <if test="lng != null">
                    LNG=#{lng,jdbcType=DECIMAL},
                </if>
                <if test="lat != null">
                    LAT=#{lat,jdbcType=DECIMAL},
                </if>
                <if test="salePrice != null">
                    UNIT_PRICE=#{unitPrice},
                </if>
                <if test="salePrice != null">
                    SALE_PRICE=#{salePrice},
                </if>
                <if test="natureType != null">
                    NATURE_TYPE=#{natureType,jdbcType=INTEGER},
                </if>
                <if test="natureCity != null">
                    NATURE_CITY=#{natureCity,jdbcType=INTEGER},
                </if>
                <if test="schoolDegree != null">
                    SCHOOL_DEGREE=#{schoolDegree,jdbcType=INTEGER},
                </if>
                <if test="enrolDegree != null">
                    ENROL_DEGREE=#{enrolDegree,jdbcType=INTEGER},
                </if>
                <if test="imgDegree != null">
                    IMG_DEGREE=#{imgDegree,jdbcType=INTEGER},
                </if>
                <if test="status != null">
                    STATUS=#{status,jdbcType=TINYINT},
                </if>
                <if test="createId != null">
                    CREATE_ID=#{createId,jdbcType=VARCHAR},
                </if>
                <if test="createDate != null">
                    CREATE_DATE=#{createDate,jdbcType=DATE},
                </if>
                <if test="updateId != null">
                    UPDATE_ID=#{updateId,jdbcType=VARCHAR},
                </if>
                <if test="updateDate != null">
                    UPDATE_DATE=#{updateDate,jdbcType=DATE},
                </if>
                <if test="saleCount != null">
                    SALE_COUNT=#{saleCount},
                </if>
            </set>
            WHERE
                ID = #{id,jdbcType=INTEGER}
        </update>
        <select id="selectList" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo" resultMap="SchoolInfo">
            SELECT
                <include refid="Base_Column_List" />
            FROM
                XQ_SCHOOL_INFO
            WHERE
                STATUS = 1
                <if test="areaId != null and areaId != null"> AND AREA_ID = #{areaId} </if>
    
        </select>
    
        <select id="selectSchoolInfoAll" resultMap="SchoolInfo">
            SELECT
                <include refid="Base_Column_List" />
            FROM
                XQ_SCHOOL_INFO
            WHERE
                STATUS = 1
            ORDER BY ID DESC
        </select>
    
        <select id="selectSchoolInfo" parameterType="com.jjshome.esf.common.model.SchoolInfoSearchModel" resultMap="SchoolInfo">
            SELECT
                i.*,
                yc.NAME as 'CITY_NAME',
                ya.NAME as 'AREA_NAME'
            FROM
                XQ_SCHOOL_INFO i
                LEFT JOIN YW_CITY_SETTING yc ON i.CITY_CODE = yc.CODE
                LEFT JOIN YW_CITY_SETTING ya ON i.AREA_CODE = ya.CODE
            WHERE
                i.STATUS = 1
            <if test="city != null and city != '' ">
                AND i.CITY_CODE=#{city}
            </if>
            <if test="area != null and area != '' ">
                AND i.AREA_CODE=#{area}
            </if>
            <if test="schoolId != null and schoolId != ''">
                AND i.ID=#{schoolId}
            </if>
    
            <if test="schoolName != null and schoolName != ''">
                AND i.NAME LIKE concat('%',#{schoolName},'%')
            </if>
    
            <if test="schoolDistrictId != null and schoolDistrictId != ''">
                AND i.AREA_ID=#{schoolDistrictId}
            </if>
            <if test="schoolType != null and schoolType != '' ">
                AND i.TYPE=#{schoolType}
            </if>
            <if test="key != null and key != '' ">
                AND (i.NAME LIKE CONCAT('%',#{key},'%') OR  i.ALIAS LIKE CONCAT('%', #{key}, '%'))
            </if>
            /*priceType == 1：起售价 2：房源售均价*/
            <if test="priceType == 1">
                <if test="salePriceStart != null and salePriceStart != '' ">
                    AND SALE_PRICE >= #{salePriceStart}
                </if>
                <if test="salePriceEnd != null and salePriceEnd != '' ">
                    AND SALE_PRICE <= #{salePriceEnd}
                </if>
            </if>
            <if test="priceType == 2">
                <if test="salePriceStart != null and salePriceStart != '' ">
                    AND UNIT_PRICE >= #{salePriceStart}
                </if>
                <if test="salePriceEnd != null and salePriceEnd != '' ">
                    AND UNIT_PRICE <= #{salePriceEnd}
                </if>
            </if>
            <if test="perfectSituation == 1">
                AND SCHOOL_DEGREE = 100
            </if>
            <if test="perfectSituation == 2">
                AND SCHOOL_DEGREE < 100
            </if>
            ORDER BY ID DESC
    
        </select>
    
        <select id="selectSchoolByNameAndCityAndArea" parameterType="java.util.Map" resultMap="SchoolInfo">
            SELECT
                <include refid="Base_Column_List" />
            FROM
                XQ_SCHOOL_INFO
            WHERE
                STATUS = 1
            AND NAME = #{name}
            AND CITY_CODE=#{city}
            AND AREA_CODE=#{area}
            AND TYPE = #{type}
        </select>
    
        <select id="selectAreaIdAndKeyWord" parameterType="java.util.Map" resultMap="SchoolInfo">
            SELECT
            XSI.*,CYCS.NAME AS  'CITY_NAME',AYCS.NAME AS  'AREA_NAME'
            FROM
                XQ_SCHOOL_INFO XSI
                LEFT JOIN YW_CITY_SETTING CYCS ON  XSI.CITY_CODE = CYCS.CODE
                LEFT JOIN YW_CITY_SETTING AYCS ON XSI.AREA_CODE = AYCS. CODE
            WHERE
                1=1
            <if test="areaId != null and areaId != ''">
                AND XSI.AREA_CODE=#{areaId}
            </if>
            <if test="key != null and key!=''">
                AND (XSI.NAME like CONCAT(#{key},'%' ) or XSI.NAME_SPELL like CONCAT(#{key},'%' ))
            </if>
            AND
            XSI.STATUS=1
            <if test="pageSize != null">
                limit #{pageSize}
            </if>
        </select>
    
    
        <select id="selectAreaIdList" parameterType="map" resultType="integer">
            SELECT DISTINCT AREA_ID FROM XQ_SCHOOL_INFO WHERE NAME LIKE CONCAT('%',#{schoolName},'%')
        </select>
    
    
        <select id="selectSchoolList" parameterType="map" resultMap="SchoolInfo">
            SELECT
            <include refid="Base_Column_List" />
            FROM
            XQ_SCHOOL_INFO
            WHERE
            STATUS = 1
            <if test="idList != null and idList.size gt 0">
                AND ID IN
                <foreach collection="idList"  item="item" index="index" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>
    
            <if test="areaId != null and areaId != null"> AND AREA_ID = #{areaId} </if>
    
        </select>
    </mapper>

[20160909101938116]: /images/20220513/b5ecb36568f247e8a4b9b1647a3ef879.png
[20160909103937243]: /images/20220513/4eeb87f117ff40849e6b79648732df9d.png
[Link 1]: https://gist.github.com/shikaiwen/fbc3259b189035cafc3c2b9fa4f8488d