【JavaEE进阶】MyBatis表查询

xxxflower

发布于 2023-10-16 15:11:07

3330

发布于 2023-10-16 15:11:07

文章被收录于专栏：《数据结构》

在上一篇博客中我们简单了解了MyBatis的创建与使用，接下来我们进一步的学习MyBatis的相关知识。注:此博客中测试案例所使用的单元测试在文末有教程.

一. 使用MyBatis完成数据库的操作

1. MyBatis程序中sql语句的即时执行和预编译

我们在JDBC中在构造sql语句的时候，常常给字段的值用问号?代替，最后在使用方法对这些?进行赋值，这是预编译。使用预编译的好处可以防止sql注入。当然还有一种sql的执行方式就是即时执行。 SQL注入是一种常见的安全漏洞，它利用了未正确过滤或转义用户输入的数据，导致恶意用户可以在执行SQL查询时插入恶意的SQL代码。下面我们来了解一下MyBatis程序中的即使执行和预编译的构建方式.

1.1 即时执行（${}）

就像下面我们写道的根据某个字段查询单个信息的时候，我们传递了参数，在xml文件中对相应的字段进行赋值的时候使用${}这种方式就是构造sql语句即时执行的方式。

    <select id="getUserById" resultType="com.example.demo.entity.UserEntity">
        select * from userinfo where id=${id}
    </select>

我们从运行结果看到,执行的sql语句是直接被赋值的,并没有使用?.

1.2 预编译（#{}）

这种写法在程序执行的时候，我们可以看到sql语句中id的值先是被?将位置占着的。这里?表示的是只能是值，而不能是sql语句，这就防止了sql注入。

    <select id="getUserById" resultType="com.example.demo.entity.UserEntity">
        select * from userinfo where id=#{id}
    </select>

1.3 即时执行和预编译的优缺点

即时执行(${}): 优点:

当我们逛淘宝的时候，筛选商品点击价格按照从低到高，这个时候传递的就是SQL命令。从低到高传递的就是asc，从高到低传递的就是desc。使用${}可以实现排序查询,而使用#{}就不能实现排序查询,因为当使用#{}查询时,如果传递的值为String就会加单引号,就会导致sql错误.

缺点:

它的执行不安全,存在sql注入.
在使用{}时，如果传入的参数是字符串类型的数据，还需要再构造sql的语句的时候使用单引号将传入的参数引住'{}'。

SQL注入是一种常见的安全漏洞，它利用了未正确过滤或转义用户输入的数据，导致恶意用户可以在执行SQL查询时插入恶意的SQL代码。查询数据库可以看到用户名和密码都是admin.

正常情况下,用户只能通过密码来输入.

    //用户登录的场景
    UserEntity login(@Param("username")String username,@Param("password")String password);

    <select id="login" resultType="com.example.demo.entity.UserEntity">
        select * from userinfo where username='${username}' and password='${password}'
    </select>

//单元测试代码
@Test
    void login() {
        UserEntity user = userMapper.login("admin","admin");
        System.out.println(user);
    }

但是非法情况下,我们给password的属性填写一个语句就可以登录成功:

@Test
    void login() {
        UserEntity user = userMapper.login("admin","'or 1='1");
        System.out.println(user);
    }

由于and的优先级高,先执行前面的结果为false.但是后面的or表示的是两个表达式中只要有一个表达式为真,那么最后的结果就为真,那么1=‘1’,这个表达式就为真. 这就是最简单的SQL注入.

预编译（#{}）: 优点:

它的执行是安全的，可以防止sql注入。预编译他会将传入的值当成value来看待，判断这个value是否和数据库中这个字段中的值是否相等，相等就会执行成功，不相等会查找不到.
在使用#{}这种写法的时候，如果我们传递的参数是字符串类型的，我们不需要使用单引号(’ ')将#{}括起来，执行的时候，他会自动给value添加单引号。

缺点:

不能传递SQL命令，当传递SQL命令的时候他会给这个命令自动添加单引号(’ ')，但是给SQL命令添加单引号SQL语句就会报错。

//UserMapper类下:
    //传递排序规则
    List<UserEntity> getAllByOrder(@Param("myorder")String myorder);
//UserMapper.xml
	    <select id="getAllByOrder" resultType="com.example.demo.entity.UserEntity">
        select * from userinfo order by id #{myorder}
    </select>
//单元测试
    @Test
    void getAllByOrder() {
        List<UserEntity> list = userMapper.getAllByOrder("desc");
        System.out.println(list);
    }

当时当我们将#{}换成${}，再次运行就会执行成功。

select * from userinfo order by id ${myorder}

总结： {}和#{}的区别在于替换方式和安全性。{}是简单的字符串替换，直接将参数值拼接到SQL语句中，没有安全处理，存在安全风险和SQL注入风险；而#{}是预编译处理，将参数放入PreparedStatement中，使用占位符进行替换，提供了类型转换、安全处理和预编译功能，更加安全可靠。因此，为了防止SQL注入攻击和保证系统的安全性，推荐使用#{}作为参数占位符。

2. 单表的增删改等操作

在上述博客中,我们简单介绍了<select>标签.详情见:MyBatis项目创建与使用接下来,我们来实现用户的增删改操作,对应使用MyBatis的标签如下: <insert>标签:插入语句. <update>标签:修改语句. <delete>标签:删除语句.

2.1 增加操作

添加操作在接口中声明方法的时候，定义的返回值类型是int，因为默认的返回值是受影响的行数，在XML文件实现add方法时，也不需要规定返回值类型。

//UserMapper类下:
    //添加用户
    int add(UserEntity user);
//UserMapper.xml:
    <insert id="add">
        insert into userinfo(username,password,photo)values(#{username},#{password},#{photo})
    </insert>	  
//单元测试:
    @Test
    void add() {
        UserEntity user = new UserEntity();
        user.setUsername("张三");
        user.setPassword("123");
        user.setPhoto("/image/default.png");
        int result = userMapper.add(user);
        System.out.println("受影响的行数： "+result);
    }

特殊的添加：返回自增id 之前的方法默认情况下返回的是受影响的行数，如果想要返回自增id，具体实现如下。

//UserMapper类下:
	//添加操作：返回自增id
    int insert(UserEntity user);
//UserMapper.xml:
    <insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        insert into userinfo(username,password,photo)values(#{username},#{password},#{photo})
    </insert>	  
//单元测试:
    @Test
    void insert() {
        UserEntity user = new UserEntity();
        user.setUsername("李四");
        user.setPassword("123");
        user.setPhoto("");
        //因为在接口中声明的 insert 方法的参数为 user,所以测试的时候可以直接将 user对象传给这个insert方法
        int result = userMapper.insert(user);
        System.out.println("受影响的行数： "+result+" | id: "+user.getId());
    }

在XML文件中的insert标签中添加useGenerateKeys,keyColumn和keyProperty属性。

useGenerateKeys: 表示获取数据库中开启自增主键的值。在insert标签中表示的意思为获取本次添加的成员的自增主键的值。默认值为false. keyColumn: 表示设置自增主键在数据表中的字段名。 keyProperty: 表示将获取到的自增主键的值赋值给keyProperty所指的属性（实体类）.

2.2 修改操作

修改的实现和删除一样在xml文件中的update标签中不用设置返回值类型(resultMap或者resultType)，默认的返回值是受影响的行数，所以在UserMapper接口中声明方法的时候，返回值类型为int。

//UserMapper类下:
    //修改操作
    int update(UserEntity user);
//UserMapper.xml:
    <update id="update">
        update userinfo set username=#{username} where id=#{id}
    </update>	  
//单元测试:
    @Test
    void update() {
        UserEntity user = new UserEntity();
        user.setId(1);
        user.setUsername("管理员");
        int result = userMapper.update(user);
        System.out.println("受影响的行数"+result);
    }

2.3 删除操作

删除信息，默认返回的是受影响的行数，所以我们在声明方法的时候设置的返回值类型为int.

//UserMapper类下:
    //删除操作
    int delById(@Param("id")Integer id);    
//UserMapper.xml:
    <delete id="delById">
        delete from userinfo where id=#{id}
    </delete>	  
//单元测试:
    @Test
    void delById() {
        int id = 2;
        int result = userMapper.delById(id);
        System.out.println("受影响的行数："+result);
    }

MyBatis中开启事务的注解:@Transactional

2.4 like(模糊查询)操作

like查询，我们按照学习MySQL是使用的语法在XML文件中构造sql语句，在执行的时候会出现报错的问题。

//UserMapper类下:
      //like查询
    List<UserEntity> getLikeList(@Param("username")String username);  
//UserMapper.xml:
    <select id="getLikeList" resultType="com.example.demo.entity.UserEntity">
        select * from userinfo where username like %#{username}%
    </select>	  
//单元测试:
    @Test
    void getLikeList() {
        String username = "三";
        List<UserEntity> list = userMapper.getLikeList(username);
        System.out.println(list);
    }

我们可以看到，使用预编译的方式不行，那我们可以尝试使用即时执行的方式，这种方式执行确实结果是对的，但是这里使用即时执行的方式并不满足使用它的条件，会出现sql注入的情况。

解决方案:

第一种解决方法是在XML中继续直接使用#{username}，我们在业务代码中给username赋值为%三%

//UserMapper类下:
      //like查询
    List<UserEntity> getLikeList(@Param("username")String username);  
//UserMapper.xml:
    <select id="getLikeList" resultType="com.example.demo.entity.UserEntity">
        select * from userinfo where username like #{username}
    </select>	  
//单元测试:
    @Test
    void getLikeList() {
        String username = "%三%";
        List<UserEntity> list = userMapper.getLikeList(username);
        System.out.println(list);
    }

虽然这种方式可以解决问题，但是在业务代码中写，看起来就是不太好看。

第二种解决方法是使用SQL语法中的concat字段，对多个字符进行拼接。

//UserMapper类下:
      //like查询
    List<UserEntity> getLikeList(@Param("username")String username);  
//UserMapper.xml:
    <select id="getLikeList" resultType="com.example.demo.entity.UserEntity">
        select * from userinfo where username like concat('%',#{username},'%')
    </select>	  
//单元测试:
    @Test
    void getLikeList() {
        String username = "三";
        List<UserEntity> list = userMapper.getLikeList(username);
        System.out.println(list);
    }

这种写法既不会出现单引号套单引号的问题，在业务代码中进行like查询传参的时候，也没有出现使用多余的符号的问题。

2.5 实体类中的属性和数据库表中的字段名不一致出现的问题的三种解决方式

MyBatis是通过实体类的属性名称和数据库中的字段名进行映射的，如果实体类中的属性名和数据库表中的字段名不同，在进行查询的时候，出现的结果中字段的值会为null.

解决方法:

将实体类中的属性名修改成和数据库表中的数据修改成一致的。这种方式只适合于当前这个实体类，只有你一个人使用了，如果其他人的代码中也使用了你创建的实体类，那么就不能使用这种方式来修改了。

使用SQL语句中的as对数据表中的字段名进行重命名，让字段名等于创建的实体类的属性名。

    <select id="getAllByOrder" resultType="com.example.demo.entity.UserEntity">
        select id,username as name from userinfo order by id ${myorder}
    </select>

定义一个resultMap，将属性名和字段名进行手动映射。

3. 多表查询

3.1 多表查询(一对一)

这里我们查询一篇文章对应的作者的名字，站在文章的角度进行多表联合查询就是一对一的情况。使用注解的方式在MyBaits程序中构造SQL语句，我们想要使用SQL的查询，就可以在接口中的方法上加上注解@Select，想要使用删除，可以在接口的方法上添加@Delete，想要使用插入可以在方法上添加@Insert，想要实现修改可以在方法上添加@Update，然后将要执行的sql语句写在这些注解的参数中即可。

创建文章实体类

package com.example.demo.entity;

import lombok.Data;
 
import java.time.LocalDateTime;
 
@Data
public class ArticleInfo {
    private int id;
    private String title;
    private  String content;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private  int uid;
    private int rcount;
    private int state;
    //链表字段
    private  String username;
}

定义接口

package com.example.demo.mapper;
 
import com.example.demo.entity.ArticleInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
 
import java.util.List;
 
@Mapper
public interface ArticleMapper {
    @Select("select articleinfo.*,userinfo.username from articleinfo left join userinfo on articleinfo.uid=userinfo.id")
    List<ArticleInfo> getAll();
}

创建单元测试

package com.example.demo.mapper;
 
import com.example.demo.entity.ArticleInfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
 
import java.util.List;
 
import static org.junit.jupiter.api.Assertions.*;
 
@SpringBootTest
class ArticleMapperTest {
    @Autowired
    private ArticleMapper articleMapper;
 
    @Test
    void getAll() {
        List<ArticleInfo> list = articleMapper.getAll();
        System.out.println(list);
    }
}

运行结果:

3.2 多表查询(一对多)

一对多的多表查询，这里我们将查询步骤分为三步:

根据id找到用户信息
根据uid查询文章列表
然后将得到的文章信息和用户信息进行组装即可
首先我们需要在userinfo类（用户实体类）中添加一个alist属性，最后用来将得到文章信息组装到userinfo对象中。

package com.example.demo.model;
 
import lombok.Data;
 
import java.time.LocalDateTime;
import java.util.List;
 
@Data
public class Userinfo {
    private int id;
    private String username;
    private String password;
    private String photo;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private int state;
    private List<ArticleInfo> alist;
}

然后在数据持久层的UserinfoMapper类和ArticleMapper类中添加查询的方法

package com.example.demo.entity;
 
import lombok.Data;
 
import java.time.LocalDateTime;
import java.util.List;
 
@Data
public class Userinfo {
    private int id;
    private String username;
    private String password;
    private String photo;
    private LocalDateTime createtime;
    private LocalDateTime updatetime;
    private int state;
    private List<ArticleInfo> alist;
}

在UserMapperTest1单元测试类中创建一个getUserList方法，在这个方法中调用上述两个方法，最后调用setAlist方法，将getListByUid方法中得到的文章列表添加到userinfo对象中，就完成了多表查询的一对多的情况

package com.example.demo.mapper;
 
import com.example.demo.entity.ArticleInfo;
import com.example.demo.entity.Userinfo;
import com.example.demo.entity.ArticleInfo;
import com.example.demo.entity.Userinfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
 
import java.util.List;
 
import static org.junit.jupiter.api.Assertions.*;
 
@SpringBootTest//不能省略，告诉当前的测试程序，目前项目是运行在Spring容器中的
class UserMapperTest1 {
    @Autowired
    private UserinfoMapper userinfoMapper;
    @Autowired
    private ArticleMapper articleMapper;
    @Test
    void getUserList(){
        int uid = 1;
        //1.根据uid查询userinfo
        Userinfo userinfo = userinfoMapper.getUserById2(uid);
        //2.根据uid查询文章列表
        List<ArticleInfo> list = articleMapper.getListByUid(uid);
        //3.组装数据
        userinfo.setAlist(list);
        System.out.println(userinfo);
    }
 
}

二. 动态SQL的使用

动态sql是MyBatis的强大特性之一，能够完成不同条件下不同的sql拼接。

1. < if >标签

我们在上网时，经常需要填写一些表单，其中有些选项是必填的，有些是选填的，那么这个时候在MyBatis程序中按照XML的方式构造sql语句时，是不能完全胜任的。比如填通讯信息的时候，出现了一个选填项是填写QQ号，如果不填这个选项，前端传给后端代码中的这个数据的值为null，现在规定让这一项在数据库中默认为空，如果如不使用标签，那么在XML中是无法完成这个规定。在数据库中null和空是两个概念。

语法:

<!--  test中的表达式是满足使用多个条件  -->
<if test="表达式">
    <!-- 满足表达式的条件，就会进入执行其中的内容  --> 
    ....
</if>

在接口中声明方法

    //动态sql添加操作<if>
    int add2(Userinfo userinfo);

在XML文件中实现动态sql，这里再sql语句中添加标签用来判断是否设置了photo的值，如果没有设置，那就不添加这个字段在sql语句中，如果添加这个字段的值，就会在sql语句中添加这个字段。

    <insert id="add2">
        insert into userinfo(username,password
        <if test="photo != null">
            ,photo
        </if>
        )values(#{username},#{password}
        <if test="photo != null">
            ,#{photo}
        </if>
        )
    </insert>

测试单元

//给对象的属性设置值得时候，给photo属性添加值
    @Test
    void add2() {
        Userinfo userinfo = new Userinfo();
        userinfo.setUsername("张三");
        userinfo.setPassword("123");
        userinfo.setPhoto("cat.png");
        int result = userMapper.add2(userinfo);
        System.out.println("执行的结果： "+result);
    }

运行结果:

可以看到如果给photo没有设置值，那么在数据看中photo这一列是空的，不会出现null.这就解决了表单中可选项的填写问题了。如果填了表单中的可选项就会将值保存在数据库中，如果没有填写可选项，那么数据库中这个字段就不会有值。

2. < trim >标签

上面我们说的表单中存在某个选填项，假设表单上所有的选项都是选填的，那么使用<if>标签就不能满足我们的需求了。因为在判断给字段是否传值时，使用<if>标签将字段包裹起来了，但是字段和字段之间要使用，逗号隔开，所以我们还需要将逗号拼接上。但是我们不知道用户选填了那些字段，所以将逗号拼接上之后，还需要考虑逗号不能出现在开始的字段前面，结束的字段后面不能出现逗号。这个就需要使用<trim>标签中的属性来解决了。

<trim>标签的属性: prefix：表示整个语句块，以prefix的值作为前缀 suffix：表示整个语句块，以suffix的值作为后缀 prefixOverrides：表示整个语句块要去除掉的前缀 suffixOverrides：表示整个语句块要去除掉的后缀

在接口中声明方法

    /*
    * 动态sql <trim>标签
    * */
    int add3(Userinfo userinfo);

在XML文件中实现方法，由于<trim>标签中的prefix和suffix属性可以添加整个语句块的前缀和后缀，所以这里我们直接使用这两个属性拼接括号，我们在<if>标签中将逗号拼接在字段的后面，使用suffixOverrides属性指定要去除语句块中某个后缀（逗号），整个时候就会将语句块中最后一个字段之后的逗号去掉。

    <insert id="add3">
        insert into userinfo
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="username != null">
                    username,
                </if>
                <if test="password != null">
                    password,
                </if>
                <if test="photo!=null">
                    photo,
                </if>
 
            </trim>
            values
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="username != null">
                    #{username},
                </if>
                <if test="password != null">
                    #{password},
                </if>
                <if test="photo != null">
                    #{photo},
                </if>
            </trim>
    </insert>

单元测试，这里我们只添加名字和密码，不添加照片的属性。

    @Test
    void add3() {
        Userinfo userinfo = new Userinfo();
        userinfo.setUsername("李四");
        userinfo.setPassword("666");
        int result = userMapper.add2(userinfo);
        System.out.println("执行的结果： "+result);
    }

执行结果可以看到执行结果中字段中没有将photo拼接上，并且语句块中结尾的字段之后也没有逗号。数据保存在数据库中，也是按照我们的预想执行的，没有添加照片字段的值，photo列为空，不是null.

3. < where >标签

MyBatis 中多个都是非必传参数的解决方案:

1=1解决方案

<trim>标签解决:

<where>标签方案:

注意: <where>只会自动帮你去除最前面的 and 关键字，使用 where 标签不会自动帮你去除最后面的and关键字. MyBatis中，<where>标签并不只能用于生成 AND 条件，它可以用于生成任何类型的条件语句（包括AND和OR）。例如:

<select id="getUserList" parameterType="Map" resultType="User">
  SELECT *
  FROM users
  WHERE age = #{age}
  <where>
    <if test="name != null">
      OR name = #{name}
    </if>
  </where>
</select>

上述示例中，无论age的值是什么，都会根据name的值生成OR条件。

4. < set >标签

<set>标签和<where>标签在sql语句中添加方式相同，只不过where标签用在查询，set标签用在修改。但是<set>标签是去掉代码块的后缀的，而<where>标签是去掉代码块的前缀的。使用<set>标签可以避免在更新操作中出现多余的逗号和无效的更新字段。

<update id="updateUser" parameterType="User">
  UPDATE users
  <set>
    <if test="name != null">
      name = #{name},
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="email != null">
      email = #{email},
    </if>
  </set>
  WHERE id = #{id}
</update>

在上述示例中，<set>标签用于动态生成set子句。根据传入的参数值判断是否生成相应的更新字段，如果参数值为null，则不会生成相应的更新语句。注意，在生成set子句时，每个更新字段末尾都会有一个逗号，即使是最后一个字段。这是因为在动态SQL中，可以通过条件判断来控制是否生成该字段，但为了简化逻辑和代码，可以在每个字段之后都加上逗号，不影响SQL的语法正确性。另外，需要注意使用占位符（如#{name}）来引用参数值，而不是直接拼接参数值。这样可以避免SQL注入攻击和确保参数值的正确性。通过使用<set>标签，可以根据条件动态生成UPDATE语句中的字段和对应的值，提高灵活性并避免不必要的逗号和无效的更新字段。

5. < foreach >标签

在MyBatis中，<foreach>标签用于循环遍历集合或数组，并将其中的元素逐个应用到SQL语句中的特定位置，以便生成动态SQL。 <foreach>标签通常与动态SQL一起使用，可以在in子句中动态生成多个值或者在批量插入/更新操作中循环处理多个数据。下面是一个使用<foreach>标签的示例：

<select id="getUserByIdList" parameterType="List" resultType="User">
  SELECT *
  FROM users
  WHERE id IN
  <foreach item="id" collection="list" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

在上述示例中，<foreach>标签将会循环遍历传入的List类型参数list，并将每个元素存储到id变量中。循环体中的#{id}表示动态插入当前迭代的值。生成的SQL语句可能类似于以下形式（假设list包含 [1, 2, 3]）：

SELECT *
FROM users
WHERE id IN (1, 2, 3)

<foreach>标签的常用属性： collection：指定要遍历的集合或数组。 item：指定当前元素的别名。 index：指定当前元素的索引。 open：指定循环开始时的字符。 close：指定循环结束时的字符。 separator：指定每个元素之间的分隔符。

需要注意的是，<foreach>标签也可以用于批量插入或更新操作中，通过循环处理多个数据。此时，可以将循环体中的SQL片段放置在合适的位置来重复执行插入或更新。

通过使用<foreach>标签，可以实现对集合或数组的循环遍历，动态生成包含多个值的SQL语句，并在动态SQL中灵活地处理多个数据。