Jboss Admin Tutorial: Database Integration on JBoss

Prev   Next

15. Database Integration on JBoss

Application components deployed on JBoss that need access to a relational database can connect to it

  • Directly - by managing their own connections

    • Bloats the code
    • Requires more testing
    • Complicated deployments - requires separate configuration for each web app
    • Slow if connections are not pooled, which is not trivial to implement (though libraries exist)
    • If a connection pool is used, it cannot be shared with other applications further complicating deployments

  • Via a shared database connection pool managed by JBoss

    • Simplifies configuration and maintenance (single file to edit in a "standard" format)
    • Faster because the connections are pooled (production-tested)
    • Can be shared among applications so the connections can be better utilized
    • Applications are portable - as they don’t depend on some internal configuration of the external environment
    • Recommended!

15.1. Steps Involved

  • Define a resource references in your application

    • Require connectivity to RDBMS

  • Provide RDBMS resources (connection pools) in the server

    • Instal JDBC drivers
    • Define a RDBMS DBCP
    • Map JBoss-managed RDBMS DBCP to the application’s resource reference

15.2. Resource Requirement

For example, in a web application we would communicate our need for a container-managed RDBMS in WEB-INF/web.xml file:

<web-app ...>
  ...
  <resource-ref>
    <description>DB Connection</description>
        <res-ref-name>jdbc/NorthwindDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
  </resource-ref>
  ...
</web-app>
[Note]Note

The resource reference jdbc/NorthwindDB is relative to java:comp/env JNDI context (ENC). This context is unique to each application and is isolated from the rest of the system.

15.3. Install JDBC Drivers

  • JDBC Driver is what enables Java applications to talk to specific RDBMS, such as MySQL, DB2, Oracle, etc.
  • Download the JDBC Driver from the database vendor (for MySQL go to http://www.mysql.com/products/connector)
  • Copy the driver JAR into directory ${jboss.server.lib.url} or ${jboss.common.lib.url}
  • Restart JBoss
[Note]Note

In addition to its standard JDBC driver, the MySQL team has also released another connector called MXJ. This is a Java utility package (a JMX Mbean) for deploying and managing a MySQL database. MXJ requires JMX 1.2, which is available since JBoss 4.x. For more info, see http://dev.mysql.com/downloads/connector/mxj/.

15.4. Define a RDBMS DBCP Resource

  • Create a datasource (*-ds.xml) file - e.g. deploy/northwind-ds.xml: 

    <datasources>
  <local-tx-datasource>
    <jndi-name>NorthwindDB</jndi-name>
        <connection-url>jdbc:mysql://localhost:3306/Northwind?autoReconnect=true</connection-url>
        <driver-class>com.mysql.jdbc.Driver</driver-class>
        <user-name>northwind</user-name>
        <password>secret</password>
        <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
        <new-connection-sql>SELECT 1</new-connection-sql>
        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
  </local-tx-datasource>
</datasources>
  • Start with ${jboss.home.dir}/docs/examples/jca/mysql-ds.xml as the template.

  • Some of the other common elements:

    • min-pool-size - the minimum number of pooled database connections. Initialized when the pool is first accessed. Defaults to 0.
    • max-pool-size - the maximum number of pooled database connections. Once this limit is reached, clients block. Defaults to 20.
    • blocking-timeout-millis - the maximum blocking time (in ms) while waiting on an available connection before timing out by throwing an exception. Defaults to 5000 (or 5 seconds).
    • track-statements - if true, unclosed statements are reported on check-in (via a warning message). Defaults to false.
    • idle-timeout-minutes - the maximum time (in minutes) before idle connections are closed.
[Note]Note

In JBoss AS, resources like this DataSource are relative to java:/ JNDI context (remember, this is context is accessible to all applications running in the same JVM). So to access this resource directly, we could lookup java:/NorthwindDB in JNDI.

15.5. Map our Resource

  • Map the application’s resource-ref to the real resource provided by JBoss AS

  • In case of a web application, we would create WEB-INF/jboss-web.xml: 

    <jboss-web>
  <resource-ref>
    <res-ref-name>jdbc/NorthwindDB</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <jndi-name>java:/NorthwindDB</jndi-name>
  </resource-ref>
</jboss-web>
  • This effectively maps java:comp/env/jdbc/NorthwindDB to java:/NorthwindDB

15.6. Using our DataSource (RDBMS DBCP)

Once mapped, the applications can access this resource to get a database connection:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/NorthwindDB");
Connection conn = ds.getConnection();
try {
  // use connection to create statements, etc.
} finally {
  conn.close();
}
[Important]Important

It is imperative to close the connection - as that is the mechanism of returning the connection back to the pool. Additionally, it is also very important to also close the connection-created resources, such as Statements and ResultSets, as these are are maintained in memory as long as the connection is opened, unless explicitly closed

15.7. Hypersonic Database

  • JBoss embedded Java-based RDBMS

  • deploy/hsqldb-ds.xml configures:

    • Embedded database (known as DefaultDS)
    • Connection factories
  • Used by JMS MQ for state management and persistence
  • Can be used for CMP
  • Data can be kept in memory or persisted
  • Can allow access to remote clients over TCP
  • This service is for development/testing use only. It is not production-quality.

  • To enable remote access, edit deploy/hsqldb-ds.xml:

    • Enable remote Hypersonic service: 

      <mbean code="org.jboss.jdbc.HypersonicDatabase" name="jboss:service=Hypersonic">
  <attribute name="Port">1701</attribute>
  <attribute name="Silent">true</attribute>
  <attribute name="Database">default</attribute>
  <attribute name="Trace">false</attribute>
  <attribute name="No_system_exit">true</attribute>
</mbean>

    • Change connection URL to: 

      <connection-url>jdbc:hsqldb:hsql://localhost:1701</connection-url>
  • The data will be persisted to: ${jboss.server.data.dir}/hypersonic/default

15.8. Detecting Connection Leaks

  • JBoss has a CachedConnectionManager service that can be used to detect connection leaks (within the scope of a request)
  • Configured in ${jboss.server.url}/deploy/jbossjca-service.xml

  • Triggered by Tomcat’s server.xml→<Host>→CachedConnectionValve

    • Enabled by default - slight overhead
    • Should be used during testing
    • Can be turned off in production if the code is stable

    • If the CachedConnectionValve is enabled in Tomcat’s server.xml file, then Tomcat must wait for the CachedConnectionManager service on startup. This is accomplished by adding the following line to Tomcat’s META-INF/jboss-service.xml file (near the end): 

      <depends>jboss.jca:service=CachedConnectionManager</depends>
  • Connection pools could be monitored (through JMX) by looking at jboss.jca:name=MyDS,service=ManagedConnectionPool→InUseConnectionCount attribute.

  • The example web application northwind.war can be made to leak resources (on /ListCustomers) by

    • appending requesting /ListCustomers?leak=true, and/or by
    • adding a custom system property: -Dleak.jdbc.resources=true to JAVA_OPTS in run.conf or run.bat (on Windows)

15.9. Lab: Database Connectivity

This lab will require to configure a datasource in order to use a database with the provided application.

  • For this lab, use the northwind.war application.
  • Make sure you have mysql installed

  • The data can be loaded from file northwind.sql by running the following in mysql client: 

    SOURCE /path/to/northwind.sql

  • Once the data is loaded, create a database user to access Northwind database: 

    GRANT ALL PRIVILEGES ON Northwind.* TO northwind@localhost IDENTIFIED BY 'secret';

  • Test that the database and the user are properly set up: 

    /path/to/mysql/bin/mysql -u northwind -psecret Northwind
mysql> SELECT count(*) FROM Customers;
  • The count should be 91.
  • Install the appropriate JDBC driver
  • Look for the required resource-reference
  • Define the needed database resource
  • Set up resource mapping
  • Deploy the application
  • Test that the application can access the referenced resource
  • Enable connection leak detection and test
Prev   Next
 Home | ToC