-
-
动态SQL
-
作用:根据传递的数据值,来拼接不同的SQL语句
-
SQL动态标签
- if标签
-
/** * 功能: 根据用户名或住址查询所有男性用户 * @param username * @param address * @return 符合要求的用户集合 */ List<User> findUsersByUserNameAndAddress(@Param("username") String username, @Param("address") String address);
-
<select id="findUsersByUserName" resultType="User"> select id, username, birthday, sex, address from user where sex = '男' <if test="username != null"> and username like concat('%', #{username}, '%'); </if> </select>
-
特点:属于单支判断
- choose标签
-
特点:多支判断(多个条件)
-
/** * 功能: 查询男性用户,如果输入了用户名,按用户名模糊查询,如果没有输入用户名,就查询所有男性用户 * * 注意: 不加 @Param("username") 会出错 * @param username * @return 符合要求的用户集合 */ List<User> findUsersByUserName(@Param("username") String username);
-
<select id="findUsersByUserNameAndAddress" resultType="User"> select id, username, birthday, sex, address from user where sex = '男' <choose> <when test="username != null"> and username like concat('%', #{username}, '%') </when> <when test="address != null"> and address = #{address} </when> <otherwise> and username = '孙悟空' </otherwise> </choose> </select>
- where标签
-
能够添加where关键字
-
能够去除多余的and或者or关键字
-
<select id="findByNameAndAddress" resultMap="User"> select id , user_name , birthday , sex ,address from user <where> <if test="name != null"> AND user_name LIKE concat('%' , #{name} , '%') </if> <if test="address != null"> AND address = #{address} </if> </where> </select>
-
set标签
-
update set 字段1=新值, 字段2=新值, 字段3=新值, 字段4=新值, where 条件 1、自动添加set关键字 2、去除多余的逗号
-
<update id="updateSelectiveUser" parameterType="User"> UPDATE user <set> <if test="username != null"> user_name = #{username}, </if> <if test="birthday != null"> birthday = #{birthday}, </if> <if test="sex != null"> sex = #{sex}, </if> <if test="address != null"> address = #{address}, </if> </set> WHERE id = #{id} </update>
-
- foreach标签
-
集合
-
List<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(5); ids.add(7); for(Integer id : ids){ }
-
-
foreach标签
-
/** * 功能: 查询数组中的用户,id * @param ids * @return */ public List<User> findUserByIds(@Param("ids") List<Integer> ids);
-
<select id="findUserByIds" resultType="User"> select id, username, birthday, sex, address from user <where> id IN <foreach collection="ids" separator="," item="id" open="(" close=")"> #{id} </foreach> </where> </select>
-
<!-- collection属性:接收的集合或者数组,集合名或者数组名 item属性:集合或者数组参数中的每一个元素 separator属性:标签分隔符 open属性:以什么开始 close属性:以什么结束 --> <foreach collection="ids" item="id" > </foreach>
-
-
SQL查询:select * from user where id IN ( 1, 2, 3, 4, 5 )
-
foreach标签
<foreach collection="ids" item="id" separator="," open="(" close=")"> </foreach>
-
-
-
-
Mybatis_动态SQL
![](https://img-home.csdnimg.cn/images/20240711042549.png)