Mybatis动态SQL及关联查询实例
动态SQL
实现:select stuno,stuname from student where stuname = #{stuName}and stuage = #{stuAge}
第一种写法:
<select id="queryStuByNOrAWishSQLTag" parameterType="student" resultType="student" >
select stuno,stuname,stuage from student where 1=1
<!--<if test="student有stuName属性 且不为null">,这里这个stuName是类的属性 -->
<if test="stuName !=null and stuName!='' ">
and stuname = #{stuName}
</if>
<if test="stuAge !=null and stuAge!=0 ">
and stuage = #{stuAge}
</if>
</select>
第二种写法:<where>
会自动处理第一个<if>
标签中的 and,但不会处理之后<if>
中的and
<select id="queryStuByNOrAWishSQLTag" parameterType="student" resultType="student" >
select stuno,stuname,stuage from student
<where>
<if test="stuName !=null and stuName!='' ">
and stuname = #{stuName}
</if>
<if test="stuAge !=null and stuAge!=0 ">
and stuage = #{stuAge}
</if>
</where>
</select>
实现:查询学号为1、2、3的学生信息 select stuno,stuname from student where stuno in(1,2,3)
<foreach>
迭代的类型:数组、对象数组、集合、属性(某个类中有属性:List<Integer>之类
)
public class Grade {
private List<Integer> stuNos ;
...
}
<!-- 将多个元素值 放入对象的属性中 -->
<select id="queryStudentsWithNosInGrade" parameterType="grade" resultType="student">
select * from student
<where>
<if test="stuNos!=null and stuNos.size>0">
<foreach collection="stuNos" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
<!-- 将多个元素值 放入数组中 int[] stuNos = {1,2,3} ,传递的是什么参数名(stuNos),在mapper.xml中 必须用array代替该数组-->
<select id="queryStudentsWithArray" parameterType="int[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length">
<foreach collection="array" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
<!-- 将多个元素值 放入数组中 List<Integer> stuNos 值[1,2,3],传递的是什么参数名(stuNos),在mapper.xml中 必须用list代替该数组 -->
<select id="queryStudentsWithList" parameterType="list" resultType="student">
select * from student
<where>
<if test="list!=null and list.size>0">
<foreach collection="list" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{stuNo}
</foreach>
</if>
</where>
</select>
对象数组形式的</foreach>
:
Student[] students = {student0,student1,student2} //每个studentx包含一个学号属性
<!-- 将多个元素值 放入对象数组中Student[] students = {student0,student1,student2} 每个studentx包含一个学号属性 -->
<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
select * from student
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")" item="stuNo" separator=",">
#{student.stuNo}
</foreach>
</if>
</where>
</select>
提取相同相似SQL片段:
<sql id="objectArrayStunos">
<where>
<if test="array!=null and array.length>0">
<foreach collection="array" open=" and stuno in (" close=")"
item="student" separator=",">
#{student.stuNo}
</foreach>
</if>
</where>
</sql>
<!-- 将多个元素值 放入对象数组中Student[] students = {student0,student1,student2} 每个studentx包含一个学号属性 -->
<select id="queryStudentsWithObjectArray" parameterType="Object[]" resultType="student">
select * from student
<!--如果sql片段和 引用处不在同一个文件中,则需要 在refid 引用时 加上namespace: namespace.id
<include refid="org.koma.mapper.XxxMapper.xxxxxxxx"></include> -->
<include refid="objectArrayStunos"></include>
</select>
关联查询
使用业务扩展类实现一对一:
<!--查询结果使用业务扩展类接收 -->
<select id="queryStudentByNoWithOO" parameterType="int" resultType="StudentBusiness" >
select s.*,c.* from student s inner join studentcard c
on s.cardid=c.cardid
where s.stuno = #{stuNo}
</select>
@Data
public class StudentBusiness extends Student{//学生业务扩展类
private int cardId;
private String cardInfo ;
}
使用 resultMap 实现一对一:
<select id="queryStudents" parameterType="int" resultMap="student_card_map">
select c.*,s.* from student s
inner join studentcard c
on s.classid = c.classid
where s.stuno = #{stuNo}
</select>
-->
<!-- 类-表的对应关系 -->
<resultMap type="student" id="student_card_map">
<id property="stuNo" column="stuNo"/>
<result property="stuName" column="stuName"/>
<result property="stuAge" column="stuAge"/>
<!-- 一对一,对象成员使用association映射,javaType指定属性的类型-->
<association property="card" javaType="studentCard">
<id property="cardId" column="cardId"/>
<result property="cardInfo" column="cardInfo"/>
</association>
</resultMap>
@Data //学生类 包含:1学生信息 2学生证信息
public class Student implements Serializable{
//学生信息
private int stuNo ;
private String stuName ;
private int stuAge ;
private String graName ;
private boolean stuSex ;
//学生证信息
private StudentCard card ;//包括俩字段:cardId和cardInfo
}
使用 resultMap 实现一对多:
<!--查询XX班的班级信息,和XX班的所有学生信息 -->
<select id="queryClassAndStudents" parameterType="int" resultMap="class_student_map">
select c.*,s.* from student s
inner join studentclass c
on c.classid = s.classid
where c.classid = #{classId}
</select>
<resultMap type="studentClass" id="class_student_map">
<!-- 因为 type的主类是班级,因此先配置班级的信息-->
<id property="classId" column="classId"/>
<result property="className" column="className"/>
<!-- 配置成员属性学生,一对多; 属性的元素类型 ofType -->
<collection property="students" ofType="student">
<id property="stuNo" column="stuNo"/>
<result property="stuName" column="stuName"/>
<result property="stuAge" column="stuAge"/>
</collection>
</resultMap>
public class StudentClass {
private int classId;//外键
private String className;
//增加学生属性 (通过该字段,让Student类和StudentClass类建立起关联)
List<Student> students ;
}