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

最新推荐文章于 2024-08-27 06:45:00 发布

等待你的蜕变

最新推荐文章于 2024-08-27 06:45:00 发布

阅读量828

点赞数

分类专栏： mybatis

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

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

public interface WorkerMapper extends Mapper<Worker> {
}
   
   1
2
   
   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">
    <!--
      WARNING - @mbggenerated
    -->
    <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
   
   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
   
   1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

这里写图片描述

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

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

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

    <bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        <property name="basePackage" value="com.jjs.zanbi.dao" />
        <property name="properties">
            <value>
                mappers=tk.mybatis.mapper.common.Mapper
            </value>
        </property>
    </bean>
   
   1
2
3
4
5
6
7
8
9
   
   1
2
3
4
5
6
7
8
9

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

原理的简单分析

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

public interface WorkerMapper extends Mapper<Worker> {}
   
   1
   
   1

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

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

}
   
   1
2
3
4
5
6
7
   
   1
2
3
4
5
6
7

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

这里选择一个接口：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
   
   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
   
   1
2
3
4
5
6
7
8
9
10
11
12
13

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

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

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

MapperFactoryBean -> SqlSessionDaoSupport -> DaoSupport
   
   1
   
   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
   
   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
   
   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
   
   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
   
   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
   
   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
   
   1
2
3
4
5
6
7

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

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

    <resultMap id="workerResultMap" type="com.jjs.kaiwen.model.Worker" extends="BaseResultMap">
        <association property="room" columnPrefix="b_"  resultMap="com.jjs.kaiwen.dao.OrgMapper.BaseResultMap"/>
    </resultMap>
   
   1
2
3
   
   1
2
3

更进一步

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

<include refid="user_col_sql_id_with_alias">
<property name="alias" value="t"/>
<property name="prefix" value="t_"/>
</include>
   
   1
2
3
4
   
   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
   
   1
2
3
4
5
6
7
8

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

通用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
   
   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
   
   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
   
   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文件内容

<?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.ISchoolAreaDAO" >
  <resultMap id="BaseResultMap" type="com.jjshome.esf.common.entity.school.SchoolArea" >
    <!--
      WARNING - @mbggenerated
    -->
    <id column="ID" property="id" jdbcType="INTEGER" />
    <result column="CITY_CODE" property="cityCode" jdbcType="VARCHAR" />
    <result column="NAME" property="name" jdbcType="VARCHAR" />
    <result column="NAME_SPELL" property="nameSpell" jdbcType="VARCHAR" />
    <result column="STATUS" property="status" jdbcType="TINYINT" />
    <result column="CREATE_ID" property="createId" jdbcType="VARCHAR" />
    <result column="CREATE_DATE" property="createDate" jdbcType="TIMESTAMP" />
    <result column="UPDATE_ID" property="updateId" jdbcType="VARCHAR" />
    <result column="UPDATE_DATE" property="updateDate" jdbcType="TIMESTAMP" />
    <result column="CITY_NAME" property="cityName"/>
    <result column="PRIMARY_COUNT" property="primaryCount"/>
    <result column="MIDDLE_COUNT" property="middleCount"/>
    <result column="HIGH_COUNT" property="highCount"/>
  </resultMap>

    <resultMap id="SchoolDetailArea" type="com.jjshome.esf.common.entity.school.SchoolAreaDetail"
               extends="com.jjshome.esf.core.dao.school.ISchoolInfoDAO.SchoolInfo">
        <result column="SCHOOL_AREA_NAME" property="schoolAreaName"/>
    </resultMap>

    <select id="selectByPage" parameterType="map" resultMap="BaseResultMap">

        SELECT A.*, C.NAME AS CITY_NAME,
        (SELECT COUNT(*) FROM XQ_SCHOOL_INFO B WHERE A.ID=B.AREA_ID AND B.TYPE='553' AND B.STATUS = 1 ) AS PRIMARY_COUNT,
        (SELECT COUNT(*) FROM XQ_SCHOOL_INFO B WHERE A.ID=B.AREA_ID AND B.TYPE='554' AND B.STATUS = 1 ) AS MIDDLE_COUNT,
        (SELECT COUNT(*) FROM XQ_SCHOOL_INFO B WHERE A.ID=B.AREA_ID AND B.TYPE='555' AND B.STATUS = 1 ) AS HIGH_COUNT
        FROM XQ_SCHOOL_AREA A
        LEFT JOIN YW_CITY_SETTING C ON A.CITY_CODE = C.CODE
        <where>
            <if test="name != null and name != '' "> A.NAME LIKE CONCAT('%',#{NAME},'%')  </if>
            <if test="areaCityCode != null and areaCityCode != '' "> A.CITY_CODE = #{areaCityCode}  </if>
            <if test="keywords != null and keywords != '' ">
                ( A.NAME LIKE CONCAT('%',#{keywords},'%')
                )
            </if>
        </where>
    </select>


    <select id="selectAreaIdAndKeyWord" parameterType="java.util.Map" resultMap="BaseResultMap">
        SELECT
        *
        FROM
        XQ_SCHOOL_AREA
        WHERE
        1=1
        <if test="cityId != null">
            AND CITY_CODE=#{cityId}
        </if>
        <if test="key != null and key!=''">
            AND (NAME like CONCAT(#{key},'%' ) or NAME_SPELL like CONCAT(#{key},'%' ))
        </if>
        AND
        STATUS=1
        <if test="pageSize != null">
            limit #{pageSize}
        </if>
    </select>


    <!--查询学区详情列表-->
    <select id="selectAreaDetailByPage" parameterType="map" resultMap="SchoolDetailArea">

        SELECT A.* ,B.NAME AS SCHOOL_AREA_NAME ,C.NAME AS CITY_NAME,D.NAME AS AREA_NAME FROM XQ_SCHOOL_INFO A
        LEFT JOIN XQ_SCHOOL_AREA B ON A.AREA_ID = B.ID
        LEFT JOIN YW_CITY_SETTING C ON A.CITY_CODE = C.CODE
        LEFT JOIN YW_CITY_SETTING D ON A.AREA_CODE = D.CODE

        WHERE A.STATUS = 1 AND B.STATUS =1
        <if test="areaId != null and areaId.length() &gt; 0">  AND A.AREA_ID = #{areaId} </if>
        <if test="typeList != null and typeList.size &gt; 0">
            AND
            A.TYPE IN
            <foreach collection="typeList"  item="item" index="index" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="name != null and name != '' "> AND   A.NAME LIKE CONCAT('%',#{name},'%')  </if>
    </select>

</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
   
   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 &gt;= #{salePriceStart}
            </if>
            <if test="salePriceEnd != null and salePriceEnd != '' ">
                AND SALE_PRICE &lt;= #{salePriceEnd}
            </if>
        </if>
        <if test="priceType == 2">
            <if test="salePriceStart != null and salePriceStart != '' ">
                AND UNIT_PRICE &gt;= #{salePriceStart}
            </if>
            <if test="salePriceEnd != null and salePriceEnd != '' ">
                AND UNIT_PRICE &lt;= #{salePriceEnd}
            </if>
        </if>
        <if test="perfectSituation == 1">
            AND SCHOOL_DEGREE = 100
        </if>
        <if test="perfectSituation == 2">
            AND SCHOOL_DEGREE &lt; 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>


   
   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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
   
   
    
    
   
   
   
   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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598