6.1 Mybatis实现动态SQL——if/if使用

本文主要是介绍6.1 Mybatis实现动态SQL——if/if使用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

当我们用模糊匹配某字段时，在mybatis可以通过动态sql来实现。
例如users表如下：

下面在mybatis中实现

SELECT * FROM users WHERE name LIKE '%z%' AND age>15 AND age<30

1、建立users表对应的User类

package com.lzj.mybaits.test1;public class User {private int id;private String name;private float age;public User() {super();}public User(int id, String name, float age) {super();this.id = id;this.name = name;this.age = age;}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 float getAge() {return age;}public void setAge(float age) {this.age = age;}@Overridepublic String toString() {return "User [id=" + id + ", name=" + name + ", age=" + age + "]";}}

2、在select查询语句中，有三个条件：name的取值，age的最小值，age的最大值，下面把查询条件封装成一个条件类

package com.lzj.mybaits.test1;public class UserCondition {private String name;private float minAge;private float maxAge;public UserCondition() {super();}public UserCondition(String name, float minAge, float maxAge) {super();this.name = name;this.minAge = minAge;this.maxAge = maxAge;}public String getName() {return name;}public void setName(String name) {this.name = name;}public float getMinAge() {return minAge;}public void setMinAge(float minAge) {this.minAge = minAge;}public float getMaxAge() {return maxAge;}public void setMaxAge(float maxAge) {this.maxAge = maxAge;}@Overridepublic String toString() {return "UserCondition [name=" + name + ", minAge=" + minAge + ", maxAge=" + maxAge + "]";}}

3、定义sql的userMapper.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.lzj.mybaits.test1.userMapper"><select id="getUsers" parameterType="UserCondition" resultType="User">select * from users where <!--该if条件中的第一个name和第三个name对应UserCondition类中的属性：第1个name相当于java中的代码，不需要加#{}，第3个name代表引用传入UserCondition对象中的name属性，需要加#{}；第2个name 为sql中的字段名--><if test='name != "%null%"'>name like #{name} and</if><!--当传入的UserCondition对象中的name属性值为"%null%"时，sql语句中就只按age的条件进行查询--><if test='name == "%null%"'></if>age between #{minAge} and #{maxAge}</select></mapper>

4、创建测试类

package com.lzj.mybaits.test1;
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;public class MybaitsTest {public static void main(String[] args) throws Exception {String resource = "conf.xml";InputStream in = MybaitsTest.class.getClassLoader().getResourceAsStream(resource);SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);/*自动提交事务*/SqlSession session = factory.openSession(true);String statement = "com.lzj.mybaits.test1.userMapper.getUsers";String name = "z";/*如果String name = null;* 在创建UserCondition对象后，对象中的name值为"%null%",所以在映射文件中要与"%null%"判断*/List<User> users = session.selectList(statement, new UserCondition("%" + name + "%", 15, 30));System.out.println(new UserCondition("%" + name + "%", 15, 30));System.out.println(users);session.close();}}

输出结果

UserCondition [name=%z%, minAge=15.0, maxAge=30.0]
[User [id=3, name=lzj, age=25.0], User [id=4, name=zhangsan, age=21.0], User [id=5, name=zhangsan, age=21.0]]

这篇关于6.1 Mybatis实现动态SQL——if/if使用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---