MybatisPlus3.3.1整合clickhouse的過程

更新時間：2025年05月23日 16:08:27 作者：fx9590

這篇文章主要介紹了MybatisPlus3.3.1整合clickhouse的過程,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧

前言ClickHouse是俄羅斯Yandex發(fā)布的一款數(shù)據(jù)分析型數(shù)據(jù)庫支持sql語法,詳情可以訪問官網(wǎng),目前網(wǎng)上還沒有MybatisPlus整合clickhouse文章發(fā)布故此寫一遍博文記錄整理一下整個過程
完整工程已提交至碼云:https://gitee.com/yankangkk/watchmen

關(guān)于大家在評論區(qū)經(jīng)常留言關(guān)于分頁的問題，其實在之前的朋友已經(jīng)有了好的解決辦法,下面附上截圖，供大家參考。

連接池部分用的是阿里的druid下面是數(shù)據(jù)庫連接的配置類

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數(shù)據(jù)庫連接池配置
 */
@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相關(guān)配置
 */
@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;
	  }
}

實體類對應(yīng)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 每頁條數(shù)
	 * @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

至此整合就已經(jīng)完成了寫一個路由層測試一下

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)

經(jīng)過測試我發(fā)現(xiàn)pagehelper和mybatis-plsu都不能正確識別clickhouse數(shù)據(jù)，只能自己寫分頁語句，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數(shù)組數(shù)據(jù)刪除數(shù)據(jù)
	 */
	@Delete("ALTER TABLE test_table DELETE WHERE id = #{id}")
	void deleteById(Integer id);
}

項目也集成了knife4j可以直接調(diào)試
百度云的knife4j是:http://106.12.154.174:8080/doc.html#/home可以直接調(diào)試

pom的jar包依賴

<!-- 數(shù)據(jù)庫相關(guān) -->
		<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驅(qū)動 -->
	    <dependency>
		    <groupId>ru.yandex.clickhouse</groupId>
		    <artifactId>clickhouse-jdbc</artifactId>
		    <version>${clickhouse-jdbc.version}</version>
		</dependency>

到此這篇關(guān)于MybatisPlus3.3.1整合clickhouse的過程的文章就介紹到這了,更多相關(guān)MybatisPlus整合clickhouse內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: