-
java 项目集成 mysql 和 clickhouse 数据库
-
依赖:因为使用阿里源管理没成功过,这里使用的数据源管理是 dynamic-datasource-spring-boot-starter ,如果原来项目有依赖于 druid-spring-boot-starter 要排除掉,
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
<version>1.18.24</version>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>2.0.10</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<!-- clickhouse -->
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.3.2</version>
</dependency>
<!-- 多数据源管理 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
<!--引入mybatisPlus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<!--mybatisPlus 生成-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.2</version>
</dependency>
<!-- 模板引擎 -->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>2.3</version>
</dependency>
<!--mybatisPlus-->
- 配置文件
# -------------------------------------------------------------------#
spring.datasource.dynamic.primary=mysql
spring.datasource.dynamic.strict=false
spring.datasource.dynamic.datasource.mysql.url=jdbc:mysql://ip:port/db_name?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&autoReconnect=true&autoReconnectForPools=true
spring.datasource.dynamic.datasource.mysql.username=root
spring.datasource.dynamic.datasource.mysql.password=123
spring.datasource.dynamic.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.dynamic.datasource.clickhouse.url=jdbc:clickhouse://ip:port/db_name
spring.datasource.dynamic.datasource.clickhouse.username=admin
spring.datasource.dynamic.datasource.clickhouse.password=password
spring.datasource.dynamic.datasource.clickhouse.driver-class-name=com.clickhouse.jdbc.ClickHouseDriver
spring.datasource.dynamic.druid.initial-size=10
spring.datasource.dynamic.druid.max-active=40
spring.datasource.dynamic.druid.min-idle=5
spring.datasource.dynamic.druid.validation-query=select 'x'
spring.datasource.dynamic.druid.max-wait=60000
spring.datasource.dynamic.druid.time-between-eviction-runs-millis=60000
spring.datasource.dynamic.druid.min-evictable-idle-time-millis=30000
- 实体类 和正常实体没有任何区别
package com.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
* @Author Meng
* @Date 2022/7/29 9:13 星期五/周五
* @Description 测试多源clickhouse
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class PhoneRecord implements Serializable {
private Long field_name_1;
private String field_name_2;
private Integer field_name_3;
private Integer field_name_4;
private Long field_name_5;
}
- mapper.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.mapper.ClickhouseMapper">
<resultMap id="BaseResultMap" type="com.entity.PhoneRecord">
<result property="field_name_1" column="field_name_1" jdbcType="BIGINT"/>
<result property="field_name_2" column="field_name_2" jdbcType="VARCHAR"/>
<result property="field_name_3" column="field_name_3" jdbcType="INTEGER"/>
<result property="field_name_4" column="field_name_4" jdbcType="INTEGER"/>
<result property="field_name_5" column="field_name_5" jdbcType="BIGINT"/>
</resultMap>
<sql id="Base_Column_List">
<!--Base_Column_List-->
field_name_1,field_name_2,field_name_3,field_name_4,field_name_5
</sql>
<select id="selectByPageCondition" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List"/>
FROM `db_name` LIMIT 0,100;
</select>
</mapper>
- mapper
package com.mapper;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.entity.PhoneRecord;
import java.util.List;
/**
* <p>
* 基本信息 Mapper 接口
* </p>
*
* @author Meng
* @since 2022-06-20
*/
@DS("clickhouse") //重点是这里 使用clickhouse 数据库要指定数据源 mysql数据库默认不用配置就可以了,因为在配置文件中配置了默认mysql
public interface ClickhouseMapper extends BaseMapper<PhoneRecord> {
List<PhoneRecord> selectByPageCondition();
}
- 接口请求
package com.controller;
import com.mapper.ClickhouseMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
/**
* @Author Meng
* @Date 2022/7/29 10:53 星期五/周五
* @Description
*/
@SuppressWarnings("Duplicates")
@RestController
@Slf4j
@RequestMapping("/control")
@Api(tags = "Control")
public class Controller {
@Resource
private ClickhouseMapper clickhouseMapper;
@PostMapping(value = "/control1")
@ApiOperation(value = "control1",httpMethod = "POST")
public String control1(){
System.out.println(clickhouseMapper.selectByPageCondition());
return "control1";
}
}
- 启动类
package com;
//import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.annotation.EnableScheduling;
/**
* @author Eng
* @date 2021/11/25 14:26
* @Description
*/
//@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
@SpringBootApplication(scanBasePackages = {"com"},exclude = {DataSourceAutoConfiguration.class})//这里排除spring自动配置
//@ServletComponentScan("com")
@EnableScheduling
@MapperScan({"com.mapper", "com.mapper"})
@EnableAsync
//@EnableCaching
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
- 因为原来项目使用的是阿里连接源依赖,一直弄不好,干脆直接重新搞个spring demo 全部重新来搞,搞好了直接复制到项目里就可以实际应用了,这里要说明下clickhouse 修改和新增语句应该是和mysql不一样的所以不能使用mybatis-plus 执行要自己写sql。记录下以后减少踩坑。