mysql 基本sql使用总结-电商项目

最新推荐文章于 2022-05-11 13:59:26 发布

weixin_30477293

最新推荐文章于 2022-05-11 13:59:26 发布

阅读量116

点赞数

文章标签： java javascript 测试 ViewUI

原文链接：http://www.cnblogs.com/shiyun32/p/10746805.html

版权

一、增加

1. 设置插入数据后自动返回主键

主键返回策略只适用于单个对象的插入；

主键返回策略：

　　(1) 单独参数

　　　　直接用set方法

　　(2) map

　　　　对象名.id

　　(3) 多参数列表

　　对象名.id（eg：keyProperty="attr.id"）

eg1: 单独参数

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);

}

View Code

<!-- 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>

View Code

eg2: 多参数对象时，需要指定keyProperty="attr.id"，这样MyBatis才知道应该将返回的主键封装给哪个对象

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);

}

View Code

<?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>

View Code

2. 批量插入

我传入的参数为map，参数封装：

// 根据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);

View Code

sql:

    <!-- 插入商品图片 -->
    <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>

View Code

3.多重对象的同步表单参数提交

A页面表达式ognl(object gram navigation language)

List_attr[0].list_value[0].shxzh 对象.属性.对象的方式去找到对应的属性

B mvc如何封装？

jsp的表单页面：页面上的级联属性名或者对象名必须在java中要有set方法方法，表单提交时才能自动封装上表单的数据。

<%@ 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>

View Code

Controller接收方法：

/**
     * 商品属性提交 （双重集合提交）
     * 页面表达式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;
    }

View Code

service中的方法：

    /**
     * 保存商品属性
     */
    @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());
        }
    }

View Code

实体：

属性值实体：

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;
    }

}

View Code

属性实体：

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;
    }

}

View Code

商品属性包装对象：将商品的属性和属性值封装为一个对象

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;
    }

}

View Code

商品属性模型实体：这个实体是因为Controller不能直接接收List<OBJECT_T_MALL_ATTR>集合参数而建立的

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;
    }

}

View Code

二、删除

三、改

四、查

1、查询有多层嵌套的实体

eg1 :

sql:

<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>

View Code

dao层：

List<OBJECT_T_MALL_ATTR> select_attr_list(int flbh2);

View Code

实体：

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;
    }

}

View Code

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;
    }

}

View Code

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;
    }

}

View Code

eg2:

sql:

<?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>

View Code

mapper:

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);

}

View Code

实体：

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;
    }

}

View Code

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;
    }

}

View Code

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;
    }

}

View Code

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;
    }

}

View Code

转载于:https://www.cnblogs.com/shiyun32/p/10746805.html

weixin_30477293

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 基本sql使用总结-电商项目

一、增加1. 设置插入数据后自动返回主键主键返回策略只适用于单个对象的插入；主键返回策略：　　(1) 单独参数　　　　直接用set方法　　(2) map　　　　对象名.id　　(3)多参数列表　　对象名.id（eg：keyProperty="attr.id"）eg1: 单独参数package com.atguigu.mapper;...
复制链接

扫一扫