mybatis复习笔记三——多表查询

r囧r小猫 2023-01-07 08:56 178阅读 0赞

# 用户和账户的关系（一对多）         一个用户可以有多个账户：         一个账户只能属于一个用户(多个账户也可以属于同一个用户)   步骤:   1、数据库中建立两张表:用户表，账户表，让用户表和账户表之间具备-对多的关系:需要使用外键在账户表中添加 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 1][]

# #

# 2、建立两个实体类:用户实体类和账户实体类，让用户和账户的实体类能体现出啦一对多的关系 #

# 用户表：  #

# private List<Account> accounts;//主类user要有副类的属性集合，用来实现接收一个用户中的多个账户 #

package com.itcast.dao.domin;
    
    import java.sql.Date;
    import java.util.List;
    
    public class User {
        private int uid;
        private String username;
        private Date birthday;
        private String sex;
        private String address;
        private List<Account> accounts;//用来实现接收一个用户中的多个账户的集合
    
        public List<Account> getAccounts() {
            return accounts;
        }
    
        public void setAccounts(List<Account> accounts) {
            this.accounts = accounts;
        }
    
        public int getUid() {
            return uid;
        }
    
        public void setUid(int uid) {
            this.uid = uid;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date 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;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "uid=" + uid +
                    ", username='" + username + '\'' +
                    ", birthday=" + birthday +
                    ", sex='" + sex + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

# 账户表： #

package com.itcast.dao.domin;
    
    public class Account {
        private int aid;
        private int uid;
        private double money;
    
        @Override
        public String toString() {
            return "Account{" +
                    "aid=" + aid +
                    ", uid=" + uid +
                    ", money=" + money +
                    '}';
        }
    
        public int getAid() {
            return aid;
        }
    
        public void setAid(int aid) {
            this.aid = aid;
        }
    
        public int getUid() {
            return uid;
        }
    
        public void setUid(int uid) {
            this.uid = uid;
        }
    
        public double getMoney() {
            return money;
        }
    
        public void setMoney(double money) {
            this.money = money;
        }
    }

#       3、建立配置文件         用户的配置文件 #

<?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.itcast.dao.IUser">
    
        <resultMap id="userAccountMap" type="com.itcast.dao.domin.User">
            <id property="uid" column="uid"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
            <collection property="accounts" ofType="com.itcast.dao.domin.Account">
                <id property="aid" column="aid"></id>
                <result property="uid" column="uid"></result>
                <result property="money" column="money"></result>
            </collection>
        </resultMap>
    
        <select id="findAll"  resultMap="userAccountMap" >
        select * from user u left outer join account a on a.uid=u.uid
      </select>
    
    </mapper>

#    4、实测试置:         当我们查询用户时，可以同时得到用户下所包含的账户信息     #

import com.itcast.dao.IAccount;
    import com.itcast.dao.IRole;
    import com.itcast.dao.IUser;
    import com.itcast.dao.domin.Account;
    import com.itcast.dao.domin.QueryVo;
    import com.itcast.dao.domin.Role;
    import com.itcast.dao.domin.User;
    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.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class UserTest {
        InputStream in;
        SqlSessionFactoryBuilder builder;
        SqlSessionFactory factory;
        SqlSession session;
        IUser User;
        IAccount iAccount;
        IRole iRole;
    
        @Before
        public void Init() throws IOException {
            //1,读取配置文件，得到sqlMapConfig.xml文件流，用于构建SqlSessionFactory的工厂对象
            in = Resources.getResourceAsStream("sqlMapConfig.xml");
            //2,创建SqlSessionFactory工厂
            builder = new SqlSessionFactoryBuilder();//SqlSessionFactory的实现类
            factory = builder.build(in);//SqlSessionFactory是一个接口，不可以直接实例化
            //3,使用工厂生产SqlSession对象
            session = factory.openSession();
            //4,使用session创建dao代理对象
            User = session.getMapper(IUser.class);
            iAccount =session.getMapper(IAccount.class);
            iRole = session.getMapper(IRole.class);
        }
    
        @After
        public void closeAll() throws IOException {
            session.close();
            in.close();
        }
    
        @Test
        public void findAll() throws IOException {
            Init();
            List<User> Users = User.findAll();
            for (User user : Users) {
                System.out.println("一对多关系");
                System.out.println(user);
                System.out.println(user.getAccounts());
            }
            closeAll();
        }
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 2][]

# 多对多：一个user可以有多个role，一个role可以对应多个user #

# 1，数据库表user，role，user\_role。user\_role中的uid和rid外键连接user，role #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 5][]

# 2，编写实体类和接口 #

# user,有存放role的list #

package com.itcast.dao.domin;
    
    import java.sql.Date;
    import java.util.List;
    
    public class User {
        private int uid;
        private String username;
        private Date birthday;
        private String sex;
        private String address;
        private List<Role> roles;
    
        /**
         * user与role的多对多关系
         * @return
         */
        public List<Role> getRoles() {
            return roles;
        }
    
        public void setRoles(List<Role> roles) {
            this.roles = roles;
        }
    
        public int getUid() {
            return uid;
        }
    
        public void setUid(int uid) {
            this.uid = uid;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date 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;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "uid=" + uid +
                    ", username='" + username + '\'' +
                    ", birthday=" + birthday +
                    ", sex='" + sex + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

# role，有存放user的list #

package com.itcast.dao.domin;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class Role implements Serializable {
        private int rid;
        private String roleName;
        private String roleDesc;
        private List<User> users;//实现user与role多对多的关系
    
        public List<User> getUsers() {
            return users;
        }
    
        public void setUsers(List<User> users) {
            this.users = users;
        }
    
        @Override
        public String toString() {
            return "Role{" +
                    "rid=" + rid +
                    ", roleName='" + roleName + '\'' +
                    ", roleDesc='" + roleDesc + '\'' +
                    '}';
        }
    
        public int getRid() {
            return rid;
        }
    
        public void setRid(int rid) {
            this.rid = rid;
        }
    
        public String getRoleName() {
            return roleName;
        }
    
        public void setRoleName(String roleName) {
            this.roleName = roleName;
        }
    
        public String getRoleDesc() {
            return roleDesc;
        }
    
        public void setRoleDesc(String roleDesc) {
            this.roleDesc = roleDesc;
        }
    }

IUser接口

ackage com.itcast.dao;
    
    import com.itcast.dao.domin.QueryVo;
    import com.itcast.dao.domin.Role;
    import com.itcast.dao.domin.User;
    import com.itcast.dao.domin.User;
    
    import java.util.List;
    
    /**学生的持久层接口*/
    public interface IUser {
        /**
         * 实现多对多
         * @return
         */
        List<User> ManyToMany();
    }

3，编写配置文件

IUserDao.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.itcast.dao.IUser">
    
        <resultMap id="userRoleMap" type="com.itcast.dao.domin.User">
            <id property="uid" column="uid"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
            <collection property="roles" ofType="com.itcast.dao.domin.Role">
                <id property="rid" column="rid"></id>
                <result property="roleName" column="roleName"></result>
                <result property="roleDesc" column="roleDesc"></result>
            </collection>
        </resultMap>
    
    <select id="ManyToMany"  resultMap="userRoleMap" >
             SELECT u.*,r.rid,r.roleName ,r.roleDesc FROM USER u
         LEFT OUTER JOIN user_role ur ON u.uid=ur.uid
         LEFT OUTER JOIN role r ON ur.rid=r.rid;
      </select>
    
    </mapper>

# IRoleDao.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.itcast.dao.IRole">
    
        <resultMap id="userRole" type="com.itcast.dao.domin.Role">
            <id property="rid" column="rid"></id>
            <result property="roleName" column="roleName"></result>
            <result property="roleDesc" column="address"></result>
            <collection property="users" ofType="com.itcast.dao.domin.User">
                <id property="uid" column="uid"></id>
                <result property="username" column="username"></result>
                <result property="address" column="address"></result>
                <result property="sex" column="sex"></result>
                <result property="birthday" column="birthday"></result>
            </collection>
        </resultMap>
        <select id="findAll"  resultMap="userRole" >
            SELECT u.*,r.rid,r.roleName ,r.roleDesc FROM role r
             LEFT OUTER JOIN user_role ur ON r.rid=ur.rid
             LEFT OUTER JOIN USER u ON ur.uid=u.uid;
      </select>
    
    </mapper>

新建配置文件的时候，要记住去总配置文件sqlMapConfig.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="mysql">
    
            <environment id="mysql">
    
                <transactionManager type="JDBC"></transactionManager>
    
                <dataSource type="POOLED">
    
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/stu"/>
                    <property name="username" value="root"/>
                    <property name="password" value="1234"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
    
            <mapper resource="com/itcast/dao/IStudentDao.xml"/>
            <mapper resource="com/itcast/dao/IAccountDao.xml"/>
            <mapper resource="com/itcast/dao/IUserDao.xml"/>
            <mapper resource="com/itcast/dao/IRoleDao.xml"/>
        </mappers>
    </configuration>

4，编写测试

import com.itcast.dao.IAccount;
    import com.itcast.dao.IRole;
    import com.itcast.dao.IUser;
    import com.itcast.dao.domin.Account;
    import com.itcast.dao.domin.QueryVo;
    import com.itcast.dao.domin.Role;
    import com.itcast.dao.domin.User;
    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.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class UserTest {
        InputStream in;
        SqlSessionFactoryBuilder builder;
        SqlSessionFactory factory;
        SqlSession session;
        IUser User;
        IAccount iAccount;
        IRole iRole;
    
        @Before
        public void Init() throws IOException {
            //1,读取配置文件，得到sqlMapConfig.xml文件流，用于构建SqlSessionFactory的工厂对象
            in = Resources.getResourceAsStream("sqlMapConfig.xml");
            //2,创建SqlSessionFactory工厂
            builder = new SqlSessionFactoryBuilder();//SqlSessionFactory的实现类
            factory = builder.build(in);//SqlSessionFactory是一个接口，不可以直接实例化
            //3,使用工厂生产SqlSession对象
            session = factory.openSession();
            //4,使用session创建dao代理对象
            User = session.getMapper(IUser.class);
            iRole = session.getMapper(IRole.class);
        }
    
        @After
        public void closeAll() throws IOException {
            session.close();
            in.close();
        }
    
        @Test
        public void manyToMany() throws IOException {
            Init();
            List<Role> roles = iRole.findAll();
            for (Role stu : roles) {
                System.out.println("role与user的关系");
                System.out.println(stu);
                System.out.println(stu.getUsers());
            }
            closeAll();
        }
        @Test
        public void many() throws IOException {
            Init();
            List<User> users = User.ManyToMany();
            for (User user: users) {
                System.out.println("user与role的关系");
                System.out.println(user);
                System.out.println(user.getRoles());
            }
            closeAll();
        }
    
    
    
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 7][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70]: /images/20221119/b8dd43f5869f4ee8bf7589a1935dea87.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 1]: /images/20221119/cd4beabf4dfd4652b6b018eaf03e70e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 2]: /images/20221119/00d381a73e2f4cbdb4f869a9a6939723.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 3]: /images/20221119/79070a876f7347489e25c8fad40ec916.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 4]: /images/20221119/27c7108e151d414fbd8da5cf8714df5a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 5]: /images/20221119/572d591931464cd2afebdd9cefbcc653.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 6]: /images/20221119/de9a34c8b7ec44ed8ad82dc080737cec.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDg0MTMxMg_size_16_color_FFFFFF_t_70 7]: /images/20221119/679c2c4f136244de9f18d82baaee63b1.png