关于springboot的mybits重写数据库Bean/DataBase和开启二级缓存配置

一、重写Database进行数据库账号密码加密处理

1、引用（具体可参照：SpringBoot中使用MyBatis+Druid+Swagger）

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <!-- 注意，这里需要排除web自带的logging依赖-->
           <exclusions>
               <exclusion>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-starter-logging</artifactId>
               </exclusion>
           </exclusions>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        
        <!-- 重写数据库的DruidDataSource，和mybatis版本要关联 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!-- 补充排除的log4j -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j</artifactId>
            <version>1.3.8.RELEASE</version>
        </dependency>

 

2、补充.properties文件

 

# mysql信息配置(需要根据实际配置)
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3307/数据库?characterEncoding=UTF-8&allowMultiQueries=true&&useAffectedRows=true
spring.datasource.username=账号
spring.datasource.password=密码
# 连接池配置
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true

mybatis.type-aliases-package=com.namejr.Web_FMSystem.bean
mybatis.mapper-locations=classpath:/dao/*.xml

 

 

 

 

3、增加配置类

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.alibaba.druid.pool.DruidDataSource;

@Configuration
public class DatabaseConfig {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    
     @Value("${spring.datasource.initialSize}")
     private int initialSize;

     @Value("${spring.datasource.minIdle}")
     private int minIdle;

     @Value("${spring.datasource.maxActive}")
     private int maxActive;

     @Value("${spring.datasource.maxWait}")
     private int maxWait;

     @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
     private int timeBetweenEvictionRunsMillis;

     @Value("${spring.datasource.minEvictableIdleTimeMillis}")
     private int minEvictableIdleTimeMillis;

     @Value("${spring.datasource.validationQuery}")
     private String validationQuery;

     @Value("${spring.datasource.testWhileIdle}")
     private boolean testWhileIdle;

     @Value("${spring.datasource.testOnBorrow}")
     private boolean testOnBorrow;

     @Value("${spring.datasource.testOnReturn}")
     private boolean testOnReturn;

     @Value("${spring.datasource.poolPreparedStatements}")
     private boolean poolPreparedStatements;

     @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
     private int maxPoolPreparedStatementPerConnectionSize;

     @Value("${spring.datasource.filters}")
     private String filters;

     @Value("{spring.datasource.connectionProperties}")
     private String connectionProperties;
     
    @Bean
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);  // 链接地址
        datasource.setUsername(username);  // 用户名,这里可以做加密处理
        datasource.setPassword(password);  // 密码,这里可以做加密处理
        datasource.setDriverClassName(driverClassName);  // 驱动
        // 下面是连接池配置
        datasource.setInitialSize(initialSize);  // 初始化大小
        datasource.setMinIdle(minIdle);  // 最小
        datasource.setMaxActive(maxActive);  // 最大
        
        datasource.setMaxWait(maxWait);  //  配置获取连接等待超时的时间
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        
        // 配置一个连接在池中最小生存的时间，单位是毫秒
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        
        // 打开PSCache，并且指定每个连接上PSCache的大小
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        
        try {
            datasource.setFilters(filters);  // 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        } catch (Exception err) {
            System.out.println(err);
        }
        
        datasource.setConnectionProperties(connectionProperties);  // 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        return datasource;
    }
}

 

二、spring boot中mybatis的二级缓存

1、未开启二级缓存时的情况

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.2</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>

  <groupId>com.namejr</groupId>
  <artifactId>testSBMDemo</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>testSBMDemo</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.30</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.2.2</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>2.0.11</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
  </dependencies>
  <build>
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.properties</include>
          <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
      </resource>
      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>**/*.properties</include>
          <include>**/*.yml</include>
          <include>**/*.xml</include>
          <include>**/*.json</include>
          <include>**/*.exe</include>
        </includes>
        <filtering>false</filtering>
      </resource>
      <resource>
        <!-- 注册webapp目录为资源目录 -->
        <directory>src/main/webapp</directory>
        <targetPath>META-INF/resources</targetPath>
        <includes>
          <include>**/**</include>
        </includes>
      </resource>
    </resources>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <configuration>
          <includeSystemScope>true</includeSystemScope>
          <jvmArguments>-Dfile.encoding=UTF-8</jvmArguments>
        </configuration>
      </plugin>
      <plugin>
        <!-- 配置jar包打包工具 -->
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-war-plugin</artifactId>
        <configuration>
          <webResources>
            <resource>
              <directory>${project.basedir}/libs</directory>
              <targetPath>WEB-INF/lib</targetPath>
              <includes>
                <include>**/*.jar</include>
              </includes>
            </resource>
          </webResources>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

 

StartApplication.java

package com.namejr;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;

@SpringBootApplication
@EnableWebMvc
@MapperScan({ "com.namejr.controller", "com.namejr.dao" })
public class StartApplication extends SpringBootServletInitializer {
    public static void main(String[] args) {
        SpringApplication.run(StartApplication.class, args);
    }
}

 

PublicController.java

package com.namejr.controller;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.namejr.dao.PublicDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import com.alibaba.fastjson.JSONObject;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping(value = "/api/Public")
public class PublicController {

    @Autowired
    private PublicDao pDao;

    @RequestMapping(value = "/getDBInfos", method = RequestMethod.GET,produces = "application/json;charset=UTF-8")
    public void getDBInfos() {
        List<?> tempQuesInfos =pDao.getQuesInfos();
        if(tempQuesInfos!=null&&!tempQuesInfos.isEmpty()){
            List<Map<String,String>> quesInfos=JSONObject.parseObject(JSON.toJSONString(tempQuesInfos),new TypeReference<List<Map<String,String>>>(){});
            quesInfos.forEach((Map<String,String> tempAAA)->{
                System.out.println(tempAAA.get("res_guid"));
            });
        }
        System.out.println("First End...");
        List<?> tempResGuidInfos =pDao.getQuesInfos();
        if(tempResGuidInfos!=null&&!tempResGuidInfos.isEmpty()){
            List<Map<String,String>> quesInfos=JSONObject.parseObject(JSON.toJSONString(tempResGuidInfos),new TypeReference<List<Map<String,String>>>(){});
            quesInfos.forEach((Map<String,String> tempAAA)->{
                System.out.println(tempAAA.get("res_guid"));
            });
        }
        System.out.println("Two End...");
    }
}

 

PublicDao.java

package com.namejr.dao;

import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface PublicDao {
    // 获取试题信息
    List<?> getQuesInfos();
}

 

resources\dao\PublicDao.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.namejr.dao.PublicDao">
    <resultMap id="publicHashMapf" type="java.util.HashMap"></resultMap>
    <!-- 获取试题信息 -->
    <select id="getQuesInfos" resultMap="publicHashMapf">
        select id,res_guid from ques_resource limit 2;
    </select>
</mapper>

 

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/lgdb_tempdemo?characterEncoding=UTF-8&allowMultiQueries=true&&useAffectedRows=true&allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username=localhost
spring.datasource.password=000000

mybatis.type-aliases-package=com.namejr.bean
mybatis.mapper-locations=classpath:/dao/*.xml

logging.config=classpath:logback-spring.xml

# 查看mybatis执行的语句，格式："logging.level."+dao接口的包路径+"=debug"
logging.level.com.namejr.dao=debug

调用api/Public/getDBInfos执行结果：(因为不存在二级缓存，所以会访问两次数据库/执行两次SQL)

2024-03-20 13:51:36.070 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : ==>  Preparing: select id,res_guid from ques_resource limit 2;
2024-03-20 13:51:36.104 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : ==> Parameters: 
2024-03-20 13:51:36.153 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : <==      Total: 2
dt-c304326b1eb54e8594c5cc35f76a04ae
dt-c63b9db676044b9fb23b97caddec840f
First End...
2024-03-20 13:51:36.220 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : ==>  Preparing: select id,res_guid from ques_resource limit 2;
2024-03-20 13:51:36.220 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : ==> Parameters: 
2024-03-20 13:51:36.237 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : <==      Total: 2
dt-c304326b1eb54e8594c5cc35f76a04ae
dt-c63b9db676044b9fb23b97caddec840f
Two End...

 

2、在“1、未开启二级缓存时的清空”基础上开启二级缓存，变动文件如下：

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/lgdb_tempdemo?characterEncoding=UTF-8&allowMultiQueries=true&&useAffectedRows=true&allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username=localhost
spring.datasource.password=000000

mybatis.type-aliases-package=com.namejr.bean 
mybatis.mapper-locations=classpath:/dao/*.xml

# 开启二级缓存
mybatis.configuration.cache-enabled=true

logging.config=classpath:logback-spring.xml

# 查看mybatis执行的语句，格式："logging.level."+dao接口的包路径+"=debug"
logging.level.com.namejr.dao=debug

resources\dao\PublicDao.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.namejr.dao.PublicDao">
　　<!-- 开启二级缓存 -->
    <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
    <resultMap id="publicHashMapf" type="java.util.HashMap"></resultMap>
    <!-- 获取试题信息 -->
    <select id="getQuesInfos" resultMap="publicHashMapf">
        select id,res_guid from ques_resource limit 2;
    </select>
</mapper>

在上面的XML配置中，<cache>元素定义了缓存的策略，eviction是淘汰策略，flushInterval是刷新间隔，size是缓存大小，readOnly表示是否只读。

注：按照网上的教程，如果resultMap="publicHashMapf"返回的时泛型，即resultType="XXXXModel"那么这个泛型必须经过序列化，如：

public class demoModel implements Serializable {
  // 实体类的属性和方法
}

 

 

调用api/Public/getDBInfos执行结果：(因为存在二级缓存，所以会访问一次数据库/执行一次SQL，下一次直接读取缓存)

2024-03-20 13:56:51.437 DEBUG -- [nio-8080-exec-2] c.namejr.dao.PublicDao  : Cache Hit Ratio [com.namejr.dao.PublicDao]: 0.0
2024-03-20 13:56:51.439  INFO -- [nio-8080-exec-2] c.z.h.HikariDataSource  : HikariPool-1 - Starting...
2024-03-20 13:56:51.588  INFO -- [nio-8080-exec-2] c.z.h.HikariDataSource  : HikariPool-1 - Start completed.
2024-03-20 13:56:51.588 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : ==>  Preparing: select id,res_guid from ques_resource limit 2;
2024-03-20 13:56:51.606 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : ==> Parameters: 
2024-03-20 13:56:51.649 DEBUG -- [nio-8080-exec-2] c.n.d.P.getQuesInfos    : <==      Total: 2
dt-c304326b1eb54e8594c5cc35f76a04ae
dt-c63b9db676044b9fb23b97caddec840f
First End...
2024-03-20 13:56:51.721 DEBUG -- [nio-8080-exec-2] c.namejr.dao.PublicDao  : Cache Hit Ratio [com.namejr.dao.PublicDao]: 0.5
dt-c304326b1eb54e8594c5cc35f76a04ae
dt-c63b9db676044b9fb23b97caddec840f
Two End...

 

 

三、查看spring boot中Mybatis执行sql的语句信息

application.properties

# 查看mybatis执行的语句，格式："logging.level."+dao接口的包路径+"=debug"
logging.level.com.namejr.dao=debug

打印信息如下：
024-03-20 11:51:35.874 DEBUG -- [nio-8080-exec-1] c.n.d.P.getQuesInfos    : ==>  Preparing: select id,res_guid from ques_resource limit 2;
2024-03-20 11:51:35.895 DEBUG -- [nio-8080-exec-1] c.n.d.P.getQuesInfos    : ==> Parameters: 
2024-03-20 11:51:35.931 DEBUG -- [nio-8080-exec-1] c.n.d.P.getQuesInfos    : <==      Total: 2

 

 

\\