mybatis传入map任意表增删改查，分页过滤字段

原创已于 2025-01-27 11:08:25 修改 · 76 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java #mysql #开发语言 #数据库

于 2019-09-21 17:23:00 首次发布

本文介绍了一个使用MyBatis进行动态SQL查询、插入、更新和分页的示例。通过参数化的map集合，实现了根据实体参数动态构建SQL语句的功能。包括了根据条件查询数据、统计查询结果数量、插入新记录、更新现有记录和实现分页功能的具体实现。

<!--根据实体参数查询 -->
    <select id="selectBaseList" resultType="java.util.HashMap">
        select
        *
        from ${map.tableName}
        where 1=1
        <foreach collection="map" index="key" item="value">
            <if
                test="key != 'tableName' and key != 'pageSize' and key != 'pageNo' ">
                and `${key}` = #{value}
            </if>
        </foreach>
        limit #{baseModel.fromRec},#{baseModel.pageSize}
    </select>

    <!--根据实体参数查询个数 -->
    <select id="selectBaseCount" resultType="java.lang.Integer">
        select
        count(1)
        from ${map.tableName}
        where 1=1
        <foreach collection="map" index="key" item="value">
            <if
                test="key != 'tableName' and key != 'pageSize' and key != 'pageNo' ">
                and  `${key}` = #{value}
            </if>
        </foreach>
    </select>


    <insert id="insertBase" parameterType="java.util.HashMap">
        insert into ${map.tableName}
        (
        <foreach collection="map" index="key" item="value"
            separator=",">
            <if test="key != 'tableName' ">
                `${key}`
            </if>
        </foreach>
        )
        values (
        <foreach collection="map" index="key" item="value"
            separator=",">
            <if test="key != 'tableName' ">
                #{value}
            </if>
        </foreach>
        )
    </insert>
    <update id="updateBaseByPrimaryKey">
        update ${map.tableName}
        <set>
            <foreach collection="map" index="key" item="value"
                separator=",">
                <if test="key != 'tableName' ">
                    `${key}`= #{value}
                </if>
            </foreach>
        </set>
        where id = #{map.id}
    </update>

java代码部分，baseModel中放入分页参数：

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;


public interface BaseDao {
    int insertBase(@Param("map") Map map);

    Map selectBaseByPrimaryKey(Long id);

    int updateBaseByPrimaryKey(@Param("map") Map map );

    List<Map> selectBaseList(@Param("map") Map map, @Param("baseModel") BaseModel baseModel);

    int selectBaseCount(@Param("map") Map map);

}