课程设计报告-购物网站的后台管理.docx

软件学院 课程设计 项 目 文 档 课程设计项目： 购物网站的后台管理 班 级： 软件1307 学 号： 20131613243 姓 名： 刘帅 上 课 时 间：2015.9. 82015.9.18 实训教师 (签名) 辅导教师（签名） 1 实训每日报告第一天Jdbc：Test1：package com.sun.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class Test1 public static void main(String args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException /一个基本JDBC操作步骤String className = com.mysql.jdbc.Driver;String url=jdbc:mysql:/:3306/goodsdb?useUnicode=true&characterEncoding=utf8;/1 创建数据库连接对象java.sql.Connection他是一个规范。具体的实现是由各数据库厂商提供的。Class.forName(className).newInstance();/类加载。加载数据库的驱动类。Connection conn = DriverManager.getConnection(url, root, 123456) ;/System.out.println(conn);/2 操作，发送SQL。需要使用PreparedStatement对象。String sql =select * from goods inner join types on goods.goodsType = types.id;PreparedStatement pstat = conn.prepareStatement(sql);/如果操作的增删改与查询执行的方法不同/pstat.executeUpdate();/3 接收查询结果,接收数据库查询 结果的对象叫结果集对象。rs，二维表格ResultSet rs = pstat.executeQuery();while(rs.next()System.out.println(商品编号：+rs.getInt(id);System.out.println(rs.getString(goodsName);System.out.println(rs.getFloat(goodsPrice);System.out.println(rs.getInt(goodsNum);System.out.println(rs.getInt(goodsType);System.out.println(rs.getString(typeName);System.out.println(-);/4 关闭conn.close();System.out.println(end);Test2：package com.sun.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class Test2 public static void main(String args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException /一个基本JDBC操作步骤String className = com.mysql.jdbc.Driver;String url=jdbc:mysql:/:3306/goodsdb?useUnicode=true&characterEncoding=utf8;/1 创建数据库连接对象java.sql.Connection他是一个规范。具体的实现是由各数据库厂商提供的。Class.forName(className).newInstance();/类加载。加载数据库的驱动类。Connection conn = DriverManager.getConnection(url, root, 123456) ;/System.out.println(conn);/2 操作，发送SQL。需要使用PreparedStatement对象。/在Pstat中可以使用参数。在SQL语句中使用？号占位在String sql =insert into goods(goodsName,goodsPrice,goodsNum,goodsType) values(?,?,?,?);PreparedStatement pstat = conn.prepareStatement(sql);/如果操作的增删改与查询执行的方法不同pstat.setString(1, 手电筒);pstat.setFloat(2, 15.2f);pstat.setInt(3, 100);pstat.setInt(4, 2);int i = pstat.executeUpdate();System.out.println(操作影响了+i+行);/4 关闭conn.close();System.out.println(end);Utils：package com.sun.utils;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;/* * 数据库连接的工具类 封装了数据库连接的创建和关闭 的同一个线程中没关闭数据库连接之间获得数据库连接对象都是同一个。 * * author Boss * */public class ConnectionUtils /本地线程对象。一个线程中只有一个本地线程对象，本地线程对象中可以存放一个对象。private static ThreadLocal threadLocal = new ThreadLocal();private static final String CLASS_NAME=com.mysql.jdbc.Driver;private static final String URL = jdbc:mysql:/:3306/goodsdb?useUnicode=true&characterEncoding=utf8;statictry / 类加载。加载数据库的驱动类。Class.forName(CLASS_NAME).newInstance(); catch (InstantiationException | IllegalAccessException| ClassNotFoundException e) e.printStackTrace();public static Connection getConnection() throws SQLException Connection conn = threadLocal.get();/先从本地线程对象取数据库连接对象if(conn = null | conn.isClosed()/如果本地线程中没有连接对象/创建连接System.out.println(创建数据库连接！);conn = DriverManager.getConnection(URL, root, 123456);/将连接放到本地线程threadLocal.set(conn);return conn;public static void closeConnection()try Connection conn = getConnection();if(conn != null & !conn.isClosed()conn.close(); catch (SQLException e) e.printStackTrace();finallythreadLocal.set(null);Test：package com.test;import java.sql.SQLException;import com.sun.utils.ConnectionUtils;public class Test public static void main(String args) throws SQLException / TODO Auto-generated method stubSystem.out.println(ConnectionUtils.getConnection();System.out.println(ConnectionUtils.getConnection();ConnectionUtils.closeConnection();System.out.println(ConnectionUtils.getConnection();第二天DBCPTest：package com.test;import java.sql.Connection;import com.utils.ConnectionUtils;public class Test public static void main(String args) throws Exception / TODO Auto-generated method stubConnection conn = ConnectionUtils.getConnection();System.out.println(conn);ConnectionUtils.closeConnection();Utils：package com.utils;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;import javax.sql.DataSource;import mons.dbcp.BasicDataSourceFactory;/* * 在这个类中连接对象的管理 1 获取连接对象 2 释放连接对象 * * author Boss */public class ConnectionUtils / 加载资源文件的内容private static Properties properties = new Properties();/ 加载资源文件的对象static / is对象是一个输入流对象try / is对象负责连接perties文件InputStream is = ConnectionUtils.class.getResourceAsStream(/perties);/ 将资源文件的内容加载到properties对象中properties.load(is);/ 关闭is流对象is.close(); catch (IOException e) / TODO Auto-generated catch blocke.printStackTrace();/ 加载资源文件 private static Connection conn = null;/* * 获取数据库连接的方法 * return 数据库连接对象 * throws Exception */public static Connection getConnection() throws Exception / 连接的创建是有条件的？连接不存在，或连接已经关闭if (conn = null | conn.isClosed() / ds是数据源对象。在这个对象中管理和维护了大量数据库连接对象DataSource ds = BasicDataSourceFactory.createDataSource(properties);conn = ds.getConnection();return conn;/* * 释放数据库连接的方法 */public static void closeConnection() try if (conn != null & !conn.isClosed() conn.close(); catch (SQLException e) e.printStackTrace();Webroot： base href= My JSP index.jsp starting page !- This is my JSP page. 系统当前时间： 第三天Com.daogoodsDAO:package com.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import com.domain.Goods;import com.domain.Types;import com.utils.ConnectionUtils;/* * 封装针对Goods表CURD的操作 * * author Boss */public class GoodsDAO /* * 查询所有商品的方法 * * return 所有商品信息的集合对象 * throws Exception */public List findAll() throws Exception try List goodsList = new ArrayList();Connection conn = ConnectionUtils.getConnection();String sql = select * from goods inner join types on goods.goodsType = types.id;PreparedStatement pstat = conn.prepareStatement(sql);ResultSet rs = pstat.executeQuery();while (rs.next() Goods goods = new Goods();goods.setId(rs.getInt(id);goods.setGoodsName(rs.getString(goodsName);goods.setGoodsPrice(rs.getFloat(goodsPrice);goods.setGoodsNum(rs.getInt(goodsNum);Types types = new Types();types.setId(rs.getInt(goodsType);types.setTypeName(rs.getString(typeName);goods.setTypes(types);goodsList.add(goods);return goodsList; finally ConnectionUtils.closeConnection();Com.domain:Goods:package com.domain;/* * 封装goods表的数据的对象 * author Boss * */public class Goods private int id;private String goodsName;private float goodsPrice;private int goodsNum;private Types types;/对应商品的类型public Goods() / TODO Auto-generated constructor stubpublic int getId() return id;public void setId(int id) this.id = id;public String getGoodsName() return goodsName;public void setGoodsName(String goodsName) this.goodsName = goodsName;public float getGoodsPrice() return goodsPrice;public void setGoodsPrice(float goodsPrice) this.goodsPrice = goodsPrice;public int getGoodsNum() return goodsNum;public void setGoodsNum(int goodsNum) this.goodsNum = goodsNum;public Types getTypes() return types;public void setTypes(Types types) this.types = types;Types:package com.domain;/* * 封装数据库中types表中内容的对象 * author Boss */public class Types private int id;private String typeName;public Types() public int getId() return id;public void setId(int id) this.id = id;public String getTypeName() return typeName;public void setTypeName(String typeName) this.typeName = typeName;Webroot:Index: My JSP index.jsp starting page !- This is my JSP page. ab1212 Show01:显示所有商品的第一版 Show02:显示所有商品的第二版-使用表格显示数据商品编号商品名称商品类型Show03:显示所有商品商品编号商品名称商品价格商品数量商品类型%GoodsDAO goodsDAO = new GoodsDAO();List goodsList = goodsDAO.findAll();for (Goods goods : goodsList) %第四天Com.dao:GoodsDAO:package com.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import com.domain.Goods;import com.domain.Types;import com.utils.ConnectionUtils;/* * 封装针对Goods表CURD的操作 * * author Boss * */public class GoodsDAO /* * 按商品名称查询 * * param goodsName * 商品名称 * return 查询到的商品对象，如果商品不存在返回null * throws Exception */public Goods findByGoodsName(String goodsName) throws Exception try Connection conn = ConnectionUtils.getConnection();String sql = select * from goods inner join types on goods.goodsType = types.id where goodsName = ?;PreparedStatement pstat = conn.prepareStatement(sql);pstat.setString(1, goodsName);ResultSet rs = pstat.executeQuery();if (rs.next() Goods goods = new Goods();goods.setId(rs.getInt(id);goods.setGoodsName(rs.getString(goodsName);goods.setGoodsPrice(rs.getFloat(goodsPrice);goods.setGoodsNum(rs.getInt(goodsNum);goods.setGoodsStatus(rs.getInt(goodsStatus);Types types = new Types();types.setId(rs.getInt(goodsType);types.setTypeName(rs.getString(typeName);goods.setTypes(types);return goods; else return null; finally ConnectionUtils.closeConnection();/* * 保存新商品 * * param goods * 要保存的新商品对象 * return true表示保存成功 * throws Exception */public boolean save(Goods goods) throws Exception try Connection conn = ConnectionUtils.getConnection();String sql = insert into goods(goodsName,goodsPrice,goodsNum,goodsStatus,goodsType) values(?,?,?,?,?);PreparedStatement pstat = conn.prepareStatement(sql);pstat.setString(1, goods.getGoodsName();pstat.setFloat(2, goods.getGoodsPrice();pstat.setInt(3, goods.getGoodsNum();pstat.setInt(4, goods.getGoodsStatus();pstat.setInt(5, goods.getTypes().getId();pstat.executeUpdate();return true;finally ConnectionUtils.closeConnection();/* * 查询所有商品的方法 * * return 所有商品信息的集合对象 * throws Exception */public List findAll() throws Exception try List goodsList = new ArrayList();Connection conn = ConnectionUtils.getConnection();String sql = select * from goods inner join types on goods.goodsType = types.id order by goodsPrice desc;PreparedStatement pstat = conn.prepareStatement(sql);ResultSet rs = pstat.executeQuery();while (rs.next() Goods goods = new Goods();goods.setId(rs.getInt(id);goods.setGoodsName(rs.getString(goodsName);goods.setGoodsPrice(rs.getFloat(goodsPrice);goods.setGoodsNum(rs.getInt(goodsNum);goods.setGoodsStatus(rs.getInt(goodsStatus);Types types = new Types();types.setId(rs.getInt(goodsType);types.setTypeName(rs.getString(typeName); goods.setTypes(types); goodsList.add(goods);return goodsList; finally ConnectionUtils.closeConnection();typesDAO:package com.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import com.domain.Types;import com.utils.ConnectionUtils;public class TypesDAO public List findAll() throws Exceptiontry List typesList = new ArrayList();Connection conn = ConnectionUtils.getConnection();String sql = select * from types;PreparedStatement pstat = conn.prepareStatement(sql);ResultSet rs = pstat.executeQuery();while (rs.next() Types types = new Types();types.setId(rs.getInt(id);types.setTypeName(rs.getString(typeName);typesList.add(types);return typesList; finally ConnectionUtils.closeConnection();Com.domain:Goods:package com.d