一对一关联查询
resultType
resultType的对于关联查询是实时查询的。
使用resultType来开发的话,就要将sql查询出来的结果映射到某个pojo中。
resultType的开发比较简单,就是查询结果是什么,就去定义一个pojo类,添加对应的属性名进行映射就OK。
resultMap
resultMap对于管理查询,可以设置一些延迟加载的配置。
resultMap里配置一对一关联,主要使用到association这个标签,指定类型用javaType。
订单pojo类:
public class OrderInfos {
private String id;
private String orderNo; //订单号
private String mark; //留言(描述)
private String address; //地址(具体地址)
private String phone; //收货人电话
private Integer quantity; //数量
private Double totalSum; //订单总金额
private Date createTime; //订单生成时间
private User user;
//....get set
用户信息pojo类:
public class User {
private String id;
private String userName; //用户名
private String nickName; //昵称
private String password; //登录密码
private Integer sex; //性别
private Date birthday; //出生日期
private String province; //省
private String city; //市
private String county; //县
private Date createTime; //创建时间
//... get set
配置文件内容:
<resultMap type="orderInfos" id="findOrderMap">
<!-- 映射订单信息
id:指定查询列中的唯一标识,如果有组合主键,则配置对个id
-->
<id column="id" property="id"/>
<result column="orderNo" property="orderNo"/>
<result column="mark" property="mark"/>
<result column="address" property="address"/>
<result column="createTime" property="createTime"/>
<!-- 订单所对应的用户信息 -->
<!-- association:用于映射关联查询单个对象的信息
property:要讲关联查询的用户信息映射到订单pojo列中的哪个属性里
javaType:表示关联查询的对象类型(可以使用别名)
-->
<association property="user" javaType="pojo.User">
<id column="user_id" property="id"/>
<result column="userName" property="userName"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="password" property="password"/>
</association>
</resultMap>
<!-- 获取订单里的用户信息 -->
<select id="findOrder" resultMap="findOrderMap">
select A.*,B.userName,B.sex,B.birthday,B.`password`
from tab_orderinfos as A
inner join tab_user as B on A.user_id=B.id
</select>
一对多关联查询
配置一对多关联,需要在resultMap里使用collection标签来实现,指定类型用ofType
订单pojo类:
public class OrderInfos {
private String id;
private String orderNo; //订单号
private String mark; //留言(描述)
private String address; //地址(具体地址)
private String phone; //收货人电话
private Integer quantity; //数量
private Double totalSum; //订单总金额
private Date createTime; //订单生成时间
private List<OrderInfosDetail> detailList;
//... get set
订单明细pojo类:
public class OrderInfosDetail {
private String id;
private String color; //颜色
private String size; //尺寸
private Double unitPrice; //价格
private Integer quantity; //数量
private Double totalSum; //总金额
//... get set
配置文件内容:
<resultMap type="orderInfos" id="oneToManyFindOrderMap">
<!-- 映射订单信息
id:指定查询列中的唯一标识,如果有组合主键,则配置对个id
-->
<id column="id" property="id"/>
<result column="orderNo" property="orderNo"/>
<result column="mark" property="mark"/>
<result column="address" property="address"/>
<result column="createTime" property="createTime"/>
<!-- 订单所对应的用户信息 -->
<!-- association:用于映射关联查询单个对象的信息
property:要讲关联查询的用户信息映射到订单pojo列中的哪个属性里
javaType:表示关联查询的对象类型(可以使用别名)
-->
<association property="user" javaType="pojo.User">
<id column="user_id" property="id"/>
<result column="userName" property="userName"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="password" property="password"/>
</association>
<!-- 获取订单明细信息,一个订单关联查询出多条明细,要使用collection来进行映射 -->
<!-- collection:对关联查询到的多条记录进行映射
property:要讲关联查询的明细信息映射到订单pojo列中的哪个属性里
ofType:指要映射到集合属性中pojo的类型
-->
<collection property="detailList" ofType="orderInfosDetail">
<id column="detailId" property="id"/>
<result column="quantity" property="quantity"/>
<result column="color" property="color"/>
<result column="unitPrice" property="unitPrice"/>
</collection>
</resultMap>
<select id="oneToManyFindOrder" resultMap="oneToManyFindOrderMap">
select A.*,B.userName,B.sex,B.birthday,B.`password`,C.id detailId,C.quantity,C.color,C.unitPrice
from tab_orderinfos as A
inner join tab_user as B on A.user_id=B.id
left join tab_orderinfosdetail as C on A.id=C.orderInfos_id
</select>
这里我们发现订单信息与用户信息的配置,跟上面的一对一里的配置一样。这种情况可以使用继承,就不需要再进行配置了。
使用extends属性设置了继承以后,配置文件内容变为:
<resultMap type="orderInfos" id="oneToManyFindOrderMap" extends="findOrderMap">
<!-- 获取订单明细信息,一个订单关联查询出多条明细,要使用collection来进行映射 -->
<!-- collection:对关联查询到的多条记录进行映射
property:要讲关联查询的明细信息映射到订单pojo列中的哪个属性里
ofType:指要映射到集合属性中pojo的类型
-->
<collection property="detailList" ofType="orderInfosDetail">
<id column="detailId" property="id"/>
<result column="quantity" property="quantity"/>
<result column="color" property="color"/>
<result column="unitPrice" property="unitPrice"/>
</collection>
</resultMap>
<select id="oneToManyFindOrder" resultMap="oneToManyFindOrderMap">
select A.*,B.userName,B.sex,B.birthday,B.`password`,C.id detailId,C.quantity,C.color,C.unitPrice
from tab_orderinfos as A
inner join tab_user as B on A.user_id=B.id
left join tab_orderinfosdetail as C on A.id=C.orderInfos_id
</select>
多对多关联查询
多多对多的关联查询其实都是基于多对一,一对多的基础上进行配置的。
会了一对一、一对多,多对多也就会了。这里不再多说。
延迟加载
什么是延迟加载?就是需要用到的时候再进行查询,这就是延迟加载。
mybatis里association和collection都支持延迟加载。
这里就拿association来测试一下,collection用法一样。
需要说明的是,延迟加载跟上面所说的一对一、一对多的查询开发方式是不一样的。上面的查询都是实时的查询出所有的数据,不是延迟加载。
针对上面所说的一对一,我们来实现一下它的延迟加载。
此时需要配置两个sql配置:
(1)查询订单信息
(2)根据订单信息中的user_id,去查询对应的用户信息
最后的配置文件内容如下:
<resultMap type="orderInfos" id="lazyMap">
<!-- 映射订单信息 -->
<id column="id" property="id"/>
<result column="orderNo" property="orderNo"/>
<result column="mark" property="mark"/>
<result column="address" property="address"/>
<result column="createTime" property="createTime"/>
<!-- 延迟加载订单所对应的用户信息
select:指定延迟加载需要执行的sql配置id
column:订单信息中关联查询用户信息的列(说白了,就是说订单表中是根据那个列去查询用户信息的)
-->
<association property="user" javaType="pojo.User" select="lazyFindUserById" column="user_id"></association>
</resultMap>
<!-- 查询订单信息,用户信息需要延迟加载,因为延迟加载是在association或collection中才能实现,所以这里需要使用resultMap来做映射 -->
<select id="findOrderAll" resultMap="lazyMap">
select * from tab_orderinfos
</select>
<!-- 根据id查询用户信息 -->
<select id="lazyFindUserById" parameterType="java.lang.String" resultType="user">
select * from tab_user where id=#{id}
</select>
注意:mybatis默认是不开启延迟加载的。所以我们需要在sqlMapConfig.xml的settings里配置开启延迟加载。具体的settings的信息请看文档《3.1.2 settings》
在sqlMapConfig.xml配置settings开启延迟加载:
<settings>
<!-- 开启延迟加载
lazyLoadingEnabled:全局配置懒加载,如果设置为false,则所有关联查询都会被初始化加载。默认值是fasle。所以这里要设为true
aggressiveLazyLoading:当设置为true时,会对全部查询发出sql了。所以我们应该要设置为false。为false时,需要用什么就查询什么,按需加载。默认值是true
-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
测试类:
@Test
public void test() throws Exception {
//获取代理对象
OrderMapper orderMapper = (OrderMapper) context.getBean("orderMapper");
List<OrderInfos> list = orderMapper.findOrderAll();
for(OrderInfos order :list){
User user = order.getUser();
System.out.println(user.getUserName());
}
System.out.println(list);
}
}
结果:
2016-04-04 16:16:04,370 [main] DEBUG [mapper.OrderMapper.findOrderAll] - ==> Preparing: select * from tab_orderinfos
2016-04-04 16:16:04,418 [main] DEBUG [mapper.OrderMapper.findOrderAll] - ==> Parameters:
2016-04-04 16:16:04,980 [main] DEBUG [mapper.OrderMapper.findOrderAll] - <== Total: 22
2016-04-04 16:16:04,987 [main] DEBUG [org.mybatis.spring.SqlSessionUtils] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4fb686]
2016-04-04 16:16:04,987 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
2016-04-04 16:16:18,278 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:18,279 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@104f568 [wrapping: com.mysql.jdbc.JDBC4Connection@1bd7522]] will not be managed by Spring
2016-04-04 16:16:18,281 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:18,284 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:18,302 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:18,303 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
2016-04-04 16:16:30,678 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:30,679 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@43690f [wrapping: com.mysql.jdbc.JDBC4Connection@1bd7522]] will not be managed by Spring
2016-04-04 16:16:30,681 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:30,684 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:30,702 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:30,703 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
2016-04-04 16:16:41,684 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:41,685 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@160e1bb [wrapping: com.mysql.jdbc.JDBC4Connection@1bd7522]] will not be managed by Spring
2016-04-04 16:16:41,685 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:41,685 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:41,689 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:41,690 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
2016-04-04 16:16:41,690 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:41,690 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@163eb77 [wrapping: com.mysql.jdbc.JDBC4Connection@1bd7522]] will not be managed by Spring
2016-04-04 16:16:41,690 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:41,691 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:41,695 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:41,696 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
2016-04-04 16:16:41,696 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:41,696 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@edd5f4 [wrapping: com.mysql.jdbc.JDBC4Connection@1bd7522]] will not be managed by Spring
2016-04-04 16:16:41,697 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:41,698 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:41,702 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:41,702 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
2016-04-04 16:16:41,702 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:41,702 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@b759cd [wrapping: com.mysql.jdbc.JDBC4Connection@1bd7522]] will not be managed by Spring
2016-04-04 16:16:41,702 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:41,703 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:41,705 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:41,705 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
2016-04-04 16:16:41,705 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:41,705 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@1497c25 [wrapping: com.mysql.jdbc.JDBC4Connection@175982a]] will not be managed by Spring
2016-04-04 16:16:41,705 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:41,706 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:41,727 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:41,727 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
2016-04-04 16:16:41,728 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource
2016-04-04 16:16:41,728 [main] DEBUG [org.mybatis.spring.transaction.SpringManagedTransaction] - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@1825bfb [wrapping: com.mysql.jdbc.JDBC4Connection@175982a]] will not be managed by Spring
2016-04-04 16:16:41,728 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Preparing: select * from tab_user where id=?
2016-04-04 16:16:41,728 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - ==> Parameters: 2c9381ed460abc1101460abddf830000(String)
2016-04-04 16:16:41,731 [main] DEBUG [mapper.OrderMapper.lazyFindUserById] - <== Total: 1
2016-04-04 16:16:41,731 [main] DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
abc
通过上面的日志打印,可以很清楚的看到,在for循环遍历订单信息时,当我们使用getUser()时,才发出sql,查询数据获取结果。这样就实现了延迟加载。