MyBatis通过注解实现多表联查：一对一/多对一关系

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

[注意]使用MyBatis的注解的话，项目中不能出现相关dao接口的映射配置文件。

一、MyBatis的常用注解说明

@Insert:实现新增
@Update:实现更新
@Delete:实现删除
@Select:实现查询
@Result:实现结果集封装
@Results:可以与@Result 一起使用，封装多个结果集
@ResultMap:实现引用@Results 定义的封装
@One:实现一对一结果集封装
@Many:实现一对多结果集封装
@SelectProvider: 实现动态 SQL 映射
@CacheNamespace:实现注解二级缓存的使用

二、使用步骤

1.数据库配置关联表

User表：

Account表：此为从表，以uid为外键，关联主表User表中的id

2.JavaBean类的属性

User：

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

Account：

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

2.Dao接口的注解

2.1 一对一查询

查询Account表中的数据，同时查询一对一的对应关系的数据，这里配置了@Results，其中@Result中的column、property分别是指数据库中对应的列名和javaBean类的属性名，如果你的JavaBean类的属性名和数据库的列名是一致的，也可以省略掉。
但最后一个@Result中除了配了对应关系，同时还配了个one属性，这里面的select指的是调用相关类的方法，需要用全限定类名.方法名，需要注意这个方法也要有对应的注解，mybatis会把column = “uid”的这个值传给调用的方法作为参数，然后把查询的结果封装到property=“user”，即封装到javabean类中的对应的属性。fetechTyper是配置加载方式的。

public interface AccountDao {
  @Select("select  * from account")
  @Results(id="accountMap",value = {
      @Result(id = true,column = "id",property = "id"),
      @Result(column = "uid",property = "uid"),
      @Result(column = "money",property = "money"),
      @Result(property = "user",column = "uid",one =
      @One(select="mediacomm.dao.UserDao.findUserById",fetchType= FetchType.DEFAULT))
  })
  List<Account> findAccountWithUser();

public interface UserDao {
@Select("select * from user where id=#{id}")
  User findUserById(int id);

测试结果：

Account{id=1, uid=16, money=11.2}
User{id=16, username='gge', password='zfb', address='dd', accounts=null, roles=null}
2021-07-15 17:07:55,096 3477   [           main] DEBUG          mediacomm.dao.UserDao  - Cache Hit Ratio [mediacomm.dao.UserDao]: 0.0
Account{id=2, uid=16, money=1200.0}
User{id=16, username='gge', password='zfb', address='dd', accounts=null, roles=null}
2021-07-15 17:07:55,097 3478   [           main] DEBUG          mediacomm.dao.UserDao  - Cache Hit Ratio [mediacomm.dao.UserDao]: 0.0
Account{id=3, uid=16, money=1300.0}
User{id=16, username='gge', password='zfb', address='dd', accounts=null, roles=null}
2021-07-15 17:07:55,097 3478   [           main] DEBUG          mediacomm.dao.UserDao  - Cache Hit Ratio [mediacomm.dao.UserDao]: 0.0
Account{id=4, uid=16, money=4444.0}
User{id=16, username='gge', password='zfb', address='dd', accounts=null, roles=null}

2.1 一对多查询

查询User表中的数据，同时查询一对多的对应关系的数据。
跟一对一的配置大致相同，不过这里不再是用one而是用到many的属性。

public interface UserDao {
  @Select(value = "select * from user")
  @Results(id = "userMap",value = {
      @Result(id = true,column = "id",property = "id"),
      @Result(column = "username",property = "username"),
      @Result(column = "password",property = "password"),
      @Result(column = "address",property = "address"),
      @Result(property = "accounts",column = "id",
          many = @Many(select = "mediacomm.dao.AccountDao.findAccountByUid",fetchType = FetchType.DEFAULT))
  })
  List<User> findAllUser();

public interface AccountDao {
 @Select("select * from account where uid=#{uid}")
  List<Account> findAccountByUid(int uid);

测试结果：

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

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

  @Test
  public void findMoreUser(){
    List<User> users = userDao.findAllUser();
    for (User user : users) {
      System.out.println(user);
    }
  }

  @After
  public void close() throws IOException {
    if(in != null){
      in.close();
    }
    if(session != null){
      session.close();
    }
  }
}

User{id=15, username='wer', password='afef', address='火星', accounts=[], roles=null}
2021-07-15 17:16:40,914 937    [           main] DEBUG ao.AccountDao.findAccountByUid  - ==>  Preparing: select * from account where uid=? 
2021-07-15 17:16:40,914 937    [           main] DEBUG ao.AccountDao.findAccountByUid  - ==> Parameters: 16(Integer)
2021-07-15 17:16:40,917 940    [           main] DEBUG ao.AccountDao.findAccountByUid  - <==      Total: 4
User{id=16, username='gge', password='zfb', address='dd', accounts=[Account{id=1, uid=16, money=11.2}, Account{id=2, uid=16, money=1200.0}, Account{id=3, uid=16, money=1300.0}, Account{id=4, uid=16, money=4444.0}], roles=null}
2021-07-15 17:16:40,922 945    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@13c27452]
2021-07-15 17:16:40,923 946    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 331510866 to pool.

---

补充

注解中的@Results(id = “userMap”，以后别的方法如果是一样的配置，可以直接复用，用 @ResultMap(value = “userMap”)引用到id即可