X

Learn Tips and Best Practices from the Oracle JDBC Development

  • March 9, 2017

Using UCP Multi-Tenant Shared Pool feature with Tomcat

Oracle Universal Connection Pool (UCP) adds a new capability called as "Shared Pool" in the Oracle Database 12c Release 2 (12.2). The blog "UCP Multi-Tenant Shared Pool Configuration" shows how a standalone application can use UCP shared pool. Tomcat can also leverage "Shared Pool" benefits by following few simple steps as shown here.

Shared Pool Architecture:

Required Setup

  • Install Apache Tomcat -8.0.26.
  • Copy ojdbc8.jar and ucp.jar into $CATALINA_HOME/lib directory.
  • Create a XML configuration file. Refer blog to create UCP XML config file.
  • Set the Java System property to specify XML configuration file location in catalina.sh. For Windows environment use catalina.bat.
    JAVA_OPTS="$JAVA_OPTS -Doracle.ucp.jdbc.xmlConfigFile='file:/test/ucp/config/SharedPool_config.xml'"
There are two ways in which an application can access the data source defined in the XML configuration file.
  1. By Using UCP PoolDataSourceFactory API:
    Here we can pass data source name as "pds1" and "pds2" to getDataSource("pds1") method. The method returns preconfigured data source object whose properties are defined in UCP XML config file. Using this data source object we can get a connection to the database using getConnection() method.

    A sample code to access data source and connection will look like below -

      // Get the preconfigured data source which is defined in XML configuration file
      private DataSource getDataSource(String dataSourceName) throws SQLException {
        javax.sql.DataSource ds = (javax.sql.DataSource) PoolDataSourceFactory
            .getPoolDataSource(dataSourceName);
        return ds;
      }
    
      private Connection getConnection(DataSource ds) throws SQLException {
        return getConnection();
      }
    
  2. By creating a JNDI Resource :
    • Create a JNDI resource in Tomcat's server.xml file for each data source defined in the UCP XML config file. All the resources defined in Tomcat's config file must point to a data source in UCP XML config file. The mapping is done using a property called "dataSourceFromConfiguration". Here "pds1" and "pds2" are the data source names defined in the sample UCP XML config file used to write this article.
      
      
      
      
      
    • Create resource links in server's context.xml file for above resources defined in server.xml.
      
      
      
      
      
    • Create an application servlet and lookup the JNDI resource to get the data source instance and then call getConnection() API to get a database connection.
        // Get the data source instance defined in UCP XML config file using JNDI lookup
        private javax.sql.DataSource getDataSource(String dataSourceName) {
          try {
            Context initialContext = new InitialContext();
            Context environmentContext = (Context) initialContext
                .lookup("java:comp/env");
            javax.sql.DataSource ds = (javax.sql.DataSource) environmentContext
                .lookup(dataSourceName);
            return ds;
          } catch (NamingException ne) {
            ne.printStackTrace();
          }
          return null;
        }
      
        private Connection getConnection(javax.sql.DataSource ds) throws SQLException {
          return ds.getConnection();
        }
      
      Here we can pass the resource link name to getDataSource(dataSourceName) method to get the data source instance. In this case the resource link names are -"ucpDS1" and "ucpDS2".
      Using this data source instance we can get a connection to the database using getConnection() method.



       

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.