SpringBoot整合MyBatis(精炼概括)

这是SpringBoot整合下的MyBatis使用，大道至简，没有废话，内容完整，能满足大部分CRUD开发
ps:此文是对传统三层架构下SpringBoot整合MyBatis的高度精炼概括，如果你是纯新手，甚至不知道application.yml和三层架构，建议去看更加基础的文章

1、导包

<!--mysql包      官方自带-->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>
<!--MyBatis-test包  官方自带-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter-test</artifactId>
    <version>2.3.1</version>
    <scope>test</scope>
</dependency>
<!--连接池-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.6</version>
</dependency>

2、配置

application.yml

spring:
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/yxksxt?useSSL=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
      username: root
      password: '123456
mybatis:
  mapper-locations: /mapper/*.xml #资源目录下mapper文件夹中的.xml文件
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl  #日志输出到控制台

3、注解

四个注解

@Mapper
public interface AddressBookMapper {

    //新增
    @Insert("insert into address_book(user_id, consignee, phone) values(#{userId}, #{consignee}, #{phone})")
    void insert(AddressBook addressBook);

	//查询
    @Select("select * from address_book where id = #{id}")
    AddressBook getById(Long id);

	//修改
    @Update("update address_book set is_default = #{isDefault} where user_id = #{userId}")
    void updateIsDefaultByUserId(AddressBook addressBook);

    //删除
    @Delete("delete from address_book where id = #{id}")
    void deleteById(Long id);
}

传参规则：MyBatis使用#{}作占位符，接收参数

• 单个参数：MyBatis会自动将该参数绑定到SQL语句中的占位符

• 多个参数：需要使用@Param注解来明确指定每个参数的名称

@Insert("insert into address_book(user_id, consignee, phone) values(#{userId}, #{consignee}, #{phone})")  
void insert(@Param("userId") int userId, @Param("consignee") String consignee, @Param("phone") String phone);

• 对象参数：保证占位符与对象属性名一致即可

• Mapper参数：保证占位符与键名一致即可

4、xml

通过xml映射编写sql

命名：映射UserMapper.java的xml文件叫UserMapper.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.zhou.mapper.TopicMapper">

    <select id="page" parameterType="com.zhou.entity.TopicPige" resultType="com.zhou.dto.Topic">
        select id,stem,answer,type_id as typeId,options,subject_id as subjectId,status from topic
            <where>
                <if test="stem != null">
                    and stem like concat('%',#{stem},'%')
                </if>
                <if test="subjectId != null">
                    and subject_id = #{subjectId}
                </if>
            </where>
    </select>
</mapper>

namespace：指定这个xml映射那个Mapper.java类

id：对应类里面的方法名称

parameterType：参数类型

resultType：返回值类型

5、动态sql

https://blog.csdn.net/sdgihshdv/article/details/78258886

1、if

<!--test条件判断语句	true则拼接SQL-->
<if test="status != nuLL">
status = #{status}
</if> 

本质：if为true，拼接SQL，以下status != null时报错

<seLect id="selectByCondition" resultMap= "brandResultMap">
	seLect * from tb_ brand where
		<if test="status != null">
			status = #{status}
		</if>
		<if test="companyName != null and companyName != ' ' "> .
			and companyName like #{companyName}
		</if> 
</select>

如果status = null，拼接后会是错误的SQL语句

seLect * from tb_ brand where and companyName like #{companyName}

处理方法：

2、< where>< /where>

它的意思就是：在以下sql的前面加上where，同时，如果sql是以and或or开头，将其删除

<seLect id="selectByCondition" resultMap= "brandResultMap">
	seLect * from tb_ brand
      <where>
		   <if test="status != null">
		   	status = #{status}
		   </if>
		   <if test="companyName != null and companyName != ' ' "> .
		   	and companyName like #{companyName}
		   </if> 
      </where>
</select>

2、< set>< /set>

不使用< ser>标签

<update id="updateUser" parameterType="com.dy.entity.User">
    update user set 
        <if test="name != null">
            name = #{name},
        </if> 
        <if test="password != null">
            password = #{password},
        </if> 
        <if test="age != null">
            age = #{age}
        </if> 
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            and deleteFlag = 0;
        </where>
</update>

问题又来了： “如果我只有name不为null, 那么这SQL不就成了 update set name = #{name}, where ........ ? 你那name后面那逗号会导致出错啊！”

是的，这时候，就可以用mybatis为我们提供的set 标签了。下面是通过set标签改造后

<update id="updateUser" parameterType="com.dy.entity.User">
    update user
        <set>
            <if test="name != null">name = #{name},</if> 
            <if test="password != null">password = #{password},</if> 
            <if test="age != null">age = #{age},</if> 
        </set>
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            and deleteFlag = 0;
        </where>
</update>

它的意思是：在以下sql的开头加上set，若sql以,结尾，删除,

3、choose(相当于swith)

<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG WHERE state = ‘ACTIVE’
    <choose>
        <when test="title != null">
            AND title like #{title}
        </when>
        <when test="author != null and author.name != null">
            AND author_name like #{author.name}
        </when>
        <otherwise>
            AND featured = 1
        </otherwise>
    </choose>
</select>

用where标签包裹choose标签，可省略otherwise

4、foreach 循环

一般用于批量添加数据

//给inset方法传入数组或集合
void inset(List<DishFlavor> flavors);

collection="flavors" 指定变量的集合或数组 item="df" 用df操作每个数组/集合单元
separator="," 每一次循环结束在末尾加上"," 最后一次循环不加

<update id="inset">
    insert into dish_flavor (dish_id,name,value)
    <foreach collection="flavors" item="df" separator=",">
        (#{df.dishId},#{df.name},#{df.value})
    </foreach>
</update>

6、分页插件 PageHelper

https://zhuanlan.zhihu.com/p/344982068

他能让select自动拼接liml
能让MyBatis的查询自动返回Page对象

(一) 、坐标

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.1</version>
</dependency>

(二) 使用

1、serverce

使用时要保证PageHelper.startPage(1,2);位于调用Mapper方法的上方

import com.github.pagehelper.Page;

PageHelper.startPage(1,2);//第几页 一页几条数据
Page<SetmealVO> page = setmealMapper.getPage(setmealPageQueryDTO);

2、Mapper

//分页查询
Page<SetmealVO> getPage(SetmealPageQueryDTO setmealPageQueryDTO);

<select id="getPage" resultType="com.sky.vo.SetmealVO">
    select * from setmeal
    <where>
        <if test="name != null">name =#{name}</if>
        <if test="categoryId != null">category_id =#{categoryId}</if>
        <if test="status != null">status =#{status}</if>
    </where>
</select>