TDengine时序数据库第二篇——SpringBoot+MySql+TDengine+Mybatis双数据源搭建

由于实际的业务情况更多是又有mysql又有TDengine，所以，SpringBoot+MySql+TDengine双数据源搭建更值得拿来一讲。

大家都是有经验的程序员哈哈，新建springboot项目就不讲了，网上很多资料，下面直接上代码：

1、工程目录结构：

2、重点是导入两个数据库的JDBC链接依赖taos-jdbcdriver与mysql-connector-java；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>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.taosdata.jdbc</groupId>
    <artifactId>springbootdemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springbootdemo</name>
    <description>Demo project for using tdengine with Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </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>com.taosdata.jdbc</groupId>
            <artifactId>taos-jdbcdriver</artifactId>
            <version>1.0.3</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.17</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

3、application.yml配置：192.168.181.10换成你自己的linux的静态ip地址;注意：连接池hikari配置不能形如下面配置，否则连接池配置不起作用：

正确配置如下：

server:
  port: 3001

spring:
  datasource:
    tdengine-server:
      jdbc-url: jdbc:TAOS://192.168.181.10:6030/sl?timezone=Asia/Beijing&charset=utf-8
      username: root
      password: taosdata
      type: com.zaxxer.hikari.HikariDataSource      # Hikari连接池的设置
      minimum-idle: 5                 #最小连接
      maximum-pool-size: 15        #最大连接
      auto-commit: true        #自动提交
      idle-timeout: 30000        #最大空闲时常
      pool-name: TDengineHikariCP        #连接池名
      max-lifetime: 1800000        #最大生命周期
      connection-timeout: 30000        #连接超时时间
      connection-test-query: show tables


    mysql-server:
      driverClassName: com.mysql.cj.jdbc.Driver
      name: mx-assess
      jdbc-url: jdbc:mysql://192.168.181.10:3306/mx_assess?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
      username: root
      password: 1111
      type: com.zaxxer.hikari.HikariDataSource      # Hikari连接池的设置
      minimum-idle: 5                 #最小连接
      maximum-pool-size: 15        #最大连接
      auto-commit: true        #自动提交
      idle-timeout: 30000        #最大空闲时常
      pool-name: DatebookHikariCP        #连接池名
      max-lifetime: 1800000        #最大生命周期
      connection-timeout: 30000        #连接超时时间

#mybatis
mybatis:
  typeAliasesPackage: com.taosdata.jdbc.springbootdemo.domain
  configuration:
    #    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl     #打印sql
    call-setters-on-nulls: true  #设置查询字段为空时，也返回该字段
    map-underscore-to-camel-case: true

4、搭建双数据源，需要自定义数据源配置：

a、在config包下新建mysql数据源配置MysqlServerConfig类：

package com.taosdata.jdbc.springbootdemo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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;

/**
 * @author tanglin
 * @date 2020/8/18 14:08
 */
@Configuration
@MapperScan(basePackages = {"com.taosdata.jdbc.springbootdemo.dao.mysql"}, sqlSessionTemplateRef  = "mysqlSqlSessionTemplate")
public class MysqlServerConfig {
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql-server")
    @Primary
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlSqlSessionFactory")
    @Primary
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mysql/*.xml"));
        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);
    }
}

b、在config包下新建TDengine数据源配置TDengineServerConfig类：

package com.taosdata.jdbc.springbootdemo.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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;

/**
 * @author tanglin
 * @date 2020/8/18 14:09
 */
@Configuration
@MapperScan(basePackages = {"com.taosdata.jdbc.springbootdemo.dao.tdengine"}, sqlSessionTemplateRef  = "tdengineSqlSessionTemplate")
public class TDengineServerConfig {
    @Bean(name = "tdengineDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.tdengine-server")
    public DataSource tdengineDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "tdengineSqlSessionFactory")
    public SqlSessionFactory tdengineSqlSessionFactory(@Qualifier("tdengineDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/tdengine/*.xml"));
        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);
    }

}

至此配置部分完成；

5、业务测试代码部分：

5.1、Controller层： MysqlController与WeatherController

package com.taosdata.jdbc.springbootdemo.controller;

import com.taosdata.jdbc.springbootdemo.service.MysqlService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Map;

/**
 * @author : tanglin
 * @date : 2020/5/11 14:41
 */
@RestController
@RequestMapping(value = "/mysql")
public class MysqlController {

    @Autowired
    private MysqlService mysqlService;

    @GetMapping("/{limit}")
    public Map queryPostingsByAccount (@PathVariable Long limit) {
        return mysqlService.selectOne();
    }

}

package com.taosdata.jdbc.springbootdemo.controller;

import com.taosdata.jdbc.springbootdemo.domain.Weather;
import com.taosdata.jdbc.springbootdemo.service.WeatherService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RequestMapping("/weather")
@RestController
public class WeatherController {

    @Autowired
    private WeatherService weatherService;

    /**
     * create database and table
     * @return
     */
    @GetMapping("/init")
    public boolean init(){
        return weatherService.init();
    }

    /**
     * Pagination Query
     * @param limit
     * @param offset
     * @return
     */
    @GetMapping("/{limit}/{offset}")
    public List<Weather> queryWeather(@PathVariable Long limit, @PathVariable Long offset){
        return weatherService.query(limit, offset);
    }

    /**
     * upload single weather info
     * @param temperature
     * @param humidity
     * @return
     */
    @GetMapping("/save/{temperature}/{humidity}")
    public int saveWeather(@PathVariable int temperature, @PathVariable float humidity){

        return weatherService.save(temperature, humidity);
    }

    /**
     * upload multi weather info
     * @param weatherList
     * @return
     */
    @PostMapping("/batch")
    public int batchSaveWeather(@RequestBody List<Weather> weatherList){

        return weatherService.save(weatherList);
    }

}

5.2、Service层：MysqlService与WeatherService，如果用到事务管理注意需要指定transactionManager等于你自己config配置文件中的事务管理器的名字

package com.taosdata.jdbc.springbootdemo.service;

import com.taosdata.jdbc.springbootdemo.dao.mysql.MysqlMapper;
import com.taosdata.jdbc.springbootdemo.domain.Weather;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

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

@Service
@Transactional(transactionManager = "mysqlTransactionManager")
public class MysqlService {

    @Autowired
    private MysqlMapper mysqlDao;

    public Map selectOne() {
        return mysqlDao.selectOne();
    }
}

package com.taosdata.jdbc.springbootdemo.service;

import com.taosdata.jdbc.springbootdemo.dao.tdengine.WeatherMapper;
import com.taosdata.jdbc.springbootdemo.domain.Weather;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional(transactionManager = "tdengineTransactionManager")
public class WeatherService {

    @Autowired
    private WeatherMapper weatherMapper;

    public boolean init() {

        weatherMapper.createDB();
        weatherMapper.createTable();

        return true;
    }

    public List<Weather> query(Long limit, Long offset) {
        return weatherMapper.select(limit, offset);
    }

    public int save(int temperature, float humidity) {
        Weather weather = new Weather();
        weather.setTemperature(temperature);
        weather.setHumidity(humidity);
        return weatherMapper.insert(weather);
    }

    public int save(List<Weather> weatherList) {
        return weatherMapper.batchInsert(weatherList);
    }

}

5.3、dao:

package com.taosdata.jdbc.springbootdemo.dao.mysql;

import org.springframework.stereotype.Repository;

import java.util.Map;

/**
 * @author tanglin
 * @date 2020/8/18 14:59
 */
@Repository
public interface MysqlMapper {
    Map selectOne();
}

package com.taosdata.jdbc.springbootdemo.dao.tdengine;

import com.taosdata.jdbc.springbootdemo.domain.Weather;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface WeatherMapper {

    int insert(Weather weather);

    int batchInsert(List<Weather> weatherList);

    List<Weather> select(@Param("limit") Long limit, @Param("offset")Long offset);

    void createDB();

    void createTable();
}

5.4、domian:

package com.taosdata.jdbc.springbootdemo.domain;

import java.sql.Timestamp;

public class Weather {

    private Timestamp ts;

    private int temperature;

    private float humidity;

    public Timestamp getTs() {
        return ts;
    }

    public void setTs(Timestamp ts) {
        this.ts = ts;
    }

    public int getTemperature() {
        return temperature;
    }

    public void setTemperature(int temperature) {
        this.temperature = temperature;
    }

    public float getHumidity() {
        return humidity;
    }

    public void setHumidity(float humidity) {
        this.humidity = humidity;
    }
}

5.5、mapper层：MysqlMapper.xml与WeatherMapper.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.taosdata.jdbc.springbootdemo.dao.mysql.MysqlMapper">

  <select id="selectOne" resultType="java.util.Map">
    SELECT
     *
    FROM
      assess_model
      limit 1
  </select>

</mapper>

<?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.taosdata.jdbc.springbootdemo.dao.tdengine.WeatherMapper">

    <resultMap id="BaseResultMap" type="com.taosdata.jdbc.springbootdemo.domain.Weather">
        <id column="ts" jdbcType="TIMESTAMP" property="ts" />
        <result column="temperature" jdbcType="INTEGER" property="temperature" />
        <result column="humidity" jdbcType="FLOAT" property="humidity" />
    </resultMap>

    <update id="createDB" >
        create database if not exists test;
    </update>

    <update id="createTable" >
        create table if not exists weather(ts timestamp, temperature int, humidity float);
    </update>

    <sql id="Base_Column_List">
        ts, temperature, humidity
    </sql>

    <select id="select" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from weather
        order by ts desc
        <if test="limit != null">
            limit #{limit,jdbcType=BIGINT}
        </if>
        <if test="offset != null">
            offset #{offset,jdbcType=BIGINT}
        </if>
    </select>

    <insert id="insert" parameterType="com.taosdata.jdbc.springbootdemo.domain.Weather" >
        insert into weather (ts, temperature, humidity) values (now, #{temperature,jdbcType=INTEGER}, #{humidity,jdbcType=FLOAT})
    </insert>

    <insert id="batchInsert" parameterType="java.util.List" >
        insert into weather (ts, temperature, humidity) values
        <foreach separator=" " collection="list" item="weather" index="index" >
            (now + #{index}a, #{weather.temperature}, #{weather.humidity})
        </foreach>
    </insert>


</mapper>

mysql的测试代码根据自己数据库情况自己调整哈。

启动项目，浏览器访问TDengine数据库：http://localhost:3001/weather/1/1 ，成功会是下面的界面：

如果不想一个个copy上面的代码，可以通过该地址：

https://download.csdn.net/download/qq_36608921/12722680?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522159789576019725222438755%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fdownload.%2522%257D&request_id=159789576019725222438755&biz_id=1&utm_medium=distribute.pc_search_result.none-task-download-2~download~first_rank_v2~rank_dl_default-8-12722680.pc_v2_rank_dl_default&utm_term=TDengine&spm=1018.2118.3001.4187

进行源码demo下载，不过csdn要收积分。下载后的源码需要自己调整一下yml中数据库的配置。