Mybatis关联查询(一对多,多对一)
目录
mybatis关联查询(一对多和多对一)
1 创建DBUtils工具类
public class DBUtils {
private static SqlSessionFactory factory = null;
static {
try {
String mybatis_config = "mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(mybatis_config);
factory = new SqlSessionFactoryBuilder().build(in);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取SqlSession
public static SqlSession getSqlSession() {
return factory.openSession(true);
}
// 获取mapper
public static <T> T getMapper(Class<T> mapper) {
return getSqlSession().getMapper(mapper);
}
}
2 准备数据
用户和账户两个表
用户表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`address` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
账户表
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int(11) NOT NULL,
`uid` int(11) DEFAULT NULL COMMENT '用户id',
`money` double(20, 0) DEFAULT NULL COMMENT '账户余额',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
插入用户数据
INSERT INTO `user` VALUES (1, '张三', 18, '河北');
INSERT INTO `user` VALUES (2, '李四', 29, '北京');
INSERT INTO `user` VALUES (3, '王五', 30, '天津');
INSERT INTO `user` VALUES (4, '赵六', 28, '广州');
INSERT INTO `user` VALUES (5, '杨七', 88, '上海');
插入账户数据
INSERT INTO `account` VALUES (1, 1, 1000);
INSERT INTO `account` VALUES (2, 1, 3000);
INSERT INTO `account` VALUES (3, 3, 5000);
3 mybatis一对多查询
ResultMap格式:
<resultMap id="唯一的标识" type="映射的pojo对象">
<id column="表的主键字段(数据库表中的字段)" jdbcType="字段类型" property="映射pojo对象的主键属性(实体类中的属性)" />
<result column="表字段名或者别名(定义别名后不是能原来的字段名)" jdbcType="字段类型" property="映射到pojo对象的一个属性(实体类中的属性)"/>
<!--多个标签<result>...-->
<!-- 一对多用collection标签-->
<collection property="pojo的集合属性名(实体类中多的一方的集合)" ofType="集合中的pojo对象的类型">
<id column="主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
<result column="表字段名或者别名" jdbcType="字段类型" property="集合中的pojo对象的属性" />
<!--多个标签<result>...-->
</collection>
</resultMap>
案例
一个用户可以有多个账户, 查询所有的用户,
用户类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer id;
private String name;
private Integer age;
private String address;
//一对多映射集合
private List<Account> accounts;
}
账户类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
private Integer id;
private Integer uid;
private Double money;
}
实现UserMapper接口
public interface UserMapper {
List<User> findAllUser();
}
方案一:联表查询(按照结果嵌套处理)
<mapper namespace="com.tedu.dao.UserMapper">
<resultMap id="userAccountMap" type="user">
<id column="uid" property="id"/>
<result column="uname" property="name"/>
<result column="uage" property="age"/>
<result column="uaddress" property="address"/>
<collection property="accounts" javaType="ArrayList" ofType="Account">
<id column="aid" property="id"/>
<result column="auid" property="uid"/>
<result column="amoney" property="money"/>
</collection>
</resultMap>
<select id="findAllUser" resultMap="userAccountMap">
select
u.id uid,
u.name uname,
u.age uage,
u.address uaddress,
a.id aid,
a.uid auid,
a.money amoney
from user u
left join account a
on u.id =a.uid;
</select>
方案二: 按查询嵌套处理
<select id="findAllUser" resultMap="userAccountMap">
select * from user
</select>
<select id="findAccountById" resultType="Account">
select * from account where uid =#{id}
</select>
<resultMap id="userAccountMap" type="user">
<!-- 数据库的字段名和pojo对象中的属性名一致,可以不用映射,但是要再次基于id执行嵌套查询,则需将id表明.将查询结果封装-->
<id property="id" column="id"/>
<collection column="id" property="accounts" ofType="Account" javaType="ArrayList" select="findAccountById"/>
</resultMap>
方案一二测试如下
// 查询所有的用户
@Test
public void test1() {
UserMapper mapper = DBUtil.getMapper(UserMapper.class);
List<User> allUser = mapper.findAllUser();
for (User user : allUser) {
System.out.println(user.getId()+","+user.getName()+","+user.getAddress()+","+user.getAge());
System.out.println(user.getAccounts());
}
}
测试结果如下:
1,张三,河北,18
[Account(id=1, uid=1, money=1000.0, user=null), Account(id=2, uid=1, money=3000.0, user=null)]
2,李四,北京,29
[]
3,王五,天津,30
[Account(id=3, uid=3, money=5000.0, user=null)]
4,赵六,广州,28
[]
5,杨七,上海,88
[]
4 mybatis多对一查询
resultMap格式:
<resultMap id="唯一的标识" type="映射的pojo对象">
<id column="表的主键字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
<result column="表字段名或者别名" jdbcType="字段类型" property="映射到pojo对象的一个属性"/>
<!--多个标签<result>...-->
<association property="pojo的对象属性名" javaType="引用配型">
<id column="主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
<result column="表字段名或者别名" jdbcType="字段类型" property="集合中的pojo对象的属性" />
<!--多个标签<result>...-->
</association>
</resultMap>
案例
用户类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
private Integer id;
private String name;
private Integer age;
private String address;
}
账户类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
private Integer id;
private Integer uid;
private Double money;
//多对一映射
private User user;
}
AccountMapper类
//查询每个账户所对应的用户
public interface AccountMapper {
List<Account> findAllAccount();
}
方案一:按照结果处理
<mapper namespace="com.tedu.dao.AccountMapper">
<resultMap id="accountUserMap" type="Account">
<id column="aid" property="id"/>
<result column="auid" property="uid"/>
<result column="amoney" property="money"/>
<association property="user" javaType="User">
<id column="uid" property="id"/>
<result column="uname" property="name"/>
<result column="uage" property="age"/>
<result column="uaddress" property="address"/>
</association>
</resultMap>
<select id="findAllAccount" resultMap="accountUserMap">
select
a.id aid,
a.uid auid,
a.money amoney,
u.id uid,
u.name uname,
u.age uage,
u.address uaddress
from account a
left join user u
on a.uid =u.id;
</select>
</mapper>
方案二:按查询嵌套
<select id="findAllAccount" resultMap="AccountUserMap">
select * from account
</select>
<select id="findUserById" resultType="User">
select * from user where id =#{uid}
</select>
<resultMap id="AccountUserMap" type="Account">
<!-- 数据库的字段名和pojo对象中的属性名一致,可以不用映射,但是要再次基于uid执行嵌套查询,则需将uid表明.将u查询结果封装-->
<id property="uid" column="uid"/>
<association property="user" javaType="User" select="findUserById" column="uid"/>
</resultMap>
方案一二测试如下
//查询多(一)个账户对应一个用户
@Test
public void test2() {
AccountMapper mapper = DBUtil.getMapper(AccountMapper.class);
List<Account> allAccount = mapper.findAllAccount();
for (Account account : allAccount) {
System.out.println(account);
}
}
测试结果如下:
Account(id=1, uid=null, money=1000.0, user=User(id=1, name=张三, age=18, address=河北, accounts=null))
Account(id=2, uid=null, money=3000.0, user=User(id=1, name=张三, age=18, address=河北, accounts=null))
Account(id=3, uid=null, money=5000.0, user=User(id=3, name=王五, age=30, address=天津, accounts=null))
5 总结
一对多查询时使用collection
多对一查询时使用association
posted on 2020-06-27 21:35 liqiangbk 阅读(1156) 评论(0) 编辑 收藏 举报
