mybatis（六）多表连接之多对多

本文主要是通过接口+映射文件的方式实现项目
一.创建一个java项目，新建javaProject
二.添加mybatis和mysql链接的jar包
三.创建数据库，特别注意创建外键
四.编写mybstis-cofig.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">
<!-- mybatis-cofig配置文件 -->
<configuration>
    <!--配置别名 -->
    <typeAliases>
        <typeAlias type="com.mybatis.manyToMany.pojo.User" alias="user" />
        <typeAlias type="com.mybatis.manyToMany.pojo.Group" alias="group" />
        <typeAlias type="com.mybatis.manyToMany.pojo.UserGroup"
            alias="userGroup" />
    </typeAliases>
    <!-- 创建数据层 运行环境 -->
    <environments default="development">
        <environment id="development">
            <!-- JDBC事务管理 -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/manytomany" />
                <property name="username" value="root" />
                <property name="password" value="110119" />
            </dataSource>
        </environment>
    </environments>
    <!-- 映射文件群 -->
    <mappers>
        <!-- 实体类对应的映射文件 -->
        <mapper resource="com/mybatis/manyToMany/mapper/UserMapper.xml" />
        <mapper resource="com/mybatis/manyToMany/mapper/GroupMapper.xml" />
        <mapper resource="com/mybatis/manyToMany/mapper/UserGroupMapper.xml" />
    </mappers>
</configuration>

五.创建实体类
1）User.java

/**
 * @author wuchao
 * @time 上午9:47:09
 * @description TODO
 */
package com.mybatis.manyToMany.pojo;

import java.util.List;

/**
 * @author wuchao
 * @time 上午9:47:09
 * 
 */
public class User {

    private int userId;
    private String userName;
    private String userMobile;
    private List<Group> groupList;

    /**
     * @author wuchao
     * @time 上午9:54:51
     */
    public User() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @author wuchao
     * @time 上午9:54:59
     */
    public User(int userId, String userName, String userMobile,
            List<Group> groupList) {
        super();
        this.userId = userId;
        this.userName = userName;
        this.userMobile = userMobile;
        this.groupList = groupList;
    }

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserMobile() {
        return userMobile;
    }

    public void setUserMobile(String userMobile) {
        this.userMobile = userMobile;
    }

    public List<Group> getGroupList() {
        return groupList;
    }

    public void setGroupList(List<Group> groupList) {
        this.groupList = groupList;
    }

}

2）Group.java

/**
 * @author wuchao
 * @time 上午9:50:45
 * @description TODO
 */
package com.mybatis.manyToMany.pojo;

import java.util.List;

/**
 * @author wuchao
 * @time 上午9:50:45
 * 
 */
public class Group {

    private int groupId;
    private String groupName;
    private List<User> userList;

    /**
     * @author wuchao
     * @time 上午9:56:18
     */
    public Group() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @author wuchao
     * @time 上午9:56:25
     */
    public Group(int groupId, String groupName, List<User> userList) {
        super();
        this.groupId = groupId;
        this.groupName = groupName;
        this.userList = userList;
    }

    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;
    }

    public List<User> getUserList() {
        return userList;
    }

    public void setUserList(List<User> userList) {
        this.userList = userList;
    }

}

3)UserGroup.java

/**
 * @author wuchao
 * @time 上午9:52:24
 * @description TODO
 */
package com.mybatis.manyToMany.pojo;

/**
 * @author wuchao
 * @time 上午9:52:24
 * 
 */
public class UserGroup {

    private int userGroupId;
    private int userId;
    private int groupId;

    /**
     * @author wuchao
     * @time 上午10:20:49
     */
    public UserGroup() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @author wuchao
     * @time 上午10:20:58
     */
    public UserGroup(int userGroupId, int userId, int groupId) {
        super();
        this.userGroupId = userGroupId;
        this.userId = userId;
        this.groupId = groupId;
    }

    public int getUserGroupId() {
        return userGroupId;
    }

    public void setUserGroupId(int userGroupId) {
        this.userGroupId = userGroupId;
    }

    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;
    }

}

六.创建接口
1)UserMapper.java

/**
 * @author wuchao
 * @time 上午9:44:06
 * @description TODO
 */
package com.mybatis.manyToMany.dao;

import java.util.List;

import com.mybatis.manyToMany.pojo.User;

/**
 * @author wuchao
 * @time 上午9:44:06
 *
 */
public interface UserMapper {

    public User getUserById(int userId);
    public List<User> getAllUserGroupe();
}

2)GroupMapper.java

/**
 * @author wuchao
 * @time 上午9:45:04
 * @description TODO
 */
package com.mybatis.manyToMany.dao;

import com.mybatis.manyToMany.pojo.Group;

/**
 * @author wuchao
 * @time 上午9:45:04
 *
 */
public interface GroupMapper {

    public Group getGroupById(int groupId); 
}

3)UserGroupMapper.java

/**
 * @author wuchao
 * @time 上午9:46:24
 * @description TODO
 */
package com.mybatis.manyToMany.dao;

import java.util.List;

import com.mybatis.manyToMany.pojo.Group;
import com.mybatis.manyToMany.pojo.User;
import com.mybatis.manyToMany.pojo.UserGroup;

/**
 * @author wuchao
 * @time 上午9:46:24
 *
 */
public interface UserGroupMapper {

    public UserGroup getUserGroupById(int userGroupId);
    public List<Group> getGroupListByUserId(int userId);
    public List<User> getUserListByGroupId(int groupId);
}

七.创建与实体类对应的映射文件xml
1）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.dao.UserMapper">
    <resultMap type="user" id="resultUserMap">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name" />
        <result property="userMobile" column="user_mobile" />
        <collection property="groupList" ofType="group" column="grouper_id">
            <id property="groupId" column="grouper_id" />
            <result property="groupName" column="grouper_name" />
        </collection>
    </resultMap>
    <select id="getUserById" parameterType="int" resultMap="resultUserMap">
        select *
        from user u
        where u.user_id = #{userId}
    </select>
    <select id="getAllUserGroupe" resultMap="resultUserMap">
        select u.*,g.*
        from user u
        inner join
        user_grouper ug
        on u.user_id = ug.user_id
        inner join
        grouper g
        on g.grouper_id = ug.grouper_id
    </select>
    <select id="getGroupByUserId" resultMap="resultUserMap">
        select g.*
        from grouper g ,user_grouper ug
        where g.grouper_id = ug.grouper_id and ug.user_id = #{userId}
    </select>
</mapper>

2）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.dao.GroupMapper">
    <resultMap type="group" id="resultGroupMap">
        <id property="groupId" column="grouper_id" />
        <result property="groupName" column="grouper_name" />
    </resultMap>
    <select id="getGroupById" parameterType="int" resultMap="resultGroupMap">
        select *
        from grouper g
        where g.grouper_id = #{groupId}
    </select>
</mapper>

3)UserGroupMapper.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.dao.UserGroupMapper">
    <resultMap type="user" id="resultUserListUserGroupMap">
        <id property="userId" column="user_id" />
        <result property="userName" column="user_name" />
        <result property="userMobile" column="user_mobile" />
    </resultMap>
    <resultMap type="group" id="resultGroupListUserGroupMap">
        <id property="groupId" column="grouper_id" />
        <result property="groupName" column="grouper_name" />
    </resultMap>

    <!-- 根据用户ID，查询用户所在的用户组 -->
    <select id="getGroupListByUserId" parameterType="group"
        resultMap="resultGroupListUserGroupMap">
        select g.*
        from grouper g,user_grouper ug
        where g.grouper_id=ug.grouper_id and ug.user_id=#{userId}
    </select>

    <!-- 根据用户组ID，查询用户组下的所有用户 -->
    <select id="getUserListByGroupId" parameterType="user"
        resultMap="resultUserListUserGroupMap">
        select u.*
        from user u, user_grouper ug
        where u.user_id = ug.user_id and ug.grouper_id = #{groupId}
    </select>
</mapper>

八.测试类，测试结果和项目结构
1)Test.java

/**
 * @author wuchao
 * @time 上午10:21:47
 * @description TODO
 */
package com.mybatis.manyToMany.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.mybatis.manyToMany.dao.GroupMapper;
import com.mybatis.manyToMany.dao.UserGroupMapper;
import com.mybatis.manyToMany.dao.UserMapper;
import com.mybatis.manyToMany.pojo.Group;
import com.mybatis.manyToMany.pojo.User;
import com.mybatis.manyToMany.pojo.UserGroup;

/**
 * @author wuchao
 * @time 上午10:21:47
 * 
 */
public class Test {

    private static Reader reader;
    private static SqlSessionFactory sqlSessionFactory;
    /**
     * @author wuchao
     * 这段静态代码块，是通过读取mybatis的配置来创建sqlSessionFactory
     */
    static {
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    /**
     * 
     * @author wuchao
     * @time 下午8:44:26
     * 获取session
     */
    public SqlSessionFactory getSession() {
        return sqlSessionFactory;
    }

    /**
     * 主方法
     * @author wuchao
     * @time 下午8:44:49
     */
    public static void main(String args[]) {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            //根据用户ID，查询用户所在的用户组 
            //getGroupListByUserId(1);
            //获取所有的信息
            //getAllUserGroupe();
            //getUserById(1);
            //根据用户组ID，查询用户组下的所有用户
            getUserListByGroupId(1);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    /**
     * 通过userId获取user
     * @author wuchao
     * @time 下午8:45:07
     */
    public static void getUserById(int userId) {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            User user = userMapper.getUserById(userId);
            System.out.println("查询到的UserId为：" + user.getUserId());
            System.out.println("查询到的UserName为：" + user.getUserName());
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            session.close();
        }
    }


    /** 根据用户ID，查询用户所在的用户组 
     * @author wuchao
     */
    public static void getGroupListByUserId(int userId){
        SqlSession session = sqlSessionFactory.openSession();
        try {
            UserGroupMapper userGroupMapper = session.getMapper(UserGroupMapper.class);
            List<Group> groupList = userGroupMapper.getGroupListByUserId(userId);
            System.out.println("查询到的结果有"+groupList.size()+"个");
            System.out.println("00"+groupList.getClass());
            for(Group group : groupList){
                System.out.println("groupId:"+group.getGroupId());
                System.out.println("groupName"+group.getGroupName());
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            session.close();
        }
    }
    /**
     * 根据用户组ID，查询用户组下的所有用户
     * @author wuchao
     * @time 下午8:46:11
     */
    public static void getUserListByGroupId(int groupId){
        SqlSession session = sqlSessionFactory.openSession();
        try {
            GroupMapper groupMapper = session.getMapper(GroupMapper.class);
            Group group = groupMapper.getGroupById(groupId);
            System.out.println("group的名字是："+group.getGroupName());
            UserGroupMapper userGroupMapper = session.getMapper(UserGroupMapper.class);
            List<User> userList = userGroupMapper.getUserListByGroupId(groupId);
            for(User user : userList){
                System.out.println("通过group查询到user的ID是："+user.getUserId());
                System.out.println("通过group查询到user的NAME是："+user.getUserName());
                System.out.println("通过group查询到user的MOBILE是："+user.getUserMobile());
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            session.close();
        }
    }

    /**
     * @author wuchao
     * 获取所有信息
     */
    public static void getAllUserGroupe(){
        SqlSession session = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = session.getMapper(UserMapper.class);
            List<User> userList = userMapper.getAllUserGroupe();
            for(User user : userList){
                for(Group group : user.getGroupList()){
                    System.out.println(user.getGroupList().size());
                    System.out.println("groupId:"+group.getGroupId());
                    System.out.println("groupName:"+group.getGroupName());
                }
                System.out.println("userId:"+user.getUserId());
                System.out.println("userName:"+user.getUserName());
                System.out.println("userMobile:"+user.getUserMobile());
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }finally{
            session.close();
        }
    }
}