productdao.java

仓库管理系统,适合各种行业的仓库管理系统

package com.bean.DAO;


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



import com.bean.DTO.ProductDTO;


public class ProductDAO {
private final int onepage=4;
private int maxpage=1;
private final String sql_getalla="select p.productID,p.productname,p.storage,p.spec,p.unit,p.sortID,s.name from product p,productsort s where p.sortID=s.sortID";
private final String sql_add="insert into product values(?,?,?,?,?)";
private final String sql_del="delete from product where productID=?";
private final String sql_update="update product set productname=?,storage=?,spec=?,unit=?,sortID=? where productID=?";
private final String sql_getall1="select * from product where sortID=?";
private final String sql_getall2="select * from product where productname=?";
private final String sql_getall3="select * from product where productID=?";
private List<ProductDTO> plist;
public ProductDAO(){
	plist=new ArrayList<ProductDTO>();
}
public int getmaxpage(Connection con){
	try {
		PreparedStatement ps=con.prepareStatement("select count(*) from product");
		ResultSet rs=ps.executeQuery();
		if(rs.next())
			this.maxpage=(rs.getInt(1)+onepage-1)/onepage;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return maxpage;
	
}
public List<ProductDTO> getAlllist(int page,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_getalla,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
		ResultSet rs=ps.executeQuery();
		rs.absolute((page-1)*onepage+1);
		rs.previous();
		int counter=0;
		while(rs.next()&&counter<onepage){
			ProductDTO pd=new ProductDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setStorage(rs.getInt(3));
			pd.setSpec(rs.getString(4));
			pd.setUnit(rs.getString(5));
			pd.setSortID(rs.getInt(6));
			pd.setSortname(rs.getString(7));
			this.plist.add(pd);
			counter++;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public List<ProductDTO> getAll(Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_getalla);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			ProductDTO pd=new ProductDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setStorage(rs.getInt(3));
			pd.setSpec(rs.getString(4));
			pd.setUnit(rs.getString(5));
			pd.setSortID(rs.getInt(6));
			pd.setSortname(rs.getString(7));
			this.plist.add(pd);
		}		  
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public List<ProductDTO> getAll(String productname,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_getall2);
		ps.setString(1, productname);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			ProductDTO pd=new ProductDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(productname);
			pd.setStorage(rs.getInt(3));
			pd.setSpec(rs.getString(4));
			pd.setUnit(rs.getString(5));
			pd.setSortID(rs.getInt(6));
			this.plist.add(pd);
		}
		  
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public ProductDTO getProduct(int productID,Connection con){
	ProductDTO pd=new ProductDTO();
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_getall3);
		ps.setInt(1, productID);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){		
			pd.setProductID(productID);
			pd.setProductname(rs.getString(2));
			pd.setStorage(rs.getInt(3));
			pd.setSpec(rs.getString(4));
			pd.setUnit(rs.getString(5));
			pd.setSortID(rs.getInt(6));
		}		  
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return pd;
}
public List<ProductDTO> getAll(int sortID,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_getall1);
		ps.setInt(1, sortID);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			ProductDTO pd=new ProductDTO();
			pd.setProductID(rs.getInt(1));
			pd.setProductname(rs.getString(2));
			pd.setStorage(rs.getInt(3));
			pd.setSpec(rs.getString(4));
			pd.setUnit(rs.getString(5));
			pd.setSortID(sortID);
			this.plist.add(pd);
		}		  
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return plist;
}
public boolean existproduct(String name,Connection con){
	
	try {
		PreparedStatement ps=con.prepareStatement("select productID from product where productname=?");
		ps.setString(1, name);
		ResultSet rs=ps.executeQuery();
		if(rs.next()){
				return true;
		}
		ps.execute();		  
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public int getproductID(String name,Connection con){
	int id=0;
	try {
		PreparedStatement ps=con.prepareStatement("select productID from product where productname=?");
		ps.setString(1, name);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			id=rs.getInt(1);			
		}		  
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return id;
}
public boolean Addproduct(ProductDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_add);
		ps.setString(1,pd.getProductname());
		ps.setInt(2,pd.getStorage());
		ps.setString(3,pd.getSpec());
		ps.setString(4,pd.getUnit());
		ps.setInt(5, pd.getSortID());
		ps.execute();		  
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean Delproduct(int productID,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(this.sql_del);
		ps.setInt(1,productID);
		ps.execute();		  
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean updsort(int sortID,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement("update product set sortID=0 where sortID=?");
		ps.setInt(1, sortID);
		ps.execute();	  
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean updproduct(ProductDTO pd,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement(sql_update);
		
		ps.setInt(6, pd.getProductID());
		ps.setString(1,pd.getProductname());
		ps.setInt(2,pd.getStorage());
		ps.setString(3,pd.getSpec());
		ps.setString(4,pd.getUnit());
		ps.setInt(5,pd.getSortID());
		ps.execute();		  
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public int getProductamount(int productID,Connection con){
	int amount=0;
	try {
		PreparedStatement ps=con.prepareStatement("select storage from product where productID=?");
		ps.setInt(1, productID);
		ResultSet rs=ps.executeQuery();
		if(rs.next())
			amount=rs.getInt(1);
		ps.execute();		  
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return amount;
}
public boolean upProductamount(int productID,int amount,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement("update product set storage=storage+? where productID=?");
		ps.setInt(2,productID);
		ps.setInt(1, amount);
		ps.execute();		  
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}
public boolean downProductamount(int productID,int amount,Connection con){
	try {
		PreparedStatement ps=con.prepareStatement("update product set storage=storage-? where productID=?");
		ps.setInt(2,productID);
		ps.setInt(1, amount);
		ps.execute();		  
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return false;
}

}