mybatis的查询sql，参数为map

古城微笑少年丶 2022-05-27 09:45 179阅读 0赞

#### 1.将参数封装到map集合中（用`param.`） ####

service层

String tableName = "userData";
    Map<String,Object> params = new HashMap<>();
    params.put("userId", userId);
    params.put("name", name);
    List<UserData> list = userDataMapper.listSelective(tableName, params);

mapper层

List<UserData> listSelective(
                @Param("tableName") String tableName,
                @Param("params") Map<String, Object> params);

xml中

<select id="listSelective" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List" />
            from ${tableName} 
            <trim prefix="where" prefixOverrides="and|or">
                <if test="params.userId != null">
                    user_id = #{params.userId,jdbcType=BIGINT}
                </if>
                <if test="params.name !=null and params.name !='' ">
                    and name like concat("%",#{params.name, jdbcType=VARCHAR},"%")
                </if>
            </trim>
        </select>

> 1.传表名使用`$`  
> 2.`concat`拼接字符串,[mysql中concat 和 group\_concat()的用法][mysql_concat _ group_concat]

#### 2.将参数封装到map集合中(不用`param.`) ####

mapper层

List<CloanUserModel> listModel(Map<String, Object> params);

xml

<sql id="searchBaseBy">
            <trim prefix="where" prefixOverrides="and|or">
                <if test="loginName !='' and loginName !=null">
                    and u.login_name like concat("%",#{loginName,jdbcType=VARCHAR},"%")  
                   </if>
                <if test="registTime !=null">
                    and DATE_FORMAT(u.regist_time,'%Y-%m-%d') = #{registTime,jdbcType=TIMESTAMP}
                </if>
                <if test="startTime != null">
                    and DATE_FORMAT(u.regist_time,'%Y-%m-%d') >= #{startTime,jdbcType=TIMESTAMP}
                </if>
                <if test="endTime != null">
                    and DATE_FORMAT(u.regist_time,'%Y-%m-%d') <= #{endTime,jdbcType=TIMESTAMP}
                </if>
            </trim>
        </sql>
    
      <select id="listModel" resultMap="BaseInfoModelMap" parameterType="java.util.HashMap">
            select u.id,u.login_name,a.real_name,
            (SELECT c.used FROM arc_credit c where c.consumer_no = u.id)  as used
            from cl_user u left join cl_user_base_info a on u.id = a.user_id
            LEFT JOIN cl_user_auth b ON a.user_id = b.user_id
            <include refid="searchBaseBy"/>
            order by u.id desc
        </select>

先暂时写这两种，至于foreach使用，日后补充，可以参考[基于mysql对mybatis中的foreach进行深入研究][mysql_mybatis_foreach]

[mysql_concat _ group_concat]: http://www.cnblogs.com/zhming26/p/6382995.html
[mysql_mybatis_foreach]: https://www.cnblogs.com/shihuc/p/6928650.html