Monday Feb 15, 2016

Get Oracle JDBC drivers and UCP from Oracle Maven Repository (without IDEs)

The Oracle JDBC drivers and Universal Connection Pool (UCP) are now available on the Oracle Maven Repository https://maven.oracle.com . The versions made available on this repository are 11.2.0.4, 12.1.0.1 and 12.1.0.2. Complementary jars such as simplefan.jar and ons.jar can also be downloaded from Oracle Maven Repository. Follow the steps mentioned in this blog to successfully download the required version of Oracle JDBC drivers and UCP. Please refer to "Get Oracle JDBC drivers and UCP from the Oracle Maven Repository - NetBeans, Eclipse, Intellij" for steps while using NetBeans, Eclipse and Intellij.

(1) Setting up Maven:

Step#1: Download Maven

If you do not have Maven installed already, download and install it from the website: http://maven.apache.org

Step#2: Update M2_HOME and JAVA_HOME environment variables

Make sure to update M2_HOME to point to the path where Maven is installed and JAVA_HOME to point to the location where the JDK is installed. If you require a specific Java version then set it using these system arguments. -Dmaven.compiler.source=1.8 -Dmaven.compiler.target=1.8

(2) Customizing Maven Settings

Step#1: Create a settings.xml file

The settings.xml file is required for downloading Oracle JDBC drivers and UCP. You must create a new one if it does not exist already. The Maven settings.xml file is kept in the local maven repository ( {USER_HOME}/.m2/ ) that is usually under the user home directory.
For example: If the username is test then the path will be
  • Unix : /home/test/.m2/settings.xml
  • Windows : C:\Users\test\.m2\settings.xml
  • Step#2: Registering with the Oracle Maven site

    The Oracle Maven repository requires a valid user registration and the user should also accept the terms and conditions (T&Cs) by logging into http://maven.oracle.com . This username will be used in settings.xml .

    Step#3: Create a settings-security.xml

    Create a settings-security.xml file that holds the master password information under the local maven repository. Refer to Step#1 for local maven repository (../.m2/.) path
    Use the following commands to generate a master password in Maven and add the output of this command to settings-security.xml as shown in the sample
    "mvn -encrypt-master-password <any_master_password>" or "mvn -emp <any_master_password>"
    Sample settings-security.xml :
    <settingsSecurity> <master>{By8wW7YcTxAHof0MF4Z3wPKboywhGJvxHD9m0NvHA2U=}</master> </settingsSecurity>

    Step#4: Encrypt the user password before using it in settings.xml

    Encrypt the user password (the one used to accept T&Cs on http://maven.oracle.com) before using it in settings.xml.
    Use the following commands to encrypt the user password and update settings.xml as shown in the sample
    "mvn -encrypt-password <user_password>" or "mvn -ep <user_password>"
    Sample settings.xml
    <settings> <proxies> <proxy> <active>true</active> <protocol>http</protocol> <host>proxy.mycompany.com</host> <nonProxyHosts>mycompany.com</nonProxyHosts> </proxy> </proxies> <servers> <server> <id>maven.oracle.com </id> <username>firstname.lastname@test.com</username> <password>{pnwmhVnzdM8H3UAneUKLmaHGZCoaprbMQ/Ac5UktvsM=}</password> <configuration> <basicAuthScope> <host>ANY </host> <port>ANY </port> <realm>OAM 11g </realm> </basicAuthScope> <httpConfiguration> <all> <params> <property> <name>http.protocol.allow-circular-redirects </name> <value>%b,true </value> </property> </params> </all> </httpConfiguration> </configuration> </server> </servers> </settings>

    (3) Create a project to test Maven download

    Step#1: Create a pom.xml and specify GAV for JDBC drivers and UCP

    Add the following GAV details for downloading JDBC driver or UCP from the required version.
    <dependencies> <dependency> <groupId>com.oracle.jdbc</groupId> <artifactId>ojdbc7</artifactId> <version>12.1.0.2</version> </dependency> <dependency> <groupId>com.oracle.jdbc</groupId> <artifactId>ucp</artifactId> <version>12.1.0.2</version> </dependency> </dependencies> <repositories> <repository> <id>maven.oracle.com</id> <name>oracle-maven-repo</name> <url>https://maven.oracle.com</url> <layout>default</layout> <releases> <enabled>true</enabled> <updatePolicy>always</updatePolicy> </releases> </repository> </repositories> <pluginRepositories> <pluginRepository> <id>maven.oracle.com</id> <name>oracle-maven-repo</name> <url>https://maven.oracle.com</url> <layout>default</layout> <releases> <enabled>true</enabled> <updatePolicy>always</updatePolicy> </releases> </pluginRepository> </pluginRepositories>

    Step#2: Create 'src' and 'target' directories

    All Java source files must be placed under src directory. Example: /src/main/java/<foldername>/<filename> In addition, create a target directory where the compiled classes will be placed.

    (4) Testing the download of JDBC and UCP

    Step#1: Compile the Maven Project

    Use the following commands to compile or install or package to test the download.
    "mvn –s settings.xml package" or "mvn -s settings.xml compile" or "mvn -s settings.xml install"
    The output of a successful download will look as shown below:
    [INFO] Building jar: /home/test/maven/public-repo-test/target/jdbc-driver-test.jar [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 2.437 s [INFO] Finished at: 2015-10-01T12:58:25-07:00 [INFO] Final Memory: 9M/282M [INFO] ------------------------------------------------------------------------

    Step#2: Check the downloaded JDBC drivers and UCP

    If the build was successful, then the JARs and POMs will be downloaded onto the local maven repository at ~/.m2/repository/com/oracle/jdbc/ojdbc7/12.1.0.2/ . Note that OJDBC7.jar will download all the dependent jar files such as osdt_core.jar, xdb6.jar, xmlparserv2.jar etc., . Each download has its own JAR and POM files.
    Sample output:
    [test@mymachine jdbc]$ pwd /home/test/.m2/repository/com/oracle/jdbc [test@mymachine jdbc]$ ls -lt total 11 drwxr-xr-x+ 3 test dba 3 Oct 1 11:13 osdt_core drwxr-xr-x+ 3 test dba 3 Oct 1 11:13 osdt_cert drwxr-xr-x+ 3 test dba 3 Oct 1 11:13 oraclepki drwxr-xr-x+ 3 test dba 3 Oct 1 11:13 xmlparserv2 drwxr-xr-x+ 3 test dba 3 Oct 1 11:13 orai18n drwxr-xr-x+ 3 test dba 3 Oct 1 11:13 xdb6 drwxr-xr-x+ 3 test dba 3 Oct 1 11:13 ojdbc7

    Thursday Sep 17, 2015

    Using Universal Connection Pooling (UCP) with JBoss AS

    Even though WebLogic with Active GridlLink is Oracle's suggested approach to deploy Java applications that use Oracle Real Applications Clusters (RAC), there might be scenarios in which you can't make that choice (e.g.: certification issues, licensing, library dependency, etc.). Application servers and their database connection pool mechanisms might end up being a bottleneck in the architecture of your applications and a critical component to provide performance, scalability and high-availability.

    UCP, besides providing connection pooling and all its intrinsic benefits, leverages features specific from RAC.
    These features are:

    • Runtime Connection Load Balancing (RCLB)
    • Fast Connection Failover (FCF)
    • Transaction Affinity
    • Built-in support for Database Resident Connection Pooling (DRCP) and Application Continuity (AC)

    You won't be able to exploit this features by using out of the box JBoss' connection pools.

    In this simple example we will show you how to configure UCP to be used in a JBoss deployed application, wrapping UCP around a Singleton Enterprise Java Bean (EJB) and using a Servlet as client to the wrapper. We will also show you how to monitor and administer UCP using JBoss JMX-Console without needing extra configuration, coding or deployment.

    In an ideal scenario the application server would provide us the possibility to set a custom implementation of a connection pooling (by implementing an interface, for example), but that is not the case with JBoss. In JBoss you can configure all the necessary information to create a connection (driver, url, user, password, etc) and some configuration for the pool (minimum size, maximum size, etc), but you can't configure the class that is going to provide the implementation for connection pooling. There's a workaround for that in this article: https://blogs.oracle.com/dev2dev/entry/how_to_use_oracle_universal1 , which explains how to set a UCP data-source using Spring). But for both scenarios you're declaring beans, or wrappers, to contain the UCP configuration.

    1. Download and start JBoss AS:

    You can download it from this site: http://jbossas.jboss.org/downloads/.
    For this sample we used “JBoss AS 6.1.0.Final”: http://download.jboss.org/jbossas/6.1/jboss-as-distribution-6.1.0.Final.zip.
    After unzipping the file you should set your JAVA_HOME env variable to an existing JDK directory (jdk7, it won't work with jdk8).
    To start the app server you go to the unzipped directory /bin and run standalone.sh or standalone.bat, depending on your OS (you can skip this step if you install JBoss Developer Studio.
    You can check successful start-up by opening a browser to http://localhost:8080/

    2. Download and install JBoss Developer Studio:

    This is also an optional step, you can download JBoss Developer Studio from http://www.jboss.org/products/devstudio/download/ after unzipping you start it by running jbdevstudio executable. This is an extension of Eclipse with JBoss specific plugins installed.

    JBoss Developer Studio

    3. Configure JBoss AS in JBoss Developer Studio:

    In “servers” view choose “new” / “server”, select “JBoss Community, JBoss AS 6.x”, select “Home Directory” to the previously downloaded JBoss AS and leave default values.
    After this step you can start/stop application server from JBoss Developer Studio and deploy / un-deploy applications by selecting “Add and Remove..” in Server's contextual menu.

    4. Add ucp.jar and ojdbc.jar both to Jboss's runtime:

    Add both jars in (JBOSS_HOME/common/lib) and in project's build path (project / properties / Java Build Path / libraries). You could also deploy this jars as specifics for the app, off course.

    5. Create the Singleton Wrapper for UCP as this:

    @Startup
    @Singleton(name = "UcpDemoSingletonWrapper")
    public class UcpDemoSingletonWrapper {

    @Resource(name = "connectionFactoryClassName")
    String connectionFactoryClassName;

    @Resource(name = "url")
    String url;

    @Resource(name = "user")
    String user;

    @Resource(name = "password")
    String password;

    @Resource(name = "initialPoolSize")
    Integer initialPoolSize;

    /**
    * Initialize method for the Singleton. This wrapper
    * could also implement JMX APIs to be able to be
    * managed through the JMX Console (or any other 
    * JMX-compliant Tool)
    */
    @PostConstruct
    void init() {

    try {

    // Retrieve values from bean's configuration
    pds.setConnectionFactoryClassName(connectionFactoryClassName);
    pds.setURL(url);
    pds.setUser(user);
    pds.setPassword(password);

    /**
    * Override example's desired pool
    * properties, will be
    * used through JMX Console
    * later in this example
    */
    pds.setInitialPoolSize(initialPoolSize);
    pds.setConnectionPoolName(this.getClass().getName());

    } catch (Exception e) {

    e.printStackTrace();
    }

    }

    // Wrapped pool
    private PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

    /**
    * Retrieves connections using wrapped pool
    *
    * @return a pooled connection
    * @throws SQLException
    */
    public Connection getConnection() throws SQLException {

    /**
    * Trivial implementation to retrieve connections.
    * Logic intrinsic to the pool could be set here.
    */
    return pds.getConnection();

    }

    }

    6. Create EJB configuration file (WEB-INF/ejb-jar.xml) with this values:

    <ejb-jar xmlns="http://java.sun.com/xml/ns/javaee" version="3.1" metadata-complete="false">

    <enterprise-beans>

    <session>

    <ejb-name>UcpDemoSingletonWrapper</ejb-name>
    <env-entry>

    <env-entry-name>connectionFactoryClassName</env-entry-name>
    <env-entry-type>java.lang.String</env-entry-type>
    <env-entry-value>oracle.jdbc.pool.OracleDataSource</env-entry-value>

    </env-entry>

    <env-entry>

    <env-entry-name>url</env-entry-name>
    <env-entry-type>java.lang.String</env-entry-type>
    <env-entry-value>jdbc:oracle:thin:@//myhost:1521/cdb1</env-entry-value>

    </env-entry>

    <env-entry>

    <env-entry-name>user</env-entry-name>
    <env-entry-type>java.lang.String</env-entry-type>
    <env-entry-value>myuser</env-entry-value>

    </env-entry>

    <env-entry>

    <env-entry-name>password</env-entry-name>
    <env-entry-type>java.lang.String</env-entry-type>
    <env-entry-value>mypwd</env-entry-value>

    </env-entry>

    <env-entry>

    <env-entry-name>initialPoolSize</env-entry-name>
    <env-entry-type>java.lang.Integer</env-entry-type>
    <env-entry-value>5</env-entry-value>

    </env-entry>

    </session>

    </enterprise-beans>

    </ejb-jar>

    7. Create the client as this sample servlet:

    /**
    * Simple client to show how to use wrapped ucp's EJB
    *
    */
    @WebServlet("/UcpDemoServletClient")
    public class UcpDemoServletClient extends HttpServlet {

    @EJB
    UcpDemoSingletonWrapper ucpWrapper;

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    doPost(request, response);

    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    try (

    // Get connection from pool, for this trivial example we avoid ORM
    Connection connection = ucpWrapper.getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("select * from test_table")) {

    // Send output to response
    PrintWriter printWriter = response.getWriter();

    while (resultSet.next()) {

    printWriter.println(resultSet.getString(1));

    }

    } catch (SQLException sqlException) {

    throw new ServletException(sqlException);

    }

    }

    }

    8. From Servers's view execute “Publish” and “Start” (or just execute from project's context menu “run in server”).

    JBoss deploy

    9. Administer EJB from JMX-Console:

    Login to http://localhost:8080/jmx-console/
    Look for UCP's MBEANs:
    oracle.ucp.admin.UniversalConnectionPoolManagerMBean
    oracle.ucp.admin.UniversalConnectionPoolMBean

    JMX Console


    Invoke getConnectionPoolName (you will get what was setted on the EJB, “ucp_demo.UcpDemoSingletonWrapper").
    Go through oracle.ucp.admin.UniversalConnectionPoolMBean and look for availableConnectionsCount.

    10. Invoke servlet:

    Typing in a browser http://localhost:8080/ucp_jboss_demo_web_project/UcpDemoServletClient
    Check that after several executions the amount of available connections remains constant.
    Do the same but not closing the connections in the wrapper (remove from the try with resources block, avoiding connection.close())
    Or just execute Connection connection = ucpWrapper.getConnection(); without closing the connection.
    You will check that available connections decreases until exception is thrown.
    Use JMX-console to administer life-cycle of UCP (start / stop / purge connection pool, etc.).
    A full-fledged test would include modifications in the RAC (shutting down/up nodes, checking load balance between them, validating transaction affinity, etc.), in these scenarios UCP would show all its capacities and features (as described at the beginning of this article).

    Wednesday Oct 09, 2013

    Write recovery code with Transaction Guard

    This article demonstrates how to use in Java a new 12C database feature called Transaction Guard.[Read More]
    About

    This blog is owned by the JDBC, UCP and OTA4H development team.

    Search

    Categories
    Archives
    « August 2016
    SunMonTueWedThuFriSat
     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
       
           
    Today