dbfunctionsmanager.java

this is example use EJB with jboss.

			szQuery = genUpdateCmd(PortVO.PORT_TBL, PortVO.PORT_ID + " = "
									+ pvo.getID());
		}
		return szQuery;
	}

	/**
	 * Generate a insert/update SQL statement for UserVO
	 * 
	 * @param uvo
	 *            UserVO object
	 * @param op
	 *            equal DBConstants.DBOPR_INSERT if operation for "insert"
	 *            otherwise for "update" (specify is DBConstants.DBOPR_UPDATE).
	 * 
	 * @return insert/update SQL statement for UserVO object
	 */
	private String getSQLCmd_InsUpd2UserTbl(UserVO uvo, int op) {
		String szQuery = null;
		clearFieldStorer();

		if (op == DBConstants.DBOPR_INSERT) {
			putfval(UserVO.USER_NAME, uvo.getName());
			putfval(UserVO.USER_TYPE, uvo.getRole());
			putfval(UserVO.USER_PASS, uvo.getPassword());
			szQuery = genInsertCmd(UserVO.USER_TBL);
		} else {
			putfval(UserVO.USER_TYPE, uvo.getRole());
			String pass = uvo.getPassword();
			if (pass != null) {
				pass = pass.trim();
				if (pass.length() > 0) {
					putfval(UserVO.USER_PASS, uvo.getPassword());
				}
			}
			szQuery = genUpdateCmd(UserVO.USER_TBL, UserVO.USER_ID + " = "
									+ uvo.getID());
		}
		return szQuery;
	}

	/**
	 * Generate a insert/update SQL statement for PackageConnection
	 * 
	 * @param pconn
	 *            PackageConnection object
	 * @param op
	 *            equal DBConstants.DBOPR_INSERT if operation for "insert"
	 *            otherwise for "update" (specify is DBConstants.DBOPR_UPDATE).
	 * 
	 * @return insert/update SQL statement for PackageConnection object
	 */
	private String getSQLCmd_InsUpd2PackageConnectionTbl(
								PackageConnection pconn, int op) {
		String szQuery = null;
		clearFieldStorer();
		putfval(PackageConnection.PACKAGE_CONNECT_ID, pconn.getID());
		putfval(PackageConnection.PACKAGE_ID_1, pconn.getPackageID1());
		putfval(PackageConnection.PACKAGE_ID_2, pconn.getPackageID2());
		putfval(PackageConnection.PACKAGE_CONNECT_COLOR, pconn.getColor());
		putfval(PackageConnection.PACKAGE_CONNECT_DIR, pconn.getDirection());
		if (op == DBConstants.DBOPR_INSERT) {
			szQuery = genInsertCmd(PackageConnection.PACKAGE_CONNECT_TBL);
		} else {
			szQuery = genUpdateCmd(PackageConnection.PACKAGE_CONNECT_TBL,
						PackageConnection.PACKAGE_CONNECT_ID + " = " + pconn.getID());
		}
		return szQuery;
	}

	/**
	 * Generate a insert/update SQL statement for PPConnection
	 * 
	 * @param ppConn
	 *            PPConnection object
	 * @param op
	 *            equal DBConstants.DBOPR_INSERT if operation for "insert"
	 *            otherwise for "update" (specify is DBConstants.DBOPR_UPDATE).
	 * 
	 * @return insert/update SQL statement for PPConnection object
	 */
	private String getSQLCmd_InsUpd2PPConnectionTbl(PPConnection ppConn, int op) {
		String szQuery = null;
		clearFieldStorer();
		putfval(PPConnection.PACKAGE_ID1, ppConn.getPackage1());
		putfval(PPConnection.PACKAGE_ID2, ppConn.getPackage2());
		putfval(PPConnection.PORT_ID1, ppConn.getPortID1());
		putfval(PPConnection.PORT_ID2, ppConn.getPortID2());
		putfval(PPConnection.CONNECT_DIR, ppConn.getDirection());

		if (op == DBConstants.DBOPR_INSERT) {
			szQuery = genInsertCmd(PPConnection.CONNECT_TBL);
		} else {
			szQuery = genUpdateCmd(PPConnection.CONNECT_TBL,
									PPConnection.CONNECT_ID + " = " + ppConn.getID());
		}
		return szQuery;
	}

	/**
	 * Generate a insert/update SQL statement for PackageTypeVO
	 * 
	 * @param ptvo
	 *            PackageTypeVO object
	 * @param op
	 *            equal DBConstants.DBOPR_INSERT if operation for "insert"
	 *            otherwise for "update" (specify is DBConstants.DBOPR_UPDATE).
	 * 
	 * @return insert/update SQL statement for PackageTypeVO object
	 */
	private String getSQLCmd_InsUpd2PackageTypeTbl(PackageTypeVO ptvo, int op) {
		String szQuery = null;
		clearFieldStorer();
		putfval(PackageTypeVO.PACKAGETYPE_NAME, ptvo.getName());
		putfval(PackageTypeVO.PACKAGETYPE_PORT, ptvo.getDescPorts());
		if (op == DBConstants.DBOPR_INSERT) {
			szQuery = genInsertCmd(PackageTypeVO.PACKAGETYPE_TBL);
		} else {
			szQuery = genUpdateCmd(PackageTypeVO.PACKAGETYPE_TBL,
									PackageTypeVO.PACKAGETYPE_ID + " = " + ptvo.getID());
		}
		return szQuery;
	}

	/**
	 * Generate a insert SQL statement for OperationVO
	 * 
	 * @param logvo
	 *            OperationVO object
	 * 
	 * @return insert SQL statement for OperationVO object
	 */
	private String getSQLCmd_Ins2LogTbl(OperationVO logvo) {
		String szQuery = null;
		clearFieldStorer();
		putfval(UserVO.USER_NAME, logvo.getUserName());
		putfval(OperationVO.OPERATION_TIME, logvo.getTime());
		putfval(OperationVO.OPERATION_CONTENT, logvo.getContent());
		szQuery = genInsertCmd(OperationVO.OPERATION_TBL);
		return szQuery;
	}

	/**
	 * Generate a insert SQL statement for TrapVO
	 * 
	 * @param tvo
	 *            TrapVO object
	 * 
	 * @return insert SQL statement for TrapVO object
	 */
	private String getSQLCmd_Ins2TrapTbl(TrapVO tvo) {
		String szQuery = null;
		clearFieldStorer();

		putfval(NodeVO.NODE_NAME, tvo.getNodeName());
		putfval(BlockVO.BLOCK_NAME, tvo.getBlockName());
		putfval(BlockVO.BLOCK_IP, tvo.getBlockIP());
		putfval(PackageTypeVO.PACKAGETYPE_NAME, tvo.getPackageTypeName());
		putfval(TrapVO.TRAP_SLOTNUM, tvo.getSlotNumber());
		putfval(TrapVO.TRAP_TYPE, tvo.getTrapType());
		putfval(TrapVO.TRAP_LEVEL, tvo.getTrapLevel());
		putfval(TrapVO.TRAP_TIME, tvo.getTime());
		putfval(TrapVO.MAIN_STATUS, tvo.getTrapStatusOnMainView());
		putfval(TrapVO.IMPL_STATUS, tvo.getTrapStatusOnImplView());
		putfval(TrapVO.TRAP_OID, tvo.getTrapOID());

		szQuery = genInsertCmd(TrapVO.TRAP_TBL);
		return szQuery;
	}

	/**
	 * Check the package in database that existed or not
	 * 
	 * @param object
	 *            PPConnection object
	 * @return a JResult object. Check method isSucceeded() true if existed.
	 */
	private JResult checkPPConnection(PPConnection object) {
		JResult result = new JResult();
		long pk_id_1 = object.getPackage1();
		long pk_id_2 = object.getPackage2();
		long port_id_1 = object.getPortID1();
		long port_id_2 = object.getPortID2();

		if ((findObject(DBConstants.PACKAGE_OBJECT, pk_id_1) <= 0)
				|| (findObject(DBConstants.PACKAGE_OBJECT, pk_id_2) <= 0)) {
			// ERROR_OBJECT_NOT_FOUND
			result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
			result.setMessage("The package object with ID specified " + "("
						+ pk_id_1 + " or " + pk_id_2 + ") was not found.");
			return result;
		}
		if ((findObject(DBConstants.PORT_OBJECT, port_id_1) <= 0)
				|| (findObject(DBConstants.PORT_OBJECT, port_id_2) <= 0)) {
			// ERROR_OBJECT_NOT_FOUND
			result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
			result.setMessage("The port object with ID specified " + "("
						+ port_id_1 + " or " + port_id_2 + ") was not found.");
			return result;
		}
		result.returnCode(JResult.DBOPR_SUCCESS);
		return result;
	}

	/**
	 * Check the package and port in PackageConnection object from database that
	 * existed or not
	 * 
	 * @param object
	 *            PackageConnection object
	 * @return a JResult object. Check method isSucceeded() true if existed.
	 */
	private JResult checkPackagePConnection(PackageConnection object) {
		JResult result = new JResult();
		long pk_id_1;
		long pk_id_2;
		pk_id_1 = object.getPackageID1();
		pk_id_2 = object.getPackageID2();
		if (findObject(DBConstants.PACKAGE_OBJECT, pk_id_1) <= 0) {
			// ERROR_OBJECT_NOT_FOUND
			result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
			result.setMessage("The package object with ID is " + pk_id_1
						+ " was not found.");
			return result;
		}
		if (findObject(DBConstants.PACKAGE_OBJECT, pk_id_2) <= 0) {
			// ERROR_OBJECT_NOT_FOUND
			result.returnCode(JResult.DBOPR_ERROR_PARENT_OBJECT_NOT_FOUND);
			result.setMessage("The package object with ID is " + pk_id_2
						+ " was not found.");
			return result;
		}
		result.returnCode(JResult.DBOPR_SUCCESS);
		return result;
	}

	/**
	 * add new one package into database
	 * 
	 * @param object
	 * @return a JResult object contains a PackageVO (consists of list ports),
	 *         use method getObjectData() of JResult to get this.
	 * @throws Exception
	 *             throws a exception if an error occurs.
	 */
	public JResult addPackage2DB(PackageVO object) throws Exception {
		
		JResult result = null;
		long packageID = -1;
		try {
			assertObjectInfo(object, this.userName + " is adding");
			if (conn == null){
				throw new SQLException("Connection has closed.");
			}
			// Disable auto commit, so we want to make sure that overall data
			// (package and ports) are inserted successful.
			conn.setAutoCommit(false);

			PackageVO pkgvo = object;
			result = addObject2DBBuffer(pkgvo);
			if (result.isFailed()) {
				throw new SQLException(result.getMessage());
			}
			// Get ID of package have just inserted
			packageID = result.getValue();
			String packageName = pkgvo.getName();
			// Get package type object from package name
			ArrayList<PackageTypeVO> lstPackageType = getPackageTypes("packagetype_name LIKE '"
														+ packageName + "%'");
			if ((lstPackageType != null) && (!lstPackageType.isEmpty())) {
				// From package name, we want to get list of port name that
				// corresponding
				String lstNamePorts = lstPackageType.get(0).getDescPorts();
				lstNamePorts = lstNamePorts.trim();
				// Get each port name from list
				String[] namePort = lstNamePorts.split(",");
				// For each port name from list, we will create one new port
				// object, then insert them to database
				for (int i = 0; i < namePort.length; i++) {
					namePort[i] = namePort[i].trim();
					if ("".equalsIgnoreCase(namePort[i])) {
						//throw new SQLException(
						//		"The list of port name is invalid. Check pakagetype_name table.");
					} else {
						PortVO pvo = new PortVO();
						pvo.setName(namePort[i]);
						pvo.setParentID(packageID);
						// insert ports
						result = addObject2DB(pvo);
						if (result.isFailed()) {
							throw new SQLException(result.getMessage());
						}
						pvo.setID(result.getValue());
						pkgvo.getListPortVO().add(pvo);
					}
				}
			} else {
				throw new Exception("An error occurred during loads package type."
								  + " Check for make sure that the package name existed.");
			}
			// If no any errors occurred, so commit
			conn.commit();
			pkgvo.setID(packageID);
			result.setValue(packageID);
			result.setObjectData(pkgvo);
			LogWriter.getDBLogger().info("Added complete.");
			result.returnCode(JResult.DBOPR_SUCCESS);
			return result;

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

	}

	/**
	 * add a list of package-package connections into database
	 * 
	 * @param lstLogicalConn
	 *            list of package-package connections
	 * @return a JResult object. Check method isSucceeded() true if success.
	 * @throws Exception
	 * 			throws a exception if an error occurs.
	 */
	public JResult addLogicalConnection2DB(
			ArrayList<PackageConnection> lstLogicalConn) throws Exception {

		JResult result = null;
		String sql = null;
		try {
			LogWriter.getDBLogger().info(this.userName + " is adding list of " +
													"package-package connection...");
			result = new JResult();
			if (conn == null){
				throw new Exception("Connection has closed.");
			}
			// Disable auto commit, so we want to make sure that overall data
			// are inserted successful.
			conn.setAutoCommit(false);
			// Delete overall connection data old
			sql = "DELETE FROM " + getTableName(DBConstants.PACKAGE_PACKAGE_OBJECT);
			conn.execUpdate(sql);
			// Insert new from list
			for (int i = 0; i < lstLogicalConn.size(); i++) {
				result = addObject2DBBuffer(lstLogicalConn.get(i));
				if (result.isFailed()) {
					throw new SQLException(result.getMessage());
				}
			}
			// If no any error occurred, so commit
			conn.commit();
			LogWriter.getDBLogger().info("Added 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_INSERT_FAILED);
		return result;
	}

	/**
	 * add an object into 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.
	 * @throws Exception if error occurs
	 */
	public JResult addObject2DB(Object object) throws Exception {
		JResult result = new JResult();
		try {
			assertObjectInfo(object, this.userName + " is adding");