5 things about Java Database connectivity you don't know: raising your relationship with the JDBC API

5 things about Java Database connectivity you don't know: raising your relationship with the JDBC API

Currently, many developers treat the Java Database connectivity (JDBC) API as a data access platform, such as Hibernate or Springmany. However, JDBC does not only serve as a background role in database connections. For JDBC, the more you know, the more efficient your RDBMS interacts.

In this 5 series, I'll introduce you to the new features introduced in several JDBC 2.0 to JDBC 4.0. Designed to take into account the challenges of modern software development, these new features support application scalability and increase the productivity of developers-the two most common challenges facing modern Java developers.

1. Scalar functions

Different RDBMS implementations provide irregular support for SQL and/or value-added features that are designed to make the programmer's work simpler. For example, it is well known that SQL supports a scalar operator count (), which returns the number of rows that meet a specific SQL filtering rule (or, more specifically, a WHERE predicate). In addition, modifying the values returned by SQL is tricky-getting the current date and time from the database can make JDBC developers, even the most patient programmers, mad (or even pining).

Thus, the JDBC specification provides a degree of isolation/rewriting through scalar functions for different RDBMS implementations. The JDBC specification includes a series of supported operations that the JDBC driver should identify and overwrite according to the needs of a particular database implementation. Therefore, for a database that supports returning the current date and/or time, the time query should be as simple as Listing 1:

Listing 1. Current time?

Connection　conn　=　...;　//　get　it　from　someplace
Statement　stmt　=　conn.createStatement();
ResultSet　rs　=　stmt.executeQuery("{CURRENT_DATE()}");

A complete list of scalar functions recognized by the JDBC API is given in the JDBC specification appendix, but a given driver or database may not support a complete list. You can use the DatabaseMetaData object returned from Connection to get the functions that are supported by the given JDBC, as shown in Listing 2:

Listing 2. What can you provide for me?

Connection　conn　=　...;　//　get　it　from　someplace
DatabaseMetaData　dbmd　=　conn.getMetaData();

A scalar function list is a comma-delimited String that is returned from various DatabaseMetaData methods. For example, all numeric scalar values are listed by the getnumericfunctions () invocation, and a string.split () is executed on the result.-Look! -Instantly appear the Equals ()-testable list.

2. Scrollable resultsets

Create a Connection object and use it to create a Statement, which is most commonly used in JDBC. The Statement provided to the SQL SELECT returns a ResultSet. The ResultSet is then obtained by a while loop (nothing different from the iterator) until the ResultSet is empty, and the loop body extracts one column at a time from left to right.

This whole process is so universal and almost sacred: it does so only because it should. Alas! In fact, this is completely unnecessary.

Introduction of scrollable ResultSet

Many developers are unaware that the JBDC has increased considerably in the past few years, although these enhancements have been reflected in the new version. The first major enhancements were in JDBC 2.0, which occurred during the use of JDK 1.2. When writing this article, JDBC has developed to JDBC 4.0.

An interesting enhancement (albeit often overlooked) in JDBC 2.0 is the ResultSet Scrolling feature, which means that you can move forward or backward as needed, or both. This requires a bit of foresight, however the-JDBC call must point to a ResultSet that can be scrolled when creating the Statement.