<?xml version='1.0'?>此配置文件一定要在sqlMapConfig中声明一下(resource)
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<!-- WARNING: This is an autogenerated file -->
<sqlMap namespace="BbSy07">定义命名空间(别名),为了避免id重名。
<cacheModel id="bbsy07-cache" type="MEMORY">配置缓存
<flushInterval hours="24" />每隔24小时,清空缓存
<flushOnExecute statement="insertBbSy07" />当执行此方法时清空缓存
<flushOnExecute statement="updateBbSy07" />
<flushOnExecute statement="deleteBbSy07" />
<property name="reference-type" value="WEAK" />弱引用形式
</cacheModel>
<resultMap class="com.kmr.domain.BbSy07" id="bbsy07-result">它就是结果集的映射,就是将返回的记录逐个字段的映射赋值给对象的属性上。其实如果没有特殊需求的话我们完全可以运用 ResultClass来代替它,因为如果字段与属性一模一样的话,查询出来数据集会自动匹配到ResultClass指定的类的实例对象,如果字段名不在属性中的话,那这个字段将不会被返回的实例体类对象接受,相当于没有查询出这个字段一样的。
<result property="BZBH" column="BZBH" />类中的属性与关系型数据库中对应表的映射(表只有在用到结果集映射的时候确定)
<result property="ID" column="ID" />
<result property="ZRDWMC" column="ZRDWMC" />
<result property="LR" column="LR" />
<result property="DJ" column="DJ" />
<result property="JZSJ" column="JZSJ" />
<result property="NY" column="NY" />
<result property="CDBH" column="CDBH" />
<result property="BMBM" column="BMBM" />
<result property="CXID" column="CXID" />
<result property="YSBZ" column="YSBZ" />
<result property="YDFL" column="YDFL" />
<result property="CQSJ" column="CQSJ" />
<result property="KSSJ" column="KSSJ" />
<result property="TJRQ" column="TJRQ" />
<result property="RYDL" column="RYDL" />
<result property="JHSDRQ" column="JHSDRQ" />
<result property="YXHJDM" column="YXHJDM" />
<result property="ZRDW" column="ZRDW" />
<result property="SLRQ" column="SLRQ" />
<result property="BZ" column="BZ" />
<result property="BZRL" column="BZRL" />
<result property="YXHJ" column="YXHJ" />
<result property="JBDF" column="JBDF" />
<result property="HM" column="HM" />
<result property="r" column="r" />
</resultMap>
<insert id="insertBbSy07" parameterClass="com.kmr.domain.BbSy07">因为插入操作返回的是执行记录的条数,所以无需返回值。
insert into BB_SY07(BZBH, ID, ZRDWMC, LR, DJ, JZSJ, NY, CDBH, BMBM, CXID, YSBZ, YDFL, CQSJ, KSSJ, TJRQ, RYDL, JHSDRQ, YXHJDM, ZRDW, SLRQ, BZ, BZRL, YXHJ, JBDF, HM)values(
<isNotEmpty property="BZBH">#BZBH#</isNotEmpty>此行是当属性值BZBH为null或“”的时候不执行添加sql条件
<isEmpty property="BZBH">NULL</isEmpty>,此行与上一行相反
<isNotEmpty property="ID">#ID#</isNotEmpty>
<isEmpty property="ID">NULL</isEmpty>,
<isNotEmpty property="ZRDWMC">#ZRDWMC#</isNotEmpty>
<isEmpty property="ZRDWMC">NULL</isEmpty>,
<isNotEmpty property="LR">#LR#</isNotEmpty>
<isEmpty property="LR">NULL</isEmpty>,
<isNotEmpty property="DJ">#DJ#</isNotEmpty>
<isEmpty property="DJ">NULL</isEmpty>,
<isNotEmpty property="JZSJ">to_date(#JZSJ#, 'yyyy-mm-dd HH24:MI:SS')</isNotEmpty>
<isEmpty property="JZSJ">NULL</isEmpty>,
<isNotEmpty property="NY">#NY#</isNotEmpty>在做插入操作的时候如果插入的时间就取系统时间的话,可以就直接在下一行中用sysdate来做(下一行是当年月为null或为“”时执行此赋值添加sql语句的操作;这样可以不要在action中手动添加时间了)
<isEmpty property="NY">to_char(sysdate,'yyyy-mm-dd HH24:MI:SS')</isEmpty>,
一般在实体类中将数据库中对应字段的属性(若是Date类型)设计成字符串形式。这样在控制层不需要手动转换,只需要在sqlMap配置文件中直接使用数据库相应的函数即可。
<isNotEmpty property="CDBH">#CDBH#</isNotEmpty>
<isEmpty property="CDBH">NULL</isEmpty>,
<isNotEmpty property="BMBM">#BMBM#</isNotEmpty>
<isEmpty property="BMBM">NULL</isEmpty>,
<isNotEmpty property="CXID">#CXID#</isNotEmpty>
<isEmpty property="CXID">NULL</isEmpty>,
<isNotEmpty property="YSBZ">#YSBZ#</isNotEmpty>
<isEmpty property="YSBZ">NULL</isEmpty>,
<isNotEmpty property="YDFL">#YDFL#</isNotEmpty>
<isEmpty property="YDFL">NULL</isEmpty>,
<isNotEmpty property="CQSJ">#CQSJ#</isNotEmpty>
<isEmpty property="CQSJ">NULL</isEmpty>,
<isNotEmpty property="KSSJ">to_date(#KSSJ#, 'yyyy-mm-dd HH24:MI:SS')</isNotEmpty>
<isEmpty property="KSSJ">NULL</isEmpty>,
<isNotEmpty property="TJRQ">to_date(#TJRQ#, 'yyyy-mm-dd HH24:MI:SS')</isNotEmpty>
<isEmpty property="TJRQ">NULL</isEmpty>,
<isNotEmpty property="RYDL">#RYDL#</isNotEmpty>
<isEmpty property="RYDL">NULL</isEmpty>,
<isNotEmpty property="JHSDRQ">to_date(#JHSDRQ#, 'yyyy-mm-dd HH24:MI:SS')</isNotEmpty>
<isEmpty property="JHSDRQ">NULL</isEmpty>,
<isNotEmpty property="YXHJDM">#YXHJDM#</isNotEmpty>
<isEmpty property="YXHJDM">NULL</isEmpty>,
<isNotEmpty property="ZRDW">#ZRDW#</isNotEmpty>
<isEmpty property="ZRDW">NULL</isEmpty>,
<isNotEmpty property="SLRQ">to_date(#SLRQ#, 'yyyy-mm-dd HH24:MI:SS')</isNotEmpty>
<isEmpty property="SLRQ">NULL</isEmpty>,
<isNotEmpty property="BZ">#BZ#</isNotEmpty>
<isEmpty property="BZ">NULL</isEmpty>,
<isNotEmpty property="BZRL">#BZRL#</isNotEmpty>
<isEmpty property="BZRL">NULL</isEmpty>,
<isNotEmpty property="YXHJ">#YXHJ#</isNotEmpty>
<isEmpty property="YXHJ">NULL</isEmpty>,
<isNotEmpty property="JBDF">#JBDF#</isNotEmpty>
<isEmpty property="JBDF">NULL</isEmpty>,
<isNotEmpty property="HM">#HM#</isNotEmpty>
<isEmpty property="HM">NULL</isEmpty>)
</insert>
<update id="updateBbSy07" parameterClass="com.kmr.domain.BbSy07">
update BB_SY07
set
<isNotEmpty property="BZBH">BZBH=#BZBH#,</isNotEmpty>
<isNotEmpty property="ID">ID=#ID#,</isNotEmpty>
<isNotEmpty property="ZRDWMC">ZRDWMC=#ZRDWMC#,</isNotEmpty>
<isNotEmpty property="LR">LR=#LR#,</isNotEmpty>
<isNotEmpty property="DJ">DJ=#DJ#,</isNotEmpty>
<isNotEmpty property="JZSJ">JZSJ=to_date(#JZSJ#, 'yyyy-mm-dd HH24:MI:SS'),</isNotEmpty>
<isNotEmpty property="NY">NY=#NY#,</isNotEmpty>
<isNotEmpty property="CDBH">CDBH=#CDBH#,</isNotEmpty>
<isNotEmpty property="BMBM">BMBM=#BMBM#,</isNotEmpty>
<isNotEmpty property="CXID">CXID=#CXID#,</isNotEmpty>
<isNotEmpty property="YSBZ">YSBZ=#YSBZ#,</isNotEmpty>
<isNotEmpty property="YDFL">YDFL=#YDFL#,</isNotEmpty>
<isNotEmpty property="CQSJ">CQSJ=#CQSJ#,</isNotEmpty>
<isNotEmpty property="KSSJ">KSSJ=to_date(#KSSJ#, 'yyyy-mm-dd HH24:MI:SS'),</isNotEmpty>
<isNotEmpty property="TJRQ">TJRQ=to_date(#TJRQ#, 'yyyy-mm-dd HH24:MI:SS'),</isNotEmpty>
<isNotEmpty property="RYDL">RYDL=#RYDL#,</isNotEmpty>
<isNotEmpty property="JHSDRQ">JHSDRQ=to_date(#JHSDRQ#, 'yyyy-mm-dd HH24:MI:SS'),</isNotEmpty>
<isNotEmpty property="YXHJDM">YXHJDM=#YXHJDM#,</isNotEmpty>
<isNotEmpty property="ZRDW">ZRDW=#ZRDW#,</isNotEmpty>
<isNotEmpty property="SLRQ">SLRQ=to_date(#SLRQ#, 'yyyy-mm-dd HH24:MI:SS'),</isNotEmpty>
<isNotEmpty property="BZ">BZ=#BZ#,</isNotEmpty>
<isNotEmpty property="BZRL">BZRL=#BZRL#,</isNotEmpty>
<isNotEmpty property="YXHJ">YXHJ=#YXHJ#,</isNotEmpty>
<isNotEmpty property="JBDF">JBDF=#JBDF#,</isNotEmpty>
<isNotEmpty property="HM">HM=#HM#,</isNotEmpty>
ID=#ID#
更新操作的时候最后一个如果不处理就会在PreparedStatement中生成的sql语句多一个逗号。
where 1=1
</update>
<delete id="deleteBbSy07" parameterClass="com.kmr.domain.BbSy07">
delete from BB_SY07
where 1=1
<isNotEmpty property="ID"> ID=#ID#</isNotEmpty> 可以加条件
</delete>
<select id="selectBbSy07Entity" resultClass="com.kmr.domain.BbSy07" parameterClass="com.kmr.domain.BbSy07" resultMap="bbsy07-result">
select BZBH, ID, ZRDWMC, LR, DJ, to_char(JZSJ, 'yyyy-mm-dd HH24:MI:SS') as JZSJ , NY, CDBH, BMBM, CXID, YSBZ, YDFL, CQSJ, to_char(KSSJ, 'yyyy-mm-dd HH24:MI:SS') as KSSJ , to_char(TJRQ, 'yyyy-mm-dd HH24:MI:SS') as TJRQ , RYDL, to_char(JHSDRQ, 'yyyy-mm-dd HH24:MI:SS') as JHSDRQ , YXHJDM, ZRDW, to_char(SLRQ, 'yyyy-mm-dd HH24:MI:SS') as SLRQ , BZ, BZRL, YXHJ, JBDF, HM from BB_SY07
where 1=1
</select>
<select id="selectBbSy07s" resultClass="com.kmr.domain.BbSy07" parameterClass="com.kmr.domain.BbSy07" resultMap="bbsy07-result">
SELECT BZBH, ID, ZRDWMC, LR DLR, DJ, to_char(JZSJ, 'yyyy-mm-dd') as JZSJ , NY, CDBH, BMBM, CXID, YSBZ, YDFL, ICQSJ ICQSJ, to_char(KSSJ, 'yyyy-mm-dd') as KSSJ , to_char(TJRQ, 'yyyy-mm-dd') as TJRQ , RYDL, to_char(JHSDRQ, 'yyyy-mm-dd') as JHSDRQ
, YXHJDM, ZRDW, to_char(SLRQ, 'yyyy-mm-dd') as SLRQ , BZ, BZRL, YXHJ, JBDF, HM,r FROM (
select aa.*,ROWNUM r from (
select NULL BZBH, NULL ID, NULL ZRDWMC, SUM(NVL(LR,0)) LR, NULL DJ, NULL JZSJ , NULL NY , NULL CDBH, NULL BMBM, NULL CXID, NULL YSBZ, NULL YDFL, NULL ICQSJ, NULL KSSJ , NULL TJRQ , SUM(NVL(RYDL,0)) RYDL, NULL JHSDRQ , NULL YXHJDM,NULL ZRDW,NULL SLRQ ,NULL BZ,
SUM(NVL(BZRL,0)) BZRL , NULL YXHJ, SUM(NVL(JBDF,0)) JBDF, '合计' HM from BB_SY07 WHERE 1=1
<isNotEmpty property="CXID">and CXID=#CXID#</isNotEmpty>
<isNotEmpty property="BZBH">and BZBH=#BZBH#</isNotEmpty>
<isNotEmpty property="HM">AND HM like '%$HM$%'</isNotEmpty>
<isEqual property="ssss10" compareValue='0'>
and CQSJ
<![CDATA[<]]>
15
</isEqual>
<isEqual property="ssss10" compareValue='1'>
and CQSJ
<![CDATA[>=]]>
15 and CQSJ
<![CDATA[<]]>
30
</isEqual>
<isEqual property="ssss10" compareValue='2'>
and CQSJ
<![CDATA[>=]]>
30
</isEqual>
union
select BZBH, ID, ZRDWMC, NVL(LR,0) LR, DJ, JZSJ , NY, CDBH, BMBM, CXID, YSBZ, YDFL, CQSJ ICQSJ, KSSJ , TJRQ ,NVL(RYDL,0) RYDL, JHSDRQ , YXHJDM, ZRDW,SLRQ , BZ,
NVL(BZRL,0) BZRL, YXHJ, NVL(JBDF,0) JBDF, HM from BB_SY07 WHERE 1=1
<isNotEmpty property="CXID">and CXID=#CXID#</isNotEmpty>
<isNotEmpty property="BZBH">and BZBH=#BZBH#</isNotEmpty>
<isNotEmpty property="HM">AND HM like '%$HM$%'</isNotEmpty>
<isEqual property="ssss10" compareValue='0'>
and CQSJ
<![CDATA[<]]>
15
</isEqual>
<isEqual property="ssss10" compareValue='1'>
and CQSJ
<![CDATA[>=]]>
15 and CQSJ
<![CDATA[<]]>
30
</isEqual>
<isEqual property="ssss10" compareValue='2'>
and CQSJ
<![CDATA[>=]]>
30
</isEqual>
order by BZRL DESC
) aa where ROWNUM
<![CDATA[<=]]>这里的 纯文本区不能乱用,它是用来防止一些特殊符号使得xml文件发生紊乱。
#rowEnd# ) WHERE r
<![CDATA[>]]> 这里千万不要用大于等于,否则分页会出问题。(rownum是从1开始的)
#rowStart#
</select>
注意selectBbSy07s和selectBbSy07sCount中的查询条件要相同,这个是与分页有关的。
count(*) 和 count(1)都是查询符合条件的记录总条数
<select id="selectBbSy07sCount" resultClass="long" parameterClass="com.kmr.domain.BbSy07">
select count(1) from (
select 1 from dual
union all
select 1 from BB_SY07 WHERE 1=1
<isNotEmpty property="CXID">and CXID=#CXID#</isNotEmpty>
<isNotEmpty property="BZBH">and BZBH=#BZBH#</isNotEmpty>
<isNotEmpty property="HM">AND HM like '%$HM$%'</isNotEmpty>
<isEqual property="ssss10" compareValue='0'>
and CQSJ
<![CDATA[<]]>
15
</isEqual>
<isEqual property="ssss10" compareValue='1'>
and CQSJ
<![CDATA[>=]]>
15 and CQSJ
<![CDATA[<]]>
30
</isEqual>
<isEqual property="ssss10" compareValue='2'>
and CQSJ
<![CDATA[>=]]>
30
</isEqual>
)
</select>
存储过程由于它的值是从参数中得来的所以用Map存储一下
<parameterMap id="PROC_BB_SY07_MAP" class="java.util.HashMap">property的值要跟存储过程中的字段名相同,也得跟Map中的键值相同。
<parameter property="A_CXID" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="A_BMBM" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="A_KHDB" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="A_NY" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="A_KSSJ" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="A_JZSJ" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="A_CZY" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN" />
<parameter property="REINT" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT" />
<parameter property="REMSG" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" />
</parameterMap>
<procedure id="PROC_BB_SY07" parameterMap="PROC_BB_SY07_MAP">
<![CDATA[ {call PROC_BB_SY07(?,?,?,?,?,?,?,?,?,?)} ]]>这一行的红色部分是存储过程名
</procedure>
</sqlMap>