【mybatis实现多对多查询】

一时失言乱红尘 2024-03-31 10:10 113阅读 0赞

## 数据库表 ##

user表

![c0db9d64ca2c4611b0ec565217f4aac1.png][]

role表

![dffe09479a00439aa8925aecc8101fcf.png][]

user\_role表

![c8d4c1ee19cc46088e6046871ef88c82.png][]

员工jack有两个角色：java架构师和java开发工程师

员工pitter有两个角色：前端开发工程师和开发经理

需求：查询员工-角色中间表，同时关联显示员工表和角色表的信息

数据库查询语句：

SELECT u.*,r.`rid`,r.`rname` FROM role r
        LEFT JOIN user_role ur ON r.`rid`=ur.`RID`
             LEFT JOIN USER u ON u.`uid`= ur.`UID`

## 代码部分 ##

1、User.java

>     package com.qingruan.bean;
>     
>     public class User {
>     
>         private Integer uid;
>         private String username;
>         private String birthday;
>         private String sex;
>         private String address;
>     
>         //实现user到role的一对多查询
>         //private List<Role> roles;
>     
>         @Override
>         public String toString() {
>             return "User{" +
>                     "uid=" + uid +
>                     ", username='" + username + '\'' +
>                     ", birthday='" + birthday + '\'' +
>                     ", sex='" + sex + '\'' +
>                     ", address='" + address + '\'' +
>                     '}';
>         }
>     
>         public Integer getUid() {
>             return uid;
>         }
>     
>         public void setUid(Integer uid) {
>             this.uid = uid;
>         }
>     
>         public String getUsername() {
>             return username;
>         }
>     
>         public void setUsername(String username) {
>             this.username = username;
>         }
>     
>         public String getBirthday() {
>             return birthday;
>         }
>     
>         public void setBirthday(String birthday) {
>             this.birthday = birthday;
>         }
>     
>         public String getSex() {
>             return sex;
>         }
>     
>         public void setSex(String sex) {
>             this.sex = sex;
>         }
>     
>         public String getAddress() {
>             return address;
>         }
>     
>         public void setAddress(String address) {
>             this.address = address;
>         }
>     }

2、Role.java

>     package com.qingruan.bean;
>     
>     import java.util.List;
>     
>     public class Role {
>         private Integer roleId;
>     
>         private String roleName;
>     
>         //多对多关系映射，一个角色赋予多个用户
>         private List<User> Users;
>     
>         @Override
>         public String toString() {
>             return "Role{" +
>                     "roleId=" + roleId +
>                     ", roleName='" + roleName + '\'' +
>                     ", Users=" + Users +
>                     '}';
>         }
>     
>         public List<User> getUsers() {
>             return Users;
>         }
>     
>         public void setUsers(List<User> users) {
>             Users = users;
>         }
>     
>         public Integer getRoleId() {
>             return roleId;
>         }
>     
>         public void setRoleId(Integer roleId) {
>             this.roleId = roleId;
>         }
>     
>         public String getRoleName() {
>             return roleName;
>         }
>     
>         public void setRoleName(String roleName) {
>             this.roleName = roleName;
>         }
>     }

3、IRoleDao.java

>     package com.qingruan.dao;
>     
>     import com.qingruan.bean.Role;
>     
>     import java.util.List;
>     
>     public interface IRoleDao {
>         //查询所有角色
>         public List<Role> findAll();
>     }

4、RoleDao.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.qingruan.dao.IRoleDao">
        
        <resultMap id="roleMap" type="role">
            <id column="rid" property="roleId"></id>
            <result column="rname" property="roleName"></result>
            <collection property="Users" ofType="user">
                <id column="uid" property="uid"></id>
                <result column="username" property="username"></result>
                <result column="birthday" property="birthday"></result>
                <result column="sex" property="sex"></result>
                <result column="address" property="address"></result>
            </collection>
        </resultMap>
    
        <select id="findAll" resultMap="roleMap">
            SELECT u.*,r.`rid`,r.`rname` FROM role r
                LEFT JOIN user_role ur ON r.`rid`=ur.`RID`
                     LEFT JOIN USER u ON u.`uid`= ur.`UID`
        </select>
    
    </mapper>

5、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>
>     
>     
>         <settings>
>             <setting name="logImpl" value="LOG4J"/>
>         </settings>
>     
>         
>         <typeAliases>
>             
>             
>             <package name="com.qingruan.bean"/>
>         </typeAliases>
>     
>         
>         <environments default="mysql">
>             <environment id="mysql">
>                 
>                 <transactionManager type="JDBC"/>
>                 <dataSource type="POOLED">
>                     <property name="driver" value="com.mysql.jdbc.Driver"/>
>                     
>                     <property name="url" value="jdbc:mysql://localhost:3306/mydata1?useUnicode=true&characterEncoding=UTF-8"/>
>                     <property name="username" value="root"/>
>                     <property name="password" value="1234"/>
>                 </dataSource>
>             </environment>
>         </environments>
>     
>     
>         <mappers>
>             <mapper resource="com/qingruan/dao/RoleDao.xml"></mapper>
>     
>         </mappers>
>     
>     </configuration>

6、MybatisTest.java

>     package com.qingruan.test;
>     
>     import com.qingruan.bean.Role;
>     import com.qingruan.dao.IRoleDao;
>     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 org.junit.After;
>     import org.junit.Before;
>     import org.junit.Test;
>     
>     import java.io.InputStream;
>     import java.util.List;
>     
>     public class MybatisTest {
>         private InputStream is;
>         private SqlSessionFactoryBuilder builder;
>         private SqlSessionFactory sessionFactory;
>         private SqlSession session;
>         private IRoleDao dao;
>     
>         @Before //在测试方法执行之前执行
>         public void init() throws  Exception{
>             is=Resources.getResourceAsStream("mybatis-config.xml");
>             //创建sqlSessionFacotory的构建着对象
>             builder=new SqlSessionFactoryBuilder();
>             //通过构建着对象创建工厂对象
>             sessionFactory = builder.build(is);
>             //获得session对象
>             session = sessionFactory.openSession();
>             //使用session创建dao接口的代理对象
>             dao = session.getMapper(IRoleDao.class);
>         }
>     
>         @After  //在测试方法执行完成之后执行
>         public void destroy() throws  Exception{
>             //提交事务  增删改后一定要控制事物的提交
>             session.commit();
>             //方式资源
>             session.close();
>             is.close();
>         }
>     
>     
>        @Test
>         public void testFindAll(){
>            List<Role> roles = dao.findAll();
>            for (Role role : roles) {
>                System.out.println(role);
>                //System.out.println(role.getUsers());
>     
>            }
>     
>     
>        }
>     
>     }

## 运行结果 ##

![5d78b32e05b34e25a131d5db9f3c2a23.png][]

[c0db9d64ca2c4611b0ec565217f4aac1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d0f1f407fbef446bb1c78730ec20a917.png
[dffe09479a00439aa8925aecc8101fcf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/439dd3e57cfd4350be6e62f28b173302.png
[c8d4c1ee19cc46088e6046871ef88c82.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/71bb4780aeb2453b80b3853676a1db9a.png
[5d78b32e05b34e25a131d5db9f3c2a23.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/173a656f2d9b4affb545b07b52998520.png