Mybatis 动态传sql可以查询表名,任意表名,不固定字段的个数返回未定义的类型以及增删改

时间:2022-06-01 17:42:01

mysql查询表名:

SELECT table_name FROM information_schema.tables WHERE table_schema='sell' AND table_type='base table';

查询表中的字段:

SELECT column_name FROM information_schema.columns WHERE table_schema='sell' AND table_name='seller_info'

SpringBoot 中mybatis 使用map的接收返回类型的时候为空的字段 不反回在配置文件中

mybatis.configuration.call-setters-on-nulls=true

今天做项目,遇到的问题就是需求修改数据表的记录,而且字段名都不是固定的,也就是说是需要通过参数传入的,

mybatis动态传表名和字段不规定个数进行条件查询使用map返回

也可以进行动态的表名字段 进行增删改

注意:表名要用${}以为表名不需要预编译

dao层:

  List<Map<String, String>> getTableInfo(@Param("tableName") String tableName, @Param("cloums") List<Cloum> cloums);

    int addTableInfo(@Param("tableName") String tableName, @Param("cloums") List<Cloum> cloums);

    int updateTableINfo(@Param("tableName") String tableName, @Param("cloums") List<Cloum> cloums,
                        @Param("id") String id);

    int delTableInfo(@Param("tableName") String tableName, @Param("ids") List<String> id);

 

 mapper:

 <select id="getTableInfo" resultType="map">
        SELECT * FROM ${tableName} where 1=1
        <if test="cloums != null">
            <foreach collection="cloums" item="item" index="index" open="and" separator="and">
                ${item.cloum}=#{item.val}
            </foreach>
        </if>

    </select>


    <insert id="addTableInfo" parameterType="com.cmbchina.ccd.itpm.entity.Cloum">

        insert into ${tableName}
        <foreach collection="cloums" item="item" index="index" open="(" separator="," close=")">
            ${item.cloum}
        </foreach>
        VALUES
        <foreach collection="cloums" item="item" index="index" open="(" separator="," close=")">
            #{item.val}
        </foreach>
    </insert>
    <delete id="delTableInfo" parameterType="java.lang.String">
        delete from ${tableName}
        where id IN
        <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>
    <update id="updateTableINfo" parameterType="com.cmbchina.ccd.itpm.entity.Cloum">
        update ${tableName}
        set
        <if test="cloums != null">
            <foreach collection="cloums" item="item" index="index" separator=",">
                ${item.cloum}=#{item.val}
            </foreach>
        </if>
        where id = #{id}
    </update>

 

  这里我是使用一个实体类接收字段和字段值进行传参

Cloum实体类:
public class Cloum {
    private String cloum;
    private String val;

    public String getCloum() {
        return cloum;
    }

    public void setCloum(String cloum) {
        this.cloum = cloum;
    }

    public String getVal() {
        return val;
    }

    public void setVal(String val) {
        this.val = val;
    }
}

  

 

 这样就可以了,本人已测试成功!