如何实现购物车功能(购物车在数据库中)?

上一篇文章提到,将购物车放入session中的方法,这里来帮大家讲购物车放在数据库中,那么到底如何实现呢?
同样,购物车放在数据库中也应该实现8中操作:
1.向购物车添加商品,(首先,应该判断该商品是否在购物车中已经存在,若存在,该商品数量+1,若不存在,则将该商品加入购物车并数量设置为1)
2.修改商品的数量(请求一个servlet,并根据该商品的某个唯一属性修改其数量)
3.删除商品(通过请求servlet并根据要删除商品的唯一属性将其删除)
4.清空购物车(请求servlet将购物车清空)
5.获得商品的初始价格
6.获得商品的会员价格
7.获得优惠价格
8.获得购物车中的所有商品
在数据库中为购物车建表,如下图:
在这里插入图片描述
在这里插入图片描述
具体代码如下(以书为例):
Gwc实体类(这里将购物车中药显示的属性封装成Gwc实体类):

  public class Gwc {
    	private int gwcId;
    	private int custId;
    	private int bookId;
    	private String bookName;
    	private String smallImg;
    	private float price;
    	private float hyPrice;
    	private int num;
    	
    	public Gwc() {
    		// TODO Auto-generated constructor stub
    	}
    
    	public Gwc(int gwcId, int custId, int bookId, String bookName,
    			String smallImg, float price, float hyPrice, int num) {
    		super();
    		this.gwcId = gwcId;
    		this.custId = custId;
    		this.bookId = bookId;
    		this.bookName = bookName;
    		this.smallImg = smallImg;
    		this.price = price;
    		this.hyPrice = hyPrice;
    		this.num = num;
    	}
    
    	public Gwc(int custId, int bookId, String bookName, String smallImg,
    			float price, float hyPrice, int num) {
    		super();
    		this.custId = custId;
    		this.bookId = bookId;
    		this.bookName = bookName;
    		this.smallImg = smallImg;
    		this.price = price;
    		this.hyPrice = hyPrice;
    		this.num = num;
    	}
    
    	public int getGwcId() {
    		return gwcId;
    	}
    
    	public void setGwcId(int gwcId) {
    		this.gwcId = gwcId;
    	}
    
    	public int getCustId() {
    		return custId;
    	}
    
    	public void setCustId(int custId) {
    		this.custId = custId;
    	}
    
    	public int getBookId() {
    		return bookId;
    	}
    
    	public void setBookId(int bookId) {
    		this.bookId = bookId;
    	}
    
    	public String getBookName() {
    		return bookName;
    	}
    
    	public void setBookName(String bookName) {
    		this.bookName = bookName;
    	}
    
    	public String getSmallImg() {
    		return smallImg;
    	}
    
    	public void setSmallImg(String smallImg) {
    		this.smallImg = smallImg;
    	}
    
    	public float getPrice() {
    		return price;
    	}
    
    	public void setPrice(float price) {
    		this.price = price;
    	}
    
    	public float getHyPrice() {
    		return hyPrice;
    	}
    
    	public void setHyPrice(float hyPrice) {
    		this.hyPrice = hyPrice;
    	}
    
    	public int getNum() {
    		return num;
    	}
    
    	public void setNum(int num) {
    		this.num = num;
    	}
    }

购物车接口:

import java.util.List;

import com.jinzhi.entity.Gwc;

public interface IGwcDAO {
	
	public int add(int custId, Gwc gwc);
	
	public int updateNum(int custId, int bookId, int num);
	
	public int delete(int custId, int bookId);
	
	public int clear(int custId);
	
	public Gwc findById(int custId, int bookId);
	
	public float getOldPrices(int custId);
	
	public float getHyPrices(int custId);
	
	public float getYhPrice(int custId);
	
	public List<Gwc> getAllItems(int custId);
}

购物车接口的实现:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jinzhi.DAO.IGwcDAO;
import com.jinzhi.db.DBManager;
import com.jinzhi.entity.Gwc;

public class GwcDAOImpl implements IGwcDAO{
	private DBManager dbManager = new DBManager();
	@Override
	public int add(int custId, Gwc gwc) {
		String sql = "insert into gwc(custId,bookId,bookName,smallImg,price,hyPrice,num)" +
				"values("+custId+","+gwc.getBookId()+",'"+gwc.getBookName()+"','"+gwc.getSmallImg()+"'," +
						" "+gwc.getPrice()+", "+gwc.getHyPrice()+","+gwc.getNum()+")";
		System.out.println(sql);
		return dbManager.update(sql);
	}

	@Override
	public int updateNum(int custId, int bookId, int num) {
		String sql = "update gwc set num = "+num+" where custId = "+custId+" and bookId = "+bookId+"";
		System.out.println(sql);
		return dbManager.update(sql);
	}

	@Override
	public int delete(int custId, int bookId) {
		String sql = "delete from gwc where custId = "+custId+" and bookId = "+bookId+" ";
		return dbManager.update(sql);
	}

	@Override
	public int clear(int custId) {
		String sql = "delete from gwc where custId = "+custId+"";
		return dbManager.update(sql);
	}

	@Override
	public float getOldPrices(int custId) {
		float price = 0;
		String sql = "select sum(price) from gwc where custId = "+custId+"";
		ResultSet rs = dbManager.query(sql);
		try {
			if (rs.next()) {
				price = rs.getFloat(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return price;
	}

	@Override
	public float getHyPrices(int custId) {
		float price = 0;
		String sql = "select sum(hyPrice) from gwc where custId = "+custId+"";
		ResultSet rs = dbManager.query(sql);
		try {
			if (rs.next()) {
				price = rs.getFloat(1);
				System.out.println(price);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return price;
	}

	@Override
	public float getYhPrice(int custId) {
		return this.getOldPrices(custId) - this.getHyPrices(custId);
	}

	@Override
	public List<Gwc> getAllItems(int custId) {
		List<Gwc> list = new ArrayList<Gwc>();
		String sql = "select * from gwc where custId="+custId+"";
		ResultSet rs = dbManager.query(sql);
		try {
			while (rs.next()) {
				Gwc gwc = new Gwc(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getString(4), rs.getString(5), rs.getFloat(6), rs.getFloat(7), rs.getInt(8));
				list.add(gwc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public Gwc findById(int custId, int bookId) {
		Gwc gwc = null;
		String sql = "select * from gwc where custId = "+custId+" and bookId = "+bookId+"";
		ResultSet rs = dbManager.query(sql);
		try {
			if (rs.next()) {
				gwc = new Gwc(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getString(4), rs.getString(5), rs.getFloat(6), rs.getFloat(7), rs.getInt(8));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return gwc;
	}
}

修改购物车某种商品的数量的servlet:

import java.io.IOException;
import java.io.PrintWriter;

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.jinzhi.DAO.IGwcDAO;
import com.jinzhi.DAO.impl.GwcDAOImpl;
import com.jinzhi.entity.CustomerInfo;

public class UpdateGwcNumServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		int bookId = Integer.parseInt(request.getParameter("bookId"));
		int num = Integer.parseInt(request.getParameter("num"));
		IGwcDAO gwcDAO = new GwcDAOImpl();
		HttpSession session = request.getSession();
		CustomerInfo customerInfo = (CustomerInfo) session.getAttribute("customerInfo");
		gwcDAO.updateNum(customerInfo.getCustId(), bookId, num);
		response.sendRedirect("InitGwcListServlet");
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	}
}

InitGwcListServlet的servlet代码:

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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.jinzhi.DAO.IGwcDAO;
import com.jinzhi.DAO.impl.GwcDAOImpl;
import com.jinzhi.entity.CustomerInfo;
import com.jinzhi.entity.Gwc;

public class InitGwcListServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		IGwcDAO gwcDAO = new GwcDAOImpl();
		HttpSession session = request.getSession();
		CustomerInfo customerInfo = (CustomerInfo) session.getAttribute("customerInfo");
		int custId = customerInfo.getCustId();
		List<Gwc> list = gwcDAO.getAllItems(custId);
		float price = gwcDAO.getOldPrices(custId);
		float hyPrice = gwcDAO.getHyPrices(custId);
		float yhPrice = gwcDAO.getYhPrice(custId);
		
		session.setAttribute("price", price);
		session.setAttribute("hyPrice", hyPrice);
		session.setAttribute("yhPrice", yhPrice);
		session.setAttribute("list", list);
		response.sendRedirect("gwc.jsp");
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	}
}

向购物车新增商品的servlet:

import java.io.IOException;
import java.util.List;

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.jinzhi.DAO.IBookInfoDAO;
import com.jinzhi.DAO.IGwcDAO;
import com.jinzhi.DAO.impl.BookInfoDAOImpl;
import com.jinzhi.DAO.impl.GwcDAOImpl;
import com.jinzhi.entity.BookInfo;
import com.jinzhi.entity.CustomerInfo;
import com.jinzhi.entity.Gwc;

public class GwcItemAddServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		int bookId = Integer.parseInt(request.getParameter("bookId"));
		HttpSession session = request.getSession();
		CustomerInfo customerInfo = (CustomerInfo) session.getAttribute("customerInfo");
		if (customerInfo == null) {
			response.sendRedirect("qtlogin.jsp");
			return;
		}
		System.out.println(customerInfo);
		IGwcDAO gwcDAO = new GwcDAOImpl();
		IBookInfoDAO bookInfoDAO = new BookInfoDAOImpl();
		int custId = customerInfo.getCustId();
		String custName = customerInfo.getCustName();
		BookInfo bookInfo = bookInfoDAO.findById(bookId);
		Gwc gwc = gwcDAO.findById(custId, bookId);
		if (gwc == null) {
			Gwc gwc2 = new Gwc(custId, bookId, bookInfo.getBookName(), bookInfo.getSmallImg(), bookInfo.getPrice(), bookInfo.getHyPrice(), 1);
			gwcDAO.add(custId, gwc2);
		} else {
			int num = gwc.getNum() + 1;
			gwcDAO.updateNum(custId, bookId, num);
		}
		response.sendRedirect("InitGwcListServlet");
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	}
}

删除购物车中某种商品的servlet:

import java.io.IOException;
import java.io.PrintWriter;

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.jinzhi.DAO.IGwcDAO;
import com.jinzhi.DAO.impl.GwcDAOImpl;
import com.jinzhi.entity.CustomerInfo;

public class GwcDeleteServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		int id = Integer.parseInt(request.getParameter("id"));
		IGwcDAO gwcDAO = new GwcDAOImpl();
		HttpSession session = request.getSession();
		CustomerInfo customerInfo = (CustomerInfo) session.getAttribute("customerInfo");
		gwcDAO.delete(customerInfo.getCustId(), id);
		response.sendRedirect("InitGwcListServlet");
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	}
}

清空购物车的servlet:

import java.io.IOException;
import java.io.PrintWriter;

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.jinzhi.DAO.IGwcDAO;
import com.jinzhi.DAO.impl.GwcDAOImpl;
import com.jinzhi.entity.CustomerInfo;

public class GwcClearServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		IGwcDAO gwcDAO = new GwcDAOImpl();
		HttpSession session = request.getSession();
		CustomerInfo customerInfo = (CustomerInfo) session.getAttribute("customerInfo");
		gwcDAO.clear(customerInfo.getCustId());
		response.sendRedirect("gwc.jsp");
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	}
}

customerInfo实体类:

public class CustomerInfo {
	private int custId;
	private String custName;
	private String pwd;
	private String email;
	public CustomerInfo(int custId, String custName, String pwd, String email) {
		super();
		this.custId = custId;
		this.custName = custName;
		this.pwd = pwd;
		this.email = email;
	}
	public CustomerInfo() {
		super();
		// TODO Auto-generated constructor stub
	}
	public CustomerInfo(String custName, String pwd, String email) {
		super();
		this.custName = custName;
		this.pwd = pwd;
		this.email = email;
	}
	public int getCustId() {
		return custId;
	}
	public void setCustId(int custId) {
		this.custId = custId;
	}
	public String getCustName() {
		return custName;
	}
	public void setCustName(String custName) {
		this.custName = custName;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	@Override
	public String toString() {
		return "CustomerInfo [custId=" + custId + ", custName=" + custName
				+ ", pwd=" + pwd + ", email=" + email + "]";
	}
}

添加上DBmanager代码,如下:
DBmanager代码:
注意:此处代码中的Class.forName("")中的值和加载驱动程序的url 值视你所使用的数据库而定!还有你的数据库名称和密码1

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class DBManager {
	private Connection con;
	private Statement sta;
	private ResultSet rs;
	
	static {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 加载驱动程序
	 */
	
	public Connection getConnection(){
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=yun4jbookSY1";
		try {
			con = DriverManager.getConnection(url, "sa", "sa");
			sta = con.createStatement();
			System.out.println("链接成功");
		} catch (SQLException e) {
			System.out.println("连接失败");
			e.printStackTrace();
		}
		
		return con;
	}
	
	/*public DBManager() {
		List<Connection> list = new ArrayList<Connection>();
		for (int i = 0; i < 5; i++) {
			list.add(this.getConnection());
		}
		this.con = list.get(0);
	}*/
	public int update(String sql){
		int row = -1;
		con = getConnection();
		try {
			row = sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			this.close();
		}
		return row;
	}
	
	public ResultSet query(String sql){
		con = getConnection();
		try {
			rs = sta.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	
	public void close(){
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (sta != null) {
				sta.close();
				sta = null;
			}
			if (con != null) {
				con.close();
				con = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

自己刚开始运营公众号啦,本人目前就职于某bank,感兴趣的可关注我的公众号,欢迎交流!

image.png

  • 30
    点赞
  • 236
    收藏
    觉得还不错? 一键收藏
  • 41
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 41
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值