如何将Mybatis连接到ClickHouse

    
      com.alibaba
      druid
      1.2.5
    

    
      ru.yandex.clickhouse
      clickhouse-jdbc
      0.2.6
    

    
      org.mybatis.spring.boot
      mybatis-spring-boot-starter
      2.1.3
    

spring:
 datasource:
  type: com.alibaba.druid.pool.DruidDataSource

  # 注意这里是自定义的配置，通过 JdbcParamConfig 来加载配置到 Spring 中
  # 然后由 DruidConfig 来配置数据源
  click:
   driverClassName: ru.yandex.clickhouse.ClickHouseDriver
   url: jdbc:clickhouse://127.0.0.1:8123/test # ip:port/database
   userName: default
   password: default # 按照自己连接的 clickhouse 数据库来
   initialSize: 10
   maxActive: 100
   minIdle: 10
   maxWait: 6000
   validationQuery: SELECT 1

@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class JdbcParamConfig {
  private String userName;
  private String password;
  private String driverClassName ;
  private String url ;
  private Integer initialSize ;
  private Integer maxActive ;
  private Integer minIdle ;
  private Integer maxWait ;
  private String validationQuery;

  // ignore getters and setters
}

@Configuration
@MapperScan(basePackages = {
    "com.aegis.analysis.clickhousestorage.dao"
})
public class DruidConfig {
  @Resource
  private JdbcParamConfig jdbcParamConfig ;

  @Bean(name = "clickDataSource")
  public DataSource dataSource() throws ClassNotFoundException {
    Class classes = Class.forName("com.alibaba.druid.pool.DruidDataSource");
    DruidDataSource dataSource = (DruidDataSource) DataSourceBuilder
        .create()
        .driverClassName(jdbcParamConfig.getDriverClassName())
        .type(classes)
        .url(jdbcParamConfig.getUrl())
        .username(jdbcParamConfig.getUserName())
        .password(jdbcParamConfig.getPassword())
        .build();
    dataSource.setMaxWait(jdbcParamConfig.getMaxWait());
    dataSource.setValidationQuery(jdbcParamConfig.getValidationQuery());
    return dataSource;
  }

  @Bean
  public SqlSessionFactory clickHouseSqlSessionFactoryBean() throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    factory.setDataSource(dataSource());
    // 实体 model的 路径 比如 com.order.model
    factory.setTypeAliasesPackage("com.example.clickhousedemo.model");
    //添加XML目录
    ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    factory.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
    //开启驼峰命名转换
    factory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
    return factory.getObject();
  }
}

CREATE TABLE test.user (
   `id` Int16,
   `name` String,
   `score` Float32,
   `score2` Float64,
   `state` Int8,
   `createTime` DateTime,
   `ranks` Array(UInt8)
   ) ENGINE = MergeTree() ORDER BY id;

public class UserInfo {

  private Integer id; // int16
  private String name; // String
  private Float score; // float16
  private Double score2; // float32
  private Boolean state; // int8
  private Date createTime; // datetime
  private Integer[] ranks; // Array - Array 类型需要进行类型转换
  // 具体转换方法与配置参考 ClickArrayToIntHandler 类与 UserMapper.xml 中关于查询和插入时 ranks 字段的配置

  // ignore getters and setters
}

/**
 * Java Int 数组与 ClockHouse Array Int 转换器
 * @version 1.0
 * @since 2019/11/14 9:59
 */
public class ClickArrayToIntHandler extends BaseTypeHandler {

  @Override
  public void setNonNullParameter(PreparedStatement preparedStatement, int i, Integer[] integers, JdbcType jdbcType) throws SQLException {
    preparedStatement.setObject(i, integers);
  }

  @Override
  public Integer[] getNullableResult(ResultSet resultSet, String s) throws SQLException {
    Object obj = resultSet.getObject(s);
    return parseClickHouseArrayToInt(obj);
  }

  @Override
  public Integer[] getNullableResult(ResultSet resultSet, int i) throws SQLException {
    Object obj = resultSet.getObject(i);
    return parseClickHouseArrayToInt(obj);
  }

  @Override
  public Integer[] getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
    Object obj = callableStatement.getObject(i);
    return parseClickHouseArrayToInt(obj);
  }

  private Integer[] parseClickHouseArrayToInt(Object obj) {
    if (obj instanceof ClickHouseArray) {
      int[] res = new int[0];
      try {
        res = (int[]) ((ClickHouseArray) obj).getArray();
      } catch (SQLException ex) {
        ex.printStackTrace();
      }

      if (res != null && res.length > 0) {
        Integer[] resI = new Integer[res.length];
        for (int i = 0; i < res.length; i++) {
          resI[i] = res[i];
        }

        return resI;
      }
    }
    return new Integer[0];
  }
}

<?xml version="1.0" encoding="UTF-8"?>


  
    
    
    
    
    
    
    
    
    
  

  
    *
  

  
    INSERT INTO user
      (id,name, score, score2, state, createTime, ranks)
    VALUES
    (#{id},#{name}, #{score}, #{score2}, #{state}, #{createTime}, #{ranks, jdbcType=ARRAY,
    typeHandler=com.example.clickhousedemo.dao.ClickArrayToIntHandler})
  

  
    select
    
    from user
    where id = #{id}
    limit 1
  

  
    select
    
    from user