9.mybatis_mapping_1to1(传智播客)

订单商品数据模型分析

1.数据模型分析思路

1. 每张表记录的数据内容
   分模块对每张表记录的内容进行熟悉，相当 于你学习系统 需求（功能）的过程
2. 每张表重要的字段设置
   非空字段、外键字段
3. 数据库级别表与表之间的关系
   外键关系
4. 表与表之间的业务关系
   在分析表与表之间的业务关系时一定要建立 在某个业务意义基础上去分析。

2.分析过程
第一步:每张表记录的数据内容

1. 用户表user:记录了购买商品的用户信息
2. 订单表orders:记录了用户所创建的订单(购买商品的订单)
3. 订单明细表orderdetail:记录了订单的详细信息即购买商品的信息
4. 商品表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.