daopronum.java

products program INSTRACTION item

package com.nextier.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

import com.nextier.model.DBcon;
import com.nextier.model.SkinUtils;
import com.nextier.model.pronum;

public class DAOpronum {
    //plan_num表信息录入
	private static final String insert_plannum="insert into plan_num(products_id,products_name,plan_num1,plan_num2,plan_num3,plan_num4,date1) values(?,?,?,?,?,?,?)";
	public void insert(pronum pro){
		Connection conn=null;
		PreparedStatement psmt=null;
		try{
			conn=DBcon.getConnection();
			psmt=conn.prepareStatement(insert_plannum);
			psmt.setString(1, pro.getproducts_id());
			psmt.setString(2, pro.getproducts_name());
			psmt.setInt(3, pro.getplan_num1());
			psmt.setInt(4, pro.getplan_num2());
			psmt.setInt(5, pro.getplan_num3());
			psmt.setInt(6, pro.getplan_num4());
			psmt.setDate(7, pro.getdate());
		    int a=psmt.executeUpdate();
		    System.out.println("a:"+a);
		}catch(Exception e){}
		  finally{
				 try{  
		         	psmt.close();  
		         }catch (Exception e) {}
		         try{    
		         	conn.close();  
		         }catch (Exception e) {}
		  }
	}
	//plan_num表信息查询
    private static final String select_all="select * from plan_num order by id asc";
    @SuppressWarnings("unchecked")
	public Vector queryAll(){
		 Connection conn=null;
		 PreparedStatement psmt=null;
		 ResultSet rs=null;
		 Vector all = new Vector() ;
		 try{
			 conn=DBcon.getConnection();
			 psmt=conn.prepareStatement(select_all);
			 rs=psmt.executeQuery();			 
			 while(rs.next()){
				 pronum pro=new pronum();
				 pro.setid(rs.getInt("id"));
				 pro.setproducts_id(rs.getString("products_id"));
				 pro.setproducts_name(rs.getString("products_name"));
				 pro.setplan_num1(rs.getInt("plan_num1"));
				 pro.setplan_num2(rs.getInt("plan_num2"));
				 pro.setplan_num3(rs.getInt("plan_num3"));
				 pro.setplan_num4(rs.getInt("plan_num4"));
				 pro.setdate(rs.getDate("date1"));
				 all.addElement(pro);
			 }
		 }catch(Exception e){}
		  finally{
				 try{  
		         	psmt.close();  
		         }catch (Exception e) {}
		         try{    
		         	conn.close();  
		         }catch (Exception e) {}
		  }
		 return all;
    }
    //删除
    private static final String delete_plan_num="delete from plan_num where id=?";
    public void delete(int id) {
		 Connection conn=null;
		 PreparedStatement psmt=null;
		 try{
			 conn=DBcon.getConnection();
			 psmt=conn.prepareStatement(delete_plan_num);
			 psmt.setInt(1, id);			 
			 psmt.executeUpdate();
		 }catch(SQLException e){}
			 finally{
				 try{  
		         	psmt.close();  
		         }catch (Exception e) {}
		         try{    
		         	conn.close();  
		         }catch (Exception e) {}
			 }
	 }
	//按ID号显示plan_num表信息
	 private static final String select_id="select * from plan_num where id=?";
	 public pronum selectid(int id){
		 Connection conn=null;
		 PreparedStatement psmt=null;
		 ResultSet rs=null;
		 pronum pa=new pronum();
	     try{
			 conn=DBcon.getConnection();
			 psmt=conn.prepareStatement(select_id);
			 psmt.setInt(1, id);
			 rs=psmt.executeQuery();
			 while(rs.next()){
				 pa.setid(rs.getInt("id"));
				 pa.setproducts_id(rs.getString("products_id"));
				 pa.setproducts_name(rs.getString("products_name"));
				 pa.setplan_num1(rs.getInt("plan_num1"));
				 pa.setplan_num2(rs.getInt("plan_num2"));
				 pa.setplan_num3(rs.getInt("plan_num3"));
				 pa.setplan_num4(rs.getInt("plan_num4"));
				 pa.setdate(rs.getDate("date1"));
			 }
	     }catch(Exception e){}
		  finally{
				 try{  
		         	psmt.close();  
		         }catch (Exception e) {}
		         try{    
		         	conn.close();  
		         }catch (Exception e) {}
			  }
		  return pa;
	 }
	 //plannum表信息修改
	 private static final String update_pronum="update plan_num set products_id=?,products_name=?,plan_num1=?,plan_num2=?,plan_num3=?,plan_num4=?,date1=? where id=?";
	 public void update(pronum pro) throws SQLException{
		 Connection conn=null;
		 PreparedStatement psmt=null;
		 try{
			 conn=DBcon.getConnection();
			 psmt=conn.prepareStatement(update_pronum);
				psmt.setString(1, pro.getproducts_id());
				psmt.setString(2, pro.getproducts_name());
				psmt.setInt(3, pro.getplan_num1());
				psmt.setInt(4, pro.getplan_num2());
				psmt.setInt(5, pro.getplan_num3());
				psmt.setInt(6, pro.getplan_num4());
				psmt.setDate(7, pro.getdate());
				psmt.setInt(8, pro.getid());
				int a=psmt.executeUpdate();
				System.out.println("a"+a);
			}catch(Exception e){}
			  finally{
					 try{  
			         	psmt.close();  
			         }catch (Exception e) {}
			         try{    
			         	conn.close();  
			         }catch (Exception e) {}
			  }
	 }
	 //按日期查询
	 public Vector query(String sql){
		 Connection conn=null;
		 PreparedStatement psmt=null;
		 ResultSet rs=null;
		 Vector<pronum> all = new Vector<pronum>() ;
		 try{
			 conn=DBcon.getConnection();
			 psmt=conn.prepareStatement(sql);
			 rs=psmt.executeQuery();
			 while(rs.next()){
				 pronum pro=new pronum();
				 pro.setid(rs.getInt("id"));
				 pro.setproducts_id(rs.getString("products_id"));
				 pro.setproducts_name(rs.getString("products_name"));
				 pro.setplan_num1(rs.getInt("plan_num1"));
				 pro.setplan_num2(rs.getInt("plan_num2"));
				 pro.setplan_num3(rs.getInt("plan_num3"));
				 pro.setplan_num4(rs.getInt("plan_num4"));
				 pro.setdate(rs.getDate("date1"));
				 pro.setid1(rs.getInt("id"));
				 pro.setid2(rs.getInt("pid"));
				 pro.setremarks(rs.getString("products_id"));
				 pro.setreal_num1(rs.getInt("real_num1"));
				 pro.setreal_num2(rs.getInt("real_num2"));
				 pro.setreal_num3(rs.getInt("real_num3"));
				 pro.setreal_num4(rs.getInt("real_num4"));
				 pro.setinstruction1(rs.getString("instruction1"));
				 pro.setinstruction2(rs.getString("instruction2"));
				 pro.setinstruction3(rs.getString("instruction3"));
				 pro.setinstruction4(rs.getString("instruction4"));
				 all.addElement(pro);
			 }
		 }
		 catch(Exception e){}
		  finally{
			 try{  
	         	psmt.close();  
	         }catch (Exception e) {}
	         try{    
	         	conn.close();  
	         }catch (Exception e) {}
	        } 
		 return all;
    }
	 //查询所有
	    //static String now1="select convert(char(10),getdate(),120)";
	    //static Date now=SkinUtils.convertToDate(now1);
		private static final String select_all1="select plan_num.*,real_num.* from plan_num INNER JOIN real_num ON plan_num.id=real_num.pid ";
		public Vector queryAll1(){
			//Calendar   today=Calendar.getInstance();   
			  //today.setTimeInMillis(System.currentTimeMillis()-(1000*60*60*24));
			  //java.text.SimpleDateFormat f = new java.text.SimpleDateFormat("yyyy-MM-dd");
			  //String haha = f.format(new java.util.Date(today.getTimeInMillis()));
		    // String select_all1="select * from plan_num where datediff('n',date1,'"+haha+"')>0 order by id asc";  
			//  String select_all1="select * from plan_num where date1=getdate() order by id asc"; 
			 Connection conn=null;
			 PreparedStatement psmt=null;
			 ResultSet rs=null;
			 Vector<pronum> all = new Vector<pronum>() ;
			 try{
				 conn=DBcon.getConnection();
				 System.out.println("sql:"+select_all1);
				 psmt=conn.prepareStatement(select_all1);
				 rs=psmt.executeQuery();			 
				 while(rs.next()){
				 pronum pro=new pronum();
				 pro.setid(rs.getInt("id"));
				 pro.setproducts_id(rs.getString("products_id"));
				 pro.setproducts_name(rs.getString("products_name"));
				 pro.setplan_num1(rs.getInt("plan_num1"));
				 pro.setplan_num2(rs.getInt("plan_num2"));
				 pro.setplan_num3(rs.getInt("plan_num3"));
				 pro.setplan_num4(rs.getInt("plan_num4"));
				 pro.setdate(rs.getDate("date1"));
				 pro.setid1(rs.getInt("id"));
				// System.out.println("i"+rs.getInt("id"));
				 pro.setid2(rs.getInt("pid"));
				 pro.setremarks(rs.getString("products_id"));
				 pro.setreal_num1(rs.getInt("real_num1"));
				 pro.setreal_num2(rs.getInt("real_num2"));
				 pro.setreal_num3(rs.getInt("real_num3"));
				 pro.setreal_num4(rs.getInt("real_num4"));
				 pro.setinstruction1(rs.getString("instruction1"));
				 pro.setinstruction2(rs.getString("instruction2"));
				 pro.setinstruction3(rs.getString("instruction3"));
				 pro.setinstruction4(rs.getString("instruction4"));
				 all.addElement(pro);
			 }
		 }
		 catch(Exception e){}
		  finally{
			 try{  
	         	psmt.close();  
	         }catch (Exception e) {}
	         try{    
	         	conn.close();  
	         }catch (Exception e) {}
	        } 
		 return all;
    }
	    //real_num表信息录入
		private static final String insert_realnum="insert into real_num(pid,products_id,real_num1,real_num2,real_num3,real_num4,instruction1,instruction2,instruction3,instruction4) values(?,?,?,?,?,?,?,?,?,?)";
		public void insert1(pronum pro){
			Connection conn=null;
			PreparedStatement psmt=null;
			try{
				conn=DBcon.getConnection();
				psmt=conn.prepareStatement(insert_realnum);
				psmt.setInt(1, pro.getid1());
				psmt.setString(2, pro.getproducts_id());
				psmt.setInt(3, pro.getreal_num1());
				psmt.setInt(4, pro.getreal_num2());
				psmt.setInt(5, pro.getreal_num3());
				psmt.setInt(6, pro.getreal_num4());
				psmt.setString(7, pro.getinstruction1());
				psmt.setString(8, pro.getinstruction2());
				psmt.setString(9, pro.getinstruction3());
				psmt.setString(10, pro.getinstruction4());
			    int a=psmt.executeUpdate();
			    System.out.println("a:"+a);
			}catch(Exception e){}
			  finally{
					 try{  
			         	psmt.close();  
			         }catch (Exception e) {}
			         try{    
			         	conn.close();  
			         }catch (Exception e) {}
			  }
		}
		//查询plan_num的最大id号
	    private static final String maxid="select max(id)id from plan_num ";
	    @SuppressWarnings("unchecked")
		public int queryid(){
			Connection conn=null;
			PreparedStatement psmt=null;
			ResultSet rs=null;	
	    	try{
	    		conn=DBcon.getConnection();	
				 psmt=conn.prepareStatement(maxid);
				 rs=psmt.executeQuery();	
	    		//rs = psmt.executeQuery(maxid);
					rs.next();
					int id = rs.getInt(1);
					System.out.println("id"+id);
					return id;
		        }
		        catch(Exception e){
		            return -1;
		        }
		        finally{
		            try{
			         	psmt.close();  
			         }catch (Exception e) {}
			         try{    
			         	conn.close();  
			         }catch (Exception e) {}
		        }
			}
		 //信息修改
		 private static final String update_realnum="update real_num set products_id=?,real_num1=?,real_num2=?,real_num3=?,real_num4=?,instruction1=?,instruction2=?,instruction3=?,instruction4=? where pid=?";
		 public void update1(pronum pro) throws SQLException{
			 Connection conn=null;
			 PreparedStatement psmt=null;
			 try{
				 conn=DBcon.getConnection();
				 psmt=conn.prepareStatement(update_realnum);
					psmt.setString(1, pro.getproducts_id());
					psmt.setInt(2, pro.getreal_num1());
					psmt.setInt(3, pro.getreal_num2());
					psmt.setInt(4, pro.getreal_num3());
					psmt.setInt(5, pro.getreal_num4());
					psmt.setString(6, pro.getinstruction1());
					psmt.setString(7, pro.getinstruction2());
					psmt.setString(8, pro.getinstruction3());
					psmt.setString(9, pro.getinstruction4());
					psmt.setInt(10, pro.getid1());
					psmt.executeUpdate();
				}catch(Exception e){}
				  finally{
						 try{  
				         	psmt.close();  
				         }catch (Exception e) {}
				         try{    
				         	conn.close();  
				         }catch (Exception e) {}
				  }
		 }
}