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

作者: kaixingdeshui | 来源:发表于2020-11-03 21:36 被阅读0次

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

实体类

@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

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>

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

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

SpringBoot 整合MyBatis

Mybatis PageHelper 分页实现案例

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

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

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

SpringBoot 整合MyBatis

Mybatis PageHelper 分页实现 案例

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Mybatis PageHelper 分页实现案例