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