一对一和一对多的关联关系已经初步了解，接下来要看多对多的级联查询，由于之前的例子并不适合多对多，因此用学生和课程这个经典例子学习。
总结下：
一对一关联：
1】pojo对象对方对象；
2】映射文件中使用association关联
一对多关联：
1】pojo对象添加对方类型的list列表；
2】映射文件使用collection关联
mybatis3.0添加了association和collection标签专门用于对多个相关实体类数据进行级联查询，但仍不支持多个相关实体类数据的级联保存和级联删除操作。因此在进行实体类多对多映射表设计时，需要专门建立一个关联对象类对相关实体类的关联关系进行描述。下文将以“User”和“Group"两个实体类之间的多对多关联映射为例进行CRUD操作。
工程目录：
Group：
package com.mybaits.onetomany.model;
import java.util.Date;
import java.util.List;
public class Group {
private long id;
private String name; // 组名
private Date createTime;
private int state; // 0可见状态 1不可见状态
private List<User> user;
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
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.mybaits.onetomany.model;
import java.util.Date;
import java.util.List;
public class User {
private long id;
private String name;
private String password;
private Date createTime;
private List<Group> groups;
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
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> getGroups() {
return groups;
}
public void setGroups(List<Group> groups) {
this.groups = groups;
}
}
package com.mybaits.onetomany.model;
import java.util.Date;
/**
* @describe: 描述User和Group之间的映射关系
*/
public class UserGroupLink {
private User user;
private Group group;
private Date createTime;
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
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;
}
}
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.mybatis.manytomany.mapper.groupMapper">
<resultMap type="com.mybaits.onetomany.model.Group" id="groupMap">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="createTime" column="createdate" />
</resultMap>
<resultMap type="com.mybaits.onetomany.model.Group" id="groupUserMap" extends="groupMap">
<collection property="user" ofType="com.mybaits.onetomany.model.User">
<id property="id" column="userId" />
<result property="name" column="userName" />
<result property="password" column="password" />
<result property="createTime" column="userCreateTime" />
</collection>
</resultMap>
<!-- 根据Group表中的id或name查询组信息和组内用户信息 -->
<select id="selectGroupUser" parameterType="com.mybaits.onetomany.model.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作为查询条件 -->
<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="com.mybaits.onetomany.model.Group" keyProperty="id"
useGeneratedKeys="true">
insert into group_info(name) values(#{name})
</insert>
<!-- 删除组与组内成员之间的对应关系 -->
<delete id="deleteGroupUser" parameterType="com.mybaits.onetomany.model.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="com.mybaits.onetomany.model.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="com.mybaits.onetomany.model.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> 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.mybatis.manytomany.mapper.userMapper">
<resultMap id="userMap" type="com.mybaits.onetomany.model.User">
<id column="id" property="id" />
<result column="name" property="name"/>
<result column="password" property="password"/>
<result column="createtime" property="createTime"/>
</resultMap>
<resultMap type="com.mybaits.onetomany.model.User" id="userGroupMap" extends="userMap">
<collection property="groups" ofType="com.mybaits.onetomany.model.Group">
<id property="id" column="goupId" />
<result property="name" column="groupName" />
<result property="state" column="state" />
<result property="createTime" column="groupCreateTime" />
</collection>
</resultMap>
<!-- 插入用户信息 -->
<insert id="saveUser" parameterType="com.mybaits.onetomany.model.User" keyProperty="id" useGeneratedKeys="true">
insert into user(name,password) values(#{name},#{password})
</insert>
<!-- 根据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="saveRelativity" parameterType="com.mybaits.onetomany.model.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>mybatis-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>
<typeAliases>
<typeAlias type="com.mybaits.onetomany.model.User" alias="User" />
<typeAlias type="com.mybaits.onetomany.model.Group"
alias="Group" />
<typeAlias type="com.mybaits.onetomany.model.UserGroupLink"
alias="UserGroupLink" />
</typeAliases>
<!-- 配置数据源信息 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=UTF-8"/>
<property name="username" value="mysql"/>
<property name="password" value="mysql"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/mybatis/manytomany/mapper/userMapper.xml"/>
<mapper resource="com/mybatis/manytomany/mapper/groupMapper.xml"/>
</mappers>
</configuration> 测试类：
package com.mybatis.test;
import java.io.Reader;
import java.util.List;
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.mybaits.onetomany.model.Group;
import com.mybaits.onetomany.model.User;
import com.mybaits.onetomany.model.UserGroupLink;
public class Test {
private static SqlSessionFactory sqlSessionFactory;
private static Reader reader;
private static String resource ="config/mybatis-config.xml";
static {
try {
reader = Resources.getResourceAsReader(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 保存用户信息
*/
public static void saveUserTest(){
SqlSession session = null;
try{
session = sqlSessionFactory.openSession();
User user = new User();
user.setName("张三");
user.setPassword("123456");
//<strong><span style="color:#3366ff;">注意：insert两个参数:1、mapper命名空间+id 2、保存对象</span></strong>
session.insert("<span style="color:#ff0000;">com.mybatis.manytomany.mapper.userMapper</span>.saveUser", user);
session.commit();
}finally{
session.close();
}
}
/**
* 获取用户信息
*/
public static void getUserTest(){
SqlSession session = null;
try{
session = sqlSessionFactory.openSession();
User user = (User) session.selectOne("com.mybatis.manytomany.mapper.userMapper.selectUser", 3L);
System.out.println("用户名： "+user.getName());
System.out.println("用户密码: "+user.getPassword());
session.commit();
}finally{
session.close();
}
}
/**
* 获取用户和用户所在组信息
*/
public static void getUserAndGroupTest(){
SqlSession session = null;
try{
session = sqlSessionFactory.openSession();
User user = (User)session.selectOne("com.mybatis.manytomany.mapper.userMapper.selectUserGroup", 3L);
System.out.println(user.getName() +"所属组信息:");
for(Group group : user.getGroups() ){
System.out.println(group);
}
session.commit();
}finally{
session.close();
}
}
/**
* 保存用户和用户所在组信息 。当用户所在组不存在时，创建该组，并生成映射关系
*
* 级联保存
*/
public static void saveUserAndGroupTest(){
SqlSession session = null;
try{
session = sqlSessionFactory.openSession();
User user = new User();
user.setName("李四");
user.setPassword("lisi");
session.insert("com.mybatis.manytomany.mapper.userMapper.saveUser", user);
Group groupImpl = (Group)session.selectOne("com.mybatis.manytomany.mapper.groupMapper.getGroupByName","用户组4");//获取组实例
UserGroupLink ugl = new UserGroupLink();//声明User和Group实体间映射关系实例
//查询到的组实例为空时的逻辑处理
if(groupImpl == null){
Group group = new Group();
group.setName("用户组4");
session.insert("com.mybatis.manytomany.mapper.groupMapper.saveGroup", group);//持久化创建好的组实例
//设置映射关系实例相关的属性
ugl.setUser(user);
ugl.setGroup(group);
session.insert("com.mybatis.manytomany.mapper.userMapper.saveRelativity",ugl);//持久化映射关系实力
}else{
ugl.setGroup(groupImpl);
ugl.setUser(user);
session.insert("com.mybatis.manytomany.mapper.userMapper.saveRelativity",ugl);
}
session.commit();
}finally{
session.close();
}
}
/**
* 删除组信息的同时，取消组内所有的成员与该组的关联关系
*/
public static void deleteGroupTest(){
SqlSession session = null;
try{
session = sqlSessionFactory.openSession();
Group group = new Group();
group.setName("用户组1"); //以组name作为查询条件
Group groupImpl = (Group)session.selectOne("com.mybatis.manytomany.mapper.groupMapper.selectGroupUser",group);//获取组实例
//判断小组是否存在
if(groupImpl != null){
List<User> users = groupImpl.getUser();
//判断“用户组1”中是否存在用户
if(users != null && users.size() > 0){
//存在用户时，先删除组与用户的对应关系
UserGroupLink ugl = new UserGroupLink();
for(User user : users){
ugl.setUser(user);
ugl.setGroup(groupImpl);
session.delete("com.mybatis.manytomany.mapper.groupMapper.deleteGroupUser",ugl );
}
}
//删除组信息
session.delete("com.mybatis.manytomany.mapper.groupMapper.deleteGroup", groupImpl);
}else{
throw new RuntimeException("查询的组不存在!!");
}
session.commit();
}finally{
session.close();
}
}
/**
*
* 更新组状态， 变成不可见时，取消该组下的用户与该组的映射关系
*/
public static void updateGroupStateTest(){
SqlSession session = null;
try{
session = sqlSessionFactory.openSession();
Group group = new Group();
group.setName("用户组2");
Group groupImpl = (Group) session.selectOne("com.mybatis.manytomany.mapper.groupMapper.selectGroupUser",group);
if(groupImpl != null){
int state = groupImpl.getState() == 1 ? 0 : 1;
System.out.println(state);
//组状态由0变成1时，即由可见变为不可见
if(state == 1){
List<User> users = groupImpl.getUser();
//查看用户组2中是否存在用户
if(users != null && users.size() > 0){
//存在用户时，删除组与用户的对应关系
UserGroupLink ugl = new UserGroupLink();
for(User user : users){
ugl.setUser(user);
ugl.setGroup(groupImpl);
session.delete("com.mybatis.manytomany.mapper.groupMapper.deleteGroupUser",ugl );
}
}
}
session.commit();
}
else{
throw new RuntimeException("查询的组不存在!!");
}
}finally{
session.close();
}
}
public static void main(String[] args) {
// System.out.println("==============一对一查询，根据车票来查顾客===============");
// selectTicketById(1);
// System.out.println("==============一对多查询，根据顾客来查车票===============");
// selectCustomerByName("小王");
// saveUserTest();
// getUserAndGroupTest();
// saveUserAndGroupTest();
// deleteGroupTest();
updateGroupStateTest();
}
}
sql语句：
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(40) collate utf8_unicode_ci default NULL,
`password` varchar(20) collate utf8_unicode_ci default NULL,
`createtime` timestamp NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
CREATE TABLE `group_info` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(40) collate utf8_unicode_ci default NULL,
`createdate` timestamp NULL default CURRENT_TIMESTAMP,
`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,
`createdate` timestamp NULL default CURRENT_TIMESTAMP,
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`)
) mybatis多对多总结：
1、在有关联关系两个实体之间建立一个实体，用于保存前两者之间的关系（实际开发过程中会不会造成大量这种中间表）
2、中间表创建时有两个外键分别来自有关联的两个实体表的主键
3、创建关联关系时在两者之间创建完还需要在中间表创建（麻烦），删除的时候还要删除中间表（关系）
上面代码还有很多需要改进的地方，学习阶段先这样吧。
扫一扫
1198
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
