SpringBoot3+最新MybatisPlus+Mysql与TDengine双数据源

2023-05-16

前言

昨天写的idea+Apifox uploader插件+apifox新年第一天上班就上榜了，真是不错。今天来补一篇，本来应该是在前一篇之前发的。实际上就是最新的springBoot集成最新的mybatisPlus，加双数据源：mysql、TDengine，一个关系型数据库，一个时序数据库。文末有独家demo的git地址。
springBoot3集成的哦，其他依赖也都是最新版本，独家的哦。好了，不废话，直接上重点。

一、新建最新springBoot3项目

这个没有啥好说的，就是选择spring项目了，下一步下一步，直接看引入的包吧。
pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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 https://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>3.0.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.xiaotian</groupId>
    <artifactId>data-trans</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>data-trans</name>
    <description>data-trans</description>

    <properties>
        <java.version>17</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <tlog.version>1.4.3</tlog.version>
        <skipTests>true</skipTests>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-tomcat</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- undertow容器支持 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-undertow</artifactId>
            <version>3.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>3.5.3.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.5.3.1</version>
        </dependency>

        <dependency>
            <groupId>com.taosdata.jdbc</groupId>
            <artifactId>taos-jdbcdriver</artifactId>
            <version>3.0.3</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>

        <!-- 多数据源支持 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.3.6</version>
        </dependency>


        <!--redis -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
            <version>2.7.5</version>
        </dependency>
        <!-- Lettuce redis支持 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
        </dependency>

        <!-- TLog引入支持 -->
        <dependency>
            <groupId>com.yomahub</groupId>
            <artifactId>tlog-all-spring-boot-starter</artifactId>
            <version>${tlog.version}</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.5.3.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.3</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>

            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>3.0.1</version>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

项目结构：
在这里插入图片描述

二、关键代码

1.多数据源配置

application.yml

#容器配置
server:
  port: 8199
  servlet:
    context-path: /datatrans
  shutdown: graceful
#spring基础配置

spring:
  application:
    name: dataTrans
  freemarker:
    check-template-location: false
    cache: false
  global:
    date-format: yyyy-MM-dd HH:mm:ss
  mvc:
    throw-exception-if-no-handler-found: true
    static-path-pattern: /**
  web:
    resources:
      static-locations: classpath:/resources/,classpath:/static/,classpath:/templates/
  profiles:
    active: dev
  servlet:
    multipart:
      max-file-size: 300MB
      max-request-size: 500MB
  thymeleaf:
    cache: false
    prefix: classpath:/templates/
    check-template-location: true
    suffix: .html
    encoding: UTF-8
    servlet:
      content-type: text/html
    mode: HTML
  main:
    allow-circular-references: true

#日志配置
logging:
  config: classpath:logback-spring.xml
  file:
    name: ./logs/dataTrans.log
  level:
    com:
      xiaotian:
        datagenius:
          mapper: debug
    root: info
path:
  log: ./logs/dataTrans/

#TLog轻量级日志追踪
tlog:
  pattern: '[$preApp][$preIp][$spanId][$traceId]'


#放行url配置
web:
  pass:
    url: /**,/**/actuator/**

application-dev.yml

spring:
  datasource:
    dynamic:
      primary: mysql-service
    mysql-service:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/wz?useUnicode=true&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowMultiQueries=true
      username: root
      password: 123456
      type: com.zaxxer.hikari.HikariDataSource
      minimum-idle: 5                 #最小连接
      maximum-pool-size: 15        #最大连接
      auto-commit: true        #自动提交
      idle-timeout: 30000        #最大空闲时常
      pool-name: MysqlDruidCP        #连接池名
      max-lifetime: 1800000        #最大生命周期
      connection-timeout: 30000        #连接超时时间
    tdengine-service:
      driver-class-name: com.taosdata.jdbc.TSDBDriver
      jdbc-url: jdbc:TAOS://ip:6030/test?user=root&password=taosdata&timezone=GMT%2b8
      username: root
      password: taosdata
      pool-name: Data_trans_HikariCP
      minimum-idle: 10 #最小空闲连接数量
      idle-timeout: 600000 #空闲连接存活最大时间，默认600000（10分钟）
      maximum-pool-size: 100 #连接池最大连接数，默认是10
      auto-commit: true  #此属性控制从池返回的连接的默认自动提交行为,默认值：true
      max-lifetime: 1800000 #此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
      connection-timeout: 30000 #数据库连接超时时间,默认30秒，即30000
      connection-test-query: SELECT 1

  #缓存配置
  data:
    redis:
      password: password
      cluster:
        max-redirects: 3
        nodes: 127.0.0.1:6379
      host: ip
      port: 6379
      database: 0
      timeout: 6000

#redis连接配置
redis:
  hostName: ${spring.data.redis.host}
  minIdle: 10
  maxIdle: 300
  maxTotal: 1000
  maxWaitMillis: 1000
  minEvictableIdleTimeMillis: 300000
  numTestsPerEvictionRun: 1024
  password: ${spring.data.redis.password}
  port: ${spring.data.redis.port}
  testOnBorrow: true
  testWhileIdle: true
  timeBetweenEvictionRunsMillis: 30000
  timeout: 10000

2.核心代码

mysql数据源配置类（MysqlServerConfig）：

package com.xiaotian.datatrans.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Collections;


/**
 * mysql配置类
 * @author zhengwen
 */
@Configuration
@MapperScan(basePackages = {"com.xiaotian.datatrans.mapper.mysql"}, sqlSessionTemplateRef  = "mysqlSqlSessionTemplate")
public class MysqlServerConfig {

    @Autowired
    private PaginationInnerInterceptor paginationInnerInterceptor;

    private static final String MAPPER_LOCATION = "classpath:mapper/tdengine/*.xml";
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql-service")
    @Primary
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        //注意这里一定要用MybatisSqlSessionFactoryBean，如果用SqlSessionFactory，配置无效
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));

        //不在这里注入分页插件，会失效，（MyBatisPlusConfig只负责注册分页插件）
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        paginationInnerInterceptor.setMaxLimit(-1L);
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInnerInterceptor.setOptimizeJoin(true);
        mybatisPlusInterceptor.setInterceptors(Collections.singletonList(paginationInnerInterceptor));
        bean.setPlugins(mybatisPlusInterceptor);

        return bean.getObject();
    }

    @Bean(name = "mysqlTransactionManager")
    @Primary
    public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mysqlSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

TDengine数据源配置类（TDengineServerConfig）：

package com.xiaotian.datatrans.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.Collections;


/**
 * TDengine配置类
 *
 * @author zhengwen
 */
@Configuration
@MapperScan(basePackages = "com.xiaotian.datatrans.mapper.tdengine", sqlSessionTemplateRef = "tdengineSqlSessionTemplate")
public class TDengineServerConfig {

    @Autowired
    private PaginationInnerInterceptor paginationInnerInterceptor;

    private static final String MAPPER_LOCATION = "classpath:mapper/tdengine/*.xml";

    @Bean(name = "tdengineDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.tdengine-service")
    public DataSource tdengineDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "tdengineSqlSessionFactory")
    public SqlSessionFactory tdengineSqlSessionFactory(@Qualifier("tdengineDataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));

        //不在这里注入分页插件，会失效，（MyBatisPlusConfig只负责注册分页插件）
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        paginationInnerInterceptor.setMaxLimit(-1L);
        paginationInnerInterceptor.setDbType(DbType.TDENGINE);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInnerInterceptor.setOptimizeJoin(true);
        mybatisPlusInterceptor.setInterceptors(Collections.singletonList(paginationInnerInterceptor));
        bean.setPlugins(mybatisPlusInterceptor);

        return bean.getObject();
    }

    @Bean(name = "tdengineTransactionManager")
    public DataSourceTransactionManager tdengineTransactionManager(@Qualifier("tdengineDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


    @Bean(name = "tdengineSqlSessionTemplate")
    public SqlSessionTemplate tdengineSqlSessionTemplate(@Qualifier("tdengineSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


}

Mapper上注意指定数据源，例如：

package com.xiaotian.datatrans.mapper.tdengine;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xiaotian.datatrans.entity.Student;
import org.apache.ibatis.annotations.Param;

/**
 * @author zhengwen
 */
@DS("tdengine-service")  //指定数据源注解，名称与配置数据源名称一致
public interface StudentMapper extends BaseMapper<Student> {

    /**
     * 插入
     * @param record
     * @return
     */
    int insertStudent(Student record);

    /**
     * 列表
     * @param page
     * @param customQueryParams
     * @return
     */
    IPage<Student> selectStudentPage(Page page,@Param("student") Student customQueryParams);
}

3.核心注意点

先看启动类：

package com.xiaotian.datatrans;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;

/**
 * @author zhengwen
 */

@EntityScan("com.xiaotian")
@SpringBootApplication
public class DataTransApplication {

    public static void main(String[] args) {
        SpringApplication.run(DataTransApplication.class, args);
        System.out.println("--数据精灵启动成功--");
    }

}

1、Mapper扫描问题：
       细心的小伙伴可能知道，一般springboot的，要么在配置里配置mapper的包路径，要么在启动类上写注解@MapperScan(“包路径”)，如果是多个@MapperScan(basePackages = {“com.xx..mapper","com.xxx..dao”})，但是我这里配置里没有配置，启动类也没有注解，为什么呢？
       原因其实很简单，这些预制的基础配置都是给单数据源用的，如果用多数据源，那么各个mapper就要对应不同的数据源，所以这些mapper扫描，在数据源配置类上设置了。大家可以回头看看我的2个数据源配置类是不是有注解@MapperScan。
2、分页插件问题
       一般单数据源，那么只需要一个分页插件配置类就行，例如我这里的MyBatisPlusConfig.java。
       但是这里因为是多数据源，所以插件的设置需要在数据源上做拦截，大家可以看看我2个数据源里的sessionFactory里的MybatisPlusInterceptor设置。
       另外这里要注意的是最新版本的mybatisPlus已经用PaginationInnerInterceptor拦截器了，原先的PaginationInterceptor已经废除了。

总结

mybatisPlus真香，支持的数据源真多
mybatisPlus、分页插件支持TDengine不久，TDengine的更新是用insert操作的，只是时间挫是一致的就行，但是mybatisPlus的BaseMapper里的update方法并不会根据数据源使用insert组织sql。
mybatisPlus在3.4.2时移除了Rest Api
CSDN的gitcode也真香啊
本次案例的整个demo就在我的gitcode里，公开的，git下载以后，改改配置里的数据源ip、账号、密码，建表就可以看到效果了。地址：https://gitcode.net/zwrlj527/data-trans.git
好了，就到这里，希望可以帮到大家，uping！！！

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)