JBoss.orgCommunity Documentation

Chapter 2. Use Alternative Databases with JBoss AS

2.1. How to Use Alternative Databases
2.2. Install JDBC Drivers
2.2.1. Special notes on Sybase
2.2.2. Configuring JDBC DataSources
2.3. Creating a DataSource for the External Database
2.4. Common configuration for DataSources and ConnectionFactorys
2.4.1. General
2.4.2. XA
2.4.3. Security parameters
2.5. Change Database for the JMS Services
2.6. Support Foreign Keys in CMP Services
2.7. Specify Database Dialect for Java Persistence API
2.8. Change Other JBoss AS Services to Use the External Database
2.8.1. The Easy Way
2.8.2. The More Flexible Way
2.9. A Special Note About Oracle DataBases
2.10. DataSource configuration
2.11. Parameters specific for java.sql.Driver usage
2.12. Parameters specific for javax.sql.XADataSource usage
2.13. Common DataSource parameters
2.14. Generic Datasource Sample
2.15. Configuring a DataSource for remote usage
2.16. Configuring a DataSource to use login modules

2.1. How to Use Alternative Databases

JBoss utilizes the Hypersonic database as its default database. While this is good for development and prototyping, you or your company will probably require another database to be used for production. This chapter covers configuring JBoss AS to use alternative databases. We cover the procedures for all officially supported databases on the JBoss Application Server. They include: MySQL 5.0, PostgreSQL 8.1, Oracle 9i and 10g R2, DB2 7.2 and 8, Sybase ASE 12.5, as well as MS SQL 2005.

Please note that in this chapter, we explain how to use alternative databases to support all services in JBoss AS. This includes all the system level services such as EJB and JMS. For individual applications (e.g., WAR or EAR) deployed in JBoss AS, you can still use any backend database by setting up the appropriate data source connection.

We assume that you have already installed the external database server, and have it running. You should create an empty database named jboss, accessible via the username / password pair jbossuser / jbosspass. The jboss database is used to store JBoss AS internal data -- JBoss AS will automatically create tables and data in it.

2.2. Install JDBC Drivers

For the JBoss Application Server and our applications to use the external database, we also need to install the database's JDBC driver. The JDBC driver is a JAR file, which you'll need to copy into your JBoss AS's <JBoss_Home>/server/all/lib directory. Replace all with the server configuration you are using if needed. This file is loaded when JBoss starts up. So if you have the JBoss AS running, you'll need to shut down and restart. The availability of JDBC drivers for different databases are as follows.

Some of the services in JBoss uses null values for the default tables that are created. Sybase Adaptive Server should be configured to allow nulls by default. 

sp_dboption db_name, "allow nulls by default", true

Refer the sybase manuals for more options.

Enable JAVA services

To use any java service like JMS, CMP, timers etc. configured with Sybase, java should be enabled on Sybase Adaptive Server. To do this use: 

sp_configure "enable java",1

Refer to the sybase manuals for more information.

If java is not enabled you might see this exception being thrown when you try to use any of the above services. 

com.sybase.jdbc2.jdbc.SybSQLException: Cannot run this command because Java services are not enabled. A user with System Administrator (SA) role must reconfigure the system to enable Java

CMP Configuration

To use Container Managed Persistence for user defined Java objects with Sybase Adaptive Server Enterprise the java classes should be installed in the database. The system table 'sysxtypes' contains one row for each extended, Java-SQL datatype. This table is only used for Adaptive Servers enabled for Java. Install java classes using the installjava program. 

installjava -f <jar-file-name> -S<sybase-server> -U<super-user> -P<super-pass> -D<db-name>

Refer the installjava manual in Sybase for more options.

Installing Java Classes

  1. You have to be a super-user with required privileges to install java classes.

  2. The jar file you are trying to install should be created without compression.

  3. Java classes that you install and use in the server must be compiled with JDK 1.2.2. If you compile a class with a later JDK, you will be able to install it in the server using the installjava utility, but you will get a java.lang.ClassFormatError exception when you attempt to use the class. This is because Sybase Adaptive Server uses an older JVM internally, and hence requires the java classes to be compiled with the same.

Rather than configuring the connection manager factory related MBeans discussed in the previous section via a mbean services deployment descriptor, JBoss provides a simplified datasource centric descriptor. This is transformed into the standard jboss-service.xml MBean services deployment descriptor using a XSL transform applied by the org.jboss.deployment.XSLSubDeployer included in the jboss-jca.sar deployment. The simplified configuration descriptor is deployed the same as other deployable components. The descriptor must be named using a *-ds.xml pattern in order to be recognized by the XSLSubDeployer.

The schema for the top-level datasource elements of the *-ds.xml configuration deployment file is shown in Figure 2.1, “The simplified JCA DataSource configuration descriptor top-level schema elements”.

The simplified JCA DataSource configuration descriptor top-level schema elements

Figure 2.1. The simplified JCA DataSource configuration descriptor top-level schema elements


Multiple datasource configurations may be specified in a configuration deployment file. The child elements of the datasources root are:

The non-transactional DataSource configuration schema

Figure 2.2. The non-transactional DataSource configuration schema


The non-XA DataSource configuration schema

Figure 2.3. The non-XA DataSource configuration schema


The XA DataSource configuration schema

Figure 2.4. The XA DataSource configuration schema


The schema for the experimental non-XA DataSource with failover

Figure 2.5. The schema for the experimental non-XA DataSource with failover


The schema for the experimental XA Datasource with failover

Figure 2.6. The schema for the experimental XA Datasource with failover


Elements that are common to all datasources include:

  • jndi-name: The JNDI name under which the DataSource wrapper will be bound. Note that this name is relative to the java:/ context, unless use-java-context is set to false. DataSource wrappers are not usable outside of the server VM, so they are normally bound under the java:/, which isn't shared outside the local VM.

  • use-java-context: If this is set to false the the datasource will be bound in the global JNDI context rather than the java: context.

  • user-name: This element specifies the default username used when creating a new connection. The actual username may be overridden by the application code getConnection parameters or the connection creation context JAAS Subject.

  • password: This element specifies the default password used when creating a new connection. The actual password may be overridden by the application code getConnection parameters or the connection creation context JAAS Subject.

  • application-managed-security: Specifying this element indicates that connections in the pool should be distinguished by application code supplied parameters, such as from getConnection(user, pw).

  • security-domain: Specifying this element indicates that connections in the pool should be distinguished by JAAS Subject based information. The content of the security-domain is the name of the JAAS security manager that will handle authentication. This name correlates to the JAAS login-config.xml descriptor application-policy/name attribute.

  • security-domain-and-application: Specifying this element indicates that connections in the pool should be distinguished both by application code supplied parameters and JAAS Subject based information. The content of the security-domain is the name of the JAAS security manager that will handle authentication. This name correlates to the JAAS login-config.xml descriptor application-policy/name attribute.

  • min-pool-size: This element specifies the minimum number of connections a pool should hold. These pool instances are not created until an initial request for a connection is made. This default to 0.

  • max-pool-size: This element specifies the maximum number of connections for a pool. No more than the max-pool-size number of connections will be created in a pool. This defaults to 20.

  • blocking-timeout-millis: This element specifies the maximum time in milliseconds to block while waiting for a connection before throwing an exception. Note that this blocks only while waiting for a permit for a connection, and will never throw an exception if creating a new connection takes an inordinately long time. The default is 5000.

  • idle-timeout-minutes: This element specifies the maximum time in minutes a connection may be idle before being closed. The actual maximum time depends also on the IdleRemover scan time, which is 1/2 the smallest idle-timeout-minutes of any pool.

  • new-connection-sql: This is a SQL statement that should be executed when a new connection is created. This can be used to configure a connection with database specific settings not configurable via connection properties.

  • check-valid-connection-sql: This is a SQL statement that should be run on a connection before it is returned from the pool to test its validity to test for stale pool connections. An example statement could be: select count(*) from x.

  • exception-sorter-class-name: This specifies a class that implements the org.jboss.resource.adapter.jdbc.ExceptionSorter interface to examine database exceptions to determine whether or not the exception indicates a connection error. Current implementations include:

    • org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter

    • org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter

    • org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter

    • org.jboss.resource.adapter.jdbc.vendor.InformixExceptionSorte

  • valid-connection-checker-class-name: This specifies a class that implements the org.jboss.resource.adapter.jdbc.ValidConnectionChecker interface to provide a SQLException isValidConnection(Connection e) method that is called with a connection that is to be returned from the pool to test its validity. This overrides the check-valid-connection-sql when present. The only provided implementation is org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker.

  • track-statements: This boolean element specifies whether to check for unclosed statements when a connection is returned to the pool. If true, a warning message is issued for each unclosed statement. If the log4j category org.jboss.resource.adapter.jdbc.WrappedConnection has trace level enabled, a stack trace of the connection close call is logged as well. This is a debug feature that can be turned off in production.

  • prepared-statement-cache-size: This element specifies the number of prepared statements per connection in an LRU cache, which is keyed by the SQL query. Setting this to zero disables the cache.

  • depends: The depends element specifies the JMX ObjectName string of a service that the connection manager services depend on. The connection manager service will not be started until the dependent services have been started.

  • type-mapping: This element declares a default type mapping for this datasource. The type mapping should match a type-mapping/name element from standardjbosscmp-jdbc.xml.

Additional common child elements for both no-tx-datasource and local-tx-datasource include:

  • connection-url: This is the JDBC driver connection URL string, for example, jdbc:hsqldb:hsql://localhost:1701.

  • driver-class: This is the fully qualified name of the JDBC driver class, for example, org.hsqldb.jdbcDriver.

  • connection-property: The connection-property element allows you to pass in arbitrary connection properties to the java.sql.Driver.connect(url, props) method. Each connection-property specifies a string name/value pair with the property name coming from the name attribute and the value coming from the element content.

Elements in common to the local-tx-datasource and xa-datasource are:

  • transaction-isolation: This element specifies the java.sql.Connection transaction isolation level to use. The constants defined in the Connection interface are the possible element content values and include:

    • TRANSACTION_READ_UNCOMMITTED

    • TRANSACTION_READ_COMMITTED

    • TRANSACTION_REPEATABLE_READ

    • TRANSACTION_SERIALIZABLE

    • TRANSACTION_NONE

  • no-tx-separate-pools: The presence of this element indicates that two connection pools are required to isolate connections used with JTA transaction from those used without a JTA transaction. The pools are lazily constructed on first use. Its use case is for Oracle (and possibly other vendors) XA implementations that don't like using an XA connection with and without a JTA transaction.

The unique xa-datasource child elements are:

  • track-connection-by-tx: Specifying a true value for this element makes the connection manager keep an xid to connection map and only put the connection back in the pool when the transaction completes and all the connection handles are closed or disassociated (by the method calls returning). As a side effect, we never suspend and resume the xid on the connection's XAResource. This is the same connection tracking behavior used for local transactions.

    The XA spec implies that any connection may be enrolled in any transaction using any xid for that transaction at any time from any thread (suspending other transactions if necessary). The original JCA implementation assumed this and aggressively delisted connections and put them back in the pool as soon as control left the EJB they were used in or handles were closed. Since some other transaction could be using the connection the next time work needed to be done on the original transaction, there is no way to get the original connection back. It turns out that most XADataSource driver vendors do not support this, and require that all work done under a particular xid go through the same connection.

  • xa-datasource-class: The fully qualified name of the javax.sql.XADataSource implementation class, for example, com.informix.jdbcx.IfxXADataSource.

  • xa-datasource-property: The xa-datasource-property element allows for specification of the properties to assign to the XADataSource implementation class. Each property is identified by the name attribute and the property value is given by the xa-datasource-property element content. The property is mapped onto the XADataSource implementation by looking for a JavaBeans style getter method for the property name. If found, the value of the property is set using the JavaBeans setter with the element text translated to the true property type using the java.beans.PropertyEditor for the type.

  • isSameRM-override-value: A boolean flag that allows one to override the behavior of the javax.transaction.xa.XAResource.isSameRM(XAResource xaRes) method behavior on the XA managed connection. If specified, this value is used unconditionally as the isSameRM(xaRes) return value regardless of the xaRes parameter.

The failover options common to ha-xa-datasource and ha-local-tx-datasource are:

  • url-delimeter: This element specifies a character used to separate multiple JDBC URLs.

  • url-property: In the case of XA datasources, this property specifies the name of the xa-datasource-property that contains the list of JDBC URLs to use.

2.3. Creating a DataSource for the External Database

JBoss AS connects to relational databases via datasources. These datasource definitions can be found in the <JBoss_Home>/server/all/deploy directory. The datasource definitions are deployable just like WAR and EAR files. The datasource files can be recognized by looking for the XML files that end in *-ds.xml.

Datasource definition files

The datasource definition files for all supported external databases can be found in the <JBoss_Home>/docs/examples/jca directory.

The following code snippet shows the mysql-ds.xml file as an example. All the other *-ds.xml files are very similiar. You will need to change the connection-url, as well as the user-name / password, to fit your own database server installation.


<datasources>

<local-tx-datasource>

<jndi-name>MySqlDS</jndi-name>

<connection-url>jdbc:mysql://localhost:3306/jboss</connection-url>

<driver-class>com.mysql.jdbc.Driver</driver-class>

<user-name>jbossuser</user-name>

<password>jbosspass</password>

<exception-sorter-class-name>

org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter

</exception-sorter-class-name>

<!-- should only be used on drivers after 3.22.1 with "ping" support

<valid-connection-checker-class-name>

org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker

</valid-connection-checker-class-name>

-->

<!-- sql to call when connection is created

<new-connection-sql>some arbitrary sql</new-connection-sql>

-->

<!-- sql to call on an existing pooled connection when it is obtained from pool - 

 MySQLValidConnectionChecker is preferred for newer drivers

<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>

 -->

   

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

   <metadata>

 <type-mapping>mySQL</type-mapping>

 </metadata>

 </local-tx-datasource>

    

</datasources>

Once you customized the *-ds.xml file to connect to your external database, you need to copy it to the <JBoss_Home>/server/all/deploy directory. The database connection is now available through the JNDI name specified in the *-ds.xml file.

2.4. Common configuration for DataSources and ConnectionFactorys

<xa-resource-timeout> - the number of seconds passed to 

XAResource.setTranasctionTimeout()

when not zero. This feature is available on JBoss AS 4.0.3 and above.

JCA Login Modules - are used to inject security configuration into the connection when configured

Unless the ResourceAdapter has <reauthentication-support> using multiple security identities will create subpools for each identity.

Note

The min and max pool size are per subpool so be careful with these parameters if you have lots of identities.

2.5. Change Database for the JMS Services

The JMS service in the JBoss AS uses relational databases to persist its messages. For improved performance, we should change the JMS service to take advantage of the external database. To do that, we need to replace the file <JBoss_Home>/server/all/deploy/jms-singleton/hsqldb-jdbc2-service.xml with a file in <JBoss_Home>/docs/examples/jms/ depending on your external database. Notice that if you are using the default server profile, the file path is <JBoss_Home>/server/default/deploy/jms/hsqldb-jdbc2-service.xml.

What about the hsqldb-jdbc-state-service.xml file?

Despite its name, the hsqldb-jdbc-state-service.xml file applies to all databases. So, there is no need to use a special jdbc-state-service.xml for each database.

2.6. Support Foreign Keys in CMP Services

Next, we need to go change the <JBoss_Home>/server/all/conf/standardjbosscmp-jdbc.xml file so that the fk-constraint property is true. That is needed for all external databases we support on the JBoss Application Server. This file configures the database connection settings for the EJB2 CMP beans deployed in the JBoss AS.


<fk-constraint>true</fk-constraint>

2.7. Specify Database Dialect for Java Persistence API

The Java Persistence API (JPA) entity manager can save EJB3 entity beans to any backend database. Hibernate provides the JPA implementation in JBoss AS. Hibernate has a dialect auto-detection mechanism that works for most databases including the dialects for databases referenced in this appendix which are listed below. If a specific dialect is needed for alternative databases, you can configure the database dialect in the <JBoss_Home>/server/all/deploy/ejb3.deployer/META-INF/persistence.properties file. You need to un-comment the hibernate.dialect property and change its value to the following based on the database you setup. For a complete list of dialects, refer to the Hibernate Reference Guide, Chapter 3, Section 4.1 SQL Dialects.

DB2 7.2 with Universal JDBC Driver (Type 4)

Large Objects (LOBs) are supported only with DB2 Version 8 servers and above with the universal JDBC driver. Hence JMS services which stores messages as BLOBS and Timer services which uses BLOB fields for storing objects do not work with the JDBC Type 4 driver and DB2 7.2.

DB2 7.2 with JDBC Type 2 driver

All JBoss services work with the JDBC Type 2 driver and DB2 Version 7.2 servers.

2.8. Change Other JBoss AS Services to Use the External Database

Besides JMS, CMP, and JPA, we still need to hook up the rest of JBoss services with the external database. There are two ways to do it. One is easy but inflexible. The other is flexible but requires more steps. Now, let's discuss those two approaches respectively.

The easy way is just to change the JNDI name for the external database to DefaultDS. Most JBoss services are hard-wired to use the DefaultDS by default. So, by changing the datasource name, we do not need to change the configuration for each service individually.

To change the JNDI name, just open the *-ds.xml file for your external database, and change the value of the jndi-name property to DefaultDS. For instance, in mysql-ds.xml, you'd change MySqlDS to DefaultDS and so on. You will need to remove the <JBoss_Home>/server/all/deploy/hsqldb-ds.xml file after you are done to avoid duplicated DefaultDS definition.

In the jms/*-jdbc2-service.xml file, you should also change the datasource name in the depends tag for the PersistenceManagers MBean to DefaultDS. For instance, for mysql-jdbc2-service.xml file, we change the MySqlDS to DefaultDS.

The easy way is just to change the JNDI name for the external database to DefaultDS. Most JBoss services are hard-wired to use the DefaultDS by default. So, by changing the datasource name, we do not need to change the configuration for each service individually.

To change the JNDI name, just open the *-ds.xml file for your external database, and change the value of the jndi-name property to DefaultDS. For instance, in mysql-ds.xml, you'd change MySqlDS to DefaultDS and so on. You will need to remove the <JBoss_Home>/server/all/deploy/hsqldb-ds.xml file after you are done to avoid duplicated DefaultDS definition.

In the jms/*-jdbc2-service.xml file, you should also change the datasource name in the depends tag for the PersistenceManagers MBean to DefaultDS. For instance, for mysql-jdbc2-service.xml file, we change the MySqlDS to DefaultDS.


.. ...

<mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"

       name="jboss.mq:service=PersistenceManager"%gt;

  <depends optional-attribute-name="ConnectionManager">

    jboss.jca:service=DataSourceBinding,name=DefaultDS

  </depends>

... ...

Changing the external datasource to DefaultDS is convenient. But if you have applications that assume the DefaultDS always points to the factory-default HSQL DB, that approach could break your application. Also, changing DefaultDS destination forces all JBoss services to use the external database. What if you want to use the external database only on some services?

A safer and more flexible way to hook up JBoss AS services with the external datasource is to manually change the DefaultDS in all standard JBoss services to the datasource JNDI name defined in your *-ds.xml file (e.g., the MySqlDS in mysql-ds.xml etc.). Below is a complete list of files that contain DefaultDS. You can update them all to use the external database on all JBoss services or update some of them to use different combination of datasources for different services.

2.9. A Special Note About Oracle DataBases

In our setup discussed in this chapter, we rely on the JBoss AS to automatically create needed tables in the external database upon server startup. That works most of the time. But for databases like Oracle, there might be some minor issues if you try to use the same database server to back more than one JBoss AS instance.

The Oracle database creates tables of the form schemaname.tablename. The TIMERS and HILOSEQUENCES tables needed by JBoss AS would not get created on a schema if the table already exists on a different schema. To work around this issue, you need to edit the <JBoss_Home>/server/all/deploy/ejb-deployer.xml file to change the table name from TIMERS to something like schemaname2.tablename.


<mbean code="org.jboss.ejb.txtimer.DatabasePersistencePolicy" 

name="jboss.ejb:service=EJBTimerService,persistencePolicy=database">

<!-- DataSourceBinding ObjectName -->

<depends optional-attribute-name="DataSource">

 jboss.jca:service=DataSourceBinding,name=DefaultDS

</depends>

<!-- The plugin that handles database persistence -->

<attribute name="DatabasePersistencePlugin">

org.jboss.ejb.txtimer.GeneralPurposeDatabasePersistencePlugin

</attribute>

<!-- The timers table name -->

<attribute name="TimersTable">TIMERS</attribute>

</mbean> 

Similarly, you need to change the <JBoss_Home>/server/all/deploy/uuid-key-generator.sar/META-INF/jboss-service.xml file to change the table name from HILOSEQUENCES to something like schemaname2.tablename as well.

<!-- HiLoKeyGeneratorFactory -->
<mbean code="org.jboss.ejb.plugins.keygenerator.hilo.HiLoKeyGeneratorFactory"
name="jboss:service=KeyGeneratorFactory,type=HiLo">
	
<depends>jboss:service=TransactionManager</depends>
	
<!-- Attributes common to HiLo factory instances -->
	
<!-- DataSource JNDI name -->
<depends optional-attribute-name="DataSource">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
	
<!-- table name -->
<attribute name="TableName">HILOSEQUENCES</attribute>

2.10. DataSource configuration

DataSources are defined inside a <datasources> element.

2.11. Parameters specific for java.sql.Driver usage


<connection-property name="char.encoding">UTF-8</connection-property>

2.12. Parameters specific for javax.sql.XADataSource usage


<xa-datasource-property name="IfxWAITTIME">10</xa-datasource-property>

<xa-datasource-property name="IfxIFXHOST">myhost.mydomain.com</xa-datasource-property>

<xa-datasource-property name="PortNumber">1557</xa-datasource-property>

<xa-datasource-property name="DatabaseName">mydb</xa-datasource-property>

<xa-datasource-property name="ServerName">myserver</xa-datasource-property>

2.13. Common DataSource parameters

From JBoss AS 3.2.6 and above, track-statements has a new option: 


<track-statements>nowarn</track-statements

This option closes Statements and ResultSets without a warning. It is also the new default value.

The purpose is to workaround questionable driver behavior where the driver applies auto-commit semantics to local transactions. 

Connection c = dataSource.getConnection(); // auto-commit == false

PreparedStatement ps1 = c.prepareStatement(...);

ResultSet rs1 = ps1.executeQuery();

PreparedStatement ps2 = c.prepareStatement(...);

ResultSet rs2 = ps2.executeQuery();

Assuming the prepared statements are the same. For some drivers, ps2.executeQuery() will automatically close rs1 so we actually need two real prepared statements behind the scenes. This *should* only be for the auto-commit semantic where re-running the query starts a new transaction automatically. For drivers that follow the spec, you can set it to true to share the same real prepared statement.

2.14. Generic Datasource Sample


<datasources>

<local-tx-datasource>

<jndi-name>GenericDS</jndi-name>

<connection-url>[jdbc: url for use with Driver class]</connection-url>

<driver-class>[fully qualified class name of java.sql.Driver implementation]</driver-class>

<user-name>x</user-name>

<password>y</password>

<!-- you can include connection properties that will get passed in 

the DriverManager.getConnection(props) call-->

<!-- look at your Driver docs to see what these might be -->

<connection-property name="char.encoding">UTF-8</connection-property>

<transaction-isolation>TRANSACTION_SERIALIZABLE</transaction-isolation>

    

<!--pooling parameters-->

<min-pool-size>5</min-pool-size>

<max-pool-size>100</max-pool-size>

<blocking-timeout-millis>5000</blocking-timeout-millis>

<idle-timeout-minutes>15</idle-timeout-minutes>

<!-- sql to call when connection is created

<new-connection-sql>some arbitrary sql</new-connection-sql>

-->

                    

<!-- sql to call on an existing pooled connection when it is obtained from pool 

<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>

-->

                    

<set-tx-query-timeout/>

<query-timeout>300</query-timeout> <!-- maximum of 5 minutes for queries -->

    

<!-- pooling criteria.  USE AT MOST ONE-->

<!--  If you don't use JAAS login modules or explicit login 

getConnection(usr,pw) but rely on user/pw specified above, 

don't specify anything here -->



<!-- If you supply the usr/pw from a JAAS login module -->

<security-domain>MyRealm</security-domain>

                    

<!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->

<application-managed-security/>



<!--Anonymous depends elements are copied verbatim into the ConnectionManager mbean config-->

<depends>myapp.service:service=DoSomethingService</depends>

                    

</local-tx-datasource>

                    

<!-- you can include regular mbean configurations like this one -->

<mbean code="org.jboss.tm.XidFactory" 

name="jboss:service=XidFactory">

<attribute name="Pad">true</attribute>

</mbean>



<!-- Here's an xa example -->

<xa-datasource>

<jndi-name>GenericXADS</jndi-name>

<xa-datasource-class>[fully qualified name of class implementing javax.sql.XADataSource goes here]</xa-datasource-class>

<xa-datasource-property name="SomeProperty">SomePropertyValue</xa-datasource-property>

<xa-datasource-property name="SomeOtherProperty">SomeOtherValue</xa-datasource-property>



<user-name>x</user-name>

<password>y</password>

<transaction-isolation>TRANSACTION_SERIALIZABLE</transaction-isolation>



<!--pooling parameters-->

<min-pool-size>5</min-pool-size>

<max-pool-size>100</max-pool-size>

<blocking-timeout-millis>5000</blocking-timeout-millis>

<idle-timeout-minutes>15</idle-timeout-minutes>

<!-- sql to call when connection is created

<new-connection-sql>some arbitrary sql</new-connection-sql>

-->



<!-- sql to call on an existing pooled connection when it is obtained from pool 

<check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>

-->



<!-- pooling criteria.  USE AT MOST ONE-->

<!--  If you don't use JAAS login modules or explicit login 

getConnection(usr,pw) but rely on user/pw specified above, 

don't specify anything here -->



<!-- If you supply the usr/pw from a JAAS login module -->

<security-domain/>



<!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->

<application-managed-security/>



</xa-datasource>



</datasources>

2.15. Configuring a DataSource for remote usage

From JBoss-4.0.0 and above, there is support for accessing a DataSource from a remote client. The one change that is necessary for the client to be able to lookup the DataSource from JNDI is to specify use-java-context=false as shown here: 


<datasources>

<local-tx-datasource>

<jndi-name>GenericDS</jndi-name>

<use-java-context>false</use-java-context>

<connection-url>...</connection-url>

This results in the DataSource being bound under the JNDI name "GenericDS" instead of the default of "java:/GenericDS" which restricts the lookup to the same VM as the jboss server.

Note

JBoss does not recommend using this feature on a production environment. It requires accessing a connection pool remotely and this is an anti-pattern as connections are not serializable. Besides, transaction propagation is not supported and it could lead to connection leaks if the remote clients are unreliable (i.e crashes, network failure). If you do need to access a datasource remotely, JBoss recommends accessing it via a remote session bean facade.

2.16. Configuring a DataSource to use login modules

Add the security-domain parameter to the *-ds.xml file. 


<datasources>

<local-tx-datasource>

...

<security-domain>MyDomain</security-domain>

...

</local-tx-datasource>

</datasources>

Add an application-policy to the login-config.xml file. The authentication section should include the configuration for your login-module. For example, if you want to encrypt the database password, use the SecureIdentityLoginModule login module. 


<application-policy name="MyDomain">

<authentication>

<login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required">

<module-option name="username">scott</module-option>

<module-option name="password">-170dd0fbd8c13748</module-option>

<module-option name="managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=OracleDSJAAS</module-option>

</login-module>

</authentication>

</application-policy>

In case you plan to fetch the data source connection from a web application, make sure authentication is turned on for the web application. This is in order for the Subject to be populated. If you wish for users to be able to connect anonymously, an additional login module needs to be added to the application-policy, in order to populate the security credentials. Add the UsersRolesLoginModule as the first login module in the chain. The usersProperties and rolesProperties parameters can be directed to dummy files. 


<login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule" flag="required">

<module-option name="unauthenticatedIdentity">nobody</module-option>

<module-option name="usersProperties">props/users.properties</module-option>

<module-option name="rolesProperties">props/roles.properties</module-option>

</login-module>