多表查询分一下4种。
一对一,一对多,多对一(mybatis映射为一对一),多对多。
要求:数据库操作熟练,会写多表查询。
mybatis主要的操作方式:
1,SQL语句直接查询封装
2,调用接口方法查询。
一对多映射:
例如,一个用户(人)有多个账号。根据关系可得类属性:
public class User implements Serializable {
private Integer id;
private String name;
private List<Account> accounts;
}
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
private User user;
}
接口方法:
public interface IAccountDao {
List<Account> findAll();
Account findById(Integer id);
List<Account>findAccountsByUid(Integer uid);
}
public interface IUserDao {
List<User> findAll();
User findById(Integer id);
List<Account> findAccountsByUid(Integer id);
}
我们想要在查询某一个用户的时候,顺便把该用户的账号都查一遍。Mapper映射文件如下
<?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="dao.IUserDao">
<!-- id唯一名称,方便引用,type指的是Java类型-->
<resultMap id="userMap" type="user">
<!-- 数据库主键字段-->
<id property="id" column="id"></id>
<!-- 数据库非主键字段-->
<result property="name" column="name"></result>
<!-- select="dao.IAccountDao.findAccountsByUid"-->
<!-- Java类中的属性-->
<!-- property指定属性,column指定根据字段联查,ofType指定集合泛型类型 -->
<collection property="accounts" column="id" ofType="account" >
<id column="aid" property="id"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select * from users;
</select>
<select id="findById" resultMap="userMap" parameterType="int">
select u.*,a.id aid,a.uid,a.money
from users u
left join account a
on u.id = a.uid
where u.id=#{uid};
</select>
</mapper>
test代码:
IUserDao userDao= session.getMapper(IUserDao.class);
User u = userDao.findById(1);
System.out.println(u);
for (Account account : u.getAccounts()) {
System.out.println(account);
}
}
执行结果:
可以看到,的确是成功了。但是我们也可以看到缺点。该查询的select语句过长,几乎就是通过查询SQL,将数据库的列和属性封装成的。有点麻烦。但是,作为主流的dao框架,自然有结局方案。
解决:引用其他接口的方法。
<?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="dao.IUserDao">
<!-- id唯一名称,方便引用,type指的是Java类型-->
<resultMap id="userMap" type="user">
<!-- 数据库主键字段-->
<id property="id" column="id"></id>
<!-- 数据库非主键字段-->
<result property="name" column="name"></result>
<!-- select="dao.IAccountDao.findAccountsByUid"-->
<!-- Java类中的属性-->
<!-- property指定属性,column指定根据字段联查,ofType指定集合泛型类型 -->
<collection property="accounts" column="id" ofType="account" select="dao.IAccountDao.findAccountsByUid">
<!-- <id column="aid" property="id"></id>-->
<!-- <result column="uid" property="uid"></result>-->
<!-- <result column="money" property="money"></result>-->
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select * from users;
</select>
<select id="findById" resultMap="userMap" parameterType="int">
select * from users where id=#{id}
</select>
</mapper>
运行结果:
我们可以看到这是一样的。所以有这么简单的方法我们为什么不用?我们观察不同,其Collection标签中添加了select属性标签。引用了namespace+id。
一对一映射。
例如一个账户对应一个用户。账户的Mapper为
<?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="dao.IAccountDao">
<resultMap id="accountMap" type="account">
<id property="id" column="id"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"/>
<association column="uid" property="user" javaType="user" select="dao.IUserDao.findById">
<!-- <id property="id" column="id"></id>-->
<!-- <result property="name" column="name"></result>-->
</association>
</resultMap>
<select id="findAll" resultMap="accountMap">
select *from account;
</select>
<select id="findById" resultType="account" parameterType="int">
select * from account where id = #{id}
</select>
<select id="findAccountsByUid" resultType="account" parameterType="int">
select * from account where uid = #{uid}
</select>
</mapper>
我们可以看到与一对多的差别。1,association和collection差别。2,JavaType和ofType。同样都是使用select语句。
多对多查询
例如:student与teacher。一个学生对应多个老师,一个老师对应多个学生。
实体类:
public class Teacher implements Serializable {
private Integer tid;
private String tname;
private List<Student> students;
}
public class User implements Serializable {
private Integer id;
private String name;
private List<Account> accounts;
}
接口方法:
public interface IStudentDao {
List<Student> findAll();
// 通过sid来查找老师们
List<Teacher>findBySid(Integer sid);
}
public interface ITeacherDao {
List<Teacher> findAll();
// 通过tid来查找学生们
List<Student> findByTid(Integer tid);
}
Mapper文件:
学生:
<?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="dao.IStudentDao">
<resultMap id="studentMap" type="student">
<id property="sid" column="sid"></id>
<result property="sname" column="sname"></result>
<collection property="teachers" column="sid" ofType="teacher" select="dao.IStudentDao.findBySid"></collection>
</resultMap>
<select id="findAll" resultMap="studentMap">
select * from student;
</select>
<select id="findBySid" resultType="teacher" parameterType="int">
select *
from teacher
where tid in
(select st.tid
from stu_tea st
where st.sid=#{sid});
</select>
</mapper>
老师:
<?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="dao.ITeacherDao">
<resultMap id="teacherMap" type="teacher">
<id column="tid" property="tid"></id>
<result column="tname" property="tname"></result>
<collection property="students" column="tid" ofType="student" select="dao.ITeacherDao.findByTid"></collection>
</resultMap>
<select id="findAll" resultMap="teacherMap" >
select * from teacher;
</select>
<select id="findByTid" resultType="student" parameterType="int">
select * from student s
where s.sid in(select st.sid
from stu_tea st
where st.tid = #{tid}) ;
</select>
</mapper>
我们可以看到,两个resultMap中类型都是Collection,ofType。明显变化的就是多对多的SQL语句变长了。原因是3张表联查。两张表之间没有关系,要借助中间表。
测试代码:
@org.junit.Test
public void test(){
IStudentDao studentDao = session.getMapper(IStudentDao.class);
List<Teacher> teacherList = studentDao.findBySid(1);
for (Teacher teacher : teacherList) {
System.out.println(teacher);
}
}
结果:
备注:三张表数据如下
mysql> select * from student;
+-----+--------+
| sid | sname |
+-----+--------+
| 1 | 刘德华 |
| 2 | 梁朝伟 |
| 3 | 黄日华 |
| 4 | 汤镇业 |
| 5 | 周杰伦 |
+-----+--------+
5 rows in set (0.00 sec)
mysql> select * from stu_tea;
+------+------+
| sid | tid |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
| 1 | 3 |
| 5 | 3 |
+------+------+
10 rows in set (0.01 sec)
mysql> select * from teacher;
+-----+--------+
| tid | tname |
+-----+--------+
| 1 | 王老师 |
| 2 | 刘老师 |
| 3 | 张老师 |
| 4 | 马老师 |
+-----+--------+