jsp电子商务购物车之四 数据库存储篇

package com.cart.entity;
//购物车的单项;
public class CartItem {
  int uid;
  int bid; //外键;理解为商品id，这样可以任意;
  int count;
  String bookname;//方便存储
  double price; //单项价格;
  private String image;


  public int getUid() {
    return uid;
  }
  public void setUid(int uid) {
    this.uid = uid;
  }
  public int getBid() {
    return bid;
  }
  public void setBid(int bid) {
    this.bid = bid;
  }
  public int getCount() {
    return count;
  }
  public void setCount(int count) {
    this.count = count;
  }
  public String getBookname() {
    return bookname;
  }
  public void setBookname(String bookname) {
    this.bookname = bookname;
  }
  public double getPrice() {
    return price;
  }
  public void setPrice(double price) {
    this.price = price;
  }

  //有一个方法，获得总价;
  public double getTotalPrice(){
    return price*count;
  }
  public void setImage(String image) {
    this.image = image;
  }
  public String getImage() {
    return image;
  }
}

//增加购物项
public interface CartDao {
  public int addCartItem(CartItem cartItem);
  public boolean isExists(int uid,int bid);
  public int updateCartItemCount(CartItem cartItem);
  public Map<Integer,CartItem> getCartItem(int uid);
  public CartItem findCartItemById(int uid,int bid);
}

package com.cart.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import com.cart.dao.CartDao;
import com.cart.entity.Book;
import com.cart.entity.CartItem;
import com.cart.util.DaoFactory;

public class CartDaoImpl implements CartDao {
  Connection con = null;
  PreparedStatement pstmt = null;
  ResultSet rs=null;
  public int addCartItem(CartItem cartItem) {
    String sql="insert cartitem values(?,?,?,?,?,?)";
    int result=-1;

    try {
      con=DaoFactory.getConnection();
      Object[]params={cartItem.getUid(),cartItem.getBid(),cartItem.getBookname(),cartItem.getPrice(),cartItem.getCount(),cartItem.getImage()};
      pstmt=con.prepareStatement(sql);
      DaoFactory.setParams(pstmt, params);
      result=DaoFactory.executeUpdate(sql, params);
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      DaoFactory.closeAll(null, pstmt, con);
    }
    return result;
  }

  /* 根据uid和bid进行判断是否已经有物品存在于否则，有则返回true，不用再插入了;
   * @see com.cart.dao.CartDao#isExists(int, int)
   */
  public boolean isExists(int uid,int bid) {
    String sql="select uid,bid from cartitem where uid=? and bid=?";
    boolean result=false;

    try {
      con=DaoFactory.getConnection();
      pstmt=con.prepareStatement(sql);
      Object[] params={uid,bid};
      DaoFactory.setParams(pstmt, params);
      rs=pstmt.executeQuery();
      if(rs.next()){
        return true;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      DaoFactory.closeAll(null, pstmt, con);
    }
    return result;
  }

  public int updateCartItemCount(CartItem cartItem) {
    String sql="update cartitem set count=? where uid=? and bid=?";
    int result=-1;
    try {
      con=DaoFactory.getConnection();
      pstmt=con.prepareStatement(sql);
      Object[] params={cartItem.getCount(),cartItem.getUid(),cartItem.getBid()};
      DaoFactory.setParams(pstmt, params);
      result=pstmt.executeUpdate();

    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      DaoFactory.closeAll(null, pstmt, con);
    }

    return result;
  }

  public Map<Integer, CartItem> getCartItem(int uid) {
    String sql="select * from cartitem where uid=?";
    Map<Integer,CartItem>cartItems=new HashMap<Integer,CartItem>();

    try {
      con=DaoFactory.getConnection();
      pstmt=con.prepareStatement(sql);
      Object[] params={uid};
      DaoFactory.setParams(pstmt, params);
      rs=pstmt.executeQuery();
      while(rs.next()){
        CartItem item=new CartItem();
        item.setUid(uid);
        int bid=rs.getInt("bid");
        item.setBid(bid);
        item.setBookname(rs.getString("bookname"));
        item.setCount(rs.getInt("count"));
        item.setPrice(rs.getDouble("price"));
        item.setImage(rs.getString("image"));

        cartItems.put(bid,item);//bid，后面是对应的item;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      DaoFactory.closeAll(null, pstmt, con);
    }
    return cartItems;
  }

  public CartItem findCartItemById(int uid,int bid) {
    String sql = "select * from cartitem where uid=? and bid=?";
    ResultSet rs = null;
    CartItem item = null;
    try {
      con=DaoFactory.getConnection();
      pstmt=con.prepareStatement(sql);
      Object[] params={uid,bid};
      DaoFactory.setParams(pstmt, params);
      rs=pstmt.executeQuery();
      if(rs.next()){
        item = new CartItem();
        item.setUid(rs.getInt("uid"));
        item.setBookname(rs.getString("bookname"));
        item.setBid(rs.getInt("bid"));
        item.setCount(rs.getInt("count"));
        item.setPrice(rs.getDouble("price"));
        item.setImage(rs.getString("image"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    finally{
      DaoFactory.closeAll(rs, pstmt, con);
    }
    return item;
  }

}

package com.cart.web;


import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.cart.entity.Book;
import com.cart.entity.Cart;
import com.cart.entity.CartItem;
import com.cart.entity.Userinfo;
import com.cart.service.BookService;
import com.cart.service.CartService;

public class BuyServlet extends HttpServlet {
  @Override
  protected void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {

    resp.setContentType("text/html;charset=utf-8");
    HttpSession session=req.getSession();

    Userinfo userinfo =(Userinfo)req.getSession().getAttribute("userinfo");
    int bid=Integer.parseInt(req.getParameter("id"));//这个就是bid
    //通过Service来获得book对象;
    BookService bookService=new BookService();
    Book book=bookService.getBookById(bid);
    //下一步，点击购物的时候，获得bid，+uid;1 1，则调出自己的购物车列表;
    CartService cartService=new CartService();
    int uid=userinfo.getId();
    Map<Integer,CartItem> cart=(Map<Integer,CartItem>)session.getAttribute("cart");
    //1.是已经存在于数据库的;
    if(cartService.isExists(uid, bid));//如果没有登录，已经过滤了;
    {
      //如果已经存在相当的数据，则应该读取出来到CartItem
      cart=cartService.getCartItem(uid);
    }
    //2.没有买过的；也没有登录过的；则新创建;
    //cart = (Map<Integer,CartItem>)session.getAttribute("cart");
    if(cart==null){
      cart=new HashMap<Integer,CartItem>();//id以及CartItem对象,这样购物车想可以是任意商品
      session.setAttribute("cart",cart);
    }

    //3.利用session登录，加入购物车了；但是没有到数据库的;
//
    CartItem cartItem=cart.get(bid);   //有4，但是数据库没有;

    if (cartItem == null) {
      cartItem=new CartItem();
      cartItem.setUid(userinfo.getId());
      cartItem.setBid(bid);// 此id为book的id;
      cartItem.setPrice(book.getPrice()); // 设置购物车项，图书的价格,升级要考虑会员的vip
      cartItem.setBookname(book.getBookname());
      cartItem.setCount(1); // 购物项完毕之后，需要将购物项放到一个购物车篮子中;
      cartItem.setImage(book.getImage());
    } else {
      // 判断是否已经是最后一本书;数量大于获得数量,其他的不能再加数量了
      if(book.getStock()>cartItem.getCount()){
        cartItem.setCount(cartItem.getCount()+1);
      }
    }
    //目前的问题是如果已经存在于数据库了，没有把cart的值给获取过来;
    cart.put(bid, cartItem);// 将购物车选项加进来!!!
    //同时设置购物车项进数据库，方便下次客户进来判断;

    //思路有问题f
    cartService.addCartItem(cartItem);
    //是否可以直接将cart插入到数据库中，对应某个该用户的id;
    session.setAttribute("cart", cart); //加入session用jstl读取；
    resp.sendRedirect("show_cart.jsp");
    //需要将购物车项加入到数据库中;
  }
  @Override
  protected void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    this.doGet(req, resp);
  }
}

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
  <head>
    <base href="<%=basePath%>">

    <title>My JSP 'cart.jsp' starting page</title>

  <meta http-equiv="pragma" content="no-cache">
  <meta http-equiv="cache-control" content="no-cache">
  <meta http-equiv="expires" content="0">    
  <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
  <meta http-equiv="description" content="This is my page">

  <link rel="stylesheet" href="css/style.css" type="text/css"></link>

  <script type="text/javascript" src="js/jquery-1.7.2.min.js"></script>

  <script type="text/javascript">
    function jian(id){
    if($("#count"+id).val()==1){
    //采用淘宝模式，如果低于1，则不用提示，直接不可用;
      $("#count"+id).prev().attribute("disabled","disabled");
      return;
    }

      $.ajax({
        url:'ChangeCartCountServlet',
        type:'post',
        dataType:'text',
        data:{
          bookid:id,
          count:parseInt($("#count"+id).val())-1   //  -1
        },
        success:function(data){

        var price = $("#price"+id).html();
          $("#count"+id).val(parseInt($("#count"+id).val())-1);
          $("#sum"+id).val("￥"+price*$("#count"+id).val());

          calcTotal();

        }
      });
    }

    function add(id){
      $.ajax({
        url:'ChangeCartCountServlet',
        type:'post',
        dataType:'text',
        data:{
          bookid:id,
          count:parseInt($("#count"+id).val())+1
        },
        success:function(data){
          if(data=="false"){
            alert("库存不足!!!!");        
          }
          else{
            var price = $("#price"+id).html();
            $("#count"+id).val(parseInt($("#count"+id).val())+1);
            $("#sum"+id).val("￥"+price*$("#count"+id).val());

            calcTotal();
          }
        }
      });




    }

    function calcTotal(){
      // input...
      var counts = $("input[id^=count]").toArray();  
      var prices = $("div[id^=price]").toArray();
      var total = 0;

      for(var i=0;i<prices.length;i++){
        total += prices[i].innerHTML*counts[i].value;
      }

      $("#total").val("￥"+total);
    }

  </script>

  </head>

  <body>
   <div id="header" class="wrap">
  <div id="banner"></div>
  <div id="navbar">
    <div class="userMenu">
      <ul>
        <li class="current"><font color="BLACK">欢迎您，<strong>andy</strong></font>   </li>
        <li><a href="index.jsp">首页</a></li>
        <li><a href="orderlist.html">我的订单</a></li>
        <li><a href="show_cart.jsp">购物车</a></li>
        <li><a href="logout.jsp">注销</a></li>
      </ul>
    </div>
  </div>
</div>
<div id="content" class="wrap">
  <div class="list bookList">
    <form method="post" name="shoping" action="">
      <table>
        <tr class="title">
          <th class="view">图片预览</th>
          <th>书名</th>
          <th class="nums">数量</th>
          <th class="price">价格</th>
          <th class="nums">合计</th>
          <th class="nums">操作</th>
        </tr>

          <c:set var="total" value="0"></c:set>

          <c:forEach items="${cart}" var="book">  
          <tr>

            <td class="thumb">
            <img src="images/book/${book.value.image}" /></td>
            <td class="title">${book.value.bookname}</td>
            <td>
            <img src="images/edit_jian.png" width="12" height="12" 
              οnclick="jian(${book.value.bid})"/>

            <input id="count${book.value.bid}" readonly="readonly"
              value="${book.value.count}" size="2"/>

            <img src="images/edit_add.png" width="12" height="12" 
                οnclick="add(${book.value.bid})"/>
            </td>

            <td>￥
              <div id="price${book.value.bid}" >${book.value.price}</div>
            </td>
            <td>
              <input id="sum${book.value.bid}"
              value='<fmt:formatNumber 
                value="${book.value.count*book.value.price}"
                type="currency"></fmt:formatNumber>'
              />


              <c:set var="total" 
                value=
                "${total+book.value.count*book.value.price}"></c:set>

              <input type="hidden" name="items" value="10:2:31.6"/>
            </td>
            <td>
            <a href="DeleteCartItemServlet?bookid=${book.value.bid}">删除</a>
            </td>
          </tr>
          </c:forEach>

        <tr><td colspan="5">

      <div class="button">
        <h4>总价：
          <input id="total" 
            value='<fmt:formatNumber value="${total}" type="currency"></fmt:formatNumber>'
          />
        元</h4>
        <input type="hidden" id="hidden_total_price" name="hidden_total_price"/>
        <input class="input-chart" type="submit" name="submit" value="" />
      </div>
      </td></tr>

      </table>
    </form>
  </div>
</div>
</body>
  <div id="footer" class="wrap">
    网上书城 &copy; 版权所有
  </div>
</html>



</html>

package com.news.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//Dao工厂类
public class DaoFactory {
  private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  private static String url = "jdbc:sqlserver://localhost:1433;DatabaseName=News";
  private static String user = "sa";
  private static String pwd = "123@qwe";

//  private static String driver="com.mysql.jdbc.Driver";
//  private static String url="jdbc:mysql://localhost:3306/news";
//    private static String user = "root" ;   
//    private static String pwd = "admin" ;  

  // 1.公共方法是获得数据库链接对象
  public static Connection getConnection() {
    Connection con = null;
    try {
      Class.forName(driver);// 加,连
      con = DriverManager.getConnection(url, user, pwd);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return con;// 非void都需要return
  }
  // 2.关闭所有方法;有3个参数!,省代码了!!!
  public static void closeAll(ResultSet rs, Statement stmt, Connection con) {
    try {
      if (rs != null) {
        rs.close();
      }
      if (stmt != null) {
        stmt.close();
      }
      if (con != null) {
        con.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  // 3.setParams,用来设置预编译语句对象的?占位符的值;
  public static void setParams(PreparedStatement pstmt, Object[] params) {
    if (params == null) {
      return;
    }// return:直接返回，啥也不做;
    try {
      for (int i = 0; i < params.length; i++) {
        pstmt.setObject(i + 1, params[i]);
      }
    } catch (SQLException e) {// 有异常,加上去
      e.printStackTrace();
    }
  }

  // 4.做公共的更新方法，可以更新所有的基本sql语句;
  public int executeUpdate(String sql, Object[] params) {
    // 1.声明对象;是将来工作当中省内存;
    Connection con = null;
    PreparedStatement pstmt = null;
    int count = 0; // 增删改受影响的行数;

    try {
      con = this.getConnection();// 调用本类的方法；
      pstmt = con.prepareStatement(sql);// 建对象:预编译对象,?
      setParams(pstmt, params);// 调用设置?的方法，已经写过了!!!
      count = pstmt.executeUpdate();// 3.执行;
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      this.closeAll(null, pstmt, con);
    }
    return count;
  }

  // 5.执行查询方法;
  public static List executeQuery(String sql, Object[] params) {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int colCount = 0;
    ArrayList tableList=new ArrayList();//表集合

    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      setParams(pstmt, params);
      rs = pstmt.executeQuery();// 执行查询，结果给rs
      ResultSetMetaData rd = rs.getMetaData();// 获得元数据
      colCount = rd.getColumnCount();
      while (rs.next()) {
        ArrayList rowList = new ArrayList();//行集合
        for (int i = 1; i <= colCount; i++) {
          rowList.add(rs.getString(i));
        }
        tableList.add(rowList);       
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }finally{
      closeAll(rs,pstmt,con);
    }
    return tableList;
  }
}