一 订单商品数据模型-分析
分析思路
-
user和orders：
- user → orders:一个用户可以创建多个订单，一对多
- orders → user:一个订单只能由一个用户创建，一对一
-
orders和orderdetail：
- orders → orderdetail:一个订单可以包括多个订单明细，因为一个订单可以购买多个商品，每个商品信息在orderdetail记录，一对多
- orderdetail → orders:一个订单明细只能包括在一个订单中，一对一
-
orderdetail和items：
- orderdetail→ items:一个订单明细只对应一个商品信息，一对一
- items → orderdetail:一个商品信息可以包括多个订单明细，一对多
二 一对一查询-使用resultType
- 需求：查询订单关联查询用户信息
OrdersBean.java
package entity;
import java.util.Date;
public class OrdersBean {
private int id;
private int user_id;
private String number;
private Date createtime;
private String note;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
@Override
public String toString() {
return "OrdersBean{" +
"id=" + id +
", user_id=" + user_id +
", number='" + number + '\'' +
", createtime=" + createtime +
", note='" + note + '\'' +
'}';
}
}
OrdersCustom.java
package pojo;
import entity.OrdersBean;
//通过此类映射订单和用户查询的结果，让此类继承包括字段较多的pojo类
public class OrdersCustom extends OrdersBean {
//添加用户属性
private String username;
private String sex;
private String address;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return super.toString()+"OrdersCustom{" +
"username='" + username + '\'' +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
OrdersMapperCustom.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="dao.OrdersMapperCustom">
<select id="findOrdersUser" resultType="pojo.OrdersCustom">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
</mapper>
OrdersMapperCustom.java
package dao;
import java.util.List;
public interface OrdersMapperCustom {
public List<OrdersMapperCustom> findOrdersUser() throws Exception;
}
OrdersMapperTest.java
import dao.OrdersMapperCustom;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class OrdersMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws IOException {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findOrdersUser() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
List<OrdersMapperCustom> ordersCustom = ordersMapperCustom.findOrdersUser();
System.out.println(ordersCustom);
}
}
执行结果
三 一对一查询-使用resultMap
OrdersBean.java
在OrdersBean中注入UserBean对象，并创建对应的get和set方法
图片.png
OrdersMapperCustom.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="dao.OrdersMapperCustom">
<!--订单查询关联用户的resultMap-->
<resultMap id="OrdersUserResultMap" type="entity.OrdersBean">
<!--配置映射的订单信息-->
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<!--配置映射的关联的用户信息-->
<!--association:用于映射关联查询单个对象的信息
property:要将关联查询的用户信息映射到Orders中哪个属性-->
<association property="userBean" javaType="entity.UserBean">
<!--id:关联查询用户的唯一标识
column：指定唯一标识用户信息的列
javaType：映射到user的那个属性-->
<id column="user_id" property="id"></id>
<result column="username" property="username"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<!--查询订单关联查询用户信息，使用resultType-->
<select id="findOrdersUser" resultType="pojo.OrdersCustom">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
<!--查询订单关联查询用户信息，使用resultMap-->
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
</mapper>
OrdersMapperCustom.java
//查询订单关联查询用户使用resultMap
public List<OrdersBean> findOrdersUserResultMap() throws Exception;
OrdersMapperTest.java
@Test
public void findOrdersUserResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
List<OrdersBean> ordersBeans = ordersMapperCustom.findOrdersUserResultMap();
System.out.println(ordersBeans);
}
测试结果
网友评论