mybatis中使用DDL语句对clickhouse操作

前置准备

mybatisplus spring boot 多数据源配置

pom.xml 添加引用

<dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
        <version>3.5.2</version>
</dependency>

application.yml 配置

spring:
    datasource:
    druid:
        stat-view-servlet:
            enabled: true
    dynamic:
        # 配置全局druid参数，请按需配置
        druid:
            initial-size: 5
            max-active: 8
            min-idle: 3
            max-wait: 1000
            validation-query: 'select 1'
        primary: master
        datasource:
            #mysql数据库
            master:
                username: root
                password: ***
                url: jdbc:mysql://1.1.1.1:3306/weilai_crawl?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
                driver-class-name: com.mysql.cj.jdbc.Driver
            #clickhouse数据库
            slave_1:
                url: jdbc:clickhouse://1.1.1.1:8123/default?use_server_time_zone=true&use_server_time_zone=false
                username: default
                password: ****
                driver-class-name: com.clickhouse.jdbc.ClickHouseDriver

ClickHousee DDL 问题

问题描述

在使用mybatis 调用update 方法执行clickhouse的ddl建表语句时出现异常 ,mybatis有sql语句的校验，对于clickhouse语法部分关键字不能识别，所以导致sql解析错误，找了半天mybatisplus组件好像没有解决办法。只能换了个思路通过mybatis获取数据源的jdbc connection，使用JDBC来执行DDL语句

Caused by: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Failed to process, Error SQL: CREATE TABLE device_data_all_history ( `sys_id` String  , `dev_id` String  ,  `create_time` DateTime DEFAULT now() , `insert_time` DateTime DEFAULT now() , `c1`  String )
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
ORDER BY (dev_id,
 create_time)
SETTINGS index_granularity = 8192


Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "ORDER" "ORDER"
    at line 4, column 4.

Was expecting one of:

    ";"
    "AS"
    "DISABLE"
    "ENABLE"
    "LIKE"
    <EOF>

解决方式

mybatisPlus 多数据源获取 JDBC Connection

@Autowired
private DynamicRoutingDataSource dynamicRoutingDataSource;

//某业务方法
.....(){
    //clickhouse 建表语句
    String sql ="create table ..... " ;
    //获取数据源 Connection
    java.sql.Connection connection =   dynamicRoutingDataSource.getDataSource("slave_1").getConnection();
    connection.setAutoCommit(true);
    java.sql.Statement statement =connection.createStatement();
    statement.execute(sql);
    statement.close();
    connection.close();
}

---

完结