USER_TEST_HPP_SqlMap.xml相关配置

<?xml version="1.0" encoding="UTF-8" ?>

<!-- 声明sqlMap片段： -->
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >

<!-- @author Wu,Yang -->
<!-- 不加namespace，那么所有sqlMap里 标签的id都不允许相同,是区别sqlMap的 -->
<sqlMap namespace="USER_TEST_HPP">

  <!-- typeAlias其实就是将bean的替换成一个短的名字 -->
    <typeAlias alias="userTestHpp" type="com.ebiz.bp_mysql.domain.UserTestHpp" />

    <cacheModel id="oneDayCache" type="OSCACHE">
    <!-- 每24小时刷新一次， 一个cacheModel只有一个flushInterval缓存
    OSCache的配置比较活的，你可以根据你的情况进行相应的配置。它主要是针对页面级的对象。
           简单的说，缓存就是Map<key,value>，创建缓存就是添加一个map,使用就是通过key取value.-->
        <flushInterval hours="24" />
        <flushOnExecute statement="insertUserTestHpp" />
        <flushOnExecute statement="updateUserTestHpp" />
        <flushOnExecute statement="deleteUserTestHpp" />
    </cacheModel>

<!-- resultmap结果集的映射，就是将返回的记录逐个字段的映射赋值给对象的属性上 -->
    <resultMap id="userTestHppResultForList" class="userTestHpp">
        <result column="ID" property="id" jdbcType="INTEGER" />
        <result column="USER_NAME" property="user_name" jdbcType="VARCHAR" />
        <result column="REAL_NAME" property="real_name" jdbcType="VARCHAR" />
        <result column="SEX" property="sex" jdbcType="INTEGER" />
        <result column="AGE" property="age" jdbcType="INTEGER" />
        <result column="EDUCATION" property="education" jdbcType="INTEGER" />
        <result column="BIRTH_YEAR" property="birth_year" jdbcType="TIMESTAMP" />
        <result column="USER_DESC" property="user_desc" jdbcType="INTEGER" />
        <result column="IS_DEL" property="is_del" jdbcType="INTEGER" />
    </resultMap>

    <resultMap id="userTestHppResult" class="userTestHpp" extends="userTestHppResultForList">
    </resultMap>

    <sql id="sf-userTestHpp"><!-- 空格加and空格 表示条件判断where -->
    <!--isNotEmpty 动态的判定条件是否存在 -->
        <isNotEmpty prepend=" and " property="id">a.ID = #id:INTEGER#</isNotEmpty>
        <isNotEmpty prepend=" and " property="user_name">a.USER_NAME = #user_name:VARCHAR#</isNotEmpty>
        <isNotEmpty prepend=" and " property="real_name">a.REAL_NAME = #real_name:VARCHAR#</isNotEmpty>
        <isNotEmpty prepend=" and " property="sex">a.SEX = #sex:INTEGER#</isNotEmpty>
        <isNotEmpty prepend=" and " property="age">a.AGE = #age:INTEGER#</isNotEmpty>
        <isNotEmpty prepend=" and " property="education">a.EDUCATION = #education:INTEGER#</isNotEmpty>
        <isNotEmpty prepend=" and " property="birth_year">a.BIRTH_YEAR = #birth_year:TIMESTAMP#</isNotEmpty>
        <isNotEmpty prepend=" and " property="user_desc">a.USER_DESC = #user_desc:INTEGER#</isNotEmpty>
        <isNotEmpty prepend=" and " property="is_del">a.IS_DEL = #is_del:INTEGER#</isNotEmpty>
        <isNotEmpty prepend=" and " property="map.not_in_id">id not in($map.not_in_id$)</isNotEmpty>
        <isNotEmpty prepend=" and " property="map.user_name_like">a.USER_NAME like '%' #map.user_name_like# '%'</isNotEmpty>
    </sql>

<!-- 这样以后改了sql，就不需要去改java代码了 -->
<!-- id表示select里的sql语句，resultClass表示返回结果的类型 -->
<!-- parameterClass表示参数的内容 -->
<!-- #表示这是一个外部调用的需要传进的参数，可以理解为占位符 -->
    <select id="selectUserTestHpp" resultMap="userTestHppResult" parameterClass="userTestHpp" cacheModel="oneDayCache">
        select * from USER_TEST_HPP a where 1 = 1
        <!-- 引入动态查询条件 -->
        <include refid="sf-userTestHpp" />
    </select>

    <select id="selectUserTestHppList" resultMap="userTestHppResultForList" parameterClass="userTestHpp" cacheModel="oneDayCache">
        select * from USER_TEST_HPP a where 1 = 1
        <include refid="sf-userTestHpp" />
        order by  USER_DESC DESC, ID DESC
        <isNotEmpty property="row.count">limit 0, #row.count#</isNotEmpty>
    </select>

    <select id="selectUserTestHppCount" resultClass="int" parameterClass="userTestHpp" cacheModel="oneDayCache">
        select count(*) from USER_TEST_HPP a where 1 = 1
        <include refid="sf-userTestHpp" />
    </select>

    <select id="selectUserTestHppPaginatedList" resultMap="userTestHppResult" parameterClass="userTestHpp" cacheModel="oneDayCache">
        select * from USER_TEST_HPP a where 1 = 1
        <include refid="sf-userTestHpp" />
        order by  USER_DESC DESC, ID DESC
        <isNotEmpty property="row.count">limit #row.first#, #row.count#</isNotEmpty>
    </select>

    <insert id="insertUserTestHpp" parameterClass="userTestHpp">
        <![CDATA[insert into USER_TEST_HPP (]]><!--CDATA 原样输出 -->
        <!-- <dynamic>内则是动态条件所相关的语句,里面填写的都是where相关的条件 对于这个动态的依赖于什么动词则为 prepend=""
        其中可以填写where也可以写group by和order by -->
        
        <dynamic prepend=" ">
            <isNotNull prepend="," property="id">ID</isNotNull>    
            <isNotNull prepend="," property="user_name">USER_NAME</isNotNull>    
            <isNotNull prepend="," property="real_name">REAL_NAME</isNotNull>    
            <isNotNull prepend="," property="sex">SEX</isNotNull>    
            <isNotNull prepend="," property="age">AGE</isNotNull>    
            <isNotNull prepend="," property="education">EDUCATION</isNotNull>    
            <isNotNull prepend="," property="birth_year">BIRTH_YEAR</isNotNull>    
            <isNotNull prepend="," property="user_desc">USER_DESC</isNotNull>    
            <isNotNull prepend="," property="is_del">IS_DEL</isNotNull>
    
        </dynamic>
        <![CDATA[) values (]]>
        <dynamic prepend=" ">
        <isNotNull prepend="," property="id"> #id:INTEGER#</isNotNull>
        <isNotNull prepend="," property="user_name"> #user_name:VARCHAR#</isNotNull>
        <isNotNull prepend="," property="real_name"> #real_name:VARCHAR#</isNotNull>
        <isNotNull prepend="," property="sex"> #sex:INTEGER#</isNotNull>
        <isNotNull prepend="," property="age">#age:INTEGER#</isNotNull>
        <isNotNull prepend="," property="education"> #education:INTEGER#</isNotNull>
        <isNotNull prepend="," property="birth_year">#birth_year:TIMESTAMP#</isNotNull>
        <isNotNull prepend="," property="user_desc"> #user_desc:INTEGER#</isNotNull>
        <isNotNull prepend="," property="is_del"> #is_del:INTEGER#</isNotNull>
        </dynamic>
        <![CDATA[)]]>
        <!-- 这里需要说明一下不同的数据库主键的生成，对各自的数据库有不同的方式： -->
        <!-- mysql:SELECT LAST_INSERT_ID() AS VALUE -->
        <!-- mssql:select @@IDENTITY as value -->
        <!-- oracle:SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL -->
        <selectKey resultClass="int" keyProperty="id">SELECT LAST_INSERT_ID()</selectKey>
    </insert>

    <update id="updateUserTestHpp" parameterClass="userTestHpp">
        update USER_TEST_HPP
        <dynamic prepend="set">
        <isNotNull prepend="," property="id">ID = #id:INTEGER#</isNotNull>
        <isNotNull prepend="," property="user_name">USER_NAME = #user_name:VARCHAR#</isNotNull>
        <isNotNull prepend="," property="real_name">REAL_NAME = #real_name:VARCHAR#</isNotNull>
        <isNotNull prepend="," property="sex">SEX = #sex:INTEGER#</isNotNull>
        <isNotNull prepend="," property="age">AGE = #age:INTEGER#</isNotNull>
        <isNotNull prepend="," property="education">EDUCATION = #education:INTEGER#</isNotNull>
        <isNotNull prepend="," property="birth_year">BIRTH_YEAR = #birth_year:TIMESTAMP#</isNotNull>
        <isNotNull prepend="," property="user_desc">USER_DESC = #user_desc:INTEGER#</isNotNull>
        <isNotNull prepend="," property="is_del">IS_DEL = #is_del:INTEGER#</isNotNull>
        </dynamic>
        where 1 = 1
        <isNotEmpty prepend=" and " property="id">ID = #id#</isNotEmpty>
        <isEmpty prepend=" and " property="id">
            <isNotEmpty prepend=" " property="map.pks">
                ID in
                <iterate close=")" open="(" conjunction="," property="map.pks">#map.pks[]#</iterate>
            </isNotEmpty>
        </isEmpty>
    </update>

    <delete id="deleteUserTestHpp" parameterClass="userTestHpp">
        delete from USER_TEST_HPP where 1 = 1
            <!-- #id#里的id可以随意取，但是上面的insert则会有影响 -->
        <!-- 我们也可以这样理解，如果有#占位符，则ibatis会调用parameterClass里的属性去赋值 -->
        <isNotEmpty prepend=" and " property="id">ID = #id#</isNotEmpty>
        <isEmpty prepend=" and " property="id">
            <isNotEmpty prepend=" " property="map.pks">
                ID in
                <!-- Iterate：这属性遍历整个集合，并为List集合中的元素重复元素体的内容。
                  Iterate的属性：
                  prepend － 可被覆盖的SQL语句组成部分，添加在语句的前面（可选）
                  property － 类型为java.util.List的用于遍历的元素（必选）
                  open － 整个遍历内容体开始的字符串，用于定义括号（可选）
                  close －整个遍历内容体结束的字符串，用于定义括号（可选）
                  conjunction － 每次遍历内容之间的字符串，用于定义AND或OR（可选） -->
                <iterate close=")" open="(" conjunction="," property="map.pks">#map.pks[]#</iterate>
            </isNotEmpty>
        </isEmpty>
    </delete>

</sqlMap>