实现mybatis多对多功能

最新推荐文章于 2024-04-01 20:29:05 发布

原创最新推荐文章于 2024-04-01 20:29:05 发布 · 337 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #框架 #mybatis

本文介绍如何在MyBatis中配置和使用多对多关系。通过创建用户表(user)、用户组表(grouper)以及关联表(user_group)，详细阐述了建立和操作这种关系的方法，包括SQL语句的示例。

在上一篇中我们介绍了mybatis如何实现一对多的功能，下面我们为大家介绍一下如何实现多对多功能

首先我们创建本篇所需要的数据库表，user为用户表，grouper为用户组表，而user_group表则是关联表用来将前面两张表的关系关联起来，表结构与插入的sql语句如下：

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `mobile` varchar(16) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', 'yiibai', '13838009988');
INSERT INTO `user` VALUES ('2', 'User-name-1', '13838009988');



CREATE TABLE `grouper` (
  `group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_name` varchar(254) NOT NULL DEFAULT '',
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `grouper` VALUES ('1', 'Group-1');
INSERT INTO `grouper` VALUES ('2', 'Group-2');


CREATE TABLE `user_group` (
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `group_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user_group` VALUES ('1', '1');
INSERT INTO `user_group` VALUES ('2', '1');
INSERT INTO `user_group` VALUES ('1', '2');

第二步编写各个表所对应的实体类，其中User类加上一个List<Grouper>的方法用于在查询后接收查询多对应的值，而Grouper类也一样不过是加上List<User>而已，User_group则不变
用户 User表里面的代码：

package com.zhiyuan.com;

import java.util.List;

public class User {

		private int id;
		private String username;
		private String mobile;
		private List<Grouper> grouper;
		public List<Grouper> getGrouper() {
			return grouper;
		}
		public void setGrouper(List<Grouper> grouper) {
			this.grouper = grouper;
		}
		public int getId() {
			return id;
		}
		public void setId(int id) {
			this.id = id;
		}
		public String getUsername() {
			return username;
		}
		public void setUsername(String username) {
			this.username = username;
		}
		public String getMobile() {
			return mobile;
		}
		public void setMobile(String mobile) {
			this.mobile = mobile;
		}
		
	}

用户组Grouper类里面的代码为：

package com.zhiyuan.com;

import java.util.List;

public class Grouper {
	private int groupId;
	private String groupName;
	private List<User> users;
	
	public List<User> getUsers() {
		return users;
	}
	public void setUsers(List<User> users) {
		this.users = users;
	}
	public int getGroupId() {
		return groupId;
	}
	public void setGroupId(int groupId) {
		this.groupId = groupId;
	}
	public String getGroupName() {
		return groupName;
	}
	public void setGroupName(String groupName) {
		this.groupName = groupName;
	}
	
}

关联表User_group里的代码为：

package com.zhiyuan.com;

public class User_group {
		private int userId;  
	    private int groupId;
		public int getUserId() {
			return userId;
		}
		public void setUserId(int userId) {
			this.userId = userId;
		}
		public int getGroupId() {
			return groupId;
		}
		public void setGroupId(int groupId) {
			this.groupId = groupId;
		}
	 
}

第三步就是创建实体类所对应的映射接口与xml配置，接口里的方法要与它所对应的XML配置里面的操作ID相同
User所对应的映射接口与xml配置如下：

package com.zhiyuan.model;

import com.zhiyuan.com.User;

public interface UserMapper {
	public User getselect(int id);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.zhiyuan.model.UserMapper">
	<resultMap type="User" id="nihao">
	<result property="id" column="id"/>
	<result property="username" column="username"/>
	<result property="mobile" column="mobile"/>
	<collection property="grouper"	column="id" select="com.zhiyuan.model.User_group.getGroupsByUserId"></collection>
	</resultMap>
 	<select id="getselect" resultMap="nihao">
 		SELECT * FROM  user where user.id=#{id}
 	</select>
</mapper>

Grouper所对应的映射接口与xml配置如下：

package com.zhiyuan.model;

import com.zhiyuan.com.Grouper;

public interface GroupMapper {
	
	public Grouper getgrouper(int id);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.zhiyuan.model.GroupMapper">

	<resultMap type="Grouper" id="Remo">
	<result property="groupId" column="group_id"/>
	<result property="groupName" column="group_name"/>
	<collection property="users" column="group_id" select="com.zhiyuan.model.User_group.getUsersByGroupId"></collection>
	
	</resultMap>
  	<select id="getgrouper" resultMap="Remo" parameterType="int">
  		SELECT * FROM grouper WHERE group_id=#{group_id}
  	</select>
</mapper>

user_grouper所对应的映射接口与xml配置如下：

package com.zhiyuan.model;

import java.util.List;

import com.zhiyuan.com.Grouper;
import com.zhiyuan.com.User;

public interface User_group {
	public List<Grouper> getGroupsByUserId(int id);
	public List<User> getUsersByGroupId(int id);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.zhiyuan.model.User_group">
  	<parameterMap type="UserGroup" id="parameterUserGroupMap">
		<parameter property="userId"/>
		<parameter property="groupId"/>
	</parameterMap>
	
	<insert id="insertUserGroup"  parameterMap="parameterUserGroupMap">
		INSERT INTO user_group(user_id, group_id)
		VALUES(#{userId},#{groupId})
	</insert>
	
	<!-- 根据一个用户组ID,查看这个用户组下的所有用户 -->
	<resultMap type="User" id="resultUserMap_2">
		<result property="id" column="id"/>
		<result property="username" column="username"/>
		<result property="mobile" column="mobile"/>
	</resultMap>
	
	<select id="getUsersByGroupId" resultMap="resultUserMap_2" parameterType="int">
		SELECT u.*, ug.group_id
		FROM user u, user_group ug
		WHERE u.id=ug.user_id AND ug.group_id=#{group_id}
	</select>
	
	<!-- 根据一个用户ID,查看这个用户所对应的组-->
	<resultMap type="Grouper" id="resultGroupMap_2">
		<result property="groupId" column="group_id"/>
		<result property="groupName" column="group_name"/>
	</resultMap> 
	
	<select id="getGroupsByUserId" resultMap="resultGroupMap_2" parameterType="int">
		SELECT g.*, u.user_id
		FROM grouper g, user_group u
		WHERE g.group_id=u.group_id AND u.user_id=#{user_id}
	</select>
</mapper>

第四步编写mybatis主配置文件config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD config 3.0//EN" "mybatis-3-config.dtd" >
<configuration>
	
	<typeAliases>
		<typeAlias alias="User" type="com.zhiyuan.com.User" />
		<typeAlias alias="UserGroup" type="com.zhiyuan.com.User_group" />
		<typeAlias alias="Grouper" type="com.zhiyuan.com.Grouper" />
	</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://127.0.0.1:3306/duoduiduo" />
				<property name="username" value="root" />
				<property name="password" value=" " />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<mapper resource="com/zhiyuan/model/UserMapper.xml" />
		<mapper resource="com/zhiyuan/model/GroupMapper.xml" />
		<mapper resource="com/zhiyuan/model/User_Group.xml" />
	</mappers>

</configuration>

第五步编写测试类进行测试：

package com.zhiyuan.ol;

import java.io.IOException;
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.zhiyuan.com.Grouper;
import com.zhiyuan.com.User;
import com.zhiyuan.model.GroupMapper;
import com.zhiyuan.model.UserMapper;

public class Main {
	public static SqlSessionFactory sql;
	public static Reader redf;
	
	static{
		try {
			redf =Resources.getResourceAsReader("config.xml");
			sql=new SqlSessionFactoryBuilder().build(redf);
			
			
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	public static void main(String[] args) {
		slet();
	}
	public static void select(){
		SqlSession sqll=sql.openSession();
		UserMapper UserMappe=sqll.getMapper(UserMapper.class);
		User op=UserMappe.getselect(1);
		System.out.println(op.getUsername());
		List<Grouper> oppp=op.getGrouper();
		for (Grouper ol:oppp) {
			System.out.println(ol.getGroupName());
		}
	}
	public static void slet(){
		SqlSession sqll=sql.openSession();
		GroupMapper GroupMapper=sqll.getMapper(GroupMapper.class);
		Grouper grouper=GroupMapper.getgrouper(1);
		System.out.println(grouper.getGroupName());
		List<User> user=grouper.getUsers();
		for (User ui:user) {
			System.out.println(ui.getUsername());
		}
		
	}
}

最后完成