Friday, August 12, 2011

Oracle / PostgreSQL Datasource in JBoss AS 7

First follows a description for an Oracle RDBMS based datasource. The PostgreSQL variant works identical, the last section contains the necessary information.

Install Oracle JDBC Driver

We assume a ready-to-use Oracle connection URL and access data. You could e.g. install Oracle XE on your local machine. But beware: Then you have to switch the default port 8080 (or the interface) for your JBoss AS server because Oracle XE uses this port for a Web based administration console.

Download the Oracle JDBC Driver from here: Oracle Database 11g Release 2 (11.2.0.2.0) JDBC Drivers
(I use ojdbc6.jar for JDK 6.)

You have two options for installing the driver:
  • as Deployment
  • as Module
I prefer the second option but I  describe both here:

    Driver as Deployment

    Put ojdbc6.jar under .../jboss-as-7.2.0.Alpha1-SNAPSHOT/standalone/deployments.

    After some seconds the file "ojdbc6.jar.deployed" should appear in the same folder. JBoss AS 7 uses such files to trigger deployment events (e.g. create empty file "<artifact>.dodeploy") or to signal results (e.g. "<artifact>.deployed" or "<artifact>.failed"). You can also use the Web-based Admin console under http://localhost:8080/, but really...this thing is outright unusable and _years_ away from something like the Oracle WebLogic Server Admin console.

    Add  datasource in .../jboss-as-7.2.0.Alpha1-SNAPSHOT/standalone/configuration/standalone.xml
    (just like java:jboss/datasources/ExampleDS):
    <datasource jndi-name="java:/jdbc/testAppDS" pool-name="OracleDS" enabled="true" jta="true" use-java-context="true" use-ccm="true">
        <connection-url>
            jdbc:oracle:thin:@localhost:1521:XE
        </connection-url>
        <driver>
            ojdbc6.jar
        </driver>
        ...
    </datasource>
    Only if you need a 2PC / XA datasource you also must add a driver section in the datasource configuration (like H2):
    <driver name="ojdbc6.jar" module="">
        <xa-datasource-class>
            oracle.jdbc.xa.client.OracleXADataSource
        </xa-datasource-class>
    </driver>
    Restart the application server. You can and _should_ check the deployment state in the running application server. Use the command line:
    .../jboss-as-7.2.0.Alpha1-SNAPSHOT/bin/jboss-cli.bat
    You are disconnected at the moment. Type 'connect' to connect to the server or 'help' for the list of supported commands.
    [disconnected /] connect
    Connected to standalone controller at localhost:9999
    [standalone@localhost:9999 /] /subsystem=datasources:installed-drivers-list
    {
        "outcome" => "success",
        "result" => [
            {
                "driver-name" => "h2",
                "deployment-name" => undefined,
                "driver-module-name" => "com.h2database.h2",
                "module-slot" => "main",
                "driver-datasource-class-name" => "",
                "driver-xa-datasource-class-name" => "org.h2.jdbcx.JdbcDataSource",
                "driver-class-name" => "org.h2.Driver",
                "driver-major-version" => 1,
                "driver-minor-version" => 2,
                "jdbc-compliant" => true
            },
            {
                "driver-name" => "ojdbc6.jar",
                "deployment-name" => "ojdbc6.jar",
                "driver-module-name" => undefined,
                "module-slot" => undefined,
                "driver-datasource-class-name" => undefined,
                "driver-xa-datasource-class-name" => undefined,
                "driver-class-name" => "oracle.jdbc.OracleDriver",
                "driver-major-version" => 11,
                "driver-minor-version" => 2,
                "jdbc-compliant" => true
            }
        ]
    }
    [standalone@localhost:9999 /] /subsystem=datasources/data-source=java\:\/jdbc\/testAppDS:test-connection-in-pool
    {
        "outcome" => "success",
        "result" => [true]
    }
    [standalone@localhost:9999 /] quit
    Closed connection to localhost:9999

    You have done something wrong if you see this instead:
    [standalone@localhost:9999 /] /subsystem=datasources/data-source=java\:\/jdbc\/testAppDS:test-connection-in-pool
    {
        "outcome" => "failed",
        "failure-description" => "Operation handler failed: java.util.NoSuchElementException: \"data-source\" => \"java:/jdbc/testAppDS\"",
        "rolled-back" => true
    }
    In this case your Web app deployments (that use this datasource) will fail with this unwilling message:
    12:04:06,628 INFO  [org.jboss.as.server.controller] (DeploymentScanner-threads - 2) Deployment of "test.war" was rolled back with failure message Operation handler failed to complete
    12:04:06,629 ERROR [org.jboss.as.deployment] (DeploymentScanner-threads - 1) {"Composite operation failed and was rolled back. Steps that failed:" => {"Operation step-2" => "Operation handler failed to complete"}}
    12:04:06,632 ERROR [org.jboss.as.deployment] (DeploymentScanner-threads - 1) Composite operation was rolled back
    According to the documentation the driver deployment is the recommended option but I had some problems with it. Sometimes the driver spontaneously undeploys and I have to trigger a redeployment - not impossible but annoying. My database views where also much faster after the switch but I don't know if this was another effect (also upgraded JBoss AS to a newer build). So I prefer the module version:

    Driver as Module

    Add folder oracle/db/main under .../jboss-as-7.2.0.Alpha1-SNAPSHOT/modules/com. Put ojdbc6.jar and the following file module.xml under this folder:

    <module xmlns="urn:jboss:module:1.0" name="com.oracle.db">
        <resources>
            <resource-root path="ojdbc6.jar"/>
        </resources>
        <dependencies>
            <module name="javax.api"/>
            <module name="javax.transaction.api"/>
        </dependencies>
    </module>

    Add this to the datasource drivers section (see above standalone.xml):
    <datasource jndi-name="java:/jdbc/testAppDS" pool-name="OracleDS" enabled="true" jta="true" use-java-context="true" use-ccm="true">
        <connection-url>
            jdbc:oracle:thin:@localhost:1521:XE
        </connection-url>
        <driver>
            oracle
        </driver>
        <security>
            <user-name>user</user-name>
            <password>pwd</password>
        </security>
    
        <new-connection-sql>select * from dual</new-connection-sql>
        <validation>
            <check-valid-connection-sql>select * from dual</check-valid-connection-sql>
            <background-validation>true</background-validation>
            <background-validation-millis>10000</background-validation-millis>
        </validation>
        <timeout>
            <allocation-retry>1</allocation-retry>
        </timeout>
    </datasource>
        ...
    <drivers>
        <driver name="oracle" module="com.oracle.db">
            <xa-datasource-class>
                oracle.jdbc.xa.client.OracleXADataSource
            </xa-datasource-class>
        </driver>
    Now you should see this in the driver list (see above jboss-admin.bat):
            {
                "driver-name" => "oracle",
                "deployment-name" => undefined,
                "driver-module-name" => "com.oracle.db",
                "module-slot" => "main",
                "driver-datasource-class-name" => "",
                "driver-xa-datasource-class-name" => "oracle.jdbc.xa.client.OracleXADataSource",
                "driver-class-name" => "oracle.jdbc.OracleDriver",
                "driver-major-version" => 11,
                "driver-minor-version" => 2,
                "jdbc-compliant" => true
            }
    

    persistence.xml

    The persistence.xml of your Web application deployment is like usual:
    <?xml version="1.0" encoding="UTF-8"?>
    <persistence xmlns="http://java.sun.com/xml/ns/persistence"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
     version="2.0">
     <persistence-unit name="database">
      <jta-data-source>java:/jdbc/testAppDS</jta-data-source>
      <properties>
       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
       <property name="hibernate.hbm2ddl.auto" value="create-drop" />
       <property name="hibernate.show_sql" value="true" />
       <property name="hibernate.format_sql" value="false" />
       <property name="hibernate.use_sql_comments" value="true" />
      </properties>
     </persistence-unit>
    </persistence>
    

    Oracle11gDialect?

    Update: The following trick isn't necessary anymore with Hibernate Since 4.1 and JBoss 7.1.

    We use schema autocreation for development and Hibernate 4.0.0 trys to map boolean attributes to the datatype boolean. This type doesn't exist in Oracle DB.
    create table Organisation (id number(19,0) not null, aktiv boolean not null, ...
    There are different ways to fix this. We simply add this class to our Web app:
    package org.hibernate.dialect;
    
    import java.sql.Types;
    
    public class Oracle11gDialect extends Oracle10gDialect {
    
     public Oracle11gDialect() {
      registerColumnType(Types.BOOLEAN, "number(1,0)");
     }
    
    }
    And declare this as dialect in the persistence.xml:
       <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle11gDialect" />
    Add this as Maven dependency to your Web app (also good for debugging and some Hibernate annotations):
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>4.0.0.Beta5</version>
        <scope>provided</scope>
    </dependency>
    Now everything works just fine.

    PostgreSQL datasource with XA

    Add folder postgresql/main under .../jboss-as-7.2.0.Alpha1-SNAPSHOT/modules/org. Put postgresql-9.1-901.jdbc4.jar and the following file module.xml under this folder:
    <?xml version="1.0" encoding="UTF-8"?>
    <module xmlns="urn:jboss:module:1.1" name="org.postgresql">
        <resources>
            <resource-root path="postgresql-9.1-901.jdbc4.jar"/>
        </resources>
        <dependencies>
            <module name="javax.api"/>
            <module name="javax.transaction.api"/>
        </dependencies>
    </module>
    

    Add this to the datasource drivers section (see above standalone.xml):
    <xa-datasource jndi-name="java:/testAppDS" pool-name="PostgresDS" enabled="true" use-java-context="true">
        <xa-datasource-property name="ServerName">
            abc.de
        </xa-datasource-property>
        <xa-datasource-property name="PortNumber">
            5432
        </xa-datasource-property>
        <xa-datasource-property name="DatabaseName">
            dbname
        </xa-datasource-property>
        <driver>postgresql</driver>
        <security>
            <user-name>user</user-name>
            <password>pwd</password>
        </security>
    </datasource>
        ...
    <drivers>
        <driver name="postgresql" module="org.postgresql">
            <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
        </driver>
    

    You might also be interested in

    22 comments:

    1. great article for jdbc connectivity with oracle

      ReplyDelete
    2. "Put ojdbc6.jar and the following file modules.xml under this folder" should be "Put ojdbc6.jar and the following file module.xml under this folder"

      ReplyDelete
    3. ups...thank you, important detail and fixed

      ReplyDelete
    4. Lets say i am not configuring the module.xml , i am simply deploying my oracle driver into the deployment folder. then what shud i mention in standalone.xml in the



      ??????

      ReplyDelete
    5. hi rohit...

      sry was at JAX conference last days...


      like described under "driver as deployment". you need the sections "datasource" _and_ "driver".


      if you install as module (what i recommand) then you only need the driver section if you need a XA datasource...e.g. you have multiple datasources in 1 transaction.

      ReplyDelete
    6. Hi, great tutorial.

      I followed the steps of the tutorial, but get an error "Caused by: java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path"

      I added the key java.library.path

      So show me the following error: "Caused by: java.lang.UnsatisfiedLinkError: C: \ app \ product \ 11.2.0 \ client_3 \ BIN \ ocijdbc11.dll: Can not find dependent libraries"

      Have you seen this?

      ReplyDelete
      Replies
      1. Hi,

        never had this one, do you use platform specific JDBC-OCI libraries?
        You may use an OCI (thick) JDBC driver with native implementation parts. This can be fixed, but I have never used this one.

        Take the thin (pure Java) driver ojdbc6.jar from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html.

        Best regards,
        André

        Delete
    7. Great article, very detailed.

      ReplyDelete
      Replies
      1. Hi,

        thank your for this article, it helps a lot.

        I am not sure if the "Driver as Deployment" use case is so useful.
        I like the idea to include the driver to my application. With my deployment I can use a fresh installed server, fine!
        However the deployment depends of the data source and the data source depends of the Oracle JDBC driver.
        That means, I must first create the data source but I cannot do it without my deployment.

        Where is my fault?

        Kai

        Delete
      2. Hi,

        if you use an JEE Application Server you normally also use features like Java Persistence API, Messaging Service, container managed XA transactions between multiple datasources or datasource pool configuration and monitoring in more sophisticated systems like WebLogic Server.

        This only works if the JDBC driver lives in the same ClassLoader layer like this Container Managed shared Services.

        If you deploy the driver into the WAR / EAR deployment file, you could (and will in most app servers) get problems with class loading issues and class loading order.

        If you don't use JPA etc. and you also deploy Hibernate/EclipseLink with the WAR/EAR (is possible) - than you don't need an application server at all.


        In each case...we allways bundle our applications with the configured and patched app servers and even with the proper Eclipse with all used plugins.
        This 3 elements, Web-App, app server and Eclipse with plugins often are dependant in this exact configuration from each other. The Eclipse project settings depend from the Eclipse version and plugins (and vice versa). The Web-App end Eclipse depend from the App server with it's configuration (for instance SFSB timeouts or JMS dead letter queue configuration).

        Best regards,
        André

        Delete
    8. Did you have any troubles with the class loader and your custom dialect? We're trying to load up Hibernate 4 in JBoss AS 7 and it's complaining about Classnotfoundexception for our custom dialect. Do we need to create a module out of that?

      ReplyDelete
      Replies
      1. Hi Kevin,

        if you use a recent version of JBoss 7, e.g. 7.1 - this trick with the custom dialect isn't necessary anymore.
        Simply use the existing "org.hibernate.dialect.Oracle10gDialect".

        Since Hibernate 4.0.0.CR5 the problem with Booleans in Schema Creation is patched. JBoss 7.1 has Hibernate 4.1.


        BTW - we naver had class loading issues for both solutions, as module or deployment. But this may depend on the initialization order and your WAR setup or if you have parallel deployments etc.

        Best regards,
        André

        Delete
    9. Greetings: great tutorial, i configured the connection as module, when i tried this command: /subsystem=datasources/data-source=java\:\/jdbc\/ifosAppDS:test-connection-in-pool (i kept the same jndi name),i received the outcome:failed, it says my datasource was not found, previously i saw my driver listed as shown in your example, but when i test the datasource from the admin console through web browser it says everything is ok, do you know which could be the problem? thanks in advance.

      ReplyDelete
      Replies
      1. Hi,

        hmmm thats hard to tell with this infos...could be anything :)
        JBoss AS 7 is great, bit it's really terrible with failure messages for misconfiguration, especially datasources.
        I can just advice, use the module variant, not as deployment. This is much more reliable.

        Delete
    10. Hey, thanks for the tutorial but there is a problem with your first approach, when you install the driver as a deployment providing an empty module () name in the standalone.xml triggers an error:

      18:47:55,273 ERROR [org.jboss.as.server] JBAS015956: Se atrapó una excepción durante el arranque: org.jboss.as.controller.persistence.ConfigurationPersistenceException: JBAS014676: No se logró analizar sintácticamente la configuración
      at org.jboss.as.controller.persistence.XmlConfigurationPersister.load(XmlConfigurationPersister.java:141) [jboss-as-controller-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
      at org.jboss.as.server.ServerService.boot(ServerService.java:270) [jboss-as-server-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
      at org.jboss.as.controller.AbstractControllerService$1.run(AbstractControllerService.java:156) [jboss-as-controller-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]
      at java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_33]
      Caused by: javax.xml.stream.XMLStreamException: javax.xml.stream.XMLStreamException: ParseError at [row,col]:[111,21]
      Message: "JBAS014704: '' es un valor inválido para el parámetro module. Los valores deben tener una longitud mínima de 1 caracteres"


      Perhaps something changed in their implementation...

      ReplyDelete
      Replies
      1. Hi

        usually I update this article as often as necessary, kind of a documentation for us too.

        As I mentioned, I really prefer the modul style and not as deployment. Hence it might be possible that something changed and I havn't noticed for this part.
        I will check as fast as possible, but as you can see I have not much time writing articles, even though I have so much similar TODO topics in mind. JBoss AS 7 is great and we love it.

        Cheers, André

        Delete
    11. Hi André,
      Great tutorial, thank you.

      There's a small error:
      For the postgres XA datasource the final tag should be rather than .

      Thanks, Steve

      ReplyDelete
    12. (xml tags were removed form my original message)

      For the postgres XA datasource the final tag should be xa-datasource rather than datasource.

      Thanks, Steve

      ReplyDelete
    13. Excellent post! Thank you!

      ReplyDelete
    14. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
      MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
      For Demo Contact us.
      Sangita Mohanty
      MaxMunus
      E-mail: sangita@maxmunus.com
      Skype id: training_maxmunus
      Ph:(0) 9738075708 / 080 - 41103383
      http://www.maxmunus.com/

      ReplyDelete