MyBatis-高级查询

相关的环境搭建和配置上一篇有说到，这里直接进入主题说说进阶查询。

数据库的表是简单的学生（student），课程（course），成绩（sc），这里建立三个pojo对象

student

<pre name="code" class="html">
import java.util.List;

public class Student {
	private int id;
	private String name;
	private Integer age;
	private int sex;
	private List<Course> courses;
	public int getId() {
		return id;
	}
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Student(String name, Integer age) {
		super();
		this.name = name;
		this.age = age;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSex() {
		return sex;
	}
	public void setSex(int sex) {
		this.sex = sex;
	}
	public List<Course> getCourses() {
		return courses;
	}
	public void setCourses(List<Course> courses) {
		this.courses = courses;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age
				+ ", sex=" + sex + ", courses=" + courses + "]";
	}
	

}

course.java

import java.util.List;


public class Course {
<span style="white-space:pre">	</span>private int id;
<span style="white-space:pre">	</span>private String name;
<span style="white-space:pre">	</span>private List<Student> students;
<span style="white-space:pre">	</span>public int getId() {
<span style="white-space:pre">		</span>return id;
<span style="white-space:pre">	</span>}
<span style="white-space:pre">	</span>public void setId(int id) {
<span style="white-space:pre">		</span>this.id = id;
<span style="white-space:pre">	</span>}
<span style="white-space:pre">	</span>public String getName() {
<span style="white-space:pre">		</span>return name;
<span style="white-space:pre">	</span>}
<span style="white-space:pre">	</span>public void setName(String name) {
<span style="white-space:pre">		</span>this.name = name;
<span style="white-space:pre">	</span>}
<span style="white-space:pre">	</span>public List<Student> getStudents() {
<span style="white-space:pre">		</span>return students;
<span style="white-space:pre">	</span>}
<span style="white-space:pre">	</span>public void setStudents(List<Student> students) {
<span style="white-space:pre">		</span>this.students = students;
<span style="white-space:pre">	</span>}


}

sc.java

public class SC {
	private int id;
	private Student student;
	private Course course;
	private float score;
	public int getId() {
		return id;
	}
	public float getScore() {
		return score;
	}
	public void setScore(float score) {
		this.score = score;
	}
	public void setId(int id) {
		this.id = id;
	}
	public Student getStudent() {
		return student;
	}
	public void setStudent(Student student) {
		this.student = student;
	}
	public Course getCourse() {
		return course;
	}
	public void setCourse(Course course) {
		this.course = course;
	}
}

student.xml

<?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="/">
	<insert id="addStudent" statementType="PREPARED" parameterType="Student">
		insert into Student(name,age,sex) values(#{name},#{age},#{sex})
	</insert>

	<resultMap type="Student" id="resultStu">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="sex" column="sex"/>
		<result property="age" column="age"/>	
	</resultMap>

	<select id="queryStuById" parameterType="int" resultMap="resultStu">
		select * from Student where id=#{id}
	
	</select>
	
	<resultMap type="Student" id="stuCourseMap">
		<id property="id" column="student.id"/>
		<result property="name" column="name"/>
		<collection property="courses" column="course.id" javaType="ArrayList" ofType="Course">
			<result property="id" column="id"/>
			<result property="name" column="name"/>
		</collection>
	</resultMap>
	
	<!-- 集合查询 -->
	<select id="queryStuCourseById" parameterType="int" resultMap="stuCourseMap">
		select * from student s,SC sc where s.id=sc.sid and s.id=#{sid}
	
	</select>
	
	
	
	

</mapper>

sc.xml

<?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="/">
	<insert id="addSC" parameterType="SC" statementType="PREPARED" keyProperty="id" useGeneratedKeys="true">
		insert into sc(sid,cid,score) values(#{student.id},#{course.id},#{score})	
	</insert>
	
	<!-- 关联查询 -->
	<resultMap type="SC" id="SCMap">
		<id property="id" column="id"/>
		<result property="score" column="score"/>
		<!-- association指明关联的实体，property是sc.student的属性值，column是sc表的字段， javaType是实体的对象类型，这里用了别名-->
		<association property="student" column="sid" javaType="Student">
			<id property="id" column="sid"/>
			<result property="name" column="name"/>
			<result property="sex" column="sex"/>
			<result property="age" column="age"/>
		</association>
		<association property="course" column="cid" javaType="Course">
			<id property="id" column="cid"/>
			<result property="name" column="name"/>
		</association>
	
	</resultMap>
	<!-- 构造查询 -->
	<resultMap type="SC" id="SCMap2">
		<id property="id" column="id"/>
		<result property="score" column="score"/>
		<association property="student" column="sid" javaType="Student">
			<!-- 封装制定的构造函数，pojo类要有对应的构造函数，pojo的属性要用数据类型才行，不然会找不到构造方法 -->
			<constructor>
				<arg column="name" javaType="String"/>
				<arg column="age" javaType="Integer"/>
			</constructor>
		</association>
	</resultMap>
	
	
	
	<!-- 关联查询，返回SCMap，注意这里如果只是返回resultType=SC是无法得到关联对象的值的 -->
	<select id="querySCBySid" parameterType="int" resultMap="SCMap2">
		select * from sc,student where sc.sid=student.id and sid=#{sid}
	</select>


	<resultMap type="SC" id="SCMap3">
		<id property="id" column="id"/>
		<result property="score" column="score"/>
		<association property="student" javaType="Student" column="sid" select="queryStuById"></association>
	</resultMap>

	<!-- 子查询 -->
	<select id="querySCBySid2" parameterType="int" resultMap="SCMap3">
		select * from sc
	</select>
	
</mapper>

测试类

import java.io.IOException;
import java.io.Reader;

import jike.book.pojo.Student;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


public class TestSC {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String resource = "jike/book/map/MyBatisConfig.xml";
		Reader reader = null;
		SqlSession session;
		try {
			reader = Resources.getResourceAsReader(resource);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		SqlSessionFactory sqlMapper = new SqlSessionFactoryBuilder()
				.build(reader);
		session = sqlMapper.openSession();
		try{
			/*Student student=new Student();
			student.setName("linxiaosheng");
			student.setAge(22);
			student.setSex(1);
			session.insert("addStudent", student);*/
			/*Student student=session.selectOne("queryStuById",new Integer(1));
			System.out.println(student.toString());*/
			
			
			/*SC sc=new SC();
			Student student=new Student();
			student.setId(1);
			Course course=new Course();
			course.setId(1);
			sc.setStudent(student);
			sc.setCourse(course);
			sc.setScore(88);
			session.insert("addSC",sc);*/
			
			/*SC sc=session.selectOne("querySCBySid", new Integer(1));
			System.out.println(sc.getStudent().toString());*/
			//子查询
			/*List<SC> sc=session.selectList("querySCBySid2", new Integer(1));
			for(SC s:sc){
				System.out.println(s.getScore());
				System.out.println("懒加载！！");
				System.out.println(s.getStudent().getName());
			}*/
			Student student=session.selectOne("queryStuCourseById", new Integer(1));
			System.out.println(student.toString());
			session.commit();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			session.close();
		}
	}

}