| |
<kunle_odutola@hotmail.com> 29 March 2001
This document provides a concise and easy-to-read set of instructions that will get you up to speed quickly on the basics of using Microsoft's SQL Server relational database management system (RDBMS) with the JBoss J2EE application server suite to develop, deploy and run Enterprise Java applications that with CMP or BMP.
JBoss is the brand name of the leading, Open Source, standards-compliant J2EE application server suite. The suite includes the JBossServer EJB v1.1 container and server, the JBossMQ JMS 1.0 implemetation, the JBossNS JNDI implemetation, the JBossCMP advanced O/R mapper and JDBC data object storage implementation and, the JAAS-based JBossSX security framework. All the JBoss applications are 100% Pure Java applications and are collectively the most innovative J2EE application server suite available with features including a much admired modular construction and an advanced plug-in architecture.
Microsoft SQL Server is a robust, high performance and secure platforms for building and managing relational databases. It is a Windows application that runs on the Windows 95, 98, Me, NT and 2000 platforms. There is also a version for the Windows CE platform but I guess it is safe to assume you aren't using that version with JBoss. Or is it?
The combination of the JBoss suite of high performance, Open Source, 100% Pure Java J2EE application servers and the ultra-high performance Microsoft SQL Server RDBMS offers you a J2EE development and deployment environment on the Windows platform with superior performance, good return on investment (ROI) and substantially reduced time to market (RTTM) compared to other J2EE application suites and RDBMS solutions on the Windows platform.
Not so fast amigos. You need to obtain a copy of the JBoss distribution from the JBoss website. General information about all JBoss products as well as instructions on how to get a copy of JBoss for yourself can be found in the JBoss FAQ or on the JBoss website. The rest of this document covers the following tasks:
Now it is important to be aware that both the JBoss application server suite and MS SQL Server are still in active development. They are both evolving as issues are reported, fixed and new features are identified and added. This means that some of the information contained in this document may be out of date with respect to your copy of JBoss or MS SQL Server. This is even more likely to be the case if you are using the latest CVS or developer version of JBoss or a beta of MS SQL Server.
If this is the case with you or, if you have any further comments or suggestions about this document or JBoss then please join the JBoss mailing list. You will then be able to contribute to the production of this document and the development of JBoss.
Before you can use Microsoft SQL Server with JBoss (or indeed any other Java application) you must obtain and install a compatible JDBC driver. Some of the many JDBC drivers that exist for Microsoft SQL Server are listed below.
Table 11.2. Partial list of available MS SQL Server JBDC drivers
| Driver | Supplier | JDBC Type | Availability |
|---|---|---|---|
| FreeTDS | www.freetds.org | Type 4 | Free/Open Source |
| Merant DataDirect Connect JDBC | www.merant.com | Type 4 | Commercial |
| i-net Opta JDBC | www.inetsoftware.de | Type 4 | Commercial |
| WebLogic jDriver for Microsoft SQL Server | WebLogic | Type 4 | Commercial |
| Atinav aveConnect JDBC | www.atinav.com | Type 4 | Commercial |
| Sun JDBC-ODBC Bridge* | java.sun.com | Type 4 | Free |
* The Sun JBDC-ODBC Bridge driver and the FreeTDS JDBC driver (at least in their current form in April 2001) are NOT recommended for serious J2EE development or deployment
For the remainder of this document, the Sun JDBC-ODBC bridge, the Merant DataDirect Connect JDBC 2.0 Client and the i-net OPTA 2000 JDBC drivers will be used to illustrate the use of MS SQL Server with JBoss.
In the sections that deal with configuration files, the settings for other MS SQL Server JDBC drivers may be shown in addition to those for the JDBC drivers identified above. In this cases, it is assumed that you have installed the drivers and you have ensured the driver files are accessible to JBoss. See below for tips on making JDBC driver files available to JBoss.
The Sun ODBC-JDBC driver is installed automatically during the Java 2 SDK installation process. It is installed as package sun.jdbc.odbc. This package must be in the CLASSPATH for any application that uses the JDBC-ODBC bridge. For this tutorial it will be assumed that the Sun JDBC-ODBC bridge is properly installed on your system.
To use the Sun JDBC-ODBC bridge with JBoss and MS SQL Server you need to create an ODBC datasource that references your MS SQL Server database. For this tutorial it will be assumed that a datasource named jboss_odbc has been created that points to an MS SQL Server database.
According to the blurb on Merant's site:
"Connect JDBC are optimized and reliable Type 4 JDBC drivers for accessing data from Java applets or applications. Connect JDBC supports the latest Sun JDBC specification and the J2EE framework, providing highly scalable data access from heterogeneous platforms, and offering greater flexibility for today's demanding corporate environment."
The Merant DataDirect Connect JDBC driver's .jar files must be accessible to JBoss. There are two ways to accomplish this, you should choose one of the following options:
Run Merant's installation program then add the base.jar, util.jar and sqlserver.jar files to the CLASSPATH. The files are located in the %MERANT_HOME%/lib directory on Windows.
Copy the base.jar, util.jar and sqlserver.jar files from the %MERANT_HOME%/lib directory to the %JBOSS_HOME%/lib/ext directory. This is the recommended option.
According to the blurb on i-net's site:
"i-net software announces the availability of i-net OPTA™ 2000. This version features i-net PLEXA™, a pool manager that comes with i-net OPTA™ free of charge. The easy to use pool manager is a means of seamlessly employing connection pooling with i-net OPTA."
The i-net OPTA 2000 JDBC driver's .jar files must be accessible to JBoss. There are two ways to accomplish this, you should choose one of the following options:
Back to install menu
The instructions for configuring JBoss to use your JDBC driver is different depending on the version of JBoss you have. Please select the appropriate link for your version:
Back to install menu
Now that you have installed a JDBC driver for MS SQL Server, you need to tell JBoss that you would like it to use this driver. Add an entry for the driver to the list of drivers that JBoss loads at startup. This list is stored in the jboss.jcml file. The file can be found in %JBOSS_HOME%/conf/ <config-name>. For a standard distribution of JBoss, <config-name> is default whilst for the JBoss-Jetty it is jetty. The entry should be a single, continuous list of comma-separated JDBC driver names.
Open the file and look for the entry that begins with the following mbean tag:
<mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
The list of drivers is kept in an <attribute> sub-tag of this entry named Drivers. Add your JBDC driver to the list as shown below. When you've finished, keep the file open in your editor.
At this point, you have told JBoss about your driver. You will now set up a connection pool that your EJBeans can connect to. For this tutorial, you will create a connection pool named SQLServerPool. To create the connection pool, locate the mbean entry that begins with the following line in the jboss.jcml file (you should have kept it open as advised in the last step):
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=DefaultDS">
Add the mbean entry given below to the file just below the mbean entry you have just located above. This should be added after the closing </mbean> tag!!.
Connection pool MBEAN declaration for Sun JDBC-ODBC bridge JDBC driver
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=SQLServerPool"> <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute> <attribute name="PoolName">SQLServerPool</attribute> <attribute name="URL">jdbc:odbc:jboss_odbc</attribute> <attribute name="JDBCUser">dbusername</attribute> <attribute name="Password">dbpassword</attribute> </mbean>
Connection pool MBEAN declaration for Merant DataDirect Connect JDBC driver
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=SQLServerPool"> <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute> <attribute name="PoolName">SQLServerPool</attribute> <attribute name="URL">jdbc:sqlserver://servername:1433</attribute> <attribute name="Properties">DatabaseName=DatabaseName</attribute> <attribute name="JDBCUser">dbusername</attribute> <attribute name="Password">dbpassword</attribute> <attribute name="MinSize">0</attribute> <attribute name="MaxSize">10</attribute> <attribute name="GCEnabled">false</attribute> <attribute name="GCMinIdleTime">1200000</attribute> <attribute name="GCInterval">120000</attribute> <attribute name="InvalidateOnError">false</attribute> <attribute name="TimestampUsed">false</attribute> <attribute name="Blocking">true</attribute> <attribute name="LoggingEnabled">false</attribute> <attribute name="IdleTimeoutEnabled">false</attribute> <attribute name="IdleTimeout">1800000</attribute> <attribute name="MaxIdleTimeoutPercent">1.0</attribute> </mbean>
Connection pool MBEAN declaration for i-net OPTA 2000 JDBC driver
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=SQLServerPool"> <attribute name="DataSourceClass">com.inet.tds.XDataSource</attribute> <attribute name="PoolName">SQLServerPool</attribute> <attribute name="Properties">host=ServerName; database=DatabaseName</attribute> <attribute name="JDBCUser">dbusername</attribute> <attribute name="Password">dbpassword</attribute> <attribute name="MinSize">0</attribute> <attribute name="MaxSize">10</attribute> <attribute name="GCEnabled">false</attribute> <attribute name="GCMinIdleTime">1200000</attribute> <attribute name="GCInterval">120000</attribute> <attribute name="InvalidateOnError">false</attribute> <attribute name="TimestampUsed">false</attribute> <attribute name="Blocking">true</attribute> <attribute name="LoggingEnabled">false</attribute> <attribute name="IdleTimeoutEnabled">false</attribute> <attribute name="IdleTimeout">1800000</attribute> <attribute name="MaxIdleTimeoutPercent">1.0</attribute> </mbean>
Connection pool MBEAN declaration for WebLogic jDriver for Microsoft SQL Server
<mbean code="org.jboss.jdbc.XADataSourceLoader" name="DefaultDomain:service=XADataSource,name=SQLServerPool"> <attribute name="DataSourceClass">org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl</attribute> <attribute name="PoolName">SQLServerPool</attribute> <attribute name="URL">jdbc:weblogic:mssqlserver4:DatabaseName@ServerName:1433</attribute> <attribute name="Properties">user=dbusername; password=dbpassword</attribute> <attribute name="MinSize">4</attribute> <attribute name="MaxSize">10</attribute> <attribute name="GCEnabled">false</attribute> <attribute name="GCMinIdleTime">1200000</attribute> <attribute name="GCInterval">120000</attribute> <attribute name="InvalidateOnError">false</attribute> <attribute name="TimestampUsed">false</attribute> <attribute name="Blocking">true</attribute> <attribute name="LoggingEnabled">false</attribute> <attribute name="IdleTimeoutEnabled">false</attribute> <attribute name="IdleTimeout">1800000</attribute> <attribute name="MaxIdleTimeoutPercent">1.0</attribute> </mbean>
Start JBoss and ensure that the JDBC driver is found and the connection pool is created. The relevant out put is shown below for successful JBDC driver load.
[Transaction manager] Initializing [Transaction manager] Loaded JDBC-driver:org.hsql.jdbcDriver [Transaction manager] Loaded JDBC-driver:org.enhydra.instantdb.jdbc.idbDriver [Transaction manager] Loaded JDBC-driver:sun.jdbc.odbc.JdbcOdbcDriver [Transaction manager] Loaded JDBC-driver:com.merant.datadirect.jdbc.sqlserver.SQLServerDriver [Transaction manager] Loaded JDBC-driver:com.inet.tds.TdsDriver [Transaction manager] Loaded JDBC-driver:weblogic.jdbc.mssqlserver4.Driver [Transaction manager] Initialized
And a litte later...the output for the connection pool. Stop JBoss afterwards.
[SQLServerPool] Starting [SQLServerPool] XA Connection pool SQLServerPool bound to java:/SQLServerPool [SQLServerPool] Started
Back to install menu
Now that you have installed a JDBC driver for MS SQL Server, you need to tell JBoss that you would like it to use this driver. Do this by adding an entry for the driver to the list of JDBC drivers that JBoss loads at startup. This list is stored in the jbdc.drivers entry in the jboss.properties file. The jboss.properties file can be found in %JBOSS_HOME%/conf/ <config-name>. For a standard distribution of JBoss, <config-name> is default whilst for the JBoss-Jetty it is jetty. The jbdc.drivers entry should be a single, continuous string of JDBC driver names separated with a comma.
Start JBoss and ensure that the JDBC driver is found (the relevant output would be similar to the output shown below). Stop JBoss afterwards.
Example 11.1. JBoss startup output showing loaded JDBC drivers
[JDBC] Loaded JDBC-driver:org.hsql.jdbcDriver [JDBC] Loaded JDBC-driver:org.enhydra.instantdb.jdbc.idbDriver [JDBC] Loaded JDBC-driver:sun.jdbc.odbc.JdbcOdbcDriver [JDBC] Loaded JDBC-driver:com.merant.datadirect.jdbc.sqlserver.SQLServerDriver [JDBC] Loaded JDBC-driver:com.inet.tds.TdsDriver
At this point, you know that the driver is correctly installed. You will now set up a connection pool that your EJBeans can connect to. Add an MLET entry into the jboss.conf file as shown below. The file can be found in %JBOSS_HOME%/conf/ <config-name>.
For this tutorial, you will create a connection pool named SQLServerPool as specified in the first argument in the MLET entry. The second argument in the MLET entry should be your driver's XADataSource classname. If your driver doesn't support XADataSource use org.jboss.minerva.xa.XADataSourceImpl instead.
Before you can use your spanking new connection pool, you need to tell it how to find your database server (you can also take this chance to specifiy other configuration parameters for the pool like LoggingEnabled). Tell your pool how to connect to your database server by adding an MBEAN entry into the jboss.jcml file as shown below. The file can be found in %JBOSS_HOME%/conf/ <config-name>.
Connection pool MBEAN declaration for Sun JDBC-ODBC bridge JDBC driver
Connection pool MBEAN declaration for Merant DataDirect Connect JDBC driver
<mbean name="DefaultDomain:service=XADataSource,name=SQLServerPool"> <attribute name="URL">jdbc:sqlserver://servername:1433</attribute> <attribute name="Properties">DatabaseName=DatabaseName</attribute> <attribute name="JDBCUser">dbusername</attribute> <attribute name="Password">dbpassword</attribute> <attribute name="MinSize">0</attribute> <attribute name="MaxSize">10</attribute> <attribute name="GCEnabled">false</attribute> <attribute name="GCMinIdleTime">1200000</attribute> <attribute name="GCInterval">120000</attribute> <attribute name="InvalidateOnError">false</attribute> <attribute name="TimestampUsed">false</attribute> <attribute name="Blocking">true</attribute> <attribute name="LoggingEnabled">false</attribute> <attribute name="IdleTimeoutEnabled">false</attribute> <attribute name="IdleTimeout">1800000</attribute> <attribute name="MaxIdleTimeoutPercent">1.0</attribute> </mbean>
Connection pool MBEAN declaration for i-net OPTA 2000 JDBC driver
<mbean name="DefaultDomain:service=XADataSource,name=SQLServerPool"> <attribute name="Properties">host=ServerName; database=DatabaseName</attribute> <attribute name="JDBCUser">dbusername</attribute> <attribute name="Password">dbpassword</attribute> <attribute name="MinSize">0</attribute> <attribute name="MaxSize">10</attribute> <attribute name="GCEnabled">false</attribute> <attribute name="GCMinIdleTime">1200000</attribute> <attribute name="GCInterval">120000</attribute> <attribute name="InvalidateOnError">false</attribute> <attribute name="TimestampUsed">false</attribute> <attribute name="Blocking">true</attribute> <attribute name="LoggingEnabled">false</attribute> <attribute name="IdleTimeoutEnabled">false</attribute> <attribute name="IdleTimeout">1800000</attribute> <attribute name="MaxIdleTimeoutPercent">1.0</attribute> </mbean>
Start JBoss and ensure that the JDBC driver is found (the relevant output would be similar to the output shown below). Stop JBoss afterwards.
[SQLServerPool] Starting [SQLServerPool] XA Connection pool SQLServerPool bound to java:/SQLServerPool [SQLServerPool] Started
Back to install menu
To configure JBoss so that it uses your MS SQL Server connection pool as the default data source, you can choose to do one of the following:
Create a jaws.xml file in the META-INF directory of your application to override the default settings in standardjaws.xml as shown below or...
Example 11.2. jaws.xml - CMP datatype mappings for MS SQL Server
<?xml version="1.0" encoding="UTF-8"?>
<jaws>
<datasource>java:/SQLServerPool</datasource>
<type-mapping>MS SQLSERVER</type-mapping>
<default-entity>
<remove-table>false</remove-table>
</default-entity>
<type-mappings>
<type-mapping>
<name>MS SQLSERVER</name>
<!-- COPY "MS SQLSERVER" MAPPINGS FROM standardjaws.xml HERE -->
</type-mapping>
</type-mappings>
</jaws>
Change the default settings in the standardjaws.xml file in %JBOSS_HOME%/conf directly. This is useful in environments like mine where all unused applications and files - such as HypersonicSQL & InstantDB - are removed from the servers.
To change your default datasource and datatype mapping, make the following highlighted changes to your standardjaws.xml file.
For more information see:
Chapter 5., Customizing JAWS for further information on configuring JAWS (aka JBossCMP).
Chapter 4., Using container-managed persistence for useful information on CMP development & deployment with JBoss.
Error message shown on start-up:
This error can occur in the following situations:
the spelling of a JDBC driver name in the jboss.properties configuration file (or jboss.jcml for JBoss 2.1 and later) is incorrect. In the example above it should have been sun.jdbc.odbc.JdbcOdbcDriver.
a JDBC driver has not been properly installed - it's class files or .jar file(s) are not in the CLASSPATH
When JBoss is started, the sequence of messages that is shown on the console stops midway. The last displayed message refers to a connection pool as shown below.
[Hypersonic] Press [Ctrl]+[C] to abort [InstantDB] Started [DefaultDS] Starting [DefaultDS] XA Connection pool DefaultDS bound to java:/DefaultDS [DefaultDS] Started [SQLServerPool] Starting [SQLServerPool] XA Connection pool SQLServerPool bound to java:/SQLServerPool
Check that your JDBC driver is loaded correctly. If not then see the section called “JDBC driver not loaded error” for possible clues. If your JDBC driver is loading correctly then this error indicates that JBoss is unable to communicate with your database server.
It can occur in the following situations:
Back to install menu
 | |