MyBatis07_多表查询一

最新推荐文章于 2022-07-22 17:15:06 发布

CodeAction

最新推荐文章于 2022-07-22 17:15:06 发布

阅读量110

点赞数

分类专栏： MyBatis 文章标签： mybatis

本文链接：https://blog.csdn.net/m0_47788964/article/details/106231677

版权

MyBatis 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

本教程源码请访问：tutorial_demo
之前的教程中，我们学习了MyBatis的单表CRUD，在实际的项目中，很多时候要用到多表的查询，今天我们就来学习一下，这篇教程用到的数据库脚本、pom.xml、SqlMapConfig.xml和《02_MyBatis快速入门》中是完全一样的，大家需要在这篇文章的基础上进行学习。

本案例分析“一对一”、“一对多”关系时，使用用户表（user）和账户表（account）来分析。

一、一对一查询

因为一个账户信息只能供某个用户使用，所以从查询账户信息（account）出发关联查询用户信息（user）为一对一查询。如果从用户信息（user）出发查询用户下的账户信息（account）则为一对多查询，因为一个用户可以有多个账户。

1.1、方式一

1.1.1、定义账户信息的实体类（Account）

package org.codeaction.domain;

import java.io.Serializable;

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;

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

1.1.2、定义包含账户信息及用户信息的类（AccountUser）

package org.codeaction.domain;

import java.io.Serializable;
import java.util.Date;

public class AccountUser extends Account implements Serializable {
    private String username;
    private String sex;
    private String address;
    private Date birthday;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

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

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return super.toString() + "       AccountUser{" +
                "username='" + username + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

1.1.3、SQL语句

select
	a.*, u.username username, u.sex sex, u.address address, u.birthday birthday
from
	account a, user u
where
	a.uid = u.id;

1.1.4、定义持久层Dao接口

package org.codeaction.dao;

import org.codeaction.domain.AccountUser;
import java.util.List;

public interface IAccountDao {
    List<AccountUser> findAll();
}

1.1.5、定义IAccountDao.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="org.codeaction.dao.IAccountDao">
    <!-- 配置查询所有 -->
    <select id="findAll" resultType="org.codeaction.domain.AccountUser">
        select
            a.*, u.username username, u.sex sex, u.address address, u.birthday birthday
        from
            account a, user u
        where
            a.uid = u.id;
    </select>
</mapper>

1.1.6、定义测试类

package org.codeaction.test;

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.codeaction.dao.IAccountDao;
import org.codeaction.domain.AccountUser;
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 MyBatisTest {
    private InputStream in;
    private SqlSession session;

    @Before
    public void init() throws IOException {
        //读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //使用工厂生产SqlSession对象
        session = factory.openSession();
    }

    @After
    public void destroy() throws IOException {
        //提交事务
        session.commit();
        //释放资源
        session.close();
        in.close();
    }

    @Test
    public void testFindAll() {
        IAccountDao accountDao = session.getMapper(IAccountDao.class);
        List<AccountUser> list = accountDao.findAll();

        list.forEach(System.out::println);
    }
}

运行测试方法，输出如下：

Account{id=1, uid=41, money=1000.0}       AccountUser{username='王一', sex='男', address='北京', birthday=Tue Dec 27 17:47:08 CST 2011}
Account{id=2, uid=45, money=1000.0}       AccountUser{username='Max', sex='男', address='西宁', birthday=Tue May 04 12:04:06 CST 2010}
Account{id=3, uid=41, money=2000.0}       AccountUser{username='王一', sex='男', address='北京', birthday=Tue Dec 27 17:47:08 CST 2011}

专门定义类作为输出类型，这种方式使用普遍。

1.2、方式二

使用resultMap，定义专门的resultMap用于映射一对一的查询结果。

1.2.1、修改账户信息的实体类（Account）

在Account类中加入User类的对象作为Account类的一个属性（User类的定义参考《02_MyBatis快速入门》）。

package org.codeaction.domain;

import java.io.Serializable;

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

1.2.2、修改持久层Dao接口

package org.codeaction.dao;

import org.codeaction.domain.Account;
import org.codeaction.domain.AccountUser;

import java.util.List;

public interface IAccountDao {
    List<AccountUser> findAll();
    List<Account> findAll1();
}

1.2.3、修改IAccountDao.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="org.codeaction.dao.IAccountDao">
    <resultMap id="AccountUser" type="org.codeaction.domain.Account">
        <id property="id" column="aid" />
        <result property="uid" column="uid" />
        <result property="money" column="money" />
        <association property="user" column="uid">
            <id property="id" column="id" />
            <result property="username" column="username" />
            <result property="birthday" column="birthday" />
            <result property="sex" column="sex" />
            <result property="address" column="address" />
        </association>
    </resultMap>
    <!-- 配置查询所有 -->
    <select id="findAll" resultType="org.codeaction.domain.AccountUser">
        select
            a.*, u.username username, u.sex sex, u.address address, u.birthday birthday
        from
            account a, user u
        where
            a.uid = u.id;
    </select>
    <!-- 配置查询所有 -->
    <select id="findAll1" resultMap="AccountUser">
        select
            a.id aid, a.uid uid, a.money money, u.*
        from
            account a, user u
        where
            a.uid = u.id;
    </select>
</mapper>

1.2.4、添加测试方法

@Test
public void testFindAll1() {
    IAccountDao accountDao = session.getMapper(IAccountDao.class);
    List<Account> list = accountDao.findAll1();

    list.forEach(System.out::println);
}

二、一对多查询

用户信息和账户信息是一对多的关系，一个用户可以对应多个账户，查询过程中如果用户没有账户信息，此时也要将用户信息查询出来，我们想到了左外连接查询比较合适。

2.1、修改User类

package org.codeaction.domain;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {
    private Integer id;
    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 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 + '\'' +
                ", accounts=" + accounts +
                '}';
    }
}

2.2、定义持久层Dao接口

package org.codeaction.dao;

import org.codeaction.domain.User;

import java.util.List;

public interface IUserDao {
    List<User> findAllUser();
}

2.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="org.codeaction.dao.IUserDao">
    <resultMap id="userAccountMap" type="org.codeaction.domain.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <!--
			collection：是用于建立一对多中集合属性的对应关系
			property：关联结果存储在哪个属性
			ofType：用于指定集合元素的数据类型
		-->
        <collection property="accounts" ofType="org.codeaction.domain.Account">
            <id property="id" column="aid"></id>
            <result property="money" column="money"></result>
            <result property="uid" column="uid"></result>
        </collection>
    </resultMap>
    <!-- 配置查询所有 -->
    <select id="findAllUser" resultMap="userAccountMap">
        select
            u.*, a.id aid, a.MONEY money, a.UID uid
        from
	        user u left join account a
        on
	        u.id = a.UID;
    </select>
</mapper>

2.4、添加测试方法

@Test
public void testFindAllUser() {
    IUserDao userDao = session.getMapper(IUserDao.class);
    List<User> list = userDao.findAllUser();

    list.forEach(System.out::println);
}

运行测试方法，控制台输出如下

User{id=41, username='王一', birthday=Tue Dec 27 17:47:08 CST 2011, sex='男', address='北京', accounts=[Account{id=1, uid=41, money=1000.0, user=null}, Account{id=3, uid=41, money=2000.0, user=null}]}
User{id=45, username='Max', birthday=Tue May 04 12:04:06 CST 2010, sex='男', address='西宁', accounts=[Account{id=2, uid=45, money=1000.0, user=null}]}
User{id=42, username='王二', birthday=Sat Mar 12 15:09:37 CST 2011, sex='女', address='上海', accounts=[]}
User{id=43, username='老李', birthday=Wed Mar 14 11:34:34 CST 2012, sex='女', address='天津', accounts=[]}
User{id=46, username='老王', birthday=Sat Aug 07 17:37:26 CST 1999, sex='女', address='拉萨', accounts=[]}
User{id=48, username='John', birthday=Mon Jan 08 11:44:00 CST 1990, sex='女', address='广州', accounts=[]}
User{id=50, username='Lucy', birthday=Wed Dec 03 20:09:32 CST 2008, sex='m', address='哈尔滨', accounts=[]}
User{id=58, username='张三', birthday=Fri May 15 18:50:04 CST 2020, sex='男', address='南昌', accounts=[]}

CodeAction

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis07_多表查询一

之前的教程中，我们学习了MyBatis的单表CRUD，在实际的项目中，很多时候要用到多表的查询，今天我们就来学习一下，这篇教程用到的数据库脚本、pom.xml、SqlMapConfig.xml和《02_MyBatis快速入门》中是完全一样的，大家需要在这篇文章的基础上进行学习。本案例分析“一对一”、“一对多”关系时，使用用户表（user）和账户表（account）来分析；本案例分析“多对多”关系时，使用用户表（user）、用户角色表(user_role)和角色表（role）来分析。一、一对一查询.
复制链接

扫一扫

专栏目录