概述

---

最近公司需求，要将kafka接收到的消息根据类型存储到不同的数据库，第一次这样配置数据库连接，所以记录一下

工程搭建

---

• 新建Spring Boot工程
• pom.xml中添加mybatis、druid、pgsql、sqlserver、influxdb依赖

  <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>druid</artifactId>
      <version>1.2.3</version>
  </dependency>
  <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
  </dependency>
  <!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
  <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <scope>runtime</scope>
  </dependency>
  <dependency>
      <groupId>org.influxdb</groupId>
      <artifactId>influxdb-java</artifactId>
      <scope>runtime</scope>
  </dependency>
  

• 配置application.yml(这里主要是对于数据库连接的配置)
  单数据源配置：

  spring:
    profiles:
      active: dev
    datasource:
      url: jdbc:postgresql://localhost:5432/ams?rewriteBatchedStatements=true
      username: username
      password: password
      driver-class-name: org.postgresql.Driver
  

  多数据源配置：

  spring:
    profiles:
      active: dev
    datasource:
      pgsql:
        # rewriteBatchedStatements=true 开启批处理
        jdbcUrl: jdbc:postgresql://localhost:5432/dbname?rewriteBatchedStatements=true
        username: username
        password: password
        driver-class-name: org.postgresql.Driver
        type: com.alibaba.druid.pool.DruidDataSource
  
      sqlserver:
        # trustServerCertificate=true 需要加具体原因请看下文
        jdbcUrl: 
  jdbc:sqlserver://localhost:1433;databasename=webServiceTest;trustServerCertificate=true;
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        username: username
        password: password
        type: com.alibaba.druid.pool.DruidDataSource
  
    influx:
      url: http://localhost:8086
      user: user
      password: password
  

  区别
  1、多数据源的配置中需要指定具体的名称来区分不同的数据库（上述配置中的dev和local，名称可以 根据具体需求自定义）
  2、需要使用jdbcUrl代替url

数据库连接配置文件

---

• pgsql配置文件

@Configuration
@MapperScan(basePackages = "com.inventec.dap.datarecord.mapper.pgsql", sqlSessionFactoryRef = "pgsqlSqlSessionFactory")
public class PgSqlDataSourceConfig {
    @Bean(name = "pgsqlDataSource")
    @ConfigurationProperties("spring.datasource.pgsql")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "pgsqlSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("pgsqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapping/pgsql/*.xml"));
        return sessionFactoryBean.getObject();
    }
}

• sqlserver配置文件

@Configuration
@MapperScan(basePackages = "com.inventec.dap.datarecord.mapper.sqlserver",sqlSessionFactoryRef = "sqlserverSqlSessionFactory")
public class SqlServerDataSourceConfig {
    @Bean(name = "sqlserverDataSource")
    @ConfigurationProperties("spring.datasource.sqlserver")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlserverSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("sqlserverDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapping/sqlserver/*.xml"));
        return sessionFactoryBean.getObject();
    }
}

不同配置文件通过@MapperScan注解的内容来区分不同数据库下的mapper文件，通过@ConfigurationProperties注解来加载指定的数据源

主启动类注解修改

---

@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})

测试连接

---

因为pgsql和sqlserver都使用了mybatis框架，因此对pgsql和sqlserver的使用不再赘诉，这里主要展示infuxdb的使用

@SpringBootTest
class DatarecordApplicationTests {

    @Autowired
    private InfluxDB influxDB;

    @Test
    void contextLoads() {
        Point point = Point.measurement("ApiQPS")     // ApiQPS表
                .tag("url", "/hello")  // url字段
                .addField("count", 123)        // 统计数据
                .time(System.currentTimeMillis(), TimeUnit.MILLISECONDS)  // 时间
                .build();
        influxDB.write("test","autogen",point);
    }
}

踩过的坑

---

• 关于 trustServerCertificate

这里是为了解决jdbc连接数据库的时候出现的异常，异常内容如下：

驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target”

该异常可通过在 url 连接中填写连接属性 trustServerCertificate=true 或 encrypt=false二者选一即可