使用多表查询,就得将两个实体类放在一块:
package com.quxiao.mybatis.pojo;
public class textAndUser {
private Text text;
private User user;
@Override
public String toString() {
String str=user.getId()+" "+user.getName()+" "+user.getAge()+" "+user.getGender()+" "+user.getEmail()+" "+text.getTime();
return str;
}
}
这是我自己想的一个办法,然后来说说我们这次的两个多表查询的案例:
既然是多表查询,就得关联多个表,所以sql就和以前有一些不同了,而且要使用别名的方式将每个字段给定别名,别名为类名.属性
<select id="selectTextAndUser" resultType="com.quxiao.mybatis.pojo.textAndUser">
select u.id "user.id", u.name "user.name", u.gender "user.gender",
t.time "text.time"
from user u
join text t on u.id = t.id
where t.id = #{id};
</select>
返回值直接为多个实体类的关联类:selectTextAndUser
然后另一种办法就是将所有的列一一对应:
<select id="selectTextAndUser" resultMap="userAndtextMap">
select u.id, u.name, u.gender, t.time
from user u
join text t on u.id = t.id
where t.id = #{id};
</select>
<resultMap id="userAndtextMap" type="com.quxiao.mybatis.pojo.textAndUser">
<result property="user.id" column="id"></result>
<result property="user.name" column="name"></result>
<result property="user.age" column="age"></result>
<result property="user.gender" column="gender"></result>
<result property="user.email" column="email"></result>
<result property="text.time" column="time"></result>
</resultMap>
这里有几点要注意一下:
property:表示接下来你在sql中将要使用的字段的key
column: 则可以理解为value,属于数据库的字段名,必须相同!
一对多关系:
非常经典的绑定关系展示,现在都是先查询父级,然后通过父级id查询子级。
其实可以通过mybatis的一对多来直接查询所有的数据:
<?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.stock1Server.mapper.UserMapper">
<resultMap id="getUserMap" type="com.stock1Server.pojo.vo.UserVO">
<result property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userCode" column="user_code"/>
<collection property="workIds" javaType="ArrayList" ofType="com.stock1Server.entity.WorktableEntity">
<result property="i1" column="i1"/>
<result property="i2" column="i2"/>
<result property="i3" column="i3"/>
<result property="i4" column="i4"/>
</collection>
</resultMap>
<select id="getUser" resultMap="getUserMap">
select user.*,
worktable.*
from user
left join worktable on worktable.user_id = user.id
</select>
</mapper>
返回就是这样:
[
{
"id": 14,
"userName": "aaa",
"userCode": "111",
"workIds": [
{
"i1": 2,
"i2": "多了一个表",
"i3": "2024-02-21T14:48:27.000+00:00",
"i4": 1,
"data": "dsaaa",
"userId": 14
},
{
"i1": 1,
"i2": "20212904",
"i3": "2024-02-20T14:48:27.000+00:00",
"i4": 1,
"data": "dsaaa",
"userId": 14
}
]
},
{
"id": 15,
"userName": "11",
"userCode": "er",
"workIds": []
},
{
"id": 16,
"userName": "11",
"userCode": "aaa",
"workIds": []
}
]