目录
本文通过实例演示Mybatis的多表查询,主要配置依然与之前的文章保持一致。
传送门: 使用Mybatis完成CRUD操作,并从源码分析执行过程
整体包含两个案例,涵盖一对一、一对多、多对多这几种多表关系。
实例一:用户和钱包(一对多)
----->项目源码
假设存在某加密货币交易网站——
一个用户可以有多个钱包;
一个钱包只能属于一个用户(多个钱包也可能属于同一个用户)。
1.建表及实体类
- user表:
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` datetime default NULL COMMENT '生日',
`sex` char(1) default NULL COMMENT '性别',
`address` varchar(256) default NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (1,'郭靖','2018-02-27 17:47:08','男','河北'),(2,'刘备','2018-03-02 15:09:37','男','河南'),(3,'中本聪','2018-03-04 11:34:34','男','日本'),(4,'西施惠','2018-03-04 12:04:06','女','苏州'),(5,'马云','2018-03-07 17:37:26','男','杭州'),(6,'林志玲','2018-03-08 11:44:00','女','北京');
- wallet表:
其中的uid为外键,指向user表的id
DROP TABLE IF EXISTS `wallet`;
CREATE TABLE `wallet` (
`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 `wallet`(`ID`,`UID`,`MONEY`) values (1,2,1000),(2,5,1000),(3,6,2000);
User实体类:
Wallet实体类:
对应的DAO接口:
映射配置:
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="com.com.zhu.dao.IUserDao">
<!-- 查询所有 -->
<select id="findAll" resultType="userMap">
select * from user
</select>
<!-- 根据id查询用户 -->
<select id="findById" resultType="INT" resultMap="userMap">
select * from user where id = #{uid}
</select>
</mapper>
IWalletDao.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.com.zhu.dao.IWalletDao">
<!-- 查询所有 -->
<select id="findAll" resultType="wallet">
select * from user
</select>
<!-- 根据id查询钱包 -->
<select id="findById" resultType="INT" resultMap="wallet">
select * from user where id = #{uid}
</select>
</mapper>
测试类:
/**
* User测试类
*/
public class UserTest {
private InputStream in;
private SqlSession sqlSession;
private IUserDao userDao;
@Before//用于在测试方法执行之前执行
public void init()throws Exception{
//1.读取配置文件,生成字节输入流
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.获取SqlSessionFactory
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//3.获取SqlSession对象
sqlSession = factory.