JBoss.orgCommunity Documentation

Chapter 14. Using other Databases

14.1. DataSource Configuration Files
14.2. Using MySQL as the Default DataSource
14.2.1. Installing the JDBC Driver and Deploying the datasource
14.2.2. Testing the MySQL DataSource
14.3. Configuring a datasource for Oracle DB
14.3.1. Installing the JDBC Driver and Deploying the DataSource
14.3.2. Testing the Oracle DataSource
14.4. Configuring a datasource for Microsoft SQL Server 200x
14.4.1. Installing the JDBC Driver and Deploying the DataSource
14.5. Configuring JBoss Messaging Persistence Manager
14.6. Creating a JDBC client

In the previous chapters, we’ve been using the JBossAS default datasource in our applications. This datasource is configured to use the embedded Hypersonic database instance shipped by default with the distribution. This datasource is bound to the JNDI name java:/DefaultDS and its descriptor is named hsqldb-ds.xml under the deploy directory

Having a database included with JBossAS is very convenient for running the server and examples out-of-the-box. However, this database is not a production quality database and as such should not be used with enterprise-class deployments. As a consequence of this JBoss Support does not provide any official support for Hypersonic.

In this chapter we will explain in details how to configure and deploy a datasource to connect JBossAS to the most popular database servers available on the market today.

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.

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

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.

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