<select id="selectById" parameterType="String" resultType="com.zl.wx.entity.WeiXinQuery"> select <include refid="Base_Column_List" /> from message where id=#{id,jdbcType=VARCHAR} </select> <select id="selectForPage" parameterType="com.zl.wx.entity.WeiXinQuery" resultType="com.zl.wx.entity.WeiXinQuery"> select <include refid="Base_Column_List" /> from message <where> <if test="user_name != null and user_name != ''"> user_name like concat(concat('%',#{user_name}),'%') </if> </where> </select> <select id="selectCountForPage" parameterType="com.zl.wx.entity.WeiXinQuery" resultType="int"> select count(1) from message <where> <if test="user_id != null and user_id != ''"> user_id like concat(concat('%',#{user_id}),'%') </if> </where> </select>
<select id="userList" parameterType="Map" resultType="com.zl.wx.entity.WeiXinQuery"> SELECT * FROM ( SELECT * FROM ( (SELECT m.send_time,1 AS mtype ,m.user_id,m.user_name FROM message m <where> <if test="user_id != null and user_id != ''"> and m.user_id =#{user_id,jdbcType=VARCHAR} </if> </where> ) UNION (SELECT p.send_time,2 AS mtype,p.user_id ,p.user_name FROM picture p <where> <if test="user_id != null and user_id != ''"> and p.user_id =#{user_id,jdbcType=VARCHAR} </if> </where> ) ) t ORDER BY send_time DESC)q GROUP BY user_id ORDER BY send_time DESC </select> <select id="messageList" parameterType="Map" resultType="com.zl.wx.entity.WeiXinQuery"> SELECT m.send_time, m.content,1 AS mtype ,m.user_id FROM message m <where> <if test="start_time != null and start_time != ''"> m.send_time >= date_format(#{start_time,jdbcType=VARCHAR},'%Y-%m-%d') </if> <if test="end_time != null and end_time != ''"> and m.send_time <= date_format(#{end_time,jdbcType=VARCHAR},'%Y-%m-%d') </if> <if test="user_id != null and user_id != ''"> and m.user_id =#{user_id,jdbcType=VARCHAR} </if> <if test="content != null and content != ''"> and m.content like concat(concat('%',#{content}),'%') </if> </where> <if test="content == null or content == ''"> UNION SELECT p.send_time,p.pic_url,2 AS mtype,p.user_id FROM picture p <where> <if test="start_time != null and start_time != ''"> p.send_time >= date_format(#{start_time,jdbcType=VARCHAR},'%Y-%m-%d') </if> <if test="end_time != null and end_time != ''"> and p.send_time <= date_format(#{end_time,jdbcType=VARCHAR},'%Y-%m-%d') </if> <if test="user_id != null and user_id != ''"> and p.user_id =#{user_id,jdbcType=VARCHAR} </if> </where> </if> ORDER BY send_time DESC </select> </mapper> 这个就是我这个xml的代码了。
卤煮你所谓的分页是怎么体现的?? sql里面查询的 select count(1) from message.... 其实就是计算一共有多少符合条件的行 你分页是在jsp上做的吧
<?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.zl.wx.dao.WeiXinQueryDao">
<sql id="Base_Column_List">
ID,USER_ID,USER_NAME,CONTENT,SEND_TIME,MSG_ID
</sql>
<select id="selectById" parameterType="String" resultType="com.zl.wx.entity.WeiXinQuery">
select <include refid="Base_Column_List" />
from message where id=#{id,jdbcType=VARCHAR}
</select>
<select id="selectForPage" parameterType="com.zl.wx.entity.WeiXinQuery"
resultType="com.zl.wx.entity.WeiXinQuery">
select
<include refid="Base_Column_List" />
from message
<where>
<if test="user_name != null and user_name != ''">
user_name like concat(concat('%',#{user_name}),'%')
</if>
</where>
</select>
<select id="selectCountForPage" parameterType="com.zl.wx.entity.WeiXinQuery"
resultType="int">
select count(1) from message
<where>
<if test="user_id != null and user_id != ''">
user_id like concat(concat('%',#{user_id}),'%')
</if>
</where>
</select>
<select id="userList" parameterType="Map" resultType="com.zl.wx.entity.WeiXinQuery">
SELECT * FROM
(
SELECT * FROM
(
(SELECT m.send_time,1 AS mtype ,m.user_id,m.user_name
FROM message m
<where>
<if test="user_id != null and user_id != ''">
and m.user_id =#{user_id,jdbcType=VARCHAR}
</if>
</where>
)
UNION
(SELECT p.send_time,2 AS mtype,p.user_id ,p.user_name
FROM picture p
<where>
<if test="user_id != null and user_id != ''">
and p.user_id =#{user_id,jdbcType=VARCHAR}
</if>
</where>
)
) t
ORDER BY send_time DESC)q
GROUP BY user_id
ORDER BY send_time DESC
</select> <select id="messageList" parameterType="Map" resultType="com.zl.wx.entity.WeiXinQuery">
SELECT m.send_time, m.content,1 AS mtype ,m.user_id
FROM message m
<where>
<if test="start_time != null and start_time != ''">
m.send_time >= date_format(#{start_time,jdbcType=VARCHAR},'%Y-%m-%d')
</if>
<if test="end_time != null and end_time != ''">
and m.send_time <= date_format(#{end_time,jdbcType=VARCHAR},'%Y-%m-%d')
</if>
<if test="user_id != null and user_id != ''">
and m.user_id =#{user_id,jdbcType=VARCHAR}
</if>
<if test="content != null and content != ''">
and m.content like concat(concat('%',#{content}),'%')
</if>
</where>
<if test="content == null or content == ''">
UNION
SELECT p.send_time,p.pic_url,2 AS mtype,p.user_id FROM picture p
<where>
<if test="start_time != null and start_time != ''">
p.send_time >= date_format(#{start_time,jdbcType=VARCHAR},'%Y-%m-%d')
</if>
<if test="end_time != null and end_time != ''">
and p.send_time <= date_format(#{end_time,jdbcType=VARCHAR},'%Y-%m-%d')
</if>
<if test="user_id != null and user_id != ''">
and p.user_id =#{user_id,jdbcType=VARCHAR}
</if>
</where>
</if>
ORDER BY send_time DESC
</select>
</mapper>
这个就是我这个xml的代码了。
sql里面查询的 select count(1) from message....
其实就是计算一共有多少符合条件的行
你分页是在jsp上做的吧
q.setFirstResult(offset); //offset开始记录
q.setMaxResults(length); //每页显示数目