productdao.java

来自「模拟网上购物系统」· Java 代码 · 共 345 行

package dao;

import java.sql.*;
import java.util.ArrayList;

import po.*;

public class ProductDAO {
	
	private Connection conn = null;
	private Statement state = null;
	private ResultSet rs = null;
	//取id
	public int getNextID()
	{
		int myID = 0;
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select max(proid) myID from product");
		    if(rs.next())
		    {
		    	myID = rs.getInt("myID");
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ++myID;
	}
    //查询所有待售商品
	public ArrayList findAll(int pageSize,int startRows,String sql)
	{
		ArrayList array = new ArrayList();
		
		try {
			conn = Tools.getConnection();
			state = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
					);
			rs = state.executeQuery(sql);
            rs.absolute(startRows);
			
			for(int i=0;i<pageSize;i++)
			{
		    	productPO type = new productPO();
		    	type.setProid(rs.getInt("proid"));
		    	type.setProname(rs.getString("proname"));
		    	type.setProprice(rs.getInt("proprice"));
		    	type.setMemberprice(rs.getInt("memberprice"));
		    	type.setPicture(rs.getString("picture"));
		    	array.add(type);
		    	if(!rs.next())
		    		break;
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return array;
	}
	
	public ArrayList findAlls()
	{
		ArrayList array = new ArrayList();
		
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select * from (select * from product order by proid) where rownum <= 8");
			
			while(rs.next())
			{
		    	productPO type = new productPO();
		    	type.setProid(rs.getInt("proid"));
		    	type.setProname(rs.getString("proname"));
		    	type.setProprice(rs.getInt("proprice"));
		    	type.setMemberprice(rs.getInt("memberprice"));
		    	type.setPicture(rs.getString("picture"));
		    	
		    	array.add(type);
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return array;
	}
	
//	查询全部行数
	public int count(String sql)
	{
		int ct = 0;
		
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery(sql);
			if(rs.next())
			{
				ct = rs.getShort("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ct;
	}
	
	//按产品名查找------模糊查询
	public productPO findProductByName(String _proname)
	{
		productPO po = null;
		conn = Tools.getConnection();
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select * from product where proname like '%"+_proname+"%'");
		    while(rs.next())
		    {   
		    	po = new productPO();
		    	po.setProid(rs.getInt("proid"));
		    	po.setProname(rs.getString("proname"));
		    	po.setProprice(rs.getInt("proprice"));
		    	po.setMemberprice(rs.getInt("memberprice"));
		    	po.setPicture(rs.getString("picture"));
		    	po.setBewrite(rs.getString("bewrite"));
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return po;
	}
	
	//按产品ID查找
	public productPO findProductByID(int _id)
	{
		
		productPO po = null;
		conn = Tools.getConnection();
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			rs = state.executeQuery("select * from product where proid ="+_id);
		    if(rs.next())
		    {   
		    	po = new productPO();
		    	po.setProid(rs.getInt("proid"));
		    	po.setProname(rs.getString("proname"));
		    	po.setProprice(rs.getInt("proprice"));
		    	po.setMemberprice(rs.getInt("memberprice"));
		    	po.setPicture(rs.getString("picture"));
		    	po.setBewrite(rs.getString("bewrite"));
		    	po.setPurpose(rs.getString("purpose"));
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return po;
	}
	//修改商品
	public boolean updatePro(productPO po)
	{
		boolean isok = false;
		conn = Tools.getConnection();
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			int i = state.executeUpdate("update product set proname='"+po.getProname()+"',proprice="+po.getProprice()+",memberprice="+po.getMemberprice()+",bewrite='"+po.getBewrite()+"',purpose='"+po.getPurpose()+"' where proid ="+po.getProid());
		    if(i > 0)
		    {   
		    	isok = true;
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
	//删除商品
	public boolean deletePro(int id)
	{
		boolean isok = false;
		conn = Tools.getConnection();
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			int i = state.executeUpdate("delete from product where proid ="+id);
		    if(i > 0)
		    {   
		    	isok = true;
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
	//添加
	public boolean addPro(productPO pro)
	{
		boolean isok = false;
		conn = Tools.getConnection();
		try {
			conn = Tools.getConnection();
			state = conn.createStatement();
			int i = state.executeUpdate("insert into product values("+pro.getProid()+",'"+pro.getProname()+"',"+pro.getProprice()+","+pro.getMemberprice()+",'"+pro.getPicture()+"','"+pro.getPurpose()+"','"+pro.getBewrite()+"')");
		    if(i > 0)
		    {   
		    	isok = true;
		    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return isok;
	}
}