接上篇文章:http://blog.csdn.net/rishengcsdn/article/details/39206993
继续测试动态查询的语句和翻页查询语句。
Test3.java源码:
package domain;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.springdemo.usermgr.vo.SUser;
public class Test3 {
public static void main(String[] args) throws IOException {
String resource = "config.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = ssf.openSession(false); //true 为自动提交事务
try {
SUser condi=new SUser();
condi.setUserName("中文名zhou");
condi.setPwd("y"); //模糊条件
List<SUser> as=session.selectList("dynamicWhereTest", condi);
System.out.println("查询结果:"+as.size());
Map<String, Object> parms = new HashMap<String, Object>();
// parms.put("pwd", "x");
parms.put("stanum", 2); //从第2条纪录起,查出4条记录,不含第2条
parms.put("offset", 4);
List<SUser> as2=session.selectList("dynamicWherePage", parms);
System.out.println("翻页结果:"+as2.size());
session.commit(true);
} catch (Exception e) {
session.rollback(true);
e.printStackTrace();
} finally {
session.close();
}
}
}
修改SUser.xml,增加翻页查询和动态条件查询
<?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.springdemo.usermgr.vo.SUserMapper">
<select id="selectSUser" parameterType="int" resultType="SUser">
select * from Suser where id = #{id}
</select>
<select id="getSUser" parameterType="String" resultType="SUser">
select * from Suser where username = #{name}
</select>
<insert id="insertSUser" parameterType="SUser">
<selectKey resultType="int" keyProperty="id">
SELECT LAST_INSERT_ID()
</selectKey>
insert into
suser(userName,pwd,signUpTime)values(#{userName},#{pwd},#{signUpTime})
</insert>
<select id="dynamicWhereTest" parameterType="SUser" resultType="SUser">
select * from Suser
<where>
<if test="userName != null">
userName = #{userName}
</if>
<if test="pwd != null">
and pwd LIKE CONCAT('%', CONCAT(#{pwd}, '%'))
</if>
</where>
</select>
<select id="dynamicWherePage" parameterType="java.util.Map" resultType="SUser">
select * from Suser
<where>
<if test="pwd != null">
and pwd LIKE CONCAT('%', CONCAT(#{pwd}, '%'))
</if>
</where>
limit #{stanum},#{offset}
</select>
</mapper>
数据库,suser表记录也增加一些记录,便于测试查询结果:
运行test3,可能的结果为:
查询结果:3
翻页结果:4
===============================================================
条件判断的一点BUG:
<if test="querC != null and querC != ''"> 使用的ONGL表达式,在判断条件为空值时候一般这么写
<if test="querC != null and querC != ''">,没有问题。
但是在判断==条件的时候会出问题。
<if test=" querC =='1'">
这种写法会出错,在querC=“0”时候,仍然判断为否。
必须修改写法为:
<if test='querC =="1"'>
这样才行。
========================================================
开源翻页插件,使用pagehelper5.0.0插件,数据库为mysql5.0,导入jar包pagehelper-5.0.0.jar,jsqlparser-0.9.5.jar:
https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
spring MVC 配置:
<!-- myBatis文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<!-- <property name="configLocation" value="classpath:config/mapper/configuration.xml"/> -->
<!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
<property name="mapperLocations" value="classpath:mybat/*.xml"/>
<property name="typeAliasesPackage" value="com.squgrc.vo"/>
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<!-- 这里的几个配置主要演示如何使用,如果不理解,一定要去掉下面的配置 -->
<property name="properties">
<value>
helperDialect=mysql
reasonable=true
supportMethodsArguments=true
params=count=countSql
autoRuntimeDialect=true
</value>
</property>
</bean>
</array>
</property>
</bean>
java代码部分:
service层:
public PageInfo selectList(String card_id,String realname,String phone,String credittype,String srcInfo,Integer pageNumber,Integer pageSize){
Map<String, String> map = new HashMap<String, String>();
map.put("name", realname);
map.put("idcard", card_id);
map.put("phone", phone);
map.put("status", credittype); //名单类型,0-白名单,1-灰名单,2-黑名单,
map.put("type", srcInfo); //0-手动倒入,1-系统导入
PageHelper.startPage(pageNumber, pageSize);
List<UserStatusVo> sVoList= impCreditTypelogMap.selectList(map);
PageInfo page = new PageInfo(sVoList);
formatImpCredit(sVoList); //将数据格式化输出
return page;
}
ctrl层:
@RequestMapping("getList.do")
@ResponseBody
public HashMap<String, Object> getImpCreditList(String card_id, String realname, String phone, String credittype,
String srcInfo,Integer pageNumber,Integer pageSize) {
HashMap<String, Object> retTable = new HashMap<String, Object>();
PageInfo pageRet = impCreditService.selectList(card_id, realname, phone, credittype, srcInfo,pageNumber,pageSize);
retTable.put("RespDesc", "成功!");
retTable.put("RespCode", "000");
//retTable.put("RetList", list);
retTable.put("rows", pageRet.getList());
retTable.put("total",pageRet.getTotal());
return retTable;
}
注意:mybati的sql语句不要太复杂,不要包含limit等已经存在翻页查询语句。只有第一条sql语句会被插件翻译并且加上翻页条件。