关联查询
-
实体间的关系(拥有 has、属于 belong)
-
OneToOne:一对一关系(account ←→ user)
-
OneToMany:一对多关系(user ←→ account)
-
ManyToMany:多对多关系(user ←→ role)
-
-
什么是关联查询
当访问关系的一方时,如果需要查看与之关联的另一方数据,则必须使用表链接查询,将查询到的另一方数据,保存在本方的属性中
-
关联查询的语法
指定“一方”关系时(对象),使用
< association javaType="" >
指定“多方”关系时(集合),使用
< collection ofType="" >
一对一查询
需求:查询账户信息,关联查询用户信息。
分析:因为一个账户信息只能供某个用户使用,所以从查询账户信息出发关联查询用户信息为一对一查询。
一对多查询
需求:查询所有用户信息及用户关联的账户信息。
分析:用户信息和他的账户信息为一对多关系,并且查询过程中如果用户没有账户信息,此时也要将用户信息查询出来,此时左外连接查询比较合适。
多对多查询
需求:查询角色及角色赋予的用户信息。
分析:一个用户可以拥有多个角色,一个角色也可以赋予多个用户,用户和角色为双向的一对多关系,多对多关系其实我们看成是双向的一对多关系。
AccountDao 接口
//一对一
List<Account> findAll();
//一对多
List<Account> findAll1();
AccountDao.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.by.dao.AccountDao">
<!-- 结果映射 -->
<resultMap type="account" id="findAllResultMap">
<id column="aid" property="id"/>
<result column="uid" property="uid"/>
<result column="money" property="money"/>
<!-- 指定关系表中数据的封装规则 -->
<association property="user" javaType="user">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findAll" resultMap="findAllResultMap">
select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id
</select>
<!-- 延迟加载-->
<select id="findAccountById" resultType="account">
select * from account where uid = #{id}
</select>
</mapper>
RoleDao 接口
//多对多查询
List<Role> findAll2();
RoleDao.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.by.dao.RoleDao">
<!--定义 role 表的 ResultMap-->
<resultMap id="findAll2ResultMap" type="Role">
<id property="id" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
<collection property="users" ofType="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
</collection>
</resultMap>
<!--查询所有-->
<select id="findAll2" resultMap="findAll2ResultMap">
select r.id as rid,r.role_name,r.role_desc,u.* from role r
left join user_role ur on r.id = ur.rid
left join user u on u.id = ur.uid
</select>
</mapper>
UserDao接口
//一对多查询
List<User> findAll1();
//延迟加载
public List<User> findAll3();
}
UserDao.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.by.dao.UserDao">
<resultMap type="user" id="findAll1ResultMap">
<id column="id" property="id"></id>
<result column="username" property="username"/>
<result column="address" property="address"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<!-- collection 是用于建立一对多中集合属性的对应关系
ofType 用于指定集合元素的数据类型
-->
<collection property="accounts" ofType="account">
<id column="aid" property="id"/>
<result column="uid" property="uid"/>
<result column="money" property="money"/>
</collection>
</resultMap>
<!-- 配置查询所有操作 -->
<select id="findAll1" resultMap="findAll1ResultMap">
select u.*,a.id as aid ,a.uid,a.money
from user u left join account a on u.id =a.uid
</select>
<!--延迟加载-->
<resultMap type="User" id="findAll3ResultMap">
<id column="id" property="id"></id>
<result column="username" property="username"/>
<result column="address" property="address"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<!--
property:属性名
ofType:泛型
select: 要调用的 select 映射的 id
column : 传递给 select 映射的参数
fetchType="lazy":懒加载,默认情况下是没有开启延迟加载的
-->
<collection property="accounts" ofType="Account"
select="com.by.dao.AccountDao.findAccountById"
column="id" fetchType="lazy">
</collection>
</resultMap>
<select id="findAll3" resultMap="findAll3ResultMap">
select * from user
</select>
</mapper>
MyBatisTest.java文件
package com.by.test;
import com.by.dao.AccountDao;
import com.by.dao.RoleDao;
import com.by.dao.UserDao;
import com.by.pojo.Account;
import com.by.pojo.Role;
import com.by.pojo.User;
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.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.HashMap;
import java.util.List;
import java.util.Map;
//传递单个参数
public class MyBatisTest {
private SqlSession sqlSession;
private InputStream inputStream;
//使用@Before注解,在运行类里面方法时先运行类里面的内容
@Before
public void start() throws IOException {
String resource = "mybatis-config.xml";
inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sessionFactory.openSession();
}
//一对一查询
@Test
public void testOneToOne() {
AccountDao accountDao = sqlSession.getMapper(AccountDao.class);
List<Account> accountList = accountDao.findAll();
for (Account ac : accountList) {
System.out.println(ac);
}
}
//一对多查询
@Test
public void testOneToMany() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.findAll1();
for(User user : userList){
System.out.println(user);
}
}
//多对多查询
@Test
public void testManyToMany() {
RoleDao roleDao = sqlSession.getMapper(RoleDao.class);
List<Role> roleList = roleDao.findAll2();
for(Role role : roleList){
System.out.println(role);
}
}
//延迟加载
@Test
public void testFindAll() {
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.findAll3();
for(User user : userList){
System.out.println(user.getUsername());//不查询account
System.out.println(user.getAccounts());//查询account
}
}
//使用@After注解,运行完类中的方法后自动运行
@After
public void end() throws IOException {
//关流
sqlSession.close();
inputStream.close();
}
}