Mybatis不使用实体类查询，修改，添加表数据，使用Map列名、表名映射

查询

1. 首先，查询之前要先查询该表的列名

<!--根据表名获取所有表 所有列-->
    <select id="listColName" resultType="java.lang.String">
        select
            lower(column_name)
        from information_schema.columns where table_schema = #{dbName} and  table_name= #{tableName};
    </select>

2.传入列名，表名,查询条件（查询条件是一段sql字符串）查询

 <!-- 根据列名和表名查找数据 -->
    <select id="listDataByBrandAId" resultType="java.util.HashMap">
        select
        <foreach collection="colNameList" item="colNameListEach" separator=",">
            ${colNameListEach}
        </foreach>
        from ${tableName}
        where 1=1
        <if test="dataScope != null and dataScope!= ''">
            ${dataScope}
        </if>

        <if test="basicDataList != null ">
        and
            <foreach collection="basicDataList" item="basicDataListEach" separator=" or ">
                (brand_id = #{basicDataListEach.brand_id} and id = #{basicDataListEach.id})
            </foreach>
        </if>

    </select>

批量插入

<insert id="insertData">
        INSERT INTO ${tableName}(
        <foreach collection="colNameList" item="colNameEach" separator=",">
            ${colNameEach}
        </foreach>
        ) VALUES
        <foreach collection="listData" item="listDataEach" separator=",">
            (
            <foreach collection="colNameList" item="colNameEach" separator=",">
                #{listDataEach.${colNameEach}}
            </foreach>
            )
        </foreach>
    </insert>

批量修改

<update id="updateDataById">
        UPDATE ${tableName} set
        <foreach collection="colNameList" item="colNameListEach"  separator="," >
            ${colNameListEach} = case
            <foreach collection="listData" item="listDataEach"   >
                when ( id = #{listDataEach.id} )
                then #{listDataEach.${colNameListEach}}
            </foreach>
            end
        </foreach>
        <if test="listData != null and listData.size() > 0 ">
            where
            <foreach collection="listData" item="listDataEach" separator=" or ">
                ( id = #{listDataEach.id})
            </foreach>
        </if>
    </update>

dao层只需要使用List和Map集合类型

/**
     * 根据表名获取所有表 所有列
     *
     * @param dbName
     * @param tableName
     * @return 表列名
     */
    List<String> listColName(String dbName, String tableName);




    /**
     * 根据列名和表名查找数据
     *
     * @param tableName   表名
     * @param colNameList 列名
     * @param dataScope   特殊过滤条件
     * @return 表中数据
     */
    List<Map<String, Object>> listDataById(@Param("tableName") String tableName,
                                           @Param("colNameList") List<String> colNameList, @Param("dataScope") String dataScope,
                                           @Param("basicDataList") List<Map<String, Object>> basicDataList);

    /**
     *
     */

    /**
     * 根据表名列名插入数据
     *
     * @param tableName   表名
     * @param colNameList 列名
     * @param listData    表中数据
     * @return
     */
    Integer insertData(@Param("tableName") String tableName, @Param("colNameList") List<String> colNameList,
                       @Param("listData") List<Map<String, Object>> listData);

    
    /**
     * 根据表名列名更新数据
     *
     * @param tableName   表名
     * @param colNameList 列名
     * @param listData    特殊过滤条件
     * @return
     */
    //根据id修改
    Integer updateDataById(@Param("tableName") String tableName, @Param("colNameList") List<String> colNameList,
                           @Param("listData") List<Map<String, Object>> listData);