db.java

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

	public static int executeUpdateEx (String SQL, String trxName) throws SQLException
	{
		if (SQL == null || SQL.length() == 0)
			throw new IllegalArgumentException("Required parameter missing - " + SQL);
		//
		String sql = getDatabase().convertStatement(SQL);
		int no = -1;
		SQLException ex = null;
		Connection conn = null;
		Statement stmt = null;
		try
		{
			Trx trx = trxName == null ? null : Trx.get(trxName, true);
			if (trx != null)
				conn = trx.getConnection();
			else
				conn = DB.getConnectionRW ();
			stmt = conn.createStatement();
			no = stmt.executeUpdate(sql);
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql + " [" + trxName + "]", e);
			ex = e;
		}
		finally
		{
			//  Always close cursor
			try
			{
				stmt.close();
			}
			catch (SQLException e2)
			{
				log.log(Level.SEVERE, "Cannot close statement");
			}
		}
		if (ex != null)
			throw new SQLException(ex.getMessage(), ex.getSQLState(), ex.getErrorCode());
		return no;
	}	//	execute Update

	/**
	 *	Commit - commit on RW connection.
	 *  Is not required as RW connection is AutoCommit (exception: with transaction)
	 *  @param throwException if true, re-throws exception
	 * 	@param trxName transaction name
	 *  @return true if not needed or success
	 *  @throws SQLException
	 */
	public static boolean commit (boolean throwException, String trxName) throws SQLException
	{
		try
		{
			Connection conn = null;
			Trx trx = trxName == null ? null : Trx.get(trxName, true);
			if (trx != null)
				conn = trx.getConnection();
			else
				conn = DB.getConnectionRW ();
		//	if (!conn.getAutoCommit())
			conn.commit();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "[" + trxName + "]", e);
			if (throwException)
				throw e;
			return false;
		}
		return true;
	}	//	commit

	/**
	 *	Rollback - rollback on RW connection.
	 *  Is has no effect as RW connection is AutoCommit (exception: with transaction)
	 *  @param throwException if true, re-throws exception
	 * 	@param trxName transaction name
	 *  @return true if not needed or success
	 *  @throws SQLException
	 */
	public static boolean rollback (boolean throwException, String trxName) throws SQLException
	{
		try
		{
			Connection conn = null;
			Trx trx = trxName == null ? null : Trx.get(trxName, true);
			if (trx != null)
				conn = trx.getConnection();
			else
				conn = DB.getConnectionRW ();
		//	if (!conn.getAutoCommit())
			conn.rollback();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "[" + trxName + "]", e);
			if (throwException)
				throw e;
			return false;
		}
		return true;
	}	//	commit

	/**
	 * 	Get Row Set.
	 * 	When a Rowset is closed, it also closes the underlying connection.
	 * 	If the created RowSet is transfered by RMI, closing it makes no difference 
	 *	@param sql sql
	 *	@param local local RowSet (own connection)
	 *	@return row set or null
	 */
	public static RowSet getRowSet (String sql, boolean local)
	{
		RowSet retValue = null;
		CStatementVO info = new CStatementVO ( 
			RowSet.TYPE_SCROLL_INSENSITIVE, RowSet.CONCUR_READ_ONLY, sql);
		CPreparedStatement stmt = new CPreparedStatement(info);
		if (local)
		{
			retValue = stmt.local_getRowSet();
		}
		else
		{
			retValue = stmt.remote_getRowSet();
		}
		return retValue;
	}	//	getRowSet

	/**
	 * 	Get Value from sql
	 * 	@param trxName trx
	 * 	@param sql sql
	 * 	@return first value or -1
	 */
	public static int getSQLValue (String trxName, String sql)
	{
		int retValue = -1;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = prepareStatement(sql, trxName);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getInt(1);
			else
				log.fine("No Value " + sql);
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql, e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		return retValue;
	}	//	getSQLValue

	/**
	 * 	Get Value from sql
	 * 	@param trxName trx
	 * 	@param sql sql
	 * 	@param int_param1 parameter 1
	 * 	@return first value or -1
	 */
	public static int getSQLValue (String trxName, String sql, int int_param1)
	{
		int retValue = -1;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = prepareStatement(sql, trxName);
			pstmt.setInt(1, int_param1);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getInt(1);
			else
				log.config("No Value " + sql + " - Param1=" + int_param1);
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql + " - Param1=" + int_param1 + " [" + trxName + "]", e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		return retValue;
	}	//	getSQLValue

	/**
	 * 	Get Value from sql
	 * 	@param trxName trx
	 * 	@param sql sql
	 * 	@param int_param1 parameter 1
	 * 	@param int_param2 parameter 2
	 * 	@return first value or -1
	 */
	public static int getSQLValue (String trxName, String sql, int int_param1, int int_param2)
	{
		int retValue = -1;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = prepareStatement(sql, trxName);
			pstmt.setInt(1, int_param1);
			pstmt.setInt(2, int_param2);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getInt(1);
			else
				log.info("No Value " + sql 
					+ " - Param1=" + int_param1 + ",Param2=" + int_param2);
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql + " - Param1=" + int_param1 + ",Param2=" + int_param2 
				+ " [" + trxName + "]", e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		return retValue;
	}	//	getSQLValue

	/**
	 * 	Get Value from sql
	 * 	@param trxName trx
	 * 	@param sql sql
	 * 	@param str_param1 parameter 1
	 * 	@return first value or -1
	 */
	public static int getSQLValue (String trxName, String sql, String str_param1)
	{
		int retValue = -1;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = prepareStatement(sql, trxName);
			pstmt.setString(1, str_param1);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getInt(1);
			else
				log.info("No Value " + sql + " - Param1=" + str_param1);
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql + " - Param1=" + str_param1, e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		return retValue;
	}	//	getSQLValue

	/**
	 * 	Get Value from sql
	 * 	@param trxName trx
	 * 	@param sql sql
	 * 	@param int_param1 parameter 1
	 * 	@param s_param2 parameter 2
	 * 	@return first value or -1
	 */
	public static int getSQLValue (String trxName, String sql, int int_param1, String s_param2)
	{
		int retValue = -1;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = prepareStatement(sql, trxName);
			pstmt.setInt(1, int_param1);
			pstmt.setString(2, s_param2);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getInt(1);
			else
				log.info("No Value: " + sql + " - Param1=" + int_param1 + ",Param2=" + s_param2);
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql + " - Param1=" + int_param1 + ",Param2=" + s_param2, e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		return retValue;
	}	//	getSQLValue

	/**
	 * 	Get String Value from sql
	 * 	@param trxName trx
	 * 	@param sql sql
	 * 	@param int_param1 parameter 1
	 * 	@return first value or null
	 */
	public static String getSQLValueString (String trxName, String sql, int int_param1)
	{
		String retValue = null;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = prepareStatement(sql, trxName);
			pstmt.setInt(1, int_param1);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getString(1);
			else
				log.info("No Value " + sql + " - Param1=" + int_param1);
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql + " - Param1=" + int_param1, e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		return retValue;
	}	//	getSQLValueString

	/**
	 * 	Get BigDecimal Value from sql
	 * 	@param trxName trx
	 * 	@param sql sql
	 * 	@param int_param1 parameter 1
	 * 	@return first value or null
	 */
	public static BigDecimal getSQLValueBD (String trxName, String sql, int int_param1)
	{
		BigDecimal retValue = null;
		PreparedStatement pstmt = null;
		try
		{
			pstmt = prepareStatement(sql, trxName);
			pstmt.setInt(1, int_param1);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				retValue = rs.getBigDecimal(1);
			else
				log.info("No Value " + sql + " - Param1=" + int_param1);
			rs.close();
			pstmt.close();
			pstmt = null;
		}
		catch (Exception e)
		{
			log.log(Level.SEVERE, sql + " - Param1=" + int_param1 + " [" + trxName + "]", e);
		}
		finally
		{
			try
			{
				if (pstmt != null)
					pstmt.close ();
			}
			catch (Exception e)
			{}
			pstmt = null;
		}
		return retValue;
	}	//	getSQLValueBD

	
	/**
	 * 	Get Array of Key Name Pairs
	 *	@param sql select with id / name as first / second column
	 *	@param optional if true (-1,"") is added 
	 *	@return array of key name pairs
	 */
	public static KeyNamePair[] getKeyNamePairs(String sql, boolean optional)
	{
		PreparedStatement pstmt = null;
		ArrayList<KeyNamePair> list = new ArrayList<KeyNamePair>();
		if (optional)
			list.add (new KeyNamePair(-1, ""));
		try
		{
			pstmt = DB.prepareStatement(sql, null);
			ResultSet rs = pstmt.executeQuery();