Mybatis 的动态 SQL 语句——if、where、choose 、when 、otherwise、foreach

Mybatis 中文开发文档 下载

一、if 、where

第一步：封装对数据库表的映射 User.java

package cn.lemon.domain;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "用户——{" +
                "编号=" + id +
                ", 姓名='" + username + '\'' +
                ", 生日=" + birthday +
                ", 性别='" + sex + '\'' +
                ", 地址='" + address + '\'' +
                '}';
    }
}

第二步：新建持久层（dao）接口 IUserDao.java ，操作数据库

package cn.lemon.dao;

import cn.lemon.domain.User;

import java.util.List;

public interface IUserDao {
    List<User> findByUser(User user);//按照指定的条件查询
}

第三步：新建配置文件 SqlMapConfig.xml 和 jdbc.properties

<?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">
<!--配置mybatis-->
<configuration>
    <!--配置属性，使用属性文件-->
    <properties resource="jdbc.properties"></properties>
    <!--定义别名-->
    <typeAliases>
        <!--定义单个别名-->
        <!--<typeAlias type="cn.lemon.domain.User" alias="user"></typeAlias>-->
        <!--配置包定义别名，别名=包中的类的名字（首字母大写小写都可以)-->
        <package name="cn.lemon.domain"></package>
    </typeAliases>
    <!--配置环境-->
    <environments default="mysql">
        <environment id="mysql">
            <!--配置事务的类型-->
            <transactionManager type="JDBC"></transactionManager>
            <!--配置连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>
            </dataSource>
        </environment>
    </environments>
    <!--配置映射文件-->
    <mappers>
        <!--配置单个映射文件-->
        <!--<mapper resource="com/lxs/dao/IUserDao.xml"></mapper>-->
        <package name="cn.lemon.dao"></package>
    </mappers>
</configuration>

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql:///db_mybatis?serverTimezone=GMT%2B8
jdbc.username=root
jdbc.password=lemon

第四步：编写持久层接口的映射文件 IUserDao.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="cn.lemon.dao.IUserDao">
    <select id="findByUser" resultType="user" parameterType="user">
        select * from user
        <where>
            <if test="username != null and username.trim() != ''">
                username like '%${username}%'
            </if>
            <if test="address != null and address.trim() != ''">
                and address like '%${address}%'
            </if>
        </where>
    </select>
</mapper>

第五步：拷贝日志文件 log4j.properties，这样我们就可以在控制台看到打印日志

# Set root category priority to INFO and its only appender to CONSOLE.
#log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
#log4j.rootCategory=debug, CONSOLE, LOGFILE
log4j.rootCategory=debug, CONSOLE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.
log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

# LOGFILE is set to be a File appender using a PatternLayout.
#log4j.appender.LOGFILE=org.apache.log4j.FileAppender
#log4j.appender.LOGFILE.File=d:\axis.log
#log4j.appender.LOGFILE.Append=true
#log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
#log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

第六步：测试类

package cn.lemon.dao;

import cn.lemon.domain.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.List;

public class IUserDaoTest {
    private InputStream inputStream;
    private SqlSessionFactory sqlSessionFactory;
    private SqlSession sqlSession;
    private IUserDao iUserDao;

    @Before//在执行其他方法之前执行该方法
    public void before() throws Exception {
        inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");//以流的方式读取配置文件
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//产生 mybatis 工厂类
        sqlSession = sqlSessionFactory.openSession(true);//表示打开 mybatis 自动提交事务，并得到Session，等同于 JDBC　的连接
        iUserDao = sqlSession.getMapper(IUserDao.class);//得到mybatis 的mapper 代理对象
    }

    @Test
    public void findByUser(){
        User user = new User();
        user.setUsername("李");
        user.setAddress("北京");
        List<User> userList = iUserDao.findByUser(user);
        for (User u : userList) {
            System.out.println(u);
        }
    }

    @After//在执行其他方法之后执行该方法
    public void after() throws Exception {
        //sqlSession.commit();//打开自动提交事务
        sqlSession.close();//关闭资源
        inputStream.close();//关闭资源
    }
}

查看

二、choose 、when 、otherwise

    <select id="findByUser" resultType="user" parameterType="user">
        select * from user where 1 = 1
        <choose>
            <when test="username != null and username.trim() != ''">and username like '%${username}%'</when>
            <when test="address != null and address.trim() != ''">and address like '%${address}%'</when>
            <otherwise></otherwise>
        </choose>
    </select>

效果和if、where 是一样的

三、foreach

SQL 语句：SELECT * FROM USER WHERE username LIKE '%李%' AND address LIKE '%北京%' AND id IN(45,46,48)

第一步：在实体类 User.java 中添加查询条件并生成get、set方法

    private List<Integer> ids = new ArrayList<>();//查询 id 条件

第二步：写 foreach

    <select id="findByUser" resultType="user" parameterType="user">
        select * from user
        <where>
            <if test="username != null and username.trim() != ''">
                username like '%${username}%'
            </if>
            <if test="address != null and address.trim() != ''">
                and address like '%${address}%'
            </if>
            <!--
            <foreach>标签用于遍历集合，它的属性：
                collection:代表要遍历的集合元素，注意编写时不要写#{}
                open:代表语句的开始部分
                close:代表结束部分
            -->
            <if test="ids != null and ids.size > 0">
                <foreach collection="ids" open="and id in(" close=")" separator="," item="uid">
                    ${uid}
                </foreach>
            </if>
        </where>
    </select>

第三步：修改测试类

    @Test
    public void findByUser(){
        User user = new User();
        user.setUsername("李");
        user.setAddress("北京");
        user.getIds().add(45);
        user.getIds().add(46);
        user.getIds().add(48);
        List<User> userList = iUserDao.findByUser(user);
        for (User u : userList) {
            System.out.println(u);
        }
    }

四、set 、if

当修改数据库中的某一条数据时，如果不想修改某列数据时

第一步：在 IUserDao.java 接口中，添加 update 方法

void update(User user);

第二步：

    <update id="update" parameterType="user">
        update user
        <set>
            <if test="username != null and username.trim() != ''">
                username = #{username},
            </if>
            <if test="birthday != null">
                birthday = #{birthday},
            </if>
            <if test="sex != null and sex.trim() != ''">
                sex = #{sex},
            </if>
            <if test="address != null and address.trim() != ''">
                address = #{address}
            </if>
        </set>
        where id = #{id};
    </update>

第三步：测试类

    @Test
    public void update() {
        User user = new User();
        user.setUsername("李总");
        //user.setBirthday(new Date());
        user.setSex("男");
        //user.setAddress("China");
        user.setId(62);
        iUserDao.update(user);
    }

五、引用 SQL 片段

<?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="cn.lemon.dao.IUserDao">
    <!--定义SQL片段,用于重复出现的片段-->
    <sql id="userSql">
        select * from user
    </sql>
    <select id="findByUser" resultType="user" parameterType="user">
        <include refid="userSql"></include><!--引用上面的Sql片段-->
        <where>
            <if test="username != null and username.trim() != ''">
                username like '%${username}%'
            </if>
            <if test="address != null and address.trim() != ''">
                and address like '%${address}%'
            </if>
            <!--
            <foreach>标签用于遍历集合，它的属性：
                collection:代表要遍历的集合元素，注意编写时不要写#{}
                open:代表语句的开始部分
                close:代表结束部分
                AND id IN(45,46,48)
            -->
            <if test="ids != null and ids.size > 0">
                <foreach collection="ids" open="and id in(" close=")" separator="," item="uid">
                    ${uid}
                </foreach>
            </if>
        </where>
    </select>

    <update id="update" parameterType="user">
        update user
        <set>
            <if test="username != null and username.trim() != ''">
                username = #{username},
            </if>
            <if test="birthday != null">
                birthday = #{birthday},
            </if>
            <if test="sex != null and sex.trim() != ''">
                sex = #{sex},
            </if>
            <if test="address != null and address.trim() != ''">
                address = #{address}
            </if>
        </set>
        where id = #{id};
    </update>
</mapper>

转载：https://blog.csdn.net/weixin_43860260/article/details/101206267