MyBatis10_基于注解的多表查询
本教程源码请访问：tutorial_demo
上一篇文章我们学习了MyBatis使用注解实现单表的CRUD，通过上篇文章的学习我们对MyBatis基于注解开发有了一个基本的理解。这篇文章我们学习一下MyBatis使用注解实现多表查询。
一、复杂关系映射的注解说明
@Results注解
代替的是标签<resultMap>
该注解中可以使用单个@Result注解，也可以使用@Result集合
@Results({@Result()， @Result()})或@Results(@Result())
@Result注解
代替了<id>标签和<result>标签
@Result中属性介绍：
id是否是主键字段
column数据库的列名
property实体类的属性名
one需要使用的@One注解(@Result(one=@One) ()))
many需要使用的@Many注解(@Result(many=@many) ()))
@One注解(一对一)
代替了<assocation>标签，是多表查询的关键，在注解中用来指定子查询返回单一对象。
@One注解属性介绍：
select指定用来多表查询的sqlmapper
使用格式：
@Result(column=" ",property="",one=@One(select=""))
@Many 注解（多对一）
代替了<Collection>标签,是多表查询的关键，在注解中用来指定子查询返回对象集合。
使用格式：
@Result(property="",column="",many=@Many(select=""))
二、使用注解实现一对一查询
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 userId;
private String userName;
private Date userBirthday;
private String userSex;
private String userAddress;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Date getUserBirthday() {
return userBirthday;
}
public void setUserBirthday(Date userBirthday) {
this.userBirthday = userBirthday;
}
public String getUserSex() {
return userSex;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", userBirthday=" + userBirthday +
", userSex='" + userSex + '\'' +
", userAddress='" + userAddress + '\'' +
'}';
}
}
2.2、添加Account实体类
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 +
'}';
}
}
2.3、添加Account的持久层接口IAccountDao并使用注解配置
package org.codeaction.dao;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.codeaction.domain.Account;
import java.util.List;
public interface IAccountDao {
@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(column = "uid",
property = "user",
one=@One(select = "org.codeaction.dao.IUserDao.findById")
)
}
)
List<Account> findAll();
}
2.4、添加User的持久层接口IUserDao并使用注解配置
package org.codeaction.dao;
import org.apache.ibatis.annotations.*;
import org.codeaction.domain.User;
import java.util.List;
public interface IUserDao {
@Select("select * from user")
@Results(id = "userMap", value = {
@Result(id = true, column = "id", property = "userId"),
@Result(column = "username", property = "userName"),
@Result(column = "birthday", property = "userBirthday"),
@Result(column = "sex", property = "userSex"),
@Result(column = "address", property = "userAddress")
})
List<User> findAll();
@Select("select * from user where id=#{uid}")
@ResultMap(value = {"userMap"})
User findById(Integer id);
}
2.5、添加测试类
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.apache.log4j.net.SyslogAppender;
import org.codeaction.dao.IAccountDao;
import org.codeaction.dao.IUserDao;
import org.codeaction.domain.Account;
import org.codeaction.domain.User;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
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 testFindAllAccount() throws IOException {
//使用SqlSession创建Dao接口的代理对象
IAccountDao accountDao = session.getMapper(IAccountDao.class);
//使用代理对象执行方法
List<Account> list = accountDao.findAll();
list.forEach(account -> {
System.out.println(account);
System.out.println(account.getUser());
});
}
}
运行测试方法，控制台输出如下：
Account{id=1, uid=41, money=1000.0}
User{userId=41, userName='王一', userBirthday=Tue Dec 27 17:47:08 CST 2011, userSex='男', userAddress='北京'}
Account{id=2, uid=45, money=1000.0}
User{userId=45, userName='Max', userBirthday=Tue May 04 12:04:06 CST 2010, userSex='男', userAddress='西宁'}
Account{id=3, uid=41, money=2000.0}
User{userId=41, userName='王一', userBirthday=Tue Dec 27 17:47:08 CST 2011, userSex='男', userAddress='北京'}
三、使用注解实现一对多查询
3.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 userId;
private String userName;
private Date userBirthday;
private String userSex;
private String userAddress;
private List<Account> accounts;
public List<Account> getAccounts() {
return accounts;
}
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Date getUserBirthday() {
return userBirthday;
}
public void setUserBirthday(Date userBirthday) {
this.userBirthday = userBirthday;
}
public String getUserSex() {
return userSex;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", userBirthday=" + userBirthday +
", userSex='" + userSex + '\'' +
", userAddress='" + userAddress + '\'' +
", accounts=" + accounts +
'}';
}
}
3.2、修改IUserDao
package org.codeaction.dao;
import org.apache.ibatis.annotations.*;
import org.codeaction.domain.User;
import java.util.List;
public interface IUserDao {
@Select("select * from user")
@Results(id = "userMap", value = {
@Result(id = true, column = "id", property = "userId"),
@Result(column = "username", property = "userName"),
@Result(column = "birthday", property = "userBirthday"),
@Result(column = "sex", property = "userSex"),
@Result(column = "address", property = "userAddress"),
@Result(column = "id", property = "accounts",
many = @Many(select = "org.codeaction.dao.IAccountDao.findByUid")
)
})
List<User> findAll();
@Select("select * from user where id=#{uid}")
@ResultMap(value = {"userMap"})
User findById(Integer id);
}
3.3、修改IAccountDao
package org.codeaction.dao;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.codeaction.domain.Account;
import java.util.List;
public interface IAccountDao {
@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(column = "uid",
property = "user",
one=@One(select = "org.codeaction.dao.IUserDao.findById")
)
}
)
List<Account> findAll();
@Select("select * from account where uid = #{uid}")
List<Account> findByUid(Integer uid);
}
3.4、添加测试方法
@Test
public void testFindAllUser() {
//使用SqlSession创建Dao接口的代理对象
IUserDao userDao = session.getMapper(IUserDao.class);
//使用代理对象执行方法
List<User> list = userDao.findAll();
list.forEach(user -> {
System.out.println(user);
user.getAccounts().forEach(System.out::println);
});
}
运行测试方法，控制台输出如下：
User{userId=41, userName='王一', userBirthday=Tue Dec 27 17:47:08 CST 2011, userSex='男', userAddress='北京', accounts=[Account{id=1, uid=41, money=1000.0}, Account{id=3, uid=41, money=2000.0}]}
Account{id=1, uid=41, money=1000.0}
Account{id=3, uid=41, money=2000.0}
User{userId=42, userName='王二', userBirthday=Sat Mar 12 15:09:37 CST 2011, userSex='女', userAddress='上海', accounts=[]}
User{userId=43, userName='老李', userBirthday=Wed Mar 14 11:34:34 CST 2012, userSex='女', userAddress='天津', accounts=[]}
User{userId=45, userName='Max', userBirthday=Tue May 04 12:04:06 CST 2010, userSex='男', userAddress='西宁', accounts=[Account{id=2, uid=45, money=1000.0}]}
Account{id=2, uid=45, money=1000.0}
User{userId=46, userName='老王', userBirthday=Sat Aug 07 17:37:26 CST 1999, userSex='女', userAddress='拉萨', accounts=[]}
User{userId=48, userName='John', userBirthday=Mon Jan 08 11:44:00 CST 1990, userSex='女', userAddress='广州', accounts=[]}
User{userId=50, userName='Lucy', userBirthday=Wed Dec 03 20:09:32 CST 2008, userSex='m', userAddress='哈尔滨', accounts=[]}
User{userId=58, userName='张三', userBirthday=Fri May 15 18:50:04 CST 2020, userSex='男', userAddress='南昌', accounts=[]}
User{userId=59, userName='小李', userBirthday=Wed May 20 20:17:41 CST 2020, userSex='女', userAddress='CHN', accounts=[]}
微信公众号：CodeAction
Github：https://github.com/codeaction
