TKmybatis的框架介绍和原理分析及Mybatis新特性
tkmybatis是在mybatis框架的基础上提供了很多工具,让开发更加高效,下面来看看这个框架的基本使用,后面会对相关源码进行分析,感兴趣的同学可以看一下,挺不错的一个工具
实现对员工表的增删改查的代码
java的dao层接口
- public interface WorkerMapper extends Mapper<Worker> {
- }
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>
实体对象
- @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 ...
- }
以上就是实现对Worker进行增删改查的所有代码,包括选择性更新、插入、删除等,所有的方法列表如下
以后对表字段的添加或修改只需要更改实体对象的注解,不需要修改xml映射文件,如将worker_id改成worker_no
- @Column(name = "worker_no")
- private String workerNo;
数据源的配置,只需要将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>
用这个库之后写代码感觉在飞…….如果只是简单的了解此框架到这里就可以了,下面是对框架实现原理的分析
原理的简单分析
此框架为我们实现这些功能所有的改动都在Mapper层面,所有的Mapper都继承了tk.mybatis.mapper.common.Mapper
public interface WorkerMapper extends Mapper<Worker> {}
Mapper接口的声明如下,可以看到Mapper接口实现了所有常用的方法
- public interface Mapper<T> extends
- BaseMapper<T>,
- ExampleMapper<T>,
- RowBoundsMapper<T>,
- Marker {
-
- }
看一下完整的UML图,太大了,可以用新窗口打开,放大之后再看
这里选择一个接口:SelectOneMapper接口,对于源码进行简单分析,此接口声明如下:
- public interface SelectOneMapper<T> {
-
- /**
- * 根据实体中的属性进行查询,只能有一个返回值,有多个结果是抛出异常,查询条件使用等号
- *
- * @param record
- * @return
- */
- @SelectProvider(type = BaseSelectProvider.class, method = "dynamicSQL")
- T selectOne(T record);
-
- }
@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();
- }
- }
到这里我们就大概知道了这个库为我们提供便利的原理了,总的来说就是这个库帮我们提供了对表的基本操作的sql,帮我们省了很多工作量,而且维护起来也很方便,否则我们的xml文件动不动就几百行甚至上千行
对源码的探索不能到这里停止,最起码要分析到与另一个框架的整合点
我们知道,mybatis的mapper接口是在启动的时候被框架以JdkProxy的形式封装了的,具体对应的类是MapperFactoryBean,这个类中有一个checkDaoConfig()方法,是从父类继承并重写了该方法,继承结构如下
MapperFactoryBean -> SqlSessionDaoSupport -> DaoSupport
这里的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 {
- }
-
- }
框架自定义的MapperFactoryBean重写了checkDaoConfig()方法,完成对所有sql语句的设置,代码如下
- @Override
- protected void checkDaoConfig() {
- super.checkDaoConfig();
- //通用Mapper
- if (mapperHelper.isExtendCommonMapper(getObjectType())) {
- //这里去处理该类所对应的MappedStatement,封装在helper类中处理
- mapperHelper.processConfiguration(getSqlSession().getConfiguration(), getObjectType());
- }
- }
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);
- }
- }
- }
- }
- }
设置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);
到这里整个sql的获取流程就分析完了,本人用这个库写过一个小项目,确实节省了开发的工作量,而且DAO层的结构更加清晰简洁了
关于mybatis新特性
从3.4.0开始,mybatis提供对外部表的alias引用方法,多表联合查询就方便多了,我们先看原始的方式是怎样做的
- select a.id,a.name,b.bid,b.bname .....
- from user a
- left join room b
原始的方式是将所有的表字段列出来,再来看用新特性怎样做- 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>
这里主要就是对基本的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>
更进一步
敏锐的程序员可能会提出问题,如当多表查询的时候可能会存在字段名称相同的情况,这里的解决方案是给include添加另一个属性
- <include refid="user_col_sql_id_with_alias">
- <property name="alias" value="t"/>
- <property name="prefix" value="t_"/>
- </include>
包含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>
如果说觉得手动写包含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);
- }
- }
注入方式区别
- <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>
<实体类
- 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;//高中数量
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() > 0"> AND A.AREA_ID = #{areaId} </if>
- <if test="typeList != null and typeList.size > 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>
普通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>