Talking is Cheap,Show Me the Code
引子
之前無(wú)論是使用實(shí)體類映射文件的方式,還是使用注解的方式悔捶,都只是對(duì)單個(gè)表進(jìn)行的增刪改查的操作捂襟。但在實(shí)際項(xiàng)目中,經(jīng)常是關(guān)聯(lián)表的查詢,比如:最常見(jiàn)到的多對(duì)一,一對(duì)多,多對(duì)多等践剂。這些查詢是要如何進(jìn)行呢,MyBatis要怎么進(jìn)行支持的呢——這次我學(xué)習(xí)了多個(gè)表之間的關(guān)聯(lián)查詢娜膘。
過(guò)程
對(duì)于多個(gè)表之間的關(guān)聯(lián)查詢逊脯,學(xué)習(xí)主要分為三個(gè)部分——一對(duì)多,多對(duì)一竣贪,多對(duì)多
一對(duì)多
在現(xiàn)實(shí)中军洼,實(shí)體類一對(duì)多的場(chǎng)景有很多,我們隨便抽象一個(gè):一個(gè)人和他發(fā)送的朋友圈時(shí)間的關(guān)系贾富。(一個(gè)人可以發(fā)送多條朋友圈歉眷,但是每條朋友圈只對(duì)應(yīng)一個(gè)發(fā)送人)
- 準(zhǔn)備工作
首先我們需要?jiǎng)?chuàng)建兩個(gè)實(shí)體類對(duì)應(yīng)的表,不妨命名為person表和post表,建表的語(yǔ)句大致如下颤枪,并且插入幾條數(shù)據(jù)
CREATE TABLE [person] (
[id] int NOT NULL PRIMARY KEY,
[name] varchar(255) NOT NULL,
[mobile] varchar(255) NOT NULL,
[create] datetime DEFAULT ('0000-00-00 00:00:00') NOT NULL
);
CREATE TABLE [post] (
[id] int NOT NULL PRIMARY KEY,
[pid] int NOT NULL,
[title] varchar(255) NOT NULL,
[time] datetime DEFAULT ('0000-00-00 00:00:00') NOT NULL
);
- 創(chuàng)建實(shí)體類
需要?jiǎng)?chuàng)建的實(shí)體類分別是Person和Post汗捡,屬性與表屬性一保持一致
//person實(shí)體類
public class Person {
private int id;
private String name;
private String mobile;
private String createTime;
private List<Post> posts;
//此處省略setter和getter方法
}
//post實(shí)體類
public class Post {
private int id;
private int pid;
private String title;
private String createTime;
private Person person;
//此處省略setter和getter方法
}
- 創(chuàng)建實(shí)體類配置文件
在Java實(shí)體對(duì)象對(duì)中,一對(duì)多可以根據(jù)List和Set來(lái)實(shí)現(xiàn)畏纲,兩者在MyBatis中都是通過(guò)collection標(biāo)簽來(lái)配合來(lái)加以實(shí)現(xiàn)扇住。
<?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="com.bean.personMapper">
<!-- Person 級(jí)聯(lián)朋友圈查詢 方法配置 (一個(gè)人對(duì)多條朋友圈) -->
<resultMap type="Person" id="resultPersonMap">
<result property="id" column="id" />
<result property="name" column="name" />
<result property="mobile" column="mobile" />
<result property="createTime" column="create"/>
<collection property="posts" ofType="com.bean.Post" column="pid">
<result property="id" column="id" javaType="int" jdbcType="INTEGER"/>
<result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
<result property="createTime" column="time" javaType="string" jdbcType="VARCHAR"/>
</collection>
</resultMap>
<select id="getPerson" resultMap="resultPersonMap" parameterType="int">
SELECT per.*,p.*
FROM person per, post p
WHERE per.id=p.pid AND per.id=#{id}
</select>
</mapper>
從配置文件中,可以看到這次所作的查詢其實(shí)只有一個(gè)——id為getPerson的一個(gè)查詢盗胀,傳入的參數(shù)是用戶的id然后聯(lián)合查詢獲取到結(jié)果——resultPersonMap艘蹋,這個(gè)就是之前resultMap標(biāo)簽聲明的結(jié)果集,結(jié)果集的屬性(也就是實(shí)體類的屬性)和數(shù)據(jù)表一一對(duì)應(yīng)票灰,而collection標(biāo)簽對(duì)應(yīng)的是通過(guò)pid(用戶id)查詢出來(lái)的朋友圈記錄女阀。
- 創(chuàng)建項(xiàng)目配置文件
創(chuàng)建Configuration.xml文件宅荤,聲明實(shí)體類映射文件和數(shù)據(jù)庫(kù)連接的參數(shù)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--聲明實(shí)體類-->
<typeAliases>
<typeAlias alias="Person" type="com.bean.Person" />
<typeAlias alias="Post" type="com.bean.Post" />
</typeAliases>
<!--聲明數(shù)據(jù)庫(kù)連接參數(shù)-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<property name="url" value="jdbc:sqlserver://localhost:1433;DatabaseName=ssm" />
<property name="username" value="sa" />
<property name="password" value="xiaogezzZ" />
</dataSource>
</environment>
</environments>
<!--實(shí)體類映射文件聲明-->
<mappers>
<mapper resource="com/bean/Person.xml" />
</mappers>
</configuration>
- 測(cè)試類
public class Main {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("config/Configure.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSession() {
return sqlSessionFactory;
}
public static void main(String[] args) {
// TODO Auto-generated method stub
SqlSession session = sqlSessionFactory.openSession();
try {
int id = 1;
Person person = session.selectOne("com.bean.personMapper.getPerson", id);
System.out.println("Name: " + person.getName());
System.out.println("Mobile: " + person.getMobile());
List<Post> posts = person.getPosts();
for(Post p : posts) {
System.out.println("----***----");
System.out.println("Title: " + p.getTitle());
System.out.println("PieceCreateTime: " + p.getCreateTime());
}
} finally {
session.close();
}
}
}
-
運(yùn)行結(jié)果
-
注意點(diǎn)
在實(shí)體類映射文件中,查詢語(yǔ)句中id需要指定明確浸策,不然會(huì)報(bào)下面的錯(cuò)誤
在創(chuàng)建表的時(shí)候需要注意冯键,兩個(gè)表盡量不要有相同名稱的屬性,不然在結(jié)果中出現(xiàn)的結(jié)果和實(shí)際結(jié)果不一致庸汗。比如惫确,我們把post表的time屬性改名和person表中的create屬性相同,都為create蚯舱,那么根據(jù)表屬性改化,實(shí)體類映射文件中Collection對(duì)應(yīng)的寫(xiě)法應(yīng)該為
<collection property="posts" ofType="com.bean.Post" column="pid">
<result property="id" column="id" javaType="int" jdbcType="INTEGER"/>
<result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
<result property="createTime" column="create" javaType="string" jdbcType="VARCHAR"/>
</collection>
這樣的寫(xiě)法按照表結(jié)構(gòu)和實(shí)體類來(lái)說(shuō)都是沒(méi)問(wèn)題的,但是一運(yùn)行枉昏,結(jié)果會(huì)是這樣我們發(fā)現(xiàn)每條朋友圈的創(chuàng)建時(shí)間都是一樣的陈肛,而且與實(shí)際創(chuàng)建時(shí)間不符合——原因是,我們盡管按照表結(jié)構(gòu)和實(shí)體類屬性來(lái)對(duì)column賦值凶掰,但這樣會(huì)讓程序運(yùn)行過(guò)程中與person表中的create屬性相混淆燥爷,導(dǎo)致得到的這個(gè)值是查詢的person的創(chuàng)建時(shí)間。
多對(duì)一
在一對(duì)多的情形中懦窘,一個(gè)人對(duì)應(yīng)多條朋友圈——反過(guò)來(lái)即是多對(duì)一的情形。根據(jù)朋友圈的發(fā)送人的id稚配,查找到對(duì)應(yīng)的這個(gè)人員的信息畅涂。
- 實(shí)體類映射文件
因?yàn)槭嵌鄬?duì)一的情形,一條朋友圈與人員之間的關(guān)系是關(guān)聯(lián)關(guān)系道川,因此要修改為association標(biāo)簽
<?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="com.bean.personMapper">
<!-- Post 級(jí)聯(lián)朋友圈查詢 方法配置 (一條(或多條)朋友圈對(duì)應(yīng)一個(gè)人員) -->
<resultMap type="Post" id="resultPostsMap">
<result property="id" column="id" />
<result property="title" column="title" />
<result property="createTime" column="create"/>
<association property="person" javaType="Person">
<id property="id" column="pid"/>
<result property="name" column="name"/>
<result property="mobile" column="mobile"/>
</association>
</resultMap>
<select id="getPosts" resultMap="resultPostsMap" parameterType="int">
SELECT per.*,p.*
FROM person per, post p
WHERE per.id=p.pid AND p.id=#{id}
</select>
</mapper>
- 測(cè)試類修改
public static void main(String[] args) {
// TODO Auto-generated method stub
SqlSession session = sqlSessionFactory.openSession();
try {
int id = 1;
Post post = session.selectOne("com.bean.personMapper.getPosts", id);
System.out.println("title: "+post.getTitle());
System.out.println("PersonName: "+post.getPerson().getName());
System.out.println("PersonMobile: "+post.getPerson().getMobile());
} finally {
session.close();
}
}
多對(duì)多
MyBatis3.0 添加了association和collection標(biāo)簽專門(mén)用于對(duì)多個(gè)相關(guān)實(shí)體類數(shù)據(jù)進(jìn)行級(jí)聯(lián)查詢午衰,之前學(xué)習(xí)的一對(duì)多,多對(duì)一情形便是其中之一冒萄。但仍不支持多個(gè)相關(guān)實(shí)體類數(shù)據(jù)的級(jí)聯(lián)保存和級(jí)聯(lián)刪除操作臊岸。因此在進(jìn)行實(shí)體類多對(duì)多映射表設(shè)計(jì)時(shí),需要專門(mén)建立一個(gè)關(guān)聯(lián)對(duì)象類對(duì)相關(guān)實(shí)體類的關(guān)聯(lián)關(guān)系進(jìn)行描述尊流。
考慮這樣的一個(gè)情形:大學(xué)里一個(gè)學(xué)生選修多門(mén)課程帅戒,而一門(mén)課程被多個(gè)學(xué)生選修。這樣學(xué)生與課程之間就構(gòu)成了多對(duì)多的關(guān)系崖技。
- 創(chuàng)建student表和course表,插入幾條數(shù)據(jù)
create table student(
sid int not null PRIMARY key,
sname varchar(255) not null,
mobile VARCHAR(255) not null DEFAULT '');
CREATE TABLE course(
cid int not NULL PRIMARY key,
cname VARCHAR(255) not NULL);
- 創(chuàng)建學(xué)生課程映射表
create table student_course(
sid int not null,
cid int not null)
insert into student_course values(1,1)
insert into student_course values(1,2)
insert into student_course values(2,1)
insert into student_course values(2,3)
insert into student_course values(3,2)
insert into student_course values(3,3)
根據(jù)隨意插入的數(shù)據(jù)可知逻住,Tom選了ComputerScience和Art兩門(mén)課,Jack選了ComputerScience和Java兩門(mén)課迎献,Sarah選了Art和Java兩門(mén)課瞎访;反過(guò)來(lái)說(shuō),選了CompterScience的有Tom與Jack吁恍,選了Art的有Tom與Sarah扒秸,選了Java的有Jack與Sarah
- 創(chuàng)建實(shí)體類
Student實(shí)體類
public class Student {
private int sid;
private String sname;
private String mobile;
private List<Course> courses;
//此處省略getter和setter方法
}
Course實(shí)體類
public class Course {
private int cid;
private String cname;
private List<Student> students;
//此處省略getter和setter方法
}
StudentCourse實(shí)體類
public class StudentCourse {
private int sid;
private int cid;
//此處省略getter和setter方法
}
- 創(chuàng)建接口映射類
因?yàn)槲覀兛赡軙?huì)不只是查詢播演,可能還會(huì)有刪除、插入等相關(guān)操作伴奥,若通過(guò)命名空間來(lái)調(diào)用相關(guān)方法不免太過(guò)繁瑣宾巍,使用接口映射類,將要使用的方法聲明渔伯,在使用的時(shí)候可以直接調(diào)用顶霞。
StudentMapper映射類
public interface StudentMapper {
//@Insert("insert into student(sid,name,mobile) values(#{student.getSid()}, #{student.getSName()},#{student.getMobile()})")
public void insertStudent(Student student);
//@Select("select * from student where sid = #{sid}")
public Student getStudent(int sid);
//@Select("select * from student")
public List<Student> getAllStudents();
}
CourseMapper映射類
public interface CourseMapper {
//@Insert("insert into course(cid,cname) values(#{course.getCid()},#{course.getCname()})")
public void insertCourse(Course course);
//@Select("select * from course where cid=#{cid}")
public Course getCourse(int cid);
//@Select("select * from course")
public List<Course> getAllCourses();
}
StudentCourseMapper映射類
public interface StudentCourseMapper {
//@Insert("insert into student_course(sid,cid) values(#{studentCourse.getSid()},#{studentCourse.getCid()})")
public void insertStudentCourse(StudentCourse studentCourse);
//@Select("select * from student where sid in (select sid from student_course)")
public List<Student> getStudents();
}
這里因?yàn)槭褂玫氖怯成湮募宰⒔舛急蛔⑨屃寺嗪稹A硗庋』耄绻胍砑悠渌姆椒ǎ热缯f(shuō)是刪除玄叠、更新等古徒,都可以在對(duì)應(yīng)的映射類中進(jìn)行聲明。
- 映射文件
Student映射
<?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="com.maper.StudentMapper">
<parameterMap type="Student" id="parameterStudentMap">
<parameter property="sid"/>
<parameter property="sname"/>
<parameter property="mobile"/>
</parameterMap>
<insert id="insertStudent" parameterMap="parameterStudentMap">
insert into student(sid,sname,mobile)
values(#{sid},#{sname},#{mobile});
</insert>
<resultMap type="Student" id="resultStudent">
<result property="sid" column="sid"/>
<result property="sname" column="sname"/>
<collection property="courses" column="sid" select="com.maper.StudentCourseMapper.getCoursesByStudentId"/>
</resultMap>
<select id="getStudent" resultMap="resultStudent" parameterType="int">
SELECT *
FROM student
WHERE sid=#{sid}
</select>
<select id="getAllStudents" resultMap="resultStudent">
select *
from student;
</select>
</mapper>
Course映射
<?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="com.maper.CourseMapper">
<parameterMap type="Course" id="parameterCourseMap">
<parameter property="cid"/>
<parameter property="cname"/>
</parameterMap>
<insert id="insertCourse" parameterMap="parameterCourseMap">
insert into course (cid,cname)
VALUES(#{cid},#{cname});
</insert>
<resultMap type="Course" id="resultCourseMap">
<result property="cid" column="cid" />
<result property="cname" column="cname" />
<collection property="students" column="cid" select="com.maper.StudentCourseMapper.getStudentsByCourseId" />
</resultMap>
<select id="getCourse" resultMap="resultCourseMap" parameterType="int">
SELECT *
FROM course
WHERE cid=#{cid}
</select>
<select id="getAllCourses" resultMap="resultCourseMap">
select * from course
</select>
</mapper>
StudentCourse映射
<?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="com.maper.StudentCourseMapper">
<parameterMap type="StudentCourse" id="parameterStudentCourseMap">
<parameter property="sid"/>
<parameter property="cid"/>
</parameterMap>
<insert id="insertStudentCourse" parameterMap="parameterStudentCourseMap">
INSERT INTO student_course(sid, cid)
VALUES(#{sid},#{cid})
</insert>
<resultMap type="Student" id="resultStudentMap_2">
<result property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="mobile" column="mobile"/>
</resultMap>
<select id="getStudentsByCourseId" resultMap="resultStudentMap_2" parameterType="int">
SELECT s.*, sc.cid
FROM student s, student_course sc
WHERE s.sid=sc.sid AND sc.cid=#{cid}
</select>
<resultMap type="Course" id="resultCourseMap_2">
<result property="cid" column="cid"/>
<result property="cname" column="cname"/>
</resultMap>
<select id="getCoursesByStudentId" resultMap="resultCourseMap_2" parameterType="int">
SELECT c.*, sc.sid
FROM course c, student_course sc
WHERE c.cid=sc.cid AND sc.sid=#{sid}
</select>
</mapper>
- 測(cè)試類
import com.bean.Course;
import com.bean.Student;
import com.bean.StudentCourse;
import com.maper.CourseMapper;
import com.maper.StudentCourseMapper;
import com.maper.StudentMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.Reader;
import java.util.List;
public class Main {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
static {
try {
reader = Resources.getResourceAsReader("config/Configure.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getSession() {
return sqlSessionFactory;
}
/**
* @param args
*/
public static void main(String[] args) {
AddCourse();
AddStudent();
AddStudentCourse();
GetCourseAndStudents();
GetStudentAndCourses();
getAllCoursesAndStudents();
getAllStudentsAndCourses();
}
/*
* 獲取一個(gè)學(xué)生對(duì)應(yīng)的所有選修課程
*/
public static void GetStudentAndCourses() {
SqlSession session = sqlSessionFactory.openSession();
try {
int sid = 2;
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.getStudent(sid);
System.out.println(student);
System.out.println(student.getSid());
System.out.println("Student => " + student.getSname() + "," + student.getMobile());
List<Course> courses = student.getCourses();
for (Course course : courses) {
System.out.println("\t:" + course.getCid() + "\t" + course.getCname());
}
} finally {
session.close();
}
}
/*
* 獲取一門(mén)課程所有選修的學(xué)生
*/
public static void GetCourseAndStudents() {
SqlSession session = sqlSessionFactory.openSession();
try {
CourseMapper courseMaper = session.getMapper(CourseMapper.class);
Course course = courseMaper.getCourse(1);
System.out.println(course);
System.out.println("Course => " + course.getCname());
System.out.println("CourseId: " + course.getCid());
List<Student> students = course.getStudents();
for (Student student : students) {
System.out.println("\t:" + student.getSid() + "\t"
+ student.getSname());
}
} finally {
session.close();
}
}
/*
* 獲取所有課程并且通過(guò)課程獲取對(duì)應(yīng)的選修了的學(xué)生
*/
public static void getAllCoursesAndStudents() {
SqlSession session = sqlSessionFactory.openSession();
try {
CourseMapper courseMaper = session.getMapper(CourseMapper.class);
List<Course> courses = courseMaper.getAllCourses();
for (Course course : courses) {
System.out.println("Course Id: " + course.getCid() + " Course Name: " + course.getCname());
List<Student> students = course.getStudents();
for (Student student : students) {
System.out.println("\t:" + student.getSid() + "\t"
+ student.getSname());
}
}
} finally {
session.close();
}
}
/*
* 獲取所有學(xué)生并且根據(jù)學(xué)生獲取對(duì)應(yīng)的選修課程
*/
public static void getAllStudentsAndCourses() {
SqlSession session = sqlSessionFactory.openSession();
try {
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
List<Student> students = studentMapper.getAllStudents();
for (Student student : students) {
System.out.println("Student Id: " + student.getSid() + " Student Name: " + student.getSname());
List<Course> courses = student.getCourses();
for (Course course : courses) {
System.out.println("\t:" + course.getCid() + "\t"
+ course.getCname());
}
}
} finally {
session.close();
}
}
/*
* 向選修關(guān)系表中添加數(shù)據(jù)
*/
public static void AddStudentCourse() {
StudentCourse studentCourse = new StudentCourse();
studentCourse.setCid(1);
studentCourse.setSid(2);
SqlSession session = sqlSessionFactory.openSession();
try {
StudentCourseMapper studentCourseMaper = session
.getMapper(StudentCourseMapper.class);
studentCourseMaper.insertStudentCourse(studentCourse);
session.commit();
} finally {
session.close();
}
}
/*
* 添加學(xué)生
*/
public static void AddStudent() {
SqlSession session = sqlSessionFactory.openSession();
try {
Student student = new Student();
student.setSname("Student-name-1");
student.setMobile("13888888888");
StudentMapper studentMaper = session.getMapper(StudentMapper.class);
studentMaper.insertStudent(student);
session.commit();
} finally {
session.close();
}
}
/*
* 添加課程
*/
public static void AddCourse() {
SqlSession session = sqlSessionFactory.openSession();
try {
Course course = new Course();
course.setCname("課程-1");
CourseMapper courseMapper = session.getMapper(CourseMapper.class);
courseMapper.insertCourse(course);
session.commit();
} finally {
session.close();
}
}
}
-
運(yùn)行結(jié)果
可以看到結(jié)果是相對(duì)應(yīng)的读恃。