mybatis学习总结(二) 关系查询
一、一对一关联
1.1、提出需求
根据班级id查询班级信息(带老师的信息)
1.2、创建表和数据
创建一张教师表和班级表，这里我们假设一个老师只负责教一个班，那么老师和班级之间的关系就是一种一对一的关系。
CREATE TABLE teacher( t_id INT PRIMARY KEY AUTO_INCREMENT, t_name VARCHAR(20) ); CREATE TABLE class( c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(20), teacher_id INT ); ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id); INSERT INTO teacher(t_name) VALUES('teacher1'); INSERT INTO teacher(t_name) VALUES('teacher2'); INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1); INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);
表之间的关系如下：
1.3、定义实体类
1、Teacher类，Teacher类是teacher表对应的实体类。
package com.fan.domain; /** * @author fan * 定义teacher表对应的实体类 */ public class Teacher { //定义实体类的属性，与teacher表中的字段对应 private int id; //id===>t_id private String name; //name===>t_name public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Teacher [id=" + id + ", name=" + name + "]"; } }
2、Classes类，Classes类是class表对应的实体类
package com.fan.domain; /** * @author gacl * 定义class表对应的实体类 */ public class Classes { //定义实体类的属性，与class表中的字段对应 private int id; //id===>c_id private String name; //name===>c_name /** * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性， * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的 */ private Teacher teacher; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } @Override public String toString() { return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher+ "]"; } }
1.4、定义sql映射文件classMapper.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="com.fan.mapping.classMapper"> <!-- 根据班级id查询班级信息(带老师的信息) ##1. 联表查询 SELECT * FROM class c,teacher t WHERE c.teacher_id=t.t_id AND c.c_id=1; ##2. 执行两次查询 SELECT * FROM class WHERE c_id=1; //teacher_id=1 SELECT * FROM teacher WHERE t_id=1;//使用上面得到的teacher_id --> <!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据) select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1 --> <select id="getClass" parameterType="int" resultMap="ClassResultMap"> select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id} </select> <!-- 使用resultMap映射实体类和字段之间的一一对应关系 --> <resultMap type="com.fan.domain.Classes" id="ClassResultMap"> <id property="id" column="c_id"/> <result property="name" column="c_name"/> <association property="teacher" javaType="com.fan.domain.Teacher"> <id property="id" column="t_id"/> <result property="name" column="t_name"/> </association> </resultMap> <!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型 SELECT * FROM class WHERE c_id=1; SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id的值 --> <select id="getClass2" parameterType="int" resultMap="ClassResultMap2"> select * from class where c_id=#{id} </select> <!-- 使用resultMap映射实体类和字段之间的一一对应关系 --> <resultMap type="me.gacl.domain.Classes" id="ClassResultMap2"> <id property="id" column="c_id"/> <result property="name" column="c_name"/> <association property="teacher" column="teacher_id" select="getTeacher"/> </resultMap> <select id="getTeacher" parameterType="int" resultType="me.gacl.domain.Teacher"> SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id} </select> </mapper>
在config.xml文件中注册classMapper.xml
<mappers> <mapper resource="com/fan/mapping/classMapper.xml"/> </mappers>
1.5、编写单元测试代码
package com.fan.test; import com.fan.domain.Classes; import com.fan.util.MyBatisUtil; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class Test3 { @Test public void testGetClass(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); String statement = "com.fan.mapping.classMapper.getClass";//映射sql的标识字符串 Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录 sqlSession.close(); System.out.println(clazz);//打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1]] } @Test public void testGetClass2(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); String statement = "me.gacl.mapping.classMapper.getClass2";//映射sql的标识字符串 Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录 sqlSession.close(); System.out.println(clazz);//打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1]] } }
1.6、MyBatis一对一关联查询总结
MyBatis中使用association标签来解决一对一的关联查询，association标签可用的属性如下：
- property:对象属性的名称
- javaType:对象属性的类型
- column:所对应的外键字段名称
- select:使用另一个查询封装的结果
二、一对多关联
2.1、提出需求
根据classId查询对应的班级信息,包括学生,老师
2.2、创建表和数据
在上面的一对一关联查询演示中，我们已经创建了班级表和教师表，因此这里再创建一张学生表
CREATE TABLE student( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT ); INSERT INTO student(s_name, class_id) VALUES('student_A', 1); INSERT INTO student(s_name, class_id) VALUES('student_B', 1); INSERT INTO student(s_name, class_id) VALUES('student_C', 1); INSERT INTO student(s_name, class_id) VALUES('student_D', 2); INSERT INTO student(s_name, class_id) VALUES('student_E', 2); INSERT INTO student(s_name, class_id) VALUES('student_F', 2);
2.3、定义实体类
1、Student类
package com.fan.domain; /** * @author gacl * 定义student表所对应的实体类 */ public class Student { //定义属性，和student表中的字段对应 private int id; //id===>s_id private String name; //name===>s_name public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + "]"; } }
2、修改Classes类，添加一个List<Student> students属性，使用一个List<Student>集合属性表示班级拥有的学生，如下：
package me.gacl.domain; import java.util.List; /** * @author gacl * 定义class表对应的实体类 */ public class Classes { //定义实体类的属性，与class表中的字段对应 private int id; //id===>c_id private String name; //name===>c_name /** * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性， * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的 */ private Teacher teacher; //使用一个List<Student>集合属性表示班级拥有的学生 private List<Student> students; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } @Override public String toString() { return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher + ", students=" + students + "]"; } }
2.4、修改sql映射文件classMapper.xml
添加如下的SQL映射信息
<!-- 根据classId查询对应的班级信息,包括学生,老师 --> <!-- 方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集 SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=1 --> <select id="getClass3" parameterType="int" resultMap="ClassResultMap3"> select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and c.c_id=#{id} </select> <resultMap type="me.gacl.domain.Classes" id="ClassResultMap3"> <id property="id" column="c_id"/> <result property="name" column="c_name"/> <association property="teacher" column="teacher_id" javaType="me.gacl.domain.Teacher"> <id property="id" column="t_id"/> <result property="name" column="t_name"/> </association> <!-- ofType指定students集合中的对象类型 --> <collection property="students" ofType="me.gacl.domain.Student"> <id property="id" column="s_id"/> <result property="name" column="s_name"/> </collection> </resultMap> <!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型 SELECT * FROM class WHERE c_id=1; SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id的值 SELECT * FROM student WHERE class_id=1 //1是第一个查询得到的c_id字段的值 --> <select id="getClass4" parameterType="int" resultMap="ClassResultMap4"> select * from class where c_id=#{id} </select> <resultMap type="me.gacl.domain.Classes" id="ClassResultMap4"> <id property="id" column="c_id"/> <result property="name" column="c_name"/> <association property="teacher" javaType="me.gacl.domain.Teacher" column="teacher_id" select="getTeacher2"></association> <collection property="students" ofType="me.gacl.domain.Student" column="c_id" select="getStudent"></collection> </resultMap> <select id="getTeacher2" parameterType="int" resultType="me.gacl.domain.Teacher"> SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id} </select> <select id="getStudent" parameterType="int" resultType="me.gacl.domain.Student"> SELECT s_id id, s_name name FROM student WHERE class_id=#{id} </select>
2.5、编写单元测试代码
package com.fan.test; import com.fan.domain.Classes; import com.fan.util.MyBatisUtil; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class Test4 { @Test public void testGetClass3(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); String statement = "me.gacl.mapping.classMapper.getClass3";//映射sql的标识字符串 Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录 sqlSession.close(); //打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1],
students=[Student [id=1, name=student_A], Student [id=2, name=student_B], Student [id=3, name=student_C]]] System.out.println(clazz); } @Test public void testGetClass4(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); String statement = "me.gacl.mapping.classMapper.getClass4";//映射sql的标识字符串 Classes clazz = sqlSession.selectOne(statement,1);//查询class表中id为1的记录 sqlSession.close(); //打印结果：Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1],
students=[Student [id=1, name=student_A], Student [id=2, name=student_B], Student [id=3, name=student_C]]] System.out.println(clazz); } }
2.6、MyBatis一对多关联查询总结
MyBatis中使用collection标签来解决一对多的关联查询，ofType属性指定集合中元素的对象类型。
三、多对多关联
3.1、创建表和数据
CREATE TABLE user ( id int(11) NOT NULL auto_increment, name varchar(40) , password varchar(20) , PRIMARY KEY ('id') )
CREATE TABLE group_info ( id int(11) NOT NULL auto_increment, name varchar(40) , state int(1) default '0' COMMENT '0:可见;1:不可见', PRIMARY KEY ('id') )
CREATE TABLE user_group ( user_id int(11) default NULL, group_id int(11) default NULL, KEY FK_user_group_user_id ('user_id'), KEY FK_user_group_group_id` ('group_id'), CONSTRAINT FK_user_group_group_id FOREIGN KEY ('group_id') REFERENCES group_info ('id'), CONSTRAINT FK_user_group_user_id FOREIGN KEY ('user_id') REFERENCES user('id') )
package com.fan.domain; import java.util.Date; import java.util.List; public class User { private long id; private String name; private String password;private List<Group> group;public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public List<Group> getGroup() { return group; } public void setGroup(List<Group> group) { this.group = group; } }
package com.fan.domain; import java.util.Date; import java.util.List; public class Group { private long id; private String name; // 组名private int state; // 0可见状态 1不可见状态 private List<User> user; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getState() { return state; } public void setState(int state) { this.state = state; } public List<User> getUser() { return user; } public void setUser(List<User> user) { this.user = user; } }
package com.fan.domain; import java.util.Date; public class UserGroupLink { private User user; private Group group;public Group getGroup() { return group; } public void setGroup(Group group) { this.group = group; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } }
UserMapper.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="com.fan.domain.User"> <resultMap type="User" id="userMap"> <id property="id" column="id" /> <result property="name" column="name" /> <result property="password" column="password" /> </resultMap> <resultMap type="User" id="userGroupMap" extends="userMap"> <collection property="groups" ofType="Group"> <id property="id" column="goupId" /> <result property="name" column="groupName" /> <result property="state" column="state" /> </collection> </resultMap> <!-- 根据user表中的id查询用户信息 --> <select id="selectUser" parameterType="long" resultMap="userMap"> select * from user where id = #{id} </select> <!-- 根据user表中的id查询用户和组信息 --> <select id="selectUserGroup" parameterType="long" resultMap="userGroupMap"> select u.id,u.name,u.password,u.createtime, gi.id as goupId,gi.name as groupName, gi.createdate as groupCreateTime, gi.state from user u left join user_group ug on u.id=ug.user_id left join group_info gi on ug.group_id=gi.id where u.id = #{id} </select> <!-- 插入用户信息 --> <insert id="saveUser" parameterType="User" keyProperty="id" useGeneratedKeys="true"> insert into user(name,password) values(#{name},#{password}) </insert> <!-- 保存用户和组之间的关系信息 --> <insert id="saveRelativity" parameterType="UserGroupLink"> insert into user_group(user_id,group_id) values(#{user.id},#{group.id}) </insert> <select id="selectAllUser" resultMap="userMap"> select * from user </select> </mapper>
GroupMapper.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="com.xxt.ibatis.dbcp.domain.Group"> <resultMap type="Group" id="groupMap"> <id property="id" column="id" /> <result property="name" column="name" /> </resultMap> <resultMap type="Group" id="groupUserMap" extends="groupMap"> <collection property="users" ofType="User"> <id property="id" column="userId" /> <result property="name" column="userName" /> <result property="password" column="password" /> </collection> </resultMap> <!-- 根据Group表中的id或name查询组信息和组内用户信息 --> <select id="selectGroupUser" parameterType="Group" resultMap="groupUserMap"> select u.id as userId,u.name as userName, u.password,u.createtime as userCreateTime, gi.id,gi.name,gi.createdate,gi.state from group_info gi left join user_group ug on gi.id=ug.group_id left join user u on ug.user_id=u.id <where> <!--当id为初始值0，不再使用id作为查询条件 第一个条件中会自动加上and --> <if test="id != 0 ">gi.id=#{id}</if> <!-- 当name为空或为空串时，不再使用name作为查询条件 --> <if test="name != null and name != ''"> or gi.name = #{name} </if> </where> </select> <!-- 根据id查询group组信息 --> <select id="selectGroup" parameterType="Date" resultMap="groupMap"> select * from group_info where id=#{group_id} </select> <!--根据name查询group组信息 --> <select id="getGroupByName" parameterType="String" resultMap="groupMap"> select * from group_info where name=#{name} </select> <!-- 插入组信息 --> <insert id="saveGroup" parameterType="Group" keyProperty="id" useGeneratedKeys="true"> insert into group_info(name) values(#{name}) </insert> <!-- 删除组与组内成员之间的对应关系 --> <delete id="deleteGroupUser" parameterType="UserGroupLink"> delete from user_group <where> <if test="user.id != 0">user_id = #{user.id}</if> <if test="group.id != 0">and group_id = #{group.id}</if> </where> </delete> <!--根据组id或者组name删除组信息 --> <delete id="deleteGroup" parameterType="Group"> delete from group_info <where> <if test="id!=0">id=#{id}</if> <if test="name!=null || name!=''">and name=#{name}</if> </where> </delete> <!-- 更新根据组id或者组name更新组状态 --> <update id="updateGroupState" parameterType="Group"> update group_info set state=#{state} <where> <if test="id!=0">id=#{id}</if> <if test="name!=null || name!=''">and name=#{name}</if> </where> </update> </mapper>
config.xml配置如下：
<?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> <properties resource="jdbc.properties" /> <typeAliases> <typeAlias type="com.xxt.ibatis.dbcp.domain.User" alias="User" /> <typeAlias type="com.xxt.ibatis.dbcp.domain.Group" alias="Group" /> <typeAlias type="com.xxt.ibatis.dbcp.domain.UserGroupLink" alias="UserGroupLink" /> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="UNPOOLED"> <property name="driver" value="${driver}" /> <property name="url" value="${url}" /> <property name="username" value="${username}" /> <property name="password" value="${password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="com/fan/domain/UserMapper.xml" /> <mapper resource="com/fan/domain/GroupMapper.xml" /> </mappers> </configuration>
测试代码如下：
package com.fan.test; import java.io.Reader; import java.text.SimpleDateFormat; import java.util.List; import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Test; 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 com.fan.domain.Group; import com.fan.domain.User; import com.fan.UserGroupLink; public class IbatisUserTest { private final static String IBATIS_CONFIG_XML = "config.xml"; private static SqlSession session; private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd k:mm:ss"); @BeforeClass public static void setUpBeforeClass() throws Exception { Reader reader = Resources.getResourceAsReader(IBATIS_CONFIG_XML); //读取ibatis配置文件 SqlSessionFactory sqlMapper = new SqlSessionFactoryBuilder().build(reader); session = sqlMapper.openSession(true); session.commit(false); //将默认提交事务属性设置为否 } //保存用户信息 @Test public void saveUserTest(){ User user = new User(); user.setName("张三"); user.setPassword("123456"); session.insert("com.fan.domain.User.saveUser", user); } //获取用户信息 @Test public void getUserTest(){ User user = (User) session.selectOne("com.fan.domain.User.selectUser", 1L); System.out.println("用户名： "+user.getName()); System.out.println("用户密码: "+user.getPassword()); } //获取用户和用户所在组信息 @Test public void getUserAndGroupTest(){ User user = (User)session.selectOne("com.fan.domain.User.selectUserGroup", 4L); System.out.println(user.getName() +"所属组信息:"); for(Group group : user.getGroups() ){ System.out.println(" 组名:"+group.getName()); } } //保存用户和用户所在组信息 //当用户所在组不存在时，创建该组，并生成映射关系 @Test public void saveUserAndGroupTest(){ User user = new User(); user.setName("无常鬼"); user.setPassword("wuchang"); session.insert("com.fan.domain.User.saveUser", user); Group groupImpl = (Group)session.selectOne("com.fan.domain.Group.getGroupByName","用户组4");//获取组实例 UserGroupLink ugl = new UserGroupLink();//声明User和Group实体间映射关系实例 //查询到的组实例为空时的逻辑处理 if(groupImpl == null){ Group group = new Group(); group.setName("用户组4"); session.insert("com.fan.domain.Group.saveGroup", group);//持久化创建好的组实例 //设置映射关系实例相关的属性 ugl.setUser(user); ugl.setGroup(group); session.insert("com.fan.domain.User.saveRelativity",ugl);//持久化映射关系实力 }else{ ugl.setGroup(groupImpl); ugl.setUser(user); session.insert("com.fan.domain.User.saveRelativity",ugl); } } //删除组信息的同时，取消组内所有的成员与该组的关联关系 @Test public void deleteGroupTest(){ Group group = new Group(); //group.setId(1L); //以组id作为查询条件 group.setName("用户组1"); //以组name作为查询条件 Group groupImpl = (Group)session.selectOne("com.fan.domain.Group.selectGroupUser",group);//获取组实例 //组实例存在时 if(groupImpl != null){ List<User> users = groupImpl.getUsers(); //查看用户组1中是否存在用户 if(users != null && users.size() > 0){ //存在用户时，先删除组与用户的对应关系 UserGroupLink ugl = new UserGroupLink(); for(User user : users){ ugl.setUser(user); ugl.setGroup(groupImpl); session.delete("com.fan.domain.Group.deleteGroupUser",ugl ); } } //删除组信息 session.delete("com.fan.domain.Group.deleteGroup", groupImpl); }else{ throw new RuntimeException("查询的组不存在!!"); } } //更新组状态，当组状态由可见状态变成不可见时，取消该组下的用户与该组的映射关系 @Test public void updateGroupStateTest(){ Group group = new Group(); group.setName("用户组2"); Group groupImpl = (Group) session.selectOne("com.fan.domain.Group.selectGroupUser",group); if(groupImpl != null){ int state = groupImpl.getState() == 1 ? 0 : 1; //组状态由0变成1时，即由可见变为不可见 if(state == 1){ List<User> users = groupImpl.getUsers(); //查看用户组2中是否存在用户 if(users != null && users.size() > 0){ //存在用户时，删除组与用户的对应关系 UserGroupLink ugl = new UserGroupLink(); for(User user : users){ ugl.setUser(user); ugl.setGroup(groupImpl); session.delete("com.fan.domain.Group.deleteGroupUser",ugl ); } } } //更新组状态 groupImpl.setState(state); } else{ throw new RuntimeException("查询的组不存在!!"); } } @AfterClass public static void tearDownAfterClass() throws Exception { if(session != null){ session.commit(); //提交事务 session.close(); //关闭连接 } } }
Mybatis插入数据行ID生成策略:https://my.oschina.net/boonya/blog/692232
