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());        }    }}