目錄

一、商品列表

本關(guān)任務(wù)

具體要求

結(jié)果輸出

實(shí)現(xiàn)代碼

二、商品詳情

本關(guān)任務(wù)

JDBC查詢方法封裝

商品相關(guān)信息介紹

具體要求

結(jié)果輸出

實(shí)現(xiàn)代碼

三、商品搜索

編程要求

測試說明

實(shí)現(xiàn)代碼

四、購物車列表

本關(guān)任務(wù)

JDBC查詢方法封裝

購物車相關(guān)信息介紹

編程要求

測試說明

實(shí)現(xiàn)代碼

五、購物車操作

本關(guān)任務(wù)

JDBC查詢方法封裝

購物車相關(guān)信息介紹

編程要求

實(shí)現(xiàn)代碼

六、下單

本關(guān)任務(wù)

編程要求

實(shí)現(xiàn)代碼

七、訂單查詢

本關(guān)任務(wù)

編程要求

測試說明

實(shí)現(xiàn)代碼

一、商品列表

本關(guān)任務(wù)

一個(gè)商場不能缺少商品，本關(guān)需要借助JDBC，從 t_goods表中獲取銷量前四的商品信息，并展示到頁面。

JDBC信息；

com.educoder.entity.Goods字段及方法；

表和類對(duì)應(yīng)表；

1. 注意：類字段和對(duì)應(yīng)的表字段名稱一致，這里不再重復(fù)列出了。

頁面初始效果圖：

最終頁面效果圖：

具體要求

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

結(jié)果輸出

[{"goodsClass":"甜品","goodsDescribe":"口感綿密，精致細(xì)膩\r\n這份雪白讓你有如獲至寶的幸福感","goodsExplain":"手工打發(fā)而成的進(jìn)口鮮奶油","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將巧克力的香醇甜蜜發(fā)揮到極致\r\n每一口都倍感甜蜜","goodsExplain":"進(jìn)口的黑巧克力","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還有絲絲香草戚風(fēng)蛋糕的香氣","goodsExplain":"手工打發(fā)而成的進(jìn)口鮮奶油","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}]

實(shí)現(xiàn)代碼

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 *********/
        }
    }

二、商品詳情

本關(guān)任務(wù)

有了商品還需要看商品的詳細(xì)信息，本關(guān)需要借助JDBC，通過參數(shù)商品Id從數(shù)據(jù)庫中獲取商品詳情，展示到頁面。為了完成本關(guān)任務(wù)，需要了解：

• JDBC?查詢方法封裝；
• 商品相關(guān)信息介紹。

JDBC查詢方法封裝

為了方便JDBC操作，平臺(tái)對(duì)JDBC查詢方法進(jìn)行了封裝，封裝類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;
    }

例如，第一關(guān)可以使用如下方法，獲取商品列表：

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

商品相關(guān)信息介紹

com.educoder.entity.Goods字段及方法；

表和類對(duì)應(yīng)表；

1. 注意：類字段和對(duì)應(yīng)的表字段名稱一致，這里不再重復(fù)列出了。

頁面初始效果圖：

最終頁面效果圖：

具體要求

• 補(bǔ)全getGoodsByGoodsId()方法，完成查詢商品詳情的任務(wù)，最后返回Goods(商品)。

結(jié)果輸出

{"goodsClass":"甜品","goodsDescribe":"口感綿密，精致細(xì)膩\r\n這份雪白讓你有如獲至寶的幸福感","goodsExplain":"手工打發(fā)而成的進(jìn)口鮮奶油","goodsFrom":"上海","goodsId":"list1","goodsImg":"list_1-1.jpg","goodsName":"雪域牛乳芝士","goodsNum":996,"goodsPrice":98.00,"goodsSaveCondition":"冷藏0~4攝氏度","goodsSize":"約450g","goodsTime":"3天","salesNum":44}

實(shí)現(xiàn)代碼

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 *********/
        }
    }

三、商品搜索

編程要求

請(qǐng)仔細(xì)閱讀右側(cè)代碼，在Begin-End區(qū)域內(nèi)進(jìn)行代碼補(bǔ)充，實(shí)現(xiàn)商品搜索的功能，具體需求如下：

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

測試說明

平臺(tái)會(huì)對(duì)你編寫的代碼進(jìn)行測試：

測試輸入：雪域牛

預(yù)期輸出：文章來源地址http://www.zghlxwxcb.cn/news/detail-429923.html

[{"goodsClass":"甜品","goodsDescribe":"口感綿密，精致細(xì)膩\r\n這份雪白讓你有如獲至寶的幸福感","goodsExplain":"手工打發(fā)而成的進(jìn)口鮮奶油","goodsFrom":"上海","goodsId":"list1","goodsImg":"list_1-1.jpg","goodsName":"雪域牛乳芝士","goodsNum":996,"goodsPrice":98.00,"goodsSaveCondition":"冷藏0~4攝氏度","goodsSize":"約450g","goodsTime":"3天","salesNum":44}]

實(shí)現(xiàn)代碼

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 *********/
        }
    }

四、購物車列表

本關(guān)任務(wù)

網(wǎng)上看中的商品，暫時(shí)不買，還需要保存起來，這個(gè)需要通過購物車來實(shí)現(xiàn)。本關(guān)需要從session里獲取屬性名為user的User對(duì)象，并借助JDBC，通過表t_cart、t_goods獲取用戶購物車的商品內(nèi)容，并展示到頁面。

為了完成本關(guān)任務(wù)，需要了解：

• JDBC查詢方法封裝；

• 購物車相關(guān)信息介紹。

JDBC查詢方法封裝

為了方便JDBC操作，平臺(tái)對(duì)JDBC查詢方法進(jìn)行了封裝，封裝類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;
    }

例如，第一關(guān)可以使用如下方法，獲取商品列表：

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

購物車相關(guān)信息介紹

com.educoder.entity.Goods字段及方法；

注意：Cart類冗余信息是通過和t_goods表關(guān)聯(lián)查詢出來的，不是存在下面的t_cart表里。

表和類對(duì)應(yīng)表；

注意：類字段和對(duì)應(yīng)的表字段名稱一致，這里不再重復(fù)列出了。

頁面初始效果圖：

最終頁面效果圖：

編程要求

根據(jù)提示，在右側(cè)編輯器補(bǔ)充getGoodsByUserId方法代碼，并返回Cart列表。

注意：后續(xù)關(guān)卡都是需要從request獲取用戶信息，從而獲得userId。在本關(guān)卡獲取成功后，后續(xù)關(guān)卡默認(rèn)已經(jīng)獲取到userId，并當(dāng)成接口參數(shù)傳入，不再需要重復(fù)獲取。

測試說明

平臺(tái)會(huì)對(duì)你編寫的代碼進(jìn)行測試：

預(yù)期輸出：

[{"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":"芝士與香草戚風(fēng)被雪域奶油的浪漫籠罩\r\n每一口都充滿快樂的滋味","goodsDiscount":0.75,"goodsExplain":"選用進(jìn)口白巧克力","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"}]

實(shí)現(xiàn)代碼

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{
                //進(jìn)行修改和添加
                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();
                }
                //進(jìn)行添加
                if(goodsList.isEmpty()) {
                    parameters.add(userId);
                    parameters.add(goodsId);
                    parameters.add(buyNum);
                    parameters.add(new Date());
                    BaseDao.operUpdate(addsql, parameters);
                }
                else {
                    //進(jìn)行修改
                    if(oper != null) {
                        buyNum += goodsList.get(0).getBuyNum();
                        parameters.add(buyNum);
                        parameters.add(userId);
                        parameters.add(goodsId);
                        BaseDao.operUpdate(updatesql, parameters);
                    }
                }
            }
            /********* End *********/
        }
    }

五、購物車操作

本關(guān)任務(wù)

為了能進(jìn)行購物車的修改、添加、刪除操作，本關(guān)需要借助JDBC，對(duì)數(shù)據(jù)庫表t_cart、t_goods表進(jìn)行操作。

為了完成本關(guān)任務(wù)，需要了解：

• JDBC更新方法封裝；

• 購物車相關(guān)信息介紹。

JDBC查詢方法封裝

為了方便JDBC操作，平臺(tái)進(jìn)行的JDBC更新方法進(jìn)行了封裝，封裝類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;//
    }

購物車相關(guān)信息介紹

com.educoder.entity.Goods字段及方法；

注意：Cart類冗余信息是需要通過和t_goods表關(guān)聯(lián)查詢出來的，不會(huì)存在下面的t_cart表里。

表和類對(duì)應(yīng)表；

注意：類字段和對(duì)應(yīng)的表字段名稱一致，這里不再重復(fù)列出了。

編程要求

根據(jù)提示，在右側(cè)編輯器補(bǔ)充doCartHandle方法代碼。

接口請(qǐng)求參數(shù)說明：

• userId 用戶Id；

• goodsId 商品Id；

• buyNum 添加或修改數(shù)量，當(dāng)為null，表示刪除；

• oper 如果存在，添加buyNum；如果不存在，修改為buyNum。

測試輸入：123456 list2 1 2

預(yù)期輸出：插入成功 userId:123456 goodsId:list2 buyNum:1 oper:2

實(shí)現(xiàn)代碼

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{
                //進(jìn)行修改和添加
                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();
                }
                //進(jìn)行添加
                if(goodsList.isEmpty()) {
                    parameters.add(userId);
                    parameters.add(goodsId);
                    parameters.add(buyNum);
                    parameters.add(new Date());
                    BaseDao.operUpdate(addsql, parameters);
                }
                else {
                    //進(jìn)行修改
                    if(oper != null) {
                        buyNum += goodsList.get(0).getBuyNum();
                        parameters.add(buyNum);
                        parameters.add(userId);
                        parameters.add(goodsId);
                        BaseDao.operUpdate(updatesql, parameters);
                    }
                }
            }
            /********* End *********/
        }
    }

六、下單

本關(guān)任務(wù)

上一關(guān)已經(jīng)完成了購物車操作功能開發(fā)，現(xiàn)在我們就可以編寫下單接口進(jìn)行購買商品了，本關(guān)需要借助JDBC，在t_order、t_order_child表中存儲(chǔ)用戶訂單信息。

下單相關(guān)信息介紹；

com.educoder.entity.Order字段及方法；

com.educoder.entity.Order冗余信息字段及方法；

注意：Order類冗余信息是需要通過關(guān)聯(lián)查詢出來的，不會(huì)存在下面的t_order表里。

com.educoder.entity.OrderChildTable字段及方法；

表和類對(duì)應(yīng)表；

注意：類字段和對(duì)應(yīng)的表字段名稱一致，這里不再重復(fù)列出了。

編程要求

在右側(cè)編輯器補(bǔ)充submitOrder方法代碼，并返回Order類。

接口請(qǐng)求參數(shù)說明：

• userId 用戶Id；

• addressId 地址Id；

• goodsBuyNum 數(shù)組，購物車選中的商品數(shù)量；

• chooseGoodId 數(shù)組，購物車選中的商品Id。

邏輯說明：

1. 下單時(shí)orderId需要唯一，可以使用UUID；

2. 表分為t_order訂單表和t_order_child訂單子表，因?yàn)橄聠紊唐酚锌赡芏鄠€(gè)；

3. 在下單時(shí)，需要調(diào)用上一關(guān)的購物車操作接口，進(jìn)行購物車數(shù)據(jù)清空。

測試輸入：123456 d01e153c-fc1f-4326-9956-cbf9582a9df7 1,1 list3,list6

預(yù)期輸出：

清空購物車成功
訂單存儲(chǔ)信息: userId:123456 addressId:d01e153c-fc1f-4326-9956-cbf9582a9df7
訂單商品存儲(chǔ)信息:[{"buyNum":1,"goodsId":"list3","id":53},{"buyNum":1,"goodsId":"list6","id":54}]

實(shí)現(xiàn)代碼

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 *********/
    }
}

七、訂單查詢

本關(guān)任務(wù)

上一關(guān)已經(jīng)完成下單接口，本關(guān)需要完成查詢訂單列表，本關(guān)需要借助JDBC從t_order、t_user、t_address、t_order_child表中查詢訂單信息。

訂單查詢相關(guān)信息介紹；

com.educoder.entity.Order字段及方法；

com.educoder.entity.Order冗余信息字段及方法；

注意：Order類冗余信息是需要通過關(guān)聯(lián)查詢出來的，不會(huì)存在下面的t_order表里。

com.educoder.entity.OrderChildTable字段及方法；

com.educoder.entity.Address字段及方法；

表和類對(duì)應(yīng)表；

注意：類字段和對(duì)應(yīng)的表字段名稱一致，這里不再重復(fù)列出了。

頁面初始效果圖：

最終頁面效果圖：

編程要求

在右側(cè)編輯器補(bǔ)充getOrderByUserId方法代碼，并返回Goods列表。

接口請(qǐng)求參數(shù)說明：

• userId?用戶Id。

測試說明

測試輸入：123456

預(yù)期輸出：

[{"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"}]

實(shí)現(xiàn)代碼

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 *********/
    }
}

到了這里，關(guān)于Educoder/頭歌JAVA——Java Web：基于JSP的網(wǎng)上商城的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！