Writing to a Relational Database

The relational database is perhaps one of the most important tools ever devised in the information technology field. Although some people might not buy into this statement completely, and there certainly are many other important candidates, the relational database has had a huge impact on the IT industry. In fact, the relational database remains a powerhouse despite the fact that other technologies may well be technologically better.

The reason for this is that relational databases are the database of choice for most business today. From Oracle to SQLServer in the large applications, to Microsoft Access in small to medium applications, relational databases are everywhere.

Although relational databases are a wonderful technology, they provide a bit of a problem when it comes to interfacing with objects. Just as with the issue of writing to a flat file, taking an object that's composed of other objects and writing it to relational databases, which are not designed in an object-oriented manner, can be problematic .

Relational databases are built on the concept of tables. Figure 11.4 shows a typical Microsoft Access table relationship. This relational model is so widespread that many people intuitively think of all data models in this way. However, the object-oriented model is not table-driven. Figure 11.4 shows the familiar Northwind relational database model that ships with Microsoft Access.

Figure 11.4. A relational model.

Because objects do not map conveniently to tables, object-oriented database systems were developed in the mid-to-late 1990s. An interesting bit of history is that although these databases represented the object-oriented model well, and might even have performed better, there was one major problem: legacy data.

Legacy Data

Legacy data is the decades of data that are stored in various storage devices. In this chapter, we consider legacy data to be the decades of data stored in relational databases.

Because most companies use relational databases, most of today's business data is stored in relational databases. This means that there is a huge investment made in these relational databases. And there is one more issue involved when it comes to these systems ”they work. Even though object databases might perform better when writing objects to a database, the cost of converting all the relational data to object data is unacceptable. In short, to use an object database, a company would have to convert all of its data from a relational database to an object database. This has many drawbacks.

First, anyone who has performed the conversion of data from one database to another knows that this is a very painful process. Second, even if the data converts successfully, there is no way to know how the change of database tools will affect the application code. Third, when problems occur (and they almost always do), it's difficult to determine whether the problem is with the database or the application code. It can be a nightmare. Most company decision makers were not willing to take these chances . Thus, object databases were relegated to totally new systems written with object-oriented code.

However, we still have the following problem: We want to write object-oriented applications, but we need to access the legacy data in the relational databases. This is where object-to-relational mapping comes in.

Accessing a Relational Database

All databases applications have the following structure:

• Database client

• Database server

• Database

The database client is the user application that provides the interface to the system. Often it is a GUI application that allows users to query and update the database.

SQL

SQL stands for Structured Query Language. It is a standard way for database clients to communicate with varied vendor database systems that implement this standard.

The database client will communicate with the database server via SQL statements. Figure 11.5 displays a general solution to the database client/server model.

Figure 11.5. Database client server model.

As an example, let's use Java to communicate to a Microsoft Access database, which is a relational database. Java uses JDBC to communicate with database servers.

JDBC

Officially, Sun does not maintain JDBC as an acronym. In the industry it is known as Java Database Connectivity.

Part of the problem with database drivers is that they tend to be vendor-specific. This is a common problem with any type of driver. As you probably know, when you purchase a new printer, the printer comes with a driver that's specific to that printer, and you might even have to download specific updates for that driver. Software products have similar issues. Each vendor has a specific protocol for communicating with its product. This solution might work well if you stay tied to a specific vendor. However, if you want to have the option to change vendors , you might be in trouble.

Microsoft has produced a standard called Open Database Connectivity (ODBC). According to Jamie Jaworski in Java 2 Platform Unleashed , "ODBC drivers abstract away vendor-specific protocols, providing a common application-programming interface to database clients. By writing your database clients to the ODBC API, you enable your programs to access more database servers." Take a look at Figure 11.6. This figure illustrates how ODBC fits into the picture.

Figure 11.6. Database client server model using ODBC.

Again we see the words abstract and interface in a definition of a software API. By using ODBC, we can write applications to a specific standard, and we do not need to know the implementation. Theoretically, we can write code to the ODBC standard and not care whether the database implementation is a Microsoft Access database or an Oracle database.

As we see in Figure 11.5, the client uses the driver to send SQL statements to the database servers. Java uses JDBC to communicate with the database servers. JDBC can work in a few different ways. First, some JDBC drivers can connect directly to the database servers. Others actually use ODBC as a connection to the database servers, as in Figure 11.7. Depending on how you decide to write your applications, you might need to download various drivers and servers. This discussion is well beyond the scope of this book, because here we are concerned mainly with the general concepts. For more detailed information on how to actually set up an actual database, and how to connect to it with your applications, please refer to more advanced books such as Java 2 Platform Unleashed ”it is not a trivial endeavor.

Figure 11.7. Database client server model using ODBC/JDBC.

The JDBC API provides the interface between the application program and the database. These interfaces are found in the Java package called java.sql . The API includes the following:

• DriverManager

• Connection

• Statement

• ResultSet

Let's explore these topics one at a time in the following sections.