dbfunctionsmanager.java

this is example use EJB with jboss.

	 * @return JResult object. Check method isSucceeded() true if success, otherwise false
	 */
	public JResult updateBlockAlarm(long id, int value) {
		JResult result = new JResult();
		String sql = null;
		try {
			LogWriter.getDBLogger().info(this.userName + " is updating block alarm <BlockID: " + 
												id + ", value: " + value + ">");
			if (conn == null){
				throw new SQLException("Connection has closed.");
			}
			clearFieldStorer();
			//putfval(BlockVO.BLOCK_ALARM, value);
			sql = genUpdateCmd(BlockVO.BLOCK_TBL, BlockVO.BLOCK_ID + " = " + id);
			// Execute SQL statement
			if (conn.execUpdate(sql) > 0) {
				LogWriter.getDBLogger().info("Update success");
				result.returnCode(JResult.DBOPR_SUCCESS);
				return result;
			} else {
				result.setMessage("The object's value id maybe not existed.");
			}
		} catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		LogWriter.getDBLogger().error(result.getMessage());
		result.returnCode(JResult.DBOPR_ERROR_UPDATE_FAILED);
		return result;
	}

	/**
	 * Update alarm corresponding with each window view
	 * @param id
	 * 			Package's ID
	 * @param wview
	 * 				Specify value Constants.DIAGRAM_VIEW if want to update
	 * 				for window diagram or Constants.PACKAGE_IMPL_VIEW for
	 * 				Package implement window
	 * 			
	 * @param value
	 * 				Value of alarm level, this will be added immediately into database
	 * @return JResult object. Check method isSucceeded() true if success, otherwise false
	 */
	public JResult updatePackageAlarm(long id, int wview, int value) {
		JResult result = new JResult();
		String sql = null;
		try {
			LogWriter.getDBLogger().info(this.userName + " is updating package alarm <PackageID: " + 
							id + ", Window: " + wview + ", value: " + value + ">");
			if (conn == null){
				throw new SQLException("Connection has closed.");
			}
			clearFieldStorer();
			if (wview == Constants.DIAGRAM_VIEW) {
				//putfval(PackageVO.PACKAGE_ALARM_1, value);
			} else if (wview == Constants.PACKAGE_IMPL_VIEW) {
				//putfval(PackageVO.PACKAGE_ALARM_2, value);
			}
			sql = genUpdateCmd(PackageVO.PACKAGE_TBL, PackageVO.PACKAGE_ID + " = " + id);

			// Execute SQL statement
			if (conn.execUpdate(sql) > 0) {
				LogWriter.getDBLogger().info("Updated complete.");
				result.returnCode(JResult.DBOPR_SUCCESS);
				return result;
			} else {
				result.setMessage("The object's value id maybe not existed.");
			}
		} catch (SQLException e) {
			result.setMessage(e.getMessage());
		}
		result.returnCode(JResult.DBOPR_ERROR_UPDATE_FAILED);
		return result;
	}

	/**
	 * Turn off trap alarm corresponding with each window view
	 * @param tvo
	 * 			TrapVO object
	 * @param wview
	 * 				Specify value Constants.DIAGRAM_VIEW if want to update
	 * 				for window diagram or Constants.PACKAGE_IMPL_VIEW for
	 * 				Package implement window
	 * 			
	 * @param value
	 * 				Value of alarm level, this will be added immediately into database
	 * @return JResult object. Check method isSucceeded() true if success, otherwise false
	 */
	public JResult turnOffTrapAlarm(TrapVO tvo, int wview, int value) throws SQLException{
		JResult result = new JResult();
//		ArrayList<TrapVO> lstTraps;
		String sql = null;
//		int slot;
//		String szOID;
//		String condition;
//		String op;
		String strView = ((wview == Constants.DIAGRAM_VIEW)?" Main view ":" Package implement view ");
//		slot = tvo.getSlotNumber();
//		szOID = tvo.getTrapOID();
		try {
			assertObjectInfo(tvo, "");
			LogWriter.getDBLogger().info(this.userName + 
					" is updating trap alarm status on " + strView + "of trap: " + tvo.toString());
			if (conn == null){
				throw new SQLException("Connection has closed.");
			}
			
//			if (slot == 1){
//			
//				if (!"".equalsIgnoreCase(szOID)){
//					condition = BlockVO.BLOCK_IP + "='" + tvo.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()){ 
//							LogWriter.getDBLogger().error("Turn off trap failed. No traps exist on database");
//						    return new JResult(JResult.DBOPR_FAILED,"No traps exist on database");
//						}
//						value = lstTraps.get(0).getTrapStatusOnImplView();
//		
//					} catch (Exception e) {
//						LogWriter.getDBLogger().error("Turn off trap failed. Error: " + e.getMessage());
//						return new JResult(JResult.DBOPR_FAILED, e.getMessage());
//					}
//					
//					if (szOID.equalsIgnoreCase(AWGNEConstants.OID_POWER_1) // OID for power 1
//							|| szOID.equalsIgnoreCase(AWGNEConstants.OID_FAN_1)){// OID for fan 1 
//							
//							value = value & 0x0ff;
//					} else if (szOID.equalsIgnoreCase(AWGNEConstants.OID_POWER_2) // OID for power 2
//									|| szOID.equalsIgnoreCase(AWGNEConstants.OID_FAN_2)) { // OID for fan 2
//								
//							value = value & 0xf0f;
//							
//					} else {
//							value = value & 0xff0;
//					}
//				}
//			} 
			clearFieldStorer();
			if (wview == Constants.DIAGRAM_VIEW) {
				putfval(TrapVO.MAIN_STATUS, value);
			} else if (wview == Constants.PACKAGE_IMPL_VIEW) {
				putfval(TrapVO.IMPL_STATUS, value);
			}
			sql = genUpdateCmd(TrapVO.TRAP_TBL, TrapVO.TRAP_TIME + " = " + tvo.getTime()
					+ " AND " + BlockVO.BLOCK_IP + "='" + tvo.getBlockIP() + "'"
					+ " AND " + TrapVO.TRAP_SLOTNUM + "=" + tvo.getSlotNumber());

			// Execute SQL statement
			if (conn.execUpdate(sql) > 0) {
				LogWriter.getDBLogger().info("Updated complete.");
				result.returnCode(JResult.DBOPR_SUCCESS);
				return result;
			} else {
				result.setMessage("The object's value id maybe not existed.");
			}
		} catch (SQLException e) {
			result.setMessage(e.getMessage());
			LogWriter.getDBLogger().error(e.getMessage());
			throw e;
		}
		result.returnCode(JResult.DBOPR_ERROR_UPDATE_FAILED);
		return result;
	}
	
	/**
	 * Update the position and dimension of node, block and package
	 * 
	 * @param nodeHelper
	 *            NodeHelper object contains new position of node, block and
	 *            package
	 * @return a JResult object. Check method isSucceeded() true if success.
	 * @throws Exception
	 * 				throws a exception if an error occurs.
	 */
	public JResult updateObjectPosition2DB(NodeHelper nodeHelper)
														throws Exception {
		JResult result = new JResult();
		try {
			LogWriter.getDBLogger().info(this.userName + " is updating position of node and child...");
			if (conn == null){
				throw new Exception("Connection has closed.");
			}
			conn.setAutoCommit(false);
			Vector<NodeVO> vtNodes = nodeHelper.getListNodeVO();
			if (vtNodes != null) {
				for (int iNode = 0; iNode < vtNodes.size(); iNode++) {
					NodeVO nvo = vtNodes.get(iNode);
					// Update position and dimension of node
					result = updateObjectSeparator2DBBuffer(nvo, DBConstants.DBPR_TYPE_POS);
					if (result.isFailed()) {
						throw new SQLException(result.getMessage());
					}
					// Get list of child objects for each node
					Vector<BlockVO> vtBlocks = nvo.getListBlockVO();
					if (vtBlocks != null) {
						for (int iBlock = 0; iBlock < vtBlocks.size(); iBlock++) {
							BlockVO bvo = vtBlocks.get(iBlock);
							// Update position and dimension of block
							result = updateObjectSeparator2DBBuffer(bvo, DBConstants.DBPR_TYPE_POS);
							if (result.isFailed()) {
								throw new SQLException(result.getMessage());
							}
							// Get list of child objects for each block
							Vector<PackageVO> vtPackages = bvo.getListPackageVO();
							if (vtPackages != null) {
								for (int iPkg = 0; iPkg < vtPackages.size(); iPkg++) {
									PackageVO pkgvo = vtPackages.get(iPkg);
									// Update position and dimension of package
									result = updateObjectSeparator2DBBuffer(pkgvo, DBConstants.DBPR_TYPE_POS);
									if (result.isFailed()) {
										throw new SQLException(result.getMessage());
									}

								}
							}
						}
					}
				}
			}
			// if all of operation have complete with no error, so commit.
			conn.commit();
			LogWriter.getDBLogger().info("Updated complete");
			result.returnCode(JResult.DBOPR_SUCCESS);
			return result;
		} catch (SQLException e) {
			result.setMessage(e.getMessage());
			conn.rollback();
		} catch (Exception e) {
			result.setMessage(e.getMessage());
		}
		result.returnCode(JResult.DBOPR_ERROR_UPDATE_FAILED);
		return result;
	}

	/**
	 * Update object data in database
	 * 
	 * @param object
	 *            Type of object is valid consists of NodeVO, BlockVO PortVO,
	 *            UserVO, PackageConnection and PPConnection (not for Trap and
	 *            Operation)
	 * @return a JResult object. Check method isSucceeded() true if success.
	 * @throws Exception
	 * 				throws a exception if an error occurs.
	 */
	public JResult updateObjectSeparator2DB(Object object) throws Exception {
		JResult result = new JResult();
		try {
			assertObjectInfo(object, this.userName + " is updating");
			if (conn == null){
				throw new Exception("Connection has closed.");
			}
			conn.setAutoCommit(false);
			result = updateObjectSeparator2DBBuffer(object,
										DBConstants.DBPR_TYPE_ALL);
			if (result.isFailed()) {
				throw new SQLException(result.getMessage());
			}
			conn.commit();
			LogWriter.getDBLogger().info("Updated complete.");
			result.returnCode(JResult.DBOPR_SUCCESS);
			return result;
		} catch (SQLException e) {
			result.setMessage(e.getMessage());
			conn.rollback();
		} catch (Exception e) {
			result.setMessage(e.getMessage());
		}
		LogWriter.getDBLogger().error(result.getMessage());
		result.returnCode(JResult.DBOPR_ERROR_UPDATE_FAILED);
		return result;
	}

	/**
	 * Update object data in database
	 * 
	 * @param object
	 *            Type of object is valid consists of NodeVO, BlockVO PortVO,
	 *            UserVO, PackageConnection and PPConnection (not for Trap and
	 *            Operation)
	 * 
	 * @param op
	 *            Specify that update all field of object or only position and
	 *            dimension. (DBConstants.DBPR_TYPE_ALL for all or
	 *            DBConstants.DBPR_TYPE_POS for only position and dimension)
	 * 
	 * @return a JResult object. Check method isSucceeded() true if success.
	 * @throws Exception
	 * 				throws a exception if an error occurs.
	 */
	private JResult updateObjectSeparator2DBBuffer(Object object, int op) {
		JResult result = null;
		String sql = null;
		try {
			result = new JResult();
			if (object instanceof NodeVO) {
				sql = getSQLCmd_InsUpd2NodeTbl((NodeVO) object,	DBConstants.DBOPR_UPDATE, op);
			} else if (object instanceof BlockVO) {
				// Check the parent object
				if (findObject(DBConstants.NODE_OBJECT, ((BlockVO) object).getParentID()) <= 0) {
					// Error object not existed
					result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
					return result;
				}
				sql = getSQLCmd_InsUpd2BlockTbl((BlockVO) object, DBConstants.DBOPR_UPDATE, op);

			} else if (object instanceof PackageVO) {
				// Check the parent object
				if (findObject(DBConstants.FUNCBLOCK_OBJECT,
						((PackageVO) object).getParentID()) <= 0) {
					// Error object not existed
					result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
					result.setMessage("Block with ID:" + ((PackageVO) object).getParentID()
																		+ "was not found.");
					return result;
				}
				sql = getSQLCmd_InsUpd2PackageTbl((PackageVO) object, DBConstants.DBOPR_UPDATE, op);

			} else if (object instanceof PortVO) {
				// Check the parent object
				if (findObject(DBConstants.PACKAGE_OBJECT, ((PortVO) object).getParentID()) <= 0) {
					// Error object not existed
					result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
					return result;
				}
				// Generate update SQL statement
				sql = getSQLCmd_InsUpd2PortTbl((PortVO) object,	DBConstants.DBOPR_UPDATE);

			} else if (object instanceof UserVO) {
				// Generate update SQL statement
				sql = getSQLCmd_InsUpd2UserTbl((UserVO) object, DBConstants.DBOPR_UPDATE);

			} else if (object instanceof PackageConnection) {
				// Check the object
				JResult ret = checkPackagePConnection((PackageConnection) object);
				if (ret.isFailed()) {
					return ret;
				}
				// Generate update SQL statement
				sql = getSQLCmd_InsUpd2PackageConnectionTbl(
						(PackageConnection) object, DBConstants.DBOPR_UPDATE);

			} else if (object instanceof PPConnection) {
				JResult ret = checkPPConnection((PPConnection) object);
				if (ret.isFailed()) {
					return ret;
				}
				// Generate update SQL statement
				sql = getSQLCmd_InsUpd2PPConnectionTbl((PPConnection) object,
						DBConstants.DBOPR_UPDATE);

			} else if (object instanceof PackageTypeVO) {
				sql = getSQLCmd_InsUpd2PackageTypeTbl((PackageTypeVO) object,
						DBConstants.DBOPR_UPDATE);

			} else {
				System.out.println("Error: The object is not supported!");
				// LOG HERE
				result.returnCode(JResult.DBOPR_ERROR_OBJECT_NOT_SUPPORTED);
				return result;
			}
			if (conn == null){
				throw new SQLException("Connection has closed.");
			}
			// Execute SQL statement
			if (conn.execUpdate(sql) > 0) {
				result.returnCode(JResult.DBOPR_SUCCESS);
				return result;
			}
			result.setMessage("The object's value id maybe not existed.");
		} catch (SQLException e) {
			result.setMessage(e.getMessage());
		} catch (Exception e) {
			result.setMessage(e.getMessage());
		}
		LogWriter.getDBLogger().error(result.getMessage());
		result.returnCode(JResult.DBOPR_ERROR_UPDATE_FAILED);
		return result;
	}

	/* LOAD