Database Integration on JBoss

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

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

To enrich your career and become a JBoss professional, visit Tekslate, the global online training platform:" JBoss Training". This course will help you achieve excellence in this field.

Steps Involved in connecting to database in JBoss

Define a resource reference in your application

Require connectivity to RDBMS

Provide RDBMS resources (connection pools) in the server

Install JDBC drivers

Define an RDBMS DBCP

Map JBoss-managed RDBMS DBCP to the application’s resource reference

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>

Installing 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

Define an RDBMS DBCP Resource Create a data source (*-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.

##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. Resource Mapping 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 Using  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();
}