MyBatis学习总结（二）表的关联以及动态SQL

最新推荐文章于 2024-08-26 20:43:46 发布

大树先生

最新推荐文章于 2024-08-26 20:43:46 发布

阅读量1.7k

点赞数

分类专栏： JavaEE 文章标签： mybatis

本文链接：https://blog.csdn.net/z55887/article/details/52424517

版权

JavaEE 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

表的关联

实际项目中我们经常需要进行多表的联查。比较常见的有一对多，多对一以及最复杂的多对多，下面我们依次讨论。

1. 一对多

以“用户”和“机构”为例：
一个“机构”中含有多个“用户”。我们想做的是：获取一个机构，然后得到这个机构下面的所有用户信息。

创建对应的表

DROP TABLE IF EXISTS `org`;
CREATE TABLE `org` (
  `orgid` varchar(255) CHARACTER SET utf8 NOT NULL,
  `orgname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`orgid`)
)

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `username` varchar(255) CHARACTER SET utf8 NOT NULL,
  `userpass` varchar(255) DEFAULT NULL,
  `orgid` varchar(255) DEFAULT NULL,
  `fullname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`username`)
)

对应的实体类：

/**
 * user表的实体类，用户
 */
public class o2mUser {
    private String username;
    private String userpass;
    private String fullname;
    private o2mOrg org;

    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getUserpass() {
        return userpass;
    }
    public void setUserpass(String userpass) {
        this.userpass = userpass;
    }
    public String getFullname() {
        return fullname;
    }
    public void setFullname(String fullname) {
        this.fullname = fullname;
    }
    public o2mOrg getOrg() {
        return org;
    }
    public void setOrg(o2mOrg org) {
        this.org = org;
    }

}
/**
 * org表的实体类，机构
 */
public class o2mOrg {
    private String orgId;
    private String orgName;
    private List<o2mUser> users;

    public o2mOrg() {
        super();
    }

    public o2mOrg(String orgId, String orgName) {
        super();
        this.orgId = orgId;
        this.orgName = orgName;
    }

    public String getOrgId() {
        return orgId;
    }

    public void setOrgId(String orgId) {
        this.orgId = orgId;
    }

    public String getOrgName() {
        return orgName;
    }

    public void setOrgName(String orgName) {
        this.orgName = orgName;
    }

    public List<o2mUser> getUsers() {
        return users;
    }

    public void setUsers(List<o2mUser> users) {
        this.users = users;
    }
}

mapper.xml文件
org里面有user的List集合，在mapper.xml文件中，用<collection …… />来表示。column指明外键。
这样，resultMapOrg中就既包含了org的属性，也包含了一个user集合的属性。通过执行“多表联查sql“查询后获取的字段就能一一对应的写入其中。

<!--一对多映射配置Demo-->
<mapper namespace="a">

    <resultMap type="o2mOrg" id="resultMapOrg">
        <result property="orgId" column="orgid"/>
        <result property="orgName" column="orgname"/>

        <collection property="users" ofType="test.wsz.mybatis.entity.o2mUser" column="orgid">
            <id property="username" column="username" javaType="string" jdbcType="VARCHAR"/>    
            <result property="userpass" column="userpass" javaType="string" jdbcType="VARCHAR"/>
            <result property="fullname" column="fullname" javaType="string" jdbcType="VARCHAR"/> 
            <!--如果user实体类里面配的orgId,则用下面的配置；如果配的实体Org，则在collection里面加column，如上-->
            <!-- <result property="orgId" column="orgid" javaType="string" jdbcType="VARCHAR"/>  -->
        </collection>
    </resultMap>

    <select id="getOrg" resultMap="resultMapOrg" parameterType="String">
        select u.* ,o.* from user u,org o where u.orgid=o.orgid and o.orgid=#{id}
  </select>
</mapper>

下面是测试代码：获取一个机构，然后获取该机构下的所有用户信息

public class o2mMain {

    private static SqlSessionFactory sqlSessionFactory;
    private static Reader reader;

    static{
        try {
            reader=Resources.getResourceAsReader("config/mybatis-config.xml");
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }

    public static void main(String[] args) {
        SqlSession session=getSqlSessionFactory().openSession();
        //一对多测试
        o2mOrg org=session.selectOne("a.getOrg", "1");
        System.out.println(org.getOrgName()+":");

        List<o2mUser> users=org.getUsers();
        for (o2mUser o2mUser : users) {
            System.out.println(o2mUser.getUsername());
        }
}

2. 多对一

仍然采用“用户”和“机构”为例，我们想要：
获取一个用户，想要知道对应的机构的信息。

mapper.xml文件
resultMap中采用<association …… />来加入其他表的属性。其实从”association”的中文意思“联合”也可以知道这个标签是什么意思。
这样，resultMap便可以一一对应执行“联查sql“后获取的表的字段。

<!--多对一映射配置Demo-->
<mapper namespace="b">

    <resultMap type="o2mUser" id="resultMapUser">
        <result property="username" column="username"/>
        <result property="userpass" column="userpass"/>
        <result property="fullname" column="fullname"/>
        <!--级联其他表配置-->
        <association property="org" javaType="o2mOrg">  
            <id property="orgId" column="orgid"/>
            <result property="orgName" column="orgName"/>   
        </association> 
    </resultMap>

    <select id="getUser" resultMap="resultMapUser" parameterType="String">
        select u.* ,o.* from user u,org o where u.orgid=o.orgid and u.username=#{username}
    </select>

</mapper>

测试代码：获取一个用户，再获取其机构信息

//多对一测试
        o2mUser user=session.selectOne("b.getUser", "test");
        System.out.println(user.getOrg().getOrgName());

3. 多对多

一对多和多对一已经能满足我们大部分的需求了，但某些时候，我们仍然需要配置多对多。

以“学生”和“课程”为例。现在我们要获取一个学生，然后知道他选了哪些课程，然后需要知道其中一门课程有那些学生选了。有点绕……

创建对应的表：学生表，课程表以及中间表

DROP TABLE IF EXISTS `choose_course`;
CREATE TABLE `choose_course` (
  `sid` varchar(255) NOT NULL,
  `cid` varchar(255) NOT NULL,
  PRIMARY KEY (`sid`,`cid`)
) 

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

对应的实体类

public class m2mStudent {
    private String id;
    private String name;
    private List<m2mCourse> courses;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<m2mCourse> getCourses() {
        return courses;
    }

    public void setCourses(List<m2mCourse> courses) {
        this.courses = courses;
    }

}

public class m2mCourse {
    private int id;
    private String name;
    private List<m2mStudent> students;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<m2mStudent> getStudents() {
        return students;
    }

    public void setStudents(List<m2mStudent> students) {
        this.students = students;
    }

}

public class m2mChooseCourse {
    private String sid;
    private String cid;

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

}

mapper.xml文件
多对多的配置比较复杂，三个实体类都需要配置mapper文件。

1.中间表的mapper：

<!--多对多映射配置Demo-->
<mapper namespace="chooseCourse">

    <parameterMap type="m2mChooseCourse" id="parameterChooseCourseMap">
        <parameter property="sid"/>
        <parameter property="cid"/>
    </parameterMap>

    <insert id="insertChooseCourse"  parameterMap="parameterChooseCourseMap">
        INSERT INTO choose_course(sid, cid)
        VALUES(#{sid},#{cid})
    </insert>

    <resultMap type="m2mCourse" id="resultCourseMap_2">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>

    <!-- 根据一个学生ID,查看这个学生选的所有课程 -->
    <select id="getCoursesByStudentId" resultMap="resultCourseMap_2" parameterType="String">
        select c.*,sc.sid from course c,choose_course sc where c.id=sc.cid and sc.sid=#{id}
    </select>

    <resultMap type="m2mStudent" id="resultStudentMap_2">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap> 

    <!-- 根据一个课程ID,查看选了这个课程的所有学生-->
    <select id="getStudentsByCourseId" resultMap="resultStudentMap_2" parameterType="String">
        select s.*, sc.cid from student s,choose_course sc where s.id=sc.sid and sc.cid=#{id}
    </select>
</mapper>

这个映射xml文件中，配置了一个<insert ……/>用于中间表插入数据。两个<select ……/>分别查出“指定id的学生选的所有课程”和“指定id的课程被哪些学生选了”。这两个<select ……/>将在“学生”和“课程”的mapper文件中被用到，后面详讲。

2.course的mapper：

<!--多对多映射配置Demo-->
<mapper namespace="m2mCourse">

    <parameterMap type="m2mCourse" id="parameterCourseMap">
        <parameter property="id"/>
        <parameter property="name"/>
    </parameterMap>

    <insert id="insertCourse" parameterType="m2mCourse">    
        INSERT INTO course(name)
        VALUES(#{name});
    </insert>

    <resultMap type="m2mCourse" id="resultCourse">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--引用中间表mapper文件中的查询，column的值为查询的参数，设定为课程的id-->
        <collection property="students" column="id" select="chooseCourse.getStudentsByCourseId"/>
    </resultMap>
    <!--获取课程，以及选中该课的所有学生-->
    <select id="getCourse" resultMap="resultCourse" parameterType="String">
        SELECT *
        FROM course
        WHERE id=#{id}
    </select>    

</mapper>

这个映射xml文件中，配置了一个一对多的<select ……/>查询。需要注意的是，<collection ……/>中直接引用了中间表mapper中的查询，通过column指定查询的传参。

3.student的mapper：

<!--多对多映射配置Demo-->
<mapper namespace="m2mStudent">

    <parameterMap type="m2mStudent" id="parameterStudentMap">
        <parameter property="id"/>
        <parameter property="name"/>
    </parameterMap>

    <insert id="insertStudent" parameterMap="parameterStudentMap">
        INSERT INTO `student` (name)
        VALUES(#{name}); 
    </insert>

    <resultMap type="m2mStudent" id="resultStudentMap_1">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <!--引用中间表mapper文件中的查询，column的值为查询的参数，设定为学生的id-->
        <collection property="courses" column="id"
            select="chooseCourse.getCoursesByStudentId" />
    </resultMap>
    <!--获取学生，以及该学生选中的所有课程-->
    <select id="getStudent" resultMap="resultStudentMap_1"
        parameterType="String">
        SELECT *
        FROM student
        WHERE id=#{id}
    </select>

</mapper>

同上，通过引用中间表mapper文件中的查询实现了一对多查询，获取了指定id的学生以及他选的课程。

测试代码：

SqlSession session=getSqlSessionFactory().openSession();
        //获取指定的学生以及其选的课程
        m2mStudent student=session.selectOne("m2mStudent.getStudent","1");
        System.out.println(student.getName()+":");
        List<m2mCourse> courses=student.getCourses();
        for (m2mCourse m2mCourse : courses) {
            System.out.println(m2mCourse.getName());
        }

        //获取该学生选中的一门课程，该课程被哪些学生选了
        m2mCourse course=session.selectOne("m2mCourse.getCourse",courses.get(0).getId());
        List<m2mStudent> students=course.getStudents();
        System.out.println(course.getName()+":");
        for (m2mStudent m2mStudent : students) {
            System.out.println(m2mStudent.getName());
        }

动态SQL

这里给出一个动态sql的模板

<?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.bamboocloud.im.cgb.entity.CgbRole">
    <resultMap id="resultMap" type="com.bamboocloud.im.cgb.entity.CgbRole">
        <result property="id" column="ZROL_ID" />
        <result property="organId" column="ZORG_ID" />
        <result property="code" column="ZROL_CODE" />
        <result property="name" column="ZROL_NAME" />
        <result property="warnValue" column="ZROL_WARN_VALUE" />
        <result property="isDeleted" column="ZROL_IS_DELETED" />
        <result property="createAt" column="ZROL_CREATE_AT" />
        <result property="updateAt" column="ZROL_UPDATE_AT" />
        <result property="syncIsCreated" column="ZROL_SYNC_IS_CREATED"/>
        <result property="syncLastStatus" column="ZROL_SYNC_LAST_STATUS" />
        <result property="syncLastTime" column="ZROL_SYNC_LAST_TIME" />
        <result property="syncLastErrcode" column="ZROL_SYNC_LAST_ERRCODE" />
    </resultMap>

    <sql id="table">Z_ROL</sql>
    <sql id="columns">ZROL_ID,ZORG_ID,ZROL_CODE,ZROL_NAME,ZROL_WARN_VALUE,ZROL_IS_DELETED,ZROL_CREATE_AT,ZROL_UPDATE_AT,ZROL_SYNC_IS_CREATED,ZROL_SYNC_LAST_STATUS,ZROL_SYNC_LAST_TIME,ZROL_SYNC_LAST_ERRCODE</sql>
    <sql id="set">
        <trim suffixOverrides=",">
            <if test="organId != null">ZORG_ID = <choose><when test="code == ''">null</when><otherwise>#{organId}</otherwise></choose>,</if>
            <if test="code != null">ZROL_CODE = <choose><when test="code == ''">null</when><otherwise>#{code}</otherwise></choose>,</if>
            <if test="name != null">ZROL_NAME = <choose><when test="name == ''">null</when><otherwise>#{name}</otherwise></choose>,</if>
            <if test="warnValue != null">ZROL_WARN_VALUE = #{warnValue},</if>
            <if test="isDeleted != null">ZROL_IS_DELETED = #{isDeleted},</if>
            <if test="updateAt != null">ZROL_UPDATE_AT =
                <choose>
                    <when test="_databaseId == 'oracle'">
                        TO_TIMESTAMP(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff'),
                    </when>
                    <when test="_databaseId == 'mysql'">
                        STR_TO_DATE(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s'),
                    </when>
                    <otherwise>
                        #{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    </otherwise>
                </choose>
            </if>
            <if test="syncIsCreated != null">ZROL_SYNC_IS_CREATED = #{syncIsCreated},</if>
            <if test="syncLastStatus != null">ZROL_SYNC_LAST_STATUS = #{syncLastStatus},</if>
            <if test="syncLastTime != null">ZROL_SYNC_LAST_TIME =
                <choose>
                    <when test="_databaseId == 'oracle'">
                        TO_TIMESTAMP(#{syncLastTime,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff'),
                    </when>
                    <when test="_databaseId == 'mysql'">
                        STR_TO_DATE(#{syncLastTime,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s'),
                    </when>
                    <otherwise>
                        #{syncLastTime,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    </otherwise>
                </choose>
            </if>
            <if test="syncLastErrcode != null">ZROL_SYNC_LAST_ERRCODE = <choose><when test="syncLastErrcode == ''">null</when><otherwise>#{syncLastErrcode}</otherwise></choose>,</if>
        </trim>
    </sql>
    <sql id="whereEntity">
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="id != null">AND ZROL_ID = #{id}</if>
            <if test="organId != null">AND ZORG_ID = #{organId}</if>
            <if test="code != null">AND ZROL_CODE = #{code}</if>
            <if test="name != null">AND ZROL_NAME = #{name}</if>
        </trim>
    </sql>
    <sql id="whereFilter">
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="filter != null">
                <if test="filter.id_eq != null">AND ZROL_ID = #{filter.id_eq}</if>
                <if test="filter.id_ne != null">AND ZROL_ID != #{filter.id_ne}</if>
                <if test="filter.id_in != null">AND <foreach item="ii" collection="filter.id_in" open="(" separator=" OR " close=")">ZROL_ID in <foreach item="i" collection="ii" open="(" separator="," close=")">#{i}</foreach></foreach></if>
                <if test="filter.id_notIn != null">AND <foreach item="ii" collection="filter.id_notIn" open="(" separator=" AND " close=")">ZROL_ID not in <foreach item="i" collection="ii" open="(" separator="," close=")">#{i}</foreach></foreach></if>
                <if test="filter.organId_eq != null">AND ZORG_ID = #{filter.organId_eq}</if>
                <if test="filter.organId_ne != null">AND ZORG_ID != #{filter.organId_ne}</if>
                <if test="filter.organId_in != null">AND <foreach item="ii" collection="filter.organId_in" open="(" separator=" OR " close=")">ZORG_ID in <foreach item="i" collection="ii" open="(" separator="," close=")">#{i}</foreach></foreach></if>
                <if test="filter.organId_notIn != null">AND <foreach item="ii" collection="filter.organId_notIn" open="(" separator=" AND " close=")">ZORG_ID not in <foreach item="i" collection="ii" open="(" separator="," close=")">#{i}</foreach></foreach></if>
                <if test="filter.code_eq != null">AND ZROL_CODE = #{filter.code_eq}</if>
                <if test="filter.code_like != null">AND lower(ZROL_CODE) LIKE lower(#{filter.code_like})</if>
                <if test="filter.code_in != null">AND <foreach item="ii" collection="filter.code_in" open="(" separator=" OR " close=")">ZROL_CODE in <foreach item="i" collection="ii" open="(" separator="," close=")">#{i}</foreach></foreach></if>
                <if test="filter.code_notIn != null">AND (ZROL_CODE IS NULL OR <foreach item="ii" collection="filter.code_notIn" open="(" separator=" AND " close=")">ZROL_CODE not in <foreach item="i" collection="ii" open="(" separator="," close=")">#{i}</foreach></foreach>)</if>
                <if test="filter.name_eq != null">AND ZROL_NAME = #{filter.name_eq}</if>
                <if test="filter.name_like != null">AND lower(ZROL_NAME) LIKE lower(#{filter.name_like})</if>
                <if test="filter.warnValue_eq != null">AND ZROL_WARN_VALUE = #{filter.warnValue_eq}</if>
                <if test="filter.isDeleted_eq != null">AND ZROL_IS_DELETED = #{filter.isDeleted_eq}</if>
                <if test="filter.createAt_gte != null">AND ZROL_CREATE_AT &gt;=
                    <choose>
                        <when test="_databaseId == 'oracle'">
                            TO_TIMESTAMP(#{filter.createAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff')
                        </when>
                        <when test="_databaseId == 'mysql'">
                            STR_TO_DATE(#{filter.createAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s')
                        </when>
                        <otherwise>
                            #{filter.createAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        </otherwise>
                    </choose>
                </if>
                <if test="filter.createAt_lte != null">AND ZROL_CREATE_AT &lt;= 
                    <choose>
                        <when test="_databaseId == 'oracle'">
                            TO_TIMESTAMP(#{filter.createAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff')
                        </when>
                        <when test="_databaseId == 'mysql'">
                            STR_TO_DATE(#{filter.createAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s')
                        </when>
                        <otherwise>
                            #{filter.createAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        </otherwise>
                    </choose>
                </if>
                <if test="filter.updateAt_gte != null">AND ZROL_UPDATE_AT &gt;= 
                    <choose>
                        <when test="_databaseId == 'oracle'">
                            TO_TIMESTAMP(#{filter.updateAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff')
                        </when>
                        <when test="_databaseId == 'mysql'">
                            STR_TO_DATE(#{filter.updateAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s')
                        </when>
                        <otherwise>
                            #{filter.updateAt_gte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        </otherwise>
                    </choose>
                </if>
                <if test="filter.updateAt_lte != null">AND ZROL_UPDATE_AT &lt;= 
                    <choose>
                        <when test="_databaseId == 'oracle'">
                            TO_TIMESTAMP(#{filter.updateAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff')
                        </when>
                        <when test="_databaseId == 'mysql'">
                            STR_TO_DATE(#{filter.updateAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s')
                        </when>
                        <otherwise>
                            #{filter.updateAt_lte,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}
                        </otherwise>
                    </choose>
                </if>
                <if test="filter.syncLastStatus_eq != null">AND ZROL_SYNC_LAST_STATUS = #{filter.syncLastStatus_eq}</if>
                <if test="filter.WHERE_SQL_custom != null">AND (${filter.WHERE_SQL_custom})</if>
                <if test="filter._groupId_eq != null and filter._roleIdType_eq != null">AND ZROL_ID in (select ZROL_ID from Z_GRP_ROL where ZGRP_ID =#{filter._groupId_eq} and ZROL_TYPE=#{filter._roleIdType_eq} and ZROL_IS_DELETED = 0)</if>
                <if test="filter._groupId_eq != null and filter._roleIdType_eq == null">AND ZROL_ID in (select ZROL_ID from Z_GRP_ROL where ZGRP_ID =#{filter._groupId_eq})</if>
            </if>
        </trim>
    </sql>
    <sql id="where">
        <trim prefix="WHERE " prefixOverrides="AND |OR ">
            <trim prefixOverrides="WHERE ">
                <include refid="whereEntity" />
            </trim>
            <trim prefix="AND " prefixOverrides="WHERE ">
                <include refid="whereFilter" />
            </trim>
        </trim>
    </sql>
    <sql id="sort">
        <if test="sort == null" >
            ORDER BY ZROL_NAME ASC
        </if>
        <if test="sort != null" >
            <trim prefix="ORDER BY " suffixOverrides=",">
                <foreach collection="sort" index="key" item="val">
                    <if test="key == 'id'">ZROL_ID ${val},</if>
                    <if test="key == 'code'">ZROL_CODE ${val},</if>
                    <if test="key == 'name'">ZROL_NAME ${val},</if>
                    <if test="key == 'warnValue'">ZROL_WARN_VALUE ${val},</if>
                    <if test="key == 'isDeleted'">ZROL_IS_DELETED ${val},</if>
                    <if test="key == 'createAt'">ZROL_CREATE_AT ${val},</if>
                    <if test="key == 'updateAt'">ZROL_UPDATE_AT ${val},</if>
                    <if test="key == 'syncIsCreated'">ZROL_SYNC_IS_CREATED ${val},</if>
                    <if test="key == 'syncLastStatus'">ZROL_SYNC_LAST_STATUS ${val},</if>
                    <if test="key == 'syncLastTime'">ZROL_SYNC_LAST_TIME ${val},</if>
                </foreach>
            </trim>
        </if>
    </sql>

    <insert id="insert">
        INSERT INTO <include refid="table" /> (
            <include refid="columns" />
        ) VALUES (
            <trim suffixOverrides=",">
                <choose><when test="id == ''">null</when><otherwise>#{id}</otherwise></choose>, 
                <choose><when test="organId == ''">null</when><otherwise>#{organId}</otherwise></choose>, 
                <choose><when test="code == ''">null</when><otherwise>#{code}</otherwise></choose>, 
                <choose><when test="name == ''">null</when><otherwise>#{name}</otherwise></choose>, 
                #{warnValue}, 
                #{isDeleted},
                <choose>
                    <when test="_databaseId == 'oracle'">
                        TO_TIMESTAMP(#{createAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff'),
                    </when>
                    <when test="_databaseId == 'mysql'">
                        STR_TO_DATE(#{createAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s'),
                    </when>
                    <otherwise>
                        #{createAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    </otherwise>
                </choose>
                <choose>
                    <when test="_databaseId == 'oracle'">
                        TO_TIMESTAMP(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler}, 'yyyy-mm-dd hh24:mi:ss.ff'),
                    </when>
                    <when test="_databaseId == 'mysql'">
                        STR_TO_DATE(#{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlDatetimeTypeHandler}, '%Y-%m-%d %H:%i:%s'),
                    </when>
                    <otherwise>
                        #{updateAt,typeHandler=com.bamboocloud.fw.mybatis.type.SqlTimestampTypeHandler},
                    </otherwise>
                </choose>
                #{syncIsCreated},
                #{syncLastStatus},
                #{syncLastTime},
                #{syncLastErrcode}
            </trim>
        )
    </insert>

    <update id="update" >
        UPDATE <include refid="table" /> SET <include refid="set" />
        WHERE ZROL_ID = #{id}
    </update>

    <delete id="delete">
        DELETE FROM <include refid="table" /> 
        WHERE ZROL_ID = #{id}
    </delete>

    <select id="unique" resultType="long">
        SELECT count(ZROL_ID) FROM <include refid="table" />
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="id != null and id != ''">AND ZROL_ID != #{id}</if>
            <trim prefix="AND (" suffix=")">
                1 = 2
                <if test="code != null and code != ''">OR lower(ZROL_CODE) = lower(#{code})</if>
                <if test="name != null and name != ''">OR ZROL_NAME = #{name}</if>
            </trim>
        </trim>
    </select>

    <select id="findId" resultType="string">
        SELECT ZROL_ID FROM <include refid="table" />
        <include refid="where"/> <include refid="sort" />
    </select>

    <select id="findBy" resultMap="resultMap">
        SELECT <include refid="columns" />FROM <include refid="table" />
        <include refid="where"/> <include refid="sort" />
    </select>

    <select id="countBy" resultType="long">
        SELECT count(ZROL_ID) FROM <include refid="table" />
        <include refid="where" />
    </select>

    <update id="updateBy">
        UPDATE <include refid="table" /> SET <include refid="set" />
        <include refid="whereFilter" />
    </update>

    <delete id="deleteBy">
        DELETE FROM <include refid="table" /> 
        <include refid="where"/>
    </delete>
</mapper>