[大数据]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 ;
}