TDengine时序数据库第二篇——SpringBoot+MySql+TDengine+Mybatis双数据源搭建
由于实际的业务情况更多是又有mysql又有TDengine，所以，SpringBoot+MySql+TDengine双数据源搭建更值得拿来一讲。
·
由于实际的业务情况更多是又有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上面的代码，可以通过该地址：
进行源码demo下载，不过csdn要收积分。下载后的源码需要自己调整一下yml中数据库的配置。
更多推荐
8
0- 0
已为社区贡献1条内容
所有评论(0)