dbfunctionsmanager.java

this is example use EJB with jboss.

			if (conn == null){
				throw new SQLException("Connection has closed.");
			}
			conn.setAutoCommit(false);
			result = addObject2DBBuffer(object);
			if (result.isFailed()) {
				throw new SQLException(result.getMessage());
			}
			conn.commit();
			LogWriter.getDBLogger().info("Added complete.");
			result.returnCode(JResult.DBOPR_SUCCESS);
			return result;

		} catch (SQLException e) {
			LogWriter.getDBLogger().error(e.getMessage());
			result.setMessage(e.getMessage());
			conn.rollback();
		} catch (Exception e){
			LogWriter.getDBLogger().error(e.getMessage());
			result.setMessage(e.getMessage());
		}
		result.returnCode(JResult.DBOPR_ERROR_INSERT_FAILED);
		return result;
	}
	/**
	 * Add object without notify error message
	 * @param object
	 */
	public void addObject2DB_NoErr(Object object){
		try{
			addObject2DB(object);
		} catch (Exception e){}
	}
	
	

	/**
	 * add an object into cache store of database
	 * 
	 * @param object
	 *            Type of object is valid consists of NodeVO, BlockVO PortVO,
	 *            UserVO, TrapVO, OperationVO, PackageConnection and
	 *            PPConnection
	 * @return a JResult object. Check method isSucceeded() true if success.
	 */
	private JResult addObject2DBBuffer(Object object) {
		JResult result = null;
		try {
			String sql = null;
			int objectType = -1;
			int objectID = -1;
			result = new JResult();
			if (object instanceof NodeVO) {
				objectType = DBConstants.NODE_OBJECT;
				sql = getSQLCmd_InsUpd2NodeTbl((NodeVO) object, DBConstants.DBOPR_INSERT
															  , DBConstants.DBPR_TYPE_ALL);
			} else if (object instanceof BlockVO) {
				objectType = DBConstants.FUNCBLOCK_OBJECT;
				// Check the parent object
				if (findObject(DBConstants.NODE_OBJECT, ((BlockVO) object).getParentID()) <= 0){
					// Error object was not found
					result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
					return result;
				}
				// Generate insert SQL statement
				sql = getSQLCmd_InsUpd2BlockTbl((BlockVO) object, DBConstants.DBOPR_INSERT
																, DBConstants.DBPR_TYPE_ALL);
			} else if (object instanceof PackageVO) {
				objectType = DBConstants.PACKAGE_OBJECT;
				PackageVO packageVO = (PackageVO) object;
				// Check the parent object
				if (findObject(DBConstants.FUNCBLOCK_OBJECT, packageVO.getParentID()) <= 0) {
					// Error object was not found
					result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
					result.setMessage("Block with ID '"	+ packageVO.getParentID() + "' was not found.");
					return result;
				}
				if (packageVO.getListPortVO() != null) {
					if (!packageVO.getListPortVO().isEmpty()) {
						result.returnCode(JResult.DBOPR_ERROR_OBJECT_NOT_SUPPORTED);
						result.setMessage("The input package object contains one or more of port's data. " +
										  "Use method addPackage2DB() instead of.");
						return result;
					}
				}
				// Generate insert SQL statement
				sql = getSQLCmd_InsUpd2PackageTbl((PackageVO) object,
						DBConstants.DBOPR_INSERT, DBConstants.DBPR_TYPE_ALL);

			} else if (object instanceof PortVO) {
				objectType = DBConstants.PORT_OBJECT;
				// Check the parent object
				if (findObject(DBConstants.PACKAGE_OBJECT, ((PortVO) object).getParentID()) <= 0) {
					// Error object was not found
					result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
					return result;
				}
				// Generate insert SQL statement
				sql = getSQLCmd_InsUpd2PortTbl((PortVO) object,	DBConstants.DBOPR_INSERT);

			} else if (object instanceof UserVO) {
				objectType = DBConstants.USER_OBJECT;
				if (((UserVO) object).getName().equalsIgnoreCase("antuti")) {// User default
					throw new Exception("Username is invalid");
				}
				sql = getSQLCmd_InsUpd2UserTbl((UserVO) object, DBConstants.DBOPR_INSERT);
			} else if (object instanceof PackageConnection) {
				objectType = DBConstants.PACKAGE_PACKAGE_OBJECT;
				// Check the object
				JResult ret = checkPackagePConnection((PackageConnection) object);
				if (ret.isFailed()) {
					return ret;
				}
				sql = getSQLCmd_InsUpd2PackageConnectionTbl((PackageConnection) object, DBConstants.DBOPR_INSERT);
			} else if (object instanceof PPConnection) {
				objectType = DBConstants.PACKAGE_PORT_OBJECT;
				JResult ret = checkPPConnection((PPConnection) object);
				if (ret.isFailed()) {
					return ret;
				}
				// Generate insert SQL statement
				sql = getSQLCmd_InsUpd2PPConnectionTbl((PPConnection) object, DBConstants.DBOPR_INSERT);

			} else if (object instanceof OperationVO) {
				objectType = DBConstants.OPERATION_OBJECT;
				// Check over the limit number of records in database
				long nOperationRecord;
				nOperationRecord = countObject(DBConstants.OPERATION_OBJECT, null, null);
				if (nOperationRecord > Setting.getDBMaxCapcityOperHistory()) {
					LogWriter.getDBLogger().info("Total operation log current: " + nOperationRecord);
					// Backup some part of records into CSV file then delete from database
					exportUserLog2CSV(operationCsvFile, tmBackupInDays);
				}
				sql = getSQLCmd_Ins2LogTbl((OperationVO) object);

			} else if (object instanceof PackageTypeVO) {
				objectType = DBConstants.PACKAGETYPE_OBJECT;
				// Generate insert SQL statement
				sql = getSQLCmd_InsUpd2PackageTypeTbl((PackageTypeVO) object, DBConstants.DBOPR_INSERT);
			} else if (object instanceof TrapVO) {
				objectType = DBConstants.TRAP_OBJECT;
				TrapVO tvo = (TrapVO)object;
				LogWriter.getDBLogger().info("Trap IP: " + tvo.getBlockIP());
				// Check over the limit number of records in database
				long nTrapRecord;
				nTrapRecord = countObject(DBConstants.TRAP_OBJECT, null, null);
				if (nTrapRecord > Setting.getDBMaxCapcityAlarmHistory()) {
					LogWriter.getDBLogger().info("Total Traps current: " + nTrapRecord);
					// Backup some part of records into CSV file then delete
					// from database
					exportTrap2CSV(alarmCsvFile, tmBackupInDays);
				}
				// Check and update alarm 
				//tvo = processAlarmSystem(tvo);
				sql = getSQLCmd_Ins2TrapTbl(tvo);
				LogWriter.getDBLogger().debug(sql);
			} else {
				System.out.println("Error: The object is not supported!");
				// LOG HERE
				result.returnCode(JResult.DBOPR_ERROR_OBJECT_NOT_SUPPORTED);
				return result;
			}
			// Append keyword "RETURNING" to insert SQL statement to get ID
			// which generated by auto-increment field.
			sql += " RETURNING " + getIDName(objectType);
			// Execute SQL statement
			if (conn == null){
				throw new SQLException("Connection has closed.");
			}
			ResultSet rs = conn.execQuery(sql);
			while (rs.next()) {
				objectID = rs.getInt(1);
			}
			// Return object's id and operation code.
			result.setValue(objectID);
			result.returnCode(JResult.DBOPR_SUCCESS);
			return result;

		} catch (SQLException e) {
			result.setMessage(e.getMessage());
		} catch (Exception e) {
			result.setMessage(e.getMessage());
		}
		LogWriter.getDBLogger().error(result.getMessage());
		result.returnCode(JResult.DBOPR_ERROR_INSERT_FAILED);
		return result;
	}

	/**
	 * Convert a alarm level name into integer value corresponding
	 * @param strAlarm
	 * 				Name of alarm level
	 * @return alarm level in integer
	 */
	private int getAlarmLevel(String strAlarm) {
		int intAlarm = 0;
		if (strAlarm.equalsIgnoreCase(AlarmLevelConstants.EVENT_STR)) {
			intAlarm = AlarmLevelConstants.EVENT_INT;
		} else if (strAlarm.equalsIgnoreCase(AlarmLevelConstants.WARNING_STR)) {
			intAlarm = AlarmLevelConstants.WARNING_INT;
		} else if (strAlarm.equalsIgnoreCase(AlarmLevelConstants.MINOR_STR)) {
			intAlarm = AlarmLevelConstants.MINOR_INT;
		} else if (strAlarm.equalsIgnoreCase(AlarmLevelConstants.MAJOR_STR)) {
			intAlarm = AlarmLevelConstants.MAJOR_INT;
		} else if (strAlarm.equalsIgnoreCase(AlarmLevelConstants.CRITICAL_STR)) {
			intAlarm = AlarmLevelConstants.CRITICAL_INT;
		} else if (strAlarm.equalsIgnoreCase(AlarmLevelConstants.CLEARED_STR)) {
			intAlarm = AlarmLevelConstants.CLEARED_INT;
		} else if (strAlarm.equalsIgnoreCase(AlarmLevelConstants.UNDEFINE_STR)) {
			intAlarm = AlarmLevelConstants.UNDEFINE_INT;
		}
		return intAlarm;
	}

	/**
	 * Update alarm value when new trap come
	 * @param trapvo
	 * 				TrapVO object
	 * @return TrapVO with alarm value might modified if no error occurs, otherwise nothing changed.
	 */
	public TrapVO processAlarmSystem(TrapVO trapvo) {
		int alarmValue1 = 0; // in hex
		int alarmValue2 = 0; // in hex
		int slot;
		int alarmValue = 0; // value of alarm in integer
		String condition = null;
		String op = null;
		ArrayList<TrapVO> lstTraps = null;
		
		boolean isfirstObjectTrap = false;
		alarmValue = getAlarmLevel(trapvo.getTrapLevel());
		if (alarmValue == AlarmLevelConstants.CLEARED_INT){
			return trapvo;
		}
		alarmValue1 = alarmValue; 
		alarmValue2 = alarmValue;
		slot = trapvo.getSlotNumber();

		try {
			LogWriter.getDBLogger().warn("Processing new trap alarm...");
			if (slot == 1) { // CU-422
				condition = BlockVO.BLOCK_IP + "='" + trapvo.getBlockIP() + "'"
				+ " AND " + TrapVO.TRAP_SLOTNUM + "=" + slot;
				
				//Get records with the condition specified after order by time and limit the number of records
				op = " ORDER BY " + TrapVO.TRAP_TIME + " DESC LIMIT 1 OFFSET 0";
				try {
					lstTraps = getTrapHistorys(TrapVO.TRAP_VTBL, condition, op, false, "");
					if (!lstTraps.isEmpty()){ 
						 alarmValue1 = lstTraps.get(0).getTrapStatusOnMainView();
						 alarmValue2 = lstTraps.get(0).getTrapStatusOnImplView();
					} else {
						isfirstObjectTrap = true;
					}
				
				} catch (Exception e) {
					LogWriter.getDBLogger().error("No trap for found.");
					isfirstObjectTrap = true;
				}
				
				// Check OID
				String szOID = trapvo.getTrapOID();
				if (szOID.equalsIgnoreCase(AWGNEConstants.OID_POWER_1) // OID for power 1
					|| szOID.equalsIgnoreCase(AWGNEConstants.OID_FAN_1)){// OID for fan 1 
							
							alarmValue = alarmValue*16*16;
							alarmValue1 = (alarmValue1 & 0x0ff) | alarmValue;
							alarmValue2 = (alarmValue2 & 0x0ff) | alarmValue;
							if (isfirstObjectTrap) {
								alarmValue1 = alarmValue;
								alarmValue2 = alarmValue;
							}
						
				} else if (szOID.equalsIgnoreCase("AWGNEConstants.OID_POWER_2") // OID for power 2
							|| szOID.equalsIgnoreCase(AWGNEConstants.OID_FAN_2)) { // OID for fan 2
										
							alarmValue = alarmValue*16;
							alarmValue1 = (alarmValue1 & 0xf0f) | alarmValue;
							alarmValue2 = (alarmValue2 & 0xf0f) | alarmValue;
							if (isfirstObjectTrap) {
								alarmValue1 = alarmValue;
								alarmValue2 = alarmValue;
							}
				} else {
							alarmValue1 = (alarmValue1 & 0xff0) | alarmValue;
							alarmValue2 = (alarmValue2 & 0xff0) | alarmValue;
							if (isfirstObjectTrap) {
								alarmValue1 = alarmValue;
								alarmValue2 = alarmValue;
							}
				}
			} 			
			trapvo.setTrapStatusOnMainView(alarmValue1);
			trapvo.setTrapStatusOnImplView(alarmValue2);
			 
		} catch (Exception e) {
			LogWriter.getDBLogger().error("Error occurs during update alarm status when save new trap. "
											+ e.getMessage());
			LogWriter.getDBLogger().trace(e);
		}
		return trapvo;	
	}
//	public boolean processAlarmSystem(TrapVO trapvo) {
//		ArrayList<PackageVO> lstPackage;
//		ArrayList<BlockVO> lstBlock;
//		int alarmValue = 0; // value of alarm in integer
//		int alarmValue1 = 0; // in hex
//		int alarmValue2 = 0; // in hex
//		int slot;
//		try {
//			alarmValue = getAlarmLevel(trapvo.getTrapLevel());
//			if (alarmValue == AlarmLevelConstants.CLEARED_INT){
//				return true;
//			}
//			alarmValue1 = alarmValue;
//			alarmValue2 = alarmValue;
//			slot = trapvo.getSlotNumber();
//			// Find block
//			lstBlock = getBlocks(BlockVO.BLOCK_IP + "='" + trapvo.getBlockIP() + "'");
//			if ((lstBlock != null) && (!lstBlock.isEmpty())) {
//				// Check package slot
//				lstPackage = getPackages(PackageVO.PACKAGE_SLOT + "='" + slot
//									+ "'" + " AND " + BlockVO.BLOCK_ID + "="
//									+ lstBlock.get(0).getID());
//				
//				if ((lstPackage != null) && (!lstPackage.isEmpty())) {
//					PackageVO p = lstPackage.get(0);
//					long packageID = p.getID();
//					int rsfind=0;
//					//check package name
//					rsfind = findObject(DBConstants.PACKAGETYPE_OBJECT
//							 , PackageTypeVO.PACKAGETYPE_NAME + "='" + trapvo.getPackageTypeName()+ 
//							 "' AND " + PackageTypeVO.PACKAGETYPE_ID + "=" + p.getPackageTypeID());
//					if (rsfind > 0){
//						if (slot == 1) { // CU-422
//							// Check OID
//							String szOID = trapvo.getTrapOID();
//							alarmValue1 = p.getAlarm1();
//							alarmValue2 = p.getAlarm2();
//							if (szOID.equalsIgnoreCase("1.3.6.1.4.1.3930.101.2.5.4.2.1.3.5") // OID for power 1
//								|| szOID.equalsIgnoreCase("1.3.6.1.4.1.3930.101.2.5.4.2.1.3.7")){// OID for fan 1 
//							
//								alarmValue1 = (alarmValue1 & 0x0ff) | alarmValue*16*16;
//								alarmValue2 = (alarmValue2 & 0x0ff) | alarmValue*16*16;
//							
//							} else if (szOID.equalsIgnoreCase("1.3.6.1.4.1.3930.101.2.5.4.2.1.3.6") // OID for power 2
//									|| szOID.equalsIgnoreCase("1.3.6.1.4.1.3930.101.2.5.4.2.1.3.8")) { // OID for fan 2
//																								
//								alarmValue1 = (alarmValue1 & 0xf0f) | alarmValue*16;
//								alarmValue2 = (alarmValue2 & 0xf0f) | alarmValue*16;
//							} else {
//								alarmValue1 = (alarmValue1 & 0xff0) | alarmValue;
//								alarmValue2 = (alarmValue2 & 0xff0) | alarmValue;
//							}
//						}
//					// Update package alarm1 and alarm2 with value equally
//					updatePackageAlarm(packageID, Constants.DIAGRAM_VIEW, alarmValue1);
//					updatePackageAlarm(packageID, Constants.PACKAGE_IMPL_VIEW, alarmValue2);
//					return true;
//					}
//				} 
//				// Update alarm for block
//				updateBlockAlarm(lstBlock.get(0).getID(), alarmValue);
//				LogWriter.getDBLogger().warn("Processing new trap alarm...Warning: Function block detected"
//												+ ", but package was not found (ignored).");
//				LogWriter.getDBLogger().info("Update block alarm with new value " + alarmValue);
//				return true;
//				
//			} else {
//				LogWriter.getDBLogger().warn("The info of function block in trap alarm was not found.");
//			}
//		} catch (Exception e) {
//			LogWriter.getDBLogger().error("Error occurs during update alarm status when save new trap. "
//											+ e.getMessage());
//			LogWriter.getDBLogger().trace(e);
//		}
//		return false;
//	}

	/**
	 * Update for only alarm field of function block object
	 * 
	 * @param id
	 *            Block ID
	 * @param value
	 *            Value of alarm