数据库关系分析
现在,存在如下的数据库:
CREATE TABLE IF NOT EXISTS `test`.`user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL COMMENT '用户名称',
`pass` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL COMMENT '用户密码',
`birthday` DATE NULL DEFAULT NULL COMMENT '生日',
`sex` CHAR(1) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL COMMENT '性别',
`address` VARCHAR(256) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;
CREATE TABLE IF NOT EXISTS `test`.`items` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL COMMENT '商品名称',
`price` FLOAT(10,1) NOT NULL COMMENT '商品价格',
`detail` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL COMMENT '商品描述',
`pic` VARCHAR(512) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL COMMENT '商品图片',
`createtime` DATETIME NULL DEFAULT NULL COMMENT '生产日期',
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;
CREATE TABLE IF NOT EXISTS `test`.`purchase` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`uid` INT(11) NOT NULL COMMENT '下订单用户ID',
`number` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL COMMENT '订单号',
`createtime` DATETIME NOT NULL COMMENT '创建订单时间',
`note` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
INDEX `FK_ORDERS_UID` (`uid` ASC),
CONSTRAINT `FK_ORDERS_UID`
FOREIGN KEY (`uid`)
REFERENCES `test`.`user` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;
CREATE TABLE IF NOT EXISTS `test`.`purchasedetail` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`orders_id` INT(11) NOT NULL COMMENT '订单ID',
`items_id` INT(11) NOT NULL COMMENT '商品ID',
`items_num` INT(11) NOT NULL COMMENT '商品数购买量',
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC),
INDEX `FK_ORDERDETAIL_ORDERSID` (`orders_id` ASC),
INDEX `FK_ORDERDETAIL_ITEMSID` (`items_id` ASC),
CONSTRAINT `FK_ORDERDETAIL_ITEMSID`
FOREIGN KEY (`items_id`)
REFERENCES `test`.`items` (`id`),
CONSTRAINT `FK_ORDERDETAIL_ORDERSID`
FOREIGN KEY (`orders_id`)
REFERENCES `test`.`purchase` (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;
其数据库的关系图如图所示:
表与表之间业务关系:
数据库的映射关系都是基于业务的需求基础上来进行分析的。上面的数据库中,test.user与test.purchase、test.purchase与test.purchasedetail、purchasedetail与test.items都分别存在外键关联。
test.user与test.purchase
a. test.user->test.purchase
一个用户可以生成多个订单:一对多
b. test.purchase->test.user
一个订单只能由一个用户生成:一对一test.purchase与test.purchasedetail
a. test.purchase->test.purchasedetail
一个订单可以包含多个订单明细:一对多
b. test.purchasedetail->test.purchase
一个订单明细只能在一个订单中:一对一test.purchasedetail与test.items
a. test.purchasedetail->test.items
一个订单明细只能包含一个商品的信息:一对一
b. test.items->test.purchasedetail
一个商品可以存在于多个订单明细中:一对多test.user与test.purchasedetail
test.user与test.purchasedetail之间没有直接的外键关联关系。但是中间可以通过test.purchase相关联。
a. test.user–>test.purchase–>test.purchasedetail
一个用户生成多个订单,一个订单可以包含多个订单明细:一对多
b. test.purchasedetail–>test.purchase–>test.user
一个订单明细只能存在于一个订单当中,而一个订单只能由一个用户创建:一对一test.purchase与test.items
test.purchase与test.items之间没有直接的外键关联关系。但是中间可以通过test.purchasedetail相关联。
a. test.purchase–>test.purchasedetail–>test.items
一个订单可以包含多个订单明细,并且一个订单明细只能包含一个商品的信息:一对多
b. test.items–>test.purchasedetail–>test.purchase
一个商品可以存在于多个订单明细中,然而一个订单详情只能在一个订单中:一对多test.user与test.items
test.user与test.items之间没有直接的外键关联关系。但是中间可以通过test.purchase、test.purchasedetail相关联。
a.test.user–>test.purchase–>test.purchasedetail–>test.items
一个用户可以生成多个订单,一个订单可以包含多个订单明细,一个订单明细只能包含一个商品的信息:一对多
b.test.items–>test.purchasedetail–>test.purchase–>test.user
一个商品可以存在于多个订单明细中,一个订单详情只能在一个订单中,一个订单只能由一个用户创建:一对多
一对一映射
需求
查询某个订单信息,关联查其用户信息。
分析
根据以上的分析,我们知道这是个一对一的关系映射。在此采用ResultType以及ResultMap两种方法来实现。
1. ResultType
SQL语句
SELECT
purchase.id, purchase.uid, purchase.number, purchase.note, user.name, user.sex, user.address
FROM
test.purchase
JOIN
test.user ON test.purchase.uid = test.user.id
WHERE
test.purchase.id = #{id}
POJO
public class OrderUserPojo extends OrderPojo {
private String name;
private int sex;
private String address;
//getter & setter
}
Mapper映射文件
<select id="selectOrderUserByID" parameterType="int" resultType="indi.latch.mybatis.pojo.OrderUserPojo">
SELECT
purchase.id, purchase.uid, purchase.number, purchase.note, user.name, user.sex, user.address
FROM
test.purchase
JOIN
test.user ON test.purchase.uid = test.user.id
WHERE
test.purchase.id = #{id}
</select>
接口
public OrderUserPojo selectOrderUserByID () throws Exception;
测试代码
@Test
public void selectOrderUser () throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
OrderUserPojo orderUser = orderDao.selectOrderUserByID();
System.out.println(orderUser.toString());
sqlSession.close();
}
2. ResultMap
POJO
public class OrderPojo {
private int id;
private int uid;
private String number;
private Date createtime;
private String note;
private UserPojo user;
//getter & setter
}
Mapper映射文件
<!--resultMap开发-->
<resultMap id="OrderUserMap" type="indi.latch.mybatis.pojo.OrderPojo">
<id column="id" property="id"></id>
<result column="uid" property="uid"></result>
<result column="number" property="number"></result>
<result column="note" property="note"></result>
<association property="user" javaType="indi.latch.mybatis.pojo.UserPojo">
<id column="uid" property="id"></id>
<result column="name" property="name"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<select id="selectOrderUserMapByID" resultMap="OrderUserMap">
SELECT
purchase.id, purchase.uid, purchase.number, purchase.note, user.name, user.sex, user.address
FROM
test.purchase
JOIN
test.user ON test.purchase.uid = test.user.id
WHERE
test.purchase.id = #{id}
</select>
接口
public OrderPojo selectOrderUserMapByID () throws Exception;
测试代码
@Test
public void selectOrderUserMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
OrderPojo order = orderDao.selectOrderUserMapByID();
System.out.println(order.toString());
sqlSession.close();
}
代码
- git@code.csdn.net:xl890727/mybatisdemo.git
- https://code.csdn.net/xl890727/mybatisdemo.git
中的advance-one2one分支
一对多映射
需求
查询某个订单信息,关联查询用户信息,并关联查询订单详情信息。
分析
从上面的数据库分析,接合需求,可知此关系为一对多映射关系。此处只采用ResultMap实现。
SQL语句
SELECT
purchase.id,
purchase.uid,
purchase.number,
purchase.note,
user.name,
user.sex,
user.address,
purchasedetail.id AS purchasedetail_id,
purchasedetail.items_id,
purchasedetail.items_num
FROM
test.purchase,
test.user,
test.purchasedetail
WHERE
test.purchase.id = #{id}
AND purchase.uid = user.id
AND purchase.id = purchasedetail.orders_id;
POJO
public class OrderPojo {
private int id;
private int uid;
private String number;
private Date createtime;
private String note;
private UserPojo user;
private List<OrderDetailPojo> orderDetails;
//setter & getter
}
Mapper映射文件
<resultMap id="OrderWithDetailsMap" type="OrderPojo">
<id column="id" property="id"></id>
<result column="uid" property="uid"></result>
<result column="number" property="number"></result>
<result column="note" property="note"></result>
<association property="user" javaType="indi.latch.mybatis.pojo.UserPojo">
<id column="uid" property="id"></id>
<result column="name" property="name"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
<collection property="orderDetails" ofType="indi.latch.mybatis.pojo.OrderDetailPojo">
<id column="purchasedetail_id" property="id"></id>
<result column="items_id" property="itemId"></result>
<result column="items_num" property="itemNum"></result>
</collection>
</resultMap>
<select id="selectOrderWithDetails" resultMap="OrderWithDetailsMap">
SELECT
purchase.id,
purchase.uid,
purchase.number,
purchase.note,
user.name,
user.sex,
user.address,
purchasedetail.id AS purchasedetail_id,
purchasedetail.items_id,
purchasedetail.items_num
FROM
test.purchase,
test.user,
test.purchasedetail
WHERE
test.purchase.id = #{id}
AND purchase.uid = user.id
AND purchase.id = purchasedetail.orders_id;
</select>
接口
public OrderPojo selectOrderWithDetails() throws Exception;
测试代码
@Test
public void selectOrderWithDetails () throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
OrderPojo orderWithDetail = orderDao.selectOrderWithDetails();
System.out.println(orderWithDetail.toString());
sqlSession.close();
}
代码
- git@code.csdn.net:xl890727/mybatisdemo.git
- https://code.csdn.net/xl890727/mybatisdemo.git
中的advance-one2multi分支
多对多映射
需求
查询所有用户信息,关联查询对应购买的商品信息。
分析
当查询所有用户以及其购买的商品信息时,根据以上的分析可知是多对多的关系。
SQL
SELECT
user.*,
purchase.id AS purchase_id,
purchase.number,
purchase.note,
purchasedetail.id AS purchasedetail_id,
purchasedetail.items_id,
purchasedetail.items_num,
items.name AS item_name,
items.price,
items.detail
FROM
test.user,
test.purchase,
test.purchasedetail,
test.items
WHERE
user.id = purchase.uid
AND purchase.id = purchasedetail.orders_id
AND items.id = purchasedetail.items_id;
POJO
public class UserPojo {
private int id;
private String name;
private String pass;
private Date birthday;
private int sex;
private String address;
private List<OrderPojo> orders;
//getter & setter
}
public class OrderPojo {
private int id;
private int uid;
private String number;
private Date createtime;
private String note;
private List<OrderDetailPojo> orderDetails;
//getter & setter
}
public class OrderDetailPojo {
private int id;
private int orderId;
private int itemId;
private int itemNum;
private ItemPojo item;
//getter & setter
}
public class ItemPojo {
private int id;
private String name;
private float price;
private String detail;
private String pic;
private Date createtime;
//getter & setter
}
Mapper映射文件
<resultMap id="UserWithItems" type="indi.latch.mybatis.pojo.UserPojo">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<!--用户与订单的一对一关系-->
<collection property="orders" ofType="indi.latch.mybatis.pojo.OrderPojo">
<id column="purchase_id" property="id"></id>
<result column="number" property="number"></result>
<result column="note" property="note"></result>
<!--订单与订单详情的一对多关系-->
<collection property="orderDetails" ofType="indi.latch.mybatis.pojo.OrderDetailPojo">
<id column="purchasedetail_id" property="id"></id>
<result column="items_id" property="itemId"></result>
<result column="items_num" property="itemNum"></result>
<!--订单详情与商品信息的一对一关系-->
<association property="item" javaType="indi.latch.mybatis.pojo.ItemPojo">
<id column="items_id" property="id"></id>
<result column="item_name" property="name"></result>
<result column="price" property="price"></result>
</association>
</collection>
</collection>
</resultMap>
<select id="selectUserWithItems" resultMap="UserWithItems">
SELECT
user . *,
purchase.id AS purchase_id,
purchase.number,
purchase.note,
purchasedetail.id AS purchasedetail_id,
purchasedetail.items_id,
purchasedetail.items_num,
items.name AS item_name,
items.price,
items.detail
FROM
test.user,
test.purchase,
test.purchasedetail,
test.items
WHERE
user.id = purchase.uid
AND purchase.id = purchasedetail.orders_id
AND items.id = purchasedetail.items_id;
</select>
接口
public List<UserPojo> selectUserWithItems() throws Exception;
测试代码
@Test
public void selectUserWithItems () throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderDao orderDao = sqlSession.getMapper(OrderDao.class);
List<UserPojo> userItemList = orderDao.selectUserWithItems();
System.out.println(userItemList.toString());
sqlSession.close();
}
代码
- git@code.csdn.net:xl890727/mybatisdemo.git
- https://code.csdn.net/xl890727/mybatisdemo/tree/advance-one2one
总结
- ResultType最终需要我们自己创建符合查询结果的POJO;
- ResultMap,其实是POJO与POJO之间的包含关系;
- POJO与POJO之间的包含关系,不外乎一对一、一对多,如果是一对一,则采用assocation,反之则为collection;
- ResultMap相比于ResultType,虽然写起来比较复杂,但是ResultMap可以具有更多的功能。譬如后面将会介绍的延迟加载。