Java IoT Authors: Zakia Bouachraoui, Pat Romanski, Elizabeth White, Liz McMillan, Yeshim Deniz

Related Topics: Java IoT

Java IoT: Article

Cross-Database Portabliity with JDBC

Cross-Database Portabliity with JDBC

Java programs can use the JDBC API to access relational databases, thereby cleanly separating the database system from the application. This approach holds the promise of cross-database portability, i.e., "write once, run on any database." In practice, several stumbling blocks stand in the way of fulfilling this promise.

This article shows examples of these stumbling blocks so you can see the common pattern of development. Drawing on this pattern, I'll develop a roadmap you can use to write applications that can work around these stumbling blocks. Finally, I'll demonstrate how the roadmap can be applied to build a portable application.

Breakdown of Portability
Three general situations in which the portability of code breaks down are using SQL escape syntax, translating external data types to database-specific types, and executing positioned updates on the rows of a table. These examples arise out of my experiments with writing JDBC-based applications using an organization schema with two tables, EMP and DEPT. These schemas were implemented in two different database systems, MS Access 97 and Oracle8 Personal Edition, both running on a Windows NT 4.0 workstation. The schemas for the respective database systems are shown in Figure 1. Note that the structure of the EMP and DEPT tables are the same in both databases, but the respective native data types of the columns are different, as shown in the box.

To drive the Access database, I used a Windows-supplied Microsoft Access driver and the JDBC-ODBC bridge from Sun (which came with JDK 1.1) as the JDBC driver of choice. For Oracle I used the JDBC OCI8 driver that supported JDBC 1.22.

Situation 1: SQL Escape Syntax
The JDBC API is implemented in the java.sql package. A java.sql.Statement instance is used to execute simple SQL statements.

JDBC specifies a so-called SQL escape syntax that may be used in Statement instances for various tasks such as pattern matching of strings, executing database-specific scalar functions, manipulating date and time values, calling stored procedures and executing outer joins. For each of these tasks JDBC specifies a syntax that's used by the developer, and the JDBC driver is responsible for translating this into database-specific code. Following is an outer join example that doesn't port.

  • Outer join: Consider the data in the EMP and DEPT tables shown in Figure 2 (only the relevant columns are shown).
The following join operation would produce the result shown in Figure 3:

select dname, ename from dept d, emp e
where d.deptno = e.deptno

Note that the department OPERATIONS (DEPTNO = 40) doesn't appear in the resulting table because none of the entries in the EMP table has a DEPTNO value of 40. But what if you want to see every department represented in the result, even if it doesn't have any entries in EMP? You need to execute an outer join.

An outer join preserves unmatched rows in either the left table (left outer join) or the right table (right outer join). JDBC prescribes a SQL escape syntax for outer joins that looks like this:

{ oj outer-join}

where the keyword oj stands for outer join, and outer-join is of the form:

table left outer join { table | outer-join} on search-condition

If you want to preserve all the unmatched departments in the example, you can issue a left outer join by specifying the tables in order DEPT followed by EMP:

select dname, ename from
{ oj dept d left outer join emp e on d.deptno = e.deptno }

This works perfectly well with Access, but not with Oracle. The OCI8 driver doesn't support the oj escape syntax. Instead, you need to use the following equivalent SQL statement:

select dname, ename from dept d, emp e
where d.deptno = e..deptno (+)

in which the (+) at the end of the statement stands for outer join.

The result of this outer join is the table in Figure 4.

Situation 2: DataType Translation
A large part of the work in making JDBC applications portable involves matching external data types to native database types (and vice versa). This matching is done in two steps: (1) the external type is matched to a JDBC SQL type (defined as a constant in the java.sql.Types class), and (2) the JDBC SQL type is translated by the JDBC driver to the native database type.

The next example shows how the table of a database can be populated. A naïve approach hard-codes datatype-specific information in the program, thereby making it nonportable. Following this I'll show a portable alternative that illustrates another stumbling block.

  • Example: Inserting data using PreparedStatement: A PreparedStatement instance is used to prepare and execute precompiled SQL statements. Suppose you want to insert rows of data in the table EMP. The pattern of the SQL statement to insert a row is always the same; it's only the values of the columns that change with every new row. To start with, you construct a PreparedStatement instance as follows:

    PreparedStatement ps = conn.prepareStatement("insert into emp " +
    "(empno, ename, job, mgr, hiredate, sal, comm, deptno) " +
    "values (?, ?, ?, ?, ?, ?, ?, ?)";

The variable conn refers to the database connection. The PreparedStatement instance, ps, is parametrized with the values of the columns, indicated by the question marks. Each parameter is matched with a value before executing the prepared statement for some row. The following code segment shows how the empno column value is filled:

String next = st.nextToken();
if (next.equals("null")) ps.setNull(1, Types.SMALLINT);
else ps.setShort(1, (short)Integer.parseInt(next));

The first line reads an input token (using a StringTokenizer instance st to parse each input line) as a String object. The second line checks whether this token spells null, which indicates that the input does not have a value for this column. In this case a special setNull method is invoked on ps. The first argument to this method indicates the parameter position in the prepared statement (empno is the first); the second argument indicates the JDBC SQL type of the intended value. As mentioned earlier, the class java.sql.Types defines a set of constants corresponding to various SQL types, and the constant java.sql.Types.SMALLINT stands for the SQL type SMALLINT.

For every column in the EMP table, these three lines of code need to be implemented with appropriate modification in data types. There are a couple of serious drawbacks to this approach. One is that the program becomes ungainly and hard to maintain. Another is that all data types are exposed to the developer, so if a column type changes, or this program is ported to a different database, this code must be rewritten.

A better alternative postpones the datatype translation to runtime, thereby making the code portable. This alternative makes use of the PreparedStatement method setObject:

ps.setObject(1, next);

used instead of

ps.setShort(1, (short)Integer.parseInt(next));

The second argument, next, is a String instance that's automatically translated by the JDBC driver to the required database type, which in this case is a small integer. There is no coding of any datatype information. This statement can be set in a loop, using the loop index to control the first argument, which is the position of the parameter in the PreparedStatement instance.

If one or more of the column values is null, a little more work is required. The statement would then be written as:

ps.setNull(1, )

To maintain the datatype independence of the code to make it portable, the SQL type of the column value isn't coded directly here. Instead, it's discovered at runtime using the java.sql.DatabaseMetaData interface, and then plugged into the above statement.

The DatabaseMetaData interface provides metadata information for a database. Metadata is data that describes data. For instance, the EMP table contains employee information. This is data. Metadata would contain information on things like the number of columns in the table, the data types of these columns, whether a column can have null values, and so on. This is data about data.

The DatabaseMetaData interface provides methods that can be called to find out various metadata information about a database.

For our example above, we need to find the data types of the columns of EMP so we can plug that information into the PreparedStatement setNull method call.

This alternative works with the MS Access database, but not with the Oracle database ­ the OCI8 driver refuses to pass the setObject invocation because it's unable to translate the String external type to the required database type.

Situation 3: Positioned Update
When a SQL query is executed in JDBC, it returns a result set that represents the resulting table, consisting of a sequence of rows. A cursor is used to traverse the rows of a result set. The term positioned update refers to updating a database row referred to by the current position of the result set cursor. The following steps need to be taken to effect positioned updates from a result set:

  1. Execute a SELECT FOR UPDATE statement. At the very least this will lock the rows of the table in the result set against other concurrent transactions. For this step to work, the database must support SELECT FOR UPDATE.
  2. Get the cursor name of the result set. This will be used to reference the current row at the time of the update.
  3. Construct a prepared statement to update a row, using the UPDATEŠWHERE CURRENT OF <cursor name> form, with input parameters for the updated columns as well as for the cursor. For this to work, the database must support positioned update.
  4. Traverse the result set, and for every row to be updated execute the prepared statement after setting all the input parameter values. The cursor name would refer to the row currently being referenced in the result set.
Now I'll show a pure JDBC code template using the above steps, written for full portability. This time around, the stumbling block to portability is even more severe.

  • Example: JDBC template and Oracle specifics: Whether a database supports the required SELECT FOR UPDATE and UPDATE...WHERE statements described above can be discovered by using the DatabaseMetaData interface. Assuming a database does in fact support the required functionality, the code in Listing 1 serves as a template for updating all employee names in the EMP table to lower case.
MS Access doesn't support the required database functionality for positioned update. Oracle supports it, but the OCI8 driver doesn't implement the JDBC specification of cursor name. Instead, a completely different solution is adopted. The driver provides a ROWID, which is equivalent to the cursor name. A ROWID is added as a pseudocolumn to a query:

select name, rowid from emp

It may be retrieved using the ResultSet getString method:

String rowid = rset.getString(2);

It may be set as a parameter using the PreparedStatement setString method:

ps.setString(2, rowid)

The JDBC-compliant template code shown in Listing 1 can be reworked for Oracle, using rowid, at the cost of giving up portability.

Roadmap for Workarounds
The examples given here point to a common factor that contributes to the stumbling blocks: the JDBC driver. A JDBC driver implements the JDBC specification for a specific database system. For any given database system there is generally a wide choice of drivers available, including those from the database system vendor as well as third-party vendors. These drivers may differ in various respects, especially in the degree to which they implement the JDBC specification. Choosing an inappropriate driver can force the developer to write database-specific code in the application, thereby giving up portability.

A related common issue that is apparent from the examples is that even if the driver is appropriate for the task at hand, some database-specific information may have to be factored into the application. A naïve approach to this, which is to hard-code the required information into the application, makes the application nonportable. However, it's often possible to discover this information at runtime instead by using the DatabaseMetaData interface, thereby maintaining the portability of the application.

These observations suggest a two-point roadmap to work around the stumbling blocks:

  • Point 1: Implement a "back-end" class that can load any given driver at runtime from a list of candidate drivers. This can be used not only to choose among a set of drivers for a single database, but among drivers for different databases if the application is ported to a different database. Figure 5 illustrates this point.

  • Point 2: Separate metadata discovery from other code by implementing one or more classes that can serve to discover metadata at runtime. Depending on what kind of metadata needs to be discovered, you could design a suite of discovery classes that could be loaded ("plugged in") at runtime as required. Figure 6 illustrates this point.
Implementing the Roadmap
I'll now use the roadmap to sketch the process of building an application that populates the EMP table with data from a text file. Recall that a row of data may be inserted into the table by using a PreparedStatement instance, which in turn requires that the type of each column of EMP be discovered at runtime using the DatabaseMetaData interface.

Following Point 1 of the roadmap, the DriverLoader class is implemented, which loads either the OCI8 driver for Oracle8 or the JDBC-ODBC bridge for MS Access, as required at runtime. Other drivers for the existing databases, as well as drivers for other databases, may be added as needed.

Following Point 2 of the roadmap, the application is divided into three classes. One of these, TableColumns, implements the metadata discovery process. In this case it's the discovery of column types for the table EMP. Another class, TableMediator, uses this metadata and interacts with a third class, TableBuilder, that reads data from the input text file and sends it down the chain of classes to the database.

This chain of classes is illustrated in Figure 7.

The JDBC driver is the most critical piece in any Java database application. The driver must be picked with care, taking into account the architecture of the application, the extent to which the driver implements the JDBC specification, and the performance of the driver for various connection and database access operations.

I picked this specific set of three stumbling blocks for illustration simply because they provide a window into very different ways in which JDBC may be used in a database application. These examples and the workarounds demonstrated point to a general way of structuring a database application for cross-database portability. Specific refinements to this general approach can be adopted based on special requirements of the applications and the architecture; there's a lot of room for maneuvering within the proposed roadmap.

More Stories By Sesh Venugopal

Sesh Venugopal holds a Ph.D. in computer science from Rutgers University. He runs his own IT and education consulting company, Intecus, Inc. (www.intecus.com), specializing in Web-based systems using the Java platform. Sesh is the author of a textbook, Data Structures: An Object-Oriented Approach with Java, also online at www.intecus.com/bookpage.html.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

IoT & Smart Cities Stories
Moroccanoil®, the global leader in oil-infused beauty, is thrilled to announce the NEW Moroccanoil Color Depositing Masks, a collection of dual-benefit hair masks that deposit pure pigments while providing the treatment benefits of a deep conditioning mask. The collection consists of seven curated shades for commitment-free, beautifully-colored hair that looks and feels healthy.
The textured-hair category is inarguably the hottest in the haircare space today. This has been driven by the proliferation of founder brands started by curly and coily consumers and savvy consumers who increasingly want products specifically for their texture type. This trend is underscored by the latest insights from NaturallyCurly's 2018 TextureTrends report, released today. According to the 2018 TextureTrends Report, more than 80 percent of women with curly and coily hair say they purcha...
The textured-hair category is inarguably the hottest in the haircare space today. This has been driven by the proliferation of founder brands started by curly and coily consumers and savvy consumers who increasingly want products specifically for their texture type. This trend is underscored by the latest insights from NaturallyCurly's 2018 TextureTrends report, released today. According to the 2018 TextureTrends Report, more than 80 percent of women with curly and coily hair say they purcha...
We all love the many benefits of natural plant oils, used as a deap treatment before shampooing, at home or at the beach, but is there an all-in-one solution for everyday intensive nutrition and modern styling?I am passionate about the benefits of natural extracts with tried-and-tested results, which I have used to develop my own brand (lemon for its acid ph, wheat germ for its fortifying action…). I wanted a product which combined caring and styling effects, and which could be used after shampo...
The platform combines the strengths of Singtel's extensive, intelligent network capabilities with Microsoft's cloud expertise to create a unique solution that sets new standards for IoT applications," said Mr Diomedes Kastanis, Head of IoT at Singtel. "Our solution provides speed, transparency and flexibility, paving the way for a more pervasive use of IoT to accelerate enterprises' digitalisation efforts. AI-powered intelligent connectivity over Microsoft Azure will be the fastest connected pat...
There are many examples of disruption in consumer space – Uber disrupting the cab industry, Airbnb disrupting the hospitality industry and so on; but have you wondered who is disrupting support and operations? AISERA helps make businesses and customers successful by offering consumer-like user experience for support and operations. We have built the world’s first AI-driven IT / HR / Cloud / Customer Support and Operations solution.
Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Druva is the global leader in Cloud Data Protection and Management, delivering the industry's first data management-as-a-service solution that aggregates data from endpoints, servers and cloud applications and leverages the public cloud to offer a single pane of glass to enable data protection, governance and intelligence-dramatically increasing the availability and visibility of business critical information, while reducing the risk, cost and complexity of managing and protecting it. Druva's...
BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for five years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe.