Mybatis一对一、一对多、多对多
1、什么是MyBatis?
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis 。2013年11月迁移到Github。
iBATIS一词来源于“internet”和“abatis”的组合，是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects（DAO）。
MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs（Plain Ordinary Java Objects，普通的 Java对象）映射成数据库中的记录。
PS:本文所有代码下载链接：http://pan.baidu.com/s/1gf8CPQN 密码：t2x9
2、MyBatis 入门实例基于xml配置
①、创建MySQL数据库：mybatisDemo和表：person
|
1
2
3
|
create database mybatisDemo;use mybatisDemo;create table person(pid int primary key AUTO_INCREMENT, pname varchar(50), page int); |
②、建立一个Java工程，并导入相应的jar包
相应的 jar 包下载链接：http://pan.baidu.com/s/1skZM09Z 密码：nkt6
③、在 MyBatisDemo 工程中添加数据库配置文件 mybatis-configuration.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
<?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><!-- 可以配置多个运行环境，但是每个 SqlSessionFactory 实例只能选择一个运行环境 一、development:开发模式 二、work：工作模式--> <environments default="development"> <!--id属性必须和上面的default一样 --> <environment id="development"> <!--事务管理器 一、JDBC：这个配置直接简单使用了 JDBC 的提交和回滚设置。它依赖于从数据源得到的连接来管理事务范围 二、MANAGED：这个配置几乎没做什么。它从来不提交或回滚一个连接。而它会让容器来管理事务的整个生命周期 比如 spring 或 JEE 应用服务器的上下文，默认情况下，它会关闭连接。然而一些容器并不希望这样， 因此如果你需要从连接中停止它，就可以将 closeConnection 属性设置为 false，比如： <transactionManager type="MANAGED"> <property name="closeConnection" value="false"/> </transactionManager> --> <transactionManager type="JDBC"/> <!--dataSource 元素使用标准的 JDBC 数据源接口来配置 JDBC 连接对象源 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatisdemo"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments></configuration> |
④、定义表所对应的实体类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
package com.ys.bean;public class Person { private int pid; private String pname; private int page; public int getPid() { return pid; } public void setPid(int pid) { this.pid = pid; } public String getPname() { return pname; } public void setPname(String pname) { this.pname = pname; } public int getPage() { return page; } public void setPage(int page) { this.page = page; } @Override public String toString() { return "Person [pid=" + pid + ", pname=" + pname + ", page=" + page + "]"; }} |
⑤、定义操作 person 表的sql映射文件personMapper.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
<?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.ys.bean.personMapper"> <!-- 根据 pid 查询 person 表中的数据 id:唯一标识符，此文件中的id值不能重复 resultType:返回值类型，一条数据库记录也就对应实体类的一个对象 parameterType:参数类型，也就是查询条件的类型 --> <select id="selectPersonById" resultType="com.ys.bean.Person" parameterType="int"> <!-- 这里和普通的sql 查询语句差不多，对于只有一个查询条件后面的 #{pid}表示占位符，里面不一定要写pid,写啥都可以，但是不要空着；如果有多个查询条件，则要写pojo类里面的属性 --> select * from person where pid = #{pid} </select> <!-- 查询person 表所有数据 --> <select id="getAllPerson" resultType="com.ys.bean.Person"> select * from person </select> <!-- 根据id更新数据 --> <update id="updatePersonById" parameterType="com.ys.bean.Person"> update person set pname=#{pname},page=#{page} where pid = #{pid} </update> <!-- 向 person 表插入一条数据 --> <insert id="addPerson" parameterType="com.ys.bean.Person"> insert into person(pid,pname,page) values(#{pid},#{pname},#{page}) </insert> <!-- 根据 pid 删除数据 --> <delete id="deletePersonById" parameterType="Long"> delete from person where pid=#{pid} </delete> </mapper> |
⑥、向 mybatis-configuration.xml 配置文件中注册 personMapper.xml 文件
|
1
2
3
4
5
|
<mappers> <!-- 注册personMapper.xml文件， personMapper.xml位于com.ys.bean这个包下，所以resource写成com/ys/bean/personMapper.xml--> <mapper resource="com/ys/bean/personMapper.xml"/></mappers> |
如下图所示：
⑦、创建测试类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
package com.ys.test;import java.io.InputStream;import java.util.List;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.Before;import org.junit.Test;import com.ys.bean.Person;public class MyBatisTest { SqlSession session; @Before public void beforeLoadXML(){ //加载 mybatis 配置文件 InputStream inputStream = MyBatisTest.class. getClassLoader().getResourceAsStream("mybatis-configuration.xml"); //构建sqlSession的工厂 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //根据 sqlSessionFactory 产生 session session = sqlSessionFactory.openSession(); } //根据 pid 查询 person 表中的数据 @Test public void testSelectById(){ //这个字符串有 personMapper.xml 文件中 两个部分构成 //<mapper namespace="com.ys.bean.personMapper"> 的 namespace 的值 //<select id="selectPersonById" > id 值 String statement = "com.ys.bean.personMapper"+".selectPersonById"; Person p = session.selectOne(statement, 1); System.out.println(p); session.close(); } //查询person 表所有数据 @Test public void testGetAllPerson(){ String statement = "com.ys.bean.personMapper.getAllPerson"; List<Person> listPerson = session.selectList(statement); System.out.println(listPerson); session.close(); } //根据id更新数据 @Test public void updateById(){ String statement = "com.ys.bean.personMapper.updatePersonById"; Person p = new Person(); p.setPid(1); p.setPname("aaa"); p.setPage(11); session.update(statement, p); session.commit(); session.close(); } //向 person 表插入一条数据 @Test public void addPerson(){ String statement = "com.ys.bean.personMapper.addPerson"; Person p = new Person(); //由于我们设置了主键的自增长机制，故这里不需要手动设置 pid 的值 //p.setPid(1); p.setPname("add"); p.setPage(11); session.insert(statement, p); session.commit(); session.close(); } //根据 pid 删除person 表中的数据 @Test public void deletePersonById(){ String statement = "com.ys.bean.personMapper.deletePersonById"; session.delete(statement, 1); session.commit(); session.close(); } } |
3、MyBatis 入门实例注解配置
①、上面的前面四步都是一样的，但是第五步不一样，我们不需要创建 personMapper.xml 文件，首先在 src 目录下创建 personMapper.java 文件
内容如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
package com.ys.annocation;import org.apache.ibatis.annotations.Delete;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.Update;import com.ys.bean.Person;public interface PersonMapper { @Insert("insert into person(pid,pname,page) values(#{pid},#{pname},#{page})") public int add(Person person); @Select("select * from person where pid = #{pid}") public Person getPerson(int pid); @Update("update person set pname=#{pname},page=#{page} where pid = #{pid}") public int updatePerson(Person preson); @Delete("delete from person where pid=#{pid}") public int deletePerson(int pid);} |
②、向 mybatis-configuration.xml 配置文件中注册 personMapper.xml 文件
③、编写测试类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
@Testpublic void testAnnocation(){ PersonMapper mapper = session.getMapper(PersonMapper.class); Person p = new Person(); p.setPid(7); p.setPname("abc"); p.setPage(11); //调用增加方法 mapper.add(p); //调用查询方法 Person p1 = mapper.getPerson(3); System.out.println(p1); //调用更新方法 p.setPage(100); mapper.updatePerson(p); //调用删除方法 mapper.deletePerson(7); session.commit(); session.close();} |
4、MyBatis 入门实例 一对一 基于xml配置
这里我们以老师和班级为例，假设一般班级只能拥有有一个老师，一个老师只能带一个班级。
①、创建实体类
Teacher.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
package one.to.one;public class Teacher { private int tid; private String tname; private Classes classes; public int getTid() { return tid; } public void setTid(int tid) { this.tid = tid; } public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public Classes getClasses() { return classes; } public void setClasses(Classes classes) { this.classes = classes; } @Override public String toString() { return "Teacher [tid=" + tid + ", tname=" + tname + ", classes=" + classes + "]"; } } |
Classes.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
package one.to.one;public class Classes { private int cid; private String cname; private Teacher teacher; public int getCid() { return cid; } public void setCid(int cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } @Override public String toString() { return "Classes [cid=" + cid + ", cname=" + cname + ", teacher=" + teacher + "]"; } } |
②、在数据库中根据实体类创建相应的数据表
③、定义操作 Classes 表的sql映射文件classesMapper.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
<?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="one.to.one.classesMapper"> <!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据) select * from classes c, teacher t where c.tid=t.tid and c.tid=#{tid} --> <select id="getClasses" resultMap="getClassesMap" parameterType="int"> select * from classes c ,teacher t where c.tid=t.tid and c.tid=#{tid} </select> <resultMap type="one.to.one.Classes" id="getClassesMap"> <id column="cid" property="cid"/> <result column="cname" property="cname"/> <association property="teacher" javaType="one.to.one.Teacher"> <id column="tid" property="tid"></id> <result column="tname" property="tname"/> </association> </resultMap> <!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据) select * from teacher t,classes c where t.cid = c.cid and t.cid=#{cid} --> <select id="getTeacher" resultMap="getTeacherMap" parameterType="int"> select * from teacher t,classes c where t.cid = c.cid and t.cid=#{cid} </select> <resultMap type="one.to.one.Teacher" id="getTeacherMap"> <id column="tid" property="tid"/> <result column="tname" property="tname"/> <association property="classes" javaType="one.to.one.Classes"> <id column="cid" property="cid"/> <result column="cname" property="cname"/> </association> </resultMap> <!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型 SELECT * FROM classes WHERE cid=1; SELECT * FROM teacher WHERE tid=1 //1 是上一个查询得到的tid的值 property:别名(属性名) column：列名 --> <!-- 把teacher的字段设置进去 --> <select id="getClasses2" resultMap="getClassesMap2"> select * from classes c where c.cid = #{cid} </select> <resultMap type="one.to.one.Classes" id="getClassesMap2"> <id column="cid" property="cid"/> <result column="cname" property="cname"/> <collection property="teacher" column="tid" select="getTeacherCollection"> </collection> </resultMap> <select id="getTeacherCollection" resultType="one.to.one.Teacher"> select tid tid,tname tname from teacher where tid=#{tid} </select> </mapper> |
说明：我们这里一对一的关联操作，有两种方式：
1、使用嵌套结果映射来处理重复的联合结果的子集
2、通过执行另外一个SQL映射语句来返回预期的复杂类型
相关属性解释：
④、向 mybatis-configuration.xml 配置文件中注册 classesMapper.xml 文件
⑤、编写测试类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
package one.to.one;import java.io.InputStream;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.Before;import org.junit.Test;import com.ys.test.MyBatisTest;public class OneToOneTest {SqlSession session; @Before public void beforeLoadXML(){ //加载 mybatis 配置文件 InputStream inputStream = MyBatisTest.class. getClassLoader().getResourceAsStream("mybatis-configuration.xml"); //构建sqlSession的工厂 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //根据 sqlSessionFactory 产生 session session = sqlSessionFactory.openSession(); } //一对一嵌套结果方式：根据教师id查询班级信息 @Test public void testGetClasses(){ String statement = "one.to.one.classesMapper.getClasses"; Classes c = session.selectOne(statement, 1); System.out.println(c); } //一对一嵌套结果方式：根据班级id查询教师信息 @Test public void testGetTeacher(){ String statement = "one.to.one.classesMapper.getTeacher"; Teacher t = session.selectOne(statement, 1); System.out.println(t); } //一对一嵌套查询方式：根据教师id查询班级信息 @Test public void testGetClasses2(){ String statement = "one.to.one.classesMapper.getClasses2"; Classes c = session.selectOne(statement, 1); System.out.println(c); } } |
4、MyBatis 入门实例 一对多，多对一 基于xml配置
这里我们以班级和学生为例，一个班级里面对应多个学生，这是一对多；反过来，多个学生对应一个班级，这是多对一
①、建立学生和班级的实体类
Student.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
package one.to.many;public class Student { private int sid; private String sname; private Classes classes; public int getSid() { return sid; } public void setSid(int sid) { this.sid = sid; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public Classes getClasses() { return classes; } public void setClasses(Classes classes) { this.classes = classes; } @Override public String toString() { return "Student [sid=" + sid + ", sname=" + sname + ", classes=" + classes + "]"; } } |
Classes.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
package one.to.many;import java.util.Set;public class Classes { private int cid; private String cname; private Set<Student> students; public int getCid() { return cid; } public void setCid(int cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } public Set<Student> getStudents() { return students; } public void setStudents(Set<Student> students) { this.students = students; } @Override public String toString() { return "Classes [cid=" + cid + ", cname=" + cname + ", students=" + students + "]"; }} |
②、在数据库中根据实体类创建相应的数据表
③、多对一：定义操作 Classes 表的sql映射文件classesMapper.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<?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="one.to.many.classesMapper"> <select id="getClasses" resultMap="getClassesMap"> select * from classes c,student s where s.cid=c.cid and c.cid=#{cid} </select> <resultMap type="one.to.many.Classes" id="getClassesMap"> <id column="cid" property="cid"></id> <result column="cname" property="cname"/> <collection property="students" ofType="one.to.many.Student"> <id column="sid" property="sid"/> <result column="sname" property="sname"/> </collection> </resultMap> </mapper> |
④、一对多：定义操作 Student 表的sql映射文件studentMapper.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
<?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="many.to.one.studentMapper"> <select id="getStudents" resultMap="getStudentMap"> select * from classes c,student s where s.cid=c.cid and s.sid=#{sid} </select> <resultMap type="one.to.many.Student" id="getStudentMap"> <id column="sid" property="sid"></id> <result column="sname" property="sname"/> <association property="classes" javaType="one.to.many.Classes"> <id column="cid" property="cid"/> <result column="cname" property="cname"/> </association> </resultMap> </mapper> |
⑤、向 mybatis-configuration.xml 配置文件中注册 classesMapper.xml 、studentMapper.xml文件
⑥、编写测试类
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
package one.to.many;import java.io.InputStream;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.Before;import org.junit.Test;import com.ys.test.MyBatisTest;public class OneToManyTest {SqlSession session; @Before public void beforeLoadXML(){ //加载 mybatis 配置文件 InputStream inputStream = MyBatisTest.class. getClassLoader().getResourceAsStream("mybatis-configuration.xml"); //构建sqlSession的工厂 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //根据 sqlSessionFactory 产生 session session = sqlSessionFactory.openSession(); } //一对多嵌套结果方式：根据班级id查询班级所有的学生信息 @Test public void testGetClasses(){ String statement = "one.to.many.classesMapper.getClasses"; Classes c = session.selectOne(statement, 1); System.out.println(c); System.out.println(c.getStudents().size()); } //多对一嵌套结果方式：根据学生id查询班级信息 @Test public void testGetStudents(){ String statement = "many.to.one.studentMapper.getStudents"; Student s = session.selectOne(statement, 1); System.out.println(s); System.out.println(s.getClasses()); } } |
5、MyBatis 入门实例 多对多 基于xml配置
这里我们以 users 表和 groups 表为例，一个 users 可能加入多个 groups，而一个 groups 可能包含多个 users，故构成 多对多 的关联
①、在数据库中建立相应的表
users 表
groups 表
两者之间的关联表users_groups表
②、建立对应的实体类
Users.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
package many.to.many;import java.util.Set;public class Users { private int uid; private String uname; private Set<Groups> groups; public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getUname() { return uname; } public void setUname(String uname) { this.uname = uname; } public Set<Groups> getGroups() { return groups; } public void setGroups(Set<Groups> groups) { this.groups = groups; } @Override public String toString() { return "User [uid=" + uid + ", uname=" + uname + ", groups=" + groups + "]"; } } |
Groups.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
package many.to.many;import java.util.Set;public class Groups { private int gid; private String gname; private Set<Users> users; public int getGid() { return gid; } public void setGid(int gid) { this.gid = gid; } public String getGname() { return gname; } public void setGname(String gname) { this.gname = gname; } public Set<Users> getUsers() { return users; } public void setUsers(Set<Users> users) { this.users = users; } @Override public String toString() { return "Group [gid=" + gid + ", gname=" + gname + ", users=" + users + "]"; } } |
Users_Groups.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
package many.to.many;public class Users_Groups { private Users user; private Groups group; public Users getUser() { return user; } public void setUser(Users user) { this.user = user; } public Groups getGroup() { return group; } public void setGroup(Groups group) { this.group = group; }} |
③、多对多：定义操作 sql映射文件userMapper.xml
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
<?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="many.to.many.userMapper"> <!-- 给一个用户 id，查看该用户下的所有用户组信息 --> <select id="getUsers" resultMap="getGroupMap"> select g.gid,g.gname from users_groups ug,groups g where ug.group_id=g.gid and ug.user_id=#{uid} </select> <resultMap type="many.to.many.Groups" id="getGroupMap"> <id column="gid" property="gid"/> <result column="gname" property="gname"/> <collection property="users" ofType="many.to.many.Users"> <id column="uid" property="uid"/> <result column="uname" property="uname"/> </collection> </resultMap> </mapper> |
⑤、向 mybatis-configuration.xml 配置文件中注册 userMapper.xml文件
⑥、编写测试类
|
1
2
3
4
5
6
7
8
9
|
//多对多：根据根据用户 id 查询所有的用户组信息 @Test public void testGetGroups(){ String statement = "many.to.many.userMapper.getUsers"; List<Groups> listGroup = session.selectList(statement,1); for(Groups g : listGroup){ System.out.println(g.toString()); } } |
