SpringBoot整合Mybatis

整合Mybatis

添加依赖

• 使用springboot2.4是因为2.4之后测试默认用的是junit5不用过多配置

  <?xml version="1.0" encoding="UTF-8"?>
  <project xmlns="http://maven.apache.org/POM/4.0.0"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
  
      <groupId>org.example</groupId>
      <artifactId>SpringBoot-Mybatis</artifactId>
      <version>1.0-SNAPSHOT</version>
  
      <parent>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-parent</artifactId>
          <version>2.4.13</version>
      </parent>
  
      <properties>
          <maven.compiler.source>8</maven.compiler.source>
          <maven.compiler.target>8</maven.compiler.target>
      </properties>
  
      <dependencies>
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-web</artifactId>
          </dependency>
  
          <dependency>
              <groupId>org.mybatis.spring.boot</groupId>
              <artifactId>mybatis-spring-boot-starter</artifactId>
              <version>2.1.0</version>
          </dependency>
  
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>5.1.45</version>
          </dependency>
  
          <dependency>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
          </dependency>
  
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
          </dependency>
  
      </dependencies>
  
  </project>

  yaml配置

server:
  port: 8080 #服务地址
spring:
  datasource:
    url:  #数据库连接地址
    username:   #用户名
    password:   #密码
    driver-class-name: com.mysql.jdbc.Driver

# 配置mybatis规则
mybatis:
#  config-location: classpath:mybatis/mybatis-config.xml  #全局配置文件位置，可以不用配置
  mapper-locations: classpath:mybatis/mapper/*.xml  #sql映射文件位置
  configuration:
    map-underscore-to-camel-case: true #开启下划线转驼峰命名规则

logging:
  level:
    com.mybatis.dao: debug #控制台打印sql

表设计



新建实体类

@Data
public class User {
    private Long userId;
    private String userName;
    private String address;
    private Integer age;
    private String sex;

    public User() {

    }

    public User(String userName, String address, Integer age, String sex) {
        this.userName = userName;
        this.address = address;
        this.age = age;
        this.sex = sex;
    }
}

新建Mapper接口

• @Param适用于传多个参数

• 不想使用@Param可以用对象/Map传多个值

@Component
public interface UserMapper {

    List<User> getAll();

    Integer addUser(User user);

    Integer updateUser(User user);

    Integer delUser(User user);

    List<User> queryUser(User user);

    List<User> getUserByNames(List<String> names);

    List<User> getUser(@Param("name") String userName,@Param("age") Integer age);
}

编写Mapper.xml配置

* resultMap标签：可以做结果集的映射
  * property属性：实体中的属性名
  * column属性：数据库中的属性


* sql标签：可以编写一些代码片段，有利于代码的复用


* include标签：用于引用，常和sql标签一起使用，可以引用sql标签


* if标签：和Java中的if一样，用于动态sql中


* trim标签：用于动态sql中
  * prefix属性：动态sql中的开始位置内容
  * suffix属性：动态sql中的结束位置内容
  * suffixOverrides属性：动态sql中的拼接符


* set标签：动态sql,用于update中给字段赋值,会在开始位置加入set


* where标签：动态sql,用于where条件,会在开始位置加入where,同时会对and和or进行处理


* bind标签：用于加工属性,并生成新的属性
  * name属性：新属性的的名称
  * value属性：新属性的值，想获取对象中的数据必须使用_parameter,和Java类似


* select标签：查询专用
* insert标签：添加专用
* update标签：更新专用
* delete标签：删除专用
  * id属性：用于绑定接口的方法名
  * resultMap属性：用于绑定resultMap标签，并指定返回类型
  * parameterType属性：接口的传入类型
  * resultType属性：指定返回类型
  * keyProperty属性：实体类绑定表中的主键，值为实体类中的属性（inset用于返回自增id）
  * useGeneratedKeys：开始生成key，也就是开启主键的生成（inset用于返回自增id）

<?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.mybatis.dao.UserMapper">
    
    <resultMap id="userResult" type="com.mybatis.entity.User">
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="address" column="address"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
    </resultMap>
    
    <sql id="selectColumn">
        `user_id`,`user_name`,`address`,`age`,`sex`
    </sql>

    <select id="getAll" resultMap="userResult">
        select
        <include refid="selectColumn"></include>
        from user;
    </select>

    <insert id="addUser" parameterType="com.mybatis.entity.User" keyProperty="userId" useGeneratedKeys="true">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userName != null">
                `user_name`,
            </if>
            <if test="address != null">
                `address`,
            </if>
            <if test="age != null">
                `age`,
            </if>
            <if test="sex != null">
                `sex`,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userName != null">
                #{userName},
            </if>
            <if test="address != null">
                #{address},
            </if>
            <if test="age != null">
                #{age},
            </if>
            <if test="sex != null">
                #{sex},
            </if>
        </trim>
    </insert>

    <update id="updateUser" parameterType="com.mybatis.entity.User">
        update user
        <set>
            <if test="userName != null">
                `user_name` = #{userName},
            </if>
            <if test="address != null">
                `address` = #{address},
            </if>
            <if test="age != null">
                `age` = #{age},
            </if>
            <if test="sex != null">
                `sex` = #{sex},
            </if>
        </set>
        where user_id = #{userId}
    </update>

    <delete id="delUser" parameterType="com.mybatis.entity.User">
        delete from user
        <where>
            <if test="userName != null">
                user_name = #{userName}
            </if>
            <if test="address != null">
                and address = #{address}
            </if>
            <if test="age != null">
                and age = #{age}
            </if>
            <if test="sex != null">
                and sex = #{sex}
            </if>
        </where>
    </delete>

    <select id="queryUser" parameterType="com.mybatis.entity.User" resultMap="userResult">
        <bind name="name" value="'%' + _parameter.getUserName() + '%'"/>
        select
        <include refid="selectColumn"/>
        from user where `user_name` like #{name}
    </select>

    <select id="getUserByNames" parameterType="list" resultMap="userResult">
        select
        <include refid="selectColumn"/>
        from user where `user_name` in
        <foreach collection="list" item="name" index="index" open="(" separator="," close=")">
            #{name}
        </foreach>
    </select>

    <select id="getUser" resultType="com.mybatis.entity.User">
        select * from user where `user_name` = #{name} and `age` = #{age}
    </select>
</mapper>

service层代码

@Service
public class UserServiceImpl implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<User> getAll() {
        return userMapper.getAll();
    }

    @Override
    public Integer addUser(User user) {
        return userMapper.addUser(user);
    }

    @Override
    public Integer updateUser(User user) {
        return userMapper.updateUser(user);
    }

    @Override
    public Integer delUser(User user) {
        return userMapper.delUser(user);
    }

    @Override
    public List<User> queryUser(User user) {
        return userMapper.queryUser(user);
    }

    @Override
    public List<User> getUserByNames(List<String> names) {
        return userMapper.getUserByNames(names);
    }

    @Override
    public List<User> getUser(String userName, Integer age) {
        return userMapper.getUser(userName,age);
    }
}

启动类

@SpringBootApplication
@MapperScan("com.mybatis.dao")//指定扫描mapper接口的位置
public class RunApplication {
    public static void main(String[] args) {
        SpringApplication.run(RunApplication.class,args);
    }
}

编写SpringBoot测试类
* 测试类使用的junit5
* 测试类必须和启动类目录同级

@SpringBootTest
public class UserTest {

    @Autowired
    UserService userService;

    @Test
    public void getAll(){
        List<User> all = userService.getAll();
        for (User user : all) {
            System.out.println(user);
        }
    }

    @Test
    public void addUser(){
        User user = new User("王五","上海市",22,"男");
        Integer integer = userService.addUser(user);
        System.out.println(integer);
        System.out.println(user.getUserId());
    }

    @Test
    public void del(){
        User user = new User();
        user.setAge(18);
        Integer integer = userService.delUser(user);
        System.out.println(integer);
    }

    @Test
    public void update(){
        User user = new User();
        user.setUserName("李四");
        user.setAddress("杭州");
        user.setSex("女");
        user.setAge(20);
        user.setUserId(11l);
        Integer integer = userService.updateUser(user);
        System.out.println(integer);
    }

    @Test
    public void queryUser(){
        User user = new User();
        user.setUserName("四");
        List<User> users = userService.queryUser(user);
        for (User user1 : users) {
            System.out.println(user1);
        }
    }

    @Test
    public void getUserByNames(){
        List<String> list = new ArrayList<>();
        list.add("张三");
        list.add("李四");
        List<User> userByNames = userService.getUserByNames(list);
        for (User userByName : userByNames) {
            System.out.println(userByName);
        }
    }

    @Test
    public void getUser(){
        List<User> users = userService.getUser("张三", 18);
        for (User user : users) {
            System.out.println(user);
        }
    }
}

启动测试类