条件筛选（[尚硅谷]_张晓飞_Mybatis 学习笔记五）

 

准备数据

create table d_user(  id int primary key auto_increment,  name varchar(10),age int(3)); insert into d_user(name,age) values('Tom',12);  insert into d_user(name,age) values('Bob',13);  insert into d_user(name,age) values('Jack',18);

 

与数据库表对应的javabean

package com.hb.bean;public class User {private int id;private String name;private int age;private int minAge;private int maxAge;    public int getMinAge() {return minAge;}public void setMinAge(int minAge) {this.minAge = minAge;}public int getMaxAge() {return maxAge;}public void setMaxAge(int maxAge) {this.maxAge = maxAge;}//get,set方法public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public User(int id, String name, int age) {super();this.id = id;this.name = name;this.age = age;}public User() {}@Overridepublic String toString() {return "User [id=" + id + ", name=" + name + ", age=" + age + "]";}}

备注：javabean比user表多了minAge喝maxAge两个属性，用于数据筛选。 

 

数据库表与javabean的映射文件

<?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.hb.bean.userMapper"><!-- 这里namespace必须是“包名 + xml文件名”，不然要运行的时候要报错 “is not known to the MapperRegistry” --><select id="getUser" parameterType="com.hb.bean.User"resultType="com.hb.bean.User">select * from user where age>=#{minAge} and age&lt;=#{maxAge}<if test='name!=null'>and name like #{name}</if></select></mapper>

 

测试类

package com.hb.test5;import java.io.InputStream;import java.util.List;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.Before;import org.junit.Test;import com.hb.bean.User;public class Test5 {public static SqlSessionFactory sqlSessionFactory;public static SqlSession sqlSession;@Beforepublic void init(){//"/"表示是在src根目录下String source = "config.xml";InputStream inputStream = Test5.class.getClassLoader().getResourceAsStream(source);try {sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//sqlSession = sqlSessionFactory.openSession();//是否自动提交，默认不是自动提交的sqlSession = sqlSessionFactory.openSession(true);} catch (Exception e) {e.printStackTrace();}}@Testpublic void selectByCondition(){String statment = "com.hb.bean.userMapper.getUser";User conditon = new User();conditon.setMaxAge(15);conditon.setMinAge(1);//conditon.setName("%a%");//查询list集合List<User> users = sqlSession.selectList(statment, conditon);System.out.println(users);}}

 

测试打印的日志

 

2015-02-24 11:54:42,824 [main] DEBUG [com.hb.bean.userMapper.getUser] - ooo Using Connection [com.mysql.jdbc.Connection@11fda7c]2015-02-24 11:54:42,824 [main] DEBUG [com.hb.bean.userMapper.getUser] - ==>  Preparing: select * from user where age>=? and age<=? 2015-02-24 11:54:42,887 [main] DEBUG [com.hb.bean.userMapper.getUser] - ==> Parameters: 1(Integer), 15(Integer)[User [id=8, name=Tom, age=12], User [id=9, name=Bob, age=13]]

 

mybatis的配置文件

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration     PUBLIC "-//mybatis.org//DTD Config 3.0//EN"     "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><settings><!-- changes from the defaults for testing --><setting name="cacheEnabled" value="false" /><setting name="useGeneratedKeys" value="true" /><setting name="defaultExecutorType" value="REUSE" /></settings><environments default="development"><environment id="development"><transactionManager type="jdbc" /><dataSource type="POOLED"><!-- <property name="driver" value="oracle.jdbc.driver.OracleDriver"/> <property name="url" value="jdbc:oracle:thin:@192.168.56.201:1521:system"/> --><property name="driver" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://localhost:3306/mybatis" /><property name="username" value="root" /><property name="password" value="123456" /></dataSource></environment></environments><mappers><mapper resource="com/hb/bean/userMapper.xml" /></mappers></configuration>

 

---

 下面是动态配置SQL的实例

<!-- 分页通过业务查询所有的机构 --><select id="queryOrgByApp" parameterType="java.util.Map" resultType="adtec.ta_org_relationManager.model.Ta_org_relation">select a.orgRelationId,a.appid,a.ta_id,a.orgId,c.orgName,b.appName  from ta_org_relation a,ta_app b,ta_organization c where a.appid = b.appid   and a.orgId = c.orgId   and a.appid=#{appid}   <if test="orgId !=null">   and a.orgId in      <foreach item="item" index="index" collection="list" open="(" separator="," close=")">            #{item}      </foreach>    </if> limit #{start},#{pageSize}</select>