How to create and manage datasources in AS7

In this post I’ll try to describe how to add and configure datasources in AS7, and related concepts like add/configure jdbc drivers and test connection of a created datasource.

As you probably already know AS7 has been announced by Red Hat officialy here. I’ll not go in deep details of what is new, cool and fast in AS7. Just have a look to the announce page to get a bird eye view of all the cool stuffs you can find there.
Before start with main contets about data source I just want to recall you 2 main concept in AS7 we will have to deal with in this article:

  1. Modular core: JBoss Modules offers true application isolation, hiding server implementation classes and only loads the classes your application needs. Class loading is concurrent for extreme performance. OSGi support is available the moment you install the application server. We will learn in this article to create a new module for new jdbc drivers, and we compare this approach to hot deployed driver analyzing plus and minus of each.
  2. Elegant administration Consistent and powerful management is available, ranging from a polished, user-friendly web console to Java and HTTP APIs to a command line tool to direct XML edits. Configuration data is centralized and user-focused. We will learn in this artcile how to add and manage datasources with all three approaches.
  3. Domain management JBoss AS 7 can be launched in two different modes. Domain mode allows you to run and manage a multi-server topology. Standalone mode runs a single server instance, a convenience choice for development. Many settings, such as port offsets and network interfaces, can be controlled with a single attribute. Rolling deployments are available. In this article we will discuss how to add and manage datasources in both mode, providing sample for both too. Domain mode is one of the main new concept and coolest feature to learn about AS7. If you want more info read documentation about this concepts here.

1. Add a driver
To be able to add a jdbc datasource you have to configure a jdbc driver. As we will see in driver configuration you will need to specify a driver name (a unique symbolic name in the domain) that will be used by JCA susbsytem to estabilish jdbc connections.
1.1 Hot deploy driver. Limit and advantages.
A driver jar can be hot deployed into standalone or domain. This is the most convenient way to add a driver when you are developing, since it require very few or none configuration, but it have some limits:
  • We are supporting hot deploy only for jdbc 4 compliant driver. The reason for that is because we get from META-INF metadata the Driver class name. This is cool because it permits zero configuration, but this metadata are standardized only in jdbc 4 specs.
  • We are not supporting XADataSourceClass at driver level. As you can see from the xsd, we are supporting DriverClass and XADataSourceClass elements both into driver and data-source/xa-data-source elements. Of course one defined into driver element is general and permit you to define just one time. Since there is no spec support for XADataSourceClass metadata in META-INF we can’t get it during deployment, and you will have to specify it for each xa-datasource you are going to create.
  • You can hot deploy only drivers with a single jar. For example if you have to work with Oracle optional orai18n.jar for additional charset support you would need a module based driver.
Note: in this case the unique symbolic name associated to the driver will be the name of the jar you are using. In my examples it will be mysql-connector-java-5.1.15.jar
If you are fine with these limit can be convenient to use an hot deployed driver. But how to deploy it?
1.1.1 Hot deploy copying jar (in standalone mode, not suggested in domain mode)

The easiest way to hot deploy a driver in standalone mode is to copy the jar into the $JBOSS_HOME/standalone/deployments (don’t forget to read the README.located there!). If you are copying a jdbc 4 compliant driver you will get a message like this into console where you have started the standalone server:

12:59:17,663 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-3) Starting deployment of "mysql-connector-java-5.1.15.jar"
12:59:18,191 INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-3) Deploying non-JDBC-compliant driver class com.mysql.jdbc.Driver (version 5.1)
12:59:18,291 INFO  [org.jboss.as.server.controller] (DeploymentScanner-threads - 2) Deployed "mysql-connector-java-5.1.15.jar"
1.1.2 Deploy a driver using jboss-admin.sh command line tool

Standalone: Start the server in standalone mode, open another console, launch the jboss-admin.sh tool. Run these commands:

[standalone@localhost:9999 /] connect
Closed connection to localhost:9999
Connected to standalone controller at localhost:9999
[standalone@localhost:9999 /] deploy /dati/drivers/mysql-connector-java-5.1.15.jar
'mysql-connector-java-5.1.15.jar' deployed successfully.

You will get an output to server running console identical to one pasted in section 1.1.1.

Domain: Start the server in domain mode, pen another console, launch the jboss-admin.sh tool. Run these commands:

[standalone@localhost:9999 /] connect
Closed connection to localhost:9999
Connected to domain controller at localhost:9999
[domain@localhost:9999 /] deploy --all-server-groups /dati/drivers/mysql-connector-java-5.1.15.jar
'mysql-connector-java-5.1.15.jar' deployed successfully.

You will get this output to server running console:

[Server:server-one] 13:07:51,933 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-5) Starting deployment of "mysql-connector-java-5.1.15.jar"
[Server:server-two] 13:07:51,934 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-6) Starting deployment of "mysql-connector-java-5.1.15.jar"
[Server:server-two] 13:07:52,344 INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-7) Deploying non-JDBC-compliant driver class com.mysql.jdbc.Driver (version 5.1)
[Server:server-one] 13:07:52,355 INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-7) Deploying non-JDBC-compliant driver class com.mysql.jdbc.Driver (version 5.1)
[Server:server-two] 13:07:52,441 INFO  [org.jboss.as.server.controller] (pool-1-thread-1) Deployed "mysql-connector-java-5.1.15.jar"
[Server:server-one] 13:07:52,441 INFO  [org.jboss.as.server.controller] (pool-1-thread-1) Deployed "mysql-connector-java-5.1.15.jar"
1.1.3 Deploy a driver using web console

Standalone: Start the server in standalone mode. Open your browser at address http://localhost:9990/console/ and follow this steps.

Domain: Start the server in domain mode. Open your browser at address http://localhost:9990/console/

 

 

Using the web console, don’t forget to enable the driver, because as you can see from the screen cast you will not get driver deployed and available until it is excplicit enabled.

 

1.2 Module drivers. How to create it and why use it.

There is another way to set up a driver in AS7: create a module providing classes needed and define the driver at domain level. Doing that you have several advantage in a domain based production environment perspective. You are going to define a driver in a unique place, without limits mentioned in 1.1. It is not so practical only if you are developing and you need to deploy/undeploy drivers for you tests and/or if you are building yourself AS7 from source and you are used to rebuild it quite frequently.

But it’s very good for a production environment, having drivers present in domain controller runtime state, and also in domain.xml. It is in my humble opinion more clear and easy to maintain for an administrator who will probably try to focus on the domain and server groups and not on single servers.

But how to create a ne module and use it as driver? With 4 simple steps

  1. Create the directory structure for the module. For example to create a module for mysql driver:
    cd $JBOSS_HOME
    mkdir modules/com/mysql
    mkdir modules/com/mysql/jdbc
    mkdir modules/com/mysql/jdbc/main
  2. Copy the driver(s) jar into the created directory
  3. Create a module.xml file to define dependencies and resources. For our mysql example it looks like that:
    <?xml version="1.0" encoding="UTF-8"?>
    <module xmlns="urn:jboss:module:1.0" name="com.mysql.jdbc">
      <resources>
        <resource-root path="mysql-connector-java-5.1.15.jar"/>
            <!-- Insert resources here -->
      </resources>
      <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
      </dependencies>
    </module>
  4.  Add driver to the domain/standalone. You have 2 option here:
    1. Edit standalone.xml/domain.xml adding a driver element into datasources subsystem
      [...]
      <subsystem xmlns="urn:jboss:domain:datasources:1.0">
                  <datasources>
                      <datasource jndi-name="java:jboss/datasources/ExampleDS" enabled="true" use-java-context="true" pool-name="H2DS">
                          <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>
                          <driver>h2</driver>
                          <pool></pool>
                          <security>
                              <user-name>sa</user-name>
                              <password>sa</password>
                          </security>
                          <validation></validation>
                          <timeout></timeout>
                          <statement></statement>
                      </datasource>
                      <drivers>
                          <driver name="h2" module="com.h2database.h2">
                              <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                          </driver>
                          <driver name="mysql" module="com.mysql.jdbc"> <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class> </driver>
                      </drivers>
                  </datasources>
              </subsystem>
      [...]
    2.  Add it using jboss-admin.sh command line interface
      /subsystem=datasources/jdbc-driver=mysql:add(driver-name="mysql",driver-module-name="com.mysql.jdbc",driver-xa-datasource-class-name="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource")

Then you can use the driver to configure a new datasource. Note that you can specify also xa-datasource-class, and it will be used in every xa-datasource you will configure to use this driver. As said in case you are using deployed driver it’s not possible at the moment and you have to configure this class name for each xa-datasource.

Note that if you are creating a module using a non jdbc4 compliant driver you HAVE TO SPECIFY also driver class using <driver-class> tag into <driver> of standalone/domain xml. As said in 1.1 this is the only way to use a non jdbc4 compliant driver.

1.3 installed-driver-list operation. Use and caveats

As said you the driver-name property is important to refer a driver during a datasource configuration. It’s so important to know which drivers are configured and available in a server/domain. To achieve this we are providina specific operation callable from jboss-admin.sh command line interface and used under the wood by web console during datasource configuration. Here you have an example of this call for a server with just h2 driver configured:

[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-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
    }]
}

There is a caveat in domain mode, since installed drivers  are read from runtime state in a server; state which does not exist on the domain controller. It depends on runtime state in a server because if a driver is in a deployment we have no idea until it is deployed; i.e. until it goes through the deployers which only exist on the server. IOW you will find into the profile only drivers defined in modules, while deployed drivers are present only at local server level. An example should be more clear than any description:

[domain@localhost:9999 /] /profile=default/subsystem=datasources:installed-drivers-list
{
    "outcome" => "success",
    "result" => "no metrics available"
}
[domain@localhost:9999 /] /host=local/server=server-one/subsystem=datasources:installed-drivers-list
{
    "outcome" => "success",
    "result" => [{
        "driver-name" => "h2",
        "deployment-name" => undefined,
        "driver-module-name" => "com.h2database.h2",
        "module-slot" => "main",
        "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
    }]
}

2. Add a local datasource

Now that we have learned how to add a new driver we can add a new datasource. Also in this case we have 3 different way to add it: editing xml, using command line interface, or using web console. These examples will be just for standalone mode, to don’t be too much verbose, leaving domain mode as exercise for readers and get feedbacks from them.

In all cases we are going to create a Mysql datasource using a driver created as module.

2.1. Adding a datasource in configuartion xml (standalone.xml)

               <datasource jndi-name="java:jboss/datasources/MySqlDS" enabled="true" use-java-context="true" pool-name="MySqlDS">
                    <connection-url>jdbc:mysql://localhost:3306/test</connection-url>
                    <driver>mysql</driver>
                    <pool></pool>
                    <security>
                        <user-name>root</user-name>
                        <password></password>
                    </security>
                    <validation></validation>
                    <timeout></timeout>
                    <statement></statement>
                </datasource>

 

2.2. Adding a datasource using jboss-admin.sh command line interface
/subsystem=datasources/data-source="java:jboss/datasources/MySqlDS2":add(jndi-name="java:jboss/datasources/MySqlDS2",pool-name="MySqlPool",driver-name="mysql",connection-url="jdbc:mysql://localhost:3306/test",user-name="root")
2.3. Adding a datasource using web console

3. Add an xa datasource

Here you have example of the 3 way to create an xa-datasource in AS7 in domain mode. Don’t’ forget that we are using a driver created as module and so the xa-datasource-class is defined at driver level. As said in 1.1 if you are using an hot deployed driver don’t forget to set this class name for each xa-datasource you are going to create.

3.1. Adding an  xa-datasource in configuartion xml (standalone.xml)
               <xa-datasource jndi-name="java:jboss/datasources/MySqlXADS" enabled="true" use-java-context="true" pool-name="MySqlXADS">
			    <xa-datasource-property name="ServerName">localhost</xa-datasource-property>
			    <xa-datasource-property name="DatabaseName">test</xa-datasource-property>
			    <xa-datasource-property name="User">root</xa-datasource-property>
			    <xa-datasource-property name="Password"></xa-datasource-property>
			    <driver>
				mysql
			    </driver>
                </xa-datasource>
3.2. Adding an xa-datasource using jboss-admin.sh command line interface
/subsystem=datasources/xa-data-source="java:jboss/datasources/MySqlXADS2":add(jndi-name="java:jboss/datasources/MySqlXADS2",pool-name="MySQLXA_Pool2",enabled=true,xa-data-source-properties = {"ServerName"=>"localhost","DatabaseName"=>"test","User"=>"root"},driver-name="mysql")
3.3. Adding an xa-datasource using web console

4. How to test a datasource is correctly working without writing code

We provide an operation to test a datasource is able to open a connection to the declared database. You can use it from command line console jboss-admin.sh. It’s very easy to call:

/subsystem=datasources/xa-data-source=java\:jboss\/datasources\/MySqlXADS2:test-connection-in-pool

Positive answer:

{
    "outcome" => "success",
    "result" => [true]
}

Negative answer:

{
    "outcome" => "failed",
    "failure-description" => "failed to invoke operation: Connection is not valid",
    "rolled-back" => true
}

5. Jndi Name

Please note that in current implementation the only valid jndi name for datasources have to start with java:/ or java:jboss/

6. Conclusion

Play with it, try something different, ask question and report bugs on AS7 user forum or (for more internal things) on IronJacamr User forum.

I’ll try to answer questions and collect the frequently asked ones in another post here.

Have fun and contribute to AS7 and IronJacamar


				                    

15 Responses to “How to create and manage datasources in AS7”

  1. Heiko says:

    You should replace http://legolas/xyz with localhost.

  2. Done. Thanks Heiko!

  3. Jeff Zhang says:

    Good article!

  4. Luca Perfetti says:

    Goooood !!

  5. Divya says:

    Hi,

    I was configuring jboss 7 but getting missing/unsatisfied dependencies error.

    module.xml looks like this

    modules\com\informix\jdbc\main

    And the datasources part in standalone.xml is as below.

    my conne url

    informix-db

    user

    pwd

    com.informix.jdbc.IfxDriver

    Can you please let me know where I am going wrong.

  6. Well, comments is not easy to read with xml content. Please post you r question on jboss.org forum (link in the article). I’ll answer you there.
    Thanks for the interest

  7. Divya says:

    Hi stefano,

    I posted my question in community.jboss.org. Can you please take a look at it. I been working on that for a long time.

    Thanks in Advance

  8. [...] para configurar um datasource para o banco de dados PostgreSQL. Mesmo seguindo o ótimo tutorial How to create and manage datasources in AS7 do Stefano Maestri, a aplicação teimava em não encontrar o [...]

  9. 2011 watches says:

    Really useful article. I simply stumbled upon your website and desired to say that I’ve extremely favored studying your weblog posts. Any implies I’ll be subscribing inside your feed and I hope you publish once far more quickly.

  10. Erik says:

    Good article!

    I am trying to figure out how to connect to install an oracle jdbc driver on openshift. Since I cannot copy ojdbc.jar onto my server directly, I am trying to figure out how to bundle the jars into my war / ear. Any suggestions? http://community.jboss.org/message/624820#624820

  11. Balajee says:

    Very useful article for the develpers. I was struggling with the data source configration and now it is clear. Meanwhile can anyone help me with the steps for migration application from Jboss 4 to 7.

  12. makadown says:

    The part :

    “Note that if you are creating a module using a non jdbc4 compliant driver you HAVE TO SPECIFY also driver class using tag into of standalone/domain xml. As said in 1.1 this is the only way to use a non jdbc4 compliant driver.”

    that you mentioned is PRETTY HELPFUL because it solved my “missing/unsatisfied dependencies error” issue.

    this is how this tutorial shows ( driver section in standalone.xml/domain.xml )
    com.mysql.jdbc.jdbc2.optional.MysqlXADataSource

    and this is how it looks when u add the driver-class section because im using a non jdbc4 compliant driver:
    com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
    com.mysql.jdbc.jdbc2.optional.MysqlXADataSource

    Problem solved for me.

    Thank you very much!

  13. makadown says:

    my bad about last comment , it didnt show xml tags. =( .

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>