整合MyBatis

整合MyBatis

官方文档：http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/

Maven仓库地址：https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter/2.1.2

整合测试

1、导入 MyBatis 所需要的依赖

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

2、配置数据库连接信息（不变）

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdata?useUnicode=true&characterEncoding=utf-8&useSSL=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

3、测试数据库是否连接成功！

4、创建实体类，导入 Lombok！

User.java

package com.jia.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data                   // 注在类上，提供类的get、set、equals、hashCode、canEqual、toString方法
@AllArgsConstructor     // 注在类上，提供类的全参构造
@NoArgsConstructor      // 注在类上，提供类的无参构造
public class User {
    private Integer id;
    private String name;
    private String pwd;
}

5、创建mapper目录以及对应的 Mapper 接口

UserMapper.java

package com.jia.mapper;

import com.jia.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
//@Repository
public interface UserMapper {

    List<User> queryUserList();

    User queryUserById(int id);

    int addUser(User user);

    int updateUser(User user);

    int deleteUser(int id);
}

6、对应的Mapper映射文件

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.jia.mapper.UserMapper">

    <select id="queryUserList" resultType="User">
        SELECT * from  `user`
    </select>

    <select id="queryUserById" resultType="User">
        SELECT * from  `user` where id = #{id}
    </select>

    <insert id="addUser" parameterType="User">
        insert into user (id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>

    <update id="updateUser" parameterType="User">
        update user set name = #{name},pwd = #{pwd} where id = #{id}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id}
    </delete>

</mapper>

7、maven配置资源过滤问

在 application.properties 和 mybatis-config.xml 中配置

mybatis.config-location = classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations= classpath:mybatis/mapper/*.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <package name="com.jia.pojo"/>
    </typeAliases>
</configuration>

8、编写 UserController 进行测试！

package com.jia.controller;

import com.jia.mapper.UserMapper;
import com.jia.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/queryUserList")
    public List<User> queryUserList() {
        return userMapper.queryUserList();
    }

    @RequestMapping("/queryUserById")
    public User queryUserById(int id) {
        return userMapper.queryUserById(id);
    }

    @RequestMapping("/addUser")
    public int addUser() {
        return userMapper.addUser(new User(4,"123","456"));
    }

    @RequestMapping("/updateUser")
    public int updateUser() {
        return userMapper.updateUser(new User(4,"111","222"));
    }

    @RequestMapping("/deleteUser")
    public int deleteUser(int id) {
        return userMapper.deleteUser(id);
    }
}

启动项目访问进行测试！

配置分页功能

增加分页支持

添加 pagehelper 和 thymleaf 依赖

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

设置方言

pagehelper.helper-dialect=mysql

创建分页配置类

@Configuration
public class PageHelperConfig {
    @Bean
    public PageHelper pageHelper(){
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        //1.offsetAsPageNum:设置为true时，会将RowBounds第一个参数offset当成pageNum页码使用.
        p.setProperty("offsetAsPageNum", "true");
        //2.rowBoundsWithCount:设置为true时，使用RowBounds分页会进行count查询.
        p.setProperty("rowBoundsWithCount", "true");
        //3.reasonable：启用合理化时，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页。
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

实现分页控制器

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.jia.mapper.UserMapper;
import com.jia.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;

@Controller
public class UserListController {
    @Autowired
    UserMapper userMapper;

    @Autowired
    @Qualifier("pageH")
    PageHelper pageHelper;

    @RequestMapping("/listall")
    public String listCategory(Model m, @RequestParam(value = "start", defaultValue = "0") int start, @RequestParam(value = "size", defaultValue = "5") int size) {
        //1. 在参数里接受当前是第几页 start ，以及每页显示多少条数据 size。 默认值分别是0和5。
        //2. 根据start,size进行分页，并且设置id 倒排序
        pageHelper.startPage(start, size, "id desc");
        //3. 因为PageHelper的作用，这里就会返回当前分页的集合了
        List<User> cs = userMapper.queryAll();
        //4. 根据返回的集合，创建PageInfo对象
        PageInfo<User> page = new PageInfo<>(cs);
        //5. 把PageInfo对象扔进model，以供后续显示
        m.addAttribute("page", page);
        //6. 跳转到list.html
        return "list";
    }

    @RequestMapping("/listall2")
    @ResponseBody
    //http://localhost:8080/listall2?pageNum=1&pageSize=2
    // 如果方法的参数不指定默认值，且请求地址也没有指定参数值，则项目运行时会报错。
    public Page<User> getUserList(@RequestParam(value = "pageNum", defaultValue = "0") int pageNum, @RequestParam(value = "pageSize", defaultValue = "5") int pageSize)
    {
        PageHelper.startPage(pageNum, pageSize);
        Page<User> userList = userMapper.getUserList();
        return userList;
    }
}

创建分页视图

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8"/>
    <title>1</title>
</head>
<body>

<div class="with:80%">

    <div th:each="u : ${page.list}">
        <span scope="row" th:text="${u.id}">id</span>
        <span th:text="${u.name}">name</span>
        <span th:text="${u.pwd}">name</span>
        <span th:text="${u.perms}">name</span>
    </div>

</div>

<div>
    <a th:href="@{listall?start=1}">[首页]</a>
    <a  th:if="${not page.IsFirstPage}" th:href="@{/listall(start=${page.pageNum-1})}">[上页]</a>
    <a  th:if="${not page.IsLastPage}" th:href="@{/listall(start=${page.pageNum+1})}">[下页]</a>
    <a th:href="@{/listall(start=${page.pages})}">[末页]</a>
    <div>当前页/总页数：<a th:text="${page.pageNum}" th:href="@{/listall(start=${page.pageNum})}"></a>
        /<a th:text="${page.pages}" th:href="@{/listall(start=${page.pages})}"></a></div>
</div>
</body>
</html>

Mapper

UserMapper.java

    List<User> queryAll();

    @Select("SELECT * from  `myuser` ")
    Page<User> getUserList();

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.jia.mapper.UserMapper">

    <select id="queryAll" resultType="User">
        SELECT * from  `myuser`
    </select>

</mapper>

SQL 日志