SpringBoot 2.x 数据库访问中间件MyBatis

SpringBoot 2.x 数据库访问中间件MyBatis

Mybatis 介绍

MyBatis是一款优秀的持久层框架（它的前身是apache的一个开源项目iBatis）,支持定制化SQL，存储过程以及高级映射。
MyBatis可以使用简单的XML或注解来配置和映射原生信息，将接口和java的POJOs(Plain Old Java Objects，普通的Java对象)映射成数据库中的记录。

MyBatis架构
接口层：接口层主要定义的是与数据库进行交互的方式
数据处理层：负责参数映射和动态SQL生成，生成后MyBatis执行SQL语句，并将返回的结果映射成自定义的类型。
框架支撑层：负责最基础的功能支撑，包括连接管理，事务管理，配置加载和缓存处理。

MyBatis工作流程

image.png

MyBatis 主要成员及结构

image.png

SpringBoot 整合MyBatis

SpringBoot 整合MyBatis和REST完成业务操作
代码在github上，地址：https://github.com/LaiHouWen/MyBatis-PageHelper
1.pom.xml导入依赖

        <!--MyBatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
      <!--web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mysql 依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>

2.yml配置文件

#配置mysql数据源
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 123456

#mybatis 配置
mybatis:
  # mybatis 配置文件路径，默认是resources目录
  config-location: classpath:mybatis/mybatis-config.xml
  # mybatis 下的 mapper 映射文件路径，默认是resources目录
  mapper-locations: classpath:mybatis/mapping/*.xml
  #实体类包路劲
  type-aliases-package: com.xxx.xxx.entity

#打印 sql 语句;com.xxx.xxx.xxx包路劲
logging:
  level:
    #com.xxx.xxx.xxx包路劲
    com.learn.xxx.xxx: debug

3. 实体类

@Data
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class User {
    private Integer id;
    private String name;
    private Double score;
    private Integer age;
}

Product

@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Product {
    private int id;
    private String name;
    private String description;
    private double price;
    private String pic;
    private int uid;
}

4.dao接口

/**
 * 定义查询接口
 */
public interface UserDao {
    /**
     * xml上写sql
     * @return
     */
    List<User> findAll();
    User findById(Integer id);
    /**
     * 注解上写sql
     */
    //模糊查询
    @Select("select id,username,password,name from users where name like #{name}")
    List<User> findByName(String name);

    @Select("select id,username,password,name from users where name like '%${name}%'")
    List<User> findByName_1(String name);

}

UsersRepository

public interface UsersRepository {

    @Select("select id,username,password,name from users")
    List<User> findAll();

    @Select("select count(1) from users where username=#{users.username} and password=#{users.password}")
    int login(@Param("users") User user);

    /**
     * 一对多查询
     * @param id
     * @return
     */
    @Select("select * from users where id=#{id}")
    @Results({
            @Result(property = "products",column = "id",
                    many =@Many(
                            select = "com.xxx.xxxx.repository.ProductRepository.findProductByUid"))
    })
    User findUserById(int id);

}

ProductRepository

public interface ProductRepository {

    @Select("select * from product")
    List<Product> findAll();

    @Select("select * from product where id=#{id}")
    Product findProductById(int id);

    @Select("select * from product where uid=#{uid}")
    List<Product> findProductByUid(int uid);

    @Select("select * from product where uid=(select id from users where username=#{username})")
    List<Product> findProductByUidName(String username);

    @Update("update product set name=#{product.name}," +
            "description=#{product.description},price=#{product.price}," +
            "pic=#{product.pic} where id=#{product.id}")
    void updataProduct(@Param("product") Product product);

    @Delete("delete from product where id=#{id}")
    void deleteProductById(int id);

}

5.web 配置 WebMvcConfigure

/**
 * 过滤页面，直接访问
 */
@Configuration
public class WebMvcConfigure implements WebMvcConfigurer {

    /**
     * 静态页面直接访问
     * 过滤
     * @param registry
     */
    @Override
    public void addViewControllers(ViewControllerRegistry registry) {
        // 访问的url 是 /  的，跳转到login.html
        //http://localhost:8081/
        registry.addViewController("/").setViewName("login.html");
    }

    /**
     * 拦截器
     * @param registry
     */
    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(new LoginHandlerIntercept()).addPathPatterns("/**")
                .excludePathPatterns("/","/login.html",
                "/user/login","/css/**","/js/**","/img/**");
    }
   
}

自定义请求拦截器
LoginHandlerIntercept

/**
 *
 * 登录拦截器
 *
 */
public class LoginHandlerIntercept implements HandlerInterceptor {

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        Object username =request.getSession().getAttribute("username");
        if (username !=null) {
            return true;
        }else {
            request.setAttribute("login_error","请先登录");
            //跳转页 /
            request.getRequestDispatcher("/").forward(request,response);
            return false;
        }
    }

    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView) throws Exception {

    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {

    }
}

Mybatis PageHelper 分页实现 案例

1 .application.yml

server:
  port: 8081
#
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/springboot_mybatis?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: 123456
  thymeleaf:
    cache: false

#mybatis
mybatis:
  mapper-locations: classpath:/mapping/*.xml
  type-aliases-package: com.learn.pagehelper.entity

#pagehelper
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true
  params: count=countSql

2. pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--MyBatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>
<!--mybatis集成pageHelper示例-->
<!--https://github.com/abel533/MyBatis-Spring-Boot-->
<!--pagehelper 分页查询-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.12</version>
</dependency>
<!--通用mapper-->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</version>
</dependency>
<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

3 .Emp实体类

@Data
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class Emp {
    private int empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Integer sal;
    private Integer comm;
    private int deptno;
}

4 .EmpDao 映射接口类

/**
 *
 */
@Repository
public interface EmpDao {

    @Select(value = "select empno,ename,job,sal,mgr,comm,deptno from emp")
    List<Emp> findAllEmp();

}

5 . EmpController

@Controller
public class EmpController {

    @Resource
    private EmpDao empDao;

    @RequestMapping(value = "/",method = RequestMethod.GET)
    public String getList(){
        return "list1.html";
    }


    @RequestMapping(value = "/emp/findallemp",method = RequestMethod.GET)
    public String findAllEmp(ModelMap modelMap, @RequestParam(defaultValue = "0",value = "pageNum") int pageNum,
                          @RequestParam(defaultValue = "sal",value = "type") String type){

//        PageHelper.startPage(pageNum,5);
        //第三个参数：按字段排序
        PageHelper.startPage(pageNum,5,type+" desc");
        List<Emp> emps = empDao.findAllEmp();

        PageInfo<Emp> pages = new PageInfo<>(emps);

        System.out.println("当前页数："+pages.getPageNum());

        System.out.println("总页数："+pages.getPages());

        System.out.println("总数量："+pages.getTotal());

        System.out.println("所有数据："+pages.getList());

        System.out.println("是否有上一页："+pages.isHasPreviousPage());

        System.out.println("是否有下一页："+pages.isHasNextPage());

        System.out.println("当前页的上一页："+(pages.isHasPreviousPage()?pages.getPrePage():"0"));

        System.out.println("当前页的下一页："+(pages.isHasNextPage()?pages.getNextPage():pages.getPages()-1));

        modelMap.addAttribute("pages",pages);
        return "/list1.html";
    }

    @ResponseBody
    @RequestMapping(value = "/emp/findallemps",method = RequestMethod.GET)
    public PageInfo<Emp> findAllEmps(@RequestParam(defaultValue = "0",value = "pageNum") int pageNum,
                             @RequestParam("type") String type){

        if (StringUtils.isEmpty(type)){
            PageHelper.startPage(pageNum,5);}
        //第三个参数：按字段排序
        else {
            PageHelper.startPage(pageNum,5,type);//desc asc
        }
        List<Emp> emps = empDao.findAllEmp();

        PageInfo<Emp> pages = new PageInfo<>(emps);

        System.out.println("当前页数："+pages.getPageNum());

        System.out.println("总页数："+pages.getPages());

        System.out.println("总数量："+pages.getTotal());

        System.out.println("所有数据："+pages.getList());

        System.out.println("是否有上一页："+pages.isHasPreviousPage());

        System.out.println("是否有下一页："+pages.isHasNextPage());

        System.out.println("当前页的上一页："+(pages.isHasPreviousPage()?pages.getPrePage():"0"));

        System.out.println("当前页的下一页："+(pages.isHasNextPage()?pages.getNextPage():pages.getPages()-1));

        return pages;
    }
}

6.list1.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <!--jQuery文件，务必在bootstrap.main.js之前引入-->
    <script src="https://cdn.staticfile.org/jquery/3.2.1/jquery.js"></script>
    <!--新 bootstrap 4 核心 css文件  -->
    <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.1.0/css/bootstrap.min.css">

    <script>
        //初始化
        $(function () {
            empSort("");
        });
        var data_page;
        var data_type;
        var sortType="";
        function empSort(type) {
            var pagenums = 0;
            if(data_page!=null && type=='top'){//首页
                pagenums = 0;
                data_type="";
                sortType="";
            }else if (data_page!=null &&  type=='pre'){//上一页
                if (data_page.hasPreviousPage){
                    pagenums=data_page.prePage;
                }else {
                    return;
                }
            }else if (data_page!=null && type=='next'){//下一页
                if ( data_page.hasNextPage){
                    pagenums=data_page.nextPage;
                }else {
                    return;
                }
            }else if (data_page!=null && type=='end'){//尾页
                pagenums=data_page.pages;
            }else if(type==""){
                data_type="";
                sortType="";
            }else {
                data_type=type;
                //点击字段会升序或者降序
                if (type==data_type){//+" desc"
                    if (sortType==" desc"){
                        sortType=" asc";
                    }else{
                        sortType=" desc";
                    }
                }else {
                    sortType=" desc";
                }
            }
            getData(pagenums,data_type+sortType);
        };

        function getData(pageNum,sortType) {
            $.ajax({
                type: "GET",
                url: "/emp/findallemps",
                data: {"pageNum":pageNum,"type":sortType},
                dataType: "json",
                success: function (data) {
                    console.log(data)
                    var str = "";//把数据组装起来
                    //清空table中的html
                    $("#tableText").html("");

                    $("#table_pagenum").html(data.pageNum);
                    $("#table_pages").html(data.pages);
                    $("#table_pagetotal").html(data.total);
                    data_page=data;

                    for (var i = 0; i < data.list.length; i++) {
                        str = "<tr>" +
                            "<td>"+data.list[i].empno + "</td>" +
                            "<td>"+data.list[i].ename + "</td>" +
                            "<td>"+data.list[i].job + "</td>" +
                            "<td>"+data.list[i].sal + "</td>" +
                            "<td>"+data.list[i].mgr + "</td>" +
                            "<td>"+data.list[i].comm + "</td>" +
                            "<td>"+data.list[i].deptno + "</td>" +
                            "</tr>";
                        $("#tableText").append(str);
                        //  $("#tableText").html(str);//把拼好的样式填到指定的位置，一个Ajax的表格刷新功能就完成了
                    }
                }
            });
        }
    </script>
</head>
<body>
<div align="center">
    <table border="1" cellpadding="0" cellspacing="0" width="60%">
        <thead>
            <tr>
                <th onclick="empSort('empno')">empno</th>
                <th onclick="empSort('ename')">ename</th>
                <th onclick="empSort('job')">job</th>
                <th onclick="empSort('sal')">sal</th>
                <th onclick="empSort('mgr')">mgr</th>
                <th onclick="empSort('comm')">comm</th>
                <th onclick="empSort('deptno')">deptno</th>
            </tr>
        </thead>

        <tbody id="tableText">
<!--            <tr th:each="emp:${pages.getList()}">-->
<!--                <th th:text="${emp.empno}">1001</th>-->
<!--                <th th:text="${emp.ename}">zhangsan</th>-->
<!--                <th th:text="${emp.job}">clerk</th>-->
<!--                <th th:text="${emp.sal}">3000</th>-->
<!--                <th th:text="${emp.mgr}">1001</th>-->
<!--                <th th:text="${emp.comm}">300</th>-->
<!--                <th th:text="${emp.deptno}">10</th>-->
<!--            </tr>-->
        </tbody>

    </table>
    <p id="table_count">当前 <span id="table_pagenum">1</span> 页,总
        <span id="table_pages"  >10</span>页，共
        <span id="table_pagetotal"  >100</span> 条记录
    </p>

<!--    <a onclick="empSort('top')">首页</a>-->
<!--    <a onclick="empSort('pre')">上一页</a>-->
<!--    <a onclick="empSort('next')">下一页</a>-->
<!--    <a onclick="empSort('end')">尾页</a>-->
    <p>
        <button onclick="empSort('top')">首页</button>
        <button onclick="empSort('pre')">上一页</button>
        <button onclick="empSort('next')">下一页</button>
        <button onclick="empSort('end')">尾页</button>
    </p>


<!--    <p id="table_count">当前 <span id="table_pagenum" th:text="${pages.getPageNum()}">1</span> 页,总-->
<!--        <span id="table_pages" th:text="${pages.getPages()}">10</span>页，共-->
<!--        <span id="table_pagetotal" th:text="${pages.getTotal()}">100</span> 条记录-->
<!--    </p>-->
<!--    <a th:href="@{/emp/findallemp}">首页</a>-->
<!--    <a th:href="@{/emp/findallemp(pageNum=${pages.isHasPreviousPage()}?${pages.getPrePage()}:0)}">上一页</a>-->
<!--    <a th:href="@{/emp/findallemp(pageNum=${pages.isHasNextPage()}?${pages.getNextPage()}:${pages.getPages()})}">下一页</a>-->
<!--    <a th:href="@{/emp/findallemp(pageNum=${pages.getPages()})}">尾页</a>-->
</div>
</body>
</html>