Mybatis的CURD及动态SQL

站长

2024年03月27日 19:46 · 阅读数 4

一、Mybatis的CURD

1. 准备工作

准备数据库表 emp
创建一个新的springboot工程，选择引入对应的起步依赖(mybatis、mysql驱动、lombok)
application.properties中引入数据库连接信息
创建对应的实体类Emp（实体类属性采用驼峰命名）
准备Mapper接口 EmpMapper

2.基本CURD操作

操作步骤：

在Mapper接口里编写方法

方法的参数：根据SQL语句需要的参数来定

方法的返回值：根据我们想要得到什么结果来定

增删改的方法返回值：可以是void、boolean、int、long
在方法上加注解，配置SQL语句

配置SQL语句：
- select语句：@Select
- insert语句：@Insert + @Options
- update语句：@Update
- delete语句：@Delete

2.1查询-根据id查询一条

先准备好SQL语句

方法的参数：根据SQL语句需要的参数来定

这些参数都是给SQL语句使用。SQL语句里需要几个参数，方法上就要加几个形参

SQL语句里需要获取参数值，如果方法只有一个参数，写法是：#{随意写}，建议写成**#{参数名}**

方法的返回值：根据我们想要得到什么结果来定

我们期望Mybatis帮我们把查询结果封装成什么对象。

写成Emp，Mybatis就会把查询的结果封装成一个Emp对象

**注意：**实体类里的属性名，要和表的字段名一致（或者符合下划线与驼峰命名的规则）

EmpMapper:实现功能

@Mapper
public interface EmpMapper {
    //根据id查询一条数据
    @Select("select * from emp where id = #{id}")
    Emp queryById(Integer id);   
}

功能测试

 @Test
    public void testQueryById(){
        Emp emp = empMapper.queryById(1);
        System.out.println("emp = " + emp);
    }

2.2 查询-查询数量

EmpMapper:实现功能

@Mapper
public interface EmpMapper {
    //查询数量
    @Select("select count(*) from emp")
    int queryCount(); 
}

功能测试

@Test
public void testQueryCount(){
    int count = empMapper.queryCount();
    System.out.println("count = " + count);
}

2.3 删除

EmpMapper：实现功能

@Mapper
public interface EmpMapper {
    //根据id删除一个员工
    @Delete("delete from emp where id = #{id}")
	void deleteById(Integer id);
}

功能测试

@Test
public void testDeleteById(){
    empMapper.deleteById(17);
}

2.4 新增

EmpMapper：实现功能

方法的参数：

如果SQL语句需要的参数过多，方法的形参可以使用一个实体类

SQL语句里使用 #{JavaBean的属性名}

注意：

不要写成 '#{JavaBean属性名}'

#{属性名}的顺序，必须与前边的字段顺序是一致对应的

@Mapper
public interface EmpMapper {
    //插入一体员工数据
   @Insert("insert into emp (username, password, name, gender, image, job)\n" +
            "values (#{username}, #{password}, #{name}, #{gender}, #{image}, #{job})")
   void insert(Emp emp);
}

功能测试

@Test
public void testInsert(){
     Emp emp = new Emp();
     emp.setUsername("tom");
     emp.setPassword("123");
     emp.setName("汤姆");
     emp.setGender(1);
     emp.setImage("1.jpg");
     emp.setJob(2);
     empMapper.insert(emp);
}

获取主键值

如果执行insert时，需要获取数据的主键值，我们可以做：

在Mapper接口里插入的方法上，再增加注解：@Options(useGeneratedKeys=true, keyProperty="JavaBean里的属性名")

如果插入数据之后，需要获取数据的主键值：@Options useGeneratedKeys：利用数据库的主键自增得到主键值 keyProperty：把得到的主键值，存储到参数实体类对象的哪个属性上

@Mapper
public interface EmpMapper {
    //插入一体员工数据
   @Options(useGeneratedKeys = true, keyProperty = "id")//会自动将生成的主键值，赋值给emp对象的id属性
   @Insert("insert into emp (username, password, name, gender, image, job)\n" +
            "values (#{username}, #{password}, #{name}, #{gender}, #{image}, #{job})")
   void insert(Emp emp);
}

2.5 修改

EmpMapper：实现功能

/**
 * 修改id为19的数据：
 * UPDATE emp SET username = 'jerry', password = '123', name = '杰瑞', gender = 1, image = null, job = null, entrydate = null, dept_id = null, create_time = '2023-08-19 14:54:29', update_time = '2023-08-19 14:54:29' WHERE id = 19;
 */
@Update("UPDATE emp SET username = #{username}, password = #{password}, name = #{name}, gender = #{gender}, image = #{image}, job = #{job}, " +
        "entrydate = #{entrydate}, dept_id = #{deptId}, create_time = #{createTime}, update_time = #{updateTime} WHERE id = #{id}")
void updateById(Emp emp);

功能测试

@Test
public void testUpdateById(){
    Emp emp = empMapper.queryById(19);

    emp.setUsername("robin li");
    emp.setGender(2);

    empMapper.updateById(emp);
}

2.6 查询-模糊查询

EmpMapper：实现功能

    /**
     * 模糊查询：查询姓名里包含“张”的员工列表
     * select * from emp where name like '%张%';
     * SQL语句里拼接字符串函数：concat(字符串1, 字符串2, 字符串3,....)
     */
    @Select("select * from emp where name like concat('%', #{name}, '%')")
    List<Emp> queryByName1(String name);

    @Select("select * from emp where name like '%${name}%'")
    List<Emp> queryByName2(String name);

CurdTest：功能测试

    @Test
    public void testQueryByName1(){
        List<Emp> list = empMapper.queryByName1("张");
        list.forEach(System.out::println);
    }

    @Test
    public void testQueryByName2(){
        List<Emp> list = empMapper.queryByName2("张");
        list.forEach(System.out::println);
    }

预编译SQL

预编译：不是Mybatis的概念，而是JDBC的概念。

不使用预编译：RDBMS先编译SQL（解析SQL语句，确定SQL的执行方案）；再执行SQL语句，得到结果
使用了预编译：先把SQL语句进行解析确定执行方案；然后设置参数值执行SQL

好处1-预编译执行SQL性能更高

Mybatis的CURD及动态SQL

好处2-可以防止SQL注入漏洞

Mybatis的CURD及动态SQL

`#{}`与`${}`的区别

#{}：底层使用的是预编译方式。

更安全，因为可以防止SQL注入漏洞

执行SQL的性能更高
${}：没有使用预编译，是直接拼接SQL字符串

不安全，可能存在SQL注入漏洞

执行SQL的性能不如预编译

3. Mybatis的方法参数与结果集

3.1 SQL里取方法参数的值

★如果方法只有一个参数：

如果参数是简单值(8种基本数据类型及包装类、String)，SQL语句里取参数值是：#{参数名}
如果参数是JavaBean对象，SQL语句里取JavaBean的属性值：#{属性名}

如果方法有多个参数，SQL语句里取参数值：

从SpringBoot2版本开始：#{形参名}
在SpringBoot2以前版本：【了解】

首先，给方法参数起名称，添加注解：@Param("名称")

然后，在SQL语句里使用：#{名称} 获取对应参数值

/**
     * 需求：根据姓名、性别、入职时间范围 搜索员工信息
     * SQL：select * from emp where name like ? and gender = ? and entrydate between ? and ?
     * 如果方法有多个参数，SQL语句里取参数值：#{参数名称}。从SpringBoot2开始提供的功能
     */
@Select("select * from emp where name like concat('%',#{name}, '%') and gender = #{gender} and entrydate between #{begin} and #{end}")
List<Emp> queryEmpList(String name,
                       Integer gender,
                       LocalDate begin,
                       LocalDate end);

@Select("select * from emp where name like concat('%',#{a}, '%') and gender = #{b} " +
        "and entrydate between #{c} and #{d}")
List<Emp> queryEmpList2(@Param("a") String name,
                        @Param("b") Integer gender,
                        @Param("c") LocalDate begin,
                        @Param("d") LocalDate end);

3.2 查询结果集的封装

Mybatis会自动帮我们把查询的结果集封装成实体类对象，前提条件是：

要么 JavaBean的属性名，和表的字段名完全相同。

比如：字段名是gender，Emp类里的属性名也叫gender
要么 JavaBean的属性名，和表的字段名按照下划线与驼峰映射是一致的。

比如：字段名是dept_id，Emp类里属性名是deptId

前提： 开启下划线与驼峰的命名转换，修改application.properties配置文件，添加参数

mybatis.configuration.map-underscore-to-camel-case=true

如果JavaBean的属性名和字段名完全不匹配，就需要处理这种情况

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
    private Integer id;
    /*对应的字段名是username*/
    private String uname;
    /*对应的字段名是password*/
    private String pword;
    private String name;
    private Integer gender;
    private String image;
    private Integer job;
    private LocalDate entrydate;
    private Integer deptId;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

方案一：SQL语句里给字段起别名

/**
 * JavaBean属性名 与  表字段名 完全不匹配：
 * SQL语句里给字段起别名，别名和JavaBean属性名相同
 */
@Select("select id, username as uname, password as pword,name,gender,image,job," +
        "entrydate,dept_id,create_time, update_time from emp")
List<Employee> queryEmployeeList2();

方案二：使用`@Results`和`@Result`手动映射

只需要把不同的字段配置一下，如果字段和属性名匹配，就不需要做配置

/**
 * JavaBean属性名 与  表字段名 完全不匹配：
 * 我们使用@Results和@Result注解，手动设置一下，哪个字段对应哪个属性
 *      注解1：@Results，用于配置当前查询里所有字段的映射关系
 *      注解2：@Result，用于配置某一个字段与属性的对应关系
 *          property：写的是JavaBean的属性名
 *          column：写的是表里的字段名
 */
@Select("select * from emp")
@Results({
        @Result(property = "uname", column = "username"),
        @Result(property = "pword", column = "password")
})
List<Employee> queryEmployeeList3();

4. Mybatis的XML映射文件

4.1 介绍

Mybatis的SQL语句，可以使用注解直接配置到Mapper接口里的方法上，也可以定义到XML文件里

如果SQL语句写到接口里的方法上：注解方式，适合于简单SQL或者固定不变的SQL
如果SQL语句写到XML文件里：xml方式，更适合于复杂SQL或者动态变化的SQL

注意：XML方式和注解方式可以同时使用，但是要注意

一个方法的SQL语句，要么用注解方式配置，要么用XML方式配置，不能重复配置

4.2 用法

XML文件的要求：

XML映射文件的名称与Mapper接口名称一致，并且将XML映射文件和Mapper接口放置在相同包下**（同包同名）**。
XML映射文件的namespace属性为Mapper接口全限定名一致。
XML映射文件中的sql语句与Mapper接口中的方法名一致，并保持返回类型一致。

如下图所示：

XML内容的要求：

根标签<mapper namespace="Mapper接口的全限定类名">：表示当前XML是给哪个Mapper接口配置语句的
在mapper标签里边，配置SQL语句：
- select标签：配置select语句，SQL语句写到标签里边。需要配置id属性和resultType属性
  - id属性：配置方法名。表示当前SQL语句是给哪个方法配置的
  - resultType属性：告诉Mybatis要把查询结果中的每一行数据，封装成什么对象
- insert标签：配置insert语句，SQL语句写到标签里边。需要配置id属性
  - id属性：配置方法名。表示当前SQL语句是给哪个方法配置的
- update标签：配置update语句，SQL语句写到标签里边。需要配置id属性
  - id属性：配置方法名。表示当前SQL语句是给哪个方法配置的
- delete标签：配置delete语句，SQL语句写到标签里边。需要配置id属性
  - id属性：配置方法名。表示当前SQL语句是给哪个方法配置的

4.3 示例

Mapper接口

/**
 * 使用XML方式配置SQL语句
 * XML文件的位置：Mapper接口在什么包，XML文件就必须在同包下。
 *      在resources目录下右键，创建Directory文件夹，以/为分隔符，千万不要以.为分隔符。
 *      比如：com/itheima/mapper
 * XML文件的名称：Mapper接口叫什么名字，XML文件也叫什么名字
 *
 */
List<Emp> queryEmpListXml(String name,Integer gender,LocalDate begin,LocalDate end);

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：要写Mapper接口的全限定类名。表示当前xml文件，是给哪个Mapper接口配置的
mapper的子标签：
    select标签：配置select语句，SQL语句写到标签里边
    insert标签：配置insert语句，SQL语句写到标签里边
    update标签：配置update语句，SQL语句写到标签里边
    delete标签：配置delete语句，SQL语句写到标签里边
    以上标签都有的属性：
        id：写方法名。表示当前语句是给哪个方法配置的
        resultType：select标签专用的属性
            用于告诉Mybatis，SQL查询语句的结果，要封装成什么对象
            写JavaBean的全限定类名，不需要写List、Set等等
-->
<mapper namespace="com.sdf.mapper.EmpMapper">
    <select id="queryEmpListXml" resultType="com.sdf.pojo.Emp">
        select * from emp
         where name like concat('%',#{name}, '%')
           and gender = #{gender}
           and entrydate between #{begin} and #{end}
    </select>
</mapper>

功能测试

@Test
public void testQueryEmpListXml(){
    LocalDate begin = LocalDate.of(2010, 1, 1);
    LocalDate end = LocalDate.of(2015, 12, 31);
    List<Emp> emps = empMapper.queryEmpListXml("张", 1, begin, end);
    for (Emp emp : emps) {
        System.out.println("emp = " + emp);
    }
}

4.4 给idea配置代码模板

Mybatis的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="$namespace$">
    $END$
</mapper>

配置方式：File -> Settings -> Editor -> Live Templates

二、Mybatis的动态SQL ★★★★★

1. 动态SQL介绍

当进行多条件搜索时，搜索条件通常是不确定的，导致SQL语句的条件也是不确定的：需要根据条件，来确定要拼接那些查询条件。这样的SQL语句，就是所谓的动态SQL

Mybatis提供了一些xml的标签，用于实现动态SQL语句：

if标签：用于判断
where标签：用于代替where关键字
set标签：用于代替set关键字
foreach标签：用于循环遍历
sql标签和include标签：用于抽取重用sql片段

2. if标签和where标签

if标签：用于进行判断。如果判断为true，标签里的sql才会生效

<if test="判断条件">
    如果判断为true，这里的内容才会生效
</if>

where标签：用于代替where关键字，它可以帮我们处理多余的and

Mapper接口

     /**
     * 动态SQL：根据条件查询员工。根据name和gender动态查询
     */
    List<Emp> searchEmp1(String name, Integer gender);

XML映射

<!--
    if标签：用于判断
      语法：
        <if test="判断条件表达式">
            如果判断为true，这里的内容将会生效
        </if>
      判断条件表达式：其实使用的是OGNL的表达式语法：
        名称，取对应参数的值。#{}是怎么取参数值的，这里也怎么取参数值
        判断运算：>, <, >=, <=, ==, !=
        逻辑运算：&&, ||, ! 或者 and or not
        调用参数的属性或者方法
    where标签：用于代替where关键字
        还会帮我们处理掉SQL语句里多余的and关键字
        使用了where标签之后，建议给所有的条件前边都加上and
    -->
    <select id="searchEmp1" resultType="com.sdf.pojo.Emp">
        select * from emp
        <where>
            <!-- 如果参数name值非空 并且不是空串，就添加上name的条件-->
            <if test="name!=null and name.length()>0">
                and name like concat('%',#{name}, '%')
            </if>
            <!-- 如果参数gender非空，就添加上gender的条件 -->
            <if test="gender!=null">
                and gender = #{gender}
            </if>
        </where>
    </select>

3. set标签

set标签：用于代替update语句里的set关键字，可以帮我们处理多余的逗号,

Mapper

void update(Emp emp);

XML映射

<!--
set标签：用于代替update语句里的set关键字
    可以帮我们处理掉多余的,逗号
    把所有要修改的字段sql片段，都写到set标签里边
-->
<update id="update">
    update emp
    <set>
        <if test="username!=null and username.length()>0">username=#{username},</if>
        <if test="password!=null and password.length()>0">password= #{password},</if>
        <if test="name!=null and name.length()>0">name= #{name},</if>
        <if test="gender!=null">gender= #{gender},</if>
        <if test="image!=null and image.length()>0">image= #{image},</if>
        <if test="entrydate!=null">entrydate= #{entrydate},</if>
        <if test="deptId!=null">dept_id= #{deptId},</if>
        <if test="updateTime!=null">update_time= #{updateTime}</if>
    </set>
    where id = #{id}
</update>

4. foreach标签

foreach标签：用于循环遍历

Mapper

void batchDelete(List<Integer> ids);

XML映射

<!--
foreach标签：
    collection：被循环的集合或数组
    item：定义一个变量名，通过这个变量名可以获取集合或数组里的每个值
    separator：拼接每个值时候，使用的分隔符
    open：拼接结果的前缀部分
    close：拼接结果的后缀部分
假如：ids值是 1,2,3
循环拼接的结果是：
    delete from emp where id in (1,2,3)
    (1,2,3)
for(Integer id:ids){}
-->
<delete id="batchDelete">
    delete from emp 
    <where>
        <foreach collection="ids" open="id in(" item="id" separator="," close=")">
        	#{id}
    	</foreach>
    </where>
</delete>

5. sql标签和include标签

sql标签：定义一个sql片段

include标签：引用一个sql片段

用法：如果多条SQL语句里，有某些片段是完全相同的，可以使用sql标签抽取出去，需要使用时用include引用即可

<!--定义一个SQL片段-->
<sql id="selectEmp">select * from emp</sql>

<!-- 引用一个SQL片段 -->
<select id="queryEmpListXml" resultType="com.sdf.pojo.Emp">
    <!--利用include标签，引用：select * from emp-->
    <include refid="selectEmp"></include>
    where name like concat('%',#{name}, '%')
    and gender = #{gender}
    and entrydate between #{begin} and #{end}
</select>

Mybatis的CURD及动态SQL

一、Mybatis的CURD

1. 准备工作

2.基本CURD操作

2.1查询-根据id查询一条

EmpMapper:实现功能

功能测试

2.2 查询-查询数量

EmpMapper:实现功能

功能测试

2.3 删除

EmpMapper：实现功能

功能测试

2.4 新增

EmpMapper：实现功能

功能测试

获取主键值

2.5 修改

EmpMapper：实现功能

功能测试

2.6 查询-模糊查询

EmpMapper：实现功能

CurdTest：功能测试

预编译SQL

好处1-预编译执行SQL性能更高

好处2-可以防止SQL注入漏洞

#{}与${}的区别

3. Mybatis的方法参数与结果集

3.1 SQL里取方法参数的值

3.2 查询结果集的封装

实体类

方案一：SQL语句里给字段起别名

方案二：使用@Results和@Result手动映射

4. Mybatis的XML映射文件

4.1 介绍

4.2 用法

4.3 示例

Mapper接口

XML映射文件

功能测试

4.4 给idea配置代码模板

二、Mybatis的动态SQL ★★★★★

1. 动态SQL介绍

2. if标签和where标签

Mapper接口

XML映射

3. set标签

Mapper

XML映射

4. foreach标签

Mapper

XML映射

5. sql标签和include标签

`#{}`与`${}`的区别

方案二：使用`@Results`和`@Result`手动映射