Spring Boot集成MySql、MyBatis和PageHelper插件

Joe.Ye • 2023-04-01 • MyBatis, Spring Boot

配置文件

在配置文件application.yml中配置MySql数据库连接池和Mybatis扫描包以及PageHelper分页插件

server:
  port: 8080
  servlet:
    context-path: /
spring:
#  mvc:
#    view:
#      prefix: /WEB-INF/views/
#      suffix: .jsp
  freemarker:
    template-loader-path: classpath:/templates/
  datasource:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8
      username: root
      password: ubuntu
      initialSize: 5
      minIdle: 5
      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,log4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 合并多个DruidDataSource的监控数据
      useGlobalDataSourceStat: true

mybatis:
  # 指定映射文件
  mapper-locations: classpath:mapper/*.xml
  # 指定Bean所在包
  type-aliases-package: cn.appblog.springboot.dao.entity

mapper:
  # 多个接口时逗号隔开
  mappers: cn.appblog.common.mybatis.BaseMapper
  not-empty: false
  identity: MYSQL
  #identity: SQLSERVER

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

同时，需要在pom.xml中添加相应的jar包依赖

<!-- mysql -->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<!-- mybatis -->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.0</version>
</dependency>

<!--druid -->
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
<!--<dependency>-->
    <!--<groupId>com.alibaba</groupId>-->
    <!--<artifactId>druid-spring-boot-starter</artifactId>-->
    <!--<version>1.1.10</version>-->
<!--</dependency>-->
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
    <scope>compile</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.7.26</version>
</dependency>

<!-- https://mvnrepository.com/artifact/tk.mybatis/mapper-spring-boot-starter -->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</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.10</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-freemarker -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-freemarker</artifactId>
    <version>2.1.3.RELEASE</version>
</dependency>

继承通用mapper

通用接口mapper：此接口不能被扫描到，否则会报错，可以在application.yml中，排除此包的扫描

Mapper3提供的全部的方法，可以查看Mapper3通用接口大全

package cn.appblog.common.mybatis;

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

/**
 * 继承自己的Mapper
 * @param <T>
 */
public interface BaseMapper<T> extends Mapper<T>, MySqlMapper<T> {
    //FIXME 特别注意，该接口不能被扫描到，否则会出错
}

更新入口主文件并设置dao路径

此处需要注意@MapperSca的引用，这里有2个可引用的包，分别是tk.mybatis.spring.annotation.MapperScan;和org.mybatis.spring.annotation.MapperScan;。
tk.mybatis.spring.annotation.MapperScan为Mybatis带的mapper扫描文件，org.mybatis.spring.annotation.MapperScan为Spring自带的扫描文件，在低版本的Mybatis上，2个包可以随意使用，但是Spring Boot 2+后，增加了一些特性，导致只能使用高版本的Mybatis，同时Spring自带的MapperScan也无法使用，所以使用Spring Boot 2+后，需要引用高版本的Mybatis，同时这里只能使用Mybatis的MapperScan的扫描包。Spring在后期更新中，应该也会使其能够用于Spring Boot中。

在Application中，以注解的形式设置扫描的basepackage，可以设置多个，dao可在不同路径。

@MapperScan(basePackages = {"cn.appblog.*.dao.*", "cn.appblog.*.dao"}, markerInterface = BaseMapper.class)
import cn.appblog.common.mybatis.BaseMapper;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;
import tk.mybatis.spring.annotation.MapperScan;

@ServletComponentScan
@MapperScan(basePackages = {"cn.appblog.*.dao.*", "cn.appblog.*.dao"}, markerInterface = BaseMapper.class)
@SpringBootApplication
public class SpringbootApplication {

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

}

后台代码，控制层->服务层->接口

@Controller
@RequestMapping(value = "/test")
public class TestController {
    @Autowired
    private TestService testService;

    @RequestMapping(value = "/index")
    public String list(ModelMap modelMap) {
    //public ModelAndView list(ModelMap modelMap) {
        List<Test> list = testService.getList();
        modelMap.put("list", list);
        return "test/index";
        //return new ModelAndView("test/index", modelMap);
    }
}
@Service
public class TestService {
    @Autowired
    private TestMapper testMapper;

    public List<Test> getList() {
        Example example = new Example(Test.class);
        example.setTableName("user");
        Example.Criteria criteria = example.createCriteria();
        criteria.andBetween("age", 10, 30);
        return testMapper.selectByExample(example);
    }
}
public interface TestMapper extends BaseMapper<Test> {

}
@Table(name = "user")
public class Test 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的代码生成器，那么均会自动加上注解。

前端ftl代码

application.yml配置：

spring:
  freemarker:
    template-loader-path: classpath:/templates/

pom.xml配置：

<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-freemarker -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-freemarker</artifactId>
    <version>2.1.3.RELEASE</version>
</dependency>
<!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>

项目启动

访问：http://localhost:8080/test/index

注：正常启动即可，一定不能使用spring-boot:run启动

版权声明：
作者：Joe.Ye
链接：https://www.appblog.cn/index.php/2023/04/01/spring-boot-integrate-mysql-mybatis-and-pagehelper-plugins/
来源：APP全栈技术分享
文章版权归作者所有，未经允许请勿转载。

THE END
分享
二维码
打赏
海报
Spring Boot集成MySql、MyBatis和PageHelper插件
配置文件 在配置文件application.yml中配置MySql数据库连接池和Mybatis扫描包以及PageHelper分页插件 server: port: 8080 servlet: context-path: / ……
<<上一篇
下一篇>>
文章目录
关闭
目 录