学习笔记——Mybatis动态SQL

2023-01-12

一、Mybatis动态SQL

即将SQL动态化

同时Mybatis的动态SQL支持OFNL表达式，OGNL（Object Graph Navigation Language）对象图导航语言。

1、先搭建环境

（1）创建一个“maven”模块，命名为“day04_mybatis”

（2）在“day04_mybatis”中的“pom.xml”中的<project>标签内部添加依赖，即添加jar包

<dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

（3）在"day04_mybatis.src.main.resources"中添加"db.properties（连接数据库，里面的形式是：key=value）,需要设置4个值（driver、url、username、password）"和“log4j.xml”

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <param name="Encoding" value="UTF-8" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug"/>
    </logger>
    <logger name="org.apache.ibatis">
        <level value="info"/>
    </logger>
    <root>
        <level value="debug" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

（4）在“day04_mybatis.src.resources”中添加“mybatis-config.xml”

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--    设置属性-->
    <properties resource="db.properties"></properties>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--        延迟加载-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--        延迟加载的属性-->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <typeAliases>
        <package name="com.hh.mybatis.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${db.driver}"/>
                <property name="url" value="${db.url}"/>
                <property name="username" value="${db.username}"/>
                <property name="password" value="${db.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/EmployeeMapper.xml"></mapper>
    </mappers>
</configuration>

（5）在“day04_mybatis.src.main.java”中创建“com.hh.mybatis.pojo”和“com.hh.mybatis.mapper”。之后在“day04_mybatis.src.main.resources”中创建“mapper”文件夹，在“mapper”文件夹下创建“EmployeeMapper.xml”

（6）在“pojo”文件夹下创建“Dept”、“Employee”。在“mapper”文件夹下创建“EmployeeMapper”接口并添加代码

public interface EmployeeMapper {
    /**
     * 按条件查询员工信息（条件不确定）
     * @return
     */
    public List<Employee> selectEmpByOpr(Employee employee);
}

（7）EmployeeMapper.xml中的映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hh.mybatis.mapper.EmployeeMapper" >
<!-- 按条件查询员工信息（条件不确定）-->
    <select id="selectEmpByOpr" resultType="employee">
        select
            id,
            last_name,
            email,
            salary,
            dept_id,
        from
            tbl_employee
    </select>
</mapper>

（8）在“day04_mybatis.src.test.java"中创建测试类“TestDynamicSql”

public class TestDynamicSql {
    @Test
    public void testDynamicSql() throws Exception{
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        Employee employee = new Employee();
        List<Employee> employees = employeeMapper.selectEmpByOpr(employee);
        for (Employee employee1 : employees) {
            System.out.println("employee1 = " + employee1);
        }
    }
}

（9）注意：

如果出现“java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax； check the manual that corres”这种问题报错时，可以将sql语句先在“SQLyog”中先写一遍，运行，如果运行无误，之后将代码再放入“映射文件”中，即可

2、进行动态SQL设置

（1）注意：在动态SQL中“test”中放置的是“属性”

（2）动态SQL<if>标签

用于完成简单的判断

<select id="selectEmpByOpr" resultType="employee">
        SELECT
            id,
            last_name,
            email,
            salary
        FROM
            tbl_employee
        WHERE
            <if test="id != null">
                id = #{id}
            </if>
            <if test="lastName != null">
                and last_name = #{lastName}
            </if>
            <if test="email != null">
                and email = #{email}
            </if>
            <if test="salary != null">
                and salary = #{salary}
            </if>
    </select>

测试类

public class TestDynamicSql {
    @Test
    public void testDynamicSql() throws Exception{
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        Employee employee = new Employee();
        //动态标签
        employee.setId(1);
//        employee.setSalary(50.0);
        List<Employee> employees = employeeMapper.selectEmpByOpr(employee);
        for (Employee employee1 : employees) {
            System.out.println("employee1 = " + employee1);
        }
    }
}

注意：动态参数中<if>，无参数时报错，没有第一个参数也报错。

(3)动态SQL：<where>标签

where用于解决SQL语句中where关键字以及条件前面的and或者or的问题

<select id="selectEmpByOpr" resultType="employee">
        SELECT
            id,
            last_name,
            email,
            salary
        FROM
            tbl_employee
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="lastName != null">
                and last_name = #{lastName}
            </if>
            <if test="email != null">
                and email = #{email}
            </if>
            <if test="salary != null">
                and salary = #{salary}
            </if>
        </where>
    </select>

（3）动态SQL：<trim>

可以在条件判断完的SQL语句前后添加或者去掉指定的字符

标签中的属性：

①prefix:添加前缀

②prefixOverrides:去掉前缀

③suffix:添加后缀

④suffixOverrides:去掉后缀

例如：

    <select id="selectEmpByOprTrim" resultType="employee">
         SELECT
            id,
            last_name,
            email,
            salary
        FROM
            tbl_employee
        <trim prefix="where" suffixOverrides="and">
            <if test="id != null">
                 id = #{id} and
            </if>
            <if test="lastName != null">
                last_name = #{lastName} and
            </if>
            <if test="email != null">
                email = #{email} and
            </if>
            <if test="salary != null">
                salary = #{salary}
            </if>
        </trim>
    </select>

（4）动态SQL：<set>

用于解决修改操作中SQL语句中可能多出逗号的问题

在“EmployeeMapper”接口中添加方法

/**
     * 按条件修改员工信息（条件不确定）
     * @param employee
     */
    public void updateEmpByOpr(Employee employee);

映射文件

<update id="updateEmpByOpr">
        update
            tbl_employee
        <set>
            <if test="lastName != null">
                last_name = #{lastName},
            </if>
            <if test="email != null">
                email = #{email},
            </if>
            <if test="salary != null">
                salary = #{salary}
            </if>
        </set>
        where
           id = #{id}
</update>

（5）动态SQL标签：<choose>

类似于java中if-else（switch-case）结构

    <update id="updateEmpByOneOpr">
        SELECT
            id,
            last_name,
            email,
            salary
        FROM
            tbl_employee
        <where>
             <choose>
                 <when test="id != null">
                     id = #{id}
                 </when>
                 <when test="email != null">
                     email = #{email}
                 </when>
                 <when test="salary != null">
                     salary = #{salary}
                 </when>
                 <otherwise>
                     1=1
                 </otherwise>
             </choose>
        </where>
    </update>

（6）动态SQL标签：<foreach>

类似于java中的for循环

collection:要迭代的集合

item:当前从集合中迭代出的元素

separator:元素与元素之间的分隔符

①“EmployeeMapper”接口中的函数

/**
     * 通过多个id获取员工信息
     * @param ids
     * @return
     */
    public List<Employee> selectEmpByIds(@Param("ids") List<Integer> ids);

②映射文件

<select id="selectEmpByIds" resultType="employee">
        SELECT
            id,
            last_name,
            email,
            salary
        FROM
            tbl_employee
        <where>
            id in
            (
            <foreach collection="ids" item="id" separator=",">
                #{id}
            </foreach>
            )
        </where>
    </select>

③测试主要代码

List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(3);
        ids.add(4);
        List<Employee> employees = employeeMapper.selectEmpByIds(ids);
        for (Employee employee1 : employees) {
            System.out.println("employee1 = " + employee1);
        }

（7）sql标签

提取可重用SQL片段

    <sql id="select_employee">
        SELECT
            id,
            last_name,
            email,
            salary
        FROM
            tbl_employee
    </sql>
    <sql id="emp_col">
        id,
        last_name,
        email,
        salary
    </sql>
<!-- 按条件查询员工信息（条件不确定）-->
    <select id="selectEmpByOpr" resultType="employee">
        select
            <include refid="emp_col"></include>
        from
            tbl_employee
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="lastName != null">
                and last_name = #{lastName}
            </if>
            <if test="email != null">
                and email = #{email}
            </if>
            <if test="salary != null">
                and salary = #{salary}
            </if>
        </where>
    </select>
标签 , ,