Extending MyBatis‑Plus for ClickHouse: Custom Update, Delete, and Mapper Implementation

Project scenario

ClickHouse operations are extended based on MyBatis‑Plus source code to handle SQL modifications and deletions that differ from MySQL.

Based on MyBatis‑Plus

The extension provides update, updateById, and delete functions.

SqlMethodDiv.java

package com.demo.infrastructure.injector.enums;

public enum SqlMethodDiv {
    /** 删除 */
    DELETE_BY_ID("deleteByIdClickHouse", "根据ID 删除一条数据", "<script>
ALTER TABLE %s DELETE WHERE %s=#{%s}
</script>"),
    /** 逻辑删除 */
    LOGIC_DELETE_BY_ID("deleteByIdClickHouse", "根据ID 逻辑删除一条数据", "<script>
ALTER TABLE %s UPDATE %s where %s=#{%s} %s
</script>"),
    /** 修改 条件主键 */
    UPDATE_BY_ID("updateByIdClickHouse", "根据ID 选择修改数据", "<script>
ALTER TABLE %s UPDATE %s where %s=#{%s} %s
</script>"),
    /** 修改 条件主键 */
    UPDATE("updateClickHouse", "根据 whereEntity 条件，更新记录", "<script>
ALTER TABLE %s UPDATE  %s %s %s
</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

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;

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

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

    public static String convertSet(final String sqlScript) {
        return "<trim prefix=\"\" suffixOverrides=\",\" > " + sqlScript + "
" + "</trim>";
    }
}

ClickHouseSqlInjector.java

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<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new UpdateByIdClickHouse());
        methodList.add(new UpdateClickHouse());
        methodList.add(new DeleteClickHouse());
        return methodList;
    }
}

SuperMapper interface

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> {
    boolean updateByIdClickHouse(@Param("et") T entity);
    boolean updateClickHouse(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);
    int deleteByIdClickHouse(Serializable id);
}

Unit tests

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("创建：" + 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("创建：" + user);
    }

    @Test
    public void delete_Test() {
        userService.delete(1L);
        System.out.println("删除：" + 1L);
    }
}