Spring boot入門（二）：Spring boot整合MySql,Mybatis和PageHelper外掛

從入門到放棄的攻城獅發表於2019-02-28

上一篇文章，寫了如何搭建一個簡單的Spring boot專案，本篇是接著上一篇文章寫得：Spring boot入門：快速搭建Spring boot專案（一），主要是spring boot整合mybatis和pagehelper

關於mybatis和pagehelper的介紹，可以自行部落格，網上很多類似的部落格，這裡，我直接上程式碼和專案搭建教程。

1.配置檔案：

在配置檔案application.yml中配置MySql資料庫連線池和Mybatis掃描包以及PageHelper分頁外掛

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.tswc.edu.entity

mapper:
  mappers:
  - com.tswc.edu.util.MyMapper
  not-empty: false
  identity: MYSQL
  #identity: SQLSERVER

pagehelper:
  helperDialect: MYSQL
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/edu?useUnicode=true&characterEncoding=utf8
    username: root
    password: 123456
    initialSize: 1
    minIdle: 3
    maxActive: 20
    # 配置獲取連線等待超時的時間
    maxWait: 60000
    # 配置間隔多久才進行一次檢測，檢測需要關閉的空閒連線，單位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一個連線在池中最小生存的時間，單位是毫秒
    minEvictableIdleTimeMillis: 30000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 開啟PSCache，並且指定每個連線上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置監控統計攔截的filters，去掉後監控介面sql無法統計，'wall'用於防火牆
    filters: stat,wall,slf4j
    # 通過connectProperties屬性來開啟mergeSql功能；慢SQL記錄
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合併多個DruidDataSource的監控資料
    #useGlobalDataSourceStat: true
複製程式碼

同時，需要在pom.xml中新增相應的jar包依賴,如果存在jar包無法下載，可以手動下載，然後放到相應的jar包資料夾中

這裡面需要注意Spring boot和mybatis的jar包之間的版本，sping boot2+要求高版本，eg：spring boot2+不能識別pagehelper1.2以下的版本

<!--mybatis -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.4</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!--druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.28</version>
        </dependency>

        <!--mapper-->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.0</version>
        </dependency>
        <!--pagehelper,Spring boot2.0+只能裝置pagehelper1.2+-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
複製程式碼

2.程式碼部分

(1).更新入口主檔案，主要增加了一些註解，

此處需要注意@MapperSca的引用，這裡有2個可引用的包，分別是tk.mybatis.spring.annotation.MapperScan和org.mybatis.spring.annotation.MapperScan;

tk.mybatis.spring.annotation.MapperScan：為mubatis帶的mapper掃描檔案，org.mybatis.spring.annotation.MapperScan為spring自帶的掃描檔案，在低版本的mybatis上，2個包可以隨意使用，但是Spring boot2+後，增加了一些特性（建議閱讀spring boot2+的底層程式碼，加深理解），導致只能使用高版本的mtbatis，同時spring自帶的MapperScan也無法使用，所以使用sping boot2+後，需要引用高版本的mybatis，同時這裡只能使用mybatis的MapperScan的掃描包；spring在後期更新中，應該也會使其能夠用於boot中

@ServletComponentScan
@MapperScan(basePackages = "com.tswc.edu.mapper")
@SpringBootApplication
public class EduApplication {

    public static void main(String[] args) {
        SpringApplication.run(EduApplication.class, args);
    }

}
複製程式碼

如果在專案正常，啟動，但是在使用mybatis時，報此類錯誤：

後臺錯誤：無法獲取實體類對應的表名!

Spring boot入門（二）：Spring boot整合MySql,Mybatis和PageHelper外掛

2018-12-14 20:09:25.967 ERROR 29024 --- [nio-8085-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is tk.mybatis.mapper.MapperException: 無法獲取實體類com.tswc.edu.entity.Index對應的表名!] with root caus 2 
tk.mybatis.mapper.MapperException: 無法獲取實體類com.tswc.edu.entity.Index對應的表名!
    at tk.mybatis.mapper.mapperhelper.EntityHelper.getEntityTable(EntityHelper.java:69) ~[mapper-core-1.1.0.jar:na]
    at tk.mybatis.mapper.entity.Example.<init>(Example.java:103) ~[mapper-core-1.1.0.jar:na]
    at tk.mybatis.mapper.entity.Example.<init>(Example.java:88) ~[mapper-core-1.1.0.jar:na]
    at tk.mybatis.mapper.entity.Example.<init>(Example.java:78) ~[mapper-core-1.1.0.jar:na]
    at com.tswc.edu.service.IndexService.getList(IndexService.java:33) ~[classes/:na]
    at com.tswc.edu.controller.IndexController.list(IndexController.java:35) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_151]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_151]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_151]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:189) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:897) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882) ~[spring-webmvc-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.3.RELEASE.jar:5.1.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) ~[tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:791) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1417) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_151]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_151]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.13.jar:9.0.13]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
複製程式碼

前端錯誤是500

 Whitelabel Error Page
 This application has no explicit mapping for /error, so you are seeing this as a fallback.
 
 Fri Dec 14 20:09:25 CST 2018
 There was an unexpected error (type=Internal Server Error, status=500).
 ???????com.tswc.edu.entity.Index?????!
複製程式碼

那麼，就是Mapper掃描包使用有誤

(2).通用介面mapper：此介面不能被掃描到，否則會報錯，可以在application.yml中，排除此包的掃描

 public interface MyMapper<T> extends Mapper<T> {
     //TODO
     //FIXME 特別注意，該介面不能被掃描到，否則會出錯
 }
複製程式碼

(3).後臺程式碼，控制村層>服務層>介面

@Controller
public class IndexController {
    @Autowired
    private IndexService indexService;

    @RequestMapping(value = "/index")
    public void list(ModelMap modelMap) {
        List<Index> list = indexService.getList();
        modelMap.put("list", list);
    }
}
複製程式碼

@Service
public class IndexService {
    @Autowired
    private IndexMapper indexMapper;

    public List<Index> getList() {
        Example example = new Example(Index.class);
        example.setTableName("index_test");
        Criteria criteria = example.createCriteria();
        criteria.andEqualTo("age", 20);
        return indexMapper.selectByExample(example);
    }
}
複製程式碼

 public interface IndexMapper extends MyMapper<Index> {
 }
複製程式碼

@Table(name = "index_test")
public class Index implements Serializable, IDynamicTableName {
    private static final long serialVersionUID = -8709793124943394842L;

    @Transient
    private String dynamicTableName;
    private String name;
    private Integer age;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String getDynamicTableName() {
        return null;
    }
}
複製程式碼

最後一個類為實體類，建議在建立實體類的時候，統一加上@Table的註解，方便後期Mybatis的使用，同時也提高了程式碼的規範性。當然，如果我們使用Mybatis的程式碼生成器，那麼均會自動加上註解（程式碼生成器在後期的部落格中會講到）

(4).前端ftl程式碼

<!DOCTYPE html>
<html>
<head lang="en">
    <title>Spring Boot Demo - FreeMarker</title>
    <link href="/css/index.css" rel="stylesheet" />
</head>
<body>
    <table>
        <#list list as user>
        <tr>
            <td>第${user_index+1}個使用者</td>
            <td>使用者名稱：${user.name}</td>
            <td>年  齡: ${user.age}</td>
        </tr>
        </#list>
    </table>
</body>
</html>
複製程式碼

(5).新增mybatis的空配置檔案，由於spring boot中，資料庫是通過配置檔案的形式，所以此檔案是空的，但是沒有此檔案，mybatis就無法通過；當然，也可以通過在此檔案作用配置資料庫連線池的方式連線資料（已過時）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD SQL MAP Config 3.1//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <settings>
    <setting name="callSettersOnNulls" value="true"/>
  </settings>
</configuration>
複製程式碼

(6).資料庫如下：新增了幾條測試資料：

(7).啟動專案，並訪問http://localhost:8085/index，結果如下：

最終，專案程式碼的機構，如下：

Spring boot學習（五）Spring boot整合Mybatis Generator以及PageHelper
2019-01-20
Spring BootMyBatis
Spring Boot 整合 Mybatis 和 MySQL
2017-06-01
Spring BootMyBatisMySql
Spring-Boot整合通用PageHelper外掛遇到的問題
2018-09-05
Springboot
spring boot（二）整合mybatis plus+ 分頁外掛 + 程式碼生成
2021-07-28
Spring BootMyBatis
Spring Boot 整合 MyBatis
2022-05-13
Spring BootMyBatis
spring boot 整合mybatis
2017-12-14
Spring BootMyBatis
Spring boot學習（三） Spring boot整合mybatis
2019-01-17
Spring BootMyBatis
Spring Boot（六）整合 MyBatis 操作 MySQL 8
2018-09-12
Spring BootMyBatisMySql
Spring boot 六整合 MyBatis
2017-11-07
Spring BootMyBatis
Spring Boot乾貨系列：（十一）資料儲存篇-Spring Boot整合Mybatis通用Mapper外掛
2017-12-20
Spring BootMyBatisAPP
Spring Boot整合hessian入門
2019-01-19
Spring Boot
Spring Boot系列（三）：Spring Boot整合Mybatis原始碼解析
2020-08-20
Spring BootMyBatis原始碼
Spring Boot 整合 Spring Security 入門案例教程
2020-04-21
Spring Boot
Spring Boot：Spring Boot配置Mybatis
2021-09-09
Spring BootMyBatis
Spring Boot 整合 MyBatis和 SQL Server實踐
2018-12-17
Spring BootMyBatisSQLServer
【Spring Boot】快速整合Mybatis-Plus
2023-02-07
Spring BootMyBatis
Spring Boot系列(一)：Spring Boot 入門篇
2019-04-26
Spring Boot
Spring Boot入門
2021-06-08
Spring Boot
Spring Boot入門系列（十六）使用pagehelper實現分頁功能
2020-06-28
Spring Boot
Spring Boot入門(一)：搭建Spring Boot專案
2019-10-25
Spring Boot
spring boot 二整合 FastJson
2017-11-03
Spring BootASTJSON
Spring Boot 2和JPA入門
2019-01-16
Spring Boot
小代學Spring Boot之整合MyBatis
2019-07-19
Spring BootMyBatis
spring-boot mybatis 連線MySQL
2017-09-04
SpringbootMyBatisMySql
Spring Boot Dubbo 入門
2020-04-19
Spring Boot
spring boot快速入門
2021-11-09
Spring Boot
Spring Boot 2.0(八)：Spring Boot 整合 Memcached
2018-09-04
Spring Boot
Spring Boot(十八)：使用 Spring Boot 整合 FastDFS
2018-01-16
Spring BootAST
Spring boot入門（一）：快速搭建Spring boot專案
2019-02-28
Spring Boot
GraphQL SPQR和Spring Boot入門 | baeldung
2022-04-06
Spring Boot
Spring Boot + Mybatis + Spring MVC環境配置（二）：Mybatis Generator配置
2018-08-18
Spring BootMyBatisMVC
Spring Boot整合MyBatis實現通用Mapper
2018-08-22
Spring BootMyBatisAPP
spring-boot快速入門學習筆記-整合JPA mybatis rabbitmq mongodb redis
2019-03-04
Springboot筆記MyBatisMQMongoDBRedis
Spring Boot整合Spring Batch
2019-05-09
Spring BootBAT
Spring Boot整合Spring Security
2018-12-18
Spring Boot
Spring Boot整合Spring Aop
2020-12-14
Spring Boot
Spring Boot入門(七)：使用MyBatis訪問MySql資料庫(xml方式)
2019-06-14
Spring BootMyBatisMySql資料庫XML
Spring Boot （一）快速入門
2019-01-19
Spring Boot

Spring boot入門（二）：Spring boot整合MySql,Mybatis和PageHelper外掛

1.配置檔案：

2.程式碼部分

相關文章