db.java

Java写的ERP系统

			pstmt.setInt(1, C_AcctSchema_ID);
			rs = pstmt.executeQuery();
			while (rs.next())
				Env.setContext(ctx, "$Element_" + rs.getString("ElementType"), "Y");
			rs.close();
			pstmt.close();

			//	This reads all relevant window neutral defaults
			//	overwriting superseeded ones.  Window specific is read in Mainain
			sql = "SELECT Attribute, Value, AD_Window_ID "
				+ "FROM AD_Preference "
				+ "WHERE AD_Client_ID IN (0, @#AD_Client_ID@)"
				+ " AND AD_Org_ID IN (0, @#AD_Org_ID@)"
				+ " AND (AD_User_ID IS NULL OR AD_User_ID=0 OR AD_User_ID=@#AD_User_ID@)"
				+ " AND IsActive='Y' "
				+ "ORDER BY Attribute, AD_Client_ID, AD_User_ID DESC, AD_Org_ID";
				//	the last one overwrites - System - Client - User - Org - Window
			sql = Env.parseContext(ctx, 0, sql, false);
			if (sql.length() == 0)
				s_log.error("loadPreferences - Missing Environment");
			else
			{
				pstmt = prepareStatement(sql);
				rs = pstmt.executeQuery();
				while (rs.next())
				{
					int AD_Window_ID = rs.getInt(3);
					String at = "";
					if (rs.wasNull())
						at = "P|" + rs.getString(1);
					else
						at = "P" + AD_Window_ID + "|" + rs.getString(1);
					String va = rs.getString(2);
					Env.setContext(ctx, at, va);
				}
				rs.close();
				pstmt.close();
			}
		}
		catch (SQLException ex)
		{
			s_log.error("loadPreferences (" + sql + ")", ex);
		}

		//	Default Values
		s_log.info("Default Values ...");
		sql = "SELECT t.TableName, c.ColumnName "
			+ "FROM AD_Column c "
			+ " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) "
			+ "WHERE c.IsKey='Y' AND t.IsActive='Y'"
			+ " AND EXISTS (SELECT * FROM AD_Column cc "
			+ " WHERE ColumnName = 'IsDefault' AND t.AD_Table_ID=cc.AD_Table_ID AND cc.IsActive='Y')";
		try
		{
			PreparedStatement pstmt = prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
				loadDefault (ctx, rs.getString(1), rs.getString(2));
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("loadPreferences", e);
		}
		//
		Ini.saveProperties(Ini.isClient());
		//
		return retValue;
	}	//	loadPreferences

	/**
	 *	Load Default Value for Table into Context.
	 *
	 *  @param ctx context
	 *  @param TableName table name
	 *  @param ColumnName column name
	 */
	private static void loadDefault (Properties ctx, String TableName, String ColumnName)
	{
		String value = null;
		//
		String SQL = "SELECT " + ColumnName + " FROM " + TableName
			+ " WHERE IsDefault='Y' AND IsActive='Y' ORDER BY AD_Client_ID";
		SQL = Access.addROAccessSQL(ctx, SQL, TableName, false);
		try
		{
			PreparedStatement pstmt = prepareStatement(SQL);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())		//	overwrites system defaults
				value = rs.getString(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("loadDefault - " + TableName + " (" + SQL + ")", e);
			return;
		}
		//	Set Context Value
		if (value != null && value.length() != 0)
		{
			if (TableName.equals("C_DocType"))
				Env.setContext(ctx, "#C_DocTypeTarget_ID", value);
			else
				Env.setContext(ctx, "#" + ColumnName, value);
		}
	}	//	loadDefault

	/**
	 *  Load Warehouses
	 *
	 * @param ctx context
	 * @param client client
	 * @return Array of Warehouse Info
	 */
	public static KeyNamePair[] loadWarehouses(Properties ctx, KeyNamePair client)
	{
		if (ctx == null || client == null)
			throw new IllegalArgumentException("DB.loadWarehouses - required parameter missing");

		s_log.info("loadWarehouses - Client=" + client.toString());

		ArrayList list = new ArrayList();
		try
		{
			String sql = "SELECT M_Warehouse_ID, Name FROM M_Warehouse "
				+ "WHERE AD_Client_ID=? AND IsActive='Y'";  //  #1

			PreparedStatement pstmt = prepareStatement(sql);
			pstmt.setInt(1, client.getKey());
			ResultSet rs = pstmt.executeQuery();

			if (!rs.next())
			{
				rs.close();
				pstmt.close();
				s_log.warn("loadWarehouses - No Warehouses for Client=" + client.getKey());
				return null;
			}

			//  load Warehousess
			do
			{
				int AD_Warehouse_ID = rs.getInt(1);
				String Name = rs.getString(2);
				KeyNamePair p = new KeyNamePair(AD_Warehouse_ID, Name);
				list.add(p);
			}
			while (rs.next());

			rs.close();
			pstmt.close();
		}
		catch (SQLException ex)
		{
			s_log.error("loadWarehouses", ex);
			return null;
		}
		//
		KeyNamePair[] retValue = new KeyNamePair[list.size()];
		list.toArray(retValue);
		s_log.info("# warehouses = " + retValue.length);
		return retValue;
	}   //  loadWarehouses

	/*************************************************************************/

	/**
	 *  Set connection
	 *  @param cc connection
	 */
	public static void setDBTarget (CConnection cc)
	{
		if (cc == null)
			throw new IllegalArgumentException("DB.setDBTarget connection is NULL");

		if (s_cc == null)
			s_cc = cc;
		synchronized (s_cc)    //  use as mutex
		{
			s_cc = cc;
			//  Closing existing
			if (s_connections != null)
			{
				for (int i = 0; i < s_connections.length; i++)
				{
					try {
						s_connections[i].close();
					} catch (Exception e) {}
				}
			}
			s_connections = null;
		}
	}   //  setDBTarget

	/**
	 *  Is there a connection to the database ?
	 *  @return true, if connected to database
	 */
	public static boolean isConnected()
	{
		try
		{
			getConnectionRW();	//	try to get a connection
			return true;
		}
		catch (Exception e)
		{
		}
		return false;
	}   //  isConnected

	/**
	 *	Return (pooled) r/w AutoCommit, read committed connection
	 *
	 *  @return Connection (r/w)
	 */
	public static Connection getConnectionRW ()
	{
		//	check health of connection
		try
		{
			if (s_connectionRW == null)
				;
			else if (s_connectionRW.isClosed())
				s_connectionRW = null;
			else if (s_connectionRW instanceof OracleConnection && ((OracleConnection)s_connectionRW).pingDatabase(1) < 0)
				s_connectionRW = null;
			else
				 s_connectionRW.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		}
		catch (Exception e)
		{
			s_connectionRW = null;
		}
		if (s_connectionRW == null)
			s_connectionRW = s_cc.getConnection (true, Connection.TRANSACTION_READ_COMMITTED);
		if (s_connectionRW == null)
			throw new UnsupportedOperationException("DB.getConnectionRW - @NoDBConnection@");
		return s_connectionRW;
	}   //  getConnectionRW

	/**
	 *	Return read committed, read/only connection with AutoCommit from pool
	 *  @return Connection (r/o)
	 */
	private static Connection getConnectionRO ()
	{
		try
		{
			synchronized (s_cc)    //  use as mutex as s_connection is null the first time
			{
				if (s_connections == null)
					s_connections = createConnections (Connection.TRANSACTION_READ_COMMITTED);     //  see below
			}
		}
		catch (Exception e)
		{
			s_log.error("getConnectionRO", e);
		}

		//  check health of connection
		int pos = s_conCount++;
		Connection connection = s_connections[pos % s_conCacheSize];
		try
		{
			if (connection == null)
				;
			else if (connection.isClosed())
				connection = null;
			else if (connection instanceof OracleConnection && ((OracleConnection)connection).pingDatabase(1) < 0)
				connection = null;
			else
				 connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		}
		catch (Exception e)
		{
			connection = null;
		}
		//	Get new
		if (connection == null)
		{
			System.out.println("DB.getConnectionRO - replacing connection #" + pos % s_conCacheSize);
		//	s_log.error("getConnectionRO - replacing connection #" + pos % s_conCacheSize);
			connection = s_cc.getConnection (true, Connection.TRANSACTION_READ_COMMITTED);            //  see above
		/*	try
			{
				retValue.setReadOnly(true);     //  not supported by Oracle
			}
			catch (Exception e)
			{
				System.err.println("DB.getConnectionRO - Cannot set to R/O - " + e);
			}   */
			s_connections[pos % s_conCacheSize] = connection;
		}
		if (connection == null)
			throw new UnsupportedOperationException("DB.getConnectionRO - @NoDBConnection@");
		return connection;
	}	//	getConnectionRO

	/**
	 *	Create new Connection.
	 *  The connection must be closed explicitly by the application
	 *
	 *  @param autoCommit auto commit
	 *  @param trxLevel - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_SERIALIZABLE.
	 *  @return Connection connection
	 */
	public static Connection createConnection (boolean autoCommit, int trxLevel)
	{
		s_log.debug("createConnection " + s_cc.getConnectionURL()
			+ ", UserID=" + s_cc.getDbUid() + ", AutoCommit=" + autoCommit + ", TrxLevel=" + trxLevel);
		return s_cc.getConnection (autoCommit, trxLevel);
	}	//	createConnection

	/**
	 *	Create new set of r/o Connections.
	 *  R/O connection might not be supported by DB
	 *
	 *  @param trxLevel - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_SERIALIZABLE.
	 *  @return Array of Connections (size based on s_conCacheSize)
	 */
	private static Connection[] createConnections (int trxLevel)
	{
		s_log.debug("createConnections (" + s_conCacheSize + ") " + s_cc.getConnectionURL()
			+ ", UserID=" + s_cc.getDbUid() + ", TrxLevel=" + trxLevel);
		Connection cons[] = new Connection[s_conCacheSize];
		try
		{
			for (int i = 0; i < s_conCacheSize; i++)
			{
				cons[i] = s_cc.getConnection (true, trxLevel);  //  auto commit
				if (cons[i] == null)
					System.err.println("createConnections - connection is NULL");	//	don't use log
			}
		}
		catch (Exception e)
		{
			//  Don't use Log
			System.err.println("DB.createConnections - " + e.getMessage());
		}
		return cons;
	}	//	createConnections

	/**
	 *  Get Database Driver.
	 *  Access to database specific functionality.
	 *  @return Compiere Database Driver
	 */
	public static CompiereDatabase getDatabase()
	{
		if (s_cc != null)
			return s_cc.getDatabase();
		return null;
	}   //  getDatabase

	/*************************************************************************/

	/**
	 *  Check database Version with Code version
	 *  @param ctx context
	 *  @return true if Database version (date) is the same
	 */
	public static boolean isDatabaseOK (Properties ctx)
	{
		//	Force loading Messages of current language
		Msg.getMsg(ctx, "0");

		//  Check Version
		String version = "?";
		String sql = "SELECT Version FROM AD_System";
		try
		{
			PreparedStatement pstmt = prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next())
				version = rs.getString(1);
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("Problem with AD_System Table - Run system.sql script - " + e.toString());
		}
		s_log.info("isDatabaseOK - DB_Version=" + version);
		//  identical version
		if (Compiere.DB_VERSION.equals(version))
			return true;

		String AD_Message = "DatabaseVersionError";
		String title = org.compiere.Compiere.getName() + " " +  Msg.getMsg(ctx, AD_Message, true);
		//	Code assumes Database version {0}, but Database has Version {1}.
		String msg = Msg.getMsg(ctx, AD_Message);	//	complete message
		msg = MessageFormat.format(msg, new Object[] {Compiere.DB_VERSION, version});
		Object[] options = { UIManager.get("OptionPane.noButtonText"), "Migrate" };
		int no = JOptionPane.showOptionDialog (null, msg,
			title, JOptionPane.DEFAULT_OPTION, JOptionPane.ERROR_MESSAGE,
			UIManager.getIcon("OptionPane.errorIcon"), options, options[0]);
		if (no == 1)
		{
			try
			{
				Class.forName("com.compiere.client.StartMaintain").newInstance();
			}
			catch (Exception ex)
			{
				JOptionPane.showMessageDialog (null,
					ex.getMessage() + "\nSee: http://www.compiere.com/maintain",
					title, JOptionPane.ERROR_MESSAGE);
				s_log.error("isDatabaseOK - " + ex.getMessage());
			}
		}
		return false;
	}   //  isDatabaseOK

	/*************************************************************************/

	/**
	 *	Security Context Login.
	 *  @param uid uid
	 *  @param pwd pwd
	 *  @param role role
	 *  @return true if connected
	 */
	public static boolean login_context (String uid, String pwd, String role)
	{
		if (uid == null || pwd == null || role == null || uid.length() == 0 || pwd.length() == 0 || role.length() == 0)
			throw new IllegalArgumentException("DB.login_context - required parameter missing");
		s_log.info("login_context uid=" + uid);
		if (uid == null || pwd == null || role == null)
			return false;
		//
		String SQL = "{CALL Compiere_Context.Login(?,?,?,?)}";
		boolean result = true;
		try
		{
			CallableStatement cstmt = getConnectionRO().prepareCall(SQL);
			cstmt.setString(1, uid);
			cstmt.setString(2, pwd);
			cstmt.setString(3, role);
			cstmt.setString(4, Language.getBaseAD_Language());
			result = cstmt.execute();
			cstmt.close();
		}
		catch (SQLException e)
		{
			s_log.error("login_context", e);
			result = false;
		}
		return result;
	}	//	login_context

	/*************************************************************************/

	/**
	 *	Close Target
	 */
	public static void closeTarget()
	{
		if (s_connections != null)
		{
			for (int i = 0; i < s_conCacheSize; i++)
			{
				try
				{
					if (s_connections[i] != null)
						s_connections[i].close();
				}
				catch (SQLException e)
				{
					s_log.error("close connection #" + i, e);
				}
				s_connections[i] = null;
			}
			try
			{
				if (s_connectionRW != null)
					s_connectionRW.close();
			}
			catch (SQLException e)
			{
				s_log.error("close R/W connection", e);
			}
			s_connectionRW = null;
		}
		s_connections = null;
//		EJB.close();
	}	//	closeTarget

	/*************************************************************************/

	/**
	 *	Prepare Forward Read Only Call
	 *  @param RO_SQL sql (RO)
	 *  @return Callable Statement
	 */
	public static CallableStatement prepareCall(String RO_SQL)
	{
		if (RO_SQL == null || RO_SQL.length() == 0)
			throw new IllegalArgumentException("DB.prepareCall - required parameter missing - " + RO_SQL);
		//
		String sql = getDatabase().convertStatement(RO_SQL);
		try
		{
			return getConnectionRO().prepareCall
				(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
		}