请选择 进入手机版 | 继续访问电脑版
MSIPO技术圈 首页 IT技术 查看内容

MyBatisPlus几个常用的通用sql扩展(基于旧版本,新版本可能有了)

2023-07-13

通过id获取–>get(id),与selectById等同。

public class GetMethod extends AbstractMethod {

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        SqlMethod sqlMethod = SqlMethod.SELECT_BY_ID;
        SqlSource sqlSource = new RawSqlSource(configuration, String.format(sqlMethod.getSql(),
                this.sqlSelectColumns(tableInfo, false),
                tableInfo.getTableName(), tableInfo.getKeyColumn(), tableInfo.getKeyProperty()), Object.class);
        return this.addSelectMappedStatement(mapperClass, "get", sqlSource, modelClass, tableInfo);
    }
}


批量插入 insertBatch(List)

public class MyInsertList extends AbstractMethod {

    private static List<String> oracleSqlGrammar;
    private static List<String> mysqlGrammar;
    private static String productName;

    static {
        oracleSqlGrammar = new ArrayList<>();
        oracleSqlGrammar.add("Oracle");
        oracleSqlGrammar.add("DM DBMS");
        oracleSqlGrammar.add("OSCAR");
        oracleSqlGrammar.add("KingbaseES");
        mysqlGrammar = new ArrayList<>();
        mysqlGrammar.add("MySQL");
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        if (null == productName) {
            productName = getProductName();
        }
        String sqlResult;
        if (ZYStrUtils.isNull(productName) || mysqlGrammar.contains(productName)) {
            sqlResult = generateMysqlVersionBatchSql(tableInfo);
        } else if (oracleSqlGrammar.contains(productName)) {
            sqlResult = generateOracleVersionBatchSql(tableInfo);
        } else {
            throw new LocalException("不支持的批量新增数据库版本");
        }
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), tableInfo.getKeyProperty(), tableInfo.getKeyColumn());
    }

    private String generateOracleVersionBatchSql(TableInfo tableInfo) {
        StringBuilder batchSql = new StringBuilder();
        batchSql.append("<script>");
        batchSql.append(" INSERT ALL ");
        batchSql.append("<foreach collection=\"list\" item=\"item\" separator=\" \">");
        batchSql.append(" INTO ").append(tableInfo.getTableName());
        batchSql.append(" ");
        batchSql.append(prepareFieldSql(tableInfo));
        batchSql.append(" VALUES (");
        batchSql.append(prepareOracleValuesSql(tableInfo));
        batchSql.append(") ");
        batchSql.append("</foreach>");
        batchSql.append(" SELECT 1 FROM dual");
        batchSql.append("</script>");
        return batchSql.toString();
    }

    public String generateMysqlVersionBatchSql(TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldSql = prepareFieldSql(tableInfo);
        final String valueSql = prepareMysqlValuesSql(tableInfo);
        return String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
    }


    private String prepareFieldSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        String keyColumn = tableInfo.getKeyColumn();
        if (ZYStrUtils.isNotNull(keyColumn)) {
            fieldSql.append(keyColumn).append(",");
        }

        tableInfo.getFieldList().forEach(x -> fieldSql.append(x.getColumn()).append(","));
        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
        fieldSql.insert(0, "(");
        fieldSql.append(")");
        return fieldSql.toString();
    }

    private String prepareMysqlValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        appendFields(tableInfo, valueSql);
        valueSql.append("</foreach>");
        return valueSql.toString();
    }

    private void appendFields(TableInfo tableInfo, StringBuilder valueSql) {
        String keyProperty = tableInfo.getKeyProperty();
        if (ZYStrUtils.isNotNull(keyProperty)) {
            valueSql.append("#{item.").append(keyProperty).append("},");
        }

        tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
        valueSql.delete(valueSql.length() - 1, valueSql.length());
    }

    private String prepareOracleValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        appendFields(tableInfo, valueSql);
        return valueSql.toString();
    }

    private String getProductName() {
        DataSource dataSource = this.configuration.getEnvironment().getDataSource();
        try {
            return dataSource.getConnection().getMetaData().getDatabaseProductName();
        } catch (SQLException e) {
            return null;
        }
    }
}

部份字段获取:getPart(id,columns)

class SelectPartById extends AbstractMethod {

    @SneakyThrows
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sql = "SELECT ${%s} FROM %s WHERE %s=#{%s}";

        sql = String.format(
                sql,
                Part.PART_COLUMN_SEGMENT,
                tableInfo.getTableName(),
                tableInfo.getKeyColumn(),
                Part.ID
        );

        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);

        return this.addSelectMappedStatement(mapperClass, "getPart", sqlSource, modelClass, tableInfo);
    }
}

批量部份字段获取:getPartList(ids,columns)

class SelectPartByIds extends AbstractMethod {

    @SneakyThrows
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sql = "<script>SELECT ${%s} FROM %s WHERE %s IN (%s)</script>";

        sql = String.format(
                sql,
                Part.PART_COLUMN_SEGMENT,
                tableInfo.getTableName(),
                tableInfo.getKeyColumn(),
                SqlScriptUtils.convertForeach("#{item}", COLLECTION, null, "item", COMMA)
        );

        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);

        return this.addSelectMappedStatement(mapperClass, "getPartList", sqlSource, modelClass, tableInfo);
    }
}

批量更新:updateByIds(ids)

public class UpdateByIds extends AbstractMethod {
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sqlScript = "<script>UPDATE %s %s WHERE %s in (%s)</script>";
        String sql = String.format(sqlScript,
                tableInfo.getTableName(),
                sqlSet(false, false, tableInfo, true, ENTITY, ENTITY_DOT),
                tableInfo.getKeyColumn(),
                SqlScriptUtils.convertForeach("#{item}", COLLECTION, null, "item", COMMA)
        );
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addUpdateMappedStatement(mapperClass, modelClass, "updateByIds", sqlSource);
    }
}

通过wrapper更新updateByWrapper(UpdateWrapper)

public class UpdateByWrapper extends AbstractMethod {
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sqlScript = "<script>UPDATE %s %s %s</script>";

        String setSqlScript = SqlScriptUtils.convertIf(SqlScriptUtils.unSafeParam(U_WRAPPER_SQL_SET),
                    String.format("%s != null and %s != null", WRAPPER, U_WRAPPER_SQL_SET), false);
        setSqlScript = SqlScriptUtils.convertSet(setSqlScript);

        String sql = String.format(
                sqlScript,
                tableInfo.getTableName(),
                setSqlScript,
                this.sqlWhereEntityWrapper(true, tableInfo)
        );
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addUpdateMappedStatement(mapperClass, modelClass, "updateByWrapper", sqlSource);
    }
}

Mapper基类里面通过default关键字定义的一些方法

// 避免sql过长引发异常
    default void insertSplitBatch(List<T> ts, int num) {
        if (null == ts || ts.isEmpty()) {
            return;
        }
        if (ts.size() <= num) {
            insertBatch(ts);
            return;
        }
        List<List<T>> lists = ZYListUtils.split(ts, num);
        for (List<T> list : lists) {
            insertBatch(list);
        }
    }

    // 避免sql过长引发异常
    default void logicDeleteBySplitIds(List<String> idList, int num) {
        if (null == idList || idList.isEmpty()) {
            return;
        }
        if (idList.size() <= num) {
            logicDeleteByIds(idList);
            return;
        }
        List<List<String>> lists = ZYListUtils.split(idList, num);
        for (List<String> list : lists) {
            logicDeleteByIds(list);
        }
    }

    // 避免sql过长引发异常
    default void deleteBySplitIds(List<String> idList, int num) {
        if (null == idList || idList.isEmpty()) {
            return;
        }
        if (idList.size() <= num) {
            deleteBatchIds(idList);
            return;
        }
        List<List<String>> lists = ZYListUtils.split(idList, num);
        for (List<String> list : lists) {
            deleteBatchIds(list);
        }
    }

    // 避免sql过长引发异常
    default List<T> selectBySplitIds(List<String> idList, int num) {
        if (null == idList || idList.isEmpty()) {
            return Collections.emptyList();
        }
        if (idList.size() <= num) {
            return this.selectBatchIds(idList);
        }
        List<List<String>> lists = ZYListUtils.split(idList, num);
        List<T

相关阅读

热门文章

    手机版|MSIPO技术圈 皖ICP备19022944号-2

    Copyright © 2024, msipo.com

    返回顶部