tempfile.tmp

this is example use EJB with jboss.

	 * @return null if an error occurred, otherwise list of port objects 
	 * 		   contained in one ArrayList<PortVO> object
	 */
	public ArrayList<PortVO> getPorts(String condition){
		try{
			String sql   = null;
			ResultSet rs = null;
			ArrayList<PortVO> lstPorts = new ArrayList<PortVO>();
			conn.setAutoCommit(true);
			sql = " SELECT " + PortVO.PORT_ID       + "," +
			  				   PackageVO.PACKAGE_ID + "," +
			  				   PortVO.PORT_NAME     + " " +	  			
			  	  " FROM "   + PortVO.PORT_TBL 
			  	  			 + ((condition == null)?"":(" WHERE " + condition));
			  
			rs = conn.execQuery(sql);
			while (rs.next()){
				  PortVO portVO = new PortVO();
				  portVO.setID(rs.getLong(PortVO.PORT_ID));
				  portVO.setParentID(rs.getLong(PackageVO.PACKAGE_ID));
				  portVO.setName(rs.getString(PortVO.PORT_NAME));
	
				  lstPorts.add(portVO);
			  }
			  conn.closeStatement();
			  rs.close();
		    return lstPorts;
		
		} catch (SQLException e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPorts(" + condition + ")" + e.getMessage());
		} catch (Exception e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPorts(" + condition + ")" + e.getMessage());
		}
	 return null;
	}
	
	/**
	 * Load all user in database (root account is not involved) and contains into one the list object
	 * @return list of user objects contained in one ArrayList<UserVO> object
	 */
	public ArrayList<UserVO> loadUsers(){
		return (getUsers(UserVO.USER_NAME + "!= 'root'"));
	}
	
	/**
	 * Get users with a specified condition 
	 * @param condition
	 * 				set null if get user without condition, otherwise such as
	 * 				" column_name = 'x'"	
	 * @return null if an error occurred, otherwise list of user objects 
	 * 		   contained in one ArrayList<UserVO> object
	 */
	public ArrayList<UserVO> getUsers(String condition){
		ArrayList<UserVO> lstUser = new ArrayList<UserVO>();
		try{
			String sql = null;
			ResultSet rs = null;
			conn.setAutoCommit(true);
			sql = " SELECT " + UserVO.USER_ID   + "," +
			  				   UserVO.USER_NAME + "," +
			  				   UserVO.USER_TYPE + "," +
			  				   UserVO.USER_PASS + " " +
			  	  " FROM " + UserVO.USER_TBL + ((condition == null)?"":(" WHERE " + condition));
			rs = conn.execQuery(sql);
		    while (rs.next()){
				  UserVO userVO = new UserVO();
				  userVO.setID(rs.getLong(UserVO.USER_ID));
				  userVO.setName(rs.getString(UserVO.USER_NAME));
				  userVO.setRole(rs.getInt(UserVO.USER_TYPE));
				  userVO.setPassword(rs.getString(UserVO.USER_PASS));

				  lstUser.add(userVO);
			 }
			 conn.closeStatement();
			 rs.close();
		 return lstUser;
	    } catch (SQLException e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getUsers(" + condition + ")" + e.getMessage());
		} catch (Exception e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getUsers(" + condition + ")" + e.getMessage());
		}
		return lstUser;
	}
	
	
	
	/**
	 * Load package-package connection from database and contains into one the list object
	 * @return
	 */
	public ArrayList<PackageConnection> loadPackageConnection(){
		return (getPackageConnection(null));
	}
	
	/**
	 * Get list of package-package connection with a specified condition 
	 * @param condition
	 * 				set null if get user without condition, otherwise such as
	 * 				" column_name = 'x'"	
	 * @return null if an error occurred, otherwise list of user objects 
	 * 		   contained in one ArrayList<PackageConnection> object
	 */
	public ArrayList<PackageConnection> getPackageConnection(String condition){
		try{
			String sql = null;
			ResultSet rs = null;
			ArrayList<PackageConnection> lstPackageConn = new ArrayList<PackageConnection>();
			
			conn.setAutoCommit(true);  
			sql = " SELECT " + PackageConnection.PACKAGE_CONNECT_ID + "," +
							   PackageConnection.PACKAGE_ID_1 + "," +
							   PackageConnection.PACKAGE_ID_2 + "," +
							   PackageConnection.PACKAGE_CONNECT_COLOR + "," +
							   PackageConnection.PACKAGE_CONNECT_DIR   + " " +
			  	  " FROM " + PackageConnection.PACKAGE_CONNECT_TBL + ((condition == null)?"":(" WHERE " + condition));
			  
			rs = conn.execQuery(sql);
			//Add objects contained in the result set into one ArrayList<PackageConnection> object
			while (rs.next()){
				  PackageConnection packageConnObj =  new ConnectorVO().new PackageConnection();
				  packageConnObj.setID(rs.getLong(PackageConnection.PACKAGE_CONNECT_ID));
				  packageConnObj.setPackageID1(rs.getLong(PackageConnection.PACKAGE_ID_1));
				  packageConnObj.setPackageID2(rs.getLong(PackageConnection.PACKAGE_ID_2));
				  packageConnObj.setColor(rs.getLong(PackageConnection.PACKAGE_CONNECT_COLOR));
				  packageConnObj.setDirection(rs.getInt(PackageConnection.PACKAGE_CONNECT_DIR));
	
				  lstPackageConn.add(packageConnObj);
			}
			conn.closeStatement();
			rs.close();
		 return lstPackageConn;
	    } catch (SQLException e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPackageConnection(" + condition + ")" 
					 												+ e.getMessage());
		} catch (Exception e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPackageConnection(" + condition + ")" 
					 												+ e.getMessage());
		}
		return null;
	}
	
	/**
	 * Load package-port connection from database 
	 * and contains into one the ArrayList<PPConnection> list object
	 * @return null if an error occurred, otherwise ArrayList<PPConnection> object
	 */
	public ArrayList<PPConnection> loadPPConnection(){
		return (getPPConnection(null));
	}
	
	/**
	 * Get list of package-port connection with a specified condition 
	 * @param condition
	 * 				set null if get list of package-port connection without condition
	 * 				, otherwise such as	" column_name = 'x'"	
	 * @return null if an error occurred, otherwise ArrayList<PackageConnection> object
	 */
	public ArrayList<PPConnection> getPPConnection(String condition){
		try{
			String sql = null;
			ResultSet rs = null;
			conn.setAutoCommit(true);
			ArrayList<PPConnection> lstPPConn = new ArrayList<PPConnection>();
			sql = " SELECT " + PPConnection.CONNECT_ID  + "," +
							   PPConnection.PACKAGE_ID1 + "," +
							   PPConnection.PACKAGE_ID2 + "," +
							   PPConnection.PORT_ID1    + "," +
							   PPConnection.PORT_ID2    + "," +
							   PPConnection.CONNECT_DIR + " " +
			  	   " FROM " + PPConnection.CONNECT_TBL 
			  	   			+ ((condition == null)?"":(" WHERE " + condition));
			  
			  rs = conn.execQuery(sql);
			//Add objects contained in the result set into one ArrayList<PPConnection> object
			  while (rs.next()){
				  PPConnection ppConnObj =  new ConnectorVO().new PPConnection();
				  ppConnObj.setID(rs.getInt(PPConnection.CONNECT_ID));
				  ppConnObj.setPackageID1(rs.getLong(PPConnection.PACKAGE_ID1));
				  ppConnObj.setPackageID2(rs.getLong(PPConnection.PACKAGE_ID2));
				  ppConnObj.setPortID1(rs.getLong(PPConnection.PORT_ID1));
				  ppConnObj.setPortID2(rs.getLong(PPConnection.PORT_ID2));
				  ppConnObj.setDirection(rs.getInt(PPConnection.CONNECT_DIR));
	
				  lstPPConn.add(ppConnObj);
			  }
			  conn.closeStatement();
			  rs.close();
		 return lstPPConn;
		
		} catch (SQLException e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPPConnection(" + condition + ")" 
																   + e.getMessage());
		} catch (Exception e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPPConnection(" + condition + ")" 
																   + e.getMessage());
	  }
 	return null;
	}
	
		
	/**
	 * Load all type of package from database and contains into one the list object
	 * @return
	 */
	public ArrayList<PackageTypeVO> loadPackageTypes(){
		return (getPackageTypes(null));
	}
	public ArrayList<PackageTypeVO> getPackageTypes(String condition){
		
		ArrayList<PackageTypeVO> lstPackageType = new ArrayList<PackageTypeVO>();
		try{
			String sql = null;
			ResultSet rs = null;
			conn.setAutoCommit(true);
		    sql = " SELECT " + PackageTypeVO.PACKAGETYPE_ID   + "," +
		    				   PackageTypeVO.PACKAGETYPE_NAME + "," +
		    				   PackageTypeVO.PACKAGETYPE_PORT + " " +
			  	  " FROM "   + PackageTypeVO.PACKAGETYPE_TBL 
			  	  			 + ((condition == null)?"":(" WHERE " + condition));
			  
			rs = conn.execQuery(sql);
			while (rs.next()){
				  PackageTypeVO pt =  new PackageTypeVO();
				  pt.setID(rs.getLong(PackageTypeVO.PACKAGETYPE_ID));
				  pt.setName(rs.getString(PackageTypeVO.PACKAGETYPE_NAME));
				  pt.setDescPort(rs.getString(PackageTypeVO.PACKAGETYPE_PORT));
				  
				  lstPackageType.add(pt);
			  }
			conn.closeStatement();
			rs.close();
		 return lstPackageType;
		} catch (SQLException e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPackageTypes(" + condition + ")" 
					   										  + e.getMessage());
		} catch (Exception e){
			 LogWriter.getInstance(LogWriter.DB_LOG).error("getPackageTypes(" + condition + ")" 
					   										  + e.getMessage());
		}
		return lstPackageType;
		
	}

	/**
	 *  Get trap history from the database
	 * @param tableName
	 * 					Input a table name which existed in the database
	 * @param condition
	 * 					set null if get trap history without condition,
	 * 					otherwise such as " column_name = 'x'" etc,.				
	 * @param option
	 * 				Used for filter result that returned from SQL statement such as
	 * 				order the records or limit the number records to get
	 * @return
	 * @throws Exception
	 */
	public ArrayList<TrapVO> getTrapHistorys(String tableName,String condition, String option) throws Exception{
		try{
			String sql = null;
			ResultSet rs = null;
			ArrayList<TrapVO> lstTraps = new ArrayList<TrapVO>();
			
			//If string is described with only blank characters,
			//so execute SQL statement will crashed in below. Therefore it needed set to null 
			if (condition != null){
				if (condition.length() == 0){
					condition = null;
				}
			}
			conn.setAutoCommit(true);  
			sql = " SELECT " + TrapVO.TRAP_ID     + "," + 
							   NodeVO.NODE_NAME   + "," +
			  				   BlockVO.BLOCK_NAME + "," + 
			  				   BlockVO.BLOCK_IP   + "," +
			  				   PackageTypeVO.PACKAGETYPE_NAME + "," +
			  				   TrapVO.TRAP_SLOTNUM     + "," + 
			  				   TrapVO.TRAP_TYPE    + "," +
			  				   TrapVO.TRAP_LEVEL + "," +
			  				   TrapVO.TRAP_TIME     + " " +
			  		" FROM " + ((tableName == null)?getTableName(DBConstants.TRAP_OBJECT):(tableName))
			  			     + ((condition == null)?"":(" WHERE " + condition)) 
			  		         + ((option == null)?"":(" " + option)) ;
			  
			  rs = conn.execQuery(sql);
			  //Add objects contained in the result set into one ArrayList<TrapVO> object
			  while (rs.next()){
				  TrapVO trapvo =  new TrapVO();
				  trapvo.setID(rs.getLong(TrapVO.TRAP_ID));
				  trapvo.setNodeName(rs.getString( NodeVO.NODE_NAME));
				  trapvo.setBlockName(rs.getString(BlockVO.BLOCK_NAME));
				  trapvo.setBlockIP(rs.getString(BlockVO.BLOCK_IP));
				  trapvo.setPackageTypeName(rs.getString(PackageTypeVO.PACKAGETYPE_NAME));
				  trapvo.setSlotNumber(rs.getInt(TrapVO.TRAP_SLOTNUM));
				  trapvo.setTrapType(rs.getString(TrapVO.TRAP_TYPE));
				  trapvo.setTrapLevel(rs.getString(TrapVO.TRAP_LEVEL));
				  trapvo.setTime(rs.getLong( TrapVO.TRAP_TIME));
	
				  lstTraps.add(trapvo);
			  }
			  //Releases this ResultSet, statement object's database and JDBC resources immediately 
			  //instead of waiting for this to happen when it is automatically closed. 
			  conn.closeStatement();
			  rs.close();
			  return lstTraps;
		} catch (SQLException e){
			throw e;
		} catch (Exception e){
			throw e;
		}
	}
	

	/**
	 *  Get user log from the database
	 * @param tableName
	 * 					Input a table name which existed in the database
	 * @param condition
	 * 					set null if get user log without condition,
	 * 					otherwise such as " column_name = 'x'" etc,.				
	 * @param option
	 * 				Used for filter result that returned from SQL statement such as
	 * 				order the records or limit the number records to get
	 * @return
	 * @throws Exception
	 */
	public ArrayList<OperationVO> getUserLogs(String tableName,String condition, String option) throws Exception{
		try{
			String sql = null;
			ResultSet rs = null;
			ArrayList<OperationVO> lstUserLog = new ArrayList<OperationVO>();
			
			//If string is described with only blank characters,
			//so execute SQL statement will crashed in below. Therefore it needed set to null 
			if(condition != null){
				if(condition.length() == 0){
					condition = null;
				}
			}
			conn.setAutoCommit(true);
			sql = " SELECT " + OperationVO.OPERATION_ID      + "," +
			  				   UserVO.USER_NAME              + "," +
			  				   OperationVO.OPERATION_TIME    + "," +
			  				   OperationVO.OPERATION_CONTENT + " " +
				  " FROM " + ((tableName == null)?getTableName(DBConstants.LOG_OBJECT):(tableName))
			  			   + ((condition == null)?"":(" WHERE " + condition)) 
			  		       + ((option    == null)?"":(" " + option)) ;
			rs = conn.execQuery(sql);
			//Add objects contained in the result set into one ArrayList<OperationVO> object
			while (rs.next()){
				 			
				  OperationVO userLog = new OperationVO();
				  userLog.setID(rs.getLong(OperationVO.OPERATION_ID));
				  userLog.setUserName(rs.getString(UserVO.USER_NAME));
				  userLog.setTime(rs.getLong(OperationVO.OPERATION_TIME));
				  userLog.setContent(rs.getString(OperationVO.OPERATION_CONTENT));
			
				  lstUserLog.add(userLog);
			}
			//Releases this ResultSet, statement object's database and JDBC resources immediately 
			//instead of waiting for this to happen when it is automatically closed. 
			conn.closeStatement();
			rs.close();
    		return lstUserLog;
		} catch (SQLException e){
			throw e;
		} catch (Exception e){
			throw e;
		}
	}