SpringBoot + Mybatis-puls + ClickHouse增刪改查入門教程

項(xiàng)目場景：

ClickHouse 操作基于 Mybatis-puls源碼擴(kuò)展開發(fā)。解決ClickHouse的修改和刪除 SQL操作與Mysql不相同。

基于 Mybatis-puls：

update 、updateById 、 delete ?函數(shù)

1、SqlMethodDiv.java 文件枚舉類，對sql腳本定義

package?com.demo.infrastructure.injector.enums;
?
/**
?*?@author?liuxiansong
?*/
public?enum?SqlMethodDiv?{
?
????/**
?????*?刪除
?????*/
????DELETE_BY_ID("deleteByIdClickHouse",?"根據(jù)ID?刪除一條數(shù)據(jù)",?""),
?
????/**
?????*?邏輯刪除
?????*/
????LOGIC_DELETE_BY_ID("deleteByIdClickHouse",?"根據(jù)ID?邏輯刪除一條數(shù)據(jù)",?""),
?
????/**
?????*?修改?條件主鍵
?????*/
????UPDATE_BY_ID("updateByIdClickHouse",?"根據(jù)ID?選擇修改數(shù)據(jù)",?""),
????/**
?????*?修改?條件主鍵
?????*/
????UPDATE("updateClickHouse",?"根據(jù)?whereEntity?條件，更新記錄",?"");
?
????private?final?String?method;
????private?final?String?desc;
????private?final?String?sql;
?
????SqlMethodDiv(String?method,?String?desc,?String?sql)?{
????????this.method?=?method;
????????this.desc?=?desc;
????????this.sql?=?sql;
????}
?
????public?String?getMethod()?{
????????return?method;
????}
?
????public?String?getDesc()?{
????????return?desc;
????}
?
????public?String?getSql()?{
????????return?sql;
????}
}

2、UpdateByIdClickHouse.java 類文件，繼承 AbstractMethod 重載 injectMappedStatement

package?com.demo.infrastructure.injector.methods;
?
import?com.baomidou.mybatisplus.core.injector.AbstractMethod;
import?com.baomidou.mybatisplus.core.metadata.TableInfo;
import?com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import?com.demo.infrastructure.injector.enums.SqlMethodDiv;
import?org.apache.ibatis.executor.keygen.NoKeyGenerator;
import?org.apache.ibatis.mapping.MappedStatement;
import?org.apache.ibatis.mapping.SqlSource;
?
/**
?*?@author?liuxiansong
?*?@date?2021-05-20
?*?@desc?通過MybatisPlus源碼?擴(kuò)展修改
?*/
public?class?UpdateByIdClickHouse?extends?AbstractMethod?{
?
????@Override
????public?MappedStatement?injectMappedStatement(Class?mapperClass,?Class?modelClass,?TableInfo?tableInfo)?{
????????SqlMethodDiv?sqlMethod?=?SqlMethodDiv.UPDATE_BY_ID;
?
????????final?String?additional?=?optlockVersion(tableInfo)?+?tableInfo.getLogicDeleteSql(true,?true);
????????String?sql?=?String.format(sqlMethod.getSql(),?tableInfo.getTableName(),
????????????????this.sqlSet(tableInfo.isWithLogicDelete(),?false,?tableInfo,?false,?ENTITY,?ENTITY_DOT),
????????????????tableInfo.getKeyColumn(),?ENTITY_DOT?+?tableInfo.getKeyProperty(),?additional);
????????SqlSource?sqlSource?=?languageDriver.createSqlSource(configuration,?sql,?modelClass);
????????return?this.addInsertMappedStatement(mapperClass,?modelClass,
????????????????sqlMethod.getMethod(),?sqlSource,?new?NoKeyGenerator(),?null,?null);
????}
?
?
????/**
?????*?SQL?更新?set?語句
?????*
?????*?@param?logic??是否邏輯刪除注入器
?????*?@param?ew?????是否存在?UpdateWrapper?條件
?????*?@param?table??表信息
?????*?@param?alias??別名
?????*?@param?prefix?前綴
?????*?@return?sql
?????*/
????@Override
????protected?String?sqlSet(boolean?logic,?boolean?ew,?TableInfo?table,?boolean?judgeAliasNull,?final?String?alias,
????????????????????????????final?String?prefix)?{
????????String?sqlScript?=?table.getAllSqlSet(logic,?prefix);
????????if?(judgeAliasNull)?{
????????????sqlScript?=?SqlScriptUtils.convertIf(sqlScript,?String.format("%s?!=?null",?alias),?true);
????????}
????????if?(ew)?{
????????????sqlScript?+=?NEWLINE;
????????????sqlScript?+=?SqlScriptUtils.convertIf(SqlScriptUtils.unSafeParam(U_WRAPPER_SQL_SET),
????????????????????String.format("%s?!=?null?and?%s?!=?null",?WRAPPER,?U_WRAPPER_SQL_SET),?false);
????????}
????????sqlScript?=?convertSet(sqlScript);
????????return?sqlScript;
????}
?
????/**
?????*?去掉sest?和?suffixOverrides=","代表去掉第一個(gè)逗號
?????*
?????*?@param?sqlScript
?????*?@return
?????*/
????public?static?String?convertSet(final?String?sqlScript)?{
????????return?"?"?+?sqlScript?+?"\n"?+?"";
????}
}

3、ClickHouseSqlInjector.java 注冊方法，繼承 DefaultSqlInjector

package?com.demo.infrastructure.injector;
?
import?com.baomidou.mybatisplus.core.injector.AbstractMethod;
import?com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import?com.demo.infrastructure.injector.methods.DeleteClickHouse;
import?com.demo.infrastructure.injector.methods.UpdateByIdClickHouse;
import?com.demo.infrastructure.injector.methods.UpdateClickHouse;
?
import?java.util.List;
?
/**
?*?注冊方法
?*
?*?@author?liuxiansong
?*/
public?class?ClickHouseSqlInjector?extends?DefaultSqlInjector?{
?
????@Override
????public?List?getMethodList(Class?mapperClass)?{
????????/**
?????????*?這里很重要，先要通過父類方法，獲取到原有的集合，不然會(huì)自帶的通用方法會(huì)失效的
?????????*/
????????List?methodList?=?super.getMethodList(mapperClass);
????????/***
?????????*?添加自定義方法類
?????????*/
????????methodList.add(new?UpdateByIdClickHouse());
????????methodList.add(new?UpdateClickHouse());
????????methodList.add(new?DeleteClickHouse());
????????return?methodList;
????}
}

4、編寫SuperMapper 繼承BaseMapper，讓所有Mapper 繼承extends

package?com.demo.domain.mapper;
?
import?com.baomidou.mybatisplus.core.conditions.Wrapper;
import?com.baomidou.mybatisplus.core.mapper.BaseMapper;
import?org.apache.ibatis.annotations.Param;
?
import?java.io.Serializable;
?
/**
?*?@author?liuxiansong
?*?自定義方法
?*/
@SuppressWarnings("all")
public?interface?SuperMapper<T>?extends?BaseMapper<T>?{
?
????/**
?????*?@return
?????*?@Description:?刪除并填充刪除人信息
?????*?@param:?id?主鍵id
?????*?@auther:?zpq
?????*?@date:?2020/11/10?11:47?上午
?????*/
????boolean?updateByIdClickHouse(@Param("et")?T?entity);
?
?
????/**
?????*?@return
?????*?@Description:?刪除并填充刪除人信息
?????*?@param:?id?主鍵id
?????*?@auther:?zpq
?????*?@date:?2020/11/10?11:47?上午
?????*/
????boolean?updateClickHouse(@Param("et")?T?entity,?@Param("ew")?Wrapper?updateWrapper);
?
????/**
?????*?主鍵刪除
?????*
?????*?@param?id
?????*?@return
?????*/
????int?deleteByIdClickHouse(Serializable?id);
}

5、單元測試看效果

package?com.demo.test;
?
import?com.demo.DemoClickHouse;
import?com.demo.domain.dataobject.User;
import?com.demo.domain.service.UserService;
import?com.demo.infrastructure.util.page.PageResult;
import?org.junit.Test;
import?org.junit.runner.RunWith;
import?org.springframework.beans.factory.annotation.Autowired;
import?org.springframework.boot.test.context.SpringBootTest;
import?org.springframework.test.context.junit4.SpringRunner;
?
@RunWith(SpringRunner.class)
@SpringBootTest(classes?=?DemoClickHouse.class)
public?class?UserMapperTest?{
?
????@Autowired
????UserService?userService;
?
?
????@Test
????public?void?findById_Test()?{
????????User?byId?=?userService.findById(1);
????????System.out.println("查詢用戶ID=1信息："?+?byId);
????}
?
????@Test
????public?void?page_Test()?{
????????User?user?=?new?User();
????????Integer?page?=?1;
????????Integer?limit?=?2;
????????PageResult?userList?=?userService.page(user,?page,?limit);
????????System.out.println("查詢用戶信息分頁："?+?userList);
????}
?
????@Test
????public?void?create_Test()?{
????????User?user?=?new?User();
????????user.setUserName("張三");
????????user.setPassWord("123");
????????user.setPhone("12312222");
????????user.setEmail("[email protected]");
????????userService.create(user);
????????System.out.println("創(chuàng)建："?+?user);
????}
?
????@Test
????public?void?update_Test()?{
????????User?user?=?new?User();
????????user.setId(1395347901827317761l);
????????user.setUserName("小李飛刀");
????????user.setPassWord("123");
????????user.setPhone("12312222");
????????user.setEmail("[email protected]");
????????userService.update(user);
????????System.out.println("創(chuàng)建："?+?user);
????}
?
?
????@Test
????public?void?delete_Test()?{
????????userService.delete(1l);
????????System.out.println("刪除："?+?1l);
????}
?
}

源碼：https://github.com/saimen90/clickhouse