订单商品数据模型分析
1.数据模型分析思路
- 每张表记录的数据内容
分模块对每张表记录的内容进行熟悉,相当 于你学习系统 需求(功能)的过程 - 每张表重要的字段设置
非空字段、外键字段 - 数据库级别表与表之间的关系
外键关系 - 表与表之间的业务关系
在分析表与表之间的业务关系时一定要建立 在某个业务意义基础上去分析。
2.分析过程
第一步:每张表记录的数据内容
- 用户表user:记录了购买商品的用户信息
- 订单表orders:记录了用户所创建的订单(购买商品的订单)
- 订单明细表orderdetail:记录了订单的详细信息即购买商品的信息
- 商品表items:记录了商品信息
第二步:每张表重要的字段设置
第三步:数据库级别表与表之间的关系
表与表之间的业务关系:在分析表与表之间的业务关系时需要建立 在某个业务意义基础上去分析。先分析数据库级别之间有关系的表之间的业务关系。
usre和orders
- user—>orders:一个用户可以创建多个订单,一对多
- orders—>user:一个订单只由一个用户创建,一对一
orders和orderdetail: - orders—>orderdetail:一个订单可以包括 多个订单明细,因为一个订单可以购买多个商品,每个商品的购买信息在orderdetail记录,一对多
- orderdetail—> orders:一个订单明细只能包括在一个订单中,一对一
orderdetail和itesm - orderdetail—>itesms:一个订单明细只对应一个商品信息,一对一
- items–> orderdetail:一个商品可以包括在多个订单明细 ,一对多
第四步:表与表之间的业务关系
再分析数据库级别没有关系的表之间是否有业务关系,orders和items之间可以通过orderdetail表建立关系。
需求:查询所有订单信息,关联查询下单用户信息。
需求分析:因为一个订单信息只会是一人下的订单,所以从查询订单信息出发关联查询用户信息为一对一查询。如果查询用户信息关联查询用户下的订单信息则为一对多查询,因为一个用户可以下多个订单。
1.mapper接口
List<Orders> findOrderUser() throws Exception;
2.sql映射文件
<?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="mapper.OrdersUserMapper">
<select id="findOrderUser" resultMap="orderUserMap">
SELECT a.*,b.username,b.address
FROM orders as a join user as b
on a.user_id = b.id
</select>
<resultMap id="orderUserMap" type="Orders">
<!-- 这里的id,是mybatis在进行一对一查询时将user字段映射为user对象时要使用,必须写 -->
<id column="order_id" property="order_id"/>
<result column="order_no" property="order_no"/>
<result column="note" property="note"/>
<association javaType="User" property="user">
<!-- 这里的id为user的id,如果写上表示给user的id属性赋值 -->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="address" property="address" />
</association>
</resultMap>
</mapper>
association:表示进行关联查询单条记录
property:表示关联查询的结果存储在cn.itcast.mybatis.po.Orders的user属性中
javaType:表示关联查询的结果类型
3.po类编写
public class Orders {
private int order_id;
private int user_id;
private String order_no;
private String createTime;
private String note;
private User user;
//get和set方法......
}
public class User {
//属性名要和数据库表的字段对应
private int id;
private String username;// 用户姓名
private String sex;// 性别
private String birthday;// 生日
private String address;// 地址
//get和set方法......
}
在Orders类中加入user属性,user属性中用于存储关联查询的用户信息,因为订单关联查询用户是一对一关系,所以这里使用单个user对象存储关联查询的用户信息。
4.测试
public class Test {
private static final Logger log = Logger.getLogger(Test.class.getName());
public static void main(String[] args) throws Exception {
// 1.获取mybatis配置文件的路径
String resource = "config/mybatis.xml";
// 2.得到配置文件流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 3.根据mybatis配置文件的信息创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersUserMapper ordersUserMapper = sqlSession.getMapper(OrdersUserMapper.class);
List<Orders> ordersList = ordersUserMapper.findOrderUser();
Iterator<Orders> iterator = ordersList.iterator();
while(iterator.hasNext()){
Orders order = iterator.next();
System.out.println("order_no:"+ order.getOrder_no());
System.out.println("note:"+ order.getNote());
System.out.println("username:"+order.getUser().getUsername());
System.out.println("address:"+order.getUser().getAddress());
System.out.println();
}
sqlSession.close();
}
}
运行测试结果如下
"C:\Program Files\Java\jdk1.8.0_51\bin\java.exe" "-javaagent:D:\IDEA\IntelliJ IDEA 2018.2\lib\idea_rt.jar=56818:D:\IDEA\IntelliJ IDEA 2018.2\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\Java\jdk1.8.0_51\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_51\jre\lib\rt.jar;E:\代码存档\mybatis\8.mybatis_mapping_1to1\target\test-classes;E:\代码存档\mybatis\8.mybatis_mapping_1to1\target\classes;E:\repository\junit\junit\4.11\junit-4.11.jar;E:\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;E:\repository\mysql\mysql-connector-java\5.1.41\mysql-connector-java-5.1.41.jar;E:\repository\org\mybatis\mybatis\3.2.2\mybatis-3.2.2.jar;E:\repository\log4j\log4j\1.2.17\log4j-1.2.17.jar" Test
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.log4j.Log4jImpl' adapter.
DEBUG [main] - Class not found: org.jboss.vfs.VFS
DEBUG [main] - JBoss 6 VFS API is not available in this environment.
DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
DEBUG [main] - Find JAR URL: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/po
DEBUG [main] - Not a JAR: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/po
DEBUG [main] - Reader entry: Orders.class
DEBUG [main] - Reader entry: User.class
DEBUG [main] - Listing file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/po
DEBUG [main] - Find JAR URL: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/po/Orders.class
DEBUG [main] - Not a JAR: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/po/Orders.class
DEBUG [main] - Reader entry: ���� 3 7
DEBUG [main] - Find JAR URL: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/po/User.class
DEBUG [main] - Not a JAR: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/po/User.class
DEBUG [main] - Reader entry: ���� 3 H
DEBUG [main] - Checking to see if class po.Orders matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class po.User matches criteria [is assignable to Object]
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Find JAR URL: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/mapper
DEBUG [main] - Not a JAR: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/mapper
DEBUG [main] - Reader entry: OrdersUserMapper.class
DEBUG [main] - Reader entry: OrdersUserMapper.xml
DEBUG [main] - Listing file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/mapper
DEBUG [main] - Find JAR URL: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/mapper/OrdersUserMapper.class
DEBUG [main] - Not a JAR: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/mapper/OrdersUserMapper.class
DEBUG [main] - Reader entry: ���� 3
DEBUG [main] - Find JAR URL: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/mapper/OrdersUserMapper.xml
DEBUG [main] - Not a JAR: file:/E:/%e4%bb%a3%e7%a0%81%e5%ad%98%e6%a1%a3/mybatis/8.mybatis_mapping_1to1/target/classes/mapper/OrdersUserMapper.xml
DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
DEBUG [main] - Checking to see if class mapper.OrdersUserMapper matches criteria [is assignable to Object]
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 1547425104.
DEBUG [main] - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@5c3bd550]
DEBUG [main] - ==> Preparing: SELECT a.*,b.username,b.address FROM orders as a join user as b on a.user_id = b.id
DEBUG [main] - ==> Parameters:
order_no:2018080400001
note:
username:steven
address:nanjing
order_no:2018080600001
note:
username:cherry
address:shanghai
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c3bd550]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c3bd550]
DEBUG [main] - Returned connection 1547425104 to pool.