【Mybatis】Mybatis-Generator-Postgresql-批量更新

最新推荐文章于 2025-02-27 12:43:02 发布

java劝退师

最新推荐文章于 2025-02-27 12:43:02 发布

阅读量2.6k

点赞数

分类专栏： Mybatis 文章标签： mybatis-generator mybatis

本文链接：https://blog.csdn.net/HuHui_/article/details/85150685

版权

Mybatis 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种用于MyBatis的PostgreSQL批量更新插件，该插件能够简化数据库批量更新操作，通过自定义的SQL语句和Java方法实现高效的数据处理。插件基于MyBatis Generator框架，适用于PostgreSQL数据库，提供了批量更新的实现方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

系列

Github

地址：https://github.com/ithuhui/hui-mybatis-generator-plugins
分支：master
位置：com.hui.mybatis.plugins

Note

PG批量更新插件，核心贴出来，有需要的看我的GITHUB，准备更新微服务BLOG

github上针对mybatis-generator还有很多不同类型的插件，我实在懒的看，而且太多复杂的功能，我简单点搞自己需要的。

Code

maven

<dependencies>
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.6</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.mybatis.generator/mybatis-generator-core -->
    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.3.7</version>
    </dependency>
</dependencies>

sql

update t_hui_table from (values (1,'name'),(2,'name2') ) as temp (id,name) where t_hui_table.id = temp.id

mybatis-implement

<update id="batchUpdate" parameterType="java.util.List">
    update T_HUI_ORDER
    <set>
       ORDER_NAME = temp.ORDER_NAME,
       PRODUCT_ID = temp.PRODUCT_ID,
       BUY_QUANTITY = temp.BUY_QUANTITY,
       CREATED_TIME = temp.CREATED_TIME
    </set>
    from (values
    <foreach collection="recordList" index="index" item="item" separator=",">
      (
      #{item.orderId,jdbcType=VARCHAR},#{item.orderName,jdbcType=VARCHAR},#{item.productId,jdbcType=VARCHAR},#{item.buyQuantity,jdbcType=DECIMAL},to_timestamp(#{item.createdTime,jdbcType=TIMESTAMP},'yyyy-MM-dd hh24:mi:ss')
      )
    </foreach>
    ) as temp (ORDER_ID,ORDER_NAME,PRODUCT_ID,BUY_QUANTITY,CREATED_TIME) where T_HUI_ORDER.ORDER_ID=temp.ORDER_ID;
  </update>

plugin-code

/**
 * <b><code>PostgreBatchUpdatePlugin</code></b>
 * <p/>
 * Description:
 * <p/>
 * <b>Creation Time:</b> 2018/12/15 1:12.
 *
 * @author HuWeihui
 */
public class PostgreBatchUpdatePlugin extends PluginAdapter {

    private final static String BATCH_UPDATE = "batchUpdate";

    private final static String PARAMETER_NAME = "recordList";

    @Override
    public boolean validate(List<String> list) {
        return true;
    }

    @Override
    public boolean clientGenerated(Interface interfaze, TopLevelClass topLevelClass, IntrospectedTable introspectedTable) {
        if (introspectedTable.getTargetRuntime().equals(IntrospectedTable.TargetRuntime.MYBATIS3)) {
            MethodGeneratorTool.defaultBatchInsertOrUpdateMethodGen(MethodGeneratorTool.UPDATE,interfaze,introspectedTable,context);
        }
        return super.clientGenerated(interfaze, topLevelClass, introspectedTable);
    }

    @Override
    public boolean sqlMapDocumentGenerated(Document document, IntrospectedTable introspectedTable) {
        if (introspectedTable.getTargetRuntime().equals(IntrospectedTable.TargetRuntime.MYBATIS3)) {
            addSqlMapper(document, introspectedTable);
        }
        return super.sqlMapDocumentGenerated(document, introspectedTable);
    }


    private void addSqlMapper(Document document, IntrospectedTable introspectedTable){
        String tableName = introspectedTable.getFullyQualifiedTableNameAtRuntime();
        List<IntrospectedColumn> columnList = introspectedTable.getAllColumns();

        String primaryKeyName = introspectedTable.getPrimaryKeyColumns().get(0).getActualColumnName();
        //primaryKey的JAVA名字
        String primaryKeyJavaName = introspectedTable.getPrimaryKeyColumns().get(0).getJavaProperty();

        XmlElement updateElement = SqlMapperGeneratorTool.baseElementGenerator(SqlMapperGeneratorTool.UPDATE,
                BATCH_UPDATE,
                FullyQualifiedJavaType.getNewListInstance());

        XmlElement foreachElement = SqlMapperGeneratorTool.baseForeachElementGenerator(PARAMETER_NAME,
                "item",
                "index",
                ",");

        String baseSql = String.format("update %s", tableName);

        updateElement.addElement(new TextElement(baseSql));

        XmlElement setElement = new XmlElement("set");


        StringBuilder columnInfo = new StringBuilder();
        StringBuilder valuesInfo = new StringBuilder();

        StringBuilder columnInfoTotal = new StringBuilder();
        for (int i = 0; i < columnList.size(); i++) {

            IntrospectedColumn introspectedColumn = columnList.get(i);

            columnInfo.append(introspectedColumn.getActualColumnName());
            columnInfoTotal.append(introspectedColumn.getActualColumnName());
            if (introspectedColumn.getFullyQualifiedJavaType().equals(FullyQualifiedJavaType.getDateInstance())){
                valuesInfo.append("to_timestamp(");
                valuesInfo.append(MyBatis3FormattingUtilities.getParameterClause(introspectedColumn, "item."));
                valuesInfo.append(",'yyyy-MM-dd hh24:mi:ss')");
            }else {
                valuesInfo.append(MyBatis3FormattingUtilities.getParameterClause(introspectedColumn, "item."));
            }

            String setSql = String.format(" %s = %s," ,columnInfo,"temp."+columnInfo);
            if (i == (columnList.size() - 1)) {
                setSql = setSql.substring(0, setSql.length() - 1);
            }
            if (!introspectedColumn.isIdentity() && !introspectedColumn.getActualColumnName().equals(primaryKeyName)) {
                setElement.addElement(new TextElement(setSql));
            }

            if (i != (columnList.size() - 1)) {
                valuesInfo.append(",");
                columnInfo.append(",");
                columnInfoTotal.append(",");
            }

            columnInfo.delete(0, valuesInfo.length());
        }

        foreachElement.addElement(new TextElement("("));

        foreachElement.addElement(new TextElement(valuesInfo.toString()));

        foreachElement.addElement(new TextElement(")"));

        updateElement.addElement(setElement);

        updateElement.addElement(new TextElement("from (values"));

        updateElement.addElement(foreachElement);

        updateElement.addElement(new TextElement(String.format(") as temp (%s) where %s.%s=temp.%s;",columnInfoTotal,tableName,primaryKeyName,primaryKeyName)));

        //3.parent Add
        document.getRootElement().addElement(updateElement);
    }
}

plugin-common-tool

插件通用方法/工具类，上面生成方法基于了一个工具类，下面博客有源码，真正需要的可以看看github

【Mybatis】Mybatis-Generator-Tool 生成Java和Xml的Method工具类

使用方法

generatorConfig.xml

<!--MySql批量更新-->
<plugin type="com.hui.mybatis.plugins.PostgreBatchUpdatePlugin"/>

pom.xml

<build>
        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>${mybatis.generator}</version>
                <configuration>
                    <!-- 配置文件 -->
                    <configurationFile>
                        ${basedir}/src/main/resources/generator/generatorConfig.xml
                    </configurationFile>
                    <!-- 允许移动生成的文件 -->
                    <verbose>true</verbose>
                    <!-- 是否覆盖 -->
                    <overwrite>true</overwrite>
                </configuration>
                <dependencies>
                    <!-- mybatis-generator-core -->
                    <dependency>
                        <groupId>org.mybatis.generator</groupId>
                        <artifactId>mybatis-generator-core</artifactId>
                        <version>${mybatis.generator}</version>
                    </dependency>

                    <!--插件导入-->
                    <dependency>
                        <groupId>com.hui.mybatis.plugins</groupId>
                        <artifactId>hui-mybatis-plugins</artifactId>
                        <version>0.0.1-SNAPSHOT</version>
                    </dependency>

                    <!--Mysql-->
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.44</version>
                    </dependency>

                    <!--ORACLE-->
                    <dependency>
                        <groupId>com.oracle</groupId>
                        <artifactId>ojdbc6</artifactId>
                        <version>11.1.0.7.0</version>
                    </dependency>

                    <!--PG SQL-->
                    <dependency>
                        <groupId>org.postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>${postgresql.version}</version>
                    </dependency>

                </dependencies>
            </plugin>
        </plugins>
    </build>

Author

 作者：HuHui
 转载：欢迎一起讨论web和大数据问题,转载请注明作者和原文链接，感谢