这是一个ibatIS xml文件

来源：互联网发布：淘宝返利网是骗局吗编辑：程序博客网时间：2024/06/03 18:47

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="t_um_message" >
<resultMap id="PubMessageResult" class="com._21cn.um.entity.PubMessage" >
<result column="msg_id" property="msgId" jdbcType="BIGINT" />
<result column="account_id" property="accountId" jdbcType="BIGINT" />
<result column="msg_type" property="msgType" jdbcType="VARCHAR" />
<result column="title" property="title" jdbcType="VARCHAR" />
<result column="status" property="status" jdbcType="INTEGER" />
<result column="push_type" property="pushType" jdbcType="INTEGER" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="send_time" property="sendTime" jdbcType="TIMESTAMP" />
<result column="total_count" property="totalCount" jdbcType="INTEGER" />
<result column="filter_count" property="filterCount" jdbcType="INTEGER" />
<result column="sent_count" property="sentCount" jdbcType="INTEGER" />
<result column="offline_count" property="offlineCount" jdbcType="INTEGER" />
<result column="fail_count" property="failCount" jdbcType="INTEGER" />
<result column="read_count" property="readCount" jdbcType="INTEGER" />
</resultMap>
<sql id="ByMap_Where_Clause" >
<dynamic prepend="where" >
<isPropertyAvailable prepend="and" property="eqMsgId" >
msg_id = #eqMsgId:BIGINT#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="inMsgIds" >
msg_id in ( $inMsgIds$ )
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="inMsgIdList" >
msg_id in <iterate prepend="" property="inMsgIdList" open="(" close=")"
conjunction=",">#inMsgIdList[]:BIGINT#</iterate>
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqAccountId" >
account_id = #eqAccountId:BIGINT#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtAccountId" >
account_id <![CDATA[ > ]]> #gtAccountId:BIGINT#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqMsgType" >
msg_type = #eqMsgType:VARCHAR#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="likeMsgType" >
msg_type like #likeMsgType:VARCHAR#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqTitle" >
title = #eqTitle:VARCHAR#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="likeTitle" >
title like #likeTitle:VARCHAR#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqStatus" >
status = #eqStatus:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="or" property="eqStatus2" >
status = #eqStatus2:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqPushType" >
push_type = #eqPushType:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtCreateTime" >
create_time <![CDATA[ > ]]> #gtCreateTime:TIMESTAMP#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="ltCreateTime" >
create_time <![CDATA[ < ]]> #ltCreateTime:TIMESTAMP#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtSendTime" >
send_time <![CDATA[ > ]]> #gtSendTime:TIMESTAMP#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="ltSendTime" >
send_time <![CDATA[ < ]]> #ltSendTime:TIMESTAMP#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqTotalCount" >
total_count = #eqTotalCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtTotalCount" >
total_count <![CDATA[ > ]]> #gtTotalCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqFilterCount" >
filter_count = #eqFilterCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtFilterCount" >
filter_count <![CDATA[ > ]]> #gtFilterCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqSentCount" >
sent_count = #eqSentCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtSentCount" >
sent_count <![CDATA[ > ]]> #gtSentCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqOfflineCount" >
offline_count = #eqOfflineCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtOfflineCount" >
offline_count <![CDATA[ > ]]> #gtOfflineCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqFailCount" >
fail_count = #eqFailCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtFailCount" >
fail_count <![CDATA[ > ]]> #gtFailCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="eqReadCount" >
read_count = #eqReadCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="gtReadCount" >
read_count <![CDATA[ > ]]> #gtReadCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="and" property="increaseReadCount" >
read_count = read_count + #increaseCount:INTEGER#
</isPropertyAvailable>
</dynamic>
</sql>
<sql id="ByMap_OrderBy_Clause" >
<dynamic prepend="order by" >
<isEqual property="sortName" compareValue="msgId" >
msg_id
</isEqual>
<isEqual property="sortName" compareValue="accountId" >
account_id
</isEqual>
<isEqual property="sortName" compareValue="msgType" >
msg_type
</isEqual>
<isEqual property="sortName" compareValue="title" >
title
</isEqual>
<isEqual property="sortName" compareValue="status" >
status
</isEqual>
<isEqual property="sortName" compareValue="createTime" >
create_time
</isEqual>
<isEqual property="sortName" compareValue="sendTime" >
send_time
</isEqual>
<isEqual property="sortName" compareValue="totalCount" >
total_count
</isEqual>
<isEqual property="sortName" compareValue="filterCount" >
filter_count
</isEqual>
<isEqual property="sortName" compareValue="sentCount" >
sent_count
</isEqual>
<isEqual property="sortName" compareValue="offlineCount" >
offline_count
</isEqual>
<isEqual property="sortName" compareValue="failCount" >
fail_count
</isEqual>
<isEqual property="sortName" compareValue="readCount" >
read_count
</isEqual>
<isEqual property="sortOrder" compareValue="desc" >
DESC
</isEqual>
<isEqual property="sortOrder" compareValue="asc" >
ASC
</isEqual>
</dynamic>
</sql>
<select id="selectById" resultMap="PubMessageResult" parameterClass="java.lang.Long" >
select msg_id, account_id, msg_type, title, status,push_type, create_time, send_time, total_count,
filter_count, sent_count, offline_count, fail_count, read_count
from t_um_message
where msg_id = #value#
</select>
<select id="selectByMap" resultMap="PubMessageResult" parameterClass="java.util.Map" >
select msg_id, account_id, msg_type, title, status,push_type, create_time, send_time, total_count,
filter_count, sent_count, offline_count, fail_count, read_count
from t_um_message
<isParameterPresent >
<include refid="t_um_message.ByMap_Where_Clause" />
<isPropertyAvailable property="orderByClause" >
order by $orderByClause$
</isPropertyAvailable>
<isPropertyAvailable property="sortName" >
<include refid="t_um_message.ByMap_OrderBy_Clause" />
</isPropertyAvailable>
<include refid="BASE_DAO.pagination_End" />
</isParameterPresent>
</select>
<select id="selectByMap_count" resultClass="int" parameterClass="java.util.Map" >
select count(1) from t_um_message
<isParameterPresent >
<include refid="t_um_message.ByMap_Where_Clause" />
</isParameterPresent>
</select>
<delete id="deleteById" parameterClass="java.lang.String" >
delete from t_um_message
where msg_id = #value#
</delete>
<delete id="deleteByMap" parameterClass="java.util.Map" >
delete from t_um_message
<include refid="t_um_message.ByMap_Where_Clause" />
</delete>
<insert id="insert" parameterClass="com._21cn.um.entity.PubMessage" >
insert into t_um_message (msg_id, account_id, msg_type, title, status, push_type,create_time, send_time,
total_count, filter_count, sent_count, offline_count, fail_count, read_count)
values (#msgId:BIGINT#, #accountId:BIGINT#, #msgType:VARCHAR#, #title:VARCHAR#,
#status:INTEGER#,#pushType:INTEGER#, #createTime:TIMESTAMP#, #sendTime:TIMESTAMP#, #totalCount:INTEGER#,
#filterCount:INTEGER#, #sentCount:INTEGER#, #offlineCount:INTEGER#, #failCount:INTEGER#,
#readCount:INTEGER#)
</insert>
<update id="updateById" parameterClass="com._21cn.um.entity.PubMessage" >
update t_um_message
set account_id = #accountId:BIGINT#,
msg_type = #msgType:VARCHAR#,
title = #title:VARCHAR#,
status = #status:INTEGER#,
push_type= #pushType:INTEGER#,
send_time = #sendTime:TIMESTAMP#,
total_count = #totalCount:INTEGER#,
filter_count = #filterCount:INTEGER#,
sent_count = #sentCount:INTEGER#,
offline_count = #offlineCount:INTEGER#,
fail_count = #failCount:INTEGER#,
read_count = #readCount:INTEGER#
where msg_id = #msgId:BIGINT#
</update>
<update id="updateByMap" parameterClass="java.util.Map" >
update t_um_message
<dynamic prepend="set" >
<isPropertyAvailable prepend="," property="accountId" >
account_id = #accountId:BIGINT#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="msgType" >
msg_type = #msgType:VARCHAR#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="title" >
title = #title:VARCHAR#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="status" >
status = #status:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="pushType" >
push_type = #pushType:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="sendTime" >
send_time = #sendTime:TIMESTAMP#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="totalCount" >
total_count = #totalCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="filterCount" >
filter_count = #filterCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="sentCount" >
sent_count = #sentCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="offlineCount" >
offline_count = #offlineCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="failCount" >
fail_count = #failCount:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="readCount" >
read_count = #readCount:INTEGER#
</isPropertyAvailable>
</dynamic>
<isParameterPresent >
<include refid="t_um_message.ByMap_Where_Clause" />
</isParameterPresent>
</update>
</sqlMap>

<isPropertyAvailable prepend="or" property="eqStatus2" >
status = #eqStatus2:INTEGER#
</isPropertyAvailable>

这个地方有个or条件，当我有and 又有 or 时，特别不好用。求解

数据库查询注意事项：

注意 or 的使用，顺序是不一样的。我的解决办法：

<isPropertyAvailable prepend="and" property="orStatus" >
( status = #orStatus:INTEGER#
</isPropertyAvailable>
<isPropertyAvailable prepend="or" property="orStatus2" >
status = #orStatus2:INTEGER# )
</isPropertyAvailable>

加上括号就能优先执行or条件了

<isPropertyAvailable prepend="and" property="inMsgIdList" >
msg_id in <iterate prepend="" property="inMsgIdList" open="(" close=")"
conjunction=",">#inMsgIdList[]:BIGINT#</iterate>
</isPropertyAvailable>

inMsgIdList是一个list，把需要查询的条件list放到map ，就能查询

0 0