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()的用法
2.将参数封装到map集合中(不用param.
)
mapper层
List<CloanUserModel> listModel(Map<String, Object> params);
xml
<!-- 基本用户信息的sql查询条件公共引用 -->
<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进行深入研究