1. 一对一查询(resultType、resultMap实现):
resultType: 需要自定义一个 POJO类的扩展类,保证SQL查询列与POJO中属性对应;
resultMap: 配置 .xml映射文件,一对一映射使用 association,过程复杂;延迟加载只能使用 resultMap;
users——用户表(id、username、age)
items——商品表(id、item_name、price、detail、pic、createtime)
orders——订单表(id、user_id、number、createtime、note)
orderdetail——订单信息表(id、orders_id、items_id、items_num)
① users——orders:一个用户可以创建多个订单(一对多)
orders——users:一个订单只能由一个用户创建(一对一)
② orders——orderdetail:一个订单可以包含多个订单信息(一对多)
orderdetail——orders:一个订单信息属于一个订单(一对一)
③ orderdetail——items:一个订单信息对应一个商品(一对一)
items——orderdetail:一个商品对应多个订单信息(一对多)
1.1 resultType 实现一对一查询:
需求:查询订单关联的用户信息(一对一查询用 resultType)
步骤:
1.SQL:
select orders.*,username,age from orders,users where users.id=orders.user_id
2.一对一查询映射 pojo
创建 order扩展类(OrderCustom)自定义 pojo
(因为上面 sql语句中 orders中包含信息比 users多)
3.mapper.xml
<select id="findOrderUserList" resultType="orderCustom">
select orders.*,username,age from orders,users where users.id=orders.user_id
</select>
4.mapper.java
Order.java
public class Order {
private int id;
private int user_id;
private String number;
private Date createtime; //util包中Date
private String note;
set、get();
}
OrderCustom.java
public class OrderCustom extends Order {
private String username;
private int age;
set、get();
}
OrderCustomMapper.xml
<mapper namespace="com.mdd.mapper.OrderCustomMapper">
<select id="findOrderUserList" resultType="orderCustom">
select orders.*,username,age from orders,users where users.id=orders.user_id
</select>
</mapper>
OrderCustomMapper.java
public interface OrderCustomMapper {
public List<OrderCustom> findOrderUserList() throws Exception;
}
AppTest.java
public class AppTest {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void init() throws IOException {
InputStream inputStream=Resources.getResourcesAsStream("SqlMapConfig.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindOrderUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
OrderCustomMapper orderCustomMapper=sqlSession.getMapper(OrderCustomMapper .class);
List<OrderCustom> list=orderCustomMapper.findOrderUserList();
sqlSession.close();
for (OrderCustom orderCustom:list) {
System.out.println(orderCustom.getId()+","+user.getUsername());
}
}
}
1.2 resultMap 实现一对一查询(用association):
在单表POJO中添加属性;(此处Order类中添加User属性)
需求:查询订单及相关的用户信息
1.POJO类的定义
2.mapper.xml
定义一个 resultMap,使用 association完成一对一映射
3.mapper.java
(1. Order.java中添加User类属性; 2. OrderCustomMapper.xml中用 resultMap(association):association:关联信息单个、property:要关联在order类中的属性、javaType:关联在order类中的属性的类型);3. OrderCustomMapper.java接口中添加方法;)
public interface OrderCustomMapper {
public List<OrderCustom> findOrderUserList() throws Exception;
}
Order.java
public class Order {
private int id;
private int user_id;
private String number;
private Date createtime; //util包中Date
private String note;
private User user; //关联用户信息
set、get();
}
OrderCustomMapper.xml
(2. OrderCustomMapper.xml中用 resultMap(association):association:关联信息单个、property:要关联在order类中的POJO类、javaType:关联在order类中的POJO类的类型);)
<mapper namespace="com.mdd.mapper.OrderCustomMapper">
<!--resultMap-->
<resultMap id="orderUserListMap" type="order">
//订单信息映射
<id column="id" property="id"/>//id表主键列,result表普通列
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
//相关用户信息映射
<association property="user" javaType="user">//association关联信息单个,property要关联在order类中的属性,关联在order类中的属性的类型
<id column="user_id" property="id"/> //column是查询结果中用于显示的别名user_id
<result column="username" property="username"/>
<result column="age" property="age"/>
</association>
</resultMap>
<select id="findOrderUserList" resultMap="orderUserListMap">
select orders.*,username,age from orders,users where users.id=orders.user_id
</select>
<!--resultType-->
<select id="findOrderUserList" resultType="orderCustom">
select orders.*,username,age from orders,users where users.id=orders.user_id
</select>
</mapper>
OrderCustomMapper.java
(3. 接口中添加一个 findOrderUserListMap方法)
public interface OrderCustomMapper {
public List<OrderCustom> findOrderUserList() throws Exception;
public List<Order> findOrderUserListMap() throws Exception;
}
AppTest.java
public class AppTest {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void init() throws IOException {
InputStream inputStream=Resources.getResourcesAsStream("SqlMapConfig.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
/* resultType实现一对一 */
@Test
public void testFindOrderUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
OrderCustomMapper orderCustomMapper=sqlSession.getMapper(OrderCustomMapper .class);
List<OrderCustom> list=orderCustomMapper.findOrderUserList();
sqlSession.close();
for (OrderCustom orderCustom:list) {
System.out.println(orderCustom.getId()+","+user.getUsername());
}
}
/* resultMap实现一对一 */
@Test
public void testFindOrderUserListMap() throws Exception()
{
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderCustomMapper orderCustomMapper = sqlSession.getMapper(OrderCustomMapper.class);
List<Order> list = orderCustomMapper.findOrderUserListMap();
sqlSession.close(); // 再debug运行测试
}
}
2. resultMap 实现一对多查询
需求:查询订单表中所有订单信息
SQL语句:
select orders.*,orderdetail.id orderdetail_id,items_id,items_num
from orders,orderdetail where orders.id=orderdetail.orders_id
resultMap 实现一对多映射(用collection):
关联映射为集合;
(1. 主表 Orders表对应的 POJO类中添加一个集合 OrderDetail对应的类; 2. OrderCustomMapper.xml中用 resultMap(collection):collection:关联信息单个、property:要关联在order类中的集合、ofType:关联在order类中的集合的类型);3. OrderCustomMapper.java接口中添加方法;)
Order.java
( Orders表对应的 POJO类中添加一个集合 OrderDetail对应的类)
public class Order {
private int id;
private int user_id;
private String number;
private Date createtime; //util包中Date
private String note;
private User user;
private List<OrderDetail> orderDetails; //添加orderDetail集合
set、get();
}
OrderCustomMapper.xml
<mapper namespace="com.mdd.mapper.OrderCustomMapper">
<!--resultType实现一对一映射-->
<select id="findOrderUserList" resultType="orderCustom">
select orders.*,username,age from orders,users where users.id=orders.user_id
</select>
<!--resultMap实现一对一映射-->
<resultMap id="orderUserListMap" type="order">
//订单信息映射
<id column="id" property="id"/>//id表主键列,result表普通列
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
//相关用户信息映射
<association property="user" javaType="user">//association关联信息单个,property要关联在order类中的属性,关联在order类中的属性的类型
<id column="user_id" property="id"/> //column是查询结果中用于显示的别名user_id
<result column="username" property="username"/>
<result column="age" property="age"/>
</association>
</resultMap>
<select id="findOrderUserList" resultMap="orderUserListMap">
select orders.*,username,age from orders,users where users.id=orders.user_id
</select>
<!--resultMap实现一对多映射-->
<resultMap id="orderAndOrderDetailMap" type="order">
//与订单进行映射
<id column="id" property="id"/>//id表主键列,result表普通列
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
//与订单详情进行映射
<collection property="orderDetails" ofType="orderDetail">
<id column="orderdetail_id" property="id"/> //column是查询结果中用于显示的别名user_id
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
</resultMap>
<select id="findOrderAndOrderDetail" resultMap="orderAndOrderDetailMap">
select orders.*,orderdetail.id orderdetail_id,items_id,items_num
from orders,orderdetail where orders.id=orderdetail.orders_id
</select>
</mapper>
OrderCustomMapper.java
public interface OrderCustomMapper {
public List<OrderCustom> findOrderUserList() throws Exception;
public List<Order> findOrderUserListMap() throws Exception;
public List<Order> findOrderAndOrderDetailMap() throws Exception;
}
3. resultMap 实现多对多查询:
需求:查询谁买了什么商品
1.SQL:
select username,age,item_name
from users,items,orders,orderdetail
where users.id=orders.user_id and orders.id=orderdetail.orders_id and items.id=orderdetail.items_id
2.mapper.xml
resultType:一开始查询时显示明细
resultMap:当鼠标一上去才显示明细
【User 类中添加 Order集合属性;
Order 类中添加 OrderDetail集合属性;
OrderDetail 类中添加 Item属性;】
User.java
public class User {
private int id;
private String username;
private int age;
private List<Order> orders;//User类中添加orders集合
set、get();
Order.java
public class Order {
private int id;
private int user_id;
private String number;
private Date createtime;
private String note;
private List<OrderDetail> orderDetails;//Order类中添加orderDetails集合
set、get();
}
OrderDetail.java
public class OrderDetail {
private int id;
private int ordersId;
private String itemsId;
private Date itemsNum;
private Item item;//OrderDetail类中添加item属性
set、get();
}
UserMapper.xml
<mapper namespace="com.mdd.mapper.UserMapper">
<!-- 法一:用resultType实现-->
<select id="findUserAndItem" resultType="userCustom">
select username,age,item_name
from users,items,orders,orderdetail
where users.id=orders.user_id and orders.id=orderdetail.orders_id and items.id=orderdetail.items_id
</select>
<!-- 法二:用resultMap实现 -->
<resultMap id="userCustomMap" type="user">
//用户信息映射
<result column="username" property="username"/>
<result column="age" property="age"/>
//订单
<collection property="orders" ofType="order">//collection用ofType
<collection property="orderDetails" ofType="orderDetail">
<association property="item" javaType="item">//association用javaType
<result column="item_name" property="name"/>
</association>
</collection>
</collection>
</resultMap>
<select id="findUserAndItemMap" resultMap="userCustomMap">
select username,age,item_name
from users,items,orders,orderdetail
where users.id=orders.user_id and orders.id=orderdetail.orders_id and items.id=orderdetail.items_id
</select>
</mapper>
UserMapper.java
public interface OrderCustomMapper {
public List<UserCustom> findUserAndItem() throws Exception;
public List<User> findUserAndItemMap() throws Exception;
}
AppTest.java
public class AppTest {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void init() throws IOException {
InputStream inputStream=Resources.getResourcesAsStream("SqlMapConfig.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindOrderUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
List<UserCustom> list=userMapper.findUserAndItem();//debug调试运行
sqlSession.close();
}
@Test
public void testFindOrderUserList() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
List<User> list=userMapper.findUserAndItemMap();//debug调试运行
sqlSession.close();
}
}
4. 延迟加载:
【1. 全局文件中加 settings配置;
2. mapper.xml映射文件中:一对一延迟加载:resultMap中(用 association(column、property、select)) ;
3. mapper.xml映射文件中:一对多延迟加载:resultMap中(用 collection(column、property、select) ;】
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<typeAliase>
<package name="com.iotek.po"/>
</typeAliase>
<properties resource="db.prperties"></properties>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.iotek.mapper"/>
</mappers>
</configuration>
)
1.延迟意义
当进行数据查询过程中,尽量使用单表查询,单表查询速度快,为了提高查询效率;
如果查询单表可满足需求,一开始使用单表,当需要关联,再关联查询,叫延迟加载。
延迟加载的使用场合(resultType、resultMap):
1.主要用于 service层
2.resultType
作用:查询结果与POJO类属性相映射
场合:每一条记录映射到POJO中
3.resultMap
使用 association完成一对一映射
使用 collection完成一对多映射
场合:方便获取关联信息
2.使用
1.配置
lazyLoadingEnabled:为 true时表示开启延迟加载。 全局启用或禁用延迟加载,当禁用时(false),所以关联对象都会及时加载,不会延迟加载;
aggressiveLazyLoading:为 false时表示按需加载。 当启用时,有延迟加载属性的对象在被调用时将会完全加载任意属性(aggressive表示“积极的”);当关闭时,每种属性将会按需要加载(false);
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
2.延迟加载的实现:
需求:查询订单及用户
思路:
① 查询订单
② 查询用户 Order类中调用getUser()方法时查询,即延迟加载
步骤:
mapper.xml
mapper.java
3.一对一延迟加载(用 association(column、property、select))
<association **column**="user_id" **property**="user" **select**="com.mdd.mapper.UserMapper.findUserById">
eg:此例句延迟加载的column、property、select,column是 UserMapper.xml中要传入的参数
OrderCustomMapper.xml
<mapper namespace="com.mdd.mapper.OrderCustomMapper">
<resultMap id="OrderCustomMap" type="order">
<id column="id" property="id"/>
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>//select:延迟加载时执行的SQL的statement的id
<association column="user_id" property="user" select="com.mdd.mapper.UserMapper.findUserById"> //延迟加载的column、property、select
<id column="user_id" property="id"/>//此column是UserMapper.xml中要传入的参数
<result column="username" property="username"/>
<result column="age" property="age"/>
</association>
</resultMap>
<select id="findOrderUserList" resultMap="OrderCustomMap">
select * from orders
</select>
</mapper>
UserMapper.xml
<mapper namespace="com.mdd.mapper.UserMapper">
<select id="findUserById" parameterType="int" resultType="user">
select * from users where id=#{id}
</select>
</mapper>
OrderCustomMapper.java
public interface OrderCustomMapper {
public List<Order> findOrderUserList() throws Exception;
}
AppTest.java
public class AppTest {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void init() throws IOException {
InputStream inputStream=Resources.getResourcesAsStream("SqlMapConfig.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test() throws Exception(){
SqlSession sqlSession=sqlSessionFactory.openSession();
OrderCustomMapper orderCustomMapper=sqlSession.getMapper(OrderCustomMapper.class);
List<Order> list=orderCustomMapper.findOrderUserList();//debug调试运行
User user=list.get(0).getUser(); //延迟加载测试
sqlSession.close();
}
}
4.一对多延迟加载(用 collection(column、property、select) ) eg:
<collection column="user_id" property="user" select="com.mdd.mapper.UserMapper.findUserById">