【Springboot】mybatis进阶操作

动态sql

动态SQL是Mybatis的强⼤特性之⼀，能够完成不同条件下不同的sql拼接。根据需求，动态拼接sql.

1.增加

根据用户的输入情况，进行插入。

-- 全部输入
insert into userinfo(username,password,age,gender,phone) values (?,?,?,?,?);
-- 如果性别为空
insert into userinfo(username,password,age,phone) values (?,?,?,?);
-- 如果年龄为空
insert into userinfo(username,password,age,phone) values (?,?,?,?);

if 标签

在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="com.example.mybatisdemo.mapper.User2Mapper">
    <insert id="insertByXML" useGeneratedKeys="true" keyProperty="id">
        insert into userinfo(
            <if test="username != null">
                username,
            </if>
            <if test="password != null">
                password,
            </if>
            <if test="age != null">
                age,
            </if>
            <if test="gender != null">
                gender,
            </if>
            <if test="phone != null">
                phone
            </if>
            )
        values (
            <if test="username != null">
                #{username},
            </if>
            <if test="password != null">
                #{password},
            </if>
            <if test="age != null">
                #{age},
            </if>
            <if test="gender != null">
                #{gender},
            </if>
            <if test="phone != null">
                #{phone}
            </if>
            );
    </insert>
</mapper>

实体层代码：

@Mapper
public interface User2Mapper {
     Integer insertByXML(UserInfo userInfo);
}

在注解中实现：

1.把全部的sql放在script标签下
2.使用if标签

 @Insert("<script> " +
         "insert into userinfo(" +
         "            <if test='username != null'>" +
         "                username," +
         "            </if>" +
         "            <if test='password != null'>" +
         "                password," +
         "            </if>" +
         "            <if test='age != null'>" +
         "                age," +
         "            </if>" +
         "            <if test='gender != null'>" +
         "                gender," +
         "            </if>" +
         "            <if test='phone != null'>" +
         "                phone" +
         "            </if>" +
         "            )" +
         "        values (" +
         "            <if test='username != null'>" +
         "                #{username}," +
         "            </if>" +
         "            <if test='password != null'>" +
         "                #{password}," +
         "            </if>" +
         "            <if test='age != null'>" +
         "                #{age}," +
         "            </if>" +
         "            <if test='gender != null'>" +
         "                #{gender}," +
         "            </if>" +
         "            <if test='phone != null'>" +
         "                #{phone}" +
         "            </if>" +
         "            );" +
         "</script>")
 Integer insert(UserInfo userInfo);

script标签中和xml中的内容是一样的。xml更加易读，书写有提示。

如果自由选择mybatis实现方式：

1. 简单SQL 使用注解
2. 动态SQL 使用xml

trim标签

trim标签帮我们去除多余的字符。比如上面如果phone为null,那么后面多余的逗号是没有办法处理的。我们解决方案就是使用trim标签。

<?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.example.mybatisdemo.mapper.User2Mapper">
    <insert id="insertByXML" useGeneratedKeys="true" keyProperty="id">
        insert into userinfo
            <trim suffixOverrides="," prefix="(" suffix=")">
                <if test="username != null">
                    username,
                </if>
                <if test="password != null">
                    password,
                </if>
                <if test="age != null">
                    age,
                </if>
                <if test="gender != null">
                    gender,
                </if>
                <if test="phone != null">
                    phone
                </if>
            </trim>
        values
            <trim suffixOverrides="," prefix="(" suffix=")">
                <if test="username != null">
                    #{username},
                </if>
                <if test="password != null">
                    #{password},
                </if>
                <if test="age != null">
                    #{age},
                </if>
                <if test="gender != null">
                    #{gender},
                </if>
                <if test="phone != null">
                    #{phone}
                </if>
            </trim>

    </insert>
</mapper>

prefixOverrides：去除trim标签代码块最前面指定的字符
prefix：在trim标签代码块最前面添加指定的字符
suffix：在trim标签代码块最后面添加指定的字符
suffixOverrides：去除trim标签代码块最后面指定的字符

2.查询

List<UserInfo> select(UserInfo userInfo);

如果使用trim+if标签处理没有任何参数的时候，需要加上条件1=1.

<select id="select" resultType="com.example.mybatisdemo.entity.UserInfo">
    select * from userinfo
    where 1=1
    <trim prefixOverrides="and">
        <if test="username!=null">
            and username = #{username}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </trim>
</select>

除此之外也可以使用where标签

where标签

<select id="select" resultType="com.example.mybatisdemo.entity.UserInfo">
    select * from userinfo
    <where>
        <if test="username!=null">
            username = #{username}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </where>
</select>

3.修改

Integer update(UserInfo userInfo);

xml文件中使用trim + if代码：

<update id="update">
    update userinfo set
        <trim suffixOverrides=",">
            <if test="username != null">
                username = #{username},
            </if>
            <if test="password != null">
                password = #{password},
            </if>
            <if test="gender != null">
                gender = #{gender}
            </if>
        </trim>
    where id = #{id}
</update>

set标签

<update id="update">
    update userinfo
    <set>
        <if test="username!=null">
            username = #{username},
        </if>
        <if test="password!=null">
            password = #{password},
        </if>
        <if test="gender!=null">
            gender = #{gender}
        </if>
    </set>
    where id = #{id}
</update>

测试代码：

@Test
void update() {
    UserInfo userInfo = new UserInfo();
    userInfo.setId(5);
    userInfo.setPassword("12123456");
    user2Mapper.update(userInfo);
}

4.删除

foreach标签

属性：

• collection：绑定⽅法参数中的集合，如List，Set，Map或数组对象
• item：遍历时的每⼀个对象
• open：语句块开头的字符串
• close：语句块结束的字符串
• separator：每次遍历之间间隔的字符串

Integer batchDelete(List<Integer> ids);

xml文件中的代码：

<delete id="batchDelete">
    delete from userinfo
    where id in
        <foreach collection="ids" separator="," item="id" open="(" close=")">
            #{id}
        </foreach>
</delete>

sql和include标签

把一些重复的sql可以提取出来,使用标签来标识，使用时,使用标签。

比如：

<sql id="selectTable">
    select * from userinfo
</sql>
<select id="select" resultType="com.example.mybatisdemo.entity.UserInfo">
    <include refid="selectTable"></include>
    where 1=1
    <trim prefixOverrides="and">
        <if test="username!=null">
            and username = #{username}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </trim>
</select>
<select id="select2" resultType="com.example.mybatisdemo.entity.UserInfo">
    <include refid="selectTable"></include>
    <where>
        <if test="username!=null">
            username = #{username}
        </if>
        <if test="age!=null">
            and age = #{age}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </where>
</select>

mybatis练习–表白墙

添加依赖：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

yml配置文件

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/messagewall?characterEncoding=utf8&useSSL=false
    username: root
    password: abc123
mybatis:
  configuration: # 配置打印 MyBatis日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true #配置驼峰自动转换
  # 配置 mybatis xml 的文件路径，在 resources/mapper 创建所有表的 xml 文件
  mapper-locations: classpath:mapper/**Mapper.xml

数据库设计：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS messagewall CHARSET utf8;
USE messagewall;
-- 创建表
DROP TABLE IF EXISTS 
CREATE TABLE message_info(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`from` VARCHAR(127) NOT NULL,
	`to` VARCHAR(127) NOT NULL,
	`message` VARCHAR(127) NOT NULL,
	`delete_flag` TINYINT(4) DEFAULT 0, # 0正常，1删除
	create_time DATETIME DEFAULT NOW(),
	update_time DATETIME DEFAULT NOW()
);

实体类改变：

@Data
public class MessageInfo {
    private Integer id;
    private String from;
    private String to;
    private String message;
    private String deleteFlag;
    private Date createTime;
    private Date updateTime;
}

实体层代码：

@Mapper
public interface MessageMapper {
    @Insert("insert into message_info (`from`,`to`,`message`) values (#{from},#{to},#{message})")
    public Integer insertMessage(MessageInfo messageInfo);
    @Select("select * from message_info")
    public List<MessageInfo> selectAllMessage();
}

service层代码：

@Service
public class MessageService {
    @Autowired
    MessageMapper messageMapper;
    public Integer add(MessageInfo messageInfo){
        Integer result = messageMapper.insertMessage(messageInfo);
        return result;
    }
    public List<MessageInfo> getAllMessage(){
        return messageMapper.selectAllMessage();
    }
}

controller层代码：

@RestController
@RequestMapping("/message")
public class MessageController {
    @Autowired
    private MessageService messageService;
    @RequestMapping("/publish")
    public boolean publishMessage(@RequestBody MessageInfo messageInfo){
        System.out.println(messageInfo);
        if (!StringUtils.hasLength(messageInfo.getFrom()) ||
            !StringUtils.hasLength(messageInfo.getTo()) ||
            !StringUtils.hasLength(messageInfo.getMessage())){
            return false;
        }
        messageService.add(messageInfo);
        return true;
    }
    @RequestMapping("/getMessageList")
    public List<MessageInfo> getMessageList(){
        return messageService.getAllMessage();
    }
}

前端代码和springMVC中一样，不需要进行改变。