Springboot --- mybatis初步使用

1.准备工具

idea jdk1.8 一个简单的springboot工程 手 postman

2.mybaties初步集成

2.1 配置mybatis环境依赖

在maven的pom.xml文件中添加以下依赖，当然还有其他依赖，比如springweb等，个人环境不一样版本不一样自己配置就行。

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

我的pom.xml完整版

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.ZJ</groupId>
    <artifactId>springboot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot</name>
    <description>springboot</description>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.33</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

然后需要在springboot配置文件中，配置相应单独所需配置（我用的yml格式配置文件）

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/socks?setUnicode=true&characterEncoding=utf8&useAffectedRows=true
    username: root
    password: zhoujian0.
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  configuration:
    map-underscore-to-camel-case: true

以上分别是jdbc驱动和驼峰功能

原因Mybatis 默认实体属性名称和数据库字段一一对应
就是说数据库字段 user_name那么实体属性字段 也必须是user_name但java一般采用驼峰命名 userName那么mybatis 为符合java 特别设置的这个参数 map-underscore-to-camel-case ： true开启驼峰功能，使得user_name==userName

2.2 实体类User

import lombok.Data;

@Data
public class User {
    private Integer userId;
    private String username;
    private String password;
    private String email;
}

2.3查询接口文件（并实现通过@selsect注解）

public interface UserMapper {

    @Select("select * from t_user")
    List<User> list();

    @Results({
            @Result(property = "userId", column = "USER_ID"),
            @Result(property = "username", column = "USERNAME"),
            @Result(property = "password", column = "PASSWORD"),
            @Result(property = "email", column = "EMAIL")
    })
    @Select("select * from t_user")
    List<User> listSample();

    @Select("select * from t_user where username like #{username} and password like #{password}")
    User get(@Param("username") String username, @Param("password") String password);

    @SelectProvider(type = UserSqlProvider.class, method = "listByUsername")
    List<User> listByUsername(String username);

    @SelectProvider(type = UserSqlProvider.class, method = "getBadUser")
    User getBadUser(@Param("username") String username, @Param("password") String password);
}

这次接口中用了两种利用sql语句的方法，一种是直接用@select注解进行查询（前三种方法，其中第二种利用了@Results进行一个字段映射，将User类中的属性与数据库中的字段联系起来，更方便处理，但是生产环境不建议这么做，应为有的数据表可能有很多字段，这么查全部很麻烦，但是查一部分还是很轻松的），后面两种方法利用了@secletprovider映射sql语句文件来进行查询，且利用了多参数查询，但是注意无论那种方式只要设计多参数就需要利用@Param注解注入参数

2.4 sql语句文件

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

public class UserSqlProvider {
    public String listByUsername(String username) {
        return "select * from t_user where username =#{username}";
    }

    public String getBadUser(@Param("username") String username, @Param("password") String password) {
        return new SQL() {{
            SELECT("*");
            FROM("t_user");
            if (username != null && password != null) {
                WHERE("username like #{username} and password like #{password}");
            } else {
                WHERE("1=2");
            }
        }}.toString();
    }
}

2.5controller文件(web)

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/user/*")
public class UserController {

    @SuppressWarnings("all")
    @Autowired
    UserMapper userMapper;

    @GetMapping("list")
    public List<User> list() {
        return userMapper.list();
//        return userMapper.listSample();
    }

    @GetMapping("list/{username}")
    public List<User> listByUsername(@PathVariable("username") String username) {
        return userMapper.listByUsername(username);
    }

    @GetMapping("get/{username}/{password}")
    public User get(@PathVariable("username") String username, @PathVariable("password") String password) {
        return userMapper.get(username, password);
    }

    @GetMapping("get/bad/{username}/{password}")
    public User getBadUser(@PathVariable("username") String username, @PathVariable("password") String password) {
        return userMapper.getBadUser(username, password);
    }

}

2.6接口查询结果

接口localhost:8080/user/list

 接口localhost:8080/user/list/lisi

 

接口 localhost:8080/user/get/lisi/321321

 

接口localhost:8080/user/get/bad/lisi/321321

 

 2.7数据库与目录结构

2.7.1数据库

sql文件链接（阿里云盘）https://www.aliyundrive.com/s/jzutYnaWFqg

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `USER_ID` int(11) NOT NULL,
  `USERNAME` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `PASSWORD` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `email` varchar(50) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('1', 'zhangsan', '123123', '11111111111@QQ.com');
INSERT INTO `t_user` VALUES ('2', 'lisi', '321321', '22222222222@168.com');

 

2.7.2目录结构 

 

 本文参考SpringBoot 快速整合Mybatis（去XML化+注解进阶） - 简书 (jianshu.co

有部分改动。我也是在学习过程中，文章只是为了梳理知识与当做笔记用。