Linking through a Shared Database

You may have the situation where you have two applications, on different platforms, that you want to share the same underlying database. This configuration addresses the issue of maintaining multiple data stores. Sharing a database between .NET Framework and J2EE applications is a simple and effective means of implementing interoperability between the two environments.

Figure 5.1: .NET Framework and J2EE applications sharing a common database

The technologies that enable you to do this are not new. Both platforms have had mechanisms to implement database connectivity since they started. The existence of database drivers makes it easy for you to link Business tier applications to a common back-end storage and share tables, records, and fields between platforms.

Both platforms provide links into multiple databases, such as Microsoft SQL Server™, Oracle, Informix, MySQL and DB2. This section discusses the ways that both .NET Framework and Java applications connect to databases, as well as some best practices for sharing databases.

Before you can learn how to share databases, you need to appreciate how each platform connects to a database. Each platform has a built-in data access API:

• ADO.NET in the .NET Framework.

• JDBC in Java.

For the majority of implementations, your choice of database and platform should not be a factor.

Note

Although the samples in this section refer to SQL Server 2000, the strategies shown apply to all databases.

Connecting with JDBC

JDBC is the API that enables J2EE applications to access tables, records, fields and stored procedures in any compatible database. You can use JDBC to connect to almost any tabular data, including spreadsheets and information in flat file format.

JDBC 3.0 represents the most recent release of the specification, which a variety of vendors now endorse, including IBM, Hewlett-Packard, BEA, Simba, and Oracle. Sun has recently filed JSR221, which covers the JDBC 4.0 API specification.

Note

Java 2 Platform, Standard Edition (J2SE) 1.4 includes complete support for JDBC 3.0, located in the Java.sql and Javax.sql packages. The classes and interfaces in these packages allow Java applications to access any database for which they have a JDBC database driver.

There are four types of JDBC database drivers:

• Type 1 (JDBC to ODBC bridge with ODBC driver) — Type 1 drivers provide JDBC access through ODBC drivers. Sun supplies a JDBC to ODBC bridge driver in the J2SE that you can use if no other driver is available. Type 1 drivers require native code installation on the client computer.

• Type 2 (Native API with Java technology driver) — Type 2 drivers convert JDBC calls into calls to the client API. As with Type 1 drivers, this implementation requires native code installation on the client computer.

• Type 3 (Pure Java driver for database middleware) — Type 3 drivers translate the JDBC calls into a middleware vendor’s protocol. The middleware then translates this protocol into DBMS calls. You do not have to install any native code on the client computer, but you must specify security configuration settings for Internet operation.

• Type 4 (Pure Java direct to database driver) — Type 4 drivers translate JDBC calls into DBMS calls directly, and use native protocols to access the database.

  Note

  Type 4 “Pure Java” drivers are the preferred option because they typically offer the best performance.

JDBC uses the concept of ResultSets. A ResultSet is a grouping of all the rows that satisfy the conditions within a SQL statement. You can access the data in the ResultSet using the get method, which enables you to traverse columns in the current row. You can also move to the next row using ResultSet.next.

Applications can execute statements and then process any number of result sets. The statement that generates a ResultSet automatically closes it, either by closing the statement, executing it again, or retrieving the next result from multiple result sequences.

Note

ResultSets are similar to ADO.NET DataSets. However, ResultSets are closer to ADO RecordSets, the pre-.NET database access API objects.

For more information about ResultSets, see “JDBC Guide: Getting Started” on the Java Web site. For more information about using JDBC, see “JDBC Data Access API” on the Java Web site.

Connecting to SQL Server 2000 with JDBC

You can access SQL Server 2000 from Java applications using any one of a range of JDBC drivers, either free or commercial in nature. For example, Microsoft provides the Microsoft SQL Server 2000 Driver for JDBC Service Pack 1. This is a Type 4 JDBC driver that provides highly scalable and reliable connectivity for J2EE applications. This driver provides JDBC access to SQL Server 2000 for any Java-enabled applet, application, or application server.

For more information about the Microsoft SQL Server 2000 Driver for JDBC Service Pack 1, including download information, go to the Microsoft Download Center.

Note

When connecting to SQL Server 2000 using a JDBC driver, ensure the security settings on SQL Server use SQL Server and Windows authentication (mixed mode authentication). Also, ensure that you set a complex (mixed case letters and numbers) password for the sa account.

For more information about connecting to SQL Server 2000 with the Microsoft JDBC driver, see Microsoft Knowledge Base article Q313100, “HOW TO: Get Started with Microsoft JDBC.”

Connecting with ADO.NET

Accessing data in relational databases has always been a feature with Microsoft platforms. Before the arrival of the .NET Framework, ActiveX Data Objects (ADO) API was the primary database access mechanism. The .NET Framework introduced ADO.NET as the new API for connecting to databases on Microsoft platforms.

ADO.NET represents the abstract design concepts that you need to build data access classes within the .NET Framework. ADO.NET lets you work with data irrespective of data source, data format, or physical location. It includes a new object model and promotes new concepts such as the DataSet and the DataReader classes.

ADO.NET improves on ADO by being less database-centric and more aligned with modern Web service based programming. It works well in a distributed environment and enables developers to link to data sources quickly and reliably.

There were several main design goals for ADO.NET:

• Explicit and factored object model — ADO.NET provides a simple to use object model in which developers have complete control over how to control data source connectivity, command execution, and data manipulation.

• Disconnected data cache model — N-tier programming and XML Web service architectures require that applications work in a disconnected, loosely coupled manner. ADO.NET provides a comprehensive caching data model for marshaling data between applications or services and then updating the original data source.

• Common data representation with data combination — ADO.NET gives you the ability to combine data from multiple and varied data sources.

• XML support — XML is a key component in building interoperable applications and more robust data processing models. ADO.NET uses the XML support in the .NET Framework by interacting with XML in either a relational manner or in native XML.

• Use existing ADO knowledge — Although the ADO.NET object model differs from the previous ADO model, the basic constructs remain the same. The ADO.NET object model consists of a provider, connection, and command objects, enabling current ADO developers to migrate easily to ADO.NET.

From a developer’s perspective, ADO.NET represents the concrete implementation of classes inside the .NET Framework that you can use for data access. These classes exist within the System.Data namespace of the .NET Framework.

Note

The ADO.NET classes in System.Data.dll are integrated with the XML classes found in System.Xml.dll. Hence to compile code that uses the System.Data namespace, you should add a reference to both System.Data.dll and System.Xml.dll in your Visual Studio .NET projects.

ADO.NET introduces the concepts of datasets, as provided by the DataSet class. You can compare a dataset to a ResultSet object in JDBC, but datasets provide a disconnected view of the data. You can take datasets offline, modify them, and then update the database with the amended values.

ADO.NET also introduces the concept of data readers, as provided by the SqlDataReader, OleDbDataReader, and OracleDataReader classes. A data reader object retrieves a read-only, forward-only stream of data from a database. You can use the data reader object to increase application performance and reduce system overhead as only one row at a time is held in memory.

To determine whether to use a dataset or data reader when you design your application, consider the level of functionality that you need in the application.

Use a dataset in order to do the following:

• Navigate between multiple discrete tables of results.

• Manipulate data from multiple sources (for example, a mixture of data from more than one database, from an XML file, and from a spreadsheet).

• Exchange data between tiers or using an XML Web service. Unlike data readers, you can pass a dataset to a remote client.

• Reuse the same set of rows to achieve a performance gain through caching (such as when sorting, searching, or filtering the data).

• Perform a large amount of processing per row. Extended processing on each row returned using a data reader ties up the connection serving the data reader longer than necessary, affecting performance.

• Manipulate data using XML operations such as Extensible Style Language Transformations (XSLT transformations) or XPath queries.

Use a data reader in your application for the following reasons:

• You do not need to cache the data.

• You are processing a set of results too large to fit into memory.

• You need to access data quickly and once only, in a forward-only and read-only manner.

ADO.NET connects to a database through managed providers. These are database drivers that expose APIs using classes operating from managed code. You can obtain managed providers for SQL Server, Oracle 8i, MySQL and IBM’s DB2, as well as several other databases.

Alternatively, managed providers can access OLEDB or ODBC-based drivers. OLEDB and ODBC are two older database-independent connection APIs that you can use to connect to any compliant data store. If you use the ADO.NET managed provider for either of these APIs, you can access virtually any database with one set of code; however, you add the overhead of another API layer.

The managed providers for each database (such as the managed provider for SQL Server 7.0 or SQL Server 2000) link directly to the database at the binary level, giving a substantial performance advantage. For this reason, you are recommended to use the managed provider for the database you are connecting to instead of the more generic OLEDB or ODBC-based drivers.

Using the managed provider for a particular database does imply that your application is then tied to that database and switching to another database would require rewriting the database access code. However, it is rare for an organization to change its database, and it is certainly not something that companies do just for fun. Hence the performance benefits are worth the minor inconvenience of dedicated database access code.

Note

There are techniques in this chapter that cover how to enable your application to use different databases without significant code rewriting.

Connecting to Microsoft SQL Server 2000 with ADO.NET

You can access SQL Server 2000 from .NET Framework applications through the SQL Managed Provider for ADO.NET. You can find most of the APIs you need to access the SQL Manager Provider for ADO.NET in the System.Data.SqlClient namespace. This is the namespace that you import at the beginning of your code.

The Connection, Command, Data Reader, and Data Adapter objects provide the core functionality of the ADO.NET data provider model. Each managed provider provides its own implementation of these core objects prefixed with the provider name. For example, the SQL Managed Provider for .NET Framework contains classes such as SqlConnection and SqlCommand. ADO.NET classes prefixed with Sql address the SQL Server managed provider and only work with SQL Server.

Using these SQL Server managed provider classes offers two major advantages over their OLE DB provider counterparts. Firstly, these classes use the native Tabular Data Stream (TDS) interface for maximum performance and the additional interface layers that the OLE DB classes require no longer exist, resulting in faster database access. Secondly, the SQL classes that these controls create have additional methods that take advantage of features specific to SQL Server. This provides you with greater flexibility in design and programming with SQL Server.

In a simple connection scenario, you can use objects in the following manner. First, you define a connection to the database using the SqlConnection class. This class builds a connection string including the computer name, database name, and authentication details, such as user name and password. You then use the Open method to open the connection to the database.

To fetch data from the database, you construct a SqlCommand object that contains the relevant select statement. The ExecuteReader method called on the SqlCommand object returns a SqlDataReader object containing the results from the select statement. Finally, you extract the fields from the reader, index them with the GetValue method and use them however you want within the application. For more information about implementing these commands, see the References section at the end of this chapter.

Sharing Data Between ADO.NET and JDBC

You have seen how both JDBC and ADO.NET provide database connectivity for .NET Framework and J2EE applications to a range of databases. You should now appreciate that both environments can point to a single data source to add, read, update, and delete records. It is now time to look at some best practices for implementing database connectivity.

Abstracting database access code from the rest of your application is best practice in both .NET Framework and J2EE application architectures. For ease of coding and consistency, you should implement a layer that abstracts the database code from the business logic, as Figure 5.2 shows.

Figure 5.2: The Data Access Logic Components abstract the database code from the Business tier

This abstraction pattern has different names depending on the platform. In J2EE, this is the Data Access Object (DAO) pattern. The Application Architecture for .NET: Designing Applications and Services guide refers to this as Data Access Logic Components or simply Data Access Logic. The next section looks at the benefits of implementing a Data Access Logic layer in both .NET Framework and J2EE applications.

Note

This book refers to the Data Access Logic Components pattern for both .NET and Java applications.

Implementing Data Access Logic Components

Regardless of the data store you choose, your application should use Data Access Logic Components to access the database. These components abstract the semantics of the underlying data store and data access technology (such as ADO.NET or JDBC) and provide a simple programmatic interface for retrieving and performing operations on the stored data.

Data access logic components usually implement a stateless design that separates the business processing from the data access logic. Each data access logic component typically provides methods to perform create, read, update, and delete (CRUD) operations on a specific business entity in the application.

For example, in an e-commerce application, you can design a data access logic component for handling all data interactions with the data relating to customer orders. This data access logic component is not necessarily tied to one table in the database; it can access any of the tables that relate to order data. Typically, this data access logic component retrieves complex data types that represent a business entity, in this case, an OrderData object.

For more information about implementing data access logic, see the References section at the end of this chapter.

Taking this concept further, if you have multiple data access logic components, it is a good idea to implement a database helper class to handle common tasks such as database connections, execute commands, and cache parameters, and so on. The data access logic components provide the logic to access specific business data, while the database helper class centralizes data access API code to a specific database. This helps to reduce code duplication.

You have already seen that if you use a managed provider class to communicate with a database, you may need to write code that is specific to that database. When you implement a database helper class, you can keep all database specific code within that class. The database helper class enables you to keep your data access logic components database independent. Changing database types means simply replacing your database helper class with one that can communicate with the new database. Additionally, environments with more than one database type can use multiple database helper classes in order to access data from the different databases.

Microsoft provides the Data Access Application Block (DAAB) for the .NET Framework, which can be used as a database helper for accessing SQL Server. For more information about the DAAB, including download information, see “Data Access Application Block Overview” on MSDN.

Benefits of Implementing Data Access Logic Components

Implementing Data Access Logic Components gives the following interoperability benefits:

• Creates a common approach for accessing data from either environment, enabling developers to write business logic code with a consistent feel regardless of the underlying platform.

• Provides the ability to abstract access to different underlying databases.

• Enables the integration of further logic and processes for accessing databases, such as:

  • Caching for added performance.

  • Authentication and authorization for user connections.

  • Transaction support and locking.

  • Data paging for large results sets.

The common approach benefit opens the door for a further interoperability scenario where the Business tier on one platform can call the Data Access Logic tier of the other platform. This advances the concept of sharing a database between .NET Framework and Java applications by allowing you not only to share the database, but also the data access logic that communicates with the database. The XBikes sample application included in this guide provides the ability to implement this scenario through one of its configuration options.

Figure 5.3: Sharing Data Access Logic Components between .NET Framework and Java applications