prodoutdaoimpl.java

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

package control.dao.prodout;

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 utils.Keys;
import beans.ProdOut;

public class ProdOutDAOImpl{
    public static String flag="";
    
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;

    public ProdOutDAOImpl(){
        
    }

    public int insert(ProdOut prodOut) {
        int rows=0;
        int pID=0, pTotalNum=0;
            String pName=prodOut.getPName();
            int pNum=prodOut.getPONum();
            double POTotalMoney=prodOut.getPOTotalMoney();
            String POByer=prodOut.getPOByer();
            String POStorager=prodOut.getPOStorager();
            String POTime=prodOut.getPOTime();
            String POComment=prodOut.getPOComment();
            ResultSet rs2=null;
            try{
                conn=this.getConn();
                stmt=conn.createStatement();
                rs=stmt.executeQuery("select pID,pTotalNum from products where PName='"+pName+"'");
                if(!rs.next()){ 
                    rows=-1;
                    return rows;
                }else{
                    pID=rs.getInt("PID");
                    pTotalNum=rs.getInt("PTotalNum");
                    pTotalNum=pTotalNum-pNum;
                    if(pTotalNum<0){
                        return -2;
                    }
                }
                String sql="insert into ProdOut (PID,PONum,POTotalMoney,POByer,POStorager,POTime,POComment) "
                            +"values("+pID +","+pNum+","+POTotalMoney+",'"+POByer+"','"+POStorager
                            +"','"+POTime+"','"+POComment+"')";
                rows=stmt.executeUpdate(sql);
                
               
                rs2=stmt.executeQuery("select * from prodstore where PID="+pID+" and PSDate='"+POTime+"'");
                
                if(rs2.next()){
                    int pINum=rs2.getInt("PINum");
                    int pSNum=rs2.getInt("PSNum");
                    pSNum+=pNum;
                    pINum+=pNum;
                    stmt.executeUpdate("update prodstore set PINum="+pINum+",PSNum="+pTotalNum+
                        " where PID="+pID+" and PSDate='"+POTime+"'");
                }else{
                    stmt.executeUpdate("insert into prodstore(PID,PINum,PSNum,PSDate) "
                            +"values ("+pID+","+pNum+","+pTotalNum+",'"+POTime+"')");
                }
                stmt.executeQuery("select * from prodstore");
                
                stmt.executeUpdate(
                        "update products set PTotalNum="+pTotalNum+" where PID="+pID);
                stmt.executeQuery("select * from products");
                stmt.executeQuery("select * from ProdOut");

            }catch(java.sql.SQLException se){
                System.out.println(se.getMessage());
                rows=0;
            }catch(Exception e){
                System.out.println(e.getMessage());
                rows=0;
            }finally{
                this.closeConn();
            }
            
        return rows;
    }
   
    public int delete(int pIID) {
        int rows=0;
        try{
            conn=this.getConn();
            stmt=conn.createStatement();
            
            
            int pID=0, pNum=0,pTotalNum=0;
            Statement stmt2=conn.createStatement();
            rs=stmt.executeQuery("select PID,PONum,POTime from ProdOut where pOID="+pIID);
            ResultSet rs2=null;
            ResultSet rs3=null;
            if(rs.next()){
                pID=rs.getInt("PID");
                pNum=rs.getInt("PONum");
                String pOTime=rs.getString("POTime");
                rs2=stmt2.executeQuery("select PTotalNum from products where PID="+pID);
                if(rs2.next()){
                    pTotalNum=rs2.getInt("PTotalNum");
                }
                pTotalNum=pTotalNum + pNum;
                int r=stmt2.executeUpdate(
                        "update products set PTotalNum="+pTotalNum+" where PID="+pID);
                
                
                rs3=stmt2.executeQuery("select * from prodstore where PID="
                        +pID+" and PSDate='"+pOTime+"'");
                if(rs3.next()){
                    int psINum=rs3.getInt("PINum");
                    psINum=psINum+pNum;
                    stmt2.executeUpdate("update prodstore set PINum="
                            +psINum+",PSNum="+pTotalNum+" where PID="
                            +pID+" and PSDate='"+pOTime+"'");
                    stmt2.executeQuery("select * from prodstore");
                }
            }
            stmt2.executeQuery("select * from products");
            rows=stmt.executeUpdate("delete from ProdOut where POID="+pIID);
            stmt.executeQuery("select * from ProdOut");
            System.out.println("删除成功");
            if(rs2 != null){
                rs2.close();
            }
            if(stmt2 != null){
                stmt2.close();
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }finally{
           this.closeConn();
        }
        return rows;
    }
   
    public ArrayList find(ProdOut prodOut){
            int pNum=prodOut.getPONum();
            double POTotalMoney=prodOut.getPOTotalMoney();
            String POByer=prodOut.getPOByer();
            String POStorager=prodOut.getPOStorager();
            String POTime=prodOut.getPOTime();

        ArrayList list=new ArrayList();
        
        try{
            String sql="select * from ProdOut ";
            String whereSql=" where ";
            if(  pNum==0 && POTotalMoney==0.0 && POTime.equals("") && 
                    POByer.equals("") && POStorager.equals("") ){
                whereSql="";
            }
            if( !POByer.equals("") ){
                whereSql += " POByer like '%" + POByer + "%' and";
            }
            if( !POStorager.equals("") ){
                whereSql += " POStorager like '%" + POStorager + "%' and";
            }
            if( !POTime.equals("") ){
                whereSql += " POTime like '%" + POTime + "%' and";
            }
            if( pNum!=0 ){
                whereSql += " PONum=" + pNum + " and";
            }
            if( POTotalMoney!=0.0 ){
                whereSql += " POTotalMoney=" + POTotalMoney + " 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);
            Statement stmt2=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
            ResultSet rs2=null;
            String pName2="";
            rs=stmt.executeQuery(querySql);
            while(rs.next()){
                int PIID2=rs.getInt("POID");
                int pID2=rs.getInt("PID");
                int pNum2=rs.getInt("PONum");
                double POTotalMoney2=rs.getDouble("POTotalMoney");
                String POByer2=rs.getString("POByer");
                String POStorager2=rs.getString("POStorager");
                String POTime2=rs.getString("POTime");
                String POComment2=rs.getString("POComment");
                
                rs2=stmt2.executeQuery("select pName from products where pID="+pID2);
                if(rs2.next())
                    pName2=rs2.getString("PName");

                ProdOut pd=new ProdOut(PIID2,pName2,pNum2,POTotalMoney2,POByer2,POStorager2,
                        POTime2,POComment2);

                list.add(pd);
                
            }
            if(rs2 != null)
                rs2.close();
            if(stmt2 != null)
                stmt2.close();
        }catch(Exception e){
            System.out.println(e.getMessage());
            return null;
        }finally{
           this.closeConn();
        }
        
        return list;
    }
 
    public ArrayList showAll(){
        ArrayList al=new ArrayList();
        ResultSet rs2=null;
        Statement stmt2=null;
        String pName2="";
        try{
            conn=this.getConn();
            stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
            stmt2=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
            rs=stmt.executeQuery("select * from ProdOut");

            while(rs.next()){
                int PIID2=rs.getInt("POID");
                int pID2=rs.getInt("PID");
                int pNum2=rs.getInt("PONum");
                double POTotalMoney2=rs.getDouble("POTotalMoney");
                String POByer2=rs.getString("POByer");
                String POStorager2=rs.getString("POStorager");
                String POTime2=rs.getString("POTime");
                String POComment2=rs.getString("POComment");
                
                rs2=stmt2.executeQuery("select pName from products where pID="+pID2);
                if(rs2.next())
                    pName2=rs2.getString("PName");

                ProdOut pd=new ProdOut(PIID2,pName2,pNum2,POTotalMoney2,POByer2,POStorager2,
                        POTime2,POComment2);

                al.add(pd);
                
            }
            if(rs2 != null)
                rs2.close();
            if(stmt2 != null)
                stmt2.close();
            
        }catch(SQLException se){
            System.out.println(se.getMessage());
            return null;
        }finally{
           this.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(rs != null){
                rs.close();
            }
            if(stmt != null){
                stmt.close();
            }
            if(conn != null){
                conn.close();
            }
            
        }catch(SQLException e){
            System.out.println(e.getMessage());
        }
    }
  
}