10g의 Connection Cache Attributes Sample Application
Oracle 10g에서의 JDBC Driver는 Connection Caching 메커니즘이 많이 향상 되었습니다.
아래 예제는 Connection Cache안에서 Connection과 관련된 속성을 어떻게 set 하는지를 보여주는 예제 입니다.
예제를 보도록 하죠!
[OTN 소스 입니다.]
----------------------------------------------
---------------------------
ConnectionParams.java
---------------------------
/**
* @author Rajat Gupta
* @version 1.0
*
* Development Environment : Oracle JDeveloper 10g
* Name of the Application : ConnectionParams.java
* Creation/Modification History :
*
* Rajat 25-Aug-2004 Created
*
* Overview of Application : This class saves the Database Connection
* Parameters. Update this file with your
* database details.
**/
package oracle.otnsamples.connectioncache.attributes;
public class ConnectionParams {
/**
* Name of the machine where the database is running
*/
public static String serverName = "insn104a.idc.oracle.com";
/**
* SID of the database
*/
public static String sid = "orcl";
/**
* Port on which the database is listening
*/
public static int portNo = 1521;
/**
* User Name of the schema. Please leave this as scott.
*/
public static String userName = "scott";
/**
* Password of the scott schema
*/
public static String password = "tiger";
/**
* Type of the driver to be used to connect to the database.
*/
public static String driverType = "thin";
}
---------------------------------
UnderstandCacheAttributes.java
---------------------------------
/**
* @author Rajat Gupta
* @version 1.0
*
* Development Environment : Oracle JDeveloper 10g
* Name of the Application : UnderstandCacheAttributes.java
* Creation/Modification History :
*
* Rajat 25-Aug-2004 Created
*
* Overview of Application : This is the main class of the sample application.
* To understand all the various possibilities
* of Connection Attributes, run this class with
* appropriate parameters.
*
**/
package oracle.otnsamples.connectioncache.attributes;
// Import Packages
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
public class UnderstandCacheAttributes {
/**
* Initialize Oracle DataSource
*/
OracleDataSource ods;
/**
* Empty Constructor
*/
public UnderstandCacheAttributes(){
}
/**
* This is the main method of the class. It invokes various
* methods within this class based on the passed arguments.
*
* @param args Passed Arguments
*/
public static void main(String[] args){
// Instantiate Class
UnderstandCacheAttributes cacheAttributes = new UnderstandCacheAttributes();
try{
// Instantiate Oracle DataSource
cacheAttributes.ods = new OracleDataSource();
if (args.length == 0)
{
// If no arguments are passed, then print the list of operations
System.out.println("\nPlease run the class with an argument. ConnectionCache"
+ " will be set for all operations with default attributes.");
System.out.println("\n1. To retrieve the connection with default "
+ "attributes, run with argument 'default'.");
System.out.println("\n2. To retrieve the connection with new attributes,"
+ " run with argument 'new'.");
System.out.println("\n3. To retrieve the connection with default"
+ " attributes and update it, run with argument 'update'.");
System.out.println("\n4. To retrieve the connection with matching "
+ "attributes, run with argument 'matching'.");
System.out.println("\n5. To clear the connection of all attributes, "
+ "run with argument 'clear'.");
}else{
// Read the parameter
String runMethod = args[0];
// Invoking various methods to demonstrate the features, functionality
// and usage of Oracle Connection Cache Attributes
if (runMethod.equalsIgnoreCase("default")){
// Initialize the Connection Cache and its attributes
cacheAttributes.setConnectionCacheAttributes();
System.out.println();
System.out.println("----> We will now retrieve this connection. The "
+ "following method does this");
cacheAttributes.getConnectionWithDefaultSetAttributes();
System.out.println();
System.out.println("----> CONCLUSION : We have retrieved the same connection");
}else if (runMethod.equalsIgnoreCase("new")){
// Initialize the Connection Cache and its attributes
cacheAttributes.setConnectionCacheAttributes();
System.out.println();
System.out.println("----> Lets try to retrieve the connection with a "
+ "new set of Attributes");
cacheAttributes.getConnectionWithNewAttributes();
System.out.println();
System.out.println("----> CONCLUSION : There is not matching connection "
+ "in the cache. So, we have got a new connection.");
}else if (runMethod.equalsIgnoreCase("update")){
// Initialize the Connection Cache and its attributes
cacheAttributes.setConnectionCacheAttributes();
System.out.println();
System.out.println("----> Let us now update the connection attributes "
+ "and use it");
cacheAttributes.getConnectionAndUpdateAttributes();
}else if (runMethod.equalsIgnoreCase("matching")){
// Initialize the Connection Cache and its attributes
cacheAttributes.setConnectionCacheAttributes();
System.out.println();
System.out.println("----> The following method tries to retrieve the "
+ "connection with a matching set of attributes");
cacheAttributes.getConnectionWithMatchingAttributes();
System.out.println();
System.out.println("----> CONCLUSION : As there is no connection with "
+ "exact set of attributes in the cache, we have retrieved a matching "
+ "connection which in this case happens to be OTN_CONNECTION. If "
+ "there was a connection in the cache with an exact match, then "
+ "that connection would be retrieved.");
}else if (runMethod.equalsIgnoreCase("clear")){
// Initialize the Connection Cache and its attributes
cacheAttributes.setConnectionCacheAttributes();
System.out.println();
System.out.println("----> The following method clears the connection "
+ "of all its attributes");
cacheAttributes.clearConnectionAttributes();
System.out.println();
System.out.println("----> Lets see if the Connection Attributes have "
+ "been cleared");
cacheAttributes.getConnectionWithDefaultSetAttributes();
System.out.println();
System.out.println("----> CONCLUSION : There is not matching connection in the cache");
}else{
}
}
}catch(Exception e){
// Catch and print in case of an exception
e.printStackTrace();
}
}
/**
* This method initializes the Oracle DataSource and Connection Cache.
* The default connection attributes are also set within this method.
*
* @exception Exception In Case of an Exception
*/
private void setConnectionCacheAttributes() throws Exception{
System.out.println("\n------------- Invoking method "
+ "setConnectionCacheAttributes() ----------------");
System.out.println("-- Set up a new Connection");
// Set DataSource properties
ods.setUser(ConnectionParams.userName);
ods.setPassword(ConnectionParams.password);
ods.setServerName(ConnectionParams.serverName);
ods.setDatabaseName(ConnectionParams.sid);
ods.setPortNumber(ConnectionParams.portNo);
ods.setDriverType(ConnectionParams.driverType);
// Enable Connection Cache
ods.setConnectionCachingEnabled(true);
// Enter a name for Connection Cache
ods.setConnectionCacheName("MyCache");
// This is a connection cache parameter. This specifies that if there
// is not an exact match of the connection attributes, even then retrieve
// a matching connection. This value, by default, is false.
// For more information on this feature and attribute, please read the
// accompanied Readme file with this sample application.
Properties cacheProps = new Properties();
cacheProps.put("ClosestConnectionMatch", "true");
ods.setConnectionCacheProperties(cacheProps);
// Get a Connection from the Oracle DataSource
OracleConnection conn = (OracleConnection)ods.getConnection();
System.out.println("-- Connection Retrieved. Executing Sample Query in "
+ "the database");
// Execute a Sample Query in the database to demonstrate that the
// connection is established
this.executeQuery(conn);
System.out.println();
System.out.println("-- Initializing Connection Attributes. This set includes");
System.out.println("-- CONNECTION_NAME = OTN_CONNECTION");
System.out.println("-- NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1");
System.out.println();
System.out.println("----> We will call it Default Set Attributes");
// Initialize default Connection Attributes
java.util.Properties connAttr = new Properties();
connAttr.setProperty("CONNECTION_NAME", "OTN_CONNECTION");
connAttr.setProperty("NLS_LANG", "AMERICAN_AMERICA.WE8ISO8859P1");
System.out.println();
System.out.println("-- Setting these Attributes And Closing Connection");
System.out.println();
// Apply the attributes to the connection and release the connection back
// to the MyCache
conn.close(connAttr);
}
/**
* This method retrieves the connection after passing default
* attributes. The attributes are then listed on the console.
*
* @exception Exception In Case of an Exception
*/
private void getConnectionWithDefaultSetAttributes() throws Exception{
System.out.println("\n----- Invoking method "
+ "getConnectionWithDefaultSetAttributes() ---------------");
System.out.println("-- Initializing Default Set Properties");
// Initialize default Connection Attributes
java.util.Properties connAttr = new Properties();
connAttr.setProperty("CONNECTION_NAME", "OTN_CONNECTION");
connAttr.setProperty("NLS_LANG", "AMERICAN_AMERICA.WE8ISO8859P1");
System.out.println("-- Retrieving Connection with Default Set Attributes");
// Get a Connection from the Oracle DataSource
OracleConnection conn = (OracleConnection)ods.getConnection(connAttr);
System.out.println();
System.out.println("-- Connection Retrieved. Executing Sample Query in "
+ "the database");
// Execute a Sample Query in the database to demonstrate that the
// connection is established
executeQuery(conn);
System.out.println();
System.out.println("-- Listing Connection Cache Attributes");
// Print the Connection Attributes onto the console
printAttributes(conn.getConnectionAttributes());
// Release the connection back to the MyCache
conn.close();
}
/**
* This method retrieves the connection after passing new
* parameters.
*
* @exception Exception In Case of an Exception
*/
private void getConnectionWithNewAttributes() throws Exception{
System.out.println("\n-------------- Invoking method "
+ "getConnectionWithNewAttributes() ---------------");
System.out.println("-- Initializing New Set of Attributes. The new Attributes are");
System.out.println("-- CONNECTION_NAME_NEW = OTN_CONNECTION_NEW");
System.out.println("-- NLS_LANG = ISO-LATIN-1");
System.out.println();
// Initialize new Connection Attributes
java.util.Properties connAttr = new Properties();
connAttr.setProperty("CONNECTION_NAME_NEW", "OTN_CONNECTION_NEW");
connAttr.setProperty("NLS_LANG", "ISO-LATIN-1");
System.out.println("-- Retrieving Connection with New Attributes");
// Get a Connection from the Oracle DataSource
OracleConnection conn = (OracleConnection)ods.getConnection(connAttr);
System.out.println("-- Connection Retrieved. Executing Sample Query in the database");
// Execute a Sample Query in the database to demonstrate that the
// connection is established
executeQuery(conn);
System.out.println();
System.out.println("-- Listing Connection Cache Attributes");
// Print the Connection Attributes onto the console
printAttributes(conn.getConnectionAttributes());
// Release the connection back to the MyCache
conn.close();
}
/**
* This method retrieves the connection with default attributes and then
* updates it and uses the connection. After its use, the default
* attributes are again set and the connection is released back to the
* cache.
*
* @exception Exception In Case of an Exception
*/
private void getConnectionAndUpdateAttributes() throws Exception{
System.out.println("\n---------- Invoking method "
+ "getConnectionAndUpdateAttributes() ------------------");
System.out.println("-- Initializing Default Set Attributes");
// Initialize default Connection Attributes
java.util.Properties connAttr = new Properties();
connAttr.setProperty("CONNECTION_NAME", "OTN_CONNECTION");
connAttr.setProperty("NLS_LANG", "AMERICAN_AMERICA.WE8ISO8859P1");
System.out.println();
System.out.println("-- Retrieving Connection with Default Attributes");
// Get a Connection from the Oracle DataSource
OracleConnection conn = (OracleConnection)ods.getConnection(connAttr);
System.out.println("-- Connection Retrieved.");
System.out.println();
System.out.println("-- Initializing New Set of Attributes. The new set includes");
System.out.println("-- TRANSACTION_ISOLATION = SERIALIZABLE");
// Initialize new Connection Attributes
Properties newProps = new Properties();
newProps.setProperty("TRANSACTION_ISOLATION", "SERIALIZABLE");
System.out.println();
System.out.println("-- Updating the Connection with new set of Attributes");
// Update the connection with the new attributes
conn.applyConnectionAttributes(newProps);
System.out.println();
System.out.println("-- Executing Sample Query in the database");
// Execute a Sample Query in the database to demonstrate that the
// connection is established
executeQuery(conn);
System.out.println();
System.out.println("-- Listing Connection Cache Attributes");
// Print the Connection Attributes onto the console
printAttributes(conn.getConnectionAttributes());
System.out.println("----> As can be seen, Connection Attributes have been updated");
System.out.println();
System.out.println("-- Applying Default Set Attributes again and closing Connection.");
// Apply the Default Set of attributes to the connection again and
// release it to MyCache
conn.close(connAttr);
}
/**
* The 'ClosestConnectionMatch' flag is set in the method
* setConnectionCacheAttributes(). This means that if there is not an
* exact attribute match, then retrieve the connection that closely
* matches.
*
* @exception Exception In Case of an Exception
*/
private void getConnectionWithMatchingAttributes() throws Exception{
System.out.println("\n-------- Invoking method "
+ "getConnectionWithMatchingAttributes() -----------");
System.out.println("-- Initializing Matching Set Properties. The matching set includes");
System.out.println("-- CONNECTION_NAME = OTN_CONNECTION");
System.out.println("-- NLS_LANG = ENGLSH_UNITED KINGDOM.WE8ISO8859P1");
// Initialize matching Connection Attributes. Note that the value of the
// NLS_LANG attribute is different when compared to the default
// connection attributes.
java.util.Properties connAttr = new Properties();
connAttr.setProperty("CONNECTION_NAME", "OTN_CONNECTION");
connAttr.setProperty("NLS_LANG", "ENGLSH_UNITED KINGDOM.WE8ISO8859P1");
System.out.println();
System.out.println("-- Retrieving Connection with matching set of attributes");
// Get a Connection from the Oracle DataSource
OracleConnection conn = (OracleConnection)ods.getConnection(connAttr);
System.out.println();
System.out.println("-- Connection Retrieved. Executing Sample Query in the database");
// Execute a Sample Query in the database to demonstrate that the
// connection is established
executeQuery(conn);
System.out.println();
System.out.println("-- Listing Connection Cache Attributes");
// Print the Connection Attributes onto the console
printAttributes(conn.getConnectionAttributes());
// Release the connection back to the MyCache
conn.close();
}
/**
* This method strips the connection of all set attributes.
*
* @exception Exception In Case of an Exception
*/
private void clearConnectionAttributes() throws Exception {
System.out.println("\n------------- Invoking method "
+ "clearConnectionAttributes() -----------");
System.out.println("-- Initializing Default Set Properties");
// Initialize default Connection Attributes
java.util.Properties connAttr = new Properties();
connAttr.setProperty("CONNECTION_NAME", "OTN_CONNECTION");
connAttr.setProperty("NLS_LANG", "AMERICAN_AMERICA.WE8ISO8859P1");
System.out.println("-- Retrieving Connection with Default Set Attributes");
// Get a Connection from the Oracle DataSource
OracleConnection conn = (OracleConnection)ods.getConnection(connAttr);
System.out.println();
System.out.println("-- Connection Retrieved. Executing Sample Query in the database");
// Execute a Sample Query in the database to demonstrate that the
// connection is established
executeQuery(conn);
System.out.println("----> Apply an empty set of attributes. This "
+ "strips the connection of all its attributes");
// The following code clears the connection of all attributes and releases
// it back to MyCache.
conn.close(new Properties());
}
/**
* This method prints the attributes onto the console.
*
* @param p Properties object holding the attributes as a name-value pair
*/
private void printAttributes(Properties p){
// Check if the properties object has some key-value pairs within it
if (p.elements().hasMoreElements()){
// It has. Print them onto the console
p.list(System.out);
}else{
// Its empty
System.out.println("-- NO ATTRIBUTES SET FOR THIS CONNECTION --");
}
}
/**
* This method executes the query in the database
*
* @param conn Connection Object
* @exception Exception In Case of an Exception
*/
private void executeQuery(OracleConnection conn) throws Exception{
// Create a Statement
Statement stmt = conn.createStatement();
// Execute the query in the database
boolean isExecuted = stmt.execute("select count(*) from emp");
// Test if the query is executed
if (isExecuted){
// It has
System.out.println("Query Executed");
}else{
// There is some problem. Check the error on the console
System.out.println("ERROR : Query not executed");
}
}
}