prodindaoimpl.java

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

package control.dao.prodin;

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.ProdIn;

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

    public ProdInDAOImpl(){
        
    }


    public int insert(ProdIn proIn) {
        int rows=0;
        int pID=0, pTotalNum=0;
            String pName=proIn.getPName();
            int pNum=proIn.getPINum();
            double pITotalMoney=proIn.getPITotalMoney();
            String pIByer=proIn.getPIByer();
            String pIStorager=proIn.getPIStorager();
            String pITime=proIn.getPITime();
            String pIComment=proIn.getPIComment();
            
            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+=pNum;
                }
                String sql="insert into ProdIn (PID,PINum,PITotalMoney,PIByer,PIStorager,PITime,PIComment) "
                            +"values("+pID +","+pNum+","+pITotalMoney+",'"+pIByer+"','"+pIStorager
                            +"','"+pITime+"','"+pIComment+"')";
                rows=stmt.executeUpdate(sql);
             
                
                rs2=stmt.executeQuery("select * from prodstore where PID="+pID+" and PSDate='"+pITime+"'");
                
                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='"+pITime+"'");
                }else{
                    stmt.executeUpdate("insert into prodstore(PID,PINum,PSNum,PSDate) "
                            +"values ("+pID+","+pNum+","+pTotalNum+",'"+pITime+"')");
                }
                stmt.executeQuery("select * from prodstore");
                
                stmt.executeUpdate(
                        "update products set PTotalNum="+pTotalNum+" where PID="+pID);
                stmt.executeQuery("select * from products");
                stmt.executeQuery("select * from ProdIn");

            }catch(java.sql.SQLException se){
                System.out.println(se.getMessage());
                rows=0;
            }catch(Exception e){
                System.out.println(e.getMessage());
                rows=0;
            }finally{
                if(rs2 != null){
                    try{
                        rs2.close();
                    }catch(SQLException rse){
                        System.out.println(rse.getMessage());
                    }
                }
                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,PINum,PITime from ProdIn where pIID="+pIID);
            ResultSet rs2=null;
            ResultSet rs3=null;
            if(rs.next()){
                pID=rs.getInt("PID"); 
                pNum=rs.getInt("PINum"); 
                String pITime=rs.getString("PITime"); 
                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='"+pITime+"'");
                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='"+pITime+"'");
                    stmt2.executeQuery("select * from prodstore");
                }
                
            }
            
            stmt2.executeQuery("select * from products");
            
            rows=stmt.executeUpdate("delete from ProdIn where PIID="+pIID);
            stmt.executeQuery("select * from ProdIn");
            System.out.println("删除成功");
            if(rs2 != null){
                rs2.close();
            }
            if(rs3 != null){
                rs2.close();
            }
            if(stmt2 != null){
                stmt2.close();
            }
        }catch(Exception e){
            System.out.println(e.getMessage());
        }finally{
           this.closeConn();
        }
        return rows;
    }
   
    public ArrayList find(ProdIn proIn){
            int pNum=proIn.getPINum();
            double pITotalMoney=proIn.getPITotalMoney();
            String pIByer=proIn.getPIByer();
            String pIStorager=proIn.getPIStorager();
            String pITime=proIn.getPITime();
 
        ArrayList list=new ArrayList();
        
        try{
            String sql="select * from ProdIn ";
            String whereSql=" where ";
            if(  pNum==0 && pITotalMoney==0.0 && pITime.equals("") && 
                    pIByer.equals("") && pIStorager.equals("") ){
                whereSql="";
            }
            if( !pIByer.equals("") ){
                whereSql += " pIByer like '%" + pIByer + "%' and";
            }
            if( !pIStorager.equals("") ){
                whereSql += " pIStorager like '%" + pIStorager + "%' and";
            }
            if( !pITime.equals("") ){
                whereSql += " pITime like '%" + pITime + "%' and";
            }
            if( pNum!=0 ){
                whereSql += " pINum=" + pNum + " and";
            }
            if( pITotalMoney!=0.0 ){
                whereSql += " pITotalMoney=" + pITotalMoney + " 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("PIID");
                int pID2=rs.getInt("PID");
                int pNum2=rs.getInt("PINum");
                double pITotalMoney2=rs.getDouble("PITotalMoney");
                String pIByer2=rs.getString("PIByer");
                String pIStorager2=rs.getString("PIStorager");
                String pITime2=rs.getString("PITime");
                String pIComment2=rs.getString("PIComment");
                
                rs2=stmt2.executeQuery("select pName from products where pID="+pID2);
                if(rs2.next())
                    pName2=rs2.getString("PName");

                ProdIn pd=new ProdIn(PIID2,pName2,pNum2,pITotalMoney2,pIByer2,pIStorager2,
                        pITime2,pIComment2);

                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 ProdIn");

            while(rs.next()){
                int PIID2=rs.getInt("PIID");
                int pID2=rs.getInt("PID");
                int pNum2=rs.getInt("PINum");
                double pITotalMoney2=rs.getDouble("PITotalMoney");
                String pIByer2=rs.getString("PIByer");
                String pIStorager2=rs.getString("PIStorager");
                String pITime2=rs.getString("PITime");
                String pIComment2=rs.getString("PIComment");
                
                rs2=stmt2.executeQuery("select pName from products where pID="+pID2);
                if(rs2.next())
                    pName2=rs2.getString("PName");

                ProdIn pd=new ProdIn(PIID2,pName2,pNum2,pITotalMoney2,pIByer2,pIStorager2,
                        pITime2,pIComment2);

                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());
        }
    }
   
    public static void main(String args[]){
        ProdInDAOImpl pdm=new ProdInDAOImpl();
        pdm.insert(new ProdIn("22",10,11.1,"11","11","2008-5-5","aaa"));
    }
}