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

項(xiàng)目場景：

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

基于 Mybatis-puls

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

SqlMethodDiv.java?文件枚舉類，對 SQL 腳本定義

package?com.demo.infrastructure.injector.enums;

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;
????}
}

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;

/**
?*?@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"?+?"";
????}
}

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;

/**
?*?注冊方法
?*/
public?class?ClickHouseSqlInjector?extends?DefaultSqlInjector?{

????@Override
????public?List?getMethodList(Class?mapperClass)?{
????????/**
?????????*?這里很重要，先要通過父類方法，獲取到原有的集合，不然會自帶的通用方法會失效的
?????????*/
????????List?methodList?=?super.getMethodList(mapperClass);
????????/***
?????????*?添加自定義方法類
?????????*/
????????methodList.add(new?UpdateByIdClickHouse());
????????methodList.add(new?UpdateClickHouse());
????????methodList.add(new?DeleteClickHouse());
????????return?methodList;
????}
}

編寫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;

/**
?*?自定義方法
?*/
@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);
}

單元測試看效果

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);
????}

}

最后需要本文完整 demo 源碼的，加我微信：xttblog2，免費(fèi)獲取！