Datasource configuration file names end with the suffix -ds.xml so that they will be recognized correctly by the JCA deployer. The docs/example/jca directory contains sample files for a wide selection of databases and it is a good idea to use one of these as a starting point. For a full description of the configuration format, the best place to look is the DTD file docs/dtd/jboss-ds_1_5.dtd. Additional documentation on the files and the JBoss JCA implementation can also be found in the JBoss Application Server Guide available at http://labs.jboss.com/projects/docs/.

Local transaction datasources are configured using the local-tx-datasource element and XA-compliant ones using xa-tx-datasource. The example file generic-ds.xml shows how to use both types and also some of the other elements that are available for things like connection pool configuration. Examples of both local and XA configurations are available for Oracle, DB2 and Informix.

If you look at the example files firebird-ds.xml, facets-ds.xml and sap3-ds.xml, you’ll notice that they have a completely different format, with the root element being connection-factories rather than datasources. These use an alternative, more generic JCA configuration syntax used with a pre-packaged JCA resource adapter. The syntax is not specific to datasource configuration and is used, for example, in the jms-ds.xml file to configure the JMS resource adapter.

We would also highly recommend consulting the JCA wiki pages at http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCA

Next, we’ll work through some step-by-step examples to illustrate what’s involved setting up a datasource for a specific database.

The MySQL® database has become the world's most popular open source database thanks to its consistent fast performance, high reliability and ease of use. This database server is used in millions of installations ranging from large corporations to specialized embedded applications across every continent of the world. . In this section, we'll be using the community version of their database server (GA 5.0.45) and the latest JDBC driver (GA 5.1.5) both available at http://www.mysql.com.

<?xml version="1.0" encoding="UTF-8"?>

<datasources>

    <local-tx-datasource>

    <jndi-name>DefaultDS</jndi-name>

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

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

    <user-name>root</user-name>

    <password>jboss</password>

    <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>

    <metadata>

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

    </metadata>

    </local-tx-datasource>

</datasources>

Oracle is one of the main players in the commercial database field and most readers will probably have come across it at some point. You can download it freely for non-commercial purposes from http://www.oracle.com/technology/products/database/xe/index.html

In this section, we'll connect the server to Oracle Database 10g Express Edition using the latest JDBC driver (11g) available at http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

<?xml version="1.0" encoding="UTF-8"?>

<datasources>

    <local-tx-datasource>

        <jndi-name>DefaultDS</jndi-name>

        <connection-url>jdbc:oracle:thin:@localhost:1521:xe</connection-url>

        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

        <user-name>SYSTEM</user-name>

        <password>jboss</password>

        <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>

        <metadata>

            <type-mapping>Oracle9i</type-mapping>

        </metadata>

    </local-tx-datasource>

</datasources>

SQL> connect;
Enter user-name: SYSTEM
Enter password:
Connected.
SQL> begin
2  dbms_xdb.sethttpport('8090');
3  end;
4  /
PL/SQL procedure successfully completed.
SQL> select dbms_xdb.gethttpport from dual;
GETHTTPPORT
-----------
8090

In this section, we'll connect the server to MS SQL Server 2000 using the latest JDBC driver (v1.2) available at http://msdn2.microsoft.com/en-us/data/aa937724.aspx.

 
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
  <local-tx-datasource>
  <jndi-name>DefaultDS</jndi-name>
  <connection-url>jdbc:sqlserver://localhost:1433;DatabaseName=pubs</connection-url>
  <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
  <user-name>sa</user-name>
  <password>jboss</password>
  <check-valid-connection-sql>SELECT 1 FROM sysobjects</check-valid-connection-sql>
  <metadata>
	  <type-mapping>MS SQLSERVER2000</type-mapping>
  </metadata>
  </local-tx-datasource>
</datasources> 		

The persistence manager of JBoss Messaging uses the default datasource to create tables to store messages, transaction data and other indexes. Configuration of "persistence" is grouped in xxx-persistence-service.xml files. JBoss Application Server ships with a default hsqldb-persistence-service.xml file, which configures the Messaging server to use the Hypersonic database instance that ships by default with the JBoss Application Server.

You can view the hsqldb-persistence-service.xml file in configurations based on the all or default configurations:

<JBoss_Home>/server/all/deploy/messaging/hsqldb-persistence-service.xml  and
			<JBoss_Home>/server/default/deploy/messaging/hsqldb-persistence-service.xml

More information on configuring JBoss Messaging can be found in the JBoss AS Configuration Guide.

When testing a newly configured datasource we suggest using some very basic JDBC client code embedded in a JSP page. First of all, you should create an exploded WAR archive under the deploy directory which is simply a folder named "jdbcclient.war". In this folder, create a text document named client.jsp and paste the code below:

<%@page contentType="text/html"
 import="java.util.*,javax.naming.*,javax.sql.DataSource,java.sql.*"
 %>
 <%
   
  DataSource ds = null;
  Connection con = null; 
  PreparedStatement pr = null; 
  InitialContext ic; 
  try {
  ic = new InitialContext();
  ds = (DataSource)ic.lookup( "java:/DefaultDS" );
  con = ds.getConnection(); 
  pr = con.prepareStatement("SELECT USERID, PASSWD FROM JMS_USERS");
  ResultSet rs = pr.executeQuery();
  while (rs.next()) {
  out.println("<br> " +rs.getString("USERID") + " | " +rs.getString("PASSWD")); 
  }
  rs.close();
  pr.close();
  }catch(Exception e){
  out.println("Exception thrown " +e); 
  }finally{
  if(con != null){
  con.close();
 }      
} %> 

Open up a web browser and hit the url: http://localhost:8080/jdbcclient/client.jsp. A list of users and password should show up as a result of the jdbc query:

dynsub | dynsub 
guest | guest 
j2ee | j2ee 
john | needle 
nobody | nobody