2.6 MyBatis中的复杂SQL

蟑螂恶霸不是恶霸

已于 2023-06-08 14:36:27 修改

阅读量559

点赞数

分类专栏：零基础入门到就业--JAVA篇文章标签： java

于 2023-04-11 16:33:36 首次发布

本文链接：https://blog.csdn.net/whatfuswd/article/details/129381451

版权

零基础入门到就业--JAVA篇专栏收录该内容

224 篇文章 5 订阅

订阅专栏

文章目录

1 动态Sql
2.关联查询
3.Mybatis-驼峰映射

1 动态Sql

1.1 动态 sql-where-if

1.1.1 业务需求

说明: 用户传递了一个user对象, 要求根据user中不为null的属性查询数据.
例子1:
User {name:“张三”}
Sql: select * from demo_user where name = “张三”

例子2:
User {name:“张三”, age: 18}
Sql: select * from demo_user where name = “张三” and age=18

1.1.2 编辑测试方法

 		/*
     * 动态Sql练习  根据对象中不为null的元素查询数据
     */
    @Test
    public void testSqlWhere(){
        User user = new User(null,"黑熊精",3000, "男");
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper2 userMapper2 = sqlSession.getMapper(UserMapper2.class);
        List<User> userList = userMapper2.findSqlWhere(user);
        System.out.println(userList);
        sqlSession.close();
    }

1.1.3 问题说明

User对象中的数据可能为null.但是如果sql不做处理,则将会把null当做参数.导致程序查询异常.
例子:
在这里插入图片描述

1.1.4 编辑mapper接口

List<User> findSqlWhere(User user);

1.1.5 编辑xml映射文件

<!-- 动态Sql语句
        核心思想: 自动判断是否为null,
                如果为null,该字段不参与sql
        动态Sql规则:
            1.  <if test="写判断条件,可以直接获取属性值"></if>
                    true: 会拼接 字段条件
                    false: 不会拼接字段条件
            2. 多余的关键字
                由于动态sql拼接必然会导致多余的and 或者 or
            3. where标签说明 可以去除 where后边多余的and 或者 or
    -->
    <select id="findSqlWhere" resultType="User">
        select * from demo_user
            <where>
                <if test="id != null"> id = #{id}</if>
                <if test="name != null">and name = #{name}</if>
                <if test="age != null ">and age  = #{age}</if>
                <if test="sex != null ">and sex  = #{sex}</if>
            </where>
    </select>

动态SQL最核心的思想:就是自动判断是否为null,如果为null,该字段不参与sql，其具体规则为：

<if test=“写判断条件,可以直接获取属性值”></if>
- true: 会拼接字段条件
- false: 不会拼接字段条件
多余的关键字
- 由于动态sql拼接必然会导致多余的and 或者 or
where标签说明可以去除 where后边多余的and 或者 or

1.2 动态 sql-set-if

1.2.1 编辑测试类

 /**
     * 需求: 实现用户数据修改, 根据对象中不为null的数据完成修改操作
     */
    @Test
    public void testSqlSet(){
        SqlSession sqlSession =
                sqlSessionFactory.openSession(true);
        UserMapper2 userMapper = sqlSession.getMapper(UserMapper2.class);
        User user = new User(1,"守山使者",3000, null);
        int rows = userMapper.updateSqlSet(user);
        System.out.println("影响"+rows+"行");
        sqlSession.close();
    }

1.2.2 编辑Mapper接口

 int updateSqlSet(User user);

1.2.3 编辑Mapper 映射文件

<!--
        set标签用法: 去除set条件中多余的,号
    -->
    <update id="updateSqlSet">
        update demo_user
            <set>
                <if test="name !=null"> name=#{name}, </if>
                <if test="age !=null">  age = #{age}, </if>
                <if test="sex !=null">  sex = #{sex} </if>
            </set>
            where id = #{id}
    </update>

1.3 动态 sql-分支结构语法

1.3.1 业务说明

需求: 根据属性查询数据, 如果name有值按照name查询,否则按照年龄查询,如果name,age都没有按照sex查询
需求分析:

	if(name !=null ){
		 name = #{name}
	}else if( age !=null){
		 age  = #{age}
	}else{
		 sex = #{sex}
	}

这里需要的注意的是，条件是互斥的，只能有一个条件生效，而不是满足的情况下，条件全部生效。

1.3.2 编辑测试方法

    /*
     * 需求: 根据属性查询数据, 如果name有值 按照name查询,否则按照年龄查询,如果name,age都没有 按照sex查询
     * 使用分支结构完成
     */
 @Test
    public void testChoose(){
        SqlSession sqlSession =
                sqlSessionFactory.openSession();
        UserMapper2 userMapper = sqlSession.getMapper(UserMapper2.class);
        User user = new User(null,null,null,"男");
        List<User> userList = userMapper.findChoose(user);
        System.out.println(userList);
        sqlSession.close();
    }

1.3.3 编辑Mapper接口

	List<User> findChoose(User user);

1.3.4 编辑Mapper映射文件

<!--
        如果只需要一个条件有效,则使用分支结构用法.
    -->
    <select id="findChoose" resultType="User">
        select * from demo_user
        <where>
            <choose>
                <when test="name !=null">
                    name = #{name}
                </when>
                <when test="age !=null">
                    age = #{age}
                </when>
                <otherwise>
                    sex = #{sex}
                </otherwise>
            </choose>
        </where>
    </select>

2.关联查询

2.1 创建新项目

2.1.1 选择项目

在这里插入图片描述

2.1.2 编辑POM.xml文件

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!--引入插件lombok 自动的set/get/构造方法插件  -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!--mybatis依赖包-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <!--jdbc依赖包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

    </dependencies>

2.1.3 复制配置文件

在这里插入图片描述

2.2 创建表

2.2.1 创建员工表

1.表名：emp
emp_id int 主键自增
emp_name varchar(40)
dept_id int
在这里插入图片描述
2.表数据

2.2.2 创建部门表

1.表名：dept
dept部门表
dept_id int 主键自增
dept_name varchar(40)
在这里插入图片描述
2. 表数据

2.2.3 创建POJO对象

2.2.3.1 编辑Emp POJO

对于Emp的数据模型类，因为多个员工对应一个部门，是多对一的关系，所以我们应该这样封装：

@Data
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Emp implements Serializable {
    private Integer empId;
    private String  empName;
    //关联关系 1: 一个员工对应一个部门
    private Dept dept;
}

2.2.3.2 编辑Dept POJO

对于Dept数据模型类，因为一个部门对应多个员工，所以是一对多的关系，所以我们应该这样封装：

@Data
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Dept implements Serializable {
    private Integer deptId;
    private String deptName;
    //一个部门对应多个员工
    private List<Emp> emps;
}

2.2.3.3 项目代码结构

在这里插入图片描述

一定要注意项目层级

2.2.3.4 配置文件修改

我们可以从springboot_demo2赋值配置文件，进行修改后使用：

DeptMapper.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">

<!--namespace是mybaits映射文件的唯一标识,与接口对应-->
<mapper namespace="com.study.mapper.DeptMapper">

</mapper>

EmpMapper.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">

<!--namespace是mybaits映射文件的唯一标识,与接口对应-->
<mapper namespace="com.study.mapper.EmpMapper">

</mapper>

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>
    <!--
        要注意MyBatis.config配置文件，对于配置信息的引入有顺序要求
        peoperties？，settings？，typeAliases？，typeHandlers？，objectFactory
    -->

    <typeAliases>
        <!--type:pojo对象路径  alias:pojo对象别名-->
        <!--<typeAlias type="com.study.pojo.User" alias="User"/>-->
        <package name="com.study.pojo"/>
    </typeAliases>

    <!--环境配置标签-->
    <!--MyBatis支持多个数据源的配置，default=“默认链接数据源”-->
    <environments default="development">

        <!--编辑开发环境-->
        <environment id="development">
            <!--MyBatis使用JDBC的方式控制数据库事物-->
            <transactionManager type="JDBC"/>
            <!--type="POOLED" 创建一个数据源吗链接池，每次从池中获取链接-->
            <dataSource type="POOLED">
                <!--版本信息：
                    如果数据源采用5.x的版本， value="com.mysql.jdbc.Driver"
                    如果数据源采用8.x的版本， value="com.mysql.cj.jdbc.Driver"
                -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <!--数据库链接地址及用户名，密码-->
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/jt?serverTimezone=GMT%2B8&amp;useUnicode=true&amp;characterEncoding=utf8&amp;autoReconnect=true&amp;allowMultiQueries=true"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!--Mybatis加载Mapper映射文件-->
    <mappers>
        <mapper resource="mybatis/mappers/DeptMapper.xml"/>
        <mapper resource="mybatis/mappers/EmpMapper.xml"/>
    </mappers>
</configuration>

2.3 常见表关系

秘诀: 从一头出发,看向另一头. 所在位置不同,得到的结果不同.

1. 一对一 例子: 一个老公对应一个老婆 , 一个员工对应一个部门
2. 一对多 例子: 一个部门对应多个员工.
3. 多对多 例子: 一个学生对应多个老师, 一个老师对应多个学生.
				一个角色对应多个权限, 一个权限对应多个角色
				多对多,其实就是双向的一对多
				多对一 实质: 站在一头出发 所以 就是一对一

2.4 Mybatis 一对一封装

2.4.1 业务说明

表关系: 一个员工对应一个部门,要注意的是，Mybatis中映射规则中，结果集不允许出现重名字段。因为Mybatis中的一个字段自动对应查询数据结果中的一个字段，当有多个重名字段时，将无法正确映射。
Sql:

/*内连接的另外的一种表现形式.*/
SELECT e.emp_id,e.emp_name,
       d.dept_id,d.dept_name 
	FROM emp e,dept d 
	WHERE e.dept_id = d.dept_id

2.4.2 测试类准备

package com.jt;

import com.jt.mapper.EmpMapper;
import com.jt.pojo.Emp;
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.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

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

class SpringbootDemo3ApplicationTests {

    /**
     * SqlSessionFactory
     */
    private SqlSessionFactory sqlSessionFactory;

    @BeforeEach
    public void init() throws IOException {
        String resource = "mybatis/mybatis-config.xml";
        InputStream inputStream =
                Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(inputStream);
    }

    /**
     * 进行一对一查询  一个员工对应一个部门
     */
    @Test
    public void testFind2(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper userMapper = sqlSession.getMapper(EmpMapper.class);
        List<Emp> userList = userMapper.findAll();
        System.out.println(userList);
        sqlSession.close();
    }

}

2.4.3 编辑Mapper接口/xml映射文件

编辑Mapper接口

public interface EmpMapper {

    List<Emp> findAll();
}

编辑Mapper映射文件

<?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.jt.mapper.EmpMapper">

    <!-- 一对一关联查询 -->
    <select id="findAll" resultMap="empRM">
        select e.emp_id,e.emp_name,
               d.dept_id,d.dept_name
	    from emp e,dept d
	    where e.dept_id = d.dept_id
    </select>

    <!--3.完成一对一封装
        固定用法:
            1.association: 将结果集封装为单独的对象 dept
            2.property 需要封装的属性名称
            3.javaType 固定写法: 属性的类型
    -->
    <resultMap id="empRM" type="Emp">
        <!--1.主键字段 -->
        <id property="empId" column="emp_id"></id>
        <!--2.映射其它属性字段-->
        <result property="empName" column="emp_name"></result>
        <association property="dept" javaType="Dept">
            <!--3.完成dept对象的封装-->
            <id property="deptId" column="dept_id"/>
            <result property="deptName" column="dept_name"/>
        </association>
    </resultMap>

</mapper>

2.4.4 一对一标签

    <resultMap id="empRM" type="Emp">
        <!--1.主键字段-->
        <id property="empId" column="emp_id"></id>
        <!--2.映射其他属性-->
        <result property="empName" column="emp_name"></result>
        <!--3.1完成1对1的封装-->
        <association property="dept" javaType="Dept">
            <!--3.2 完成dept对象的封装-->
            <id property="deptId" column="dept_id"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>

2.5 一对多

2.5.1 业务场景

说明: 一个部门有多个员工.

@Data
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Dept implements Serializable {

    private Integer deptId;
    private String deptName;

    //一个部门下有多个员工；
    private List<Emp> emps;

}

Sql:

/*内连接的另外的一种表现形式.*/
SELECT d.dept_id,d.dept_name,e.emp_id,e.emp_name
	FROM dept d,emp e
	WHERE e.dept_id = d.dept_id

2.5.2 编辑测试方法

//一个部门下有多个员工   主表: dept
    @Test
    public void testOneToMore(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
        List<Dept> list = deptMapper.findAll();
        System.out.println(list);
        sqlSession.close();
    }

2.5.3 编辑Mapper接口/映射文件

mapper接口

public interface DeptMapper {
    List<Dept> findAll();
}

编辑Mapper映射文件

<?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.jt.mapper.DeptMapper">
    
    <select id="findAll" resultMap="deptRM">
        select d.dept_id,d.dept_name,e.emp_id,e.emp_name
	from dept d,emp e
	where e.dept_id = d.dept_id
    </select>

    <!--Mybatis的映射,一般都是一级封装 -->
    <resultMap id="deptRM" type="Dept">
        <!--指定主键-->
        <id column="dept_id" property="deptId"/>
        <!--封装其它的属性字段-->
        <result column="dept_name" property="deptName"/>
        <!--封装集合 属于同一个部门下的员工,封装到一个集合中 -->
        <collection property="emps" ofType="Emp">
            <id column="emp_id" property="empId"/>
            <result column="emp_name" property="empName"/>
        </collection>
    </resultMap>
</mapper>

2.6 Mybatis的子查询

2.6.1 子查询原理

矛盾点:

如果想简化Sql,则映射文件肯定复杂.
如果想简化映射文件, 则Sql语句复杂.

案例说明: 需求简化Sql, 那么映射文件复杂.

2.6.2 编辑测试案例

 /**
     * 利用Mybatis的子查询实现数据封装
     */
    @Test
    public void testOneToMore2(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
        List<Dept> list = deptMapper.selectChildren();
        System.out.println(list);
        sqlSession.close();
    }

2.6.3 编辑Mapper接口/xml映射文件

编辑Mapper接口

package com.jt.mapper;

import com.jt.pojo.Dept;
import com.jt.pojo.Emp;

import java.util.List;

public interface DeptMapper {
    //进行一对多的查询  一个部门对应多个员工
    List<Dept> findAll();

    利用mybatis的子查询实现数据封装
    List<Emp> selectChildren();
}

编辑xml映射文件

   <!--
        子查询的本质特点：将多表关联查询，转化为多个单表查询
    -->
    <select id="selectChildren" resultMap="cRM">
        select * from dept
    </select>
    
 <!--子查询:
        1.标签: select  进行二次查询
        2.关联字段信息:  column="dept_id" 将字段的值作为参数 传递给子查询
    -->
    <resultMap id="cRM" type="Dept">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
        <!--数据集合封装-->
        <collection property="emps" ofType="Emp" select="findEmp" column="dept_id"></collection>
    </resultMap>

    <select id="findEmp" resultMap="empRM">
        select * from emp where dept_id = #{dept_id}
    </select>

    <resultMap id="empRM" type="Emp">
        <id column="emp_id" property="empId"/>
        <result column="emp_name" property="empName"/>
    </resultMap>

2.7 小结

常见对应方式 1-1 1-多多对多
对一封装关键标签 <result property="deptName" column="dept_name"/>
对多封装关键标签 <collection property="emps" ofType="Emp">
什么时候使用resultMap
4.1 字段名称与属性名称不一致.
4.2 做关联查询实现数据封装时使用.
子查询用法:
5.1 select 开始二次查询
5.2 column 将字段的值作为参数传递给子查询子查询通过#{字段名称}动态接收.

2.8 练习

使用子查询的方式,封装 1-1对应关系.

编辑测试案例

/*
     *利用mybatis的子查询实现数据封装2
     */
    @Test
    void testOneToMore3() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
        List<Dept> list = empMapper.selecChildren2();
        System.out.println(list);
        sqlSession.close();

编辑Mapper接口

List<Dept> selecChildren2();

编辑xml映射文件

    <!--
    子查询练习
		-->
    <select id="selecChildren2" resultMap="dRM">
        select * from emp
    </select>

    <resultMap id="dRM" type="Emp">
        <id column="emp_id" property="empId"/>
        <result column="emp_name" property="empName"/>
        <collection property="dept" ofType="Dept" select="findDept" column="dept_id"></collection>
    </resultMap>

    <select id="findDept" resultMap="deptRM">
        select * from dept where dept_id = #{dept_id}
    </select>

    <resultMap id="deptRM" type="Dept">
        <id column="dept_id" property="deptId"/>
        <result column="dept_name" property="deptName"/>
    </resultMap>

3.Mybatis-驼峰映射

3.1 案例演示

问题说明: 工作中使用驼峰规则的定义的场景比较多的.但是如果每个属性都需要自己手动的封装,则比较繁琐. 所以框架应该提供自动映射的功能。

    <!--通过驼峰映射规则查询所有员工信息-->
    <select id="getAll" resultMap="getEmpMap">
        select * from emp
    </select>
    <resultMap id="getEmpMap" type="Emp">
        <id column="emp_id" property="empId"></id>
        <result column="emp_name" property="empName"></result>
    </resultMap>

3.2 驼峰规则说明

1. 官网描述:
在这里插入图片描述
2. 设置settings
在mybatis-config.xml文件中配置settings信息

  <!--配置settings信息-->
    <settings>
        <!--开启驼峰映射规则-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <typeAliases>
        <!--type:  POJO对象的路径  alias: 为当前的POJO对象起的别名-->
        <!--<typeAlias type="com.jt.pojo.User" alias="User"/>-->

        <!--设置别名包 -->
        <package name="com.study.pojo"/>
    </typeAliases>

3. 驼峰规则映射说明

 <!--查询用户信息  开启驼峰映射规则
        resultType:
                1.适用与单表查询,同时要求属性名称与字段相同.
                2.如果属性与字段满足驼峰命名规则,开启驼峰映射之后,
                  可以使用resultType
        resultMap:
                1.如果字段不一致时使用
                2.多表关联查询时使用.
                3.如果开启了驼峰映射规则, 则自动映射的属性可以省略,最好标识主键
                4.如果使用驼峰规则映射时,需要映射封装对象时(一对一/一对多),默认条件下.驼峰规则失效.
                  可以使用: autoMapping="true" 要求开启驼峰映射.
                5.默认条件下 一对一,一对多不会自动完成驼峰规则映射.
                  需要配置 autoMapping="true"才能自动映射
     -->
    <select id="getAll" resultMap="getEmpMap">
        select e.emp_id,e.emp_name,
               d.dept_id,d.dept_name
	    from emp e,dept d
	    where e.dept_id = d.dept_id
    </select>
    <resultMap id="getEmpMap" type="Emp" autoMapping="true">
        <id column="emp_id" property="empId"/>
        <!--其它属性自动映射-->
        <!--实现部门映射-->
        <association property="dept" javaType="Dept" autoMapping="true">
            <id column="dept_id" property="deptId"/>
        </association>
    </resultMap>

3.3 练习

使用驼峰映射实现部门信息查询

1.编辑测试方法：

    /**
     *使用驼峰映射实现部门信息查询
     */
    @Test
    void testfindDept() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
        List<Dept> list = deptMapper.getAll();
        System.out.println(list);
        sqlSession.close();
    }

2.编辑接口：

    List<Dept> getAll();

3.编辑映射文件：

    <select id="getAll" resultMap="getDeptMap">
        select d.dept_id,d.dept_name,e.emp_id,e.emp_name from emp e,dept d where e.dept_id = d.dept_id
    </select>
    <resultMap id="getDeptMap" type="Dept" autoMapping="true">
        <id column="dept_id" property="deptId"/>
        <collection property="emps" ofType="Emp" autoMapping="true">
            <id column="emp_id" property="empId"/>
        </collection>
    </resultMap>

蟑螂恶霸不是恶霸

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
2.6 MyBatis中的复杂SQL

说明: 用户传递了一个user对象, 要求根据user中不为null的属性查询数据.例子1:User {name:“张三”}Sql: select * from demo_user where name = “张三”例子2:User {name:“张三”, age: 18}Sql: select * from demo_user where name = “张三” and age=181.1.3 问题说明User对象中的数据可能为null.但是如果sql不做处理,则将会把null当做参数.导致
复制链接

扫一扫