语法糖

  1. <sql id="colums">
  2.  person_id, name, gender, person_addr, birthday
  3. </sql>
  5. <select id="selectPersonAll" resultMap="">
  6.     select <include refid="colums" /> from person
  7. </select>
  1. // parameterType 可以使用别名，int 可以代替java.lang.Integer ,
  2. // map 可以代替 java.lang.Map , 其他自定义类，必须通过mybatis配置文件，配置别名使用
  4. <update id="update" parameterType="com.rl.model1.Person">
  5.     update person t
  6.   <set>
  7.     <if test="name != null">
  8.       t.name = #{name},
  9.     </if>
  10.     <if test="gender != null">
  11.       t.gender = #{gender},
  12.     </if>
  13.     <if test="person_addr != null">
  14.       t.person_addr = #{person_addr},
  15.     </if>
  16.   </set>
  17.   where t.person_id = #{personId}
  18. </update>
  20. <update id="updateWhere" parameterType="com.rl.model1.Person">
  21.   update person t set
  22.   t.name = #{name},
  23.   t.gender = #{gender}
  24.   t.person_addr = #{personAddr}
  25.   where t.person_id = #{personId}
  26. </update>
  1. <!-- mysql 删除不可以用别名 -->
  2. <delete id="enumerationDelete" parameterType="enumeration">
  3.     delete from enumeration_list where enumId = #{enumId}
  4. </delete>
  6.     <!-- 批量删除,只能少量数据,大数据会溢出 -->
  7. <delete id="enumerationDeleteMap" parameterType="Map">
  8.     delete from enumeration_list where enumId in
  9.     <foreach collection="ids" open="(" close=")" item="enumId" separator="," index="index">
  10.         #{enumId}
  11.     </foreach>
  12. </delete>
  1. <insert id="enumerationInsert" parameterType="enumeration">
  2.         <!--
  3.             selectKey 是做主键返回的
  4.             keyProperty 接受主键返回的属性
  5.             order: "AFTER" 是mysql中使用的 "BEFORE" 指的是顺序，生成主键的顺序 自增序列
  6.              resultType 返回主键的数据类型
  7.              生成主键的sql select LAST_INSERT_ID()
  8.          -->
  9.         <selectKey keyProperty="enumId" order="AFTER" resultType="java.lang.Integer">
  10.             <!-- 此处是mysql的写法，oracle中不一样 -->
  11.             select LAST_INSERT_ID()
  12.         </selectKey>
  13.         insert into enumeration_list (enumId, typeId, name, parentId)
  14.         values 
  15.     <foreach collection="enumList" separator="," item="enum">
  16.       (#{enum.enumId}, #{enum.typeId}, #{enum.name}, #{enum.parentId})
  17.     </foreach>
  18. </insert>
  20. <insert id="enumerationInsert" parameterType="enumeration">
  21.         <!--
  22.             selectKey 是做主键返回的
  23.             keyProperty 接受主键返回的属性
  24.             order: "AFTER" 是mysql中使用的 "BEFORE" 指的是顺序，生成主键的顺序 自增序列
  25.              resultType 返回主键的数据类型
  26.              生成主键的sql select LAST_INSERT_ID()
  27.          -->
  28.         <selectKey keyProperty="enumId" order="AFTER" resultType="java.lang.Integer">
  29.             <!-- 此处是mysql的写法，oracle中不一样 -->
  30.             select LAST_INSERT_ID()
  31.         </selectKey>
  32.         insert into enumeration_list (enumId, typeId, name, parentId)
  33.         value(#{enumId}, #{typeId}, #{name}, #{parentId})
  34. </insert>
  1. ### 转义语法 ###
  2. <![CDATA[
  3.     select * from user u where u.datetime > #{date}
  4. ]]>
  6. ### parameterType的用法，可以是包装类型与任意Class，Map , 通过getUserId方法获取值的 ### 
  7. <select id="userSelectOne" parameterType="com.lijunyang.model.user" resultMap="user">
  8. <![CDATA[
  9.     select * from user u where u.datetime > #{datatime}
  10. ]]>
  11. </select>
  13. <select id="userSelectOne" parameterType="java.util.Map" resultMap="user">
  14.   select * from PersonTest2 p where p.name like '%${name}%'
  15. </select>
  17. <select id="enumerationSelectMap" parameterType="java.util.Map" resultMap="enumeration">
  18.         select * from enumeration_list e
  19.         <where>
  20.             <if test="enumId != null">
  21.                 e.enumId = #{enumId}
  22.             </if>
  23.             <if test="typeId != null">
  24.                 and e.typeId = #{typeId}
  25.             </if>
  26.             <if test="name != null">
  27.                 and e.name = #{name}
  28.             </if>
  29.             <if test="parentId != null">
  30.                 and e.parentId = #{parentId}
  31.             </if>
  32.         </where>
  33. </select>
  35. <select id="enumerationMap" parameterType="Map">
  36.     select * from enumeration_list where enumId in
  37.     <foreach collection="ids" open="(" close=")" item="enumId" separator="," index="index">
  38.         #{enumId}
  39.     </foreach>
  40. </select>

一对一

一个班主任只属于一个班级，一个班级也只能有一个班主任
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1

classObj = SELECT FROM class WHERE c_id = 1;
SELECT FROM teacher WHERE t_id = 1; //1 是上一个查询得到的teacher_id的值

表设计,使用MYSQL

  1. CREATE TABLE IF NOT EXISTS `class_list` (
  2.     `c_id` int(18) UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     `c_name` varchar(255) NOT NULL,
  4.     `t_id` int(18) UNSIGNED NOT NULL,
  5.     PRIMARY KEY (`c_id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  8. CREATE TABLE IF NOT EXISTS `teacher_list` (
  9.     `t_id` int(18) UNSIGNED NOT NULL AUTO_INCREMENT,
  10.     `t_name` varchar(255) NOT NULL,
  11.     PRIMARY KEY (`t_id`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  14. SET FOREIGN_KEY_CHECKS=0;
  16. alter table class_list add constraint `FK_TEACHERID` foreign key(`t_id`) references teacher_list(`t_id`);
  18. SET FOREIGN_KEY_CHECKS=1;
  1. <?xml version="1.0" encoding="UTF-8" ?> 
  2. <!DOCTYPE mapper    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"    
  3.     "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 
  4. <!--  为这个mapper指定一个唯一的namespace，namespace的值习惯上设置成包名+sql映射文件名，这样保证了namespace的值是唯一的-->
  5. <mapper namespace="com.yc.mybatis.test.classMapper">
  7.         <!-- 
  8.          方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
  9.                  封装联表查询的数据(去除重复的数据)
  10.          select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1
  11.      -->
  13.     <select id="getClass" parameterType="int" resultMap="getClassMap">
  14.         select * from class c, teacher t  where c.teacher_id = t.t_id and c.teacher_id=#{id}
  15.     </select>
  17.     <!-- resultMap:映射实体类和字段之间的一一对应的关系 -->
  18.     <resultMap type="Classes" id="getClassMap">
  19.         <id property="id" column="c_id"/>   
  20.         <result property="name" column="c_name"/>
  21.         <association property="teacher" javaType="Teacher">   
  22.             <id property="id" column="t_id"/>
  23.             <result property="name" column="t_name"/>
  24.         </association>
  25.     </resultMap>
  27.      <!-- 
  28.          方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
  29.          SELECT * FROM class WHERE c_id=1;
  30.          SELECT * FROM teacher WHERE t_id=1   //1 是上一个查询得到的teacher_id的值
  31.          property:别名(属性名)    column：列名 -->
  32.           <!-- 把teacher的字段设置进去 -->
  33.     <select id="getClass1" parameterType="int" resultMap="getClassMap1">
  34.         select * from class where c_id=#{id}
  35.     </select>
  37.     <resultMap type="Classes" id="getClassMap1">
  38.         <id property="id" column="c_id"/>   
  39.         <result property="name" column="c_name"/>
  40.         <association property="teacher" column="teacher_id" select="getTeacher"/>   
  41.     </resultMap>
  42.     <select id="getTeacher" parameterType="int" resultType="Teacher">
  43.         select t_id id,t_name name from teacher where t_id =#{id}
  44.     </select>
  45. </mapper>
property 对象属性的名称
javaType 对象属性的类型
column 所对应的外键字段名称
select 使用另一个查询封装的结果

一对多 and 多对一

一个顾客对应多个订单，而一个订单只能对应一个客户。
一个订单对应多个商品
1表示当前的orderId

  1. CREATE TABLE `user` (
  2.   `userId` int(18) unsigned NOT NULL,
  3.   `userName` varchar(255) NOT NULL,
  4.   `password` varchar(255) NOT NULL,
  5.   `Telephone` varchar(255) DEFAULT NULL,
  6.   `email` varchar(255) DEFAULT '',
  7.   `createTime` datetime NOT NULL,
  8.   `updatePasswordTime` datetime NOT NULL,
  9.   PRIMARY KEY (`userId`),
  10.   UNIQUE KEY `userName` (`userName`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  13. CREATE TABLE `goods_list` (
  14.   `goodsId` int(18) unsigned NOT NULL AUTO_INCREMENT,
  15.   `goodsName` varchar(255) NOT NULL,
  16.   `goodsPrice` double(11,2) NOT NULL,
  17.   `goodsUnit` varchar(255) NOT NULL,
  18.   `goodsIntroduce` varchar(255) DEFAULT '',
  19.   `pathId` int(18) NOT NULL,
  20.   `classType` int(18) DEFAULT NULL,
  21.   PRIMARY KEY (`goodsId`)
  22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  24. CREATE TABLE IF NOT EXISTS `order_list` (
  25.     `orderId` int(18) UNSIGNED NOT NULL AUTO_INCREMENT,
  26.     `orderStartTime` datetime NOT NULL,
  27.     `orderEndTime` datetime,
  28.   `orderTotalPrice` double(11,2) NOT NUll,
  29.   `userId` int(18) UNSIGNED NOT NULL,
  30.   `orderGoodsListNum` int(18) UNSIGNED NOT NULL,
  31.     PRIMARY KEY (`orderId`)
  32. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  34. alter table order_list add constraint `FK_ORDERID` 
  35. foreign key(`userId`) references user(`userId`);
  37. CREATE TABLE `order_goods_list` (
  38.     `orderId` int(18) UNSIGNED NOT NULL,
  39.   `goodsId` int(18) UNSIGNED NOT NULL,
  40.   `goodsCount` int(18) UNSIGNED NOT NULL,
  41.   `goodsTotalPrice` double(11,2) NOT NULL
  42. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  44. alter table order_goods_list add constraint `FK_ORDERID_GOODS` 
  45. foreign key(`orderId`) references order_list(`orderId`);
  47. alter table order_goods_list add constraint `FK_GOODSID` 
  48. foreign key(`goodsId`) references goods_list(`goodsId`);
  1. select * from user;

image.png

  1. select * from goods_list;

image.png

  1. INSERT INTO `order_list` (orderStartTime, orderTotalPrice, userId, orderGoodsListNum) 
  2. VALUES ('2019-03-18 16:12:34', 10.0, 10010, 10);
  3. select * from order_goods_list ol;
  4. select * from order_goods_list ol where ol.orderId = 1;
  6. update order_list set orderTotalPrice = 310.00 where orderId = 1;
  7. update order_list set orderTotalPrice = 310.00, orderGoodsListNum = 30  where orderId = 1;

image.png

  1. INSERT INTO `order_goods_list` (orderId, goodsId, goodsCount, goodsTotalPrice) 
  2. VALUES (1, 29, 10, 1.0);
  4. INSERT INTO `order_goods_list` (orderId, goodsId, goodsCount, goodsTotalPrice) 
  5. VALUES (1, 30, 10, 10.0);
  7. INSERT INTO `order_goods_list` (orderId, goodsId, goodsCount, goodsTotalPrice) 
  8. VALUES (1, 31, 10, 10.0);
  10. INSERT INTO `order_goods_list` (orderId, goodsId, goodsCount, goodsTotalPrice) 
  11. VALUES (1, 32, 10, 10.0);
  13. select * from order_goods_list;
  14. select * from order_goods_list ogl where ogl.orderId = 1;
  15. select * from order_goods_list ol where ol.orderId = 1 limit 0,3;

image.png

image.png

  1. select * from order_list o, order_goods_list ol where o.orderId = 1 and o.orderId = ol.orderId;

image.png

  1. ## 包含 in
  2. select * from goods_list where goodsId in (29, 30, 31, 32);
  3. ## between and 在什么什么之间
  4. select * from goods_list where goodsId between 29 and 32;
  6. ## is 
  7. select * from goods where classType is (not) NULL；
  8. select * from goods_list where classType is not NUll;
  9. select * from goods_list where classType is NUll;

image.png

  1. #select distinct 字段名 from 表名；字段名表示要过滤重复记录的字段

image.png

  1. ## like % 
  2. ## % 表示匹配任意字符
  3. ## _ 表示匹配单个字符
  4. #### 如果需要查询带有 % 或 _ 的数据，由于 % 和 _ 是通配符，则需要使用 \ 进行转义 
  5. ## \% 表示 %，\_ 表示 _
  6. ## and(&&) 有时在查询时为了查询结果更加精确，需要多个限条件，这时就需要 and(&&) 来连接条件
  7. ## or(||) 有时在查询时，只需要数据满足某些条件中的某一个，这时就需要使用 or(||) 来连接条件
  8. select * from user where userName like 'lijunyang%';
  1. ### count()函数：统计记录条数 select count(记录) from 表名
  2. select count(userId) from user;
  4. ### sum()函数：计算表中某个字段值的总和，select sum(字段名) from 表名
  5. ### avg()函数：计算表中某个字段的平均值 select avg(字段名) from 表名 
  6. ### max()函数：返回表中某个字段中的最大值
  7. ### min()函数：返回表中某个字段中的最小值

image.png image.png

  1. ### 在对数据表中的数据进行统计时，需要将数据按照一定的特征分组统计，此时就需
  2. ### 要使用分组查询  select 查询内容 from 表名 group by 分组依据 [having表达式条件]
  3. ### select 查询内容 from 表名 order by 排序条件 asc/desc，asc表示升序 desc表示降序
  4. ### 分页查询 select 查询内容 from 表名 limit 偏移量m,记录数n
  6. where 与 having：
  7.     where 与 having关键字都用于设置条件表达式对查询结果进行过滤，区别是having后面可以跟聚合
  8.     函数，而where不能，通常having关键字都与group by 一起使用，表示对分组后的数据进行过滤

image.png

private List orders;

最终一对多select语句

分为几种查询方式：sql查询，一次性查出所有关联信息用下面的语句

  1. select * from order_list o, order_goods_list ogl, goods_list ol where 
  2. o.orderId = 1 and
  3. o.orderId = ogl.orderId and 
  4. ol.goodsId in 
  5. (select goodsId from order_goods_list where orderId = o.orderId) limit 0,3
  7. ## 查询表格型数据，与下面的面向对象型不一样

image.png

在实际工作中，配合ORM框架配置,如Mybatis等，查询要分为2次查询

  1. ## 首先查询订单信息
  2. select * from order_list where orderId = #{orderId}
  3. ## 在查询订单id关联的所有商品信息
  4. select * from order_goods_list ogl, goods_list ol where ogl.orderId = #{orderId} and ol.goodsId = ogl.goodsId
  5. ## 最好在将数据合并，就是一份面向对象结构的数据了

注意事项

注意，此种设计只为了学习使用，实际场景中可以使用冗余字段，将order_goods_list,包含goods_list的所有字段，形成冗余，这样只需要查询2张表即可

Goods 商品类 : 映射商品表

  1. package com.lijunyang.model;
  2. /**
  3.  * 商品类
  4.  *  商品名称
  5.  *  商品ID
  6.  *  商品价格
  7.  *  商品单位
  8.  *  商品介绍
  9.  *  商品图片路径ID
  10.  * */
  11. public class Goods {
  13.     private String goodsName;
  14.     private Integer goodsId;
  15.     private Double goodsPrice;
  16.     private String goodsUnit;
  17.     private String goodsIntroduce;
  18.     private Integer pathId;
  19.     private FileSavePath filePath;
  21.     private Integer classType;
  22.     private Enumeration enumeration;
  24.     public Integer getClassType() {
  25.         return classType;
  26.     }
  28.     public void setClassType(Integer classType) {
  29.         this.classType = classType;
  30.     }
  32.     public Enumeration getEnumeration() {
  33.         return enumeration;
  34.     }
  36.     public void setEnumeration(Enumeration enumeration) {
  37.         this.enumeration = enumeration;
  38.     }
  40.     public Integer getPathId() {
  41.         return pathId;
  42.     }
  44.     public void setPathId(Integer pathId) {
  45.         this.pathId = pathId;
  46.     }
  48.     public String getGoodsName() {
  49.         return goodsName;
  50.     }
  52.     public void setGoodsName(String goodsName) {
  53.         this.goodsName = goodsName;
  54.     }
  56.     public Integer getGoodsId() {
  57.         return goodsId;
  58.     }
  60.     public void setGoodsId(Integer goodsId) {
  61.         this.goodsId = goodsId;
  62.     }
  64.     public Double getGoodsPrice() {
  65.         return goodsPrice;
  66.     }
  68.     public void setGoodsPrice(Double goodsPrice) {
  69.         this.goodsPrice = goodsPrice;
  70.     }
  72.     public String getGoodsUnit() {
  73.         return goodsUnit;
  74.     }
  76.     public void setGoodsUnit(String goodsUnit) {
  77.         this.goodsUnit = goodsUnit;
  78.     }
  80.     public String getGoodsIntroduce() {
  81.         return goodsIntroduce;
  82.     }
  84.     public void setGoodsIntroduce(String goodsIntroduce) {
  85.         this.goodsIntroduce = goodsIntroduce;
  86.     }
  88.     public FileSavePath getFilePath() {
  89.         return filePath;
  90.     }
  92.     public void setFilePath(FileSavePath filePath) {
  93.         this.filePath = filePath;
  94.     }
  96.     @Override
  97.     public String toString() {
  98.         return "Goods{" +
  99.                 "goodsName='" + goodsName + '\'' +
  100.                 ", goodsId=" + goodsId +
  101.                 ", goodsPrice=" + goodsPrice +
  102.                 ", goodsUnit='" + goodsUnit + '\'' +
  103.                 ", goodsIntroduce='" + goodsIntroduce + '\'' +
  104.                 ", pathId=" + pathId +
  105.                 ", filePath=" + filePath +
  106.                 ", classType=" + classType +
  107.                 ", enumeration=\n" + enumeration +
  108.                 '}';
  109.     }
  110. }

OrderGoods类 订单商品表 : 映射订单商品表

  1. package com.lijunyang.model;
  3. import java.util.List;
  5. /**
  6.  * 订单商品类
  7.  *  订单ID
  8.  *  商品
  9.  *  商品数量
  10.  *  商品价格
  11.  * */
  13. public class OrderGoods {
  14.     private Integer orderId;
  15.     private List<Goods> goodsList;
  16.     private Integer goodsCount;
  17.     private Double goodsTotalPrice;
  19.     public Integer getOrderId() {
  20.         return orderId;
  21.     }
  23.     public void setOrderId(Integer orderId) {
  24.         this.orderId = orderId;
  25.     }
  27.     public List<Goods> getGoodsList() {
  28.         return goodsList;
  29.     }
  31.     public void setGoodsList(List<Goods> goodsList) {
  32.         this.goodsList = goodsList;
  33.     }
  35.     public Integer getGoodsCount() {
  36.         return goodsCount;
  37.     }
  39.     public void setGoodsCount(Integer goodsCount) {
  40.         this.goodsCount = goodsCount;
  41.     }
  43.     public Double getGoodsTotalPrice() {
  44.         return goodsTotalPrice;
  45.     }
  47.     public void setGoodsTotalPrice(Double goodsTotalPrice) {
  48.         this.goodsTotalPrice = goodsTotalPrice;
  49.     }
  51.     @Override
  52.     public String toString() {
  53.         return "OrderGoods{" +
  54.                 "orderId=" + orderId +
  55.                 ", goodsList=" + goodsList +
  56.                 ", goodsCount=" + goodsCount +
  57.                 ", goodsTotalPrice=" + goodsTotalPrice +
  58.                 '}';
  59.     }
  60. }

OrderGoods类的mapper

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.lijunyang.mapper.orderGoodsMapper">
  7.     <resultMap type="orderGoods" id="orderGoods">
  8.         <id column="orderId" property="orderId" />
  9.         <result column="goodsCount" property="goodsCount"/>
  10.         <result column="goodsTotalPrice" property="goodsTotalPrice"/>
  11.         <collection property="goodsList" javaType="ArrayList" ofType="com.lijunyang.model.Goods">
  12.             <id column="goodsId" property="goodsId" />
  13.             <result column="goodsName" property="goodsName"/>
  14.             <result column="goodsPrice" property="goodsPrice"/>
  15.             <result column="goodsUnit" property="goodsUnit"/>
  16.             <result column="goodsIntroduce" property="goodsIntroduce"/>
  17.             <result column="pathId" property="pathId"/>
  18.             <result column="classType" property="classType"/>
  19.             <association property="filePath" javaType="com.lijunyang.model.FileSavePath">
  20.                 <result column="fileId" property="fileId" />
  21.                 <result column="path" property="path"/>
  22.             </association>
  23.             <association property="enumeration" javaType="com.lijunyang.model.Enumeration">
  24.                 <result column="enumId" property="enumId" />
  25.                 <result column="typeId" property="typeId"/>
  26.                 <result column="parentId" property="parentId"/>
  27.                 <result column="name" property="name"/>
  28.             </association>
  29.          </collection>
  30.     </resultMap>
  33.     <select id="orderGoodsSelectMap" parameterType="java.util.Map" resultMap="orderGoods">
  34.         select * from goods_list gl, order_goods_list ogl ,fileSavePath f, enumeration_list e
  35.         <where>
  36.             ogl.orderId = #{orderId}
  37.             and ogl.goodsId = gl.goodsId
  38.             and gl.pathId = f.fileId
  39.             and e.enumId = gl.classType
  40.         </where>
  41.     </select>
  43.     <select id="orderGoodsSelectPagesMap" parameterType="java.util.Map" resultMap="orderGoods">
  44.         select * from goods_list gl, order_goods_list ogl ,fileSavePath f, enumeration_list e
  45.         <where>
  46.             ogl.orderId = #{orderId}
  47.             and ogl.goodsId = gl.goodsId
  48.             and gl.pathId = f.fileId
  49.             and e.enumId = gl.classType
  50.         </where>
  51.         limit #{page},#{pageSize}
  52.     </select>
  54.     <select id="orderGoodsSelectPagesThreeMap" parameterType="java.util.Map" resultMap="orderGoods">
  55.         select * from goods_list gl, order_goods_list ogl ,fileSavePath f, enumeration_list e
  56.         <where>
  57.             ogl.orderId = #{orderId}
  58.             and ogl.goodsId = gl.goodsId
  59.             and gl.pathId = f.fileId
  60.             and e.enumId = gl.classType
  61.         </where>
  62.         limit 0, 3
  63.     </select>
  65. </mapper>

OrderGoods junit 单元测试

  1. package com.lijunyang.test;
  3. import com.lijunyang.model.Order;
  4. import com.lijunyang.model.OrderGoods;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.Before;
  10. import org.junit.Test;
  12. import java.io.InputStream;
  13. import java.util.HashMap;
  14. import java.util.List;
  15. import java.util.Map;
  17. public class OrderGoodsDaoTest {
  19.     SqlSessionFactory sessionFactory;
  21.     @Before
  22.     public void setUp() throws Exception {
  23.         InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
  24.         sessionFactory = new SqlSessionFactoryBuilder().build(in);
  25.         //注册接口类
  26.         sessionFactory.getConfiguration().addMapper(Order.class);
  27.     }
  29.     @Test
  30.     public void select() {
  31.         // 创建SqlSession
  32.         SqlSession session = sessionFactory.openSession();
  33.         try {
  34.             Map<String, Object> map = new HashMap<String, Object>();
  35.             map.put("orderId", 1);
  36.             List<OrderGoods> list = session.selectList("com.lijunyang.mapper.orderGoodsMapper.orderGoodsSelectMap", map);
  37.             for (OrderGoods o : list) {
  38.                 System.out.println(o);
  39.             }
  40.         } finally {
  41.             session.close();
  42.         }
  43.     }
  45.     @Test
  46.     public void selectPages() {
  47.         // 创建SqlSession
  48.         SqlSession session = sessionFactory.openSession();
  49.         try {
  50.             Map<String, Object> map = new HashMap<String, Object>();
  51.             map.put("orderId", 1);
  52.             map.put("page", 0);
  53.             map.put("pageSize", 3);
  54.             List<OrderGoods> list = session.selectList("com.lijunyang.mapper.orderGoodsMapper.orderGoodsSelectPagesMap", map);
  55.             for (OrderGoods o : list) {
  56.                 System.out.println(o);
  57.             }
  58.         } finally {
  59.             session.close();
  60.         }
  61.     }
  62. }

Order类 订单类 : 映射订单表

  1. package com.lijunyang.model;
  3. import java.util.Date;
  4. import java.util.List;
  6. /**
  7.  * 订单商品类
  8.  *  订单ID
  9.  *  用户ID
  10.  *  订单开始时间
  11.  *  确认订单时间
  12.  *  商品总数量
  13.  *  商品总价格
  15.  当用户点击下单时，orderStartTime 赋值，此时允许用户修改订单
  16.  当用户点击确认订单时，orderEndTime 赋值，此时不在允许用户修改订单
  17.  可以通过userId获取到用户未确认的订单，只允许有一个未确认订单
  19.  * */
  21. public class Order {
  22.     private Integer orderId;
  23.     private Integer userId;
  24.     private Date orderStartTime;
  25.     private Date orderEndTime;
  26.     private Integer orderGoodsListNum;
  27.     private Double orderTotalPrice;
  29.     private OrderGoods OrderGoods;
  31.     public Integer getOrderId() {
  32.         return orderId;
  33.     }
  35.     public void setOrderId(Integer orderId) {
  36.         this.orderId = orderId;
  37.     }
  39.     public Integer getUserId() {
  40.         return userId;
  41.     }
  43.     public void setUserId(Integer userId) {
  44.         this.userId = userId;
  45.     }
  47.     public Date getOrderStartTime() {
  48.         return orderStartTime;
  49.     }
  51.     public void setOrderStartTime(Date orderStartTime) {
  52.         this.orderStartTime = orderStartTime;
  53.     }
  55.     public Date getOrderEndTime() {
  56.         return orderEndTime;
  57.     }
  59.     public void setOrderEndTime(Date orderEndTime) {
  60.         this.orderEndTime = orderEndTime;
  61.     }
  63.     public Integer getOrderGoodsListNum() {
  64.         return orderGoodsListNum;
  65.     }
  67.     public void setOrderGoodsListNum(Integer orderGoodsListNum) {
  68.         this.orderGoodsListNum = orderGoodsListNum;
  69.     }
  71.     public Double getOrderTotalPrice() {
  72.         return orderTotalPrice;
  73.     }
  75.     public void setOrderTotalPrice(Double orderTotalPrice) {
  76.         this.orderTotalPrice = orderTotalPrice;
  77.     }
  79.     public com.lijunyang.model.OrderGoods getOrderGoods() {
  80.         return OrderGoods;
  81.     }
  83.     public void setOrderGoods(com.lijunyang.model.OrderGoods orderGoods) {
  84.         OrderGoods = orderGoods;
  85.     }
  87.     @Override
  88.     public String toString() {
  89.         return "Order{" +
  90.                 "orderId=" + orderId +
  91.                 ", userId=" + userId +
  92.                 ", orderStartTime=" + orderStartTime +
  93.                 ", orderEndTime=" + orderEndTime +
  94.                 ", orderGoodsListNum=" + orderGoodsListNum +
  95.                 ", orderTotalPrice=" + orderTotalPrice +
  96.                 ", OrderGoods=" + OrderGoods +
  97.                 '}';
  98.     }
  99. }

Order类的mapper

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.lijunyang.mapper.orderMapper">
  7.     <resultMap type="order" id="order">
  8.         <id column="orderId" property="orderId" />
  9.         <result column="userId" property="userId"/>
  10.         <result column="orderStartTime" property="orderStartTime"/>
  11.         <result column="orderEndTime" property="orderEndTime"/>
  12.         <result column="orderGoodsListNum" property="orderGoodsListNum"/>
  13.         <result column="orderTotalPrice" property="orderTotalPrice"/>
  14.         <association property="orderGoods" 
  15.         javaType="com.lijunyang.model.OrderGoods" 
  16.         column="orderId" 
  17.         select="com.lijunyang.mapper.orderGoodsMapper.orderGoodsSelectPagesThreeMap"/>
  18.     </resultMap>
  21.     <select id="orderIdSelectMap" parameterType="java.util.Map" resultMap="order">
  22.         select * from order_list o
  23.         <where>
  24.             o.orderId = #{orderId}
  25.         </where>
  26.     </select>
  28.     <select id="orderSelectMap" parameterType="java.util.Map" resultMap="order">
  29.         select * from order_list o
  30.     </select>
  33. </mapper>

Order junit 单元测试

  1. package com.lijunyang.test;
  3. import com.lijunyang.model.Enumeration;
  4. import com.lijunyang.model.Order;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.Before;
  10. import org.junit.Test;
  12. import java.io.InputStream;
  13. import java.util.HashMap;
  14. import java.util.List;
  15. import java.util.Map;
  17. public class OrderDaoTest {
  19.     SqlSessionFactory sessionFactory;
  21.     @Before
  22.     public void setUp() throws Exception {
  23.         InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
  24.         sessionFactory = new SqlSessionFactoryBuilder().build(in);
  25.         //注册接口类
  26.         sessionFactory.getConfiguration().addMapper(Order.class);
  27.     }
  29.     @Test
  30.     public void select() {
  31.         // 创建SqlSession
  32.         SqlSession session = sessionFactory.openSession();
  33.         try {
  34.             Map<String, Object> map = new HashMap<String, Object>();
  35.             map.put("orderId", 1);
  36.             List<Order> list = session.selectList("com.lijunyang.mapper.orderMapper.orderSelectMap", map);
  37.             for (Order o : list) {
  38.                 System.out.println(o);
  39.             }
  40.         } finally {
  41.             session.close();
  42.         }
  43.     }
  45. }

多对多

上面的表设计中，商品表，订单商品表，订单表
其中商品表与订单表的关系是， 多对多，因为，订单商品表与商品表在结构上是多对多
订单表与订单商品表的关系是，一对多
通过中间表，订单商品表维护他们之间的关系

商品表，多个商品会重复对应多条订单
image.png

订单表，这里只有一条，假设有多条的情况，如何和商品表相互对应，多条订单会重复对应多个商品
image.png

中间表（关系表）
image.png

还有一个例子是

多对多关系处理：
通过学生选课了解多对多问题的处理：
mybatis 一对一，一对多，多对多 - 图16

在多对多中在一个表中添加一个字段就行不通了，所以处理多对多表问题时，就要考虑建立关系表了
例：
学生表：mybatis 一对一，一对多，多对多 - 图17

课程表：mybatis 一对一，一对多，多对多 - 图18

关系表：mybatis 一对一，一对多，多对多 - 图19**

注：所以对于多对多表，通过关系表就建立起了两张表的联系！多对多表时建立主外键后，要先删除约束表内容再删除主表内容