1.为什么使用Mybatis的关联映射?
实际开发中,对数据库的操作往往会涉及多张表,这在面向对象中就涉及了对象与对象之间的关联关系。针对多张表之间的操作,Mybatis提供了关联映射,可以很好处理对象与对象之间的关联关系。
2.在实际关系型数据库中,多张表之间存在三种关系:
一对一:在任意一方引入对方的主键作为外键
一对多:在“多”的一方,添加“一”的一方主键作为外键
多对多:产生中间关系表,引入两张表的主键作为外键,两个主键联合成为主键或使用新的字段作为主键
3.在JAVA对象中描述如下:
一对一:
class A{
B b;
}
class B{
A a;
}
一对多:
class A{
List b;
}
class B{
A a;
}
多对多:
class A{
List b;
}
class B{
List a;
}
4.MyBatis加载关联关系主要有两种方式:
嵌套查询:通过执行另外一条SQL映射语句来返回预期的复杂类型;
a.是在查询SQL中嵌入一个子查询
b.会执行多条SQL
c.SQL语句比较简单
嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集;
a.只执行一条复杂的SQL语句
b.SQL语句比较复杂,是关联查询
注意:
嵌套查询虽然简单,但是需要执行多条SQL,在数据库表多的时候,极大消耗数据库的性能并降低查询效率,不推荐使用。
5.一对一:通过resultMap元素的association实现;
IdCardMapper.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.wds.mapper.IdCardMapper">
<!--根据id查询证件信息-->
<select id="findCodeById" parameterType="Integer" resultType="IdCard">
select * from tb_idcard where id=#{id}
</select>
</mapper>
PersonMapper.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.wds.mapper.PersonMapper">
<!--通过另外一条sql映射语句返回预期的结果类型-->
<select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
select * from tb_person where id=#{id}
</select>
<resultMap id="IdCardWithPersonResult" type="Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<!--一对一,association使用select属性引入另外一条SQL语句-->
<association property="card" column="card_id" javaType="IdCard"
select="com.wds.mapper.IdCardMapper.findCodeById"/>
</resultMap>
<!--嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集-->
<select id="findPersonById2" parameterType="Integer" resultMap="IdCardWithPersonResult2">
select p.*,idcard.code from tb_person p,tb_idcard idcard where p.card_id=idcard.id and p.id=#{id}
</select>
<resultMap id="IdCardWithPersonResult2" type="Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<association property="card" javaType="IdCard" >
<id property="id" column="id"/>
<result property="code" column="code"/>
</association>
</resultMap>
</mapper>
MybatisAssociationTest.java
package com.wds.test;
import com.wds.po.Person;
import com.wds.utils.Myutils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
/**
* Mbatis关联映射测试类
*/
public class MybatisAssociationTest {
/**
* 嵌套查询
*/
@Test
public void findPersonByIdTest(){
//1.通过工具类生成SqlSession对象
SqlSession sqlSession = Myutils.getSession();
//2.通过MyBatis关联映射查询
Person person = sqlSession.selectOne("com.wds.mapper.PersonMapper.findPersonById", 1);
//3.输出结果
System.out.println(person);
//4.关闭sqlSession
sqlSession.close();
}
/**
* 嵌套结果
*/
@Test
public void findPersonByIdTest2(){
//1.工具类生成SqlSession
SqlSession sqlSession = Myutils.getSession();
//2.使用Mybatis的嵌套结果的方法查询id为1的用户的信息
Person person = sqlSession.selectOne("com.wds.mapper.PersonMapper.findPersonById2", 1);
//3.输出结果
System.out.println(person);
//4.关闭SqlSession
sqlSession.close();
}
}
结果:
6.一对多:通过resultMap的collection元素实现;
UserMapper.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.wds.mapper.UserMapper">
<!--一对多:查看某一用户及其关联的订单信息-->
<select id="findUserWithOrders" parameterType="Integer" resultMap="UserWithOrdersResult">
select u.*,o.id as orders_id ,o.number
from tb_user u,tb_orders o
where u.id=o.user_id
and u.id=#{id}
</select>
<resultMap id="UserWithOrdersResult" type="User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<!--一对多关联映射:collection
ofType表示属性集合中的类型,List<Orders>属性即Orders类-->
<collection property="ordersList" ofType="Orders">
<id property="id" column="orders_id"/>
<result property="number" column="number"/>
</collection>
</resultMap>
</mapper>
7.多对多:通过resultMap的collection元素实现;
OrdersMapper.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.wds.mapper.OrdersMapper">
<!--对对多:通过执行另外一条SQL映射语句返回预期的特殊类型-->
<select id="findOrdersWithProduct" parameterType="Integer"
resultMap="OrdersWithProductResult">
select * from tb_orders where id=#{id}
</select>
<resultMap id="OrdersWithProductResult" type="Orders">
<id property="id" column="id"/>
<result property="number" column="number"/>
<collection property="productList" column="id" ofType="Product"
select="com.wds.mapper.ProductMapper.findProductById"/>
</resultMap>
</mapper>
ProductMapper.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.wds.mapper.ProductMapper">
<select id="findProductById" parameterType="Integer" resultType="Product">
select * from tb_product where id in
(select product_id from tb_ordersitem where orders_id = #{id})
</select>
</mapper>
MybatisAssociationTest.java
package com.wds.test;
import com.wds.po.Orders;
import com.wds.po.Person;
import com.wds.po.User;
import com.wds.utils.Myutils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import static com.wds.utils.Myutils.getSession;
/**
* Mbatis关联映射测试类
*/
public class MybatisAssociationTest {
/**
* 嵌套查询
*/
@Test
public void findPersonByIdTest(){
//1.通过工具类生成SqlSession对象
SqlSession sqlSession = getSession();
//2.通过MyBatis关联映射查询
Person person = sqlSession.selectOne("com.wds.mapper.PersonMapper.findPersonById", 1);
//3.输出结果
System.out.println(person);
//4.关闭sqlSession
sqlSession.close();
}
/**
* 嵌套结果
*/
@Test
public void findPersonByIdTest2(){
//1.工具类生成SqlSession
SqlSession sqlSession = getSession();
//2.使用Mybatis的嵌套结果的方法查询id为1的用户的信息
Person person = sqlSession.selectOne("com.wds.mapper.PersonMapper.findPersonById2", 1);
//3.输出结果
System.out.println(person);
//4.关闭SqlSession
sqlSession.close();
}
/**
* 一对多
*/
@Test
public void findUserWithOrdersTest(){
//1.生成SqlSession
SqlSession sqlSession = getSession();
//2.查询id为1的用户信息
User user = sqlSession.selectOne("com.wds.mapper.UserMapper.findUserWithOrders", 1);
//3.输出结果
System.out.println(user);
//4.关闭sqlSession
sqlSession.close();
}
/**
* 多对多
*/
@Test
public void findOrdersTest(){
SqlSession sqlSession = getSession();
Orders orders = sqlSession.selectOne("com.wds.mapper.OrdersMapper.findOrdersWithProduct", 1);
System.out.println(orders);
sqlSession.close();
}
}