goodsdb.java

用java做的网上商店系统！代码完整。希望为大家有所帮助！

package org.qhit.shop.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDB extends Page{
	//查询所有商品的方法
	public List queryAllGoods(int pageNo){
		List all=new ArrayList();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		GoodsBean gb=null;
		String sql="select * from goods";
		try {
			//1.创建连接
			conn=DBConn.getConn();
			//2.创建指令集对象(设置rs类型为滚动可更新的)
			ps=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			//3.执行SQL指令
			rs=ps.executeQuery();
			//4.遍历rs
			//开始应用分页处理
			//参数：rs,每页显示多少条,当前页
			this.init(rs,8,pageNo);
			//取数据时
			for(int i=1;i<=8;i++){
				if(!rs.next())break;
				gb=new GoodsBean();
				gb.setId(rs.getInt(1));
				gb.setGName(rs.getString(2));
				gb.setGPrice1(rs.getDouble(3));
				gb.setGPrice2(rs.getDouble(4));
				gb.setXc(rs.getString(5));
				gb.setCc(rs.getString(6));
				gb.setGPinpai(rs.getString(7));
				gb.setGPic(rs.getString(8));
				gb.setGXiangxi(rs.getString(9));
				all.add(gb);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)rs.close();
				if(ps!=null)ps.close();
				DBConn.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return all;
	}
	//根据id查询商品的方法
	public GoodsBean queryById(int id){
		GoodsBean gb=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;	
		String sql="select * from goods where id=?";
		try {
			//1.创建连接
			conn=DBConn.getConn();
			//2.创建指令集对象
			ps=conn.prepareStatement(sql);
			//为参数绑定值
			ps.setInt(1, id);
			//3.执行SQL指令
			rs=ps.executeQuery();
			//4.遍历rs
			if(rs.next()){
				gb=new GoodsBean();
				gb.setId(rs.getInt(1));
				gb.setGName(rs.getString(2));
				gb.setGPrice1(rs.getDouble(3));
				gb.setGPrice2(rs.getDouble(4));
				gb.setXc(rs.getString(5));
				gb.setCc(rs.getString(6));
				gb.setGPinpai(rs.getString(7));
				gb.setGPic(rs.getString(8));
				gb.setGXiangxi(rs.getString(9));				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)rs.close();
				if(ps!=null)ps.close();
				DBConn.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return gb;
	}
//	1.插入新商品
	public int insert(GoodsBean gb){
		//声明
		int i=0;
		Connection conn=null;
		PreparedStatement ps=null;
		String sql="insert into goods values(?,?,?,?,?,?,?,?)";
		try {
			//1.连接
			conn=DBConn.getConn();
			//2.创建预编译指令集对象
			ps=conn.prepareStatement(sql);
			//3.绑定参数（和？对应的）
			ps.setString(1,gb.getGName());
			ps.setDouble(2,gb.getGPrice1());
			ps.setDouble(3,gb.getGPrice2());
			ps.setString(4,gb.getXc());
			ps.setString(5,gb.getCc());
			ps.setString(6,gb.getGPinpai());
			ps.setString(7,gb.getGPic());
			ps.setString(8,gb.getGXiangxi());	
			
			//4.执行
			i=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(ps!=null)ps.close();
				DBConn.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}		
		return i;
	}
//	多条件查询商品的方法
	public List searchGoods(GoodsBean item){
		List all=new ArrayList();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		GoodsBean gb=null;
		StringBuilder sql=new StringBuilder()
		.append("select *")
		.append("  from goods")
		.append(" where 1=1 ");
		if(item.getGName()!=null&&!"".equals(item.getGName())){
			sql.append(" and gName like '%"+item.getGName()+"%'");		
		}
		if(item.getGPinpai()!=null&&!"".equals(item.getGPinpai())){
			sql.append(" and gPinpai like '%"+item.getGPinpai()+"%'");		
		}
		if(item.getCc()!=null&&!"".equals(item.getCc())){
			sql.append(" and cc='"+item.getCc()+"'");		
		}
		if(item.getXc()!=null&&!"".equals(item.getXc())){
			sql.append(" and xc='"+item.getXc()+"'");		
		}		
		try {
			//1.创建连接
			conn=DBConn.getConn();
			//2.创建指令集对象(设置rs类型为滚动可更新的)
			System.out.println("sql:"+sql.toString());
			ps=conn.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			//3.执行SQL指令
			rs=ps.executeQuery();
			//4.遍历rs			
			while(rs.next()){				
				gb=new GoodsBean();
				gb.setId(rs.getInt(1));
				gb.setGName(rs.getString(2));
				gb.setGPrice1(rs.getDouble(3));
				gb.setGPrice2(rs.getDouble(4));
				gb.setXc(rs.getString(5));
				gb.setCc(rs.getString(6));
				gb.setGPinpai(rs.getString(7));
				gb.setGPic(rs.getString(8));
				gb.setGXiangxi(rs.getString(9));
				all.add(gb);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)rs.close();
				if(ps!=null)ps.close();
				DBConn.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return all;
	}
}