前面我们花费很多篇幅来介绍核心元素<resultMap>的使用,在日常开发中,基本的静态的查询情况已经足够对付。但有些时候,我们想寻求一个能够根据参数自动调整SQL查询的方法。如,学校教导主任发现一个学生违反校纪校规,但是并没有当场抓住该学生,于是,他通过已经掌握的学生信息如【性别】,【年纪】,【身高】等信息来查询符合条件的学生。但是,这些信息的数量每次得到的数量都是不同的。换句话说,我们在设计系统时,无法确定传入参数的数量。也没有获得一个足以唯一确定这个对象的主键【如学号】。接下来我们就来演示,如何通过Mybatis的<if>元素来实现我们动态查询的需求。
准备工作:
a.操作系统 :win7 x64
b.基本软件:MySQL,Mybatis,spring,SQLyog
-------------------------------------------------------------------------------------------------------------------------------------
【为了方便各位看官学习,我们把本例所有的文件再复述一次,如果已经有前文工程的读者,可以只修改差异的部分即可。】
1.作为演示,我们就不再设计新的数据库,及模型。建议复制上文中使用的工程,创建为Mybatis06,工程修改之后的目录结构如下:
2.pom文件的内容如下:
- <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
- <modelVersion>4.0.0</modelVersion>
-
- <groupId>com.java.mybatis</groupId>
- <artifactId>mybatis01</artifactId>
- <version>0.0.1-SNAPSHOT</version>
- <packaging>jar</packaging>
-
- <name>mybatis01</name>
- <url>http://maven.apache.org</url>
-
- <properties>
- <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
- </properties>
-
- <dependencies>
- <dependency>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- <version>4.12</version>
- <scope>test</scope>
- </dependency>
- <dependency>
- <groupId>org.mybatis</groupId>
- <artifactId>mybatis</artifactId>
- <version>3.3.1</version>
- </dependency>
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- <version>5.1.26</version>
- </dependency>
- <dependency>
- <groupId>log4j</groupId>
- <artifactId>log4j</artifactId>
- <version>1.2.17</version>
- </dependency>
- </dependencies>
- </project>
3.UserInfo.java文件的内容如下:
- package com.csdn.ingo.entity;
-
- import java.io.Serializable;
-
-
-
-
-
-
- @SuppressWarnings("serial")
- public class UserInfo implements Serializable {
-
- private String userid;
- private String department;
- private String position;
- private String mobile;
- private String gender;
- private String email;
-
-
- }
4.UserInfoDao.java文件的内容如下:
- package com.csdn.ingo.dao;
-
- import java.util.List;
- import java.util.Map;
-
- import com.csdn.ingo.entity.UserInfo;
-
-
-
-
-
-
- public interface UserInfoDao {
- List<UserInfo> findUserInfoByParams(Map<String,Object> map);
- }
5.SqlSessionFactoryUtil.java文件的内容如下:
- package com.csdn.ingo.util;
-
- import java.io.InputStream;
-
- import org.apache.ibatis.io.Resources;
- import org.apache.ibatis.session.SqlSession;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.session.SqlSessionFactoryBuilder;
-
- public class SqlSessionFactoryUtil {
-
- private static SqlSessionFactory sqlSessionFactory;
-
- public static SqlSessionFactory getSqlSessionFactory(){
- if(sqlSessionFactory==null){
- InputStream inputStream=null;
- try{
- inputStream=Resources.getResourceAsStream("mybatis-config.xml");
- sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
- }catch(Exception e){
- e.printStackTrace();
- }
- }
- return sqlSessionFactory;
- }
-
- public static SqlSession openSession(){
- return getSqlSessionFactory().openSession();
- }
- }
6.UserInfoMapper.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.csdn.ingo.dao.UserInfoDao">
- <resultMap type="userInfo" id="UserInfoResult">
- <id property="userid" column="userid" />
- <result property="department" column="department" />
- <result property="position" column="position" />
- <result property="mobile" column="mobile" />
- <result property="gender" column="gender" />
- <result property="email" column="email" />
- </resultMap>
- <select id="findUserInfoByParams" parameterType="Map" resultMap="UserInfoResult">
- select * from userinfo
- where department=#{department}
- <if test="gender!=null">
- and gender = #{gender}
- </if>
- <if test="position!=null">
- and position like #{position}
- </if>
- </select>
- </mapper>
【注意】
这里的where之后至少有一个department条件。而gender,position条件会动态的判断。这里的处理逻辑就是:
1.假设,没有传入动态参数,那么只会按照department条件查询
2.如果,传入了动态的参数,则就在department条件上,在加入动态参数查询。
具体的查询结果,请继续跟着我们完善工程中的其他文件后,运行单元测试来观察实际结果吧。
7.jdbc.properties文件内容如下:
- jdbc.driverClassName=com.mysql.jdbc.Driver
- jdbc.url=jdbc:mysql://localhost:3306/mybatis
- jdbc.username=root
- jdbc.password=1234
8.log4j.properties文件内容如下:
- log4j.rootLogger=debug,stdout
- log4j.appender.stdout=org.apache.log4j.ConsoleAppender
- log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
- log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
9.mybatis-config.xml文件内容如下:
- <?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>
- <properties resource="jdbc.properties"/>
- <settings>
- <setting name="logImpl" value="LOG4J"/>
- </settings>
- <typeAliases>
- <package name="com.csdn.ingo.entity"/>
- </typeAliases>
- <environments default="development">
- <environment id="development">
- <transactionManager type="JDBC" />
- <dataSource type="POOLED">
- <property name="driver" value="${jdbc.driverClassName}" />
- <property name="url" value="${jdbc.url}" />
- <property name="username" value="${jdbc.username}" />
- <property name="password" value="${jdbc.password}" />
- </dataSource>
- </environment>
- </environments>
- <mappers>
- <mapper resource="mappers/UserInfoMapper.xml" />
- </mappers>
- </configuration>
10.单元测试方法如下:
- public class UserTest {
- private static Logger log = Logger.getLogger(UserTest.class);
-
- private SqlSession sqlSession = null;
-
- @Before
- public void setUp() throws Exception {
- log.info("方法执行前调用");
- sqlSession = SqlSessionFactoryUtil.openSession();
- }
-
- @After
- public void tearDown() throws Exception {
- log.info("方法执行后调用");
- sqlSession.close();
- }
-
- @Test
- public void testSelet() {
- Map<String,Object> map=new HashMap<String,Object>();
- map.put("department", "2");
- map.put("gender", "1");
- map.put("position", "%售%");
- UserInfoDao userInfo = sqlSession.getMapper(UserInfoDao.class);
- List<UserInfo> UIList= userInfo.findUserInfoByParams(map);
- for(UserInfo ui:UIList){
- System.out.println(ui.toString());
- }
- }
- }
11.运行测试方法,我们给出一个示例输出,更多参数内容还请读者自行设计:
=============================================================================
至此,<if>元素的基本用法结束,下面我们来看看更加灵活的用法!
=============================================================================
回顾一下,如果读者有看过前面两篇例子的话,一定还记的<association>这个标签,并且应该还记得我们如何通过<association>实现集合查询。
1.首先,我们先来现实看看如何根据集合属性中的值来查询条目。举个例子,我们想查询某个部门中的,爱好有篮球的人。这里的爱好是一个集合属性。当然,这里我们只是说明问题,并不再创建这张表出来,各位读者不要太在意这个细节,举一反三的能力还是要有的。
2.我们先修改UserInfo.Java对象,具体内容如下:
- @SuppressWarnings("serial")
- public class UserInfo implements Serializable {
-
- private String userid;
- private String department;
- private String position;
- private String mobile;
- private String gender;
- private String email;
- private Departments depart;
-
-
- }
2.修改UserInfoMapper.xml,具体内容如下:
- <select id="findUserInfoByParams" parameterType="Map" resultMap="UserInfoResult">
- select * from userinfo
- where gender = #{gender}
- <if test="position!=null">
- and position like #{position}
- </if>
- <if test="d != null and d.id != null">
- AND department = #{d.id}
- </if>
- </select>
3.对应的,单元测试的方法,我们也需要更改,如下:
- @Test
- public void testSelet() {
- Map<String,Object> map=new HashMap<String,Object>();
- map.put("gender", "1");
- map.put("position", "%售%");
- Departments d = new Departments("1","%售%");
- UserInfoDao userInfo = sqlSession.getMapper(UserInfoDao.class);
- List<UserInfo> UIList= userInfo.findUserInfoByParams(map);
- for(UserInfo ui:UIList){
- System.out.println(ui.toString());
- }
- }
4.运行单元测试方法,应该能够看到如下输出
这里完整的输出如下:
- UserInfo [userid=customer, department=2, position=售前, mobile=33334444, gender=1, email=customer@email.com, depart=null]
- UserInfo [userid=customer2, department=2, position=售后, mobile=55556666, gender=1, email=customer@email.com, depart=null]
细心的读者已经发现,我们上面的depart输出为null,如果我们想要在结果集合中再加入部门表的信息。这里就请读者一定先阅读上文的实例代码,并且完整的运行一遍。
接下来,我们就来叙述具体的操作步骤:
1.首先,请读者先创建,或者复制上文的Departments.java,具体内容如下:
- @SuppressWarnings("serial")
- public class Departments implements Serializable{
-
- private String id;
- private String departmentName;
-
-
- }
2.创建,或者创建上文的DepartmentsDao.java,具体内容如下:
- public interface DepartmentsDao {
- Departments findDepartmentById(String id);
- }
3.复制,或者创建上文的DepartmentsDaoMapper.xml,具体内容如下:【注意,这里要将路径增加到mybatis-config.xml中的mapper配置中,前文我们有叙述过如果使用包名配置,请读者查阅《Mybatis最入门---配置详解(上)》,这里,博主不太喜欢xml与itf混合放置的方式,所以没有使用包路径配置的方式。但是,后续我们将Spring与Mybatis结合时,将会借用Spring的配置,使得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.csdn.ingo.dao.DepartmentsDao">
- <resultMap type="Departments" id="DepartmentsResult">
- <id property="id" column="id" />
- <result property="departmentName" column="departmentName" />
- </resultMap>
- <select id="findDepartmentById" parameterType="String" resultMap="DepartmentsResult">
- select * from departments where id=#{id}
- </select>
- </mapper>
4.修改UserInfoMapper.xml,具体内容如下:
- <resultMap type="userInfo" id="UserInfoResult">
- <id property="userid" column="userid" />
- <result property="department" column="department" />
- <result property="position" column="position" />
- <result property="mobile" column="mobile" />
- <result property="gender" column="gender" />
- <result property="email" column="email" />
- <association property="depart" column="department"
- select="com.csdn.ingo.dao.DepartmentsDao.findDepartmentById"></association>
- </resultMap>
5.重新单元测试方法,应该可以看到如下内容:
- UserInfo [userid=customer, department=2, position=售前, mobile=33334444, gender=1, email=customer@email.com, depart=Departments [id=2, departmentName=销售部]]
- UserInfo [userid=customer2, department=2, position=售后, mobile=55556666, gender=1, email=customer@email.com, depart=Departments [id=2, departmentName=销售部]]
-------------------------------------------------------------------------------------------------------------------------------------
至此,Mybatis最入门---动态查询(if)结束
特别备注:
本文演示的if用法按照官方文档中的内容,where之后,至少有一个给出的元素。假如,没有给出department的值,就算两外两个元素中有正确的值,Mybatis也不能够帮我们从数据库中查找出对应的条目,当然,返回数目为零,也不是以错误的形式