1；数据表：
+-------+------------------+----------+------------------------------------------------------------------------------------+
| SP_NO | SP_NAME | SP_PRICE | SP_INFO |
+-------+------------------+----------+------------------------------------------------------------------------------------+
| cp001 | 计算机科学导论 | 79.80 | 计算机科学导论是计算机专业学生必须掌握的一门基础学科 |
| cp002 | 编译原理 | 67.88 | 编译原理作为计算机软件类课程中一门核心理论课程 |
| cp003 | 计算机网络 | 72.80 | 计算机网络作为网络通信，网络编程中一门基础课程 |
| cp004 | 数据库基础 | 62.98 | 数据库作为数据存储的一门数据库管理技术 |
| cp005 | java语言程序设计 | 74.68 | java作为一门面向对象语言，它的优点太多了 |
| cp006 | 算法导论 | 98.80 | 算法作为程序设计的核心，其地位举足轻重 |
| cp007 | 面向对象语言C++ | 70.80 | c++作为一门面向对象语言，全面兼容了C语言，又拥有面向对象的思想 |
| cp008 | 计算机组成原理 | 66.68 | 计算机组成原理是计算各个组件之间的深入分析 |
| cp009 | 计算机专业英语 | 56.88 | 计算机专业英语是计算机人员应该必须掌握的一门语言 |
| cp010 | C语言程序设计 | 63.68 | C语言作为世界上最为受欢迎的语言应用范围十分广泛 |
| cp011 | Python核心编程 | 72.78 | Python作为一门服务器端的脚本语言，在linux下运行十分常见 |
| cp012 | 数据结构 | 66.66 | 数据结构是软件开发行业最技术，最重要的一门课程，与其它课程交叉很多。必须要熟练掌握 |
2：VO类
package cn.com.vo;
public class VOShop {
private String SP_NO;//商品编号
private String SP_NAME;//商品名称
private Float SP_PRICE;//商品价格
private String SP_INFO;//商品信息
public String getSP_NO() {
return SP_NO;
}
public void setSP_NO(String sP_NO) {
SP_NO = sP_NO;
}
public String getSP_NAME() {
return SP_NAME;
}
public void setSP_NAME(String sP_NAME) {
SP_NAME = sP_NAME;
}
public Float getSP_PRICE() {
return SP_PRICE;
}
public void setSP_PRICE(Float sP_PRICE) {
SP_PRICE = sP_PRICE;
}
public String getSP_INFO() {
return SP_INFO;
}
public void setSP_INFO(String sP_INFO) {
SP_INFO = sP_INFO;
}
}
3：JDBC
package cn.com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/mydatabase";
private static String username = "root";
private static String password = "123456";
private static Connection conn=null;
public DBConnection()throws Exception
{
try
{
Class.forName(driver);
conn=DriverManager.getConnection(url,username,password);
}catch(Exception e)
{
e.printStackTrace();
}
}
public static Connection getConnectoion()
{
try {
new DBConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn)throws Exception
{
if(conn!=null)
{
try
{
conn.close();
}catch(Exception e)
{
e.printStackTrace();
}
}
}
}
4:DAO接口
package cn.com.dao.op;
import java.util.List;
import cn.com.vo.VOShop;
public interface IShopDAO {
public void addShop(VOShop vo);//添加商品
public void updateShop(VOShop vo);//更新商品
public void deleteShop(String SP_NO);//删除商品
public VOShop findById(String SP_NO);//查询商品
public List<VOShop> findAll();//查询所有
public int getCount();//统计查询
}
5：真实类(javaBean)
package cn.com.dao.impl;
import java.sql.Connection;
import java.util.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import cn.com.jdbc.DBConnection;
import cn.com.dao.op.IShopDAO;
import cn.com.vo.VOShop;
public class ShopDAOImpl implements IShopDAO{
private Connection conn=null;
private PreparedStatement pstmt=null;
private ResultSet rs=null;
@Override
public void addShop(VOShop vo) {
conn=DBConnection.getConnectoion();
String sql="insert into shop(SP_NO,SP_NAME,SP_PRICE,SP_INFO) values(?,?,?,?)";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, vo.getSP_NO());
pstmt.setString(2, vo.getSP_NAME());
pstmt.setFloat(3,vo.getSP_PRICE());
pstmt.setString(4, vo.getSP_INFO());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void updateShop(VOShop vo) {
conn=DBConnection.getConnectoion();
String sql="update shop set SP_NAME=?,SP_PRICE=?,SP_INFO=? where SP_NO=?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, vo.getSP_NO());
pstmt.setString(2, vo.getSP_NAME());
pstmt.setFloat(3,vo.getSP_PRICE());
pstmt.setString(4, vo.getSP_INFO());
pstmt.setString(5, vo.getSP_NO());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void deleteShop(String SP_NO) {
conn=DBConnection.getConnectoion();
String sql="delete form shop where SP_NO=?";
try {
pstmt.setString(1, SP_NO);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public VOShop findById(String SP_NO) {
VOShop vo=new VOShop();
conn=DBConnection.getConnectoion();
String sql="select * from shop where SP_NO=?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, SP_NO);
rs=pstmt.executeQuery();
vo.setSP_NO(rs.getString(1));
vo.setSP_NAME(rs.getString(2));
vo.setSP_PRICE(rs.getFloat(3));
vo.setSP_INFO(rs.getString(4));
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return vo;
}
@Override
public List<VOShop> findAll() {
List<VOShop> list=new ArrayList<VOShop>();
conn=DBConnection.getConnectoion();
String sql="select * from shop";
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next())
{
VOShop vo=new VOShop();
vo.setSP_NO(rs.getString(1));
vo.setSP_NAME(rs.getString(2));
vo.setSP_PRICE(rs.getFloat(3));
vo.setSP_INFO(rs.getString(4));
list.add(vo);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
@Override
public int getCount() {
int count=0;
conn=DBConnection.getConnectoion();
String sql="select Count(SP_NO) from shop";
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
rs.last();
count=rs.getRow();
} catch (SQLException e) {
e.printStackTrace();
}
finally
{
try {
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return count;
}
}
6;工厂类
package cn.com.dao.factory;
import cn.com.dao.impl.ShopDAOImpl;
import cn.com.dao.op.IShopDAO;
public class ShopDAOFactory {
public static IShopDAO getShopDAOImplInstance()
{
return new ShopDAOImpl();
}
}
7：jsp(显示层)
<%@ page contentType="text/html;charset=UTF-8" language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="cn.com.jdbc.DBConnection"%>
<%@ page import="cn.com.vo.VOShop" %>
<%@ page import="cn.com.dao.op.IShopDAO"%>
<%@ page import="cn.com.dao.impl.ShopDAOImpl"%>
<%@ page import="cn.com.dao.factory.ShopDAOFactory"%>
<!DOCTYPE HTML>
<html>
<head>
<title>商品列表</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">
</head>
<body bgcolor="CCCFFF">
<%
String s=null;
IShopDAO shopDao=ShopDAOFactory.getShopDAOImplInstance();
List<VOShop> list=shopDao.findAll();
%>
<table width="700" border="1" align="center">
<tr bgcolor="#dddddd">
<td align="left">商品序号</td>
<td align="left">商品编号</td>
<td align="left">商品名称</td>
<td align="left">商品价格</td>
<td align="left">商品信息</td>
<td align="left">购买情况</td>
</tr>
<%
Iterator<VOShop> iter=list.iterator();
int count=1;
while(iter.hasNext())
{
VOShop vo=iter.next();
session.setAttribute("car", vo);
%>
<tr>
<td align="left" width="100"><%=count%></td>
<td align="left"><%=vo.getSP_NO()%></td>
<td align="Left"><%=vo.getSP_NAME()%></td>
<td align="Left"><%=vo.getSP_PRICE()%></td>
<td align="Left"><%=vo.getSP_INFO()%></td>
<td align="center"><a href="buyShop.jsp?SP_NO=<%=vo.getSP_NO()%>">在线购买</a></td>
</tr>
<%
count++;
}
%>
<tr>
<td><a href="buyShop.jsp?SP_NO=<%=s%>">查看购物车>></a></td>
</tr>
</table>
</body>
</html>