?? prodoutdaoimpl.java
字號:
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()); } } }?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -