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.click编程客栈house.ClickHouseDriver
      uphprl: 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;
impohttp://www.devze.comrt 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>
	     <depe编程ndency>
            <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>

到此这篇关于MybatisPlus3.3.1整合clickhouse的过程的文章就介绍到这了,更多相关MybatisPlus整合clickhouse内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！