📄 db_oracle.java
字号:
/******************************************************************************
* The contents of this file are subject to the Compiere License Version 1.1
* ("License"); You may not use this file except in compliance with the License
* You may obtain a copy of the License at http://www.compiere.org/license.html
* Software distributed under the License is distributed on an "AS IS" basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
* the specific language governing rights and limitations under the License.
* The Original Code is Compiere ERP & CRM Smart Business Solution. The Initial
* Developer of the Original Code is Jorg Janke. Portions created by Jorg Janke
* are Copyright (C) 1999-2005 Jorg Janke.
* All parts are Copyright (C) 1999-2005 ComPiere, Inc. All Rights Reserved.
* Contributor(s): ______________________________________.
*****************************************************************************/
package org.compiere.db;
import java.math.*;
import java.sql.*;
import java.util.*;
import java.util.logging.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import org.compiere.*;
import org.compiere.util.*;
/**
* Oracle Database Port
*
* @author Jorg Janke
* @version $Id: DB_Oracle.java,v 1.46 2006/02/09 01:03:20 jjanke Exp $
*/
public class DB_Oracle implements CompiereDatabase, OracleConnectionCacheCallback
{
/**
* Oracle Database
*/
public DB_Oracle()
{
/** Causes VPN problems ???
try
{
getDriver();
}
catch (Exception e)
{
log.log(Level.SEVERE, e.getMessage());
}
**/
} // DB_Oracle
/** Static Driver */
private static OracleDriver s_driver = null;
/** Driver Class Name */
public static final String DRIVER = "oracle.jdbc.OracleDriver";
/** Default Port */
public static final int DEFAULT_PORT = 1521;
/** Default Connection Manager Port */
public static final int DEFAULT_CM_PORT = 1630;
/** Connection String */
private String m_connectionURL;
/** Statement Cache (50) */
private static final int MAX_STATEMENTS = 50;
/** Data Source */
private OracleDataSource m_ds = null;
/** Use Connection Cache (false)*/
private static final boolean USE_CACHE = false;
/** Connection Cache */
private OracleConnectionCacheManager m_cacheMgr = null;
/** Connection Cache Name */
private static final String CACHE_NAME = "CompiereCCache";
/** Cached User Name */
private String m_userName = null;
/** Logger */
private static CLogger log = CLogger.getCLogger (DB_Oracle.class);
/**
* Get Database Name
* @return database short name
*/
public String getName()
{
return Database.DB_ORACLE;
} // getName
/**
* Get Database Description
* @return database long name and version
*/
public String getDescription()
{
try
{
if (s_driver == null)
getDriver();
}
catch (Exception e)
{
}
if (s_driver != null)
return s_driver.toString();
return "No Driver";
} // getDescription
/**
* Get Standard JDBC Port
* @return standard port
*/
public int getStandardPort()
{
return DEFAULT_PORT;
} // getStandardPort
/**
* Get and register Database Driver
* @return Driver
*/
public Driver getDriver() throws SQLException
{
if (s_driver == null)
{
// Speed up transfer rate
System.setProperty("oracle.jdbc.TcpNoDelay", "true");
// Oracle Multi - Language
System.setProperty("oracle.jdbc.defaultNChar", "true");
//
s_driver = new OracleDriver();
DriverManager.registerDriver (s_driver);
DriverManager.setLoginTimeout (Database.CONNECTION_TIMEOUT);
}
return s_driver;
} // getDriver
/**
* Get Database Connection String.
* <pre>
* Timing:
* - CM with source_route not in address_list = 28.5 sec
* - CM with source_route in address_list = 58.0 sec
* - direct = 4.3-8 sec (no real difference if on other box)
* - bequeath = 3.4-8 sec
* </pre>
* @param connection Connection Descriptor
* @return connection String
*/
public String getConnectionURL (CConnection connection)
{
StringBuffer sb = null;
// Server Connections (bequeath)
if (connection.isBequeath())
{
sb = new StringBuffer ("jdbc:oracle:oci8:@");
// bug: does not work if there is more than one db instance - use Net8
// sb.append(connection.getDbName());
}
else // thin driver
{
sb = new StringBuffer ("jdbc:oracle:thin:@");
// direct connection
if (connection.isViaFirewall())
{
// (description=(address_list=
// ( (source_route=yes)
// (address=(protocol=TCP)(host=cmhost)(port=1630))
// (address=(protocol=TCP)(host=dev)(port=1521))
// (connect_data=(service_name=dev1.compiere.org)))
sb.append("(DESCRIPTION=(ADDRESS_LIST=")
.append("(SOURCE_ROUTE=YES)")
.append("(ADDRESS=(PROTOCOL=TCP)(HOST=").append(connection.getFwHost())
.append(")(PORT=").append(connection.getFwPort()).append("))")
.append("(ADDRESS=(PROTOCOL=TCP)(HOST=").append(connection.getDbHost())
.append(")(PORT=").append(connection.getDbPort()).append(")))")
.append("(CONNECT_DATA=(SERVICE_NAME=").append(connection.getDbName()).append(")))");
}
else
{
// old: dev2:1521:sid
// new: //dev2:1521/serviceName
sb.append("//")
.append(connection.getDbHost())
.append(":").append(connection.getDbPort())
.append("/").append(connection.getDbName());
}
}
m_connectionURL = sb.toString();
// log.config(m_connectionURL);
//
m_userName = connection.getDbUid();
return m_connectionURL;
} // getConnectionURL
/**
* Get Connection URL.
* http://download-east.oracle.com/docs/cd/B14117_01/java.101/b10979/urls.htm#BEIDBFDF
* @param dbHost db Host
* @param dbPort db Port
* @param dbName db Name
* @param userName user name
* @return connection
*/
public String getConnectionURL (String dbHost, int dbPort, String dbName,
String userName)
{
m_userName = userName;
return "jdbc:oracle:thin:@//"
+ dbHost + ":" + dbPort + "/" + dbName;
} // getConnectionURL
/**
* Get JDBC Catalog
* @return null - not used
*/
public String getCatalog()
{
return null;
} // getCatalog
/**
* Get JDBC Schema
* @return user name
*/
public String getSchema()
{
if (m_userName != null)
return m_userName.toUpperCase();
log.severe("User Name not set (yet) - call getConnectionURL first");
return null;
} // getSchema
/**
* Supports BLOB
* @return true if BLOB is supported
*/
public boolean supportsBLOB()
{
return true;
} // supportsBLOB
/**
* String Representation
* @return info
*/
public String toString()
{
StringBuffer sb = new StringBuffer("DB_Oracle[");
sb.append(m_connectionURL);
try
{
if (m_ds != null)
sb.append("-").append(m_ds.getDataSourceName())
// .append(",ExplCache=").append(m_ds.getExplicitCachingEnabled())
.append(",ImplCache=").append(m_ds.getImplicitCachingEnabled())
.append(",MaxStmts=").append(m_ds.getMaxStatements());
// .append(",Ref=").append(m_ds.getReference());
if (m_cacheMgr != null && m_cacheMgr.existsCache(CACHE_NAME))
sb.append(";ConnectionActive=").append(m_cacheMgr.getNumberOfActiveConnections(CACHE_NAME))
.append(",CacheAvailable=").append(m_cacheMgr.getNumberOfAvailableConnections(CACHE_NAME));
}
catch (Exception e)
{
sb.append("=").append(e.getLocalizedMessage());
}
sb.append("]");
return sb.toString();
} // toString
/**
* Get Status
* @return status info
*/
public String getStatus()
{
StringBuffer sb = new StringBuffer();
try
{
if (m_cacheMgr != null && m_cacheMgr.existsCache(CACHE_NAME))
sb.append("-Connections=").append(m_cacheMgr.getNumberOfActiveConnections(CACHE_NAME))
.append(",Cache=").append(m_cacheMgr.getNumberOfAvailableConnections(CACHE_NAME));
}
catch (Exception e)
{}
return sb.toString();
} // getStatus
/**************************************************************************
* Convert an individual Oracle Style statements to target database statement syntax.
* @param oraStatement oracle statement
* @return converted Statement oracle statement
*/
public String convertStatement (String oraStatement)
{
return oraStatement;
} // convertStatement
/**
* Get Name of System User
* @return system
*/
public String getSystemUser()
{
return "system";
} // getSystemUser
/**
* Get Name of System Database
* @param databaseName database Name
* @return e.g. master or database Name
*/
public String getSystemDatabase(String databaseName)
{
return databaseName;
} // getSystemDatabase
/**
* Create SQL TO Date String from Timestamp
*
* @param time Date to be converted
* @param dayOnly true if time set to 00:00:00
*
* @return TO_DATE('2001-01-30 18:10:20',''YYYY-MM-DD HH24:MI:SS')
* or TO_DATE('2001-01-30',''YYYY-MM-DD')
*/
public String TO_DATE (Timestamp time, boolean dayOnly)
{
if (time == null)
{
if (dayOnly)
return "TRUNC(SysDate)";
return "SysDate";
}
StringBuffer dateString = new StringBuffer("TO_DATE('");
// YYYY-MM-DD HH24:MI:SS.mmmm JDBC Timestamp format
String myDate = time.toString();
if (dayOnly)
{
dateString.append(myDate.substring(0,10));
dateString.append("','YYYY-MM-DD')");
}
else
{
dateString.append(myDate.substring(0, myDate.indexOf("."))); // cut off miliseconds
dateString.append("','YYYY-MM-DD HH24:MI:SS')");
}
return dateString.toString();
} // TO_DATE
/**
* Create SQL for formatted Date, Number
*
* @param columnName the column name in the SQL
* @param displayType Display Type
* @param AD_Language 6 character language setting (from Env.LANG_*)
*
* @return TRIM(TO_CHAR(columnName,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.'''))
* or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language
* @see org.compiere.util.DisplayType
* @see org.compiere.util.Env
*
* */
public String TO_CHAR (String columnName, int displayType, String AD_Language)
{
StringBuffer retValue = new StringBuffer("TRIM(TO_CHAR(");
retValue.append(columnName);
// Numbers
if (DisplayType.isNumeric(displayType))
{
if (displayType == DisplayType.Amount)
retValue.append(",'9G999G990D00'");
else
retValue.append(",'TM9'");
// TO_CHAR(GrandTotal,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')
if (!Language.isDecimalPoint(AD_Language)) // reversed
retValue.append(",'NLS_NUMERIC_CHARACTERS='',.'''");
}
else if (DisplayType.isDate(displayType))
{
retValue.append(",'")
.append(Language.getLanguage(AD_Language).getDBdatePattern())
.append("'");
}
retValue.append("))");
//
return retValue.toString();
} // TO_CHAR
/**
* Return number as string for INSERT statements with correct precision
* @param number number
* @param displayType display Type
* @return number as string
*/
public String TO_NUMBER (BigDecimal number, int displayType)
{
if (number == null)
return "NULL";
return number.toString();
} // TO_NUMBER
/**
* Get SQL Commands.
* The following variables are resolved:
* @SystemPassword@, @CompiereUser@, @CompierePassword@
* @SystemPassword@, @DatabaseName@, @DatabaseDevice@
* @param cmdType CMD_*
* @return array of commands to be executed
*/
public String[] getCommands (int cmdType)
{
if (CMD_CREATE_USER == cmdType)
return new String[]
{
};
//
if (CMD_CREATE_DATABASE == cmdType)
return new String[]
{
};
//
if (CMD_DROP_DATABASE == cmdType)
return new String[]
{
};
//
return null;
} // getCommands
/**
* Create DataSource
* @param connection connection
* @return data dource
*/
public DataSource getDataSource(CConnection connection)
{
if (m_ds != null)
return m_ds;
try
{
m_ds = new OracleDataSource();
m_ds.setDriverType("thin");
m_ds.setNetworkProtocol("tcp");
m_ds.setServerName(connection.getDbHost());
m_ds.setServiceName(connection.getDbName());
m_ds.setPortNumber(connection.getDbPort());
m_ds.setUser(connection.getDbUid());
m_ds.setPassword(connection.getDbPwd());
//
m_ds.setDataSourceName("CompiereDS");
m_ds.setDescription("Compiere Oracle Data Source");
m_ds.setImplicitCachingEnabled(true);
m_ds.setExplicitCachingEnabled(true);
m_ds.setMaxStatements(MAX_STATEMENTS);
//
Properties cacheProperties = new Properties();
// cacheProperties.setProperty("InitialLimit", "3"); // at startup
// cacheProperties.setProperty("MaxStatementsLimit", "10");
cacheProperties.setProperty("ClosestConnectionMatch", "true");
cacheProperties.setProperty("ValidateConnection", "true");
if (Ini.isClient())
{
cacheProperties.setProperty("MinLimit", "0");
// cacheProperties.setProperty("MaxLimit", "5");
cacheProperties.setProperty("InactivityTimeout", "300"); // 5 Min
cacheProperties.setProperty("AbandonedConnectionTimeout", "300"); // 5 Min
}
else // Server Settings
{
cacheProperties.setProperty("MinLimit", "3");
// cacheProperties.setProperty("MaxLimit", "5");
cacheProperties.setProperty("InactivityTimeout", "600"); // 10 Min
cacheProperties.setProperty("AbandonedConnectionTimeout", "600"); // 10 Min
}
cacheProperties.setProperty("PropertyCheckInterval", "120"); // 2 Min
//
if (USE_CACHE)
{
m_ds.setConnectionCachingEnabled(true);
m_ds.setConnectionCacheName(CACHE_NAME);
m_ds.setFastConnectionFailoverEnabled(true);
}
//
if (m_cacheMgr == null && USE_CACHE)
{
m_cacheMgr = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
if (!m_cacheMgr.existsCache(CACHE_NAME))
m_cacheMgr.createCache(CACHE_NAME, m_ds, cacheProperties);
}
// test
// OracleConnection con = m_ds.getConnection();
// con.close();
//
log.config(toString());
//
return m_ds;
}
catch (Exception e)
{
log.log(Level.SEVERE, toString(), e);
}
return null;
} // getDataSource
/**
* Get Cached Connection
* @param connection info
* @param autoCommit true if autocommit connection
* @param transactionIsolation Connection transaction level
* @return connection or null
*/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -