（八）mybatis之多对多

一、需求分析

• 需求：查询所有用户的信息以及每个用户所属的组的信息
• 分析：一个用户可以有多个组，一个组也可以由多个用户。
• 多对多，可以设置一张中间表，该表存放的是用户表和组表的对应关系。

二、创建数据库和实体对象

 User.java

package com.shyroke.entity;

import java.util.List;

public class User {
    private int id;
    private String userName;
    private String passWord;

    //一个用户有多个组
    private List<Group> groupList;
//隐藏get和set方法
｝

 

 Group.java

package com.shyroke.entity;

import java.util.List;

public class Group {
    private int id;
    private String group_name;
    private String group_num;

//一个组有多个用户
    private List<User> userList;

}

 

 三、创建UserMapper.java接口和UserMapper.xml配置文件

 UserMapper.java

package com.shyroke.mapper;

import java.util.List;

import com.shyroke.entity.User;

public interface UserMapper {
        public List<User> selectUserAndGroup();
}

 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.shyroke.mapper.UserMapper">

    <resultMap type="com.shyroke.entity.User" id="userMap">
        <id column="id" property="id" />
        <result column="userName" property="userName" />
        <result column="passWord" property="passWord" />
        <collection property="groupList" ofType="com.shyroke.entity.Group">
            <id column="group_id" property="id" />
            <result column="group_name" property="group_name" />
            <result column="group_num" property="group_num" />
        </collection>
    </resultMap>

    <select id="selectUserAndGroup" resultMap="userMap">
        select
        users.*,groups.*
        from
        users,groups,user_group
        where
        users.id=user_group.m_user_id
        and groups.group_id=user_group.m_group_id

    </select>
</mapper>

 

四、添加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>
    <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/test?useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC" />
                <property name="username" value="root" />
                <property name="password" value="" />
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper class="com.shyroke.mapper.UserMapper"/>
    </mappers>
   
</configuration>

 

五、测试

package com.shyrolk.firstMybatis;

import java.io.IOException;
import java.io.InputStream;
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.shyroke.entity.User;
import com.shyroke.mapper.UserMapper;

/**
 * Hello world!
 *
 */
public class App {
    public static void main(String[] args) throws IOException {
        String resource = "resource/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession session = sessionFactory.openSession();

        UserMapper userMapper= session.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectUserAndGroup();

    
        
        for(User user:userList){
            
            System.out.println(user.getId());
            System.out.println(user.getPassWord());
            System.out.println(user.getUserName());
            System.out.println(user.getGroupList());
        }

    }
}

结果：