MybatisPlus3.3.1整合clickhouse

前言ClickHouse是俄罗斯Yandex发布的一款数据分析型数据库支持sql语法,详情可以访问官网,目前网上还没有MybatisPlus整合clickhouse文章发布故此写一遍博文记录整理一下整个过程
完整工程已提交至码云:https://gitee.com/yankangkk/watchmen

关于大家在评论区经常留言关于分页的问题，其实在之前的朋友已经有了好的解决办法,下面附上截图，供大家参考。

连接池部分用的是阿里的druid下面是数据库连接的配置类

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.alibaba.druid.pool.DruidDataSource;
/**
 * 
 * @author kk
 * Druid数据库连接池配置
 */
@Configuration
public class DruidConfig {

	@Resource
	private JdbcParamConfig jdbcParamConfig;
	
	@Bean
	public DataSource dataSource() {
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setUrl(jdbcParamConfig.getUrl());
		dataSource.setDriverClassName(jdbcParamConfig.getDriverClassName());
		dataSource.setInitialSize(jdbcParamConfig.getInitialSize());
		dataSource.setMinIdle(jdbcParamConfig.getMinIdle());
		dataSource.setMaxActive(jdbcParamConfig.getMaxActive());
		dataSource.setMaxWait(jdbcParamConfig.getMaxWait());
		return dataSource;
	}
}

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import lombok.Data;

/**
 * @author kk
 * clickhouse连接信息配置
 */
@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.click")
public class JdbcParamConfig {

	private String driverClassName;
	
	private String url ;
	   
	private Integer initialSize ;
	   
	private Integer maxActive ;
	    
	private Integer minIdle ;
	    
	private Integer maxWait ;
}

分页插件配置

import java.util.Properties;

import org.springframework.context.annotation.Bean;

import com.github.pagehelper.PageHelper;
/**
 * 
 * @author kk
 * MybatisPlus相关配置
 */
@Configuration
public class MybatisPlusConfig {

	  @Bean
	  public PageHelper pageHelper() {
	      PageHelper pageHelper = new PageHelper();
	      Properties properties = new Properties();
	      properties.setProperty("offsetAsPageNum", "true");
	      properties.setProperty("rowBoundsWithCount", "true");
	      properties.setProperty("reasonable", "true");
	      pageHelper.setProperties(properties);
	      return pageHelper;
	  }
}

实体类对应clickhouse中的表

import java.util.Date;

import com.baomidou.mybatisplus.annotation.TableName;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * 
 * @author kk
 * 实体类
 */
@TableName("test_table")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class TestTableEntity {

	private Long id;
	
	private String name;
	
	private String value;
	
	private Date createDate;
	
	private Object array;
}

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.watchmen.clickhouse.entity.TestTableEntity;

public interface TestTableMapper extends BaseMapper<TestTableEntity> {

	
}

import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.watchmen.clickhouse.entity.TestTableEntity;

public interface TestTableService extends IService<TestTableEntity>{

	/**
	 * 分页查询
	 * @param page 第几页
	 * @param pageSize 每页条数
	 * @return Page
	 */
	Page<TestTableEntity> list(Integer page, Integer pageSize);
}

import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.watchmen.clickhouse.entity.TestTableEntity;
import com.watchmen.clickhouse.mapper.TestTableMapper;
import com.watchmen.clickhouse.service.TestTableService;

@Service
public class TestTableServiceImpl extends ServiceImpl<TestTableMapper,TestTableEntity> implements TestTableService {

	@Override
	public Page<TestTableEntity> list(Integer page, Integer pageSize) {
		return this.page(new Page<TestTableEntity>(page,pageSize),
				  new QueryWrapper<TestTableEntity>());
	}
}

启动类加上扫描注解

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.watchmen.clickhouse.mapper")
@SpringBootApplication
public class DemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}

}

application.yml配置 106.12.154.174是我在百度云上搭建的clickhouse搭建可以直接连接测试使用

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    click:
      driverClassName: ru.yandex.clickhouse.ClickHouseDriver
      url: jdbc:clickhouse://106.12.154.174:8123/default?max_result_bytes=10000
      username: root
      paswword: 
      initialSize: 10
      maxActive: 100
      minIdle: 10
      maxWait: 6000

至此整合就已经完成了写一个 路由层测试一下

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.watchmen.clickhouse.entity.TestTableEntity;
import com.watchmen.clickhouse.service.TestTableService;

/**
 * 
 * @author kk
 * Clickhouse增删改查测试路由
 */
@RestController
@RequestMapping("/clickhouse")
public class ClickhouseTest {
	
	@Autowired
	TestTableService testTableService; 
	
	/**
	 * 分页查询
	 * @return
	 */
	@GetMapping("/list")
	public Object list(@RequestParam(value = "page",defaultValue = "1") Integer page,
					   @RequestParam(value = "page_size",defaultValue = "10") Integer pageSize) {
		List<TestTableEntity> list = testTableService.list();
		System.out.println(list);
		return testTableService.list(page, pageSize);
	}
	
	
}

测试表sql脚本

CREATE TABLE default.test_table (
`id` UInt16,
 `name` String,
 `value` String,
 `create_date` Date,
 `array` Array(String)
) ENGINE = MergeTree(create_date, id, 8192)

经过测试我发现pagehelper和mybatis-plsu都不能正确识别clickhouse数据，只能自己写分页语句，clickhouse的删除语句也比较特殊这里一并写了出来，官方的建议还是批量删除，虽然它支持单条删除，代码如下:

package com.watchmen.clickhouse.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.watchmen.clickhouse.entity.TestTableEntity;

public interface TestTableMapper extends BaseMapper<TestTableEntity> {

	/**
	 *  分页查询
	 * @param page
	 * @param pageSize
	 * @return
	 */
	@Select("select * from test_table tt limit #{page}, #{pageSize}")
	List<TestTableEntity> selectPages(Integer page, Integer pageSize);
	
	/**
	 * @author kk
	 * 按id数组数据删除数据
	 */
	@Delete("ALTER TABLE test_table DELETE WHERE id = #{id}")
	void deleteById(Integer id);
	
}

项目也集成了knife4j可以直接调试
百度云的knife4j是:http://106.12.154.174:8080/doc.html#/home可以直接调试

pom的jar包依赖

		<!-- 数据库相关 -->
		<dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
		<dependency>
	        <groupId>com.baomidou</groupId>
	        <artifactId>mybatis-plus-boot-starter</artifactId>
	        <version>${mybatis-plsu.version}</version>
	    </dependency>
	     <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
         <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>${pagehelper.version}</version>
        </dependency>
        <!-- sql性能分析插件 -->
        <dependency>
		    <groupId>p6spy</groupId>
		    <artifactId>p6spy</artifactId>
		    <version>${p6spy.version}</version>
		</dependency>
        <!-- clickhouse-jdbc驱动 -->
	    <dependency>
		    <groupId>ru.yandex.clickhouse</groupId>
		    <artifactId>clickhouse-jdbc</artifactId>
		    <version>${clickhouse-jdbc.version}</version>
		</dependency>