Using other Databases
- DataSource Configuration Files
- Using MySQL as the Default DataSource
- Configuring a datasource for Oracle DB
- Configuring a datasource for Microsoft SQL Server 200x
- Configuring JBoss Messaging Persistence Manager
- 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 Files
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.
Using MySQL as the Default DataSource
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.
Installing the JDBC Driver and Deploying the datasource
To make the JDBC driver classes available to the JBoss Application Server, copy the archive mysql-mysql-connector-java-5.1.5-bin.jar from the Connector/J distribution to the lib directory in the default server configuration (assuming that is the server configuration you’re running).
Then create a text file in the deploy directory called mysql-ds.xml with the following datasource descriptor:
<?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>The datasource is pointing at the database called test provided by default with MySQL 5.x. Remember to update the connection url attributes as well as the combo username/password to match your environment setup.
Testing the MySQL DataSource
Using the test client described in Creating a JDBC client”, you may now verify the proper installation of your datasource.
Configuring a datasource for Oracle DB
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
Installing the JDBC Driver and Deploying the DataSource
To make the JDBC driver classes available to JBoss Application Server, copy the archive ojdbc5.jar to the lib directory in the default server configuration (assuming that is the server configuration you’re running).
Then create a text file in the deploy directory called oracle-ds.xml with the following datasource descriptor :
<?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>The datasource is pointing at the database/SID called “xe” provided by default with Oracle XE.
Of course, you need to update the connection url attributes as well as the username/password combination to match your environment setup.
Testing the Oracle DataSource
Before you can verify the datasource configuration, Oracle XE should be reconfigured to avoid port conflict with JBossAS as by default they both start a web server on port 8080.
Open up an Oracle SQLcommand line and execute the following commands:
SQL> connect;Enter user-name: SYSTEMEnter password:Connected. SQL>begin2 dbms_xdb.sethttpport('8090');3 end;4 /PL/SQL procedure successfully completed. SQL>select dbms_xdb.gethttpport from dual;GETHTTPPORT ----------- 8090
The web server started by Oracle XE to provide http-based administration tools is now running on port 8090. Start the JBossAS server instance as you would normally do. You are now ready to use the test client described in Chapter 6.5 to verify the proper installation of your datasource.
Configuring a datasource for Microsoft SQL Server 200x
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.
Installing the JDBC Driver and Deploying the DataSource
To make the JDBC driver classes available to JBoss Application Server, copy the archive sqljdbc.jar from the sqljdbc_1.2 distribution to the lib directory in the default server configuration (assuming that is the server configuration you’re running).
Then create a text file in the deploy directory called mssql-ds.xml with the following datasource descriptor :
<?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 datasource is pointing at a database “pubs” provided by default with MS SQL Server 2000.
Remember to update the connection url attributes as well as the username/password combination to match your environment setup.
Testing the datasource
Using the test client described in Creating a JDBC client”, you may now verify the proper installation of your datasource.
Configuring JBoss Messaging Persistence Manager
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.xmlWarning
Please note that the Hypersonic database is not recommended for production environments due to its limited support for transaction isolation and its low reliability under high load
More information on configuring JBoss Messaging can be found in the JBoss AS Configuration Guide.
Creating a JDBC client
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
Comments