相关的环境搭建和配置上一篇有说到,这里直接进入主题说说进阶查询。
数据库的表是简单的学生(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>}
}
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();
}
}
}