mybatis动态查询

在实际的开发当中,经常需要根据不同条件拼接SQL语句

常用的动态SQL元素包括

if,choose(when,otherwise,where,set,foreach,bind

1.if

<mapper namespace="org.taohan.online.exam.dao" 
                resultType="org.taohan.online.StudentInfo">
select * from  tb_student where state='ACTIVE'
<!--可选条件，如果传进来的参数里面有id属性则加入id查询条件-->
<if test="id！=null">
and id=#{id}
</if>
<select>
</mapper>                

 如果有多个可选条件使用and来连接‘

<if test="id！=null" and lognname!=null>
    and id=#{id}  and loginname=#{loginname}
</if>

 利用map传入值

有如下数据接口

public List<StudentInfo> getStudents(Map<String, Object> map);

 对应的实现如下

<!-- 获取学生集合 -->
<select id="getStudents" parameterMap="getStudentParameterMap"      resultMap="queryStudent">
</select>

 加入多表连接

<!--多表连接-->
SELECT a.*,b.className, c.gradeName FROM StudentInfo a
INNER JOIN ClassInfo b ON a.classId=b.classId
INNER JOIN GradeInfo c ON b.gradeId=c.gradeId
		

 加入动态查询

通过map传入数据

StudentInfo student = new StudentInfo();
if (studentId != null)
student.setStudentId(studentId);
if (classId != null) {
classInfo.setClassId(classId);
student.setClassInfo(classInfo);
}
if (gradeId != null) {
grade.setGradeId(gradeId);
student.setGrade(grade);
}

 存入集合中

map.put("student", student);//学生对象
map.put("startIndex", startIndex);//默认是0
map.put("pageShow", pageShow);//默认是10

测试

<!-- mybatis动态查询 -->
		<where>
		<if test="studentId != null">
			studentId=#{studentId}
			</if>
                       <if test="classInfo != null">
			      <if test="classInfo.classId != null">
			           b.classId=#{classInfo.classId}
			      </if>
			</if>
                       <if test="grade != null">
				<if test="grade.gradeId != null">
				c.gradeId=#{grade.gradeId}
			</if>
			</if>
		</where>
		<!-- 传入的map数据 -->
		<if test="startIndex != null and pageShow != null">
			LIMIT #{startIndex}, #{pageShow}
		</if>

 2.choose(when，otherwise)

有时候,我们不想用所有的条件语句,而只是只想从中选择一个，mybatis提供choose,类似于switch语句

<mapper namespace="org.taohan.online.exam.dao" >
<select id="query"  resultType="org.taohan.online.StudentInfo">
select * from  tb_student where state='ACTIVE'
<!--如果传入了id,就根据id查询,如果传入了loginname和password就根据这些来查询,如果都没有就使用性别为男性来查找-->
<choose>
<when test="id!=null">
and id=#{id}
</when>
<when test="loginname!=null and password!=null">
and loginname=#{loginname} and password=#{password}
</when>
<otherwise>
 and sex='男'
</otherwise>       
</choose>
</select>
</mapper> 

 3.where

<mapper namespace="org.taohan.online.exam.dao" 
                resultType="org.taohan.online.StudentInfo">
select * from  tb_student 
<where>
<if test="state!=null">
state=#{state}
</if>
<if test="id！=null">
and id=#{id}
</if>
</where>
<select>
</mapper>  

 where元素知道只有一个以上的if条件有值的情况下才去插入where子句,若有“AND"或者"OR"开头,则where元素也会去将他们删除

4.set

<update id="update">
  update  tb_user
    <set>
     <if test="loginname!=null">
           loginname=#{login}
      </if>
      <if test="password!=null">
         password=#{password}
      </if>
      </set>
   where id=#{id}
</update>

 set元素会动态设置前置的set关键字,同时也会消除无关的逗号

5.foreach

6.bind

<select >
<bind name="pattern" value="'%"+_parameter.getName()+‘%’"/>
select * from tb_user
where loginname like #{pattern}
</select>

 从OGNL表达式中创建一个变量并将其绑定在上下文
select * from tb_user where loginname like '%_Parameter.getName()%'