springboot mybatis oracle插入SQL动态字段和值使用Map实现单条或批量插入

直接看代码

场景1:

Mapper

@Mapper
public interface ExcelDataMapper {

    public void insertExcelData(@Param("params")LinkedHashMap<String, Object> linkMap, @Param("tableName")String tableName);

    public void batchExceDataInsert(@Param("list") ArrayList<LinkedHashMap<String, Object>> list,@Param("tableName")String tableName);
}

XML

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.xxx.oracleMapper.ExcelDataMapper">

    <insert id="insertExcelData" parameterType="java.util.LinkedHashMap">
        insert into ${tableName}
        <foreach collection="params.keys" item="key" open="(" close=")" separator=",">
            ${key}
        </foreach>
        values 
        <foreach collection="params.keys" item="key" open="(" close=")" separator=",">
            #{params[${key}]}
        </foreach>
    </insert>

<!-- 
insert all 
into 表名(字段1,字段2) VALUES ('ABCD','ABCD')
into 表名 (字段1,字段2) VALUES ('ABCD','ABCD')
select 1 from dual;
 -->
    <insert id="batchExceDataInsert" parameterType="java.util.ArrayList" >
        insert all
        <foreach collection="list" item="paramap" index="index" separator=" " > 
            into ${tableName}
            <foreach collection="paramap.keys" item="key" open="(" close=")" separator=",">
                ${key}
            </foreach>
            values 
            <foreach collection="paramap.values" item="value" open="(" close=")" separator=",">
                ${value}
            </foreach>
        </foreach>
        select 1 from dual
    </insert>

</mapper>

场景2:

Mapper

@Mapper 
public interface QueryDataMapper {

void UpdateDimensionTabData(@Param("tableName")String tableName,
        @Param("newMap")HashMap<String, Object> newMap, @Param("oldData")HashMap<String, Object> oldData);
}

XML

    <update id="UpdateDimensionTabData" parameterType="java.util.HashMap">
        UPDATE ${tableName}
        SET
        <foreach item="value" index="key" collection="newMap" separator=",">
             ${key} = #{value}
        </foreach>
        WHERE
        <foreach item="value" index="key" collection="oldData" separator="and">
           <choose>
                <when test="value ==''" >
                    ${key} is null
                </when>
                <otherwise>
                    ${key} = #{value}
                </otherwise>
            </choose>
        </foreach>
    </update>
展开阅读全文

没有更多推荐了,返回首页