JSP与JDBC购物车例子源码



JSP与JDBC购物车例子源码。

1.index.jsp

<%@ page language=”java” import=”java.util.*” pageEncoding=”gb2312″%>
<%@ page import=”java.sql.*”%>

<html>
  <head>
   <meta http-equiv=”content-type” content=”text/html; charset=UTF-8″>
    <title>在线购物</title>
  </head>
  <%
 //装载MySQL5.0的JDBC驱动
 Class.forName(“com.mysql.jdbc.Driver”).newInstance();
 //建立数据库连接
 String url =”jdbc:mysql://localhost:3306/eshop?user=root&password=&useUnicode=true&characterEncoding=gb2312″;
 Connection conn= DriverManager.getConnection(url);
 //建立一个Statement对象，用于执行SQL语句
 Statement stat = conn.createStatement();
 //执行查询并得到查询结果
 String sql = “select * from shop”;
 ResultSet rs = stat.executeQuery(sql); 
  %>  
  <body>
    <h2>在线购物</h2>
    <hr>
 <table border=”1″ width=”600″ >
  <tr bgcolor=”#dddddd”>
   <td align=”center” width=”80″ >商品缩图</td>
   <td align=”center”>商品摘要</td>
   <td align=”center” width=”100″>在线购买</td>
  </tr>
  <%
   String bm,name,price,info,img;
   //将查询结果集中的记录输出到页面上
   while (rs.next()){
    //从当前记录中读取各字段的值
    img = rs.getString(“SP_PIC”).trim();
    bm = rs.getString(“SP_NO”).trim();
    name = rs.getString(“SP_NAME”).trim();
    price = rs.getString(“SP_PRICE”).trim();
    info = rs.getString(“SP_INFO”).trim();
    
    out.println(“<tr>”);
    out.println(“<td><img src=’”+ img +”‘ border=0 height=60 width=60></td>”);
    out.println(“<td>”);
    out.println(“商品编号：”+ bm +”<br>”);
    out.println(“商品名称：”+ name +”<br>”);
    out.println(“商品价格：”+ price +”元<br>”);
    out.println(“商品简介：”+ info +”<br>”);
    out.println(“</td>”);
    out.println(“<td><a href=’buy.jsp?op=add&bm=”+bm+”‘>放入购物车</a></td>”);
    out.println(“</tr>”);
   }
  %>  
 </table>
 <br>
 <a href=”cart.jsp”>查看购物车</a>&nbsp;&nbsp;
 <a href=”buy.jsp?op=clear”>清空购物车</a>       
  </body>
</html>

2.cart.jsp

<%@ page language=”java” import=”java.util.*” pageEncoding=”gb2312″%>
<%@ page import=”java.sql.*”%>

<html>
  <head>
    <title>我的购物车</title>
  </head>
  <%
 //装载MySQL5.0的JDBC驱动
 Class.forName(“com.mysql.jdbc.Driver”).newInstance();
 //建立数据库连接
 String url =”jdbc:mysql://localhost:3306/eshop?user=root&password=&useUnicode=true&characterEncoding=gb2312″;
 Connection conn= DriverManager.getConnection(url);
 //建立一个Statement对象，用于执行SQL语句
 Statement stat = conn.createStatement();
 //执行查询并得到查询结果
 String sql = “select * from cart”;
 ResultSet rs = stat.executeQuery(sql); 
  %>  
  <body>
    <h2>我的购物车</h2>
    <hr>
 <table border=”1″ width=”600″>
  <tr bgcolor=”#dddddd”>
   <td align=”center” width=”80″>商品名称</td>
   <td align=”center”>商品单价</td>
   <td align=”center” width=”100″>购买数量</td>
   <td align=”center” width=”100″>金额</td>
   <td align=”center” width=”100″>编辑</td>
  </tr>
  <%
   String id,name,price,num,count;
   //将查询结果集中的记录输出到页面上
   while (rs.next()){
    //从当前记录中读取各字段的值
    id = rs.getString(“ID”).trim();
    name = rs.getString(“SP_NAME”).trim();
    price = rs.getString(“SP_PRICE”).trim();
    num = rs.getString(“BUY_NUM”).trim();
    count = rs.getString(“COUNT”).trim();
    
    out.println(“<tr>”);
    out.println(“<td>”+ name +”</td>”);
    out.println(“<td>”+ price +”</td>”);
    out.println(“<td><input type=text value=”+ num +” onChange=\”updateNum(‘”+id+”‘,this.value,’”+price+”‘)\”></td>”);
    out.println(“<td>”+ count +”</td>”);
    out.println(“<td><a href=’buy.jsp?op=del&id=”+id+”‘>退回商品架</a></td>”);
    out.println(“</tr>”);
   }
  %>  
 </table>
 <br>
 <a href=”index.jsp”>继续购物</a>&nbsp;&nbsp;
 <a href=”buy.jsp?op=clear”>清空购物车</a>        
  </body>
  <script language=”javascript”>
   function updateNum(id,num,price){
    var url = “buy.jsp?op=update&id=”+id+”&num=”+num+”&price=”+price;
    window.location = url;
   }
  </script> 
</html>

3.buy.jsp


<%@ page language=”java” import=”java.util.*” pageEncoding=”gb2312″%>
<%@ page import=”java.sql.*”%>
<%
 //装载MySQL5.0的JDBC驱动
 Class.forName(“com.mysql.jdbc.Driver”).newInstance();
 //建立数据库连接
 String url =”jdbc:mysql://localhost:3306/eshop?user=root&password=&useUnicode=true&characterEncoding=gb2312″;
 Connection conn= DriverManager.getConnection(url);
 PreparedStatement pstat = null;
 ResultSet rs = null;
 
 String sql = null;
 String op = request.getParameter(“op”);
 
 //将商品放入购物车
 if (op.equals(“add”)){
  //从商品库中取出所选购商品的数据
  String bm = request.getParameter(“bm”);
  String name=null,price=null;
  sql = “select * from shop where SP_NO=?”;
  pstat = conn.prepareStatement(sql);
  pstat.setString(1,bm);
  rs = pstat.executeQuery();
  if (rs.next()){
   name = rs.getString(“SP_NAME”).trim();
   price = rs.getString(“SP_PRICE”).trim();
  }
  rs.close();
  pstat.close();
  
  //将所选购商品加入到购物车中
  sql = “insert into cart(SP_NAME,SP_PRICE,BUY_NUM,COUNT) values(?,?,?,?)”;
  pstat = conn.prepareStatement(sql);
  pstat.setString(1,name);
  pstat.setString(2,price);
  pstat.setInt(3,1);
  pstat.setString(4,price);
  pstat.executeUpdate();
  pstat.close();
  
  //关闭数据库连接
  conn.close();
  
  //重定向到购物车页面
  response.sendRedirect(“cart.jsp”);
 }
 
 //更改商品的数量
 if (op.equals(“update”)){
  int id = Integer.parseInt(request.getParameter(“id”));
  int num = Integer.parseInt(request.getParameter(“num”));
  double price=Double.parseDouble(request.getParameter(“price”));
  sql = “update cart set BUY_NUM = ?, COUNT = ? where ID=?”;
  pstat = conn.prepareStatement(sql);
  pstat.setInt(1,num);
  pstat.setString(2,new Double(price*num).toString());
  pstat.setInt(3,id);
  pstat.executeUpdate();
  pstat.close();
  
  //关闭数据库连接
  conn.close();
  
  //重定向到购物车页面
  response.sendRedirect(“cart.jsp”);
 } 
 
 //将商品退回到商品架(将商品从购物车中删除)
 if (op.equals(“del”)){
  int id = Integer.parseInt(request.getParameter(“id”));
  sql = “delete from cart where ID=?”;
  pstat = conn.prepareStatement(sql);
  pstat.setInt(1,id);
  pstat.executeUpdate();
  pstat.close();
  
  //关闭数据库连接
  conn.close();
  
  //重定向到购物车页面
  response.sendRedirect(“cart.jsp”);
 }
 
 //清空购物车
 if (op.equals(“clear”)){
  sql = “delete from cart”;
  pstat = conn.prepareStatement(sql);
  pstat.executeUpdate();
  pstat.close();
  
  //关闭数据库连接
  conn.close();
  
  //重定向到购物车页面
  response.sendRedirect(“cart.jsp”);
 }
%>

4.sql文件

;

DROP DATABASE IF EXISTS `eshop`;
CREATE DATABASE `eshop` ;
USE `eshop`;

;

CREATE TABLE `cart` (
  `ID` int(11) NOT NULL auto_increment,
  `SP_NAME` varchar(40) default NULL,
  `SP_PRICE` decimal(8,2) default NULL,
  `BUY_NUM` int(11) default NULL,
  `COUNT` decimal(9,2) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

CREATE TABLE `shop` (
  `SP_NO` char(10) NOT NULL,
  `SP_NAME` varchar(40) default NULL,
  `SP_PRICE` decimal(8,2) default NULL,
  `SP_INFO` varchar(200) default NULL,
  `SP_PIC` varchar(50) default NULL,
  PRIMARY KEY  (`SP_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES (‘SP0001′,’音频卡’,950,’MAYA44 V3是具有24bit/96KHz专业指标的4进4出专业数字音频卡’,'pic/pic01.jpg’);
INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES (‘SP0002′,’合成器’,4980,’PK-6以较低的价格为你提供E-MU最新的Proteus声音和高质量的键盘’,'pic/pic02.jpg’);
INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES (‘SP0003′,’MP3′,450,’功能提示支持USB2.0高速传输’,'pic/pic03.jpg’);
INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES (‘SP0004′,’监听音箱’,3800,’PX-T6A是强大的独立双功放、近场监听系统的理想选择’,'pic/pic04.jpg’);
INSERT INTO `shop` (`SP_NO`,`SP_NAME`,`SP_PRICE`,`SP_INFO`,`SP_PIC`) VALUES (‘SP0005′,’摄像机’,29800,’3CCD\传感器总象素：112万像素’,'pic/pic05.jpg’);