db_goods.java

通过JSP技术实现商品房产的查询与购买

package ch14.JavaBean;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import ch14.JavaBean.DB_Conn;
import ch14.JavaBean.Goods;

public class DB_goods {
	DB_Conn db_conn = new DB_Conn();

	// 列出网上商城中所有的商品
	public Vector list_goods() throws SQLException{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			String sql = "select * from goods";
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			rs = db_conn.sm.executeQuery(sql);
			Goods goods = null;
			Vector vector = new Vector();
			// ResultSet指针最初位于第一行之前，调用next方法使下一行成为当前行
			while (rs.next()) {
				goods = new Goods();
				goods.setGoods_id(rs.getInt("goods_id"));
				goods.setGoods_name(rs.getString("Goods_name"));
				goods.setTypeid(rs.getString("place"));
				goods.setGoods_price(rs.getFloat("Goods_price"));
				goods.setGoods_discount(rs.getFloat("Goods_discount"));
				goods.setGoods_desc(rs.getString("Goods_desc"));
				goods.setSupplier(rs.getString("supplier"));
				// 向Vector矢量中添加对象goods
				vector.addElement(goods);
			}
			return vector;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} catch (Exception E) {
			E.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	// 对上述函数进行重载，为了实现通过id获取商品的信息
	public Vector list_goods(int id) throws SQLException// 列出网上商城中所有的商品
	{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			String sql = "select * from goods where Goods_id=" + id;
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			rs = db_conn.sm.executeQuery(sql);
			Goods goods = null;
			Vector vector = new Vector();
			while (rs.next()) {
				goods = new Goods();
				goods.setGoods_id(rs.getInt("Goods_id"));
				goods.setGoods_name(rs.getString("Goods_name"));
				goods.setTypeid(rs.getString("place"));
				goods.setGoods_price(rs.getFloat("Goods_price"));
				goods.setGoods_discount(rs.getFloat("Goods_discount"));
				goods.setGoods_desc(rs.getString("Goods_desc"));
				goods.setSupplier(rs.getString("supplier"));
				vector.addElement(goods);
			}
			return vector;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} catch (Exception E) {
			E.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	public String getStr(String s) {
		String str = s;
		try {
			byte b[] = str.getBytes("ISO-8859-1");
			str = new String(b);
			return str;
		} catch (Exception e) {
			return null;
		}
	}

	// 检索出网上商城中商品编号为Goods_id的商品
	public Goods listgoods(String Goods_id) throws SQLException{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			String sql = "select * from goods where Goods_id=" + Goods_id;
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			rs = db_conn.sm.executeQuery(sql);
			Goods goods = null;
			if (rs.next()) {
				goods = new Goods();
				goods.setGoods_id(rs.getInt("Goods_id"));
				goods.setGoods_name(rs.getString("Goods_name"));
				goods.setTypeid(rs.getString("place"));
				goods.setGoods_price(rs.getFloat("Goods_price"));
				goods.setGoods_discount(rs.getFloat("Goods_discount"));
				goods.setGoods_desc(rs.getString("Goods_desc"));
				goods.setSupplier(rs.getString("supplier"));
			}
			return goods;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} catch (Exception E) {
			E.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}
    //按照商品名称进行模糊查询函数
	public Vector list_goods(String key) throws SQLException// 列出网上商城中所有的商品
	{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			// 拼接SQL
			String sql = "select * from goods where Goods_name like " + "'"
					+ "%" + key + "%" + "'";
			rs = db_conn.sm.executeQuery(sql);
			Goods goods = null;
			Vector vector = new Vector();
			while (rs.next()) {
				goods = new Goods();
				goods.setGoods_id(rs.getInt("Goods_id"));
				goods.setGoods_name(rs.getString("Goods_name"));
				goods.setTypeid(rs.getString("place"));
				goods.setGoods_price(rs.getFloat("Goods_price"));
				goods.setGoods_discount(rs.getFloat("Goods_discount"));
				goods.setGoods_desc(rs.getString("Goods_desc"));
				goods.setSupplier(rs.getString("supplier"));
				vector.addElement(goods);
			}
			return vector;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} catch (Exception E) {
			E.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	// 返回网上商城中商品种类数量
	public int Goods_count() throws SQLException{
		db_conn.ConnectDB();
		ResultSet rs = null;
		try {
			String sql = "select * from goods";
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			rs = db_conn.sm.executeQuery(sql);
			int i = 0;
			// 如果next方法返回true，说明ResultSet指针移至下一行时依然有元素存在，则
			// 使得变量i的值加1
			while (rs.next()) {
				i++;
			}
			return i;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} catch (Exception E) {
			E.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	// 修改商品信息
	public int Goods_update(Goods goods) throws SQLException{
		db_conn.ConnectDB();
		try {
			String sql = "update goods set Goods_id=" + goods.getGoods_id()
					+ ",Goods_name='" + goods.getGoods_name() + "',place="
					+ goods.getTypeid() + ",Goods_price="
					+ goods.getGoods_price() + ",Goods_discount="
					+ goods.getGoods_discount() + ",Goods_desc='"
					+ goods.getGoods_desc() + "',supplier='"
					+ goods.getSupplier() + "' where Goods_id="
					+ goods.getGoods_id();
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			System.out.println(sql);
			int i = db_conn.sm.executeUpdate(sql);
			return i;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} catch (Exception E) {
			E.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	// 插入商品信息
	public int Goods_insert(Goods goods) throws SQLException{
		db_conn.ConnectDB();
		try {

			String sql = "insert into goods (Goods_id,Goods_name,place,"
					+ "Goods_price,Goods_discount,Goods_desc,supplier) values("
					+ goods.getGoods_id() + "," + "'" + goods.getGoods_name()
					+ "'" + "," + goods.getTypeid() + ","
					+ goods.getGoods_price() + "," + goods.getGoods_discount()
					+ "," + "'" + goods.getGoods_desc() + "'" + "," + "'"
					+ goods.getSupplier() + "')";
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			int i = db_conn.sm.executeUpdate(sql);
			return i;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} catch (Exception E) {
			E.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

	// 删除商品信息
	public int Goods_delete(int Goods_id) throws SQLException{
		db_conn.ConnectDB();
		try {
			String sql = "delete from goods where goods_id='" + Goods_id + "'";
			sql = new String(sql.getBytes("ISO8859-1"), "GB2312");
			int i = db_conn.sm.executeUpdate(sql);
			return i;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} catch (Exception E) {
			E.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			db_conn.CloseDB();
		}
	}

}