Mybatis进阶（动态SQL）

文章目录

- 1.动态SQL
- - - 1.基本介绍
    - - 1.为什么需要动态SQL
      - 2.基本说明
      - 3.动态SQL常用标签
    - 2.环境搭建
    - - 1.新建子模块
      - 2.删除不必要的两个文件夹
      - 3.创建基本结构
      - 4.父模块的pom.xml
      - 5.jdbc.properties
      - 6.mybatis-config.xml
      - 7.MyBatisUtils.java
      - 8.MonsterMapper.java
      - 9.MonsterMapper.xml
      - 10.测试MonsterMapperTest.java
    - 3.动态SQL-if标签
    - - 1.需求分析
      - 2.MonsterMapper.java添加@Param注解
      - 3.MonsterMapper.xml
      - 4.测试
    - 4.动态SQL-where标签
    - - 1.需求分析
      - 2.MonsterMapper.java添加方法
      - 3.MonsterMapper.xml添加实现类
      - 4.测试
    - 5.动态SQL-choose标签
    - - 1.需求分析
      - 2.MonsterMapper.java添加方法
      - 3.MonsterMapper.xml添加实现类
      - 4.测试
    - 6.动态SQL-foreach标签
    - - 1.需求分析
      - 2.MonsterMapper.java添加方法
      - 3.MonsterMapper.xml添加实现类
      - 4.测试
    - 7.动态SQL-set标签（重点）
    - - 1.需求分析
      - 2. MonsterMapper.java添加方法
      - 3.MonsterMapper.xml
      - 4.测试

1.动态SQL

1.基本介绍

1.为什么需要动态SQL

2.基本说明

3.动态SQL常用标签

2.环境搭建

1.新建子模块

2.删除不必要的两个文件夹

3.创建基本结构

4.父模块的pom.xml

<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/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.example</groupId>
  <artifactId>mybatis</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>pom</packaging>
  <name>Archetype - mybatis</name>
  <url>http://maven.apache.org</url>
  <modules>
    <module>mybatis_quickstart</module>
    <module>xml-mapper</module>
    <module>dynamic-sql</module>
  </modules>
  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.49</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.7</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <!--如果设置scope为test,则只能在test包下使用-->
      <scope>test</scope>
    </dependency>
  </dependencies>
  <!--在父模块中的pom.xml文件中配置在build的时候要扫描的文件，解决某些类型文件拷贝到target目录失败的问题-->
  <build>
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <!--在java文件夹下的多级目录下的xml文件-->
          <include>**/*.xml</include>
        </includes>
      </resource>
      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <!--在resources文件夹下的多级目录下的xml文件和properties文件-->
          <include>**/*.xml</include>
          <include>**/*.properties</include>
        </includes>
      </resource>
    </resources>
  </build>
</project>

5.jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8
jdbc.username=root
jdbc.password=root

6.mybatis-config.xml

配置com.sun.entity的类型别名
引入com.sun.mapper里的Mapper.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>
    <!--引入外部文件jdbc.properties-->
    <properties resource="jdbc.properties"/>

    <!--配置mybatis自带的日志，settings需要放到最前面-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--配置类型别名-->
    <typeAliases>
        <!--方式二：这样配置完了在这个包下的所有类都可以直接使用类名表示-->
        <package name="com.sun.entity"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!--配置事务管理器-->
            <transactionManager type="JDBC"/>
            <!--配置数据源-->
            <dataSource type="POOLED">
                <!--配置驱动-->
                <property name="driver" value="${jdbc.driver}"/>
                <!--配置连接url-->
                <!--
                1.jdbc:mysql：协议
                2.127.0.0.1:3306：指定连接mysql的ip+端口
                3.mybatis：连接的db
                4.useSSL：使用安全连接
                5.&amp;：表示&
                6.useUnicode=true：使用unicode,防止编码错误
                7.characterEncoding=UTF-8：字符集使用utf-8
                -->
                <property name="url" value="${jdbc.url}"/>
                <!--用户名-->
                <property name="username" value="${jdbc.username}"/>
                <!--密码-->
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--包的方式引入含有Mapper.xml文件的类-->
        <package name="com.sun.mapper"/>
    </mappers>

</configuration>

7.MyBatisUtils.java

package com.util;

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 java.io.IOException;
import java.io.InputStream;

/**
 * @author 孙显圣
 * @version 1.0
 */
public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    //使用静态代码块初始化SqlSessionFactory
    static {
        try {
            //获取Mybatis配置文件的输入流
            String resource = "mybatis-config.xml";
            //默认是从类路径下获取资源，在maven中指的就是resources文件夹下，会映射到实际的工作目录
            InputStream resourceAsStream = Resources.getResourceAsStream(resource);
            //读取资源获取SessionFactory，可以理解为连接池
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    //编写方法返回SqlSession
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

8.MonsterMapper.java

package com.sun.mapper;



/**
 * 这个接口用于声明操作monster表的方法
 *
 * @author 孙显圣
 * @version 1.0
 */
public interface MonsterMapper {

}

9.MonsterMapper.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">
<!--指定该xml文件和哪个接口对应-->
<mapper namespace="com.sun.mapper.MonsterMapper">

</mapper>

10.测试MonsterMapperTest.java

package com.sun.mapper;

import com.sun.entity.Monster;
import com.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Before;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 孙显圣
 * @version 1.0
 */
public class MonsterMapperTest {
    //两个属性
    private SqlSession sqlSession; //相当于连接
    private MonsterMapper monsterMapper;

    //编写方法完成初始化
    @Before //标注了Before之后表示了在执行目标测试方法前会执行该方法
    public void init() {
        //获取到sqlSession
        sqlSession = MyBatisUtils.getSqlSession();
        //获取到MonsterMapper对象
        monsterMapper = sqlSession.getMapper(MonsterMapper.class);
        System.out.println(monsterMapper.getClass());
    }
    @Test

    public void test() {
        System.out.println("ttt");
    }

}

3.动态SQL-if标签

1.需求分析

2.MonsterMapper.java添加@Param注解

package com.sun.mapper;


import com.sun.entity.Monster;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 这个接口用于声明操作monster表的方法
 *
 * @author 孙显圣
 * @version 1.0
 */
public interface MonsterMapper {
    //使用@Param("age")注解来使得动态sql的test域可以取出这个age
    public List<Monster> findMonsterByAge(@Param("age") Integer age);
}

3.MonsterMapper.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">
<!--指定该xml文件和哪个接口对应-->
<mapper namespace="com.sun.mapper.MonsterMapper">
    <!--public List<Monster> findMonsterByAge(Integer age);
        要求：当用户输入的值不大于0则输出所有妖怪
    -->
    <select id="findMonsterByAge" resultType="Monster" parameterType="Integer">
        select * from monster where 1 = 1
        <!--这里的age是在参数中使用@Param注解取出的-->
        <if test="age >= 0">
            and age > #{age}
        </if>
    </select>
</mapper>

4.测试

    @Test
    public void findMonsterByAge() {
        List<Monster> list = monsterMapper.findMonsterByAge(10);
        for (Monster monster : list) {
            System.out.println(monster);
        }
        //关闭sqlsession
        if (sqlSession != null) {
            sqlSession.close();
        }
    }

4.动态SQL-where标签

1.需求分析

2.MonsterMapper.java添加方法

    public List<Monster> findMonsterByIdAndName(Monster monster);

3.MonsterMapper.xml添加实现类

    <!--public List<Monster> findMonsterByIdAndName(Monster monster);
        1.id小于0则不拼接，名字为空也不拼接
        2.where标签会自动清除没用的and
		3.如果传入的是对象，就不用使用@Param
    -->
    <select id="findMonsterByIdAndName" resultType="Monster" parameterType="Monster">
        SELECT * FROM monster
        <where>
            <if test="id >= 0">
                and id > #{id}
            </if>
            <if test="name != null and name != ''">
                and `name` = #{name}
            </if>
        </where>
    </select>

4.测试

    @Test
    public void findMonsterByIdAndName() {
        Monster monster = new Monster();
        monster.setId(4);
        monster.setName("牛魔王");
        List<Monster> monsters = monsterMapper.findMonsterByIdAndName(monster);
        for (Monster monster1 : monsters) {
            System.out.println(monster1);
        }
        //关闭sqlsession
        if (sqlSession != null) {
            sqlSession.close();
        }
    }

5.动态SQL-choose标签

1.需求分析

当name不为空和id>0的时候有不同的操作

2.MonsterMapper.java添加方法

    public List<Monster> findMonsterByIdOrName_choose(Map<String, Object> map);

3.MonsterMapper.xml添加实现类

<!--    public List<Monster> findMonsterByIdOrName_choose(Map<String, Object> map);-->
    <select id="findMonsterByIdOrName_choose" parameterType="map" resultType="Monster">
        select * from `monster`
        <choose>
            <when test="name != null and name != ''">
                where `name` = #{name}
            </when>
            <when test="id > 0">
                where `id` = #{id}
            </when>
            <otherwise>
                where `salary` > 100
            </otherwise>
        </choose>
    </select>

4.测试

    @Test
    public void findMonsterByIdOrName_choose() {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id", 2);
        map.put("name", "");
        List<Monster> list = monsterMapper.findMonsterByIdOrName_choose(map);
        for (Monster monster : list) {
            System.out.println(monster);
        }
    }

6.动态SQL-foreach标签

1.需求分析

查询id为1，2，3的妖怪

2.MonsterMapper.java添加方法

public List<Monster> findMonsterById_forEach(Map<String, Object> map);

3.MonsterMapper.xml添加实现类

    <select id="findMonsterById_forEach" parameterType="map" resultType="Monster">
        select * from `monster`
        <if test="ids != null and ids != ''">
            <where>
                id in
                <!--
                    map中传入一个ids集合为[1,2,3]
                    目标：id in (1,2,3)
                    collection：要遍历的集合
                    item：遍历的数据项
                    open：开始标志
                    close：结束标志
                    separator：数据间隔
                -->
                <foreach collection="ids" item="id" open="(" separator="," close=")">
                    <!--  这里取出的是item里的id-->
                    #{id}
                </foreach>
            </where>
        </if>
    </select>

4.测试

    @Test
    public void findMonsterById_forEach() {
        Map<String, Object> map = new HashMap<String, Object>();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        //添加一个集合key为ids
        List<Monster> list = monsterMapper.findMonsterById_forEach(map);
        for (Monster monster : list) {
            System.out.println(monster);
        }
    }

7.动态SQL-set标签（重点）

1.需求分析

2. MonsterMapper.java添加方法

    public void updateMonster_set(Map<String, Object> map);

3.MonsterMapper.xml

    <!--根据id来对表进行修改，如果没有传值则不修改那个字段-->
    <!--public void updateMonster_set(Map<String, Object> map);-->
    <!--UPDATE monster , birthday = '2020-1-2', email = 'libai@qq.com', gender = 1, name = '李白', salary = 12.2 WHERE id = 3-->
    <update id="updateMonster_set" parameterType="map">
        UPDATE monster
        <set>
            <if test="age != null and age != ''">
                 age = #{age},
            </if>
            <if test="birthday != null and birthday != ''">
                 birthday = #{birthday},
            </if>
            <if test="gender != null and gender != ''">
                 gender = #{gender},
            </if>
            <if test="name != null and name != ''">
                 name = #{name},
            </if>
            <if test="salary != null and salary != ''">
                 salary = #{salary},
            </if>
            <if test="salary != null and salary != ''">
                 salary = #{salary},
            </if>
        </set>
        where id = #{id}
    </update>

4.测试

    @Test
    public void updateMonster_set() {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id", 3);
        map.put("age", 22);
        map.put("gender", 1);
        monsterMapper.updateMonster_set(map);
    }