productdao.java

企业宣传网 用JSP+SQLSERVER开发 商品展示 在线聊天 留言板 新闻发布 后台管理

package com.accpedu.LHcompany.dao;

import java.sql.*;
import java.util.*;
import com.accpedu.LHcompany.entity.product;

public class productDao {

	Connection con=null;
	PreparedStatement ps=null;
	DBAccess dba=new DBAccess();
	
	
	
	public ArrayList<product> select(int page)
	{
		ArrayList<product> al=new ArrayList<product>();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select top 5* from product where productID not in (select top "+(page-1)*5+" productID from product)");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				product p=new product();
				p.setBrand(rs.getString("brand"));
				p.setModel(rs.getString("model"));
				p.setName(rs.getString("name"));
				p.setPrice(rs.getDouble("price"));
				p.setProductID(rs.getInt("productID"));
				p.setPicture(rs.getString("picture"));
				p.setSerialNumber(rs.getString("serialNumber"));
				p.setDescription(rs.getString("description"));
				al.add(p);
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return al;
	}
	
	public ArrayList<product> selects()
	{
		ArrayList<product> al=new ArrayList<product>();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select * from product ");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				product p=new product();
				p.setBrand(rs.getString("brand"));
				p.setModel(rs.getString("model"));
				p.setName(rs.getString("name"));
				p.setPrice(rs.getDouble("price"));
				p.setProductID(rs.getInt("productID"));
				p.setPicture(rs.getString("picture"));
				p.setSerialNumber(rs.getString("serialNumber"));
				p.setDescription(rs.getString("description"));
				al.add(p);
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return al;
	}
	
	public ArrayList<product> select2(int id)
	{
		ArrayList<product> al=new ArrayList<product>();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select * from product where productID="+id);
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				product p=new product();
				p.setBrand(rs.getString("brand"));
				p.setModel(rs.getString("model"));
				p.setName(rs.getString("name"));
				p.setPrice(rs.getDouble("price"));
				p.setProductID(rs.getInt("productID"));
				p.setPicture(rs.getString("picture"));
				p.setSerialNumber(rs.getString("serialNumber"));
				p.setDescription(rs.getString("description"));
				al.add(p);
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return al;
	}
	
	public int selectPage()
	{
		int x=0;
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select count(*) from product");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				x=rs.getInt(1);
			}
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		if(x%5==0)
		{
			x=x/5;
			
		}else
		{
			x=x/5+1;
		}
		return x;
	}
	
	public int insert(product p)
	{
		int x=0;
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("insert into product values(?,?,?,?,?,?,?)");
    		ps.setString(1,p.getSerialNumber());
    		ps.setString(2,p.getName());
    		ps.setString(3,p.getBrand());
    		ps.setString(4,p.getModel());
    		ps.setDouble(5,p.getPrice());
    		ps.setString(6,p.getPicture());
    		ps.setString(7,p.getDescription());
    		x=ps.executeUpdate();
    		ps.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		return x;	
	}
	
	public int update(product p)
	{
		int x=0;
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("update product set SerialNumber=?,Name=?,Brand=?,Model=?,Price=?,Picture=?,Description=? where productID="+p.getProductID());
    		ps.setString(1,p.getSerialNumber());
    		ps.setString(2,p.getName());
    		ps.setString(3,p.getBrand());
    		ps.setString(4,p.getModel());
    		ps.setDouble(5,p.getPrice());
    		ps.setString(6,p.getPicture());
    		ps.setString(7,p.getDescription());
    		x=ps.executeUpdate();
    		ps.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		return x;	
	}
	
	public int delete(int id)
	{
		int x=0;
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("delete from product where productId="+id);
			x=ps.executeUpdate();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		return x;
	}
	
	public ArrayList<product> selects1()
	{
		ArrayList<product> al=new ArrayList<product>();
		con=dba.DBOpen();
		try
		{
			ps=con.prepareStatement("select top 8* from product ");
			ResultSet rs=ps.executeQuery();
			while(rs.next())
			{
				product p=new product();
				p.setBrand(rs.getString("brand"));
				p.setModel(rs.getString("model"));
				p.setName(rs.getString("name"));
				p.setPrice(rs.getDouble("price"));
				p.setProductID(rs.getInt("productID"));
				p.setPicture(rs.getString("picture"));
				p.setSerialNumber(rs.getString("serialNumber"));
				p.setDescription(rs.getString("description"));
				al.add(p);
			}
			rs.close();
			dba.DBClose();
		}catch(SQLException ce)
		{
			System.out.println(ce.getMessage());
		}
		
		return al;
	}
}