materialmaintaindao.java

JSP移动商品管理平台源代码.........

				}else{
					mmb.setReMark(rs.getString("ReMark").trim());
				}
				list.add(mmb);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
		}

		return list;
	}
	
	public MaterialMaintainBean getMaterialMaintainBeanOfMaintainID(int maintainID){
		MaterialMaintainBean mmb = new MaterialMaintainBean();
		dbpool.getConnection();
		String sql = "select MaintainID,MaterialName,MaModel,MaBrand,MamadeIn,MaterialTypeNo,MaintainCount,MaintainDate,UserCode,MaintainDep,ManagerBy,UseBy,RoomNu,BuildingNu,MaintainCause,MaintainFee,ReMark from Material_Maintain_Table where MaintainID="+maintainID;
		
		try {
			st = dbpool.conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				mmb.setMaintainID(rs.getInt("MaintainID"));
				if(rs.getString("MaterialName")==null){
					mmb.setMaterialName("");
				}else{
					mmb.setMaterialName(rs.getString("MaterialName").trim());
				}
				if(rs.getString("MaModel")==null){
					mmb.setMaModel("");
				}else{
					mmb.setMaModel(rs.getString("MaModel").trim());
				}
				if(rs.getString("MaBrand")==null){
					mmb.setMaBrand("");
				}else{
					mmb.setMaBrand(rs.getString("MaBrand").trim());
				}
				if(rs.getString("MaMadeIn")==null){
					mmb.setMaMadeIn("");
				}else{
					mmb.setMaMadeIn(rs.getString("MaMadeIn").trim());
				}
				mmb.setMaterialTypeNo(rs.getString("MaterialTypeNo"));
				mmb.setMaintainCount(rs.getInt("MaintainCount"));
				if(rs.getDate("MaintainDate")==null){
					mmb.setMaintainDate("");
				}else{
					mmb.setMaintainDate(rs.getDate("MaintainDate").toString().trim());
				}
				if(rs.getString("UserCode")==null){
					mmb.setUserCode("");
				}else{
					mmb.setUserCode(rs.getString("UserCode").trim());
				}
				mmb.setMaintainDep(rs.getInt("MaintainDep"));
				if(rs.getString("ManagerBy")==null){
					mmb.setManagerBy("");
				}else{
					mmb.setManagerBy(rs.getString("ManagerBy").trim());
				}
				if(rs.getString("UseBy")==null){
					mmb.setUseBy("");
				}else{
					mmb.setUseBy(rs.getString("UseBy").trim());
				}
				if(rs.getString("RoomNu")==null){
					mmb.setRoomNu("");
				}else{
					mmb.setRoomNu(rs.getString("RoomNu").trim());
				}
				if(rs.getString("BuildingNu")==null){
					mmb.setBuildingNu("");
				}else{
					mmb.setBuildingNu(rs.getString("BuildingNu").trim());
				}
				if(rs.getString("MaintainCause")==null){
					mmb.setMaintainCause("");
				}else{
					mmb.setMaintainCause(rs.getString("MaintainCause").trim());
				}
				mmb.setMaintainFee(rs.getDouble("MaintainFee"));
				if(rs.getString("ReMark")==null){
					mmb.setReMark("");
				}else{
					mmb.setReMark(rs.getString("ReMark").trim());
				}
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			dbpool.freeConnection();
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				st=null;
			}
		}
		if(mmb.getMaintainID()==0)
		{
			return null;
		}
		else
		return mmb;
	}
	
	//按条件查询 名称 维修人 责任人 使用人 时间间隔
	public ArrayList searchMaterialMaintain(String materialName,String userCode,String startDate ,String endDate ,String managerBy ,String useBy) throws Exception {
		 ArrayList arrayList = new ArrayList();
	        //Connection connection = null;
		 	DBPoolManager db = new DBPoolManager();
	        db.getConnection();
	        Statement statement = null;
	        ResultSet rs = null;
	        StringBuffer criteriaSql = new StringBuffer(512);
	        criteriaSql.append("SELECT * FROM Material_Maintain_Table WHERE ");
	        
	        	        
	        
	        if (!materialName.equals("")) {
	            criteriaSql.append("  materialName like'%" + materialName + "%' AND ");
	        } 
	         
	       
	        if (!userCode.equals("")) {
	        	 criteriaSql.append("userCode='" + userCode + "' AND ");
	            
	        }
	        if (!materialName.equals("")) {
	            criteriaSql.append("materialName like'%" + materialName + "%' AND ");
	        } 
	        if (!managerBy.equals("")) {
	            criteriaSql.append("managerBy='" + managerBy + "' AND ");
	        } 
	        if (!useBy.equals("")) {
	            criteriaSql.append("useBy='" + useBy + "' AND ");
	        } 
	        if((startDate != null && !startDate.equals("")) && (endDate != null && !endDate.equals(""))){
	        	criteriaSql.append(" MaintainDate between to_date('"+ startDate +"','YYYY-MM-DD') and to_date('"+endDate+"','YYYY-MM-DD')");
	        }
	       
	         //criteriaSql.append("ElecDevChkDate=to_date('" + elecDev_Chk.getElecDevChkDate()+ "','yy-mm-dd')");
	       
	        if (criteriaSql.substring(criteriaSql.length()-5).
		            equals(" AND "))
		        criteriaSql.delete(criteriaSql.length()-5,
		                criteriaSql.length()-1);
		    if (criteriaSql.substring(criteriaSql.length()-7).
		            equals(" WHERE "))
		        criteriaSql.delete(criteriaSql.length()-7,
		                criteriaSql.length()-1);
		    try {
		        //connection = DBConnection.getConnection();
		        statement = db.conn.createStatement();
		      
		        
		        rs = statement.executeQuery(criteriaSql.toString());
		        
		        while (rs.next()) {
		    		MaterialMaintainBean mmb = new MaterialMaintainBean();
		        	mmb.setMaintainID(rs.getInt("MaintainID"));
					if(rs.getString("MaterialName")==null){
						mmb.setMaterialName("");
					}else{
						mmb.setMaterialName(rs.getString("MaterialName").trim());
					}
					if(rs.getString("MaModel")==null){
						mmb.setMaModel("");
					}else{
						mmb.setMaModel(rs.getString("MaModel").trim());
					}
					if(rs.getString("MaBrand")==null){
						mmb.setMaBrand("");
					}else{
						mmb.setMaBrand(rs.getString("MaBrand").trim());
					}
					if(rs.getString("MaMadeIn")==null){
						mmb.setMaMadeIn("");
					}else{
						mmb.setMaMadeIn(rs.getString("MaMadeIn").trim());
					}
					mmb.setMaterialTypeNo(rs.getString("MaterialTypeNo"));
					mmb.setMaintainCount(rs.getInt("MaintainCount"));
					if(rs.getDate("MaintainDate")==null){
						mmb.setMaintainDate("");
					}else{
						mmb.setMaintainDate(rs.getDate("MaintainDate").toString().trim());
					}
					if(rs.getString("UserCode")==null){
						mmb.setUserCode("");
					}else{
						mmb.setUserCode(rs.getString("UserCode").trim());
					}
					mmb.setMaintainDep(rs.getInt("MaintainDep"));
					if(rs.getString("ManagerBy")==null){
						mmb.setManagerBy("");
					}else{
						mmb.setManagerBy(rs.getString("ManagerBy").trim());
					}
					if(rs.getString("UseBy")==null){
						mmb.setUseBy("");
					}else{
						mmb.setUseBy(rs.getString("UseBy").trim());
					}
					if(rs.getString("RoomNu")==null){
						mmb.setRoomNu("");
					}else{
						mmb.setRoomNu(rs.getString("RoomNu").trim());
					}
					if(rs.getString("BuildingNu")==null){
						mmb.setBuildingNu("");
					}else{
						mmb.setBuildingNu(rs.getString("BuildingNu").trim());
					}
					if(rs.getString("MaintainCause")==null){
						mmb.setMaintainCause("");
					}else{
						mmb.setMaintainCause(rs.getString("MaintainCause").trim());
					}
					mmb.setMaintainFee(rs.getDouble("MaintainFee"));
					if(rs.getString("ReMark")==null){
						mmb.setReMark("");
					}else{
						mmb.setReMark(rs.getString("ReMark").trim());
					}
		
		            arrayList.add(mmb);
		        }
		
		    } catch (SQLException e) {
		        e.printStackTrace();
		        throw new Exception();
		    } finally {
				//DBConnection.close(rs);
				//DBConnection.close(statement);
		    	db.freeConnection();
		    }
		    
		    return arrayList;
		}
	
}