Writing to a Relational Database

The relational database is perhaps one of the most successful 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 businesses 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 may be 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 12.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 12.4 shows the familiar Northwind relational database model that ships with Microsoft Access.

Figure 12.4. A relational model.

Because objects do not map conveniently to tables, object-oriented database systems were developed in the 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.

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 one more issue is 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 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.

The database client communicates with the database server via SQL statements. Figure 12.5 displays a general solution to the database client/server model.

Figure 12.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 (Java Database Connectivity) to communicate with database servers.

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 continue to use a specific vendor. However, if you want to maintain the option of changing 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 12.6. This figure illustrates how ODBC fits into the picture.

Figure 12.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—theoretically, at least.

As we see in Figure 12.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 various ways. First, some JDBC drivers can connect directly to the database servers. Others use ODBC as a connection to the database servers, as in Figure 12.7. Depending on how you decide to write your applications, you might need to download various drivers and servers. These specifics are well beyond the scope of this book because here we are concerned mainly with the general concepts. For more detailed information on how to set up a database and how to connect to it with your applications, refer to more advanced books, such as Java 2 Platform Unleashed—it is not a trivial endeavor.

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