mybatis 动态sql

最新推荐文章于 2025-08-17 21:38:20 发布

转载最新推荐文章于 2025-08-17 21:38:20 发布 · 63 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/nidegui/p/11084022.html

文章标签：

#java

本文详细解析了MyBatis框架中动态SQL的使用方法，包括if、choose、when、otherwise、where、trim、foreach和set等标签的应用场景及代码示例。通过具体的XML映射文件，展示了如何根据不同条件动态生成SQL语句，实现灵活的数据查询和更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


public interface StudentMapper {
        // if 条件
	public List<Student> searchStudents(Map<String,Object> map);
	
        // choose，when 和 otherwise 条件
	public List<Student> searchStudents2(Map<String,Object> map);
	
        //where 条件
	public List<Student> searchStudents3(Map<String,Object> map);
	 
        //trim 条件
	public List<Student> searchStudents4(Map<String,Object> map);
	
         //foreach 循环
	public List<Student> searchStudents5(Map<String,Object> map);
	
        //set 条件
	public int updateStudent(Student student);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.java1234.mappers.StudentMapper">

	<resultMap type="Student" id="StudentResult">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
	</resultMap>
	
	<select id="searchStudents" parameterType="Map" resultMap="StudentResult">
		select * from t_student 
		 where gradeId=#{gradeId}
		 <if test="name!=null">
		 	and name like #{name}
		 </if>
		 <if test="age!=nulll">
		 	and age=#{age}
		 </if>
	</select>
	
	<select id="searchStudents2" parameterType="Map" resultMap="StudentResult">
		select * from t_student 
		 <choose>
		 	<when test="searchBy=='gradeId'">
		 		where gradeId=#{gradeId}
		 	</when>
		 	<when test="searchBy=='name'">
		 		where name like #{name}
		 	</when>
		 	<otherwise>
		 		where age=#{age}
		 	</otherwise>
		 </choose>
		 
	</select>
	
	<select id="searchStudents3" parameterType="Map" resultMap="StudentResult">
		select * from t_student 
		 <where>
			 <if test="gradeId!=null">
			 	gradeId=#{gradeId}
			 </if>
			 <if test="name!=null">
			 	and name like #{name}
			 </if>
			 <if test="age!=nulll">
			 	and age=#{age}
			 </if>
		 </where>
	</select>
	
	<select id="searchStudents4" parameterType="Map" resultMap="StudentResult">
		select * from t_student 
		 <trim prefix="where" prefixOverrides="and|or">
			 <if test="gradeId!=null">
			 	gradeId=#{gradeId}
			 </if>
			 <if test="name!=null">
			 	and name like #{name}
			 </if>
			 <if test="age!=nulll">
			 	and age=#{age}
			 </if>
		 </trim>
	</select>
	
	<select id="searchStudents5" parameterType="Map" resultMap="StudentResult">
		select * from t_student 
		 <if test="gradeIds!=null">
		 	<where>
		 		gradeId in 
		 		<foreach item="gradeId" collection="gradeIds" open="(" separator="," close=")">
		 		 #{gradeId}
		 		</foreach>
		 	</where>
		 </if>
	</select>
	
	<update id="updateStudent" parameterType="Student">
		update t_student
		<set>
		 <if test="name!=null">
		 	name=#{name},
		 </if>
		 <if test="age!=null">
		 	age=#{age},
		 </if>
		</set>
		where id=#{id}
	</update>
</mapper>

转载于:https://www.cnblogs.com/nidegui/p/11084022.html