在tomcat的context.xml中配置如下:
<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<!-- Uncomment this to enable Comet connection tacking (provides events
on session expiration as well as webapp lifecycle) -->
<!--
<Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
-->
<Resource name="jdbc/myDataSource" auth="Container"
type="javax.sql.DataSource"
maxActive="50" maxIdle="10" maxWait="5000"
username="root" password="1234"
driverClassName="org.gjt.mm.mysql.Driver"
url="jdbc:mysql://localhost/eshop" />
</Context>
在web项目中的web.xml中配置如下
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/myDataSource</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
在数据库中做数据库和表
Eshop.sql文件的内容如下:
/*!40101 SET CHARACTER SET 'utf8' */;
DROP DATABASE IF EXISTS eshop;
CREATE DATABASE eshop /*!40100 DEFAULT CHARACTER SET utf8 */;
USE eshop;
/*!40101 SET CHARACTER SET 'utf8' */;
CREATE TABLE cart1(
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=myisam DEFAULT CHARSET=utf8;
CREATE TABLE shop1(
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=myisam DEFAULT CHARSET=utf8;
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');
Index.jsp内容如下:
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="javax.naming.*" %>
<html>
<head>
<title>在线购物</title>
</head>
<%
//装载MySQL5.5的JDBC驱动
//Class.forName("com.mysql.jdbc.Driver").newInstance();
//建立数据库连接
//String url ="jdbc:mysql://localhost:3306/eshop?user=root&password=1234&useUnicode=true&characterEncoding=utf8";
//Connection conn= DriverManager.getConnection(url);
//建立一个Statement对象,用于执行SQL语句
Context ctx=new InitialContext();
DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/myDataSource");
Connection conn=ds.getConnection();
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>
<a href="buy.jsp?op=clear">清空购物车</a>
</body>
</html>
Buy.jsp
<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="javax.naming.*" %>
<%
//装载MySQL5.5的JDBC驱动
//Class.forName("com.mysql.jdbc.Driver").newInstance();
//建立数据库连接
//String url ="jdbc:mysql://localhost:3306/eshop?user=root&password=1234&useUnicode=true&characterEncoding=gb2312";
//Connection conn= DriverManager.getConnection(url);
Context ctx=new InitialContext();
DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/myDataSource");
Connection conn=ds.getConnection();
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");
}
%>
Cart.jsp
<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="javax.naming.*" %>
<html>
<head>
<title>我的购物车</title>
</head>
<%
//装载MySQL5.5的JDBC驱动
//Class.forName("com.mysql.jdbc.Driver").newInstance();
//建立数据库连接
//String url ="jdbc:mysql://localhost:3306/eshop?user=root&password=1234&useUnicode=true&characterEncoding=gb2312";
//Connection conn= DriverManager.getConnection(url);
//建立一个Statement对象,用于执行SQL语句
Context ctx=new InitialContext();
DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/myDataSource");
Connection conn=ds.getConnection();
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>
<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>
Web项目的文件结构