SpringBoot + Mybatis Plus + ClickHouse增刪改查入門教程
作者 | handler-劉
項目場景：
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ù)", "<script>\nALTER TABLE %s DELETE WHERE %s=#{%s}\n</script>"),
/**
* 邏輯刪除
*/
LOGIC_DELETE_BY_ID("deleteByIdClickHouse", "根據(jù)ID 邏輯刪除一條數(shù)據(jù)", "<script>\nALTER TABLE %s UPDATE %s where %s=#{%s} %s\n</script>"),
/**
* 修改 條件主鍵
*/
UPDATE_BY_ID("updateByIdClickHouse", "根據(jù)ID 選擇修改數(shù)據(jù)", "<script>\nALTER TABLE %s UPDATE %s where %s=#{%s} %s\n</script>"),
/**
* 修改 條件主鍵
*/
UPDATE("updateClickHouse", "根據(jù) whereEntity 條件，更新記錄", "<script>\nALTER TABLE %s UPDATE %s %s %s\n</script>");
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
如果您正在學(xué)習(xí)Spring Boot，推薦一個連載多年還在繼續(xù)更新的免費教程：http://blog.didispace.com/spring-boot-learning-2x/
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=","代表去掉第一個逗號
*
* @param sqlScript
* @return
*/
public static String convertSet(final String sqlScript) {
return "<trim prefix=\"\" suffixOverrides=\",\"> " + sqlScript + "\n" + "</trim>";
}
}
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<AbstractMethod> getMethodList(Class<?> mapperClass) {
/**
* 這里很重要，先要通過父類方法，獲取到原有的集合，不然會自帶的通用方法會失效的
*/
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
/***
* 添加自定義方法類
*/
methodList.add(new UpdateByIdClickHouse());
methodList.add(new UpdateClickHouse());
methodList.add(new DeleteClickHouse());
return methodList;
}
}
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<T> updateWrapper);
/**
* 主鍵刪除
*
* @param id
* @return
*/
int deleteByIdClickHouse(Serializable id);
}
另外，如果您正在學(xué)習(xí)Spring Cloud，推薦一個連載多年還在繼續(xù)更新的免費教程：https://blog.didispace.com/spring-cloud-learning/
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<User> 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
往期推薦
技術(shù)交流群最近有很多人問，有沒有讀者交流群，想知道怎么加入。加入方式很簡單，有興趣的同學(xué)，只需要點擊下方卡片，回復(fù)“加群“，即可免費加入我們的高質(zhì)量技術(shù)交流群！
點擊閱讀原文，送你免費Spring Boot教程！
評論
圖片
表情