小粥学Mybatis(14)之多表操作----一对一/一对多

小粥学Mybatis(14)之多表操作----一对一/一对多

数据库环境

创建账户表

DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `ID` int(11) NOT NULL COMMENT '编号',
  `UID` int(11) default NULL COMMENT '用户编号',
  `MONEY` double default NULL COMMENT '金额',
  PRIMARY KEY  (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

账户表添加数据

insert  into `account`(`ID`,`UID`,`MONEY`) values (1,41,1000),(2,45,1000),(3,41,2000);

创建实体类

User

/**
 *@ClassName User
 *@Description TODO
 *@Author Yin.Liu8
 *@Date 2021/3/1 10:43
 *@Version 1.0
 */
public class User {

    private Integer id;

    private String username;

    private Date birthday;

    private String sex;

    private String address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

Account

/**
 *@ClassName Account
 *@Description TODO
 *@Author Yin.Liu8
 *@Date 2021/3/2 12:08
 *@Version 1.0
 */
public class Account implements Serializable {
    private Integer id;

    private Integer uid;

    private Double money;

    //从表实体，应该包含主表实体的对象引用
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

DAO

IUserDAO

/**
 * Description:用户的持久层接口
 * <p>
 * ClassName: IUserDAO
 * date: 2021/3/1 10:47
 *
 * @author Yin.Liu
 * @version 1.0
 * @since JDK 1.8
 */
public interface IUserDAO {

    /**
     * 查询所有操作
     * @return
     */
    List<User> findAll();
    
}

IAccountDAO

/**
 * Description:
 * <p>
 * ClassName: IAccountDAO
 * date: 2021/3/2 12:10
 *
 * @author Yin.Liu
 * @version 1.0
 * @since JDK 1.8
 */
public interface IAccountDAO {

    /**
     * 查询所有账户
     * @return
     */
    List<Account> findAll();
}

mapper.xml

IUserMapper.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">
<!--namespace必须是dao接口的全限定类名-->
 <mapper namespace="xyz.zhouzhousag.dao.IUserDAO">


    <select id="findAll" resultType="user">

        select * from user

    </select>




    <select id="findByIdUser" parameterType="Integer" resultType="user">
        select * from user where id = #{id}
    </select>


</mapper>

IAccountMapper.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="xyz.zhouzhousag.dao.IAccountDAO">

    <!--定义封装account和user的resultMap-->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--一对一的关系映射：配置user的内容-->
        <!--column：用哪个字段获取的-->
        <association property="user" column="uid" javaType="user">
            <result property="id" column="id"></result>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </association>

    </resultMap>


    <select id="findAll" resultMap="accountUserMap">

        select
            t1.*,
            t2.ID aid,
            t2.MONEY,
            t2.UID
        from
            user t1,
            account t2
        where
            t1.id = t2.UID

    </select>

</mapper>

测试

测试类

public class AccountTest {
    private InputStream in;
    private SqlSession sqlSession;
    private IUserDAO userDAO;
    private IAccountDAO accountDAO;

    @Before //用于测试方法之前使用
    public void init() throws Exception {
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建SqlSessionFactory工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3.使用工厂生产 SqlSession对象
        sqlSession = factory.openSession(true);
        //4.使用SqlSession创建DAO接口的代理对象
        userDAO = sqlSession.getMapper(IUserDAO.class);
        accountDAO = sqlSession.getMapper(IAccountDAO.class);
    }


    @After   //用于测试方法之后
    public void destroy() throws Exception {
        //6.释放资源
        sqlSession.close();
        in.close();
    }
    /**
     * 查询所有
     * @param
     */
    @Test
    public void mybatisTest() {
        List<Account> accounts = accountDAO.findAll();
        for (Account account :accounts){
            System.out.println(account.toString());
        }
    }
}

测试结果

Account{id=1, uid=41, money=1000.0, user=User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}}

Account{id=2, uid=45, money=1000.0, user=User{id=45, username='传智播客', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='北京金燕龙'}}

Account{id=3, uid=41, money=2000.0, user=User{id=41, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}}

一对多

如：一个用户的账户信息，

在主表写明从表的引用对象。

class User{
    private Integer id;
    private String username;
    .....
        
    private List<Account> accountList;  //从表的引用对象
}

对象集合映射，

使用collection标签,

ofType属性：集合中元素的类型

<resultMap id="accountUserMap" type="account">
    <id property="id" column="aid"></id>
    <result property="uid" column="uid"></result>
    <result property="money" column="money"></result>
    
    <!--集合中元素的类型-->
    <collection property="accountList" ofType="account">
        <result property="id" column="id"></result>
        <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>