📄 db_oracle.java
字号:
public Connection getCachedConnection (CConnection connection,
boolean autoCommit, int transactionIsolation)
throws Exception
{
OracleConnection conn = null;
Exception exception = null;
try
{
if (USE_CACHE && m_cacheMgr == null)
getDataSource(connection);
if (m_ds == null)
getDataSource(connection);
// Properties connAttr = new Properties();
// connAttr.setProperty("TRANSACTION_ISOLATION", CConnection.getTransactionIsolationInfo(transactionIsolation));
// OracleConnection conn = (OracleConnection)m_ds.getConnection(connAttr);
//
// Try 5 times max
for (int i = 0; i < 5; i++)
{
try
{
conn = (OracleConnection)m_ds.getConnection();
if (conn != null)
{
if (conn.getTransactionIsolation() != transactionIsolation)
conn.setTransactionIsolation(transactionIsolation);
if (conn.getAutoCommit() != autoCommit)
conn.setAutoCommit(autoCommit);
conn.setDefaultRowPrefetch(20); // 10 default - reduces round trips
}
}
catch (Exception e)
{
exception = e;
conn = null;
if (e instanceof SQLException
&& ((SQLException)e).getErrorCode() == 1017) // invalid username/password
{
log.severe("Cannot connect to database: "
+ getConnectionURL(connection)
+ " - UserID=" + connection.getDbUid());
break;
}
}
try
{
if (conn != null && conn.isClosed())
conn = null;
// OK
if (conn != null && !conn.isClosed())
break;
if (i == 0)
Thread.yield(); // give some time
else
Thread.sleep(100);
}
catch (Exception e)
{
exception = e;
conn = null;
}
} // 5 tries
if (conn == null && exception != null)
{
log.log(Level.SEVERE, exception.toString());
log.fine(toString());
log.finest("Reference=" + m_ds.getReference());
}
// else
// {
// System.out.println(conn + " " + getStatus());
// conn.registerConnectionCacheCallback(this, "test", OracleConnection.ALL_CONNECTION_CALLBACKS);
// }
}
catch (Exception e)
{
// System.err.println ("DB_Oracle.getCachedConnection");
// if (!(e instanceof SQLException))
// e.printStackTrace();
exception = e;
}
if (exception != null)
throw exception;
return conn;
} // getCachedConnection
/**
* Get Connection from Driver
* @param connection info
* @return connection or null
*/
public Connection getDriverConnection (CConnection connection) throws SQLException
{
getDriver();
return DriverManager.getConnection (getConnectionURL (connection),
connection.getDbUid(), connection.getDbPwd());
} // getDriverConnection
/**
* Get Driver Connection
* @param dbUrl URL
* @param dbUid user
* @param dbPwd password
* @return connection
* @throws SQLException
*/
public Connection getDriverConnection (String dbUrl, String dbUid, String dbPwd)
throws SQLException
{
getDriver();
return DriverManager.getConnection (dbUrl, dbUid, dbPwd);
} // getDriverConnection
/**
* Close
*/
public void close()
{
log.config(toString());
if (m_ds != null)
{
try
{
m_ds.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if (m_cacheMgr != null)
{
try
{
if (m_cacheMgr.existsCache(CACHE_NAME))
m_cacheMgr.purgeCache(CACHE_NAME, false); // not active
// m_cache.disableCache(CACHE_NAME);
// m_cache.removeCache(CACHE_NAME, 0);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
m_cacheMgr = null;
m_ds = null;
} // close
/**
* Clean up
*/
public void cleanup()
{
if (!USE_CACHE)
return;
log.config("");
try
{
if (m_cacheMgr == null)
m_cacheMgr = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
String[] cacheNames = m_cacheMgr.getCacheNameList();
for (int i = 0; i < cacheNames.length; i++)
{
String name = cacheNames[i];
System.out.println(" cleanup: " + name);
System.out.println(" Before = Active=" + m_cacheMgr.getNumberOfActiveConnections(name)
+ ", Available=" + m_cacheMgr.getNumberOfAvailableConnections(name));
m_cacheMgr.purgeCache(name, false);
System.out.println(" Cached = Active=" + m_cacheMgr.getNumberOfActiveConnections(name)
+ ", Available=" + m_cacheMgr.getNumberOfAvailableConnections(name));
m_cacheMgr.purgeCache(name, true);
System.out.println(" All = Active=" + m_cacheMgr.getNumberOfActiveConnections(name)
+ ", Available=" + m_cacheMgr.getNumberOfAvailableConnections(name));
}
}
catch (Exception e)
{
e.printStackTrace();
}
} // cleanup
/**************************************************************************
* Handle Abandoned Connection
* @param conn connection
* @param userObject
* @return true if close - false for keeping it
*/
public boolean handleAbandonedConnection (OracleConnection conn, Object userObject)
{
System.out.println("--------------------handleAbandonedConnection " + conn + " - " + userObject);
return true; // reclaim it
} // handleAbandonedConnection
/**
* Release Connection
* @param conn connection
* @param userObject
*/
public void releaseConnection (OracleConnection conn, Object userObject)
{
System.out.println("----------------------releaseConnection " + conn + " - " + userObject);
} // releaseConnection
/**
* Get Data Type
* @param displayType display type
* @param precision precision
* @param defaultValue if true adds default value
* @return data type
*/
public String getDataType (int displayType, int precision,
boolean defaultValue)
{
String retValue = null;
switch (displayType)
{
// IDs
case DisplayType.Account:
case DisplayType.Assignment:
case DisplayType.Color:
case DisplayType.ID:
case DisplayType.Location:
case DisplayType.Locator:
case DisplayType.PAttribute:
case DisplayType.Search:
case DisplayType.Table:
case DisplayType.TableDir:
retValue = "NUMBER(10)";
break;
// Dynamic Precision
case DisplayType.Amount:
retValue = "NUMBER";
if (defaultValue)
retValue += " DEFAULT 0";
break;
case DisplayType.Binary:
case DisplayType.Image:
retValue = "BLOB";
break;
case DisplayType.Button:
retValue = "CHAR(1)";
break;
// Number Dynamic Precision
case DisplayType.CostPrice:
retValue = "NUMBER";
if (defaultValue)
retValue += " DEFAULT 0";
break;
// Date
case DisplayType.Date:
case DisplayType.DateTime:
case DisplayType.Time:
retValue = "DATE";
if (defaultValue)
retValue += " DEFAULT SYSDATE";
break;
// Number(10)
case DisplayType.Integer:
retValue = "NUMBER(10)";
break;
case DisplayType.List:
retValue = "CHAR(" + precision + ")";
break;
// NVARCHAR
case DisplayType.Memo:
case DisplayType.String:
case DisplayType.Text:
retValue = "NVARCHAR(" + precision + ")";
break;
case DisplayType.TextLong:
retValue = "CLOB";
break;
// Dyn Prec
case DisplayType.Quantity:
retValue = "NUMBER";
break;
case DisplayType.YesNo:
retValue = "CHAR(1)";
break;
default:
log.severe("Unknown: " + displayType);
break;
}
return retValue;
} // getDataType
/**************************************************************************
* Testing
* @param args ignored
*/
public static void main (String[] args)
{
Compiere.startupEnvironment(true);
CConnection cc = CConnection.get();
DB_Oracle db = (DB_Oracle)cc.getDatabase();
db.cleanup();
try
{
Connection conn = null;
// System.out.println("Driver=" + db.getDriverConnection(cc));
DataSource ds = db.getDataSource(cc);
System.out.println("DS=" + ds.getConnection());
conn = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
System.out.println("Cached=" + conn);
System.out.println(db);
//////////////////////////
System.out.println("JAVA classpath: [\n" +
System.getProperty("java.class.path") + "\n]");
DatabaseMetaData dmd = conn.getMetaData();
System.out.println("DriverVersion: ["+
dmd.getDriverVersion()+"]");
System.out.println("DriverMajorVersion: ["+
dmd.getDriverMajorVersion()+"]");
System.out.println("DriverMinorVersion: ["+
dmd.getDriverMinorVersion()+"]");
System.out.println("DriverName: ["+
dmd.getDriverName()+"]");
System.out.println("ProductName: ["+
dmd.getDatabaseProductName() +"]");
System.out.println("ProductVersion: [\n"+
dmd.getDatabaseProductVersion()+"\n]");
//////////////////////////
}
catch (Exception e1)
{
e1.printStackTrace();
}
db.cleanup();
System.out.println("--------------------------------------------------");
/**
DROP TABLE X_Test;
CREATE TABLE X_Test
(
Text1 NVARCHAR2(2000) NULL,
Text2 VARCHAR2(2000) NULL
);
**/
try
{
String myString1 = "123456789 12345678";
String myString = "";
for (int i = 0; i < 99; i++)
myString += myString1 + (char)('a'+i) + "\n";
System.out.println(myString.length());
System.out.println(Util.size(myString));
//
myString = Util.trimSize(myString, 2000);
System.out.println(myString.length());
System.out.println(Util.size(myString));
//
Connection conn2 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
/** **/
PreparedStatement pstmt = conn2.prepareStatement
("INSERT INTO X_Test(Text1, Text2) values(?,?)");
pstmt.setString(1, myString); // NVARCHAR2 column
pstmt.setString(2, myString); // VARCHAR2 column
System.out.println(pstmt.executeUpdate());
/** **/
Statement stmt = conn2.createStatement();
System.out.println(stmt.executeUpdate
("INSERT INTO X_Test(Text1, Text2) values('" + myString + "','" + myString + "')"));
}
catch (Exception e)
{
e.printStackTrace();
}
db.cleanup();
System.out.println("--------------------------------------------------");
System.exit(0);
System.out.println("--------------------------------------------------");
try
{
Connection conn1 = db.getCachedConnection(cc, false, Connection.TRANSACTION_READ_COMMITTED);
Connection conn2 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
Connection conn3 = db.getCachedConnection(cc, false, Connection.TRANSACTION_READ_COMMITTED);
System.out.println("3 -> " + db);
conn1.close();
conn2.close();
conn1 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
conn2 = db.getCachedConnection(cc, true, Connection.TRANSACTION_READ_COMMITTED);
System.out.println("3 -> " + db);
conn1.close();
conn2.close();
conn3.close();
System.out.println("0 -> " + db);
}
catch (Exception e1)
{
e1.printStackTrace();
}
db.cleanup();
// System.exit(0);
System.out.println("--------------------------------------------------");
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.getConnectionRO());
System.out.println(DB.getConnectionRW());
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED));
System.out.println(db);
try
{
System.out.println("-- Sleeping --");
Thread.sleep(60000);
System.out.println(db);
db.close();
db.cleanup();
System.out.println(db);
}
catch (InterruptedException e)
{
}
/**
// Connection option 1
try
{
System.setProperty("oracle.jdbc.Trace", "true");
DriverManager.registerDriver(new OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//dev:1521/dev", "compiere", "compiere");
System.out.println("Catalog=" + con.getCatalog());
DatabaseMetaData md = con.getMetaData();
System.out.println("URL=" + md.getURL());
System.out.println("User=" + md.getUserName());
//
System.out.println("Catalog");
ResultSet rs = md.getCatalogs();
while (rs.next())
System.out.println("- " + rs.getString(1));
//
System.out.println("Table");
rs = md.getTables(null, "COMPIERE", null, new String[] {"TABLE"});
while (rs.next())
System.out.println("- User=" + rs.getString(2) + " | Table=" + rs.getString(3)
+ " | Type=" + rs.getString(4) + " | " + rs.getString(5));
//
System.out.println("Column");
rs = md.getColumns(null, "COMPIERE", "C_ORDER", null);
while (rs.next())
System.out.println("- Tab=" + rs.getString(3) + " | Col=" + rs.getString(4)
+ " | Type=" + rs.getString(5) + ", " + rs.getString(6)
+ " | Size=" + rs.getString(7) + " | " + rs.getString(8)
+ " | Digits=" + rs.getString(9) + " | Radix=" + rs.getString(10)
+ " | Null=" + rs.getString(11) + " | Rem=" + rs.getString(12)
+ " | Def=" + rs.getString(13) + " | " + rs.getString(14)
+ " | " + rs.getString(15) + " | " + rs.getString(16)
+ " | Ord=" + rs.getString(17) + " | Null=" + rs.getString(18)
);
con.close();
}
catch (SQLException ex)
{
ex.printStackTrace();
}
**/
} // main
} // DB_Oracle
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -