1.一对一
需求：从订单角度出发，查询所有订单和用户信息
（1）传统方式：自定义包装pojo OrderCustom类继承Orders类，增加User的几个属性，映射返回结果用resultType
//代码见 工程ch04-mybatis01_0
（2）使用mybatis ：association标签 ，映射文件中用resultMap
//代码见 工程ch04-mybatis01_1
//订单pojo
public class Orders {
private Integer id;
private Integer userId;
private String orderId;
private Date createtime;
private String note;
//关联用户对象
private User user;
}
//用户pojo
public class User{
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
}
//映射文件
<mapper namespace="com.neuedu.mapper.OrdersMapperCustom">
<resultMap type="com.neuedu.pojo.Orders" id="OrdersUserResultMap">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="orderid" property="orderId"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 配置映射的关联的用户信息 -->
<!-- association：用于映射关联查询单个对象的信息
property：要将关联查询的用户信息映射到Orders中哪个属性
javaType：表示关联查询的结果类型
-->
<association property="user" javaType="com.neuedu.pojo.User">
<!-- id：关联查询用户的唯 一标识 property：映射到user的哪个属性
-->
<id column="userid" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<!-- 查询订单关联查询用户信息，使用resultmap -->
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
SELECT
orders.*,
T_USER.username,
T_USER.sex,
T_USER.address
FROM
orders,
T_USER
WHERE orders.userid = T_USER.id
</select>
//接口
public interface OrdersMapperCustom {
public List<Orders> findOrdersUserResultMap()throws Exception;
}
//测试类
public class OrdersMapperCustomTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
// mybatis配置文件
String resource = "SqlMapConfig.xml";
// 得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建会话工厂，传入mybatis的配置文件信息
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindOrdersUserResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建代理对象
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
// 调用maper的方法
List<Orders> list = ordersMapperCustom.findOrdersUserResultMap();
System.out.println(list);
for(Orders order:list) {
System.out.print("用户id "+order.getUserId());
System.out.print("用户id "+order.getUser().getId());
System.out.print("用户名 "+order.getUser().getUsername());
System.out.print("用户地址 "+order.getUser().getAddress());
System.out.print("订单号 "+order.getOrderId());
System.out.println("订单生成时间 "+order.getCreatetime());
}
sqlSession.close();
}
}
2.一对多
需求：从订单角度出发，查询所有订单和用户信息以及订单详细信息
mybatis使用collection, 返回结果resultmap //见工程ch04-mybatis02
//订单pojo
public class Orders {
private Integer id;
private Integer userId;
private String orderId;
private Date createtime;
private String note;
//用户信息
private User user;
//订单明细
private List<Orderdetail> orderdetails;
}
//接口
public interface OrdersMapperCustom {
public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;
}
//映射文件
<mapper namespace="com.neuedu.mapper.OrdersMapperCustom">
<!-- 订单查询关联用户的resultMap-->
<resultMap type="com.neuedu.pojo.Orders" id="OrdersUserResultMap">
<id column="id" property="id"/>
<result column="userid" property="userId"/>
<result column="orderid" property="orderId"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<association property="user" javaType="com.neuedu.pojo.User">
<id column="userid" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<!-- 订单及订单明细的resultMap
使用extends继承，不用在中配置订单信息和用户信息的映射 -->
<resultMap type="com.neuedu.pojo.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
<collection property="orderdetails" ofType="com.neuedu.pojo.Orderdetail">
<id column="orderdetailid" property="id"/>
<result column="itemsid" property="itemsId"/>
<result column="itemsnum" property="itemsNum"/>
<result column="ordersid" property="ordersId"/>
</collection>
</resultMap>
<!-- 查询订单关联查询用户及订单明细，使用resultmap -->
<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
SELECT
orders.*,
T_USER.username,
T_USER.sex,
T_USER.address,
orderdetail.id orderdetailid,
orderdetail.itemsid,
orderdetail.itemsnum,
orderdetail.ordersid
FROM
orders,
T_USER,
orderdetail
WHERE orders.userid = T_USER.id AND orderdetail.ordersid=orders.id
</select>
</mapper>
//测试类
@Test
public void testFindOrdersAndOrderDetailResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建代理对象
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
// 调用maper的方法
List<Orders> list = ordersMapperCustom.findOrdersAndOrderDetailResultMap();
for(Orders order:list) {
System.out.print("用户id "+order.getUserId());
System.out.print("用户id "+order.getUser().getId());
System.out.print("用户名 "+order.getUser().getUsername());
System.out.print("用户地址 "+order.getUser().getAddress());
System.out.print("订单号 "+order.getOrderId());
System.out.print("订单号 "+order.getCreatetime());
List<Orderdetail> odList = order.getOrderdetails();
for(Orderdetail orderdetail:odList) {
System.out.print("订单明细id "+orderdetail.getId());
System.out.print("商品id "+orderdetail.getItemsId());
System.out.println("商品数量 "+orderdetail.getItemsNum());
}
}
sqlSession.close();
}
3.多对多关联查询
从用户角度查询所有订单，订单明细，商品信息
用户-- 订单 一对多
订单--订单明细 一对多
订单明细--商品信息 一对一
从用户角度看： 订单---商品 多对多
//用户pojo
public class User {
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
//用户创建的订单列表
private List<Orders> ordersList;
}
//订单pojo
public class Orders {
private Integer id;
private Integer userId;
private String orderId;//订单号
private Date createtime;//订单生成日期
private String note;
//订单明细
private List<Orderdetail> orderdetails;
}
//订单详情pojo
public class Orderdetail {
private Integer id;
private Integer ordersId;//订单号
private Integer itemsId;//商品编号
private Integer itemsNum;//数量
//明细对应的商品信息
private Items items;
}
//商品pojo
public class Items {
private Integer id;//商品编号
private String name;//商品名
private Float price;//价格
private String pic;//图片
private Date createtime;//商品商家日期
private String detail;//商品介绍
}
//接口
public interface OrdersMapperCustom {
public List<User> findUserAndItemsResultMap() throws Exception;
}
//映射文件
<mapper namespace="com.neuedu.mapper.OrdersMapperCustom">
<!-- 查询用户及购买的商品 -->
<resultMap type="com.neuedu.pojo.User" id="UserAndItemsResultMap">
<!-- 用户信息 -->
<id column="userid" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 订单信息 一个用户对应多个订单，使用collection映射 -->
<collection property="ordersList" ofType="com.neuedu.pojo.Orders">
<id column="id" property="id"/>
<result column="orderId" property="orderId"/>
<result column="userid" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 订单明细 一个订单包括 多个明细 -->
<collection property="orderdetails" ofType="com.neuedu.pojo.Orderdetail">
<id column="orderdetailid" property="id"/>
<result column="itemsid" property="itemsId"/>
<result column="itemsnum" property="itemsNum"/>
<result column="ordersid" property="ordersId"/>
<!-- 商品信息 一个订单明细对应一个商品 -->
<association property="items" javaType="com.neuedu.pojo.Items">
<id column="itemsid" property="id"/>
<result column="itemsname" property="name"/>
<result column="itemsdetail" property="detail"/>
<result column="itemsprice" property="price"/>
</association>
</collection>
</collection>
</resultMap>
<!-- 查询用户及购买的商品信息，使用resultmap -->
<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
SELECT
orders.*,
t_user.username,
t_user.sex,
t_user.address,
orderdetail.id orderdetailid,
orderdetail.itemsid,
orderdetail.itemsnum,
orderdetail.ordersid,
items.name itemsname,
items.detail itemsdetail,
items.price itemsprice
FROM
orders,
t_user,
orderdetail,
items
WHERE orders.userid = t_user.id AND orderdetail.ordersid=orders.id AND orderdetail.itemsid = items.id
</select>
</mapper>
//测试类
@Test
public void testFindUserAndItemsResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
// 创建代理对象
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
// 调用maper的方法
List<User> list = ordersMapperCustom.findUserAndItemsResultMap();
for(User user:list) {
System.out.print("用户id "+user.getId());
System.out.println("用户名 "+user.getUsername());
List<Orders> orderList = user.getOrdersList();
for(Orders order:orderList) {
List<Orderdetail> detailList = order.getOrderdetails();
System.out.print("订单编号："+order.getOrderId());
System.out.print("订单创建时间："+order.getCreatetime());
for(Orderdetail detail:detailList) {
Items items = detail.getItems();
System.out.print("商品id " +items.getId());
System.out.print("商品名称 " +items.getName());
System.out.print("商品价格 " +items.getPrice());
System.out.print("商品详情 " +items.getDetail());
System.out.println();
}
}
}
sqlSession.close();
}
4、分页设计
//传统方式 见ch04-mybatis04
<!-- mysql 分页查询 -->
<select id="pageList" parameterType="com.neuedu.common.Page" resultMap="UserAndItemsResultMap">
SELECT
orders.*,
t_user.username,
t_user.sex,
t_user.address,
orderdetail.id orderdetailid,
orderdetail.itemsid,
orderdetail.itemsnum,
orderdetail.ordersid,
items.name itemsname,
items.detail itemsdetail,
items.price itemsprice
FROM
orders,
t_user,
orderdetail,
items
WHERE orders.userid = t_user.id AND orderdetail.ordersid=orders.id AND orderdetail.itemsid = items.id
limit #{startIndex},#{pageSize}
</select>
<select id="pageCount" resultType="int">
SELECT count(*)
FROM
orders,
t_user,
orderdetail,
items
WHERE orders.userid = t_user.id AND orderdetail.ordersid=orders.id AND orderdetail.itemsid = items.id
</select>
（2）使用rowBounds 插件
//映射文件
<select id="findUserWithPage" resultType="com.neuedu.pojo.User">
SELECT * FROM T_USER
</select>
//接口
public interface UserMapper {
public List<User> findUserWithPage(RowBounds rowBounds) throws Exception;
}
//测试类
@Test
public void testFindUserWithPage() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
//创建UserMapper对象，mybatis自动生成mapper代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//调用userMapper的方法
List<User> list = userMapper.findUserWithPage(new RowBounds(4, 4));//从第一条取到第四条
for (User user : list) {
System.out.println(user);
}
sqlSession.close();
}
3893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
