一：实体关系分析
表：班级、学生、学籍档案、课程
** 班级(1)与学生(多)外键关联，一对多的关系
** 学生(1)与学籍档案(1)外键关联，一对一的关系
** 学生(多)与课程(多)是多对多的关系，通过中间表进行关联
二：实体类
Goods：
package com.imooc.mybatis.entity;
import java.util.List;
/**
* 商品类
*/
public class Goods {
//商品编号
private Integer goodsId;
//标题
private String title;
//子标题
private String subTitle;
//原始价格
private Float originalCost;
//当前价格
private Float currentPrice;
//折扣率
private Float discount;
//是否包邮
private Integer isFreeDelivery;
//分类编号
private Integer categoryId;
//一对多，一个goods对象下有多个GoodsDetail对象
private List<GoodsDetail> goodsDetails;
public Integer getGoodsId() {
return goodsId;
}
public void setGoodsId(Integer goodsId) {
this.goodsId = goodsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSubTitle() {
return subTitle;
}
public void setSubTitle(String subTitle) {
this.subTitle = subTitle;
}
public Float getOriginalCost() {
return originalCost;
}
public void setOriginalCost(Float originalCost) {
this.originalCost = originalCost;
}
public Float getCurrentPrice() {
return currentPrice;
}
public void setCurrentPrice(Float currentPrice) {
this.currentPrice = currentPrice;
}
public Float getDiscount() {
return discount;
}
public void setDiscount(Float discount) {
this.discount = discount;
}
public Integer getIsFreeDelivery() {
return isFreeDelivery;
}
public void setIsFreeDelivery(Integer isFreeDelivery) {
this.isFreeDelivery = isFreeDelivery;
}
public Integer getCategoryId() {
return categoryId;
}
public void setCategoryId(Integer categoryId) {
this.categoryId = categoryId;
}
public List<GoodsDetail> getGoodsDetails() {
return goodsDetails;
}
public void setGoodsDetails(List<GoodsDetail> goodsDetails) {
this.goodsDetails = goodsDetails;
}
}
GoodsDetail ：
package com.imooc.mybatis.entity;
/**
* 商品详情
*/
public class GoodsDetail {
private Integer gdId;
private Integer goodsId;
private String gdPicUrl;
private Integer gdOrder;
public Integer getGdId() {
return gdId;
}
public void setGdId(Integer gdId) {
this.gdId = gdId;
}
public Integer getGoodsId() {
return goodsId;
}
public void setGoodsId(Integer goodsId) {
this.goodsId = goodsId;
}
public String getGdPicUrl() {
return gdPicUrl;
}
public void setGdPicUrl(String gdPicUrl) {
this.gdPicUrl = gdPicUrl;
}
public Integer getGdOrder() {
return gdOrder;
}
public void setGdOrder(Integer gdOrder) {
this.gdOrder = gdOrder;
}
}
三：对应的xml
商品详情 goods_getail.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">
<!-- namespace命名空间下 的id是唯一的 -->
<mapper namespace="goodsDetail">
<select id="selectByGoodsId" parameterType="Integer" resultType="com.imooc.mybatis.entity.GoodsDetail">
select * from t_goods_detail where goods_id = #{value};
</select>
</mapper>
商品 goods.xml：核心<collection ></collection>标签【集合】
<?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">
<!-- namespace命名空间下 的id是唯一的 -->
<mapper namespace="goods">
<!-- 一对多关联查询 -->
<!--
resultMap 可用于说明一对多或者多对一的映射逻辑
id 是resultMap属性引用的标志(关联select标签与resultMap标签)
type 指向One的实体(Goods)
-->
<resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
<!-- 映射goods对象的主建goods_id字段
column-goods表的goods_id字段，property-goods类的goodsId属性
-->
<id column="goods_id" property="goodsId"/>
<!--
collection的含义是：
在select * from t_goods limit 0,2 得到结果后，对所有Goods对象遍历得到goods_id字段值，
并代入到goodsDetail命名空间的findByGoodsId的SQL中执行查询，
将得到的 商品详情 集合赋值给 goodsDetails List对象。
goods类的关联集合 List<GoodsDetail>
property="goodsDetails"：为goods类的集合属性
select="gxxx"：为goods_detail.xml的selectByGoodsId
column="goods_id"：两表的关联外键(列)
-->
<collection property="goodsDetails" select="goodsDetail.selectByGoodsId" column="goods_id"/>
</resultMap>
<select id="selectOneToMany" resultMap="rmGoods1">
select * from t_goods limit 0,1
</select>
<!--结果映射： 使用DTO对象接收 多表关联查询返回的结果集-->
<resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
<!--goods表：设置主键-->
<id property="goods.goodsId" column="goods_id"/>
<!--goods表：除了id以外的字段-->
<result property="goods.title" column="title"/>
<result property="goods.subTitle" column="sub_title"/>
<result property="goods.originalCost" column="original_cost"/>
<result property="goods.currentPrice" column="current_price"/>
<result property="goods.discount" column="discount"/>
<result property="goods.isFreeDelivery" column="is_free_delivery"/>
<result property="goods.categoryId" column="category_id"/>
<!--category表：DTO的属性-->
<result property="category.categoryId" column="category_id"/>
<result property="category.categoryName" column="category_name"/>
<result property="category.parentId" column="parent_id"/>
<result property="category.categoryLevel" column="category_level"/>
<result property="category.categoryOrder" column="category_order"/>
<result property="test" column="test"/>
</resultMap>
<select id="selectGoodsDTO" resultMap="rmGoods">
select g.*, c.*,'1' as test
from t_goods g, t_category c
where g.category_id = c.category_id
</select>
</mapper>四：测试用例
/**
* 一对多
* @throws Exception
*/
@Test
public void testOneToMany() throws Exception{
SqlSession sqlSession = null;
try{
sqlSession = MybatisUtils.openSession();
List<Goods> list = sqlSession.selectList("goods.selectOneToMany");
for (Goods goods : list){
System.out.println(goods.getTitle()+"---"+goods.getGoodsDetails().size());
}
}catch (Exception e){
throw e;
}finally {
MybatisUtils.closeSession(sqlSession);
}
}五：MybatisUtils工具类，创建全局唯一的SqlSessionFactory对象
package com.imooc.mybatis.utils;
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 java.io.IOException;
import java.io.Reader;
/**
* MybatisUtils工具类，创建全局唯一的SqlSessionFactory对象
* @author lihaisong
* @version 1.0
* @date 2021/5/14 12:24
*/
public class MybatisUtils {
//利用static关键字，属于类不属于对象，且全局唯一
private static SqlSessionFactory sqlSessionFactory = null;
//利用静态块在初始化类时实例化sqlSessionFactory
static {
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
//抛出初始化时出现的异常
throw new ExceptionInInitializerError(e);
}
}
/**
* openSession创建一个新的SqlSession对象
* @return
*/
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
/**
* 是否一个有效的SqlSession对象
* @param sqlSession
*/
public static void closeSession(SqlSession sqlSession){
if (sqlSession != null){
sqlSession.close();
}
}
}
六：数据库
/*
Navicat MySQL Data Transfer
Source Server : xampp
Source Server Version : 100140
Source Host : localhost:3306
Source Database : babytun
Target Server Type : MYSQL
Target Server Version : 100140
File Encoding : 65001
Date: 2021-05-15 15:15:36
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_category
-- ----------------------------
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category` (
`category_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '分类id',
`category_name` varchar(64) NOT NULL COMMENT '分类名称',
`parent_id` int(10) DEFAULT NULL COMMENT '上级分类',
`category_level` int(2) DEFAULT NULL COMMENT '分类的级别',
`category_order` int(6) DEFAULT '0' COMMENT '前端顺序 数字大的优先显示',
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='分类表';
-- ----------------------------
-- Records of t_category
-- ----------------------------
INSERT INTO `t_category` VALUES ('1', '母婴专区', null, '1', '100');
INSERT INTO `t_category` VALUES ('2', '美妆护肤', null, '1', '200');
INSERT INTO `t_category` VALUES ('3', '家装生活', null, '1', '300');
INSERT INTO `t_category` VALUES ('4', '食品营养', null, '1', '400');
INSERT INTO `t_category` VALUES ('5', '面膜', '2', '2', '0');
INSERT INTO `t_category` VALUES ('6', '面部护理', '2', '2', '0');
INSERT INTO `t_category` VALUES ('7', '孕产护肤', '2', '2', '0');
INSERT INTO `t_category` VALUES ('8', '纸尿裤', '1', '2', '0');
INSERT INTO `t_category` VALUES ('9', '彩妆', '2', '2', '0');
INSERT INTO `t_category` VALUES ('10', '身材护理', '2', '2', '0');
-- ----------------------------
-- Table structure for t_goods
-- ----------------------------
DROP TABLE IF EXISTS `t_goods`;
CREATE TABLE `t_goods` (
`goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
`title` varchar(128) NOT NULL COMMENT '标题',
`sub_title` varchar(128) NOT NULL COMMENT '子标题',
`original_cost` double(9,2) NOT NULL COMMENT '原始价格',
`current_price` double(9,2) NOT NULL COMMENT '当前价格',
`discount` double(5,2) DEFAULT NULL COMMENT '折扣率',
`is_free_delivery` int(1) DEFAULT '0' COMMENT '是否包邮 1包邮 0不包邮',
`category_id` int(10) NOT NULL COMMENT '分类编号',
PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COMMENT='商品表';
-- ----------------------------
-- Records of t_goods
-- ----------------------------
INSERT INTO `t_goods` VALUES ('16', '数学15', '这个是⾯试专题概要', '9900.00', '500.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('17', '数学16', '这个是⾯试专题概要', '9900.00', '632.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('18', '数学17', '这个是⾯试专题概要', '635.00', '600.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('20', '测试--xml-插入数据2', '这个是⾯试专题概要', '635.00', '555.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('21', '测试--xml-插入数据3', '这个是⾯试专题概要', '635.00', '588.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('22', '测试--xml-插入数据4', '这个是⾯试专题概要', '635.00', '566.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('23', '测试--xml-插入数据5', '这个是⾯试专题概要', '635.00', '577.00', '0.44', '0', '2');
INSERT INTO `t_goods` VALUES ('24', '小滴课堂1', '不偶像', '400.00', '356.00', '0.44', '0', '1');
INSERT INTO `t_goods` VALUES ('25', '小滴课堂23', '不偶像2', '700.00', '666.00', '0.44', '0', '3');
INSERT INTO `t_goods` VALUES ('26', '小滴课堂155', '不偶像', '400.00', '388.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('27', '小滴课堂26', '不偶像2', '700.00', '635.00', '0.44', '0', '4');
INSERT INTO `t_goods` VALUES ('28', '测试商品', '测试子标题', '200.00', '100.00', '0.50', '1', '3');
INSERT INTO `t_goods` VALUES ('30', '测试商品', '测试子标题', '200.00', '100.00', '0.50', '1', '3');
INSERT INTO `t_goods` VALUES ('31', '测试商品', '测试子标题', '200.00', '100.00', '0.50', '1', '3');
-- ----------------------------
-- Table structure for t_goods_detail
-- ----------------------------
DROP TABLE IF EXISTS `t_goods_detail`;
CREATE TABLE `t_goods_detail` (
`gd_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '详情id',
`goods_id` int(10) NOT NULL COMMENT '商品id',
`gd_pic_url` varchar(128) NOT NULL COMMENT '商品图片',
`gd_order` int(6) NOT NULL COMMENT '排序',
PRIMARY KEY (`gd_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COMMENT='商品详情表';
-- ----------------------------
-- Records of t_goods_detail
-- ----------------------------
INSERT INTO `t_goods_detail` VALUES ('1', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('2', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('3', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('4', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '1');
INSERT INTO `t_goods_detail` VALUES ('5', '16', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('6', '17', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('7', '17', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('8', '26', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('9', '26', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
INSERT INTO `t_goods_detail` VALUES ('10', '26', 'http://www.xiongsongedu.com/uploads/20200604/02eee05ba47f465572b4d05b70899e7f.png', '2');
版权声明：本文为a898712940原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。