一、增加
1. 设置插入数据后自动返回主键
主键返回策略只适用于单个对象的插入;
主键返回策略:
(1) 单独参数
直接用set方法
(2) map
对象名.id
(3) 多参数列表
对象名.id(eg:keyProperty="attr.id")
eg1: 单独参数
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.mapper; import java.util.List; import java.util.Map; import com.atguigu.bean.T_MALL_PRODUCT; public interface SpuMapper { void insert_spu(T_MALL_PRODUCT spu); void insert_images(Map<Object, Object> map); List<T_MALL_PRODUCT> select_spu_list(Map<Object, Object> map); }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<!-- useGeneratedKeys:主键返回策略,默认为false,如果设置为true,则MyBatis插入后的主键返回回去 keyColumn:数据库生成的主键字段名 keyProperty:实体属性名(指所插入的对象的属性名)--> <insert useGeneratedKeys="true" keyColumn="id" keyProperty="id" id="insert_spu" parameterType="com.atguigu.bean.T_MALL_PRODUCT"> insert into t_mall_product ( shp_mch, shp_tp, flbh1, flbh2, pp_id, shp_msh ) values ( #{shp_mch}, #{shp_tp}, #{flbh1}, #{flbh2}, #{pp_id}, #{shp_msh} ) </insert>
eg2: 多参数对象时,需要指定keyProperty="attr.id",这样MyBatis才知道应该将返回的主键封装给哪个对象
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.mapper; import java.util.List; import org.apache.ibatis.annotations.Param; import com.atguigu.bean.OBJECT_T_MALL_ATTR; //import com.atguigu.bean.OBJECT_T_MALL_ATTR; import com.atguigu.bean.T_MALL_VALUE; public interface AttrMapper { /** * 保存商品属性 * @param flbh2 * @param attr:主键返回策略只适用于单个对象的插入,所以这里需要封装成OBJECT_T_MALL_ATTR对象 * 这里的insert_attr有两个参数,需要用 @Param,这样主键生成策略中(通过指定keyProperty="attr.id") * 才知道应该将主键封装给哪个参数 */ void insert_attr(@Param("flbh2") int flbh2, @Param("attr") OBJECT_T_MALL_ATTR attr); /** * 插入属性值 * @param attr_id * @param list_value */ void insert_values(@Param("attr_id") int attr_id, @Param("list_value") List<T_MALL_VALUE> list_value); List<OBJECT_T_MALL_ATTR> select_attr_list(int flbh2); }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.atguigu.mapper.AttrMapper"> <select id="select_attr_list" parameterType="int" resultMap="select_attr_list_map"> select attr.id as attr_id ,attr.*,val.id as val_id ,val.* from t_mall_attr attr , t_mall_value val where attr.id = val.shxm_id and attr.flbh2 = #{flbh2} </select> <!-- autoMapping:属性自动映射 --> <resultMap type="com.atguigu.bean.OBJECT_T_MALL_ATTR" id="select_attr_list_map" autoMapping="true"> <result column="attr_id" property="id" /> <collection property="list_value" ofType="com.atguigu.bean.T_MALL_VALUE" autoMapping="true"> <!-- 指定主键映射,其他属性自动映射 --> <result column="val_id" property="id" /> </collection> <!-- 如果里面还有对象类型的数据 --> <!-- <association property=""></association> --> <!-- 如果还有集合类型的数据 --> <!-- <collection property=""></collection> --> </resultMap> <insert id="insert_attr" useGeneratedKeys="true" keyColumn="id" keyProperty="attr.id"> insert into t_mall_attr( shxm_mch, flbh2 ) values ( #{attr.shxm_mch}, #{flbh2} ) </insert> <insert id="insert_values"> insert into t_mall_value( shxzh, shxm_id, shxzh_mch ) values <foreach collection="list_value" item="val" separator=","> ( #{val.shxzh}, #{attr_id}, #{val.shxzh_mch} ) </foreach> </insert> </mapper>
2. 批量插入
我传入的参数为map,参数封装:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
// 根据spu的主键,批量插入spu图片 Map<Object, Object> map = new HashMap<Object, Object>(); map.put("shp_id", spu.getId()); map.put("list_image", list_image); spuMapper.insert_images(map);
sql:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<!-- 插入商品图片 --> <insert id="insert_images" parameterType="Map"> insert into t_mall_product_image(shp_id,url) values <foreach collection="list_image" item="image" separator=","> (#{shp_id},#{image}) </foreach> </insert>
3.多重对象的同步表单参数提交
A页面表达式ognl(object gram navigation language)
List_attr[0].list_value[0].shxzh 对象.属性.对象的方式去找到对应的属性
B mvc如何封装?
jsp的表单页面:页面上的级联属性名或者对象名必须在java中要有set方法方法,表单提交时才能自动封装上表单的数据。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@page isELIgnored="false" %> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%> <%String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+request.getContextPath()+"/";%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <base href="<%=basePath %>"> <script type="text/javascript" src="js/jquery-1.7.2.min.js"></script> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <script type="text/javascript"> function b(){} </script> <title>硅谷商城</title> </head> <body> 添加商品属性 <hr> <form action="attr_add.do"> <input type="text" value="${flbh2}" name="flbh2"/> <table border="1" width="800px"> <tr><td>属性名:<input type="text" name="list_attr[0].shxm_mch"/></td><td></td><td>添加属性值</td></tr> <tr><td>属性值:<input type="text" name="list_attr[0].list_value[0].shxzh"/></td><td>单位:<input type="text" name="list_attr[0].list_value[0].shxzh_mch"/></td><td>删除</td></tr> <tr><td>属性值:<input type="text" name="list_attr[0].list_value[1].shxzh"/></td><td>单位:<input type="text" name="list_attr[0].list_value[1].shxzh_mch"/></td><td>删除</td></tr> </table> <table border="1" width="800px"> <tr><td>属性名:<input type="text" name="list_attr[1].shxm_mch"/></td><td></td><td>添加属性值</td></tr> <tr><td>属性值:<input type="text" name="list_attr[1].list_value[0].shxzh"/></td><td>单位:<input type="text" name="list_attr[1].list_value[0].shxzh_mch"/></td><td>删除</td></tr> <tr><td>属性值:<input type="text" name="list_attr[1].list_value[1].shxzh"/></td><td>单位:<input type="text" name="list_attr[1].list_value[1].shxzh_mch"/></td><td>删除</td></tr> </table> 添加:<input type="submit" value="提交"/> </form> </body> </html>
Controller接收方法:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/** * 商品属性提交 (双重集合提交) * 页面表达式ognl(object gram navigation language) * List_attr[0].list_value[0].shxzh * @param flbh2 * @param list_attr * @return */ @RequestMapping("attr_add") public ModelAndView attr_add(int flbh2, MODEL_T_MALL_ATTR list_attr) { // 保存属性 attrServiceInf.save_attr(flbh2, list_attr.getList_attr()); ModelAndView mv = new ModelAndView("redirect:/index.do"); // mv.addObject("flbh2", flbh2); mv.addObject("url","goto_attr_add.do?flbh2="+flbh2); mv.addObject("title","添加属性"); return mv; }
service中的方法:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/** * 保存商品属性 */ @Override public void save_attr(int flbh2, List<OBJECT_T_MALL_ATTR> list_attr) { for (int i = 0; i < list_attr.size(); i++) { // 插入属性,返回主键 OBJECT_T_MALL_ATTR attr = list_attr.get(i); attrMapper.insert_attr(flbh2, attr); // 获得返回主键批量插入属性值 attrMapper.insert_values(attr.getId(), attr.getList_value()); } }
实体:
属性值实体:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.util.Date; public class T_MALL_VALUE { private int id; private String shxzh; private String shfqy; private int shxm_id; private String shxzh_mch; private Date chjshj; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getShxzh() { return shxzh; } public void setShxzh(String shxzh) { this.shxzh = shxzh; } public String getShfqy() { return shfqy; } public void setShfqy(String shfqy) { this.shfqy = shfqy; } public int getShxm_id() { return shxm_id; } public void setShxm_id(int shxm_id) { this.shxm_id = shxm_id; } public String getShxzh_mch() { return shxzh_mch; } public void setShxzh_mch(String shxzh_mch) { this.shxzh_mch = shxzh_mch; } public Date getChjshj() { return chjshj; } public void setChjshj(Date chjshj) { this.chjshj = chjshj; } }
属性实体:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.util.Date; public class T_MALL_ATTR { private int id; private String shxm_mch; private String shfqy; private int flbh2; private Date chjshj; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getShxm_mch() { return shxm_mch; } public void setShxm_mch(String shxm_mch) { this.shxm_mch = shxm_mch; } public String getShfqy() { return shfqy; } public void setShfqy(String shfqy) { this.shfqy = shfqy; } public int getFlbh2() { return flbh2; } public void setFlbh2(int flbh2) { this.flbh2 = flbh2; } public Date getChjshj() { return chjshj; } public void setChjshj(Date chjshj) { this.chjshj = chjshj; } }
商品属性包装对象:将商品的属性和属性值封装为一个对象
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.util.List; /** * 商品属性包装对象 * @author doublening * */ public class OBJECT_T_MALL_ATTR extends T_MALL_ATTR { //属性值 private List<T_MALL_VALUE> list_value; public List<T_MALL_VALUE> getList_value() { return list_value; } public void setList_value(List<T_MALL_VALUE> list_value) { this.list_value = list_value; } }
商品属性模型实体:这个实体是因为Controller不能直接接收List<OBJECT_T_MALL_ATTR>集合参数而建立的
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.util.List; /** * 这个对象是对List<OBJECT_T_MALL_ATTR> list_attr的一个封装 * 因为后端不能直接接收前端传递的list_attr * @author doublening * */ public class MODEL_T_MALL_ATTR { private List<OBJECT_T_MALL_ATTR> list_attr; public List<OBJECT_T_MALL_ATTR> getList_attr() { return list_attr; } public void setList_attr(List<OBJECT_T_MALL_ATTR> list_attr) { this.list_attr = list_attr; } }
二、删除
三、改
四、查
1、查询有多层嵌套的实体
eg1 :
sql:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<select id="select_attr_list" parameterType="int" resultMap="select_attr_list_map"> select attr.id as attr_id ,attr.*,val.id as val_id ,val.* from t_mall_attr attr , t_mall_value val where attr.id = val.shxm_id and attr.flbh2 = #{flbh2} </select> <!-- autoMapping:属性自动映射 --> <resultMap type="com.atguigu.bean.OBJECT_T_MALL_ATTR" id="select_attr_list_map" autoMapping="true"> <result column="attr_id" property="id" /> <collection property="list_value" ofType="com.atguigu.bean.T_MALL_VALUE" autoMapping="true"> <!-- 指定主键映射,其他属性自动映射 --> <result column="val_id" property="id" /> </collection> <!-- 如果里面还有对象类型的数据 --> <!-- <association property=""></association> --> <!-- 如果还有集合类型的数据 --> <!-- <collection property=""></collection> --> </resultMap>
dao层:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
List<OBJECT_T_MALL_ATTR> select_attr_list(int flbh2);
实体:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public class OBJECT_T_MALL_ATTR extends T_MALL_ATTR { //属性值 private List<T_MALL_VALUE> list_value; public List<T_MALL_VALUE> getList_value() { return list_value; } public void setList_value(List<T_MALL_VALUE> list_value) { this.list_value = list_value; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.util.Date; public class T_MALL_ATTR { private int id; private String shxm_mch; private String shfqy; private int flbh2; private Date chjshj; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getShxm_mch() { return shxm_mch; } public void setShxm_mch(String shxm_mch) { this.shxm_mch = shxm_mch; } public String getShfqy() { return shfqy; } public void setShfqy(String shfqy) { this.shfqy = shfqy; } public int getFlbh2() { return flbh2; } public void setFlbh2(int flbh2) { this.flbh2 = flbh2; } public Date getChjshj() { return chjshj; } public void setChjshj(Date chjshj) { this.chjshj = chjshj; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.util.Date; public class T_MALL_VALUE { private int id; private String shxzh; private String shfqy; private int shxm_id; private String shxzh_mch; private Date chjshj; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getShxzh() { return shxzh; } public void setShxzh(String shxzh) { this.shxzh = shxzh; } public String getShfqy() { return shfqy; } public void setShfqy(String shfqy) { this.shfqy = shfqy; } public int getShxm_id() { return shxm_id; } public void setShxm_id(int shxm_id) { this.shxm_id = shxm_id; } public String getShxzh_mch() { return shxzh_mch; } public void setShxzh_mch(String shxzh_mch) { this.shxzh_mch = shxzh_mch; } public Date getChjshj() { return chjshj; } public void setChjshj(Date chjshj) { this.chjshj = chjshj; } }
eg2:
sql:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.atguigu.mapper.ListMapper"> <select id="select_list_by_attr" parameterType="Map" resultMap="select_list_by_flbh2_map"> SELECT spu.id as spu_id , spu.*,sku.id as sku_id ,sku.*,tm.id as tm_id ,tm.* FROM t_mall_product spu, t_mall_sku sku, t_mall_trade_mark tm WHERE spu.Id = sku.shp_id and spu.pp_id = tm.Id and spu.flbh2 = #{flbh2} ${subSql} </select> <select id="select_list_by_flbh2" parameterType="int" resultMap="select_list_by_flbh2_map"> SELECT spu.id as spu_id , spu.*,sku.id as sku_id ,sku.*,tm.id as tm_id ,tm.* FROM t_mall_product spu, t_mall_sku sku, t_mall_trade_mark tm WHERE spu.Id = sku.shp_id and spu.pp_id = tm.Id and spu.flbh2 = #{flbh2} </select> <resultMap type="com.atguigu.bean.OBJECT_T_MALL_SKU" id="select_list_by_flbh2_map" autoMapping="true"> <id column="sku_id" property="id" /> <association property="spu" javaType="com.atguigu.bean.T_MALL_PRODUCT" autoMapping="true"> <id column="spu_id" property="id" /> </association> <association property="tm" javaType="com.atguigu.bean.T_MALL_TRADE_MARK" autoMapping="true"> <id column="tm_id" property="id" /> </association> </resultMap> </mapper>
mapper:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
public interface ListMapper { List<OBJECT_T_MALL_SKU> select_list_by_flbh2(int flbh2); List<OBJECT_T_MALL_SKU> select_list_by_attr(HashMap<Object, Object> hashMap); }
实体:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; public class OBJECT_T_MALL_SKU extends T_MALL_SKU { private T_MALL_PRODUCT spu; private T_MALL_TRADE_MARK tm; public T_MALL_PRODUCT getSpu() { return spu; } public void setSpu(T_MALL_PRODUCT spu) { this.spu = spu; } public T_MALL_TRADE_MARK getTm() { return tm; } public void setTm(T_MALL_TRADE_MARK tm) { this.tm = tm; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.math.BigDecimal; import java.util.Date; public class T_MALL_SKU { private int id; private int shp_id; private int kc; private BigDecimal jg; private Date chjshj; private String sku_mch; private String kcdz; private long sku_xl; public long getSku_xl() { return sku_xl; } public void setSku_xl(long sku_xl) { this.sku_xl = sku_xl; } public String getKcdz() { return kcdz; } public void setKcdz(String kcdz) { this.kcdz = kcdz; } public int getId() { return id; } public void setId(int id) { this.id = id; } public int getShp_id() { return shp_id; } public void setShp_id(int shp_id) { this.shp_id = shp_id; } public int getKc() { return kc; } public void setKc(int kc) { this.kc = kc; } public Date getChjshj() { return chjshj; } public void setChjshj(Date chjshj) { this.chjshj = chjshj; } public String getSku_mch() { return sku_mch; } public void setSku_mch(String sku_mch) { this.sku_mch = sku_mch; } public BigDecimal getJg() { return jg; } public void setJg(BigDecimal jg) { this.jg = jg; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; import java.util.Date; public class T_MALL_PRODUCT { private int id; private String shp_mch; private String shp_tp; private int flbh1; private int flbh2; private int pp_id; private Date chjshj; private String shp_msh; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getShp_mch() { return shp_mch; } public void setShp_mch(String shp_mch) { this.shp_mch = shp_mch; } public String getShp_tp() { return shp_tp; } public void setShp_tp(String shp_tp) { this.shp_tp = shp_tp; } public int getFlbh1() { return flbh1; } public void setFlbh1(int flbh1) { this.flbh1 = flbh1; } public int getFlbh2() { return flbh2; } public void setFlbh2(int flbh2) { this.flbh2 = flbh2; } public int getPp_id() { return pp_id; } public void setPp_id(int pp_id) { this.pp_id = pp_id; } public Date getChjshj() { return chjshj; } public void setChjshj(Date chjshj) { this.chjshj = chjshj; } public String getShp_msh() { return shp_msh; } public void setShp_msh(String shp_msh) { this.shp_msh = shp_msh; } }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
package com.atguigu.bean; public class T_MALL_TRADE_MARK { private int id; private String ppmch; private String url; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getPpmch() { return ppmch; } public void setPpmch(String ppmch) { this.ppmch = ppmch; } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } }