prodaoimpl.java

利用java swing实现简单的单机版仓库管理

package control.dao.product;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;

import beans.Product;
import utils.Keys;

public class ProDAOImpl {
    public static String flag="";
    
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    
    public ProDAOImpl(){
        
    }
   
    public int insert(Product pro) {
        
        int rows=0;

            String pName=pro.getPName();
            String pType=pro.getPType();
            String pClassID=pro.getPClassID();
            String pUnit=pro.getPUnit();
            double pPrice=pro.getPPrice();
            
            
            try{
                conn=this.getConn();
                stmt=conn.createStatement();
                rs=stmt.executeQuery("select pName from products where PName='"+pName+"'");
                if(rs.next()){
                    rows=-1;
                    return rows;
                }
                String sql="insert into products (PName,PType,PClassID,PUnit,PPrice) values('"+
                        pName +"','"+pType+"','"+pClassID+"','"+pUnit+"',"+pPrice+")";
                rows=stmt.executeUpdate(sql);

                stmt.executeQuery("select * from products");
                
            }catch(java.sql.SQLException se){
                System.out.println(se.getMessage());
            }catch(Exception e){
                e.printStackTrace();
                rows=0;
            }finally{
                closeConn();
            }
        return rows;
    }
    
    public int delete(int pID) {
        int rows=0;
        try{
            conn=this.getConn();
            stmt=conn.createStatement();
            rows=stmt.executeUpdate("delete from products where PID="+pID);
            rs=stmt.executeQuery("select * from products");
            System.out.println("删除成功");
        }catch(Exception e){
            System.out.println(e.getMessage());
        }finally{
            closeConn();
        }
        
        return rows;
    }
    
    public ArrayList find(Product pro){
        String pName=pro.getPName();
        String pType=pro.getPType();
        String pClassID=pro.getPClassID();
        String pUnit=pro.getPUnit();
        double pPrice=pro.getPPrice();
            
        ArrayList list=new ArrayList();
        
        try{
            String sql="select * from products ";
            String whereSql=" where ";
            if( pName.equals("") && pType.equals("") && pClassID.equals("")
                    && pUnit.equals("") && pPrice==0 ){
                whereSql="";
            }
            if( !pType.equals("") ){
                whereSql += " pName like '%" + pName + "%' and";
            }
            if( !pClassID.equals("") ){
                whereSql += " pClassID like '%" + pClassID + "%' and";
            }
            if( !pUnit.equals("") ){
                whereSql += " pUnit like '%" + pUnit + "%' and";
            }
            if( pPrice!=0 ){
                whereSql += " pPrice = " + pPrice + " and";
            }
            if( !(whereSql.equals(""))){
                whereSql = whereSql.substring(0, whereSql.length()-4);
            }
            
            String querySql=sql + whereSql;
            
            conn=this.getConn();
            stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
            rs=stmt.executeQuery(querySql);
            while(rs.next()){
                int pID2=Integer.parseInt(rs.getString("PID"));
                String pName2=rs.getString("PName");
                String pType2=rs.getString("PType");
                String pClassID2=rs.getString("PClassID");
                String pUnit2=rs.getString("PUnit");
                double pPrice2=rs.getDouble("PPrice");
                int pTotalNum=rs.getInt("PTotalNum");
                Product pd=new Product(pID2,pName2,pType2,pClassID2,pUnit2,pPrice2,pTotalNum);
                list.add(pd);
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
            return null;
        }finally{
            closeConn();
        }
        return list;
    }
    
    public ArrayList showAll(){
        ArrayList al=new ArrayList();
        try{
            conn=this.getConn();
            stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
            rs=stmt.executeQuery("select * from products");
            while(rs.next()){
                int pID=Integer.parseInt(rs.getString("PID"));
                String pName=rs.getString("PName");
                String pType=rs.getString("PType");
                String pClassID=rs.getString("PClassID");
                String pUnit=rs.getString("PUnit");
                double pPrice=rs.getDouble("PPrice");
                int pTotalNum=rs.getInt("PTotalNum");
                Product pd=new Product(pID,pName,pType,pClassID,pUnit,pPrice,pTotalNum);
                al.add(pd);
            }
            
        }catch(SQLException se){
            System.out.println(se.getMessage());
            return null;
        }finally{
            closeConn();
        }
        
        return al;
    }
    
    public Connection getConn(){
        try{
            Class.forName(Keys.connDriver);
            return DriverManager.getConnection(Keys.connUrl,Keys.connUsername,Keys.connPassword);
        }catch(ClassNotFoundException e){
            return null;
        }catch(SQLException se){
            return null;
        }
    }
    
    public void closeConn(){
        try{
            if(conn != null){
                conn.close();
            }
            if(stmt != null){
                stmt.close();
            }
            if(rs != null){
                rs.close();
            }
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }

}