MyBatis通过映射配置文件实现多表联查：一对一/多对一关系

文章内容仅提及多表查询的配置，省略了如domain类、dao接口、Mybatis配置文件等的配置说明。

前提配置

使用Mysql数据库，创建两个表，分别为用户表User和描述用户信息的Account表，在account表中添加列uid参照主表的列id的外键约束。
/*
*User表
/
/
*Account表
*/

测试一下一对一查询的sql语句：

测试一下一对多查询的sql语句：

实现类与dao接口

public class Account {
  private int id;
  private int uid;
  private double money;
  //一对一
  private User user;

public class User {
  private int id;
  private String username;
  private String password;
  private String address;
  //一对多
  private List<Account> accounts;

public interface AccountDao {
  /**
   * 查询用户同时获取到用户的关联信息
   * @return
   */
  //List<Account> findAllAccount();
  List<Account> findAccountWithUser();
}

public interface UserDao {
  List<User> findAllUser();
  /**
   * 一对多
   */
  List<User> findUserWithAccount();
}

一对一查询配置

实现一对一的关系查询，即一条account信息对应一条user信息
AccountDao.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.mediacomm.dao.AccountDao">
    <resultMap id="accountMap" type="account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!-- 一对一的关系映射，配置封装user的内容 -->
        <association property="user" column="uid" javaType="user">
            <id property="id" column="uid"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
    <select id="findAllAccount" resultType="account">
        select * from account;
    </select>
    <select id="findAccountWithUser" resultMap="accountMap">
        SELECT us.*,ac.id as aid,ac.money,ac.uid from account ac , user us where ac.uid = us.id;
    </select>

</mapper>

这里主要配置的就是resultMap了，配置javabean类中属性与数据库列名的对应关系，association是用来指定从表方的引用实体属性的。
注意最后写的findAccountWithUser中，是使用到resultMap作为接收结果值返回，与上文配置的resultMap相对应。

测试结果

执行测试

public class Test {
  private InputStream in;
  private SqlSession session;
  private AccountDao accountDao;
  private UserDao userDao;

  @Before
  public void init() throws IOException {
    in = Resources.getResourceAsStream("SqlConfig.xml");
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    SqlSessionFactory factory = builder.build(in);
    session = factory.openSession(true);
    accountDao = session.getMapper(AccountDao.class);
    userDao = session.getMapper(UserDao.class);

  }
  @org.junit.Test
  public  void find(){
    List<Account> list = accountDao.findAccountWithUser();
    for (Account ac:list){
      System.out.println(ac.getUser());
      System.out.println(ac);
    }
  }

输出结果

2021-07-13 11:08:21,442 4341   [           main] DEBUG AccountDao.findAccountWithUser  - <==      Total: 4
User{id=16, username='gge', password='zfb', address='dd', accounts=null}
Account{id=1, uid=16, money=1100.0}
User{id=16, username='gge', password='zfb', address='dd', accounts=null}
Account{id=2, uid=16, money=1200.0}
User{id=16, username='gge', password='zfb', address='dd', accounts=null}
Account{id=3, uid=16, money=1300.0}
User{id=16, username='gge', password='zfb', address='dd', accounts=null}
Account{id=4, uid=16, money=4444.0}
2021-07-13 11:08:21,443 4342   [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@192d3247]
2021-07-13 11:08:21,443 4342   [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 422392391 to pool.

一对多查询配置

实现一对多的关系查询，即一条user信息对应多条account信息
UserDao.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.mediacomm.dao.UserDao">
    <resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <result property="address" column="address"></result>
        <!-- User中的集合映射 -->
        <collection property="accounts" ofType="com.mediacomm.domain.Account">
            <id column="aid" property="id"></id>
            <result column="uid" property="uid"></result>
            <result column="money" property="money"></result>
        </collection>

    </resultMap>
    <select id="findAllUser" resultType="com.mediacomm.domain.User">
        select * from user ;
    </select>
    <!-- 一对多 -->
    <select id="findUserWithAccount" resultMap="userAccountMap">
        SELECT *from user u left OUTER JOIN account a on u.id = a.uid
    </select>
</mapper>

虽然知道使用这条sql语句查询后的结果会有重复项，但是不用担心，应为mybatis会自动识别到重复的内容，只保留一个。

测试结果

执行测试

  @org.junit.Test
  public void testFindUserOfAccounts(){
    List<User> list = userDao.findUserWithAccount();
    for (User user: list) {
      System.out.println(user);
    }
  }

输出结果

2021-07-13 14:47:55,912 4502   [           main] DEBUG ao.UserDao.findUserWithAccount  - <==      Total: 13
User{id=1, username='admin', password='admin', address='新西兰', accounts=[]}
User{id=2, username='ffff', password='123456', address='水星', accounts=[]}
User{id=3, username='null', password='null', address='null', accounts=[]}
User{id=10, username='aaa', password='112', address='火星', accounts=[]}
User{id=11, username='www', password='xxx', address='1111', accounts=[]}
User{id=12, username='gsrg', password='zz', address='gggg', accounts=[]}
User{id=13, username='12', password='wef', address='hhhh', accounts=[]}
User{id=14, username='ag', password='123kj1231lkr', address='xxx', accounts=[]}
User{id=15, username='wer', password='afef', address='火星', accounts=[]}
User{id=16, username='gge', password='zfb', address='dd', accounts=[Account{id=0, uid=16, money=1100.0}, Account{id=0, uid=16, money=1200.0}, Account{id=0, uid=16, money=1300.0}, Account{id=0, uid=16, money=4444.0}]}
2021-07-13 14:47:55,912 4502   [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@192d3247]