数据模型（数据库表）的分析思路

作者: Stringer | 来源:发表于2016-11-26 09:38 被阅读152次

1.了解每张表记录的数据内容
分模块的对每张表记录的内筒进行分析，相当于在学习系统需求（功能）的过程

2.每张表重要字段的设置
非空字段、外键字段

3.数据库级别表一表之间的关系
外键关系

4.表与表之间的业务关系
一定要建立在某个也无意义基础上去分析，先分析在数据库级别有关系的两表间的业务关系，再分析数据库级别没有关系的表之间是否有关系

案例：

2016-11-22_234755.png

用户表user：记录了购买商品的用户信息

订单表orders：记录了用户创建的订单（购买商品的订单）

订单详情表orderdetail：记录了订单信息商品信息

商品表items：记录了商品信息

表与表之间的业务关系：

user和orders：
user---->orders:一个用户可以创建多个订单，一对多
orders---->user:一个订单只由一个用户创建，一对一

orders和orderdetail：
orders---->orderdetail:一个订单可以包括多个订单详情（一个订单可以购买多个商品），一对多

orderdetail---->orders:一个订单详情只能包括在一个订单中，一对一

orderdetail和items：
orderdetail---->items:一个订单详情只对应一个商品，一对一

items---->orderdetail:一个商品可以包括在多个订单商品中，一对多

orders和items（通过orderdetail判断）：
items---->orders:一对多

orders---->items:一对多
orders和items是多对多的关系

user和items：多对多

高级查询：

1.一对一
需求：查询订单信息，关联查询创建订单的用户信息
1.1resultType
1.1.1sql语句：
确定查询的主表：orders
确定查询的关联表：user

SELECT orders.* ,
        `user`.username,
        `user`.sex,
        `user`.address
FROM orders,user WHERE orders.user_id=`user`.id

1.1.2创建pojo
将上面sql查询的结果映射到pojo中，pojo中必须包含所有查询列名
原始的orders.java不能映射全部字段，需要新创建的pojo
创建一个pojo继承包括查询字段较多的po类

package cn.ztc.mybatis.po;

//通过此类映射订单关联用户的查询结果
public class OrdersCustom extends Orders{
    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;
    }

}

OrdersCustomMapper.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等于mapper接口的地址 -->
<mapper namespace="cn.ztc.mybatis.mapper.OrdersCustomMapper">
    <select id="findOrdersUser" resultType="cn.ztc.mybatis.po.OrdersCustom">
        SELECT orders.* ,
        `user`.username,
        `user`.sex,
        `user`.address
        FROM orders,user WHERE orders.user_id=`user`.id
    </select>
</mapper>```
OrdersCustomMapper.java

package cn.ztc.mybatis.mapper;

import java.util.List;

import cn.ztc.mybatis.po.OrdersCustom;

public interface OrdersCustomMapper {
public List<OrdersCustom> findOrdersUser()throws Exception;
}

junit测试类

package cn.ztc.mybatis.mapper;

import static org.junit.Assert.*;

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.ztc.mybatis.po.OrdersCustom;

public class OrdersCustomMapperTest {

private SqlSessionFactory sqlSessionFactory;
//此方法在执行testFindUserById方法之前执行
@Before
public void setUp() throws Exception{
    String resource = "SqlMapConfig.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}

@Test
public void testFindOrdersUser() throws Exception {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //创建代理对象
    OrdersCustomMapper OrdersCustomMapper = sqlSession.getMapper(OrdersCustomMapper.class);
    List<OrdersCustom> list = OrdersCustomMapper.findOrdersUser();
    System.out.println(list);
    sqlSession.close();
}

}


1.2resultMap
使用resultMap将查询到的订单信息映射到Orders对象中，在Orders对象中添加User属性，将关联查询出来的用户信息映射到Orders对象中添加User属性中
1.2.1sql语句

1.2.2在Orders对象中添加User属性

1.2.3mapper.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.ztc.mybatis.mapper.OrdersCustomMapper">

<resultMap type="cn.ztc.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.ztc.mybatis.po.User">
<id column="user_id" javaType="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>

<!-- resultType -->
<select id="findOrdersUser" resultType="cn.ztc.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>```
1.2.4mapper.java

package cn.ztc.mybatis.mapper;

import java.util.List;

import cn.ztc.mybatis.po.*;


public interface OrdersCustomMapper {
    public List<OrdersCustom> findOrdersUser()throws Exception;
    
    public List<Orders> findOrdersUserResultMap()throws Exception;
}

实现一对一查询：
使用resultType较为简单，只需在pojo扩展类中添加列名对应的属性

使用resultMap，需要在mapper.xml中单独定义resultMap

resultMap可以实现延迟加载，resultType无法实现延迟加载

2.一对多
需求：查询订单关联订单详情

resultMap

sql语句：
主查询表：订单表
关联查询表：订单详情表

在orders表中添加List<OrderDetail> orderDetails属性

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等于mapper接口的地址 -->
<mapper namespace="cn.ztc.mybatis.mapper.OrdersCustomMapper">
    <!-- 订单关联查询用户的resultMap -->
    <resultMap type="cn.ztc.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要讲关联查询的用户信息映射到Orders的哪个属性
         -->
        <association property="user" javaType="cn.ztc.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>
    <!-- 使用extends就不用配置订单信息和用户信息的映射 -->
    <resultMap type="cn.ztc.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
        <!-- 订单信息 -->
        
        <!-- 配置映射的用户信息 -->
        <!-- association用于关联查询单个对象的信息
            property要讲关联查询的用户信息映射到Orders的哪个属性
         -->
        
        <!-- 订单详情信息 
            collection关联查询多条记录映射到对象中
            映射到集合中pojo的类型
        -->
        <collection property="orderdetails" ofType="cn.ztc.mybatis.po.Orderdetail">
            <id column="detailId" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
        </collection>
    </resultMap>
        
    
    <!-- resultType -->
    <select id="findOrdersUser" resultType="cn.ztc.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>
    
    <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
        SELECT orders.* ,
        `user`.username,
        `user`.sex,
        `user`.address,
        orderdetail.id detailId,
        orderdetail.items_id,
        orderdetail.items_num
        FROM orders,user,orderdetail
        WHERE orders.user_id=`user`.id and orderdetail.orders_id=orders.id
    </select>
</mapper>```
mapper.java

package cn.ztc.mybatis.mapper;

import java.util.List;

import cn.ztc.mybatis.po.*;

public interface OrdersCustomMapper {
public List<OrdersCustom> findOrdersUser()throws Exception;

public List<Orders> findOrdersUserResultMap()throws Exception;

public List<Orders> findOrdersAndOrderDetailResultMap()throws Exception;

}


3.多对多
需求：查询用户关联商品信息
主表：用户表
关联表：orders、orderdetail、items

在user类中添加List<Orders> orderslist属性
在Orders类中添加List<Orderdetail> Orderdetaillist属性
在Orderdetail中添加items属性

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.ztc.mybatis.mapper.OrdersCustomMapper">

<resultMap type="cn.ztc.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.ztc.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 type="cn.ztc.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">

    <!-- 配置映射的用户信息 -->
    <!-- association用于关联查询单个对象的信息
        property要讲关联查询的用户信息映射到Orders的哪个属性
     -->
    
    <!-- 订单详情信息 
        collection关联查询多条记录映射到对象中
        映射到集合中pojo的类型
    -->
    <collection property="orderdetails" ofType="cn.ztc.mybatis.po.Orderdetail">
        <id column="detailId" property="id"/>
        <result column="items_id" property="itemsId"/>
        <result column="items_num" property="itemsNum"/>
    </collection>
</resultMap>

<resultMap type="cn.ztc.mybatis.po.User" id="UserAndItemsResultMap">
    <!-- user信息 -->
    <id column="user_id" property="id"/>
    <result column="username" property="username"/>
    <result column="sex" property="sex"/>
    <result column="address" property="address"/>
    <!-- orders信息 -->
    <collection property="ordersList" ofType="cn.ztc.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.ztc.mybatis.po.Orderdetail">
            <id column="detailId" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <!-- 商品信息 -->
            <association property="items" javaType="cn.ztc.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>
    

<!-- resultType -->
<select id="findOrdersUser" resultType="cn.ztc.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>

<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
    SELECT orders.* ,
    `user`.username,
    `user`.sex,
    `user`.address,
    orderdetail.id detailId,
    orderdetail.items_id,
    orderdetail.items_num
    FROM orders,user,orderdetail
    WHERE orders.user_id=`user`.id and orderdetail.orders_id=orders.id
</select>

<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
    SELECT orders.* ,
    `user`.username,
    `user`.sex,
    `user`.address,
    orderdetail.id detailId,
    orderdetail.items_id,
    orderdetail.items_num,
    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>

</mapper>```
mapper.java

package cn.ztc.mybatis.mapper;

import java.util.List;

import cn.ztc.mybatis.po.*;


public interface OrdersCustomMapper {
    public List<OrdersCustom> findOrdersUser()throws Exception;
    
    public List<Orders> findOrdersUserResultMap()throws Exception;
    
    public List<Orders> findOrdersAndOrderDetailResultMap()throws Exception;
    
    public List<User> findUserAndItemsResultMap()throws Exception;
}

数据模型（数据库表）的分析思路

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读