Special Reports

Create a .NET-J2EE Shared Database

Create a shared database to provide interoperability between .NET and J2EE platforms, then extend it with a notification level between platforms.

• By Simon Guest
• 04/01/2004

Note: This article is excerpted from Chapter 7, "Creating a Shared Database," of Microsoft .NET and J2EE Interoperability Toolkit [Microsoft Press, 2003, ISBN: 0735619220]. It has been edited for length and format to fit the magazine. You can read a PDF of the full chapter here.

You can achieve interoperability between the Microsoft .NET Framework and Java 2 Enterprise Edition (J2EE) simply and effectively with a shared database. Database connectivity lies at the core of both .NET and J2EE, allowing you to exploit the data access drivers available for each and whip out applications that share fields, records, and tables among both platforms.

It's called business tier resource sharing. In previous projects I've worked on, certain functionality has been added to an existing application where I had to keep the same data but use technology from a different platform. For example, you might build a reporting application in which you write the reporting software for a different platform than the original application.

Here I'll show you how to create data access in the context of both .NET and J2EE and explore two connectivity options. To enable this connectivity, I'll access Microsoft SQL Server 2000 with Service Pack 3 from J2EE by using JDBC, and from .NET by using Microsoft ADO.NET.

Database connectivity APIs are core packages for both .NET and J2EE. The J2EE 1.3 specification promotes the use of JDBC to provide database access, while .NET uses ADO.NET. I'll discuss the connection and sharing of data based on Microsoft SQL Server, but you can get both JDBC and ADO.NET drivers for a variety of database providers. The net result is open data access.

I'll also tell you what you need to know about JDBC and ADO.NET in the context of using a shared database. Finally, I'll provide a data access pattern you can apply to both .NET and J2EE, and I'll cover the advantages of doing so.

JDBC is an API that enables Java apps to access fields, records, tables, and stored procedures from any database for which a JDBC driver exists. JDBC 3.0 is in its final release, but I'll use JDBC 2.0 calls here for maximum compatibility. Many vendors have produced JDBC-compatible drivers for popular databases including Microsoft SQL Server, IBM DB2, and many versions of Oracle.

ADO.NET marks the next stage in the evolution of Microsoft's data access strategy—with an emphasis on providing database access and connectivity for the .NET Framework. In addition to simple database connectivity, ADO.NET has a strong relationship with XML (to enable persistence and transport) and introduces the concept of a DataSet, allowing a disconnected view of data.

ADO.NET connects to a database through managed providers—database drivers that expose APIs with classes based on managed code, which is Microsoft Intermediate Language (MSIL) under the covers. You can get managed providers for SQL Server, Oracle 8i, and DB2. You can access other databases by using an ADO.NET managed provider to access drivers based on either OLE DB or ODBC.

Create a Database Table
I'll show you how to connect to a shared database using a table, ORDERTBL, that simulates incoming orders for a stock-trading scenario (see Table 1 for the schema). You create this shared database and table on a local example of SQL Server by running the SQL script (dbscript.sql) located in the downloadable code. With Microsoft SQL Server 2000 installed, process the script by loading and executing this file within the Query Analyzer, which you can access from the Microsoft SQL Server program group.

During SQL Server installation, be sure to select the "SQL Server And Windows" authentication mode in the SQL Server instance's Properties dialog box. If you set the authentication mode to "Windows Only," a JDBC app won't be able to connect to the database, because the Java Virtual Machine (JVM) requires access to the Windows authentication credentials stored on the machine.

Running this script creates a new database, SharedDB, with a table, ORDERTBL. The script also creates a test user account, testuser. Give this account a default password, StrongPassword, and ownership rights of the SharedDB database.

Next, use JDBC to enable accessing the same SQL Server database from a Java application. You can choose between a number of JDBC drivers. Some are free, some are commercial, and some have even been packaged with a number of J2EE application servers. You can get Microsoft's JDBC driver offering as a free download through the Microsoft Developer Network (MSDN). It's a Type 4 JDBC driver.

JDBC drivers come in four types. Type 1 and Type 2 drivers require you to install native code on client machines. Type 3 drivers don't require native code but do require additional security to work over the Internet. Use Type 4 drivers if you can because they use the native protocol to access the database without requiring any additional installation.

Download and install the Microsoft SQL Server 2000 Driver for JDBC (Service Pack 1) by going to http://msdn.microsoft.com/downloads/list/sqlserver.asp and selecting the appropriate product. A simple SETUP.exe program installs the driver on computers using the Microsoft Windows operating system. A TAR package is also available for running it on UNIX systems.

Once the installation has completed, you can find three Java Archive (JAR) files in the C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib directory. These JAR files contain the libraries JDBC needs to connect to an instance of Microsoft SQL Server 2000. You can see the JDBC driver in action by looking at the sample in the C:\Interoperability\Samples\Resource\Database\Simple\JDBC directory. Examine how the driver works by stepping through this sample (Client.java). You need to import classes from the existing java.sql.* packages because the driver is based on JDBC:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

Load With Class
After the declaration for your main class, use the class loader to load the SQL Server 2000 JDBC driver. This ensures that the driver is loaded for these JDBC calls:

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

If you plan to use this sample code with another JDBC driver and/or database, reference the driver you want to use in this line. Once the class is loaded, build a connection string to specify how to connect to your database. This connection string determines the parameters to use to find and connect to the instance of SQL Server. Here's the format of a default connection string:

jdbc:microsoft:sqlserver://server:port;DatabaseName=db

The server referenced in the string is the machine name where SQL is installed. port is the IP port that the driver should use to connect with SQL Server. This port should be 1433 if you accepted the defaults during the SQL Server installation. db is the name of the database to access. The username and password needed to access the server and database are supplied separately when the database connection is created.

This sample uses a connection string that connects to a local instance of SQL Server 2000, with a user named testuser and the password StrongPassword. Use the DriverManager.getConnection method to create the connection:

Connection conn = DriverManager.getConnection ("jdbc:microsoft:"
    +"sqlserver://localhost:1433;DatabaseName=SharedDB",
     "testuser","StrongPassword");

Issue a SELECT statement to the SQL Server instance after you establish connection to the database:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FR OM ORDERTBL");

This query requests all records from the order table, then executes and returns an object of type java.sql.ResultSet. You can then iterate through the ResultSet to display the data from the query:

while (rs.next())
{
System.out.println(rs.getString("ID")
	+"\t"+rs.getString("TYPE")
	+"\t"+rs.getString("ACCOUNT_ID")
	+"\t"+rs.getString("TICKER")
	+"\t"+rs.getString("QTY")
	+"\t"+rs.getString("PRICE"));
}

This query uses the ResultSet code's getString method to display the values for each of the six columns defined within the table. I'll perform more complex calls later on. Complete this JDBC sample by wrapping the code in a try?catch block. Some of the JDBC calls throw explicit exceptions that you catch by trapping for a general exception.

Build and run the code by entering ant run at a command prompt in the Simple\JDBC directory. My sample accesses the Microsoft SQL Server 2000 Driver for JDBC at run time. The build script assumes the libraries (JAR files) are located in the default installation directory (C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib). If this isn't the case, you should modify the C:\Interoperability\Samples\Config\sqljdbc.properties file to reflect the correct directory.

A list of records will be returned upon calling the database successfully:

1000	P	47238	CONT	400	12.77 00
1001	P	93083	CONT	200	12.78 00
1002	S	39223	NWND	1000	50.12 00
1003	P	29080	WOOD	150	14.45 00
1004	S	37973	FTHC	10000	3.450 0
1005	P	32279	WOOD	800	14.79 00
1006	P	38084	LUCP	5000	46.44 00
1007	S	39397	COHO	200	21.60 00
1008	S	71129	FABR	950	15.11 00
1009	P	38293	TRRE	250	9.350 0
1010	P	38293	COHO	250	21.58 00

Connect to SQL Server Using ADO.NET
Now I'll show you how to access the same database with the same functionality and comparable code using ADO.NET. The best way to show simple database access using ADO.NET is with a sample, as I did with JDBC. I'll use code similar to the previous sample, only using ADO.NET rather than JDBC. Start with the sample code in the download.

Most of the APIs required to access the SQL Managed Provider for ADO.NET reside within the System.Data.Sql namespace. This namespace is imported at the start of the sample:

using System.Data.SqlClient;

Define the connection to the database using the SqlConnection class. Build a connection string including the machine, username, password, and database information. This resembles the JDBC connection string I built except it uses different parameters. Open the connection with the Open method:

SqlConnection conn 
    = new SqlConnection("Data Source=localhost; User ID=testuser; "
        + "Password=StrongPassword; Initial Catalog=Sha redDB");
conn.Open();

Issue a command to the database by constructing a SqlCommand containing the select statement you want to execute. The results from the select statement return as a SqlDataReader object:

SqlCommand cmd = new SqlCommand("SELECT * FRO M ORDERTBL", conn);
SqlDataReader reader = cmd.ExecuteReader();

Extract fields from this reader as you did with the Java ResultSet: Index them with the GetValue method. This returns the values in a format that lets you write them to the console:

while (reader.Read())
{
Console.WriteLine(reader.GetValue(0)+"\t"
	+reader.GetValue(1)+"\t"
	+reader.GetValue(2)+"\t"
	+reader.GetValue(3)+"\t"
	+reader.GetValue(4)+"\t"
	+reader.GetValue(5));
}

As you work with these code samples, you'll see how to use the actual types of the fields instead of getting a default value from them.

Build and run the ADO.NET code by entering nant run at a command prompt in the Simple\ADO.NET directory. Calling the database returns a list of records matching the ones you observed with the JDBC sample.

So far, you've seen how you can use JDBC and ADO.NET to perform simple database access from Java and .NET respectively. From here, it should be clear how both drivers could point to the same instance of a database and be used to insert, update, and delete records, enabling data to be shared between the two platforms (see Figure 1).

I'll show you a particular implementation of this by working with a design pattern known as Data Access Objects (DAO), also known as Data Access Components (DAC) or Data Access Layer Components (DALC). You can learn more in Design Patterns by Erich Gamma, et al (see Additional Resources).

You use the DAO in its simplest form to separate database access code like the samples I've been using from business logic. The DAO resembles a pattern known as the agent-service pattern, except you abstract code that makes direct calls to the database instead of abstracting a Web service proxy. The DAO layer resides directly between the database layer and the business logic layer.

The DAO pattern works well in a shared database environment, where it provides CRUD-type functionality for abstracting a database from business logic. CRUD stands for Creating new database records, Reading them (returning them as data types used by the business logic), and Updating them (using updated data types used by the business logic).

Delete Database Records
You can create and read these records using both Java and .NET. You can also update and delete them. Look up the DAO sample code in the download. There you'll find both ADO.NET and JDBC versions. Each defines the data type StockRecord, which contains elements that match the fields of the order table, and each defines a class for the DAO itself, StockRecordDAO. The StockRecordDAO class maintains the connection to the database and exposes the database connection through a series of methods to perform the creates and updates. These methods include:

GetStockRecord(long ID)
GetStockRecords(long accountID)
GetStockRecords(String type, long accountID)
GetStockRecords(String type, String ticker)
GetStockRecords(long accountID, String 
	ticker)
GetStockRecords(String type, long accountID, 
	String ticker)
GetAllStockRecords()
AddStockRecord(StockRecord record)

When you invoke a method, the DAO constructs the correct SELECT statement, executes the query against the database, and returns the result either as a StockRecord (for single records returned) or an ArrayList containing one or more StockRecords.

Compile both the ADO.NET and JDBC samples, using either NAnt or Ant, depending on your programming language. The DAO is invoked by a Client class that simulates some simple business logic to read and add a record from the database:

Reading a single record with ID=1000:  CONT
There are 2 records with ID = 38293
Adding a record...

Whenever you run the sample, it displays the ticker of an individual record and adds a new record to the table. By running both samples multiple times, you can observe how both ADO.NET and JDBC create new records.

The .NET client code closely resembles Java's, but the DAO layer abstracts the distinct platform-specific code (the database access). When you develop on both platforms, this abstraction delivers one of the benefits for using a consistent DAO. Your direct database calls will be the same whether or not you use a pattern. So developing data access based on a shared DAO gives you a common frame of reference for accessing data on either platform. If you were to follow a similar pattern to the one shown in the previous sample, writing business logic for either .NET or J2EE would allow for a consistent feel regardless of the platform used to access the data.

The DAO pattern also lets you provide additional logic and functionality for accessing databases. DAO could include code providing a level of caching between the business logic and the database. You could return recently accessed records from a cache to increase performance, add security management (authentication and authorization) for connecting to the database, handle transactional and locking issues, and provide data paging to return pages (or blocks) of data for large data sets.

Based on this list, a DAO pattern used by both .NET and J2EE allows for other notification options in a shared database. Imagine a configuration in which business components from both .NET and J2EE access a shared database using the DAO pattern. You might want to find out when either of the platforms has updated a particular record in a database. For example, if a component on one of the platforms has made an update, a component on the other platform might want to reload that data to either perform some function or keep the cache current.

Using this DAO pattern, you could extend the DAO layer on both .NET and Java to provide notifications from either platform to the other. For example, you could use either .NET Remoting or Web services to pass an update message between the two DAO layers. Doing this can eliminate the need for each of the platforms to constantly "poll" records in the database for changes—which can be common in situations where you need a shared database setup.

Extend DAO to Include Updates
You can extend DAO to include update operations. Locate the sample in the download directory. It shows a small .NET WinForms-based app used to monitor the database's order table. The app lets users view current records populated in this table and uses methods exposed from the same DAO pattern I've discussed already. The second half of the sample shows a Java client that also accesses the database using the DAO pattern and adds a new record when run.

Perform the update operation using .NET Remoting between the Java client and the WinForms app, which exposes a server-side component you can call with .NET Remoting. The Java DAO adds a new record to the database, then uses a .NET Remoting call to tell the WinForms app to refresh its view of the database (see Figure 2).

The exposed server-side component of the WinForms app is attached to an event in code. When the Java DAO makes an incoming call, the event triggers a refresh screen.

To see this in action, compile both the ADO.NET and JDBC portions of the UpdateDAO sample code. The JDBC code uses Intrinsyc Ja.NET to perform .NET Remoting. All the required proxies have been generated as part of the sample, but you need to configure the licensing using the Janetor tool.

Use nant run to compile and run the WinForms code within the ADO.NET sample directory. The form displayed shows a simple summary of each record within the order table. Scroll through the records, using the navigation control to view a particular record number. Now switch to the JDBC sample in the UpdateDAO directory, and run the Client app, which uses the DAO layer to add a new record to the database, indicated by this output:

Adding a record...

Once this operation is complete, switch back to the WinForms app. You'll see that the number of records has been incremented automatically. Navigate to the last record for the details of the record added by the Java DAO layer. Also, this line can be uncommented in the SummaryForm.cs class:

MessageBox.Show("The Java client has added a record to the order 
    +" table.  Click on OK to refresh.");

This code displays a message each time the Java DAO layer updates the database.

The WinForms app exposes a NotificationRemoting object that lets the Java DAO layer inform the app of a database update. This object calls a NotificationFactory when it's called, obtaining a singleton reference to a notification object. In .NET Remoting terms, a singleton is a static instance of an object that can be shared across processes. Both the Java and ADO.NET DAO layer require the same reference of a notification object; a singleton provides both with a single point for sharing update events.

The notification object creates a new event when it's called. The ADO.NET DAO layer handles this, and in turn reexposes a new event that the app's business layer can consume (see Figure 3). The DAO layer abstracts all the notification and access logic, so the business logic code simply has to take action on the update event. The WinForms app includes a line to register the event handler from the DAO layer:

dao.Updated += new UpdateEventHandler(
	dao_Updated);

The dao_Updated method contains the code that updates the screen.

You can extend the concept of this notification process by imagining how the DAO layer could pass additional information with the update to further optimize the sample. For example, the DAO layer could pass information about which record the update affected. In general, you can expand the interoperability techniques I've discussed to include the more advanced features of database connectivity for both platforms.

• Code Download