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

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

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

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

xml映射文件

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  3. <mapper namespace="com.jjs.kaiwen.dao.WorkerMapper">
  4.   <resultMap id="BaseResultMap" type="com.jjs.kaiwen.model.Worker">
  5.     <!--
  6.       WARNING - @mbggenerated
  7.     -->
  8.     <id column="id" jdbcType="INTEGER" property="id" />
  9.     <result column="worker_id" jdbcType="VARCHAR" property="workerId" />
  10.     <result column="name" jdbcType="VARCHAR" property="name" />
  11.     <result column="org_id" jdbcType="INTEGER" property="orgId" />
  12.     <result column="status" jdbcType="VARCHAR" property="status" />
  13.     <result column="role_id" property="roleId" jdbcType="INTEGER" />
  14.   </resultMap>
  15. </mapper>

实体对象

  1. @Table(name = "worker")
  2. public class Worker {
  3.     @Id
  4.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  5.     private Integer id;
  6.  
  7.     @Column(name = "worker_id")
  8.     private String workerId;
  9.  
  10.     private String name;
  11.  
  12.     @Column(name = "org_id")
  13.     private Integer orgId;
  14.  
  15.     private String status;
  16.  
  17.     @Column(name = "role_id")
  18.     private Integer roleId;
  19.  
  20.     // getters and setters ...
  21.     }

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

这里写图片描述

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

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

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

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

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

原理的简单分析

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

public interface WorkerMapper extends Mapper<Worker> {}

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

  1. public interface Mapper<T> extends
  2.         BaseMapper<T>,
  3.         ExampleMapper<T>,
  4.         RowBoundsMapper<T>,
  5.         Marker {
  6.  
  7. }

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

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

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

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

  1. public class BaseSelectProvider extends MapperTemplate {
  2.  
  3.     public String selectOne(MappedStatement ms) {
  4.         Class<?> entityClass = getEntityClass(ms);
  5.         //修改返回值类型为实体类型
  6.         setResultType(ms, entityClass);
  7.         StringBuilder sql = new StringBuilder();
  8.         sql.append(SqlHelper.selectAllColumns(entityClass));
  9.         sql.append(SqlHelper.fromTable(entityClass, tableName(entityClass)));
  10.         sql.append(SqlHelper.whereAllIfColumns(entityClass, isNotEmpty()));
  11.         return sql.toString();
  12.     }
  13. }

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

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

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

MapperFactoryBean -> SqlSessionDaoSupport -> DaoSupport

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

  1. public abstract class DaoSupport implements InitializingBean {
  2.  
  3. // spring 完成属性设置后会调用此方法
  4.     @Override
  5.     public final void afterPropertiesSet() throws IllegalArgumentException, BeanInitializationException {
  6.         // 这里提供了接口供子类去实现
  7.         checkDaoConfig();
  8.  
  9.         // Let concrete implementations initialize themselves.
  10.         try {
  11.             initDao();
  12.         }
  13.         catch (Exception ex) {
  14.             throw new BeanInitializationException("Initialization of DAO failed", ex);
  15.         }
  16.     }
  17.  
  18.     protected abstract void checkDaoConfig() throws IllegalArgumentException;
  19.  
  20.     protected void initDao() throws Exception {
  21.     }
  22.  
  23. }

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

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

MapperHelper的processConfiguration方法如下

  1.     public void processConfiguration(Configuration configuration, Class<?> mapperInterface) {
  2.         String prefix;
  3.         if (mapperInterface != null) {
  4.             prefix = mapperInterface.getCanonicalName();
  5.         } else {
  6.             prefix = "";
  7.         }
  8.         for (Object object : new ArrayList<Object>(configuration.getMappedStatements())) {
  9.             if (object instanceof MappedStatement) {
  10.                 MappedStatement ms = (MappedStatement) object;
  11.                 //检查这个MappedStatement是否属于此映射对象
  12.                 if (ms.getId().startsWith(prefix) && isMapperMethod(ms.getId())) {
  13.                     if (ms.getSqlSource() instanceof ProviderSqlSource) {
  14.                        //去设置该statement的sql语句
  15.                         setSqlSource(ms);
  16.                     }
  17.                 }
  18.             }
  19.         }
  20.     }

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

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

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

关于mybatis新特性

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

  1. select a.id,a.name,b.bid,b.bname .....
  2. from user a 
  3. left join room b 
原始的方式是将所有的表字段列出来，再来看用新特性怎样做
  1. select id="selectUsers" resultType="map">
  2.   select
  3.     <include refid="user_col_sql_id"><property name="alias" value="t1"/>,
  4.     <include refid="room_col_sql_id"><property name="alias" value="t2"/>
  5.   from user t1
  6.     left join room t2
  7. </select>
这里主要就是对基本的sql进行了复用，如果对表进行了修改只要在原始的sql节点修改就可以了，就算是5个表的联合查询，sql也是清晰易懂，维护起来会更轻松
新版本的mybatis对于对象映射也提供了更友好的方式，直接使用外部的ResultMap再加上查询语句中的别名就映射完成了
  1.     <resultMap id="workerResultMap" type="com.jjs.kaiwen.model.Worker" extends="BaseResultMap">
  2.         <association property="room" columnPrefix="b_"  resultMap="com.jjs.kaiwen.dao.OrgMapper.BaseResultMap"/>
  3.     </resultMap>
更进一步
敏锐的程序员可能会提出问题，如当多表查询的时候可能会存在字段名称相同的情况，这里的解决方案是给include添加另一个属性
  1. <include refid="user_col_sql_id_with_alias">
  2. <property name="alias" value="t"/>
  3. <property name="prefix" value="t_"/>
  4. </include>
包含prefix的sqlNode如下
  1.     <sql id="base_column_with_alias">
  2.         ${alias}.ID as ${prefix}ID,
  3.         ${alias}.WORKER_ID as ${prefix}WORKER_ID,
  4.         ${alias}.NAME as ${prefix}NAME,
  5.         ${alias}.ZB_ROLE_ID as ${prefix}ZB_ROLE_ID,
  6.         ${alias}.ORG_ID as ${prefix}ORG_ID,
  7.         ${alias}.STATUS as ${prefix}STATUS
  8.     </sql>
如果说觉得手动写包含alias和prefix的字段麻烦，可以用，mybatis代码生成器的插件的方式实现，我自己写了一个生成器的插件，可以代码再这里，仅供参考
通用Service类
  1. /**
  2.  * Created by Kaiwen
  3.  */
  4. @Service
  5. public abstract class CommonServiceImpl<T,PK extends Serializable> implements CommonService<T,PK> {
  6.     /**
  7.      * 泛型注入
  8.      */
  9.     @Autowired
  10.     private Mapper<T> mapper;
  11.  
  12.     public T selectByPrimaryKey(PK entityId) {
  13.  
  14.         return mapper.selectByPrimaryKey(entityId);
  15.     }
  16.  
  17.     public int deleteByPrimaryKey(PK entityId) {
  18.         return mapper.deleteByPrimaryKey(entityId);
  19.     }
  20.  
  21.     public int insert(T record) {
  22.         return mapper.insert(record);
  23.     }
  24.  
  25.     public int insertSelective(T record) {
  26.         return mapper.insertSelective(record);
  27.     }
  28.  
  29.     public int updateByPrimaryKeySelective(T record) {
  30.         return mapper.updateByPrimaryKeySelective(record);
  31.     }
  32.  
  33.     public int updateByPrimaryKey(T record) {
  34.         return mapper.updateByPrimaryKey(record);
  35.     }
  36.  
  37.     public List<T> selectByExample(Example example) {
  38.         return mapper.selectByExample(example);
  39.     }
  40. }
注入方式区别
  1.     <bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
  2.         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
  3.         <property name="basePackage" value="com.jjshome.esf.core.dao.school" />
  4.         <property name="properties">
  5.             <value>
  6.                 mappers=tk.mybatis.mapper.common.Mapper
  7.             </value>
  8.         </property>
  9.     </bean>
  10.  
  11.  
  12.     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  13.         <property name="basePackage" value="com.jjshome.esf.core.dao.community,com.jjshome.esf.core.dao.hsl"/>
  14.     </bean>
<
实体类
  1. package com.jjshome.esf.common.entity.school;
  2.  
  3. import java.util.Date;
  4. import javax.persistence.*;
  5.  
  6. @Table(name = "XQ_SCHOOL_AREA")
  7. public class SchoolArea {
  8.     /**
  9.      * 主键ID
  10.      */
  11.     @Id
  12.     @Column(name = "ID")
  13.     private Integer id;
  14.  
  15.     /**
  16.      * 城市编码
  17.      */
  18.     @Column(name = "CITY_CODE")
  19.     private String cityCode;
  20.  
  21.     /**
  22.      * 学区名称
  23.      */
  24.     @Column(name = "NAME")
  25.     private String name;
  26.  
  27.     /**
  28.      * 学区名称拼音
  29.      */
  30.     @Column(name = "NAME_SPELL")
  31.     private String nameSpell;
  32.  
  33.     /**
  34.      * 状态,1:正常,0:删除
  35.      */
  36.     @Column(name = "STATUS")
  37.     private Byte status;
  38.  
  39.     /**
  40.      * 添加人
  41.      */
  42.     @Column(name = "CREATE_ID")
  43.     private String createId;
  44.  
  45.  
  46.     @Transient
  47.     private Integer primaryCount; //小学数量
  48.     @Transient
  49.     private Integer middleCount; //初中数量
  50.     @Transient
  51.     private Integer highCount;//高中数量
TK mybatis Mapper文件内容
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  3. <mapper namespace="com.jjshome.esf.core.dao.school.ISchoolAreaDAO" >
  4.   <resultMap id="BaseResultMap" type="com.jjshome.esf.common.entity.school.SchoolArea" >
  5.     <!--
  6.       WARNING - @mbggenerated
  7.     -->
  8.     <id column="ID" property="id" jdbcType="INTEGER" />
  9.     <result column="CITY_CODE" property="cityCode" jdbcType="VARCHAR" />
  10.     <result column="NAME" property="name" jdbcType="VARCHAR" />
  11.     <result column="NAME_SPELL" property="nameSpell" jdbcType="VARCHAR" />
  12.     <result column="STATUS" property="status" jdbcType="TINYINT" />
  13.     <result column="CREATE_ID" property="createId" jdbcType="VARCHAR" />
  14.     <result column="CREATE_DATE" property="createDate" jdbcType="TIMESTAMP" />
  15.     <result column="UPDATE_ID" property="updateId" jdbcType="VARCHAR" />
  16.     <result column="UPDATE_DATE" property="updateDate" jdbcType="TIMESTAMP" />
  17.     <result column="CITY_NAME" property="cityName"/>
  18.     <result column="PRIMARY_COUNT" property="primaryCount"/>
  19.     <result column="MIDDLE_COUNT" property="middleCount"/>
  20.     <result column="HIGH_COUNT" property="highCount"/>
  21.   </resultMap>
  22.  
  23.     <resultMap id="SchoolDetailArea" type="com.jjshome.esf.common.entity.school.SchoolAreaDetail"
  24.                extends="com.jjshome.esf.core.dao.school.ISchoolInfoDAO.SchoolInfo">
  25.         <result column="SCHOOL_AREA_NAME" property="schoolAreaName"/>
  26.     </resultMap>
  27.  
  28.     <select id="selectByPage" parameterType="map" resultMap="BaseResultMap">
  29.  
  30.         SELECT A.*, C.NAME AS CITY_NAME,
  31.         (SELECT COUNT(*) FROM XQ_SCHOOL_INFO B WHERE A.ID=B.AREA_ID AND B.TYPE='553' AND B.STATUS = 1 ) AS PRIMARY_COUNT,
  32.         (SELECT COUNT(*) FROM XQ_SCHOOL_INFO B WHERE A.ID=B.AREA_ID AND B.TYPE='554' AND B.STATUS = 1 ) AS MIDDLE_COUNT,
  33.         (SELECT COUNT(*) FROM XQ_SCHOOL_INFO B WHERE A.ID=B.AREA_ID AND B.TYPE='555' AND B.STATUS = 1 ) AS HIGH_COUNT
  34.         FROM XQ_SCHOOL_AREA A
  35.         LEFT JOIN YW_CITY_SETTING C ON A.CITY_CODE = C.CODE
  36.         <where>
  37.             <if test="name != null and name != '' "> A.NAME LIKE CONCAT('%',#{NAME},'%')  </if>
  38.             <if test="areaCityCode != null and areaCityCode != '' "> A.CITY_CODE = #{areaCityCode}  </if>
  39.             <if test="keywords != null and keywords != '' ">
  40.                 ( A.NAME LIKE CONCAT('%',#{keywords},'%')
  41.                 )
  42.             </if>
  43.         </where>
  44.     </select>
  45.  
  46.  
  47.     <select id="selectAreaIdAndKeyWord" parameterType="java.util.Map" resultMap="BaseResultMap">
  48.         SELECT
  49.         *
  50.         FROM
  51.         XQ_SCHOOL_AREA
  52.         WHERE
  53.         1=1
  54.         <if test="cityId != null">
  55.             AND CITY_CODE=#{cityId}
  56.         </if>
  57.         <if test="key != null and key!=''">
  58.             AND (NAME like CONCAT(#{key},'%' ) or NAME_SPELL like CONCAT(#{key},'%' ))
  59.         </if>
  60.         AND
  61.         STATUS=1
  62.         <if test="pageSize != null">
  63.             limit #{pageSize}
  64.         </if>
  65.     </select>
  66.  
  67.  
  68.     <!--查询学区详情列表-->
  69.     <select id="selectAreaDetailByPage" parameterType="map" resultMap="SchoolDetailArea">
  70.  
  71.         SELECT A.* ,B.NAME AS SCHOOL_AREA_NAME ,C.NAME AS CITY_NAME,D.NAME AS AREA_NAME FROM XQ_SCHOOL_INFO A
  72.         LEFT JOIN XQ_SCHOOL_AREA B ON A.AREA_ID = B.ID
  73.         LEFT JOIN YW_CITY_SETTING C ON A.CITY_CODE = C.CODE
  74.         LEFT JOIN YW_CITY_SETTING D ON A.AREA_CODE = D.CODE
  75.  
  76.         WHERE A.STATUS = 1 AND B.STATUS =1
  77.         <if test="areaId != null and areaId.length() &gt; 0">  AND A.AREA_ID = #{areaId} </if>
  78.         <if test="typeList != null and typeList.size &gt; 0">
  79.             AND
  80.             A.TYPE IN
  81.             <foreach collection="typeList"  item="item" index="index" open="(" close=")" separator=",">
  82.                 #{item}
  83.             </foreach>
  84.         </if>
  85.         <if test="name != null and name != '' "> AND   A.NAME LIKE CONCAT('%',#{name},'%')  </if>
  86.     </select>
  87.  
  88. </mapper>
普通mybatisMapper文件
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  3. <mapper namespace="com.jjshome.esf.core.dao.school.ISchoolInfoDAO">
  4.     <resultMap id="SchoolInfo" type="com.jjshome.esf.common.entity.school.SchoolInfo">
  5.         <id column="ID" property="id"/>
  6.         <result column="NAME" property="name"/>
  7.         <result column="NAME_SPELL" property="nameSpell"/>
  8.         <result column="ALIAS" property="alias"/>
  9.         <result column="ALIAS_SPELL" property="aliasSpell"/>
  10.         <result column="TYPE" property="type" typeHandler="com.jjshome.esf.core.component.handler.DictValueTypeHandler"/>
  11.         <result column="AREA_ID" property="areaId"/>
  12.         <result column="CITY_CODE" property="cityCode"/>
  13.         <result column="AREA_CODE" property="areaCode"/>
  14.         <result column="ADDR" property="addr"/>
  15.         <result column="START_TIME" property="startTime"/>
  16.         <result column="MOTTO" property="motto"/>
  17.         <result column="WEB_SITE" property="webSite"/>
  18.         <result column="PHONE" property="phone"/>
  19.         <result column="FEATURE" property="feature" typeHandler="com.jjshome.esf.core.component.handler.DictValueListTypeHandler"/>
  20.         <result column="LNG" property="lng"/>
  21.         <result column="LAT" property="lat"/>
  22.         <result column="UNIT_PRICE" property="unitPrice"/>
  23.         <result column="SALE_PRICE" property="salePrice"/>
  24.         <result column="NATURE_TYPE" property="natureType" typeHandler="com.jjshome.esf.core.component.handler.DictValueTypeHandler"/>
  25.         <result column="NATURE_CITY" property="natureCity" typeHandler="com.jjshome.esf.core.component.handler.DictValueTypeHandler"/>
  26.         <result column="SCHOOL_DEGREE" property="schoolDegree"/>
  27.         <result column="ENROL_DEGREE" property="enrolDegree"/>
  28.         <result column="IMG_DEGREE" property="imgDegree"/>
  29.         <result column="STATUS" property="status"/>
  30.         <result column="CREATE_ID" property="createId"/>
  31.         <result column="CREATE_DATE" property="createDate"/>
  32.         <result column="UPDATE_ID" property="updateId"/>
  33.         <result column="UPDATE_DATE" property="updateDate"/>
  34.  
  35.         <result column="CITY_NAME" property="cityName" />
  36.         <result column="AREA_NAME" property="areaName" />
  37.         <result column="SCHOOL_DISTRICT_NAME" property="schoolDistrictName" />
  38.         <result column="SALE_COUNT" property="saleCount" />
  39.     </resultMap>
  40.  
  41.     <sql id="Base_Column_List">
  42.         ID,
  43.         NAME,
  44.         NAME_SPELL,
  45.         ALIAS,
  46.         ALIAS_SPELL,
  47.         TYPE,
  48.         AREA_ID,
  49.         CITY_CODE,
  50.         AREA_CODE,
  51.         ADDR,
  52.         START_TIME,
  53.         MOTTO,
  54.         WEB_SITE,
  55.         PHONE,
  56.         FEATURE,
  57.         LNG,
  58.         LAT,
  59.         UNIT_PRICE,
  60.         SALE_PRICE,
  61.         NATURE_TYPE,
  62.         NATURE_CITY,
  63.         SCHOOL_DEGREE,
  64.         ENROL_DEGREE,
  65.         IMG_DEGREE,
  66.         STATUS,
  67.         CREATE_ID,
  68.         CREATE_DATE,
  69.         UPDATE_ID,
  70.         UPDATE_DATE,
  71.         SALE_COUNT,
  72.         SALE_COUNT
  73.     </sql>
  74.  
  75.     <select id="selectById" resultMap="SchoolInfo" parameterType="java.lang.Integer">
  76.         SELECT
  77.             i.*,
  78.             yc.NAME as 'CITY_NAME',
  79.             ya.NAME as 'AREA_NAME',
  80.             xq.NAME as 'SCHOOL_DISTRICT_NAME'
  81.         FROM
  82.             XQ_SCHOOL_INFO i
  83.             LEFT JOIN YW_CITY_SETTING yc ON i.CITY_CODE = yc.CODE
  84.             LEFT JOIN YW_CITY_SETTING ya ON i.AREA_CODE = ya.CODE
  85.             LEFT JOIN XQ_SCHOOL_AREA xq ON i.AREA_ID = xq.ID
  86.         WHERE
  87.             i.ID = #{id,jdbcType=INTEGER}
  88.     </select>
  89.  
  90.     <delete id="deleteById" parameterType="java.util.Map">
  91.         UPDATE
  92.             XQ_SCHOOL_INFO
  93.         SET
  94.             STATUS = 0,
  95.             UPDATE_ID = #{updateId},
  96.             UPDATE_DATE = NOW()
  97.         WHERE
  98.             ID = #{id,jdbcType=INTEGER}
  99.     </delete>
  100.  
  101.     <delete id="batchDeleteByIds" parameterType="java.util.Map">
  102.         UPDATE
  103.             XQ_SCHOOL_INFO
  104.         SET
  105.             STATUS = 0,
  106.             UPDATE_ID = #{updateId},
  107.             UPDATE_DATE = NOW()
  108.         WHERE
  109.         ID IN (${ids})
  110.     </delete>
  111.  
  112.     <update id="deleteAreaRelation" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
  113.         update XQ_SCHOOL_INFO
  114.             SET AREA_ID = NULL,
  115.             UPDATE_DATE = NOW()
  116.         WHERE
  117.         ID = #{id}
  118.     </update>
  119.  
  120.     <insert id="insert" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
  121.         <selectKey resultType="Integer" keyProperty="id">
  122.             SELECT LAST_INSERT_ID()
  123.         </selectKey>
  124.         INSERT INTO XQ_SCHOOL_INFO
  125.             (NAME,
  126.             NAME_SPELL,
  127.             ALIAS,
  128.             ALIAS_SPELL,
  129.             TYPE,
  130.             AREA_ID,
  131.             CITY_CODE,
  132.             AREA_CODE,
  133.             ADDR,
  134.             START_TIME,
  135.             MOTTO,
  136.             WEB_SITE,
  137.             PHONE,
  138.             FEATURE,
  139.             LNG,
  140.             LAT,
  141.             UNIT_PRICE,
  142.             SALE_PRICE,
  143.             NATURE_TYPE,
  144.             NATURE_CITY,
  145.             SCHOOL_DEGREE,
  146.             ENROL_DEGREE,
  147.             IMG_DEGREE,
  148.             STATUS,
  149.             CREATE_ID,
  150.             CREATE_DATE,
  151.             UPDATE_ID,
  152.             UPDATE_DATE)
  153.         VALUES
  154.             (#{name,jdbcType=VARCHAR},
  155.             #{nameSpell,jdbcType=VARCHAR},
  156.             #{alias,jdbcType=VARCHAR},
  157.             #{aliasSpell,jdbcType=VARCHAR},
  158.             #{type,jdbcType=INTEGER},
  159.             #{areaId,jdbcType=INTEGER},
  160.             #{cityCode,jdbcType=VARCHAR},
  161.             #{areaCode,jdbcType=VARCHAR},
  162.             #{addr,jdbcType=VARCHAR},
  163.             #{startTime,jdbcType=DATE},
  164.             #{motto,jdbcType=VARCHAR},
  165.             #{webSite,jdbcType=VARCHAR},
  166.             #{phone,jdbcType=VARCHAR},
  167.             #{feature,jdbcType=VARCHAR},
  168.             #{lng,jdbcType=DECIMAL},
  169.             #{lat,jdbcType=DECIMAL},
  170.             #{unitPrice},
  171.             #{salePrice},
  172.             #{natureType,jdbcType=INTEGER},
  173.             #{natureCity,jdbcType=INTEGER},
  174.             #{schoolDegree,jdbcType=INTEGER},
  175.             #{enrolDegree,jdbcType=INTEGER},
  176.             #{imgDegree,jdbcType=INTEGER},
  177.             #{status,jdbcType=TINYINT},
  178.             #{createId,jdbcType=VARCHAR},
  179.             #{createDate,jdbcType=DATE},
  180.             #{updateId,jdbcType=VARCHAR},
  181.             #{updateDate,jdbcType=DATE})
  182.     </insert>
  183.     <insert id="insertSelective" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
  184.         <selectKey resultType="Integer" keyProperty="id">
  185.             SELECT LAST_INSERT_ID()
  186.         </selectKey>
  187.         INSERT INTO XQ_SCHOOL_INFO
  188.         <trim prefix="(" suffix=")" suffixOverrides=",">
  189.             <if test="name != null">
  190.                 NAME,
  191.             </if>
  192.             <if test="nameSpell != null">
  193.                 NAME_SPELL,
  194.             </if>
  195.             <if test="alias != null">
  196.                 ALIAS,
  197.             </if>
  198.             <if test="aliasSpell != null">
  199.                 ALIAS_SPELL,
  200.             </if>
  201.             <if test="type != null">
  202.                 TYPE,
  203.             </if>
  204.             <if test="areaId != null">
  205.                 AREA_ID,
  206.             </if>
  207.             <if test="cityCode != null">
  208.                 CITY_CODE,
  209.             </if>
  210.             <if test="areaCode != null">
  211.                 AREA_CODE,
  212.             </if>
  213.             <if test="addr != null">
  214.                 ADDR,
  215.             </if>
  216.             <if test="startTime != null">
  217.                 START_TIME,
  218.             </if>
  219.             <if test="motto != null">
  220.                 MOTTO,
  221.             </if>
  222.             <if test="webSite != null">
  223.                 WEB_SITE,
  224.             </if>
  225.             <if test="phone != null">
  226.                 PHONE,
  227.             </if>
  228.             <if test="feature != null">
  229.                 FEATURE,
  230.             </if>
  231.             <if test="lng != null">
  232.                 LNG,
  233.             </if>
  234.             <if test="lat != null">
  235.                 LAT,
  236.             </if>
  237.             <if test="UNIT_PRICE != null">
  238.                 UNIT_PRICE,
  239.             </if>
  240.             <if test="SALE_PRICE != null ">
  241.                 SALE_PRICE,
  242.             </if>
  243.             <if test="natureType != null">
  244.                 NATURE_TYPE,
  245.             </if>
  246.             <if test="natureCity != null">
  247.                 NATURE_CITY,
  248.             </if>
  249.             <if test="schoolDegree != null">
  250.                 SCHOOL_DEGREE,
  251.             </if>
  252.             <if test="enrolDegree != null">
  253.                 ENROL_DEGREE,
  254.             </if>
  255.             <if test="imgDegree != null">
  256.                 IMG_DEGREE,
  257.             </if>
  258.             <if test="status != null">
  259.                 STATUS,
  260.             </if>
  261.             <if test="createId != null">
  262.                 CREATE_ID,
  263.             </if>
  264.             <if test="createDate != null">
  265.                 CREATE_DATE,
  266.             </if>
  267.             <if test="updateId != null">
  268.                 UPDATE_ID,
  269.             </if>
  270.             <if test="updateDate != null">
  271.                 UPDATE_DATE,
  272.             </if>
  273.         </trim>
  274.         <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
  275.             <if test="name != null">
  276.                 #{name,jdbcType=VARCHAR},
  277.             </if>
  278.             <if test="nameSpell != null">
  279.                 #{nameSpell,jdbcType=VARCHAR},
  280.             </if>
  281.             <if test="alias != null">
  282.                 #{alias,jdbcType=VARCHAR},
  283.             </if>
  284.             <if test="aliasSpell != null">
  285.                 #{aliasSpell,jdbcType=VARCHAR},
  286.             </if>
  287.             <if test="type != null">
  288.                 #{type,jdbcType=INTEGER},
  289.             </if>
  290.             <if test="areaId != null">
  291.                 #{areaId,jdbcType=INTEGER},
  292.             </if>
  293.             <if test="cityCode != null">
  294.                 #{cityCode,jdbcType=VARCHAR},
  295.             </if>
  296.             <if test="areaCode != null">
  297.                 #{areaCode,jdbcType=VARCHAR},
  298.             </if>
  299.             <if test="addr != null">
  300.                 #{addr,jdbcType=VARCHAR},
  301.             </if>
  302.             <if test="startTime != null">
  303.                 #{startTime,jdbcType=DATE},
  304.             </if>
  305.             <if test="motto != null">
  306.                 #{motto,jdbcType=VARCHAR},
  307.             </if>
  308.             <if test="webSite != null">
  309.                 #{webSite,jdbcType=VARCHAR},
  310.             </if>
  311.             <if test="phone != null">
  312.                 #{phone,jdbcType=VARCHAR},
  313.             </if>
  314.             <if test="feature != null">
  315.                 #{feature,jdbcType=VARCHAR},
  316.             </if>
  317.             <if test="lng != null">
  318.                 #{lng,jdbcType=DECIMAL},
  319.             </if>
  320.             <if test="lat != null">
  321.                 #{lat,jdbcType=DECIMAL},
  322.             </if>
  323.             <if test="unitPrice ! =null">
  324.                 #{unitPrice},
  325.             </if>
  326.             <if test="salePrice">
  327.                 #{salePrice},
  328.             </if>
  329.             <if test="natureType != null">
  330.                 #{natureType,jdbcType=INTEGER},
  331.             </if>
  332.             <if test="natureCity != null">
  333.                 #{natureCity,jdbcType=INTEGER},
  334.             </if>
  335.             <if test="schoolDegree != null">
  336.                 #{schoolDegree,jdbcType=INTEGER},
  337.             </if>
  338.             <if test="enrolDegree != null">
  339.                 #{enrolDegree,jdbcType=INTEGER},
  340.             </if>
  341.             <if test="imgDegree != null">
  342.                 #{imgDegree,jdbcType=INTEGER},
  343.             </if>
  344.             <if test="status != null">
  345.                 #{status,jdbcType=TINYINT},
  346.             </if>
  347.             <if test="createId != null">
  348.                 #{createId,jdbcType=VARCHAR},
  349.             </if>
  350.             <if test="createDate != null">
  351.                 #{createDate,jdbcType=DATE},
  352.             </if>
  353.             <if test="updateId != null">
  354.                 #{updateId,jdbcType=VARCHAR},
  355.             </if>
  356.             <if test="updateDate != null">
  357.                 #{updateDate,jdbcType=DATE},
  358.             </if>
  359.         </trim>
  360.     </insert>
  361.     <update id="updateSelective" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo">
  362.         UPDATE XQ_SCHOOL_INFO
  363.         <set>
  364.             <if test="name != null">
  365.                 NAME=#{name,jdbcType=VARCHAR},
  366.             </if>
  367.             <if test="nameSpell != null">
  368.                 NAME_SPELL=#{nameSpell,jdbcType=VARCHAR},
  369.             </if>
  370.             <if test="alias != null">
  371.                 ALIAS=#{alias,jdbcType=VARCHAR},
  372.             </if>
  373.             <if test="aliasSpell != null">
  374.                 ALIAS_SPELL=#{aliasSpell,jdbcType=VARCHAR},
  375.             </if>
  376.             <if test="type != null">
  377.                 TYPE=#{type,jdbcType=INTEGER},
  378.             </if>
  379.             <if test="type != null">
  380.                 AREA_ID=#{areaId,jdbcType=INTEGER},
  381.             </if>
  382.  
  383.             <if test="cityCode != null">
  384.                 CITY_CODE=#{cityCode,jdbcType=VARCHAR},
  385.             </if>
  386.             <if test="areaCode != null">
  387.                 AREA_CODE=#{areaCode,jdbcType=VARCHAR},
  388.             </if>
  389.             <if test="addr != null">
  390.                 ADDR=#{addr,jdbcType=VARCHAR},
  391.             </if>
  392.             <if test="startTime != null">
  393.                 START_TIME=#{startTime,jdbcType=DATE},
  394.             </if>
  395.             <if test="motto != null">
  396.                 MOTTO=#{motto,jdbcType=VARCHAR},
  397.             </if>
  398.             <if test="webSite != null">
  399.                 WEB_SITE=#{webSite,jdbcType=VARCHAR},
  400.             </if>
  401.             <if test="phone != null">
  402.                 PHONE=#{phone,jdbcType=VARCHAR},
  403.             </if>
  404.             <if test="feature != null">
  405.                 FEATURE=#{feature,jdbcType=VARCHAR},
  406.             </if>
  407.             <if test="lng != null">
  408.                 LNG=#{lng,jdbcType=DECIMAL},
  409.             </if>
  410.             <if test="lat != null">
  411.                 LAT=#{lat,jdbcType=DECIMAL},
  412.             </if>
  413.             <if test="salePrice != null">
  414.                 UNIT_PRICE=#{unitPrice},
  415.             </if>
  416.             <if test="salePrice != null">
  417.                 SALE_PRICE=#{salePrice},
  418.             </if>
  419.             <if test="natureType != null">
  420.                 NATURE_TYPE=#{natureType,jdbcType=INTEGER},
  421.             </if>
  422.             <if test="natureCity != null">
  423.                 NATURE_CITY=#{natureCity,jdbcType=INTEGER},
  424.             </if>
  425.             <if test="schoolDegree != null">
  426.                 SCHOOL_DEGREE=#{schoolDegree,jdbcType=INTEGER},
  427.             </if>
  428.             <if test="enrolDegree != null">
  429.                 ENROL_DEGREE=#{enrolDegree,jdbcType=INTEGER},
  430.             </if>
  431.             <if test="imgDegree != null">
  432.                 IMG_DEGREE=#{imgDegree,jdbcType=INTEGER},
  433.             </if>
  434.             <if test="status != null">
  435.                 STATUS=#{status,jdbcType=TINYINT},
  436.             </if>
  437.             <if test="createId != null">
  438.                 CREATE_ID=#{createId,jdbcType=VARCHAR},
  439.             </if>
  440.             <if test="createDate != null">
  441.                 CREATE_DATE=#{createDate,jdbcType=DATE},
  442.             </if>
  443.             <if test="updateId != null">
  444.                 UPDATE_ID=#{updateId,jdbcType=VARCHAR},
  445.             </if>
  446.             <if test="updateDate != null">
  447.                 UPDATE_DATE=#{updateDate,jdbcType=DATE},
  448.             </if>
  449.             <if test="saleCount != null">
  450.                 SALE_COUNT=#{saleCount},
  451.             </if>
  452.         </set>
  453.         WHERE
  454.             ID = #{id,jdbcType=INTEGER}
  455.     </update>
  456.     <select id="selectList" parameterType="com.jjshome.esf.common.entity.school.SchoolInfo" resultMap="SchoolInfo">
  457.         SELECT
  458.             <include refid="Base_Column_List" />
  459.         FROM
  460.             XQ_SCHOOL_INFO
  461.         WHERE
  462.             STATUS = 1
  463.             <if test="areaId != null and areaId != null"> AND AREA_ID = #{areaId} </if>
  464.  
  465.     </select>
  466.  
  467.     <select id="selectSchoolInfoAll" resultMap="SchoolInfo">
  468.         SELECT
  469.             <include refid="Base_Column_List" />
  470.         FROM
  471.             XQ_SCHOOL_INFO
  472.         WHERE
  473.             STATUS = 1
  474.         ORDER BY ID DESC
  475.     </select>
  476.  
  477.     <select id="selectSchoolInfo" parameterType="com.jjshome.esf.common.model.SchoolInfoSearchModel" resultMap="SchoolInfo">
  478.         SELECT
  479.             i.*,
  480.             yc.NAME as 'CITY_NAME',
  481.             ya.NAME as 'AREA_NAME'
  482.         FROM
  483.             XQ_SCHOOL_INFO i
  484.             LEFT JOIN YW_CITY_SETTING yc ON i.CITY_CODE = yc.CODE
  485.             LEFT JOIN YW_CITY_SETTING ya ON i.AREA_CODE = ya.CODE
  486.         WHERE
  487.             i.STATUS = 1
  488.         <if test="city != null and city != '' ">
  489.             AND i.CITY_CODE=#{city}
  490.         </if>
  491.         <if test="area != null and area != '' ">
  492.             AND i.AREA_CODE=#{area}
  493.         </if>
  494.         <if test="schoolId != null and schoolId != ''">
  495.             AND i.ID=#{schoolId}
  496.         </if>
  497.  
  498.         <if test="schoolName != null and schoolName != ''">
  499.             AND i.NAME LIKE concat('%',#{schoolName},'%')
  500.         </if>
  501.  
  502.         <if test="schoolDistrictId != null and schoolDistrictId != ''">
  503.             AND i.AREA_ID=#{schoolDistrictId}
  504.         </if>
  505.         <if test="schoolType != null and schoolType != '' ">
  506.             AND i.TYPE=#{schoolType}
  507.         </if>
  508.         <if test="key != null and key != '' ">
  509.             AND (i.NAME LIKE CONCAT('%',#{key},'%') OR  i.ALIAS LIKE CONCAT('%', #{key}, '%'))
  510.         </if>
  511.         /*priceType == 1：起售价 2：房源售均价*/
  512.         <if test="priceType == 1">
  513.             <if test="salePriceStart != null and salePriceStart != '' ">
  514.                 AND SALE_PRICE &gt;= #{salePriceStart}
  515.             </if>
  516.             <if test="salePriceEnd != null and salePriceEnd != '' ">
  517.                 AND SALE_PRICE &lt;= #{salePriceEnd}
  518.             </if>
  519.         </if>
  520.         <if test="priceType == 2">
  521.             <if test="salePriceStart != null and salePriceStart != '' ">
  522.                 AND UNIT_PRICE &gt;= #{salePriceStart}
  523.             </if>
  524.             <if test="salePriceEnd != null and salePriceEnd != '' ">
  525.                 AND UNIT_PRICE &lt;= #{salePriceEnd}
  526.             </if>
  527.         </if>
  528.         <if test="perfectSituation == 1">
  529.             AND SCHOOL_DEGREE = 100
  530.         </if>
  531.         <if test="perfectSituation == 2">
  532.             AND SCHOOL_DEGREE &lt; 100
  533.         </if>
  534.         ORDER BY ID DESC
  535.  
  536.     </select>
  537.  
  538.     <select id="selectSchoolByNameAndCityAndArea" parameterType="java.util.Map" resultMap="SchoolInfo">
  539.         SELECT
  540.             <include refid="Base_Column_List" />
  541.         FROM
  542.             XQ_SCHOOL_INFO
  543.         WHERE
  544.             STATUS = 1
  545.         AND NAME = #{name}
  546.         AND CITY_CODE=#{city}
  547.         AND AREA_CODE=#{area}
  548.         AND TYPE = #{type}
  549.     </select>
  550.  
  551.     <select id="selectAreaIdAndKeyWord" parameterType="java.util.Map" resultMap="SchoolInfo">
  552.         SELECT
  553.         XSI.*,CYCS.NAME AS  'CITY_NAME',AYCS.NAME AS  'AREA_NAME'
  554.         FROM
  555.             XQ_SCHOOL_INFO XSI
  556.             LEFT JOIN YW_CITY_SETTING CYCS ON  XSI.CITY_CODE = CYCS.CODE
  557.             LEFT JOIN YW_CITY_SETTING AYCS ON XSI.AREA_CODE = AYCS. CODE
  558.         WHERE
  559.             1=1
  560.         <if test="areaId != null and areaId != ''">
  561.             AND XSI.AREA_CODE=#{areaId}
  562.         </if>
  563.         <if test="key != null and key!=''">
  564.             AND (XSI.NAME like CONCAT(#{key},'%' ) or XSI.NAME_SPELL like CONCAT(#{key},'%' ))
  565.         </if>
  566.         AND
  567.         XSI.STATUS=1
  568.         <if test="pageSize != null">
  569.             limit #{pageSize}
  570.         </if>
  571.     </select>
  572.  
  573.  
  574.     <select id="selectAreaIdList" parameterType="map" resultType="integer">
  575.         SELECT DISTINCT AREA_ID FROM XQ_SCHOOL_INFO WHERE NAME LIKE CONCAT('%',#{schoolName},'%')
  576.     </select>
  577.  
  578.  
  579.     <select id="selectSchoolList" parameterType="map" resultMap="SchoolInfo">
  580.         SELECT
  581.         <include refid="Base_Column_List" />
  582.         FROM
  583.         XQ_SCHOOL_INFO
  584.         WHERE
  585.         STATUS = 1
  586.         <if test="idList != null and idList.size gt 0">
  587.             AND ID IN
  588.             <foreach collection="idList"  item="item" index="index" open="(" close=")" separator=",">
  589.                 #{item}
  590.             </foreach>
  591.         </if>
  592.  
  593.         <if test="areaId != null and areaId != null"> AND AREA_ID = #{areaId} </if>
  594.  
  595.     </select>
  596. </mapper>
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jpfss/p/10882165.html