SpringBoot+MyBatis+Mysql+Durid实现动态多数据源

亲测有效，网上其他不是报错就是唠海嗑，所以自用在别人基础上改进报错，封装一套。

一、项目重点有:

(1)、SpringBoot+Mybatis+Mysql+Durid整合
(2)、错误后跳转到指定页面
(3)、多数据源动态切换
(4)、mybatis分页
(5)、durid监控
(6)、集成log4j2日志
(7)、通过mybatis拦截器,在控制台打印完整的sql

二、项目截图:

 

 

 

 

 

 三、SpringBoot+Mybatis+Mysql+Durid整合

(1)、application.yml:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
spring:
  dynamic-datasource:
    druid:
      # 连接池的配置信息
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1
      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
      # 配置DruidStatFilter
      web-stat-filter:
        enabled: true
        url-pattern: "/*"
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
      # 配置DruidStatViewServlet
      stat-view-servlet:
        url-pattern: "/druid/*"
        # IP白名单(没有配置或者为空，则允许所有访问)
        allow: 127.0.0.1
        # IP黑名单 (存在共同时，deny优先于allow)
        deny: 192.168.1.73
        #  禁用HTML页面上的“Reset All”功能
        reset-enable: false
        # 登录名
        login-username: admin
        # 登录密码
        login-password: 123456
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: true
        wall:
          config:
            multi-statement-allow: true
    druid-datasources:
      jwpd:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: 137972zc
      lkj:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        username: root
        password: 137972zc
  mvc:
    view:
      prefix: /WEB-INF/page/
      suffix: .jsp
server:
  port: 9090
logging:
  config: classpath:log4j2-spring-dev.xml
mybatis:
  type-aliases-package: com.base.springboot.entity

 说明:配置druid数据连接池,配置jdbc连接(两个数据源)

(2)、配置数据源(DataSourceProperties.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@Configuration
public class DataSourceProperties {
 
    @ConfigurationProperties(prefix = "spring.dynamic-datasource.druid-datasources.jwpd")
    @Bean(name = "JWPDDataSource")
    public DataSource JWPDDataSource(StandardEnvironment env){
        DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
        return common(env,druidDataSource);
    }
 
 
 
    @ConfigurationProperties(prefix = "spring.dynamic-datasource.druid-datasources.lkj")
    @Bean(name = "LKJDataSource")
    public DataSource LKJDataSource(StandardEnvironment env){
        DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
        return common(env,druidDataSource);
    }
 
    public DataSource common(StandardEnvironment env, DruidDataSource druidDataSource){
        Properties properties = new Properties();
        PropertySource<?> appProperties =  env.getPropertySources().get("applicationConfig: [classpath:/application.yml]");
        Map<String,Object> source = (Map<String, Object>) appProperties.getSource();
        properties.putAll(source);
        druidDataSource.configFromPropety(properties);
        return druidDataSource;
    }
}

 

说明:配置数据源,common(env,druidDataSource)方法,是为了继续设置为null的属性(durid配置属性,最大最小连接数、监控地址等等)

(3)、Spring和Mybatis的整合配置文件(MybatisConfig.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
@Configuration
public class MybatisConfig {
 
    //注入数据源JWPDDataSource
    @Autowired
    @Qualifier("JWPDDataSource")
    public DataSource JWPDDataSource;
 
    //注入数据源LKJDataSource
    @Autowired
    @Qualifier("LKJDataSource")
    public DataSource LKJDataSource;
 
 
    //声明动态数据源,默认值为JWPDDataSource
    @Bean("dynamicDataSource")
    @Primary
    public DynamicDataSource dynamicDataSource(){
        //动态数据源集合
        Map<Object, Object> targetDataSourcesMap = new HashMap<>(2);
        targetDataSourcesMap.put(DataSourceEnum.jwpd.name(),JWPDDataSource);
        targetDataSourcesMap.put(DataSourceEnum.lkj.name(),LKJDataSource);
        DynamicDataSource dynamicDataSource = new DynamicDataSource(targetDataSourcesMap,JWPDDataSource);
 
        return dynamicDataSource;
    }
 
 
    @Bean(name="pageHelper")
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        p.setProperty("dialect", "mysql");
        pageHelper.setProperties(p);
 
        return pageHelper;
    }
 
 
    //sql打印插件
    @Bean(name="fullSqlInterceptor")
    public FullSqlInterceptor fullSqlInterceptor(){
        return new FullSqlInterceptor();
    }
 
    /**
     * 声明sql会话
     * @return
     */
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("pageHelper")PageHelper pageHelper,@Qualifier("fullSqlInterceptor")FullSqlInterceptor fullSqlInterceptor) throws Exception{
        //声明sql会话工厂
        SqlSessionFactoryBean factoryBean=new SqlSessionFactoryBean();
        //设置数据源
        factoryBean.setDataSource(dynamicDataSource());
        //设置扫描mybatisXml的路径
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:**/dao/*/*.xml"));
        factoryBean.setPlugins(new Interceptor[] {pageHelper(), fullSqlInterceptor});//添加分页插件
        //返回sql会话
        return factoryBean.getObject();
    }
 
 
    /**
     * 声明事务管理器
     * @return PlatformTransactionManager
     */
    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
 
    /**
     * 声明sqlSession模板
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new  SqlSessionTemplate(sqlSessionFactory);
    }
}

说明:

  1. 注入多个数据源
  2. 声明动态数据源,声明动态数据源和目标数据源(多个用于切换的数据源)
  3. 声明一些插件,比如分页插件和打印全文sql的插件
  4. 声明sql会话
  5. 声明事务管理器
  6. 声明sqlSession模板

(4)、创建动态数据源对象(DynamicDataSource.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public class DynamicDataSource extends AbstractRoutingDataSource {
 
 
    /**
     * 有参构造方法,声明对象的时候执行,调用父类AbstractRoutingDataSource的方法
     * @param targetDataSources  数据源Map集合
     * @param defaultTargetDataSource  默认数据源
     */
    public DynamicDataSource(Map<Object,Object> targetDataSources, DataSource defaultTargetDataSource) {
        //将数据源的 key 放到数据源上下文的 key 集合中，用于切换时判断数据源是否有效
        DynamicDataSourceContextHolder.addDataSourceKeys(targetDataSources.keySet());
        //设置数据源集合
        super.setTargetDataSources(targetDataSources);
        //设置默认数据源
        super.setDefaultTargetDataSource(defaultTargetDataSource);
    }
 
    /**
     * 重写determineCurrentLookupKey方法,这个方法返回一个key值,
     * 通过这个key值执行determineTargetDataSource方法,获取当前的数据源
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceKey();
    }
}

说明:继承AbstractRoutingDataSource抽象类,实现setTargetDataSources(设置目标数据源集合)、setDefaultTargetDataSource(设置默认数据源)、determineCurrentLookupKey(返回当前数据源)等方法。

(5)、动态数据源操作上下文类(DynamicDataSourceContextHolder.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
public class DynamicDataSourceContextHolder {
 
    /**
     * 静态ThreadLocal常量contextHolder,用来装当前线程的数据源key
     */
    public static final ThreadLocal<String> contextHolder=new ThreadLocal<>();
 
 
 
    /**
     * 数据源的 key集合，用于切换时判断数据源是否存在
     */
    public static List<Object> dataSourceKeys = new ArrayList<>();
 
 
    /**
     * 获取contextHolder值(数据源key)的方法(获取当前数据源)
     */
    public static String getDataSourceKey(){
        return contextHolder.get();
    }
 
 
    /**
     * 写入contextHolder值(数据源key)的方法(写入当前数据源)
     */
    public static void setDataSourceKey(String key){
        contextHolder.set(key);
    }
 
 
    /**
     * 清除contextHolder值(数据源key)的方法(写入当前数据源)
     */
    public static void clearDataSourceKey(){
        contextHolder.remove();
    }
 
 
    /**
     * 判断是否包含数据源
     * @param key 数据源key
     * @return boolean
     */
    public static boolean containDataSourceKey(String key) {
        return dataSourceKeys.contains(key);
    }
 
    /**
     * 添加数据源keys
     * @param keys
     * @return boolean
     */
    public static boolean addDataSourceKeys(Collection<? extends Object> keys) {
        return dataSourceKeys.addAll(keys);
    }
}

说明:声明set、get当前数据源key、判断是否包含当前数据源、添加数据源key的方法

(6)、设置当前数据源注解(TargetDs.class)

?
1
2
3
4
5
6
7
8
9
10
11
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDs {
 
    /**
     * 数据源key值
     * @return
     */
    String value();
}

(7)、设置动态数据源切换类(DynamicDataSourceAspect.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@Aspect
@Order(-1)  // 该切面应当先于 @Transactional 执行
@Component
public class DynamicDataSourceAspect {
 
 
    /**
     * 前置通知,进入切点之前,先切换数据源
     * @param point
     * @param targetDs
     */
    @Before("@annotation(targetDs)")
    public void switchDataSource(JoinPoint point, TargetDs targetDs) {
        //判断,如果没有此数据源
        if (!DynamicDataSourceContextHolder.containDataSourceKey(targetDs.value())){
            System.out.println("没有找到key为[{}]的数据源,所以当前还是使用默认数据源!"+targetDs.value());
        }else {
            DynamicDataSourceContextHolder.setDataSourceKey(targetDs.value());
            System.out.println("方法"+point.getSignature().getName()+"上发现@TargetDs注解,"+"当前数据源已经切换为[{}]!"+targetDs.value());
        }
    }
 
 
    /**
     * 后置通知,切合方法执行完成之后,重置数据源
     * @param point
     * @param targetDs
     */
    @After("@annotation(targetDs)")
    public void restoreDataSource(JoinPoint point, TargetDs targetDs) {
        System.out.println("重置数据源 [" + DynamicDataSourceContextHolder.getDataSourceKey()
                + "] in Method [" + point.getSignature() + "]");
        // 将数据源置为默认数据源
        DynamicDataSourceContextHolder.clearDataSourceKey();
    }
}

说明:设置切面类,切点是带有@targetDs注解的方法,当遇到这种方法，执行前将数据源切换到对应的key对应的数据源,执行完成后还原到默认数据源。

(8)、事务配置(TransactionAdviceConfig.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@Aspect
@Configuration
public class TransactionAdviceConfig {
 
    //声明切面
    private static final String AOP_POINTCUT_EXPRESSION = "execution (* com.base.springboot.service.*.impl.*.*(..))";
 
 
    //事务管理器
    @Autowired
    @Qualifier("transactionManager")
    private PlatformTransactionManager transactionManager;
 
    //声明通知
    @Bean(name = "txInterceptor")
    public TransactionInterceptor txInterceptor(){
 
        Properties attributes = new Properties();
        attributes.setProperty("insert*",   "PROPAGATION_REQUIRED");
        attributes.setProperty("add*",      "PROPAGATION_REQUIRED");
        attributes.setProperty("update*",   "PROPAGATION_REQUIRED");
        attributes.setProperty("delete*",   "PROPAGATION_REQUIRED");
        attributes.setProperty("deploy*",   "PROPAGATION_REQUIRED");
        attributes.setProperty("select*",   "PROPAGATION_REQUIRED,readOnly");
        attributes.setProperty("get*",      "PROPAGATION_REQUIRED,readOnly");
        attributes.setProperty("query*",    "PROPAGATION_REQUIRED,readOnly");
        return new TransactionInterceptor(transactionManager, attributes);
    }
 
    @Bean
    public AspectJExpressionPointcutAdvisor pointcutAdvisor(@Qualifier("txInterceptor") TransactionInterceptor txInterceptor){
        AspectJExpressionPointcutAdvisor pointcutAdvisor = new AspectJExpressionPointcutAdvisor();
        pointcutAdvisor.setAdvice(txInterceptor);
        pointcutAdvisor.setExpression(AOP_POINTCUT_EXPRESSION);
        return pointcutAdvisor;
    }
}

说明:注入事务管理器,声明切面(控制的范围),声明事务拦截器(设置不同的方法对应的事务策略),声明AspectJExpressionPointcutAdvisor,传入切面和事务拦截通知,完成aop切入。

以上8个步骤,就完成了SpringBoot+MyBatis+Mysql+Durid的配置,多数据源通过自定义注解,动态切换,没有数据源都被事务管控,实现多数据源动态切换核心点就是AbstractRoutingDataSource。

四、集成log4j2日志

(1)、log4j2-spring-dev.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
<?xml version="1.0" encoding="UTF-8"?>
<configuration status="OFF" monitorInterval="30">
    <Properties>
        <!-- 日志默认存放的位置,这里设置为项目根路径下,也可指定绝对路径 -->
        <property name="basePath">D://log4j2Logs</property>
        <!-- 控制台默认输出格式,"%-5level":日志级别,"%l":输出完整的错误位置,是小写的L,因为有行号显示,所以影响日志输出的性能 -->
        <property name="console_log_pattern">%d{yyyy-MM-dd HH:mm:ss.SSS} [%-5level] %l - %m%n</property>
        <!-- 日志文件默认输出格式,不带行号输出(行号显示会影响日志输出性能);%C:大写,类名;%M:方法名;%m:错误信息;%n:换行 -->
        <property name="log_pattern">%d{yyyy-MM-dd HH:mm:ss.SSS} [%-5level] %C.%M - %m%n</property>
    </Properties>
 
    <appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <!--只接受程序中INFO级别的日志进行处理-->
            <ThresholdFilter level="DEBUG" onMatch="ACCEPT" onMismatch="DENY"/>
            <PatternLayout pattern="${console_log_pattern}"/>
        </Console>
 
        <!--处理DEBUG级别的日志，并把该日志放到logs/debug.log文件中-->
        <!--打印出DEBUG级别日志，每次大小超过size，则这size大小的日志会自动存入按年份-月份建立的文件夹下面并进行压缩，作为存档-->
        <RollingFile name="RollingFileDebug" fileName="${basePath}/logs/debug.log"
                     filePattern="${basePath}/logs/$${date:yyyy-MM}/debug-%d{yyyy-MM-dd}-%i.log.gz">
            <Filters>
                <ThresholdFilter level="DEBUG"/>
                <ThresholdFilter level="INFO" onMatch="DENY" onMismatch="NEUTRAL"/>
            </Filters>
            <PatternLayout pattern="${log_pattern}"/>
            <Policies>
                <SizeBasedTriggeringPolicy size="500 MB"/>
                <TimeBasedTriggeringPolicy/>
            </Policies>
        </RollingFile>
 
        <!--处理INFO级别的日志，并把该日志放到logs/info.log文件中-->
        <RollingFile name="RollingFileInfo" fileName="${basePath}/logs/info.log"
                     filePattern="${basePath}/logs/$${date:yyyy-MM}/info-%d{yyyy-MM-dd}-%i.log.gz">
            <Filters>
                <!--只接受INFO级别的日志，其余的全部拒绝处理-->
                <ThresholdFilter level="INFO"/>
                <ThresholdFilter level="WARN" onMatch="DENY" onMismatch="NEUTRAL"/>
            </Filters>
            <PatternLayout pattern="${log_pattern}"/>
            <Policies>
                <SizeBasedTriggeringPolicy size="500 MB"/>
                <TimeBasedTriggeringPolicy/>
            </Policies>
        </RollingFile>
 
        <!--处理WARN级别的日志，并把该日志放到logs/warn.log文件中-->
        <RollingFile name="RollingFileWarn" fileName="${basePath}/logs/warn.log"
                     filePattern="${basePath}/logs/$${date:yyyy-MM}/warn-%d{yyyy-MM-dd}-%i.log.gz">
            <Filters>
                <ThresholdFilter level="WARN"/>
                <ThresholdFilter level="ERROR" onMatch="DENY" onMismatch="NEUTRAL"/>
            </Filters>
            <PatternLayout pattern="${log_pattern}"/>
            <Policies>
                <SizeBasedTriggeringPolicy size="500 MB"/>
                <TimeBasedTriggeringPolicy/>
            </Policies>
        </RollingFile>
 
        <!--处理error级别的日志，并把该日志放到logs/error.log文件中-->
        <RollingFile name="RollingFileError" fileName="${basePath}/logs/error.log"
                     filePattern="${basePath}/logs/$${date:yyyy-MM}/error-%d{yyyy-MM-dd}-%i.log.gz">
            <ThresholdFilter level="ERROR"/>
            <PatternLayout pattern="${log_pattern}"/>
            <Policies>
                <SizeBasedTriggeringPolicy size="500 MB"/>
                <TimeBasedTriggeringPolicy/>
            </Policies>
        </RollingFile>
 
        <!--druid的日志记录追加器-->
        <RollingFile name="druidSqlRollingFile" fileName="${basePath}/logs/druid-sql.log"
                     filePattern="${basePath}/logs/$${date:yyyy-MM}/api-%d{yyyy-MM-dd}-%i.log.gz">
            <PatternLayout pattern="${log_pattern}"/>
            <Policies>
                <SizeBasedTriggeringPolicy size="500 MB"/>
                <TimeBasedTriggeringPolicy/>
            </Policies>
        </RollingFile>
    </appenders>
 
    <loggers>
        <root level="DEBUG">
            <appender-ref ref="Console"/>
            <appender-ref ref="RollingFileInfo"/>
            <appender-ref ref="RollingFileWarn"/>
            <appender-ref ref="RollingFileError"/>
            <appender-ref ref="RollingFileDebug"/>
        </root>
 
        <!--记录druid-sql的记录-->
        <logger name="druid.sql.Statement" level="info" additivity="false">
            <appender-ref ref="druidSqlRollingFile"/>
        </logger>
 
        <!--过滤掉spring和hibernate的一些无用的debug信息-->
        <logger name="org.springframework" level="INFO"></logger>
        <!--输出sql语句-->
        <logger name="com.base.springboot.dao" level="info" additivity="false">
            <appender-ref ref="Console" />
        </logger>
    </loggers>
</configuration>

 (2)、application.yml:

?
1
2
logging:
　　config: classpath:log4j2-spring-dev.xml

说明:配置log4j2日志,记录不同的级别的日志到不同的文件。

五、不同的错误跳转到错误页面

1、创建错误配置类(ErrorPageConfig.class),继承HandlerInterceptorAdapter。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Component
public class ErrorPageConfig extends HandlerInterceptorAdapter {
 
    private List<Integer> errorList= Arrays.asList(404, 405, 500);
 
 
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        if (errorList.contains(response.getStatus())){
            response.sendRedirect("/error/"+response.getStatus());
            return false;
        }
        return super.preHandle(request, response, handler);
    }
}

 2、在Controller里面写错误页面跳转方法(BaseController.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@Controller
public class BaseController {
 
    @RequestMapping("/error/{code}")
    public String errorController(@PathVariable("code") String code, Model model){
        String errorStr="";
        switch (code){
            case "404":
                errorStr="找不到页面!";
                break;
            case "405":
                errorStr="405错误!";
                break;
            case "500":
                errorStr="服务器错误!";
                break;
        }
        model.addAttribute("errorStr",errorStr);
        return "errorPage";
    }
}

说明:继承拦截器,重写preHandle方法(执行之前拦截)，当response.getStatus()状态码为错误码时,重定向到"/error/"+response.getStatus()方法，这个方法里面做不同的处理。

六、mybatis分页

(1)、声明列表视图类ListVo.class

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class ListVo<T> {
    //数据量
    private int totalSize = 0;
    //数据列表
    private List<T> list = new ArrayList();
 
    public int getTotalSize() {
        return totalSize;
    }
 
    public void setTotalSize(int totalSize) {
        this.totalSize = totalSize;
    }
 
    public List<T> getList() {
        return list;
    }
 
    public void setList(List<T> list) {
        this.list = list;
    }
}

说明:属性有数量和list列表

(2)、MybatisConfig.class配置类里面声明pageHelper对象,并且在sql会话里面插入插件。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
@Bean(name="pageHelper")
public PageHelper pageHelper() {
    PageHelper pageHelper = new PageHelper();
    Properties p = new Properties();
    p.setProperty("offsetAsPageNum", "true");
    p.setProperty("rowBoundsWithCount", "true");
    p.setProperty("reasonable", "true");
    p.setProperty("dialect", "mysql");
    pageHelper.setProperties(p);
 
    return pageHelper;
}
 
    /**
     * 声明sql会话
     * @return
     */
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("pageHelper")PageHelper pageHelper,@Qualifier("fullSqlInterceptor")FullSqlInterceptor fullSqlInterceptor) throws Exception{
        //声明sql会话工厂
        SqlSessionFactoryBean factoryBean=new SqlSessionFactoryBean();
        //设置数据源
        factoryBean.setDataSource(dynamicDataSource());
        //设置扫描mybatisXml的路径
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:**/dao/*/*.xml"));
        factoryBean.setPlugins(new Interceptor[] {pageHelper(), fullSqlInterceptor});//添加分页插件
        //返回sql会话
        return factoryBean.getObject();
    }

 (3)、Dao层的base操作类里面(BaseDaoImpl.class)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
@Repository("baseDao")
public class BaseDaoImpl implements IBaseDao {
 
    @Autowired
    public SqlSessionTemplate sqlSessionTemplate;
 
 
    @Override
    public List<Object> getObjectList(String statement, Map<String, Object> paramMap) {
 
        List<Object> list=null;
        try {
            list=sqlSessionTemplate.selectList(statement,paramMap);
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
 
 
    @Override
    public <T> ListVo<T> getObjectPage(String start,String limit,String statement, Map<String, Object> paramMap) {
        try {
//            RowBounds rowBounds=new RowBounds(Integer.parseInt(start),Integer.parseInt(limit));
//            List<T> list=sqlSessionTemplate.selectList(statement,paramMap,rowBounds);
            ListVo<T> listVo = new ListVo<T>();
            PageHelper.startPage(Integer.parseInt(start),Integer.parseInt(limit),true);
            List<T> list=sqlSessionTemplate.selectList(statement,paramMap);
            listVo.setList(list);
            Page<T> page = (Page<T>)list;
            listVo.setTotalSize((int)page.getTotal());
            return listVo;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
 
    @Override
    public void insertObject(String statement, Object object) {
        try{
            sqlSessionTemplate.insert(statement,object);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

 说明:

?
1
2
3
4
5
6
7
ListVo<T> listVo = new ListVo<T>();
PageHelper.startPage(Integer.parseInt(start),Integer.parseInt(limit),true);
List<T> list=sqlSessionTemplate.selectList(statement,paramMap);
listVo.setList(list);
Page<T> page = (Page<T>)list;
listVo.setTotalSize((int)page.getTotal());
return listVo;
  • PageHelper.startPage:进行物理分页
  • listVo.setList(list);:将list设置进listVo类的list属性
  • Page page = (Page)list;
  • listVo.setTotalSize((int)page.getTotal());:设置进listVo类的totalSize属性。

七、通过mybatis拦截器,在控制台打印完整的sql(FullSqlInterceptor.class)

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }),
        @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
                RowBounds.class, ResultHandler.class }) })
public class FullSqlInterceptor implements Interceptor {
 
 
    public Object intercept(Invocation invocation) throws Throwable {
        //获取<select> or <update> or <delete>节点
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = null;
        if (invocation.getArgs().length > 1) {
            //获取参数
            parameter = invocation.getArgs()[1];
        }
        //获取方法id
        String sqlId = mappedStatement.getId();
        //获取sql语句
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        //获取连接信息
        Configuration configuration = mappedStatement.getConfiguration();
        Object returnValue = null;
        //获取系统时间
        long start = System.currentTimeMillis();
        //获取返回数据
        returnValue = invocation.proceed();
        long end = System.currentTimeMillis();
        long time = (end - start);
        if (time > 1) {
            //调用getSql方法,获取处理后的sql语句
            String sql = getSql(configuration, boundSql, sqlId, time);
            System.err.println(sql);
        }
        return returnValue;
    }
 
    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) {
        String sql = showSql(configuration, boundSql);
        StringBuilder str = new StringBuilder(100);
        str.append("执行方法: ");
        str.append(sqlId);
        str.append("\n");
        str.append("执行语句:  ");
        str.append(sql);
        str.append("   :耗时");
        str.append(time);
        str.append("ms");
        return str.toString();
    }
 
    //这个方法是用来转换参数值的
    private static String getParameterValue(Object obj) {
        String value = null;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
 
        }
        return value;
    }
 
    public static String showSql(Configuration configuration, BoundSql boundSql) {
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        //如果有参数
        if (parameterMappings.size() > 0 && parameterObject != null) {
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            //如果是基础类型
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
            } else {
                //如果参数不是基础类型
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?", getParameterValue(obj));
                    }
                }
            }
        }
        return sql;
    }
 
}

说明:主要是在执行mybatis方法的时候,替换掉原生语句中的参数#{},替换成具体的值,方便在控制台直接复制到数据库查看。

以上就是SpringBoot+MyBatis+Mysql+Durid动态多数据源项目搭建的主要代码和过程。

 

posted @   47号Gamer丶  阅读(414)  评论(0)  编辑  收藏  举报
编辑推荐：
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· AI 智能体引爆开源社区「GitHub 热点速览」
· 写一个简单的SQL生成工具
点击右上角即可分享
微信分享提示