【一对一查询】
需求:查询订单信息,关联查询创建订单的用户信息
方式一:resultType
1.创建pojo类(包括除了主表实体类Orders.java中包含的字段外,其他需要查询的字段):
package cn.itcast.mybatis.po;
//通过此类映射订单和用户查询的结果,让此类继承包括 字段较多的pojo类
public class OrdersCustom extends Orders {
// 添加用户属性
/*
* USER.username, USER.sex, USER.address
*/
private String username;
private String sex;
private String address;
//get和set方法省略
}
2.配置mapper.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="cn.itcast.mybatis.mapper.OrdersMapperCustom">
<!-- 查询订单关联查询用户信息 -->
<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
select
orders.*,user.username,user.sex,user.address from orders,user where
orders.user_id= user.id
</select>
</mapper>
3.创建对应的mapper.java接口
// 查询订单关联查询用户信息
publicList<OrdersCustom> findOrdersUser() throws Exception;
4.测试方法
package cn.itcast.mybatis.mapper;
import java.io.InputStream;
import java.util.List;
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.Before;
import org.junit.Test;
import cn.itcast.mybatis.po.OrdersCustom;
public class OrdersMapperCustomTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
// 创建sqlSessionFactory
// mybatis配置文件
String resource = "SqlMapConfig.xml";
// 得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建会话工厂,传入mybatis的配置文件信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindOrdersUser() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession
.getMapper(OrdersMapperCustom.class);
List<OrdersCustom> list = ordersMapperCustom.findOrdersUser();
System.out.println(list);
sqlSession.close();
}
}
方式二:resultMap
使用ResultMap将查询结果在红的用户信息映射到Orders对象中,在orders类中添加User属性,将关联查询出来的用户信息映射到orders对象中的user属性中。
1.在Orders类中添加user属性
package cn.itcast.mybatis.po;
import java.util.Date;
import java.util.List;
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//用户信息
private User user;
//get和set方法省略
}
2.配置mapper.xml文件(ResultMap定义和Statement定义)
<?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="cn.itcast.mybatis.mapper.OrdersMapperCustom">
<!-- 定义ResultMap start -->
<!-- 订单查询关联用户的ResultMap -->
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
<id column="id" property="id" />
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
<association property="user" javaType="cn.itcast.mybatis.po.User">
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association>
</resultMap>
<!-- 定义ResultMap end -->
<!-- 查询订单关联查询用户信息 -->
<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
select
orders.*,user.username,user.sex,user.address from orders,user where
orders.user_id= user.id
</select>
<!-- 查询订单关联查询用户信息,使用resultmap -->
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
select
orders.*,user.username,user.sex,user.address from orders,user where
orders.user_id= user.id
</select>
</mapper>
3.创建对应的mapper.java接口
publicList<OrdersCustom> findOrdersUserResultMap() throws Exception;
4.测试方法
package cn.itcast.mybatis.mapper;
import java.io.InputStream;
import java.util.List;
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.Before;
import org.junit.Test;
import cn.itcast.mybatis.po.OrdersCustom;
public class OrdersMapperCustomTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
// 创建sqlSessionFactory
// mybatis配置文件
String resource = "SqlMapConfig.xml";
// 得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建会话工厂,传入mybatis的配置文件信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindOrdersUser() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession
.getMapper(OrdersMapperCustom.class);
List<OrdersCustom> list = ordersMapperCustom.findOrdersUser();
System.out.println(list);
sqlSession.close();
}
@Test
public void findOrdersUserResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession
.getMapper(OrdersMapperCustom.class);
List<OrdersCustom> list = ordersMapperCustom.findOrdersUserResultMap();
System.out.println(list);
sqlSession.close();
}
}
比较小结:
当一对一查询时,使用ResultMap和resultType都可以实现。但是resultType较为简单,如果pojo中更没有包括查询出来的列名,需要增加列名对应的属性即可完成映射。而ResultMap需要单独定义ResultMap,相较来说有点麻烦。
【一对多查询】
需求:查询订单及订单明细的信息。
1.在orders中添加List<Orderdetail>属性
package cn.itcast.mybatis.po;
import java.util.Date;
import java.util.List;
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//用户信息
private User user;
//订单明细
private List<Orderdetail> orderdetails;
//get和set方法略
}
2.配置mapper.xml(ResultMap定义和Statement定义)
<?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="cn.itcast.mybatis.mapper.OrdersMapperCustom">
<!-- 定义ResultMap start -->
<!-- 1.订单查询关联用户的ResultMap -->
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
<id column="id" property="id" />
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
<association property="user" javaType="cn.itcast.mybatis.po.User">
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association>
</resultMap>
<!-- 2.订单及订单明细的ResultMap -->
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap"
extends="OrdersUserResultMap">
<!-- 订单信息 -->
<!-- 用户信息 -->
<!-- 使用extends继承,不用在中配置订单信息和用户信息的映射 -->
<!-- 订单明细信息 一个订单关联查询出了多条明细,要使用collection进行映射 collection:对关联查询到多条记录映射到集合对象中
property:将关联查询到多条记录映射到cn.itcast.mybatis.po.Orders哪个属性 ofType:指定映射到list集合属性中pojo的类型 -->
<collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
<!-- id:订单明细唯 一标识 property:要将订单明细的唯 一标识 映射到cn.itcast.mybatis.po.Orderdetail的哪个属性 -->
<id column="orderdetail_id" property="id" />
<result column="items_id" property="itemsId" />
<result column="items_num" property="itemsNum" />
<result column="orders_id" property="ordersId" />
</collection>
</resultMap>
<!-- 定义ResultMap end -->
<!-- Statement定义 begin -->
<!-- 查询订单关联查询用户信息 -->
<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
select
orders.*,user.username,user.sex,user.address from orders,user where
orders.user_id= user.id
</select>
<!-- 1.查询订单关联查询用户信息,使用resultmap -->
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
select
orders.*,user.username,user.sex,user.address from orders,user where
orders.user_id= user.id
</select>
<!-- 2.查询订单关联查询用户及订单明细,使用resultmap -->
<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
select
orders.*,
user.username,
user.sex,
user.address,
orderdetail.id
orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id
from
orders,
user,
orderdetail
where orders.user_id =
user.id and orderdetail.orders_id=orders.id
</select>
<!-- Statement定义 end -->
</mapper>
3.mapper.java
package cn.itcast.mybatis.mapper;
import java.util.List;
import cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;
public interface OrdersMapperCustom {
// 查询订单关联查询用户信息
public List<OrdersCustom> findOrdersUser() throws Exception;
public List<OrdersCustom> findOrdersUserResultMap() throws Exception;
// 查询订单(关联用户)及订单明细
public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;
}
4.测试方法
package cn.itcast.mybatis.mapper;
import java.io.InputStream;
import java.util.List;
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.Before;
import org.junit.Test;
import cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;
public class OrdersMapperCustomTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
// 创建sqlSessionFactory
// mybatis配置文件
String resource = "SqlMapConfig.xml";
// 得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建会话工厂,传入mybatis的配置文件信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findOrdersAndOrderDetailResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession
.getMapper(OrdersMapperCustom.class);
List<Orders> list = ordersMapperCustom
.findOrdersAndOrderDetailResultMap();
System.out.println(list);
sqlSession.close();
}
}
比较小结:
与上面一对一查询不同,一条订单记录对应一个用户,但是订单和订单明细这两张表的关系是,一个订单中可能有多个商品,这样在orderDetail表中就可能出现订单ID相同的记录,这明显是一对多的关系。如果用resultType实现,就会出现重复数据,例如订单表中有两条订单,并且每个订单中都买了两个商品,用resultType实现,最后返回的数据就是4条,我们还需要自己处理数据,大大的不明智。所以使用上例中所示的ResultMap。
【多对多查询】
需求:查询用户及用户购买商品信息。
1.映射思路
将用户信息表映射到user类中。在user类中添加订单列表属性List<Orders>,将用户创建的订单映射到ordersList。在Orders中添加订单明细列表属性List<OrderDetail>,将订单的明细映射到orderdetails(这一步在一对多查询中已经实现)。在OrderDetail中添加items属性,将订单明细所对应的商品银蛇到items。
package cn.itcast.mybatis.po;
public class Orderdetail {
private Integer id;
private Integer ordersId;
private Integer itemsId;
private Integer itemsNum;
// 明细对应的商品信息
private Items items;
// get和set方法略
}
package cn.itcast.mybatis.po;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class User implements Serializable {
//属性名和数据库表的字段对应
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
//用户创建的订单列表
private List<Orders> ordersList;
//get和set方法略
}
2.配置mapper.xml(ResultMap定义和Statement定义)
<?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="cn.itcast.mybatis.mapper.OrdersMapperCustom">
<!-- 定义ResultMap *****************************************************start -->
<!-- 1.订单查询关联用户的ResultMap -->
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">
<id column="id" property="id" />
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
<association property="user" javaType="cn.itcast.mybatis.po.User">
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="sex" property="sex" />
<result column="address" property="address" />
</association>
</resultMap>
<!-- 2.订单及订单明细的ResultMap -->
<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap"
extends="OrdersUserResultMap">
<!-- 订单信息 -->
<!-- 用户信息 -->
<!-- 使用extends继承,不用在中配置订单信息和用户信息的映射 -->
<!-- 订单明细信息 一个订单关联查询出了多条明细,要使用collection进行映射 collection:对关联查询到多条记录映射到集合对象中
property:将关联查询到多条记录映射到cn.itcast.mybatis.po.Orders哪个属性 ofType:指定映射到list集合属性中pojo的类型 -->
<collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
<!-- id:订单明细唯 一标识 property:要将订单明细的唯 一标识 映射到cn.itcast.mybatis.po.Orderdetail的哪个属性 -->
<id column="orderdetail_id" property="id" />
<result column="items_id" property="itemsId" />
<result column="items_num" property="itemsNum" />
<result column="orders_id" property="ordersId" />
</collection>
</resultMap>
<!-- 3.查询用户及购买的商品 -->
<resultMap type="cn.itcast.mybatis.po.User" id="UserAndItemsResultMap">
<!-- 用户信息 -->
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="sex" property="sex" />
<result column="address" property="address" />
<!-- 订单信息 一个用户对应多个订单,使用collection映射 -->
<collection property="ordersList" ofType="cn.itcast.mybatis.po.Orders">
<id column="id" property="id" />
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
<!-- 订单明细 一个订单包括 多个明细 -->
<collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
<id column="orderdetail_id" property="id" />
<result column="items_id" property="itemsId" />
<result column="items_num" property="itemsNum" />
<result column="orders_id" property="ordersId" />
<!-- 商品信息 一个订单明细对应一个商品 -->
<association property="items" javaType="cn.itcast.mybatis.po.Items">
<id column="items_id" property="id" />
<result column="items_name" property="name" />
<result column="items_detail" property="detail" />
<result column="items_price" property="price" />
</association>
</collection>
</collection>
</resultMap>
<!-- 定义ResultMap *****************************************************end -->
<!-- Statement定义 *****************************************************begin -->
<!-- 查询订单关联查询用户信息 -->
<select id="findOrdersUser" resultType="cn.itcast.mybatis.po.OrdersCustom">
select
orders.*,user.username,user.sex,user.address from orders,user where
orders.user_id= user.id
</select>
<!-- 1.查询订单关联查询用户信息,使用resultmap -->
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
select
orders.*,user.username,user.sex,user.address from orders,user where
orders.user_id= user.id
</select>
<!-- 2.查询订单关联查询用户及订单明细,使用resultmap -->
<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
select
orders.*,
user.username,
user.sex,
user.address,
orderdetail.id
orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id
from
orders,
user,
orderdetail
where orders.user_id =
user.id and orderdetail.orders_id=orders.id
</select>
<!-- 3.查询用户及购买的商品信息,使用resultmap -->
<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
select
orders.*,
user.username,
user.sex,
user.address,
orderdetail.id
orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
orderdetail.orders_id,
items.name items_name,
items.detail items_detail,
items.price items_price
from
orders,
user,
orderdetail,
items
where
orders.user_id = user.id and orderdetail.orders_id=orders.id and
orderdetail.items_id = items.id
</select>
<!-- Statement定义 *****************************************************end -->
</mapper>
3.mapper.java
package cn.itcast.mybatis.mapper;
import java.util.List;
import cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;
import cn.itcast.mybatis.po.User;
public interface OrdersMapperCustom {
// 查询订单关联查询用户信息
public List<OrdersCustom> findOrdersUser() throws Exception;
public List<OrdersCustom> findOrdersUserResultMap() throws Exception;
// 查询订单(关联用户)及订单明细
public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;
// 查询用户购买商品信息
public List<User> findUserAndItemsResultMap() throws Exception;
}
4.测试方法
package cn.itcast.mybatis.mapper;
import java.io.InputStream;
import java.util.List;
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.Before;
import org.junit.Test;
import cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;
import cn.itcast.mybatis.po.User;
public class OrdersMapperCustomTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
// 创建sqlSessionFactory
// mybatis配置文件
String resource = "SqlMapConfig.xml";
// 得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建会话工厂,传入mybatis的配置文件信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findUserAndItemsResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession
.getMapper(OrdersMapperCustom.class);
List<User> list = ordersMapperCustom.findUserAndItemsResultMap();
System.out.println(list);
sqlSession.close();
}
}
比较小结:
对于多个表的级联查询,主要是要分析清楚哪个是主表,我们可以通过在此基础上的映射和包装,来实践这些特殊要求的查询。