db_goods.java

精通JSP-Web开发技术与典型应用（光盘）

package ch24.JavaBean;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import ch24.JavaBean.DB_Conn;
import ch24.JavaBean.Goods;
public class DB_goods
{
	DB_Conn db_conn=new DB_Conn();
    //列出网上商城中所有的商品
	public  Vector list_goods()
	{
		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.getInt("typeid"));
				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"));
				goods.setQty(rs.getInt("qty"));
				goods.setIspreferred(rs.getInt("ispreferred"));
				goods.setGoods_pic(rs.getString("goods_pic"));
				//向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)//列出网上商城中所有的商品
	{
		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.getInt("typeid"));
				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"));
				goods.setQty(rs.getInt("qty"));
				goods.setIspreferred(rs.getInt("ispreferred"));
				goods.setGoods_pic(rs.getString("goods_pic"));
				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)
	{
		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.getInt("typeid"));
				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"));
				goods.setQty(rs.getInt("qty"));
				goods.setIspreferred(rs.getInt("ispreferred"));
				goods.setGoods_pic(rs.getString("goods_pic"));
			}
			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)//列出网上商城中所有的商品
	{
		db_conn.ConnectDB();
		ResultSet rs=null;
		try
		{
			//String new_key=getStr(key);
			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.getInt("typeid"));
				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"));
				goods.setQty(rs.getInt("qty"));
				goods.setIspreferred(rs.getInt("ispreferred"));
				goods.setGoods_pic(rs.getString("goods_pic"));
				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()
	{
		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)
	{
		db_conn.ConnectDB();
		try
		{
			String sql = "update goods set goods_id="
				+goods.getGoods_id()+",goods_name='"
				+goods.getGoods_name()+"',typeid="
				+goods.getTypeid()+",goods_price="
				+goods.getGoods_price()+",goods_discount="
				+goods.getGoods_discount()+",goods_desc='"
				+goods.getGoods_desc()+"',supplier='"
				+goods.getSupplier()+"',qty="
				+goods.getQty()+",ispreferred="
				+goods.getIspreferred()+",goods_pic='"
				+goods.getGoods_pic()+"' 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)
	{
		db_conn.ConnectDB();
		try
		{
			
			String sql="insert into goods (goods_id,goods_name,typeid," 
					+"goods_price,goods_discount,goods_desc,supplier," 
					+"qty,ispreferred,goods_pic) values("+goods.getGoods_id()+","+"'"
					+goods.getGoods_name()+"'"+","
					+goods.getTypeid()+","
					+goods.getGoods_price()+","
					+goods.getGoods_discount()+","+"'"
					+goods.getGoods_desc()+"'"+","+"'"
					+goods.getSupplier()+"'"+","
					+goods.getQty()+","
					+goods.getIspreferred()+","+"'"
					+goods.getGoods_pic()+"'"+")";
			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)
	{
		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();
		}
	}
	
	
}