Educoder/头歌JAVA——Java Web：基于JSP的网上商城

一、商品列表

本关任务

一个商场不能缺少商品，本关需要借助

JDBC

，从

t_goods

表中获取销量前四的商品信息，并展示到页面。

JDBC

信息；
MYSQL用户名MYSQL密码驱动URL

root

123123

com.mysql.jdbc.Driver

jdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

com.educoder.entity.Goods

字段及方法；
字段描述类型get方法set方法

goodsId

商品

Id

String

getGoodsId()

setGoodsId(String goodsId)

goodsName

商品名

String

getGoodsName()

setGoodsName(String goodsName)

goodsImg

展示于主页的图

String

getGoodsImg() 

setGoodsImg(String goodsImg)

goodsPrice

价格

BigDecimal

getGoodsPrice()

setGoodsPrice(BigDecimal goodsPrice)

goodsNum

库存数量

Integer

getGoodsNum()

setGoodsNum(Integer goodsNum)

salesNum

销售数量

Integer

getSalesNum()

setSalesNum(Integer salesNum)

goodsSize

商品规格

String

getGoodsSize()

setGoodsSize(String goodsSize)

goodsFrom

商品产地

String

getGoodsFrom()

setGoodsFrom(String goodsFrom)

goodsTime

保质期

String

getGoodsTime()

setGoodsTime(String goodsTime)

goodsSaveCondition

存储条件

String

getGoodsSaveCondition()

setGoodsSaveCondition(String goodsSaveCondition)

goodsDescribe

商品描述介绍

String

getGoodsDescribe()

setGoodsDescribe(String goodsDescribe)

goodsExplain

对商品简短说明

String

getGoodsExplain()

setGoodsExplain(String goodsExplain)

goodsClass

所属类别

String

getGoodsClass()

setGoodsClass(String goodsClass)

goodsDiscount

折扣

BigDecimal

getGoodsDiscount()

setGoodsDiscount(BigDecimal goodsDiscount)

discountStartTime

优惠起始时间

Date

getDiscountStartTime()

setDiscountStartTime(Date discountStartTime)

discountEndTime

优惠截止时间

Date

getDiscountEndTime()

setDiscountEndTime(Date discountEndTime)

表和类对应表；
库名表名类名

online_shop

t_goods

Goods

1. 注意：类字段和对应的表字段名称一致，这里不再重复列出了。

页面初始效果图：

最终页面效果图：

具体要求

• 补全 getGoodsList（）方法，返回List<Goods>(商品列表)。

结果输出

[{"goodsClass":"甜品","goodsDescribe":"口感绵密，精致细腻\r\n这份雪白让你有如获至宝的幸福感","goodsExplain":"手工打发而成的进口鲜奶油","goodsFrom":"上海","goodsId":"list1","goodsImg":"list_1-1.jpg","goodsName":"雪域牛乳芝士","goodsNum":996,"goodsPrice":98.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约450g","goodsTime":"3天","salesNum":44},{"goodsClass":"甜品","goodsDescribe":"酸甜清爽\r\n回味无穷","goodsExplain":"卡福洛芒果泥","goodsFrom":"上海","goodsId":"list3","goodsImg":"list_3-1.jpg","goodsName":"芒果熔岩星球蛋糕 Mango Planet","goodsNum":1994,"goodsPrice":118.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约680g","goodsTime":"2天","salesNum":42},{"goodsClass":"甜品","goodsDescribe":"通过精致的工艺\r\n将巧克力的香醇甜蜜发挥到极致\r\n每一口都倍感甜蜜","goodsExplain":"进口的黑巧克力","goodsFrom":"上海","goodsId":"list2","goodsImg":"list_2-1.jpg","goodsName":"哈!蜜瓜蛋糕 Hey Melon","goodsNum":1496,"goodsPrice":99.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约500g","goodsTime":"3天","salesNum":39},{"goodsClass":"甜品","goodsDescribe":"在金色芒果淋面的外衣下\r\n是芒果慕斯的柔情\r\n是芒果啫喱的牵挂\r\n还有丝丝香草戚风蛋糕的香气","goodsExplain":"手工打发而成的进口鲜奶油","goodsFrom":"上海","goodsId":"list5","goodsImg":"list_5-1.jpg","goodsName":"蟹蟹你偷吃 Yum Yum Cake","goodsNum":6999,"goodsPrice":88.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约480g","goodsTime":"3天","salesNum":5}]

实现代码

package com.educoder.service.impl;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import com.educoder.entity.Goods;
    import com.educoder.service.GoodsService;
    import com.educoder.dao.impl.BaseDao;
    public class GoodsServiceImpl implements GoodsService {
        /**
         * 商品详情接口
         */
        public Goods getGoodsByGoodsId(String goodsId) {
            /********* Begin *********/
             String sql = "select * from t_goods where goodsId = ?";
            List<Object> parameters = new ArrayList<Object>();
            parameters.add(goodsId);
            List<Goods> goodsList = null;
            try {
                goodsList = BaseDao.operQuery(sql, parameters, Goods.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return goodsList.size() == 0 ? null : goodsList.get(0);
            /********* End *********/
        }
        /**
         * 商品搜索接口
         */
        public List<Goods> searchGoods(String condition) {
             /********* Begin *********/
            String sql = "select * from t_goods where goodsName like ? or goodsClass like ? order by salesNum desc";
            List<Goods> goodsList = null;
            condition = "%" + condition + "%";
            List<Object> paramenter = new ArrayList<Object>();
            paramenter.add(condition);
            paramenter.add(condition);
            try {
                goodsList = BaseDao.operQuery(sql, paramenter, Goods.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return goodsList;
            /********* End *********/
        }
        /**
         * 商品列表接口
         */
        public List<Goods> getGoodsList() {
            /********* Begin *********/
            String USERNAME = "root";
            String PASSWORD = "123123";
            String URL = "jdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true";
            Connection conn = null;
            PreparedStatement pste = null;
            ResultSet executeQuery = null;
            List<Goods> list = new ArrayList<Goods>();
            String sql = "select * from t_goods order by salesNum desc limit 4";
            try {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                pste = conn.prepareStatement(sql);
                executeQuery = pste.executeQuery();
                while (executeQuery.next()) {
                    Goods goods = new Goods();
                    goods.setGoodsId(executeQuery.getString("goodsId"));
                    goods.setGoodsName(executeQuery.getString("goodsName"));
                    goods.setGoodsImg(executeQuery.getString("goodsImg"));
                    goods.setGoodsPrice(executeQuery.getBigDecimal("goodsPrice"));
                    goods.setGoodsNum(executeQuery.getInt("goodsNum"));
                    goods.setSalesNum(executeQuery.getInt("salesNum"));
                    goods.setGoodsSize(executeQuery.getString("goodsSize"));
                    goods.setGoodsFrom(executeQuery.getString("goodsFrom"));
                    goods.setGoodsTime(executeQuery.getString("goodsTime"));
                    goods.setGoodsSaveCondition(executeQuery.getString("goodsSaveCondition"));
                    goods.setGoodsDescribe(executeQuery.getString("goodsDescribe"));
                    goods.setGoodsExplain(executeQuery.getString("goodsExplain"));
                    goods.setGoodsClass(executeQuery.getString("goodsClass"));
                    goods.setGoodsDiscount(executeQuery.getBigDecimal("goodsDiscount"));
                    goods.setDiscountStartTime(executeQuery.getDate("discountStartTime"));
                    goods.setDiscountEndTime(executeQuery.getDate("discountEndTime"));
                    list.add(goods);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (executeQuery != null)
                        executeQuery.close();
                    if (pste != null)
                        pste.close();
                    if (conn != null)
                        conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return list;
            /********* End *********/
        }
    }

二、商品详情

本关任务

有了商品还需要看商品的详细信息，本关需要借助

JDBC

，通过参数

商品Id

从数据库中获取商品详情，展示到页面。为了完成本关任务，需要了解：

• JDBC 查询方法封装；
• 商品相关信息介绍。

JDBC查询方法封装

为了方便

JDBC

操作，平台对

JDBC

查询方法进行了封装，封装类

com.educoder.dao.impl.BaseDao

。

public static <T> List<T> operQuery(String sql, List<Object> p, Class<T> cls)
            throws Exception {
        Connection conn = null;
        PreparedStatement pste = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<T>();
        conn = getConn();
        try {
            pste = conn.prepareStatement(sql);
            if (p != null) {
                for (int i = 0; i < p.size(); i++) {
                    pste.setObject(i + 1, p.get(i));
                }
            }
            
            rs = pste.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                T entity = cls.newInstance();
                for (int j = 0; j < rsmd.getColumnCount(); j++) {
                    String col_name = rsmd.getColumnName(j + 1);
                    Object value = rs.getObject(col_name);
                    Field field = cls.getDeclaredField(col_name);
                    field.setAccessible(true);
                    field.set(entity, value);
                }
                list.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            releaseAll(rs, pste, conn);
        }
        return list;
    }

例如，第一关可以使用如下方法，获取商品列表：

String sql = "select * from t_goods order by salesNum desc limit 4";
        List<Goods> goodsList = null;
        try {
            goodsList = BaseDao.operQuery(sql, null, Goods.class);
        } catch (Exception e) {
            e.printStackTrace();
        }

商品相关信息介绍

MYSQL用户名MYSQL密码驱动URL

root

123123

com.mysql.jdbc.Driver

jdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

com.educoder.entity.Goods

字段及方法；
字段描述类型get方法set方法

goodsId

商品

Id

String

getGoodsId()

setGoodsId(String goodsId)

goodsName

商品名

String

getGoodsName()

setGoodsName(String goodsName)

goodsImg

展示于主页的图

String

getGoodsImg() 

setGoodsImg(String goodsImg)

goodsPrice

价格

BigDecimal

getGoodsPrice()

setGoodsPrice(BigDecimal goodsPrice)

goodsNum

库存数量

Integer

getGoodsNum()

setGoodsNum(Integer goodsNum)

salesNum

销售数量

Integer

getSalesNum()

setSalesNum(Integer salesNum)

goodsSize

商品规格

String

getGoodsSize()

setGoodsSize(String goodsSize)

goodsFrom

商品产地

String

getGoodsFrom()

setGoodsFrom(String goodsFrom)

goodsTime

保质期

String

getGoodsTime()

setGoodsTime(String goodsTime)

goodsSaveCondition

存储条件

String

getGoodsSaveCondition()

setGoodsSaveCondition(String goodsSaveCondition)

goodsDescribe

商品描述介绍

String

getGoodsDescribe()

setGoodsDescribe(String goodsDescribe)

goodsExplain

对商品简短说明

String

getGoodsExplain()

setGoodsExplain(String goodsExplain)

goodsClass

所属类别

String

getGoodsClass()

setGoodsClass(String goodsClass)

goodsDiscount

折扣

BigDecimal

getGoodsDiscount()

setGoodsDiscount(BigDecimal goodsDiscount)

discountStartTime

优惠起始时间

Date

getDiscountStartTime()

setDiscountStartTime(Date discountStartTime)

discountEndTime

优惠截止时间

Date

getDiscountEndTime()

setDiscountEndTime(Date discountEndTime)

表和类对应表；
库名表名类名

online_shop

t_goods

Goods

1. **注意：**类字段和对应的表字段名称一致，这里不再重复列出了。

页面初始效果图：

最终页面效果图：

具体要求

• 补全getGoodsByGoodsId()方法，完成查询商品详情的任务，最后返回Goods(商品)。

结果输出

{"goodsClass":"甜品","goodsDescribe":"口感绵密，精致细腻\r\n这份雪白让你有如获至宝的幸福感","goodsExplain":"手工打发而成的进口鲜奶油","goodsFrom":"上海","goodsId":"list1","goodsImg":"list_1-1.jpg","goodsName":"雪域牛乳芝士","goodsNum":996,"goodsPrice":98.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约450g","goodsTime":"3天","salesNum":44}

实现代码

package com.educoder.service.impl;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import com.educoder.entity.Goods;
    import com.educoder.service.GoodsService;
    import com.educoder.dao.impl.BaseDao;
    public class GoodsServiceImpl implements GoodsService {
        /**
         * 商品详情接口
         */
        public Goods getGoodsByGoodsId(String goodsId) {
            /********* Begin *********/
             String sql = "select * from t_goods where goodsId = ?";
            List<Object> parameters = new ArrayList<Object>();
            parameters.add(goodsId);
            List<Goods> goodsList = null;
            try {
                goodsList = BaseDao.operQuery(sql, parameters, Goods.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return goodsList.size() == 0 ? null : goodsList.get(0);
            /********* End *********/
        }
        /**
         * 商品搜索接口
         */
        public List<Goods> searchGoods(String condition) {
             /********* Begin *********/
            String sql = "select * from t_goods where goodsName like ? or goodsClass like ? order by salesNum desc";
            List<Goods> goodsList = null;
            condition = "%" + condition + "%";
            List<Object> paramenter = new ArrayList<Object>();
            paramenter.add(condition);
            paramenter.add(condition);
            try {
                goodsList = BaseDao.operQuery(sql, paramenter, Goods.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return goodsList;
            /********* End *********/
        }
        /**
         * 商品列表接口
         */
        public List<Goods> getGoodsList() {
            /********* Begin *********/
            String USERNAME = "root";
            String PASSWORD = "123123";
            String URL = "jdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true";
            Connection conn = null;
            PreparedStatement pste = null;
            ResultSet executeQuery = null;
            List<Goods> list = new ArrayList<Goods>();
            String sql = "select * from t_goods order by salesNum desc limit 4";
            try {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                pste = conn.prepareStatement(sql);
                executeQuery = pste.executeQuery();
                while (executeQuery.next()) {
                    Goods goods = new Goods();
                    goods.setGoodsId(executeQuery.getString("goodsId"));
                    goods.setGoodsName(executeQuery.getString("goodsName"));
                    goods.setGoodsImg(executeQuery.getString("goodsImg"));
                    goods.setGoodsPrice(executeQuery.getBigDecimal("goodsPrice"));
                    goods.setGoodsNum(executeQuery.getInt("goodsNum"));
                    goods.setSalesNum(executeQuery.getInt("salesNum"));
                    goods.setGoodsSize(executeQuery.getString("goodsSize"));
                    goods.setGoodsFrom(executeQuery.getString("goodsFrom"));
                    goods.setGoodsTime(executeQuery.getString("goodsTime"));
                    goods.setGoodsSaveCondition(executeQuery.getString("goodsSaveCondition"));
                    goods.setGoodsDescribe(executeQuery.getString("goodsDescribe"));
                    goods.setGoodsExplain(executeQuery.getString("goodsExplain"));
                    goods.setGoodsClass(executeQuery.getString("goodsClass"));
                    goods.setGoodsDiscount(executeQuery.getBigDecimal("goodsDiscount"));
                    goods.setDiscountStartTime(executeQuery.getDate("discountStartTime"));
                    goods.setDiscountEndTime(executeQuery.getDate("discountEndTime"));
                    list.add(goods);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (executeQuery != null)
                        executeQuery.close();
                    if (pste != null)
                        pste.close();
                    if (conn != null)
                        conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return list;
            /********* End *********/
        }
    }

三、商品搜索

编程要求

请仔细阅读右侧代码，在

Begin-End

区域内进行代码补充，实现商品搜索的功能，具体需求如下：

• 补全searchGoods()方法，完成商品搜索的功能，最后返回List<Goods>(商品列表)。

测试说明

平台会对你编写的代码进行测试：

测试输入：

雪域牛

预期输出：

[{"goodsClass":"甜品","goodsDescribe":"口感绵密，精致细腻\r\n这份雪白让你有如获至宝的幸福感","goodsExplain":"手工打发而成的进口鲜奶油","goodsFrom":"上海","goodsId":"list1","goodsImg":"list_1-1.jpg","goodsName":"雪域牛乳芝士","goodsNum":996,"goodsPrice":98.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约450g","goodsTime":"3天","salesNum":44}]

实现代码

package com.educoder.service.impl;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import com.educoder.entity.Goods;
    import com.educoder.service.GoodsService;
    import com.educoder.dao.impl.BaseDao;
    public class GoodsServiceImpl implements GoodsService {
        /**
         * 商品详情接口
         */
        public Goods getGoodsByGoodsId(String goodsId) {
            /********* Begin *********/
             String sql = "select * from t_goods where goodsId = ?";
            List<Object> parameters = new ArrayList<Object>();
            parameters.add(goodsId);
            List<Goods> goodsList = null;
            try {
                goodsList = BaseDao.operQuery(sql, parameters, Goods.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return goodsList.size() == 0 ? null : goodsList.get(0);
            /********* End *********/
        }
        /**
         * 商品搜索接口
         */
        public List<Goods> searchGoods(String condition) {
             /********* Begin *********/
            String sql = "select * from t_goods where goodsName like ? or goodsClass like ? order by salesNum desc";
            List<Goods> goodsList = null;
            condition = "%" + condition + "%";
            List<Object> paramenter = new ArrayList<Object>();
            paramenter.add(condition);
            paramenter.add(condition);
            try {
                goodsList = BaseDao.operQuery(sql, paramenter, Goods.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return goodsList;
            /********* End *********/
        }
        /**
         * 商品列表接口
         */
        public List<Goods> getGoodsList() {
            /********* Begin *********/
            String USERNAME = "root";
            String PASSWORD = "123123";
            String URL = "jdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true";
            Connection conn = null;
            PreparedStatement pste = null;
            ResultSet executeQuery = null;
            List<Goods> list = new ArrayList<Goods>();
            String sql = "select * from t_goods order by salesNum desc limit 4";
            try {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                pste = conn.prepareStatement(sql);
                executeQuery = pste.executeQuery();
                while (executeQuery.next()) {
                    Goods goods = new Goods();
                    goods.setGoodsId(executeQuery.getString("goodsId"));
                    goods.setGoodsName(executeQuery.getString("goodsName"));
                    goods.setGoodsImg(executeQuery.getString("goodsImg"));
                    goods.setGoodsPrice(executeQuery.getBigDecimal("goodsPrice"));
                    goods.setGoodsNum(executeQuery.getInt("goodsNum"));
                    goods.setSalesNum(executeQuery.getInt("salesNum"));
                    goods.setGoodsSize(executeQuery.getString("goodsSize"));
                    goods.setGoodsFrom(executeQuery.getString("goodsFrom"));
                    goods.setGoodsTime(executeQuery.getString("goodsTime"));
                    goods.setGoodsSaveCondition(executeQuery.getString("goodsSaveCondition"));
                    goods.setGoodsDescribe(executeQuery.getString("goodsDescribe"));
                    goods.setGoodsExplain(executeQuery.getString("goodsExplain"));
                    goods.setGoodsClass(executeQuery.getString("goodsClass"));
                    goods.setGoodsDiscount(executeQuery.getBigDecimal("goodsDiscount"));
                    goods.setDiscountStartTime(executeQuery.getDate("discountStartTime"));
                    goods.setDiscountEndTime(executeQuery.getDate("discountEndTime"));
                    list.add(goods);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (executeQuery != null)
                        executeQuery.close();
                    if (pste != null)
                        pste.close();
                    if (conn != null)
                        conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return list;
            /********* End *********/
        }
    }

四、购物车列表

本关任务

网上看中的商品，暂时不买，还需要保存起来，这个需要通过购物车来实现。本关需要从

session

里获取属性名为

user

的

User

对象，并借助

JDBC

，通过表

t_cart

、

t_goods

获取用户购物车的商品内容，并展示到页面。

为了完成本关任务，需要了解：

• JDBC查询方法封装；
• 购物车相关信息介绍。

JDBC查询方法封装

为了方便

JDBC

操作，平台对

JDBC

查询方法进行了封装，封装类

com.educoder.dao.impl.BaseDao

。

public static <T> List<T> operQuery(String sql, List<Object> p, Class<T> cls)
            throws Exception {
        Connection conn = null;
        PreparedStatement pste = null;
        ResultSet rs = null;
        List<T> list = new ArrayList<T>();
        conn = getConn();
        try {
            pste = conn.prepareStatement(sql);
            if (p != null) {
                for (int i = 0; i < p.size(); i++) {
                    pste.setObject(i + 1, p.get(i));
                }
            }
            
            rs = pste.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                T entity = cls.newInstance();
                for (int j = 0; j < rsmd.getColumnCount(); j++) {
                    String col_name = rsmd.getColumnName(j + 1);
                    Object value = rs.getObject(col_name);
                    Field field = cls.getDeclaredField(col_name);
                    field.setAccessible(true);
                    field.set(entity, value);
                }
                list.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            releaseAll(rs, pste, conn);
        }
        return list;
    }

例如，第一关可以使用如下方法，获取商品列表：

1. String sql = "select * from t_goods order by salesNum desc limit 4";
2. List<Goods> goodsList = null;
3. try {
4. goodsList = BaseDao.operQuery(sql, null, Goods.class);
5. } catch (Exception e) {
6. e.printStackTrace();
7. }

购物车相关信息介绍

MYSQL用户名MYSQL密码驱动URL

root

123123

com.mysql.jdbc.Driver

jdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

com.educoder.entity.Goods

字段及方法；
字段描述类型get方法set方法

goodsId

商品

Id

String

getGoodsId()

setGoodsId(String goodsId)

goodsName

商品名

String

getGoodsName()

setGoodsName(String goodsName)

goodsImg

展示于主页的图

String

getGoodsImg() 

setGoodsImg(String goodsImg)

goodsPrice

价格

BigDecimal

getGoodsPrice()

setGoodsPrice(BigDecimal goodsPrice)

goodsNum

库存数量

Integer

getGoodsNum()

setGoodsNum(Integer goodsNum)

salesNum

销售数量

Integer

getSalesNum()

setSalesNum(Integer salesNum)

goodsSize

商品规格

String

getGoodsSize()

setGoodsSize(String goodsSize)

goodsFrom

商品产地

String

getGoodsFrom()

setGoodsFrom(String goodsFrom)

goodsTime

保质期

String

getGoodsTime()

setGoodsTime(String goodsTime)

goodsSaveCondition

存储条件

String

getGoodsSaveCondition()

setGoodsSaveCondition(String goodsSaveCondition)

goodsDescribe

商品描述介绍

String

getGoodsDescribe()

setGoodsDescribe(String goodsDescribe)

goodsExplain

对商品简短说明

String

getGoodsExplain()

setGoodsExplain(String goodsExplain)

goodsClass

所属类别

String

getGoodsClass()

setGoodsClass(String goodsClass)

goodsDiscount

折扣

BigDecimal

getGoodsDiscount()

setGoodsDiscount(BigDecimal goodsDiscount)

discountStartTime

优惠起始时间

Date

getDiscountStartTime()

setDiscountStartTime(Date discountStartTime)

discountEndTime

优惠截止时间

Date

getDiscountEndTime()

setDiscountEndTime(Date discountEndTime)

注意：

Cart

类冗余信息是通过和

t_goods

表关联查询出来的，不是存在下面的

t_cart

表里。

表和类对应表；
库名表名类名

online_shop

t_cart

Cart

注意：类字段和对应的表字段名称一致，这里不再重复列出了。

页面初始效果图：

最终页面效果图：

编程要求

根据提示，在右侧编辑器补充

getGoodsByUserId

方法代码，并返回

Cart

列表。

注意：后续关卡都是需要从

request

获取用户信息，从而获得

userId

。在本关卡获取成功后，后续关卡默认已经获取到

userId

，并当成接口参数传入，不再需要重复获取。

测试说明

平台会对你编写的代码进行测试：

预期输出：

[{"addTime":1543505629000,"buyNum":1,"cartId":101,"goodsClass":"甜品","goodsDescribe":"酸甜清爽\r\n回味无穷","goodsExplain":"卡福洛芒果泥","goodsFrom":"上海","goodsId":"list3","goodsImg":"list_3-1.jpg","goodsName":"芒果熔岩星球蛋糕 Mango Planet","goodsNum":1994,"goodsPrice":118.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约680g","goodsTime":"2天","salesNum":42,"userId":"123456"},{"addTime":1543505631000,"buyNum":1,"cartId":102,"discountEndTime":1499184679000,"discountStartTime":1498923465000,"goodsClass":"甜品","goodsDescribe":"芝士与香草戚风被雪域奶油的浪漫笼罩\r\n每一口都充满快乐的滋味","goodsDiscount":0.75,"goodsExplain":"选用进口白巧克力","goodsFrom":"上海","goodsId":"list6","goodsImg":"list_6-1.jpg","goodsName":"美刀刀蛋糕 Ms. Golden","goodsNum":3000,"goodsPrice":99.00,"goodsSaveCondition":"冷藏0~4摄氏度","goodsSize":"约700g","goodsTime":"3天","salesNum":5,"userId":"123456"}]

实现代码

package com.educoder.service.impl;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import javax.servlet.http.HttpServletRequest;
    import com.educoder.dao.impl.BaseDao;
    import com.educoder.entity.Cart;
    import com.educoder.entity.User;
    import com.educoder.service.CartService;
    public class CartServiceImpl implements CartService {
        // 查询购物车列表
        public List<Cart> getGoodsByUserId(HttpServletRequest request) {
            /********* Begin *********/
            User user = (User)request.getSession().getAttribute("user");
            String userId = user.getUserId();
            String sql = "select * from t_cart as a, t_goods as b where a.goodsId = b.goodsId and a.userId = ?";
            List<Cart> cartList = null;
            List<Object> parameters = new ArrayList<Object>();
            parameters.add(userId);
            try {
                cartList = BaseDao.operQuery(sql, parameters, Cart.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return cartList;
            /********* End *********/
        }
        // 购物车操作接口
        @Override
        public void doCartHandle(String userId, String goodsId, String buyNum, String oper) {
            /********* Begin *********/
            String deletesql = "delete from t_cart where userId=? and goodsId=?";
            String querysql = "select * from t_cart where userId = ? and goodsId = ?";
            String updatesql = "update t_cart set buyNum=? where userId=? and goodsId=?";
            String addsql = "insert into t_cart(userId, goodsId, buyNum, addTime) values(?, ?, ?, ?)";
            List<Object> parameters = new ArrayList<Object>();
            //删除
            if(buyNum == null) {
                parameters.add(userId);
                parameters.add(goodsId);
                BaseDao.operUpdate(deletesql, parameters);
            }
            else{
                //进行修改和添加
                List<Cart> goodsList = null;
                parameters.add(userId);
                parameters.add(goodsId);
                try {
                    goodsList = BaseDao.operQuery(querysql, parameters, Cart.class);
                    parameters.clear();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                //进行添加
                if(goodsList.isEmpty()) {
                    parameters.add(userId);
                    parameters.add(goodsId);
                    parameters.add(buyNum);
                    parameters.add(new Date());
                    BaseDao.operUpdate(addsql, parameters);
                }
                else {
                    //进行修改
                    if(oper != null) {
                        buyNum += goodsList.get(0).getBuyNum();
                        parameters.add(buyNum);
                        parameters.add(userId);
                        parameters.add(goodsId);
                        BaseDao.operUpdate(updatesql, parameters);
                    }
                }
            }
            /********* End *********/
        }
    }

五、购物车操作

本关任务

为了能进行购物车的修改、添加、删除操作，本关需要借助

JDBC

，对数据库表

t_cart

、

t_goods

表进行操作。

为了完成本关任务，需要了解：

• JDBC更新方法封装；
• 购物车相关信息介绍。

JDBC查询方法封装

为了方便

JDBC

操作，平台进行的

JDBC

更新方法进行了封装，封装类

com.educoder.dao.impl.BaseDao

。

public static boolean operUpdate(String sql, List<Object> p) {
        Connection conn = null;
        PreparedStatement pste = null;
        int res = 0;
        conn = getConn();
        try {
            pste = conn.prepareStatement(sql);
            if (p != null) {
                for (int i = 0; i < p.size(); i++) {
                    pste.setObject(i + 1, p.get(i));
                }
            }
            res = pste.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            releaseAll(null, pste, conn);
        }
        return res > 0;//
    }

购物车相关信息介绍

MYSQL用户名MYSQL密码驱动URL

root

123123

com.mysql.jdbc.Driver

jdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

com.educoder.entity.Goods

字段及方法；
字段描述类型get方法set方法

goodsId

商品

Id

String

getGoodsId()

setGoodsId(String goodsId)

goodsName

商品名

String

getGoodsName()

setGoodsName(String goodsName)

goodsImg

展示于主页的图

String

getGoodsImg() 

setGoodsImg(String goodsImg)

goodsPrice

价格

BigDecimal

getGoodsPrice()

setGoodsPrice(BigDecimal goodsPrice)

goodsNum

库存数量

Integer

getGoodsNum()

setGoodsNum(Integer goodsNum)

salesNum

销售数量

Integer

getSalesNum()

setSalesNum(Integer salesNum)

goodsSize

商品规格

String

getGoodsSize()

setGoodsSize(String goodsSize)

goodsFrom

商品产地

String

getGoodsFrom()

setGoodsFrom(String goodsFrom)

goodsTime

保质期

String

getGoodsTime()

setGoodsTime(String goodsTime)

goodsSaveCondition

存储条件

String

getGoodsSaveCondition()

setGoodsSaveCondition(String goodsSaveCondition)

goodsDescribe

商品描述介绍

String

getGoodsDescribe()

setGoodsDescribe(String goodsDescribe)

goodsExplain

对商品简短说明

String

getGoodsExplain()

setGoodsExplain(String goodsExplain)

goodsClass

所属类别

String

getGoodsClass()

setGoodsClass(String goodsClass)

goodsDiscount

折扣

BigDecimal

getGoodsDiscount()

setGoodsDiscount(BigDecimal goodsDiscount)

discountStartTime

优惠起始时间

Date

getDiscountStartTime()

setDiscountStartTime(Date discountStartTime)

discountEndTime

优惠截止时间

Date

getDiscountEndTime()

setDiscountEndTime(Date discountEndTime)

注意：

Cart

类冗余信息是需要通过和

t_goods

表关联查询出来的，不会存在下面的

t_cart

表里。

表和类对应表；
库名表名类名

online_shop

t_cart

Cart

注意：类字段和对应的表字段名称一致，这里不再重复列出了。

编程要求

根据提示，在右侧编辑器补充

doCartHandle

方法代码。

接口请求参数说明：

• userId 用户Id；
• goodsId 商品Id；
• buyNum 添加或修改数量，当为null，表示删除；
• oper 如果存在，添加buyNum；如果不存在，修改为buyNum。

测试输入：

123456 list2 1 2

预期输出：

插入成功 userId:123456 goodsId:list2 buyNum:1 oper:2

实现代码

package com.educoder.service.impl;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import javax.servlet.http.HttpServletRequest;
    import com.educoder.dao.impl.BaseDao;
    import com.educoder.entity.Cart;
    import com.educoder.entity.User;
    import com.educoder.service.CartService;
    public class CartServiceImpl implements CartService {
        // 查询购物车列表
        public List<Cart> getGoodsByUserId(HttpServletRequest request) {
            /********* Begin *********/
            User user = (User)request.getSession().getAttribute("user");
            String userId = user.getUserId();
            String sql = "select * from t_cart as a, t_goods as b where a.goodsId = b.goodsId and a.userId = ?";
            List<Cart> cartList = null;
            List<Object> parameters = new ArrayList<Object>();
            parameters.add(userId);
            try {
                cartList = BaseDao.operQuery(sql, parameters, Cart.class);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return cartList;
            /********* End *********/
        }
        // 购物车操作接口
        @Override
        public void doCartHandle(String userId, String goodsId, String buyNum, String oper) {
            /********* Begin *********/
            String deletesql = "delete from t_cart where userId=? and goodsId=?";
            String querysql = "select * from t_cart where userId = ? and goodsId = ?";
            String updatesql = "update t_cart set buyNum=? where userId=? and goodsId=?";
            String addsql = "insert into t_cart(userId, goodsId, buyNum, addTime) values(?, ?, ?, ?)";
            List<Object> parameters = new ArrayList<Object>();
            //删除
            if(buyNum == null) {
                parameters.add(userId);
                parameters.add(goodsId);
                BaseDao.operUpdate(deletesql, parameters);
            }
            else{
                //进行修改和添加
                List<Cart> goodsList = null;
                parameters.add(userId);
                parameters.add(goodsId);
                try {
                    goodsList = BaseDao.operQuery(querysql, parameters, Cart.class);
                    parameters.clear();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                //进行添加
                if(goodsList.isEmpty()) {
                    parameters.add(userId);
                    parameters.add(goodsId);
                    parameters.add(buyNum);
                    parameters.add(new Date());
                    BaseDao.operUpdate(addsql, parameters);
                }
                else {
                    //进行修改
                    if(oper != null) {
                        buyNum += goodsList.get(0).getBuyNum();
                        parameters.add(buyNum);
                        parameters.add(userId);
                        parameters.add(goodsId);
                        BaseDao.operUpdate(updatesql, parameters);
                    }
                }
            }
            /********* End *********/
        }
    }

六、下单

本关任务

上一关已经完成了购物车操作功能开发，现在我们就可以编写下单接口进行购买商品了，本关需要借助

JDBC

，在

t_order

、

t_order_child

表中存储用户订单信息。

下单相关信息介绍；
MYSQL用户名MYSQL密码驱动URLroot123123com.mysql.jdbc.Driverjdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

com.educoder.entity.Order

字段及方法；
字段描述类型get方法set方法orderId订单编号StringgetOrderId()setOrderId(String orderId)userId用户IdStringgetUserId()setUserId(String userId)orderTime订单时间DategetOrderTime()setOrderTime(Date orderTime)addressId订单地址StringgetAddressId()setAddressId()(String addressId)

com.educoder.entity.Order

冗余信息字段及方法；
字段描述类型get方法set方法goodsId商品IdStringgetGoodsId()setGoodsId(String goodsId)goodsName商品名称StringgetGoodsName()setGoodsName(String goodsName)goodsImg商品图片StringgetGoodsImg()setGoodsImg(String goodsImg)goodsPrice下单价格BigDecimalgetGoodsPrice()setGoodsPrice(BigDecimal goodsPrice)

注意：

Order

类冗余信息是需要通过关联查询出来的，不会存在下面的

t_order

表里。

com.educoder.entity.OrderChildTable

字段及方法；
字段描述类型get方法set方法id订单商品主键IntegergetId()setId(Integer id)orderId订单IdStringgetOrderId()setOrderId(String orderId)goodsId商品IdStringgetGoodsId()setGoodsId(String goodsId)buyNum购买数量IntegergetBuyNum()setBuyNum()(Integer buyNum)
表和类对应表；
库名表名类名online_shopt_orderOrderonline_shopt_order_childOrderChildTable

注意：类字段和对应的表字段名称一致，这里不再重复列出了。

编程要求

在右侧编辑器补充

submitOrder

方法代码，并返回

Order

类。

接口请求参数说明：

• userId 用户Id；
• addressId 地址Id；
• goodsBuyNum 数组，购物车选中的商品数量；
• chooseGoodId 数组，购物车选中的商品Id。

逻辑说明：

1. 下单时orderId需要唯一，可以使用UUID；
2. 表分为t_order订单表和t_order_child订单子表，因为下单商品有可能多个；
3. 在下单时，需要调用上一关的购物车操作接口，进行购物车数据清空。

测试输入：

123456 d01e153c-fc1f-4326-9956-cbf9582a9df7 1,1 list3,list6

预期输出：

清空购物车成功
订单存储信息: userId:123456 addressId:d01e153c-fc1f-4326-9956-cbf9582a9df7
订单商品存储信息:[{"buyNum":1,"goodsId":"list3","id":53},{"buyNum":1,"goodsId":"list6","id":54}]

实现代码

package com.educoder.service.impl;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import com.educoder.dao.impl.BaseDao;
import com.educoder.entity.Order;
import com.educoder.service.OrderService;
public class OrderServiceImpl implements OrderService {
     private CartServiceImpl catService = new CartServiceImpl();
    // 下单
    public Order submitOrder(String userId, String addressId, String[] goodsBuyNum,
            String[] chooseGoodId) {
        /********* Begin *********/
        Order order = new Order();
        String ordersql = "insert into t_order(orderId, userId, orderTime, addressId) values(?, ?, ?, ?)";
        String child_order_sql = "insert into t_order_child (orderId, goodsId, buyNum) values( ?, ?, ?) ";
        String orderId = UUID.randomUUID().toString();
        List<Object> parameters = new ArrayList<Object>();
        parameters.add(orderId);
        parameters.add(userId);
        parameters.add(new Date());
        parameters.add(addressId);
        BaseDao.operUpdate(ordersql, parameters);
        for(int n = 0 ; n < chooseGoodId.length; n++) {
            parameters.clear();
            parameters.add(orderId);
            parameters.add(chooseGoodId[n]);
            parameters.add(goodsBuyNum[n]);
            BaseDao.operUpdate(child_order_sql, parameters);
            //下单后，需要清空购物车
            catService.doCartHandle(userId, chooseGoodId[n], null, null);
        }
        //返回带有订单id的订单
        order.setOrderId(orderId);
        return order;
        /********* End *********/
    }
    // 订单查询
    public List<Order> getOrderByUserId(String userId) {
        /********* Begin *********/
        String sql = "select A.orderId, A.userId, B.userName, A.orderTime, C.address, E.goodsId, E.goodsName, D.buyNum, E.goodsPrice from " +
                "t_order as A, t_user as B, t_address as C, t_order_child as D, t_goods as E " +
                "where A.userId=? and A.orderId=D.orderId and A.userId=B.userId and A.addressId=C.addressId and D.goodsId=E.goodsId " +
                "order by orderTime desc";
        List<Object> parameters = new ArrayList<Object>();
        parameters.add(userId);
        List<Order> orderList = null;
        try {
            orderList = BaseDao.operQuery(sql, parameters, Order.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return orderList;
        /********* End *********/
    }
}

七、订单查询

本关任务

上一关已经完成下单接口，本关需要完成查询订单列表，本关需要借助

JDBC

从

t_order

、

t_user

、

t_address

、

t_order_child

表中查询订单信息。

订单查询相关信息介绍；
MYSQL用户名MYSQL密码驱动URLroot123123com.mysql.jdbc.Driverjdbc:mysql://127.0.0.1:3306/online_shop?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

com.educoder.entity.Order

字段及方法；
字段描述类型get方法set方法orderId订单编号StringgetOrderId()setOrderId(String orderId)userId用户IdStringgetUserId()setUserId(String userId)orderTime订单时间DategetOrderTime()setOrderTime(Date orderTime)addressId订单地址StringgetAddressId()setAddressId()(String addressId)

com.educoder.entity.Order

冗余信息字段及方法；
字段描述类型get方法set方法goodsId商品IdStringgetGoodsId()setGoodsId(String goodsId)goodsName商品名称StringgetGoodsName()setGoodsName(String goodsName)goodsImg商品图片StringgetGoodsImg()setGoodsImg(String goodsImg)goodsPrice下单价格BigDecimalgetGoodsPricesetGoodsPrice(BigDecimal goodsPrice)

注意：

Order

类冗余信息是需要通过关联查询出来的，不会存在下面的

t_order

表里。

com.educoder.entity.OrderChildTable

字段及方法；
字段描述类型get方法set方法id订单商品主键IntegergetId()setId(Integer id)orderId订单IdStringgetOrderId()setOrderId(String orderId)goodsId商品IdStringgetGoodsId()setGoodsId(String goodsId)buyNum购买数量IntegergetBuyNum()setBuyNum()(Integer buyNum)

com.educoder.entity.Address

字段及方法；
字段描述类型get方法set方法addressId地址IdStringgetAddressId()setAddressId(String addressId)userId用户IdStringgetUserId()setUserId(String userId)userName收货姓名StringgetUserName()setUserName(String userName)userTel收货电话StringgetUserTel()setUserTel()(String addressId)address收货地址StringgetAddress()setAddress()(String addressId)
表和类对应表；
库名表名类名online_shopt_orderOrderonline_shopt_order_childOrderChildTableonline_shopt_addressAddress

注意：类字段和对应的表字段名称一致，这里不再重复列出了。

页面初始效果图：

最终页面效果图：

编程要求

在右侧编辑器补充

getOrderByUserId

方法代码，并返回

Goods

列表。

接口请求参数说明：

• userId 用户Id。

测试说明

测试输入：

123456

预期输出：

[{"address":"测试地址","buyNum":1,"goodsId":"list1","goodsName":"雪域牛乳芝士","goodsPrice":98.00,"orderId":"ab25ca41-2230-4af1-81f7-6cfe8872d6b0","orderTime":1543220250000,"userId":"123456","userName":"test"},{“address":"测试地址","buyNum":1,"goodsId":"list2","goodsName":"哈!蜜瓜蛋糕 Hey Melon","goodsPrice":99.00,"orderId":"ab25ca41-2230-4af1-81f7-6cfe8872d6b0","orderTime":1543220250000,"userId":"123456","userName":"test"}]

实现代码

package com.educoder.service.impl;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import com.educoder.dao.impl.BaseDao;
import com.educoder.entity.Order;
import com.educoder.service.OrderService;
public class OrderServiceImpl implements OrderService {
     private CartServiceImpl catService = new CartServiceImpl();
    // 下单
    public Order submitOrder(String userId, String addressId, String[] goodsBuyNum,
            String[] chooseGoodId) {
        /********* Begin *********/
        Order order = new Order();
        String ordersql = "insert into t_order(orderId, userId, orderTime, addressId) values(?, ?, ?, ?)";
        String child_order_sql = "insert into t_order_child (orderId, goodsId, buyNum) values( ?, ?, ?) ";
        String orderId = UUID.randomUUID().toString();
        List<Object> parameters = new ArrayList<Object>();
        parameters.add(orderId);
        parameters.add(userId);
        parameters.add(new Date());
        parameters.add(addressId);
        BaseDao.operUpdate(ordersql, parameters);
        for(int n = 0 ; n < chooseGoodId.length; n++) {
            parameters.clear();
            parameters.add(orderId);
            parameters.add(chooseGoodId[n]);
            parameters.add(goodsBuyNum[n]);
            BaseDao.operUpdate(child_order_sql, parameters);
            //下单后，需要清空购物车
            catService.doCartHandle(userId, chooseGoodId[n], null, null);
        }
        //返回带有订单id的订单
        order.setOrderId(orderId);
        return order;
        /********* End *********/
    }
    // 订单查询
    public List<Order> getOrderByUserId(String userId) {
        /********* Begin *********/
        String sql = "select A.orderId, A.userId, B.userName, A.orderTime, C.address, E.goodsId, E.goodsName, D.buyNum, E.goodsPrice from " +
                "t_order as A, t_user as B, t_address as C, t_order_child as D, t_goods as E " +
                "where A.userId=? and A.orderId=D.orderId and A.userId=B.userId and A.addressId=C.addressId and D.goodsId=E.goodsId " +
                "order by orderTime desc";
        List<Object> parameters = new ArrayList<Object>();
        parameters.add(userId);
        List<Order> orderList = null;
        try {
            orderList = BaseDao.operQuery(sql, parameters, Order.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return orderList;
        /********* End *********/
    }
}