Springboot+mybatis+mysql8.0

一、环境信息

jdk:1.8
mysql：8.0.23
springboot：2.1.0
mybatis:3.5.2

二、maven依赖

<?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>spring-mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.0.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <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>2.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.mybatis</groupId>
                    <artifactId>mybatis</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.12</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <resources>
            <!--此处的配置是识别到mapper.xml文件，也可以在application.properties中配置-->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>
</project>

三、mybatis配置信息

1、数据库配置信息

spring.datasource.url = jdbc:mysql://192.168.0.12:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=riant
spring.datasource.password=root
#数据库名称
spring.datasource.name=test
#默认情况下mybatis是不开启SQL日志输出，需要手动配置,com.riant.dao为mapper文件夹路径
logging.level.com.riant.mybatis.dao=debug
#指定mapper的配置文件的路径是mapper文件夹下的所有 xml文件。
mybatis.mapper-locations=classpath:mapper/*.xml 

注意：mysql8.0版本配置与mysql5.0之间的区别：

• 驱动区别，com.mysql.cj.jdbc.Driver
• 连接信息，要有时区信息，要有是否使用ssl信息

2、mybatis的mapper.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" >
<!--上面2行的是约束依赖，固定照抄就好-->
<!--下面的才是要自己编写的地方-->
<!--写mapper的配置文件第一步就是要写<mapper></mapper>标签-->
<!--<mapper></mapper>标签里包含着各个CURD操作的SQL语句-->
<mapper namespace="com.riant.mybatis.dao.WebSiteMapper">
    <!--定义一个名为BaseResultMap的返回类型-->
    <resultMap id="BaseResultMap" type="com.riant.mybatis.bean.WebSite">
        <id column="id" property="id" jdbcType="INTEGER"></id>
        <result column="name" property="name" jdbcType="CHAR"></result>
        <result column="url" property="url" jdbcType="VARCHAR"></result>
        <result column="alexa" property="alexa" jdbcType="INTEGER"></result>
        <result column="country" property="country" jdbcType="CHAR"></result>
    </resultMap>

    <!--查找语句-->
    <!--resultMap表示函数返回的类型-->
    <select id="selectWebSiteById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
       select * from websites where id = #{id,jdbcType=INTEGER}
    </select>
    <!--resultMap表示函数返回的类型-->
    <select id="selectAllWebSite" resultMap="BaseResultMap">
       select * from websites
    </select>
    <insert id="insertWebSite">
        insert into websites
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name,
            </if>
            <if test="url != null">
                url,
            </if>
            <if test="alexa != null">
                alexa,
            </if>
            <if test="country != null">
                country,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name,jdbcType=CHAR},
            </if>
            <if test="url != null">
                #{url,jdbcType=VARCHAR},
            </if>
            <if test="alexa != null">
                #{alexa,jdbcType=INTEGER},
            </if>
            <if test="country != null">
                #{country,jdbcType=CHAR},
            </if>
        </trim>
    </insert>
    <delete id="deleteById">
        delete from websites where id=#{id,jdbcType=INTEGER}
    </delete>
    <update id="updateWebSiteById" parameterType="com.riant.mybatis.bean.WebSite">
        update websites
        <set>
            <if test="name != null">
                name =#{name},
            </if>
            <if test="url != null">
                url =#{url},
            </if>
            <if test="alexa != null">
                alexa =#{alexa},
            </if>
            <if test="country != null">
                country =#{country},
            </if>
        </set>
        where id=#{id,jdbcType=INTEGER}
    </update>
</mapper>

3、建表语句

CREATE TABLE `websites` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` CHAR(20) NOT NULL DEFAULT '' COMMENT '站点名称',
	`url` VARCHAR(255) NOT NULL DEFAULT '',
	`alexa` INT NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
	`country` CHAR(10) NOT NULL DEFAULT '' COMMENT '国家',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10
;

四、分层接口

1、dao层接口

@Repository
@Mapper
public interface WebSiteMapper {
    WebSite selectWebSiteById(int id);
    List<WebSite> selectAllWebSite();
    Integer insertWebSite(WebSite webSite);
    Integer deleteById(int id);
    Integer updateWebSiteById(WebSite webSite);
}

2、service层接口

package com.riant.mybatis.service.impl;

import com.github.pagehelper.PageHelper;
import com.riant.mybatis.bean.WebSite;
import com.riant.mybatis.dao.WebSiteMapper;
import com.riant.mybatis.service.WebSiteService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @Classname WebSiteServiceImpl
 * @Description TODO
 * @Date 2021/6/3 22:31
 * @Created by 张斌
 */
@Service("webSiteService")
public class WebSiteServiceImpl implements WebSiteService {

    @Autowired
    private WebSiteMapper webSiteMapper;

    @Override
    public WebSite getWebSite(int id) {
        return webSiteMapper.selectWebSiteById(id);
    }

    @Override
    public List<WebSite> getAllWebSite() {
        return webSiteMapper.selectAllWebSite();
    }

    /*
     * 这个方法中springboot分页插件pagehelper
     * 很简单，只需要在service层传入参数，然后将参数传递给一个插件的一个静态方法即可；
     * pageNum 开始页数
     * pageSize 每页显示的数据条数
     * */
    @Override
    public List<WebSite> getAllWebSite(int pageNum, int pageSize) {
        //将参数传给这个方法就可以实现物理分页了，非常简单。
        PageHelper.startPage(pageNum, pageSize);
        return webSiteMapper.selectAllWebSite();
    }

    @Override
    public Integer updateWebSite(WebSite webSite) {
        return webSiteMapper.updateWebSiteById(webSite);
    }

    @Override
    public Integer insertWebSite(WebSite webSite) {
        return webSiteMapper.insertWebSite(webSite);
    }

    @Override
    public Integer deleteWebSite(int id) {
        return webSiteMapper.deleteById(id);
    }
}

3、contoller层接口

package com.riant.mybatis.controller;

import com.alibaba.fastjson.JSONObject;
import com.riant.mybatis.bean.WebSite;
import com.riant.mybatis.service.WebSiteService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @Classname WebSiteController
 * @Description TODO
 * @Date 2021/6/3 22:22
 * @Created by 张斌
 */
@RestController
@RequestMapping("/website")
public class WebSiteController {

    @Autowired
    private WebSiteService webSiteService;

    @RequestMapping(path = "/v1/{id}", method = RequestMethod.GET)
    public WebSite getWebSite(@PathVariable int id) {
        return webSiteService.getWebSite(id);
    }

    @RequestMapping(path = "/v1/website", method = RequestMethod.GET)
    public List<WebSite> getAllWebSite() {
        List<WebSite> list = webSiteService.getAllWebSite();
        return webSiteService.getAllWebSite();
    }

    @RequestMapping(path = "/v1/{pageNum}/{size}", method = RequestMethod.GET)
    public List<WebSite> getAllWebSite(@PathVariable int pageNum, @PathVariable int size) {
        List<WebSite> list = webSiteService.getAllWebSite(pageNum, size);
        return list;
    }

    @RequestMapping(path = "/v1/update", method = RequestMethod.POST)
    public JSONObject updateWebSite(@RequestBody WebSite webSite) {
        Integer result = webSiteService.updateWebSite(webSite);
        JSONObject jsonObject = new JSONObject();
        if (result > 0) {
            jsonObject.put("code", "000000");
            jsonObject.put("message", "SUCCESS");
        } else {
            jsonObject.put("code", "999999");
            jsonObject.put("message", "FAIL");
        }
        List<WebSite> list = webSiteService.getAllWebSite();
        return jsonObject;
    }

    @RequestMapping(path = "/v1/insert", method = RequestMethod.POST)
    public JSONObject insertWebSite(@RequestBody WebSite webSite) {
        Integer result = webSiteService.insertWebSite(webSite);
        JSONObject jsonObject = new JSONObject();
        if (result > 0) {
            jsonObject.put("code", "000000");
            jsonObject.put("message", "SUCCESS");
        } else {
            jsonObject.put("code", "999999");
            jsonObject.put("message", "FAIL");
        }
        return jsonObject;
    }
    @RequestMapping(path = "/v1/{id}", method = RequestMethod.DELETE)
    public JSONObject deleteWebSite(@PathVariable int id) {
        Integer result = webSiteService.deleteWebSite(id);
        JSONObject jsonObject = new JSONObject();
        if (result > 0) {
            jsonObject.put("code", "000000");
            jsonObject.put("message", "SUCCESS");
        } else {
            jsonObject.put("code", "999999");
            jsonObject.put("message", "FAIL");
        }
        return jsonObject;
    }
}

4、启动类

@SpringBootApplication
//表示要扫描的Mapper的目录路径是在com.riant.mybatis.dao下的。
@MapperScan("com.riant.mybatis.dao")
public class MybatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisApplication.class, args);
    }
}

五、测试

1、查询单条记录

2、查询多条记录

3、分页查询

4、插入记录

5、更新记录

6、删除记录