SQL Zone is brought to you in partnership with:

Daniel has posted 3 posts at DZone. View Full User Profile

The ABCs of JDBC, Part 3 - The JDBC API

05.24.2010
| 10985 views |
  • submit to reddit

In this week's installment of our JDBC FAQ series, we look at the progression of the JDBC API from version 2.0 through 4.0 and examine how to perform some common tasks such as inserting and deleting rows programmatically, inserting raw data (such as images) into a database, and setting your JDBC driver properties.

Read the other parts in DZone's JDBC FAQ series:

What's new in the JDBC 2.0 API ?           

JDBC 2.0 represented the partitioning of the JDBC API into core package and standard extension. With the core JDBC package fulfilling functionality present in JDBC 1.2 -- which predated JDBC 2.0 -- guaranteeing backward compatibility and the standard extension package containing new functionality distributed separately of the JDK.

The new functionality of the standard extension package placed under Java's javax.sql namespace included: JNDI support for JDBC Data Sources, connection pooling, rowsets and distributed transactions.

What's new in the JDBC 3.0 API ?

JDBC 3.0 represented the inclusion of the standard extension package (i.e. javax.sql) into the JDK itself, as well as additional enhancements to the API's overall functionality.

Among the enhancements present in JDBC 3.0 over JDBC 2.0 are: Better connection pooling, increased support for SQL99 features -- the last of which is a database industry standard -- updated metadata APIs, named parameters in Callable statements in order to support parameter names in stored procedures, as well as new and updated data types.

New features of the JDBC 3.0 include: Retrieving auto-generated keys, integration support for the Java Connector Architecture(JCA), ResultSet hold-ability, PreparedStatement pooling, as well as savepoints for transactions.

What's new in the JDBC 4.0 API ?

JDBC 4.0 represents the latest version of the JDBC API and forms part of the JDK 6.0 (i.e. Java SE 6).

Among the enhancements present in JDBC 4.0 over JDBC 3.0 are: Enhanced driver and connection management, increased support for SQL2003 features -- the last of which is a database industry standard and a successor to SQL99 -- better exception handling, as well as new and updated data types.

New features of the JDBC 4.0 include: SQLXML and XML support, as well as support for national character set conversion -- supported through SQL2003.

What does Class.forName return ?              

It returns the Class object associated with the class or interface with a given string name. Class.forName allows code of a class to be dynamically loaded, without having to know the actual classname until runtime. In the context of JDBC, Class.forName is typically used to load the class corresponding to a particular JDBC driver, therefore allowing to dynamically load a JDBC driver's main class (e.g. Class.forName("com.mysql.jdbc.Driver")).

Note that Class.forName is a construct used when performing a connection through DriverManager and it's functionality is no longer required if using a driver compatible with the JDBC 4.0 API (i.e. JDBC 4.0 automatically performs the duties of Class.forName).

What JDBC objects generate SQLWarnings ?              

SQLWarnings are generated from Connection, Statement, and ResultSet objects. Access to SQLWarnings is provided by the methods, Connection.getWarnings(), Statement.getWarnings(), ResultSet.getWarnings(), respectively.

What are the common tasks of JDBC ?              

The common tasks of JDBC are:
  •  Performing a connection to a database -- through a Connection object.
  •  Performing an operation on a database (i.e.create, read, update, delete ) -- through a Statement object.
  • Obtaining the results of an operation on a database -- through a ResultSet object.

What are the two major components of JDBC ?              

  •  The JDBC API used by application developers to connect to a permanent storage system.
  •  The JDBC SPI (Service Provider Interface) used by permanent storage manufacturers to provide connectivity for JDBC (i.e. through the creation of JDBC drivers).

What is a JDBC DataSource ?

A JDBC DataSource is an object used to establish a connection to a permanent storage system. It's the preferred way by which to establish a connection, instead of the older DriverManager approach.

What types of DataSource objects are specified in the Optional Package ?

  •   Basic - Provides a standard Connection object.
  •   Pooled - Provides a Connection pool and returns a Connection that is controlled by the pool.
  •   Distributed - Provides a Connection that can participate in distributed transactions.

Note the optional package forms part of the JDBC 2.0 API, in later versions -- JDBC 3.0 and JDBC 4.0 -- the optional package forms part of the core JDBC API (i.e. Java's JDK)

How to insert and delete a row programmatically ?              

In order to insert or delete a row programatically, you first need to obtain the set of rows on which you want to delete a particular row or insert a new row.

The ResultSet object used in conjunction with JDBC's standard Statement is used for this purpose, as illustrated next:
    Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("SELECT * FROM NOTES");

In this case, the ResultSet object represents a query for all the rows belonging to the NOTES table.

To delete a row programmatically, two methods belonging to the ResultSet object are used: absolute() and deleterow(). The absolute() method is used to move the cursor of the ResultSet() to a particular position and the deleterow() is used to delete the row of a selected cursor. So for example, the following snippet deletes the second row of a ResultSet, as well as the row from the database:

     rset.absolute(2);
rset.deleteRow();

To add a row programmatically, the methods moveToInsertRow() and insertRow(), as well as the updateXXX methods are used -- all belonging to the ResultSet object and where XXX represent data types (e.g.String, BigDecimal, Int, etc). So for example, the following snippet adds a row to the ResultSet object, as well as the database.

     rset.moveToInsertRow(); 
rset.updateInt(1, 7777);
rset.updateString(2, "JDBC tips");
rset.updateString(3, "D.Rubio");
rset.insertRow();

 

The moverToInsertRow() is a method which moves the ResultSet's cursor to a special row designed to insert rows. Once the cursor is in this position (i.e. insert row), the row's fields are filled with insertion values. The updateInt(1, 777) indicates to populate the row's first columns with the integer 777, the updateString indicates to populate the row's second column with the string "JDBC tips" and so on. On the special row's (i.e. insert row) fields are populated, the insertRow() method is called to commit the row to both the ResultSet object, as well as the database.

How do I insert an image file (or other raw data) into a database ? (Same as: How do I insert a .jpg into a mySQL data base ? ) 

In order to insert an image file or any other type of raw data (i.e. binary), you first need to ensure the database is capable of storing this type of data. The majority of commercial databases support a data type called BLOB (Binary Large Object) for this purpose. So ensuring you hava a database table with a BLOB column, the following code snippet illustrates the insertion process:

     // Connection object defined 
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO logos VALUES (?,?)");
pstmt.setInt(1,1000);
File fBlob = new File("logo.jpg");
FileInputStream is = new FileInputStream(fBlob);
pstmt.setBinaryStream (2,is,(int)fBlob.length());
pstmt.execute();

The first line creates a JDBC PreparedStatement object for inserting a row into a table called logos, with the two columns of the table representing an integer and BLOB. Note the use of the syntax "?" as a placeholder for each value. Next, you can observe a call to the setInt() method which belongs to the PreparedStatement object, indicating to insert the integer value 1000 in position 1.

Next, to insert an image file or any other type of raw data (i.e. binary) you need to create a stream from its contents. To do this, you rely on the Java File class to read a file from the file system (e.g. new File("logo.jpg")), once read, you use the FileInputStream class to generate the actual stream from the File object. Once this is done, a called is made to the setBinaryStream() method -- belonging to the PreparedStatement object -- to assign the stream as an insertion parameter. The values passed to setBinaryStream in this case are, '2' representing position 2, 'is' representing the binary stream and the length of the binary stream -- 'fBlob.length()' -- taken from the parent File object.

Finally, a called is made to the execute() statement of the PreparedStatement class in order to commit the insertion to the database.

How do I write Greek ( or other non-ASCII/8859-1 ) characters to a database ?              

In order to insert Greek ( or other non-ASCII/8859-1 ) characters, you first need to ensure the database is capable of storing these type of characters. Upon installation, a database is set-up with support for these type of characters (i.e. languages), this is done because support for special characters can cause overhead both in terms of disk space and processing.

Once you have a database which supports Greek ( or other non-ASCII/8859-1 ) characters, you have met the only requirement for inserting encoded characters into the database. The translation of characters from a standard Java String encoding into the database encoding is handled completely by the JDBC driver irrespective of the encoding of the JVM or the String object itself.

How do I disallow NULL values in a table ?              

Disallowing nulls in a table is not a job particularly suited for JDBC, since it involves checking insertion values. Therefore, disallowing nulls is best done either prior to attempting a JDBC call -- using Java code and rejecting a null value prior to insertion (i.e. at the application level) -- or after attempting the JDBC call -- enforcing a non null constraint on a database table (i.e. at the database level by creating the column as NOT NULL)

How do I extract the SQL statements required to move all tables and views from an existing database to another database ?              

 Table and view structures from database vendors are highly specific, therefore there is no standard way in which to obtain them through JDBC. However, the process of obtaining all data from a database is common procedure -- often call a 'dump' -- which can be achieved using standard JDBC queries. For example, MySQL supports the statement "SHOW CREATE TABLE" which outputs the SQL statements required to rebuild a table, Oracle on the other hands ha Metadata tables which contain information which can be used to rebuild tables and views. These approaches though specific to each vendor, can be executed using standard JDBC queries.

One piece of functionality that has been discussed by the JDBC Expert Group is the idea of Bulk Loading. Several driver vendors already include Bulk Load mechanisms within their JDBC driver set so you can easily and quickly move tables from one database to another, which will greatly enhance the movement of an entire database.

A project you might want to look at if your moving tables and views from one database vendor to another is the DBCopy Plugin available at http://dbcopyplugin.sourceforge.net/ .

How does a custom RowSetReader get called from a CachedRowSet ?              

 The RowSetReader must be registered with the CachedRowSet as follows:

     CachedRowSet.setReader(javax.sql.RowSetReader reader)

 

Once assigned, a call to CachedRowSet.execute() will call the RowSetReader.

What is the JDBC syntax for using a date literal or variable in a standard Statement ?              

First, be advised that using a PreparedStatement -- instead of a Statement -- shields you from a series of syntax errors involving date literals and variables. However, if you prefer to insert date literals using a Statement object the way to do so is using the following syntax: {d 'yyyy-mm-dd'}.

Using Statement objects with date literals or variables, the only thing you need to be careful about is mixing single quotes (') with double quotes ("). For example, the following statement illustrates a JDBC query string containing a date literal: 

      String sqlQuery= "SELECT * FROM articles WHERE  publishingdate = {d '2001-10-21'}" 

The following statement illustrates a JDBC query string containing a date variable:

     java.sql.Date pDate; 
// Set date
String sqlQuery = "SELECT * FROM articles WHERE publishingdate = {d '" + pDate + "'}"

When an SQL select statement doesn't return any rows, is an SQLException thrown ?              

No. The results of an SQL select statement are assigned to a ResultSet object which can contain or not contain rows. In order to determine if an SQL select statement didn't return any rows, you would need to rely on ResultSet's auxiliary methods (e.g. next()).

What scalar functions can I expect to be supported by JDBC ?              

Even though the JDBC specification defines a base set of scalar functions -- using an escape syntax in the form {fn function_name(arguments)} (e.g. {fn concat("JDBC", "Tipos")} ) -- support for these type of functions is highly dependent on the JDBC driver your using. You should consult your JDBC driver for a complete list of supported scalar functions.

Why doesn't JDBC accept URLs instead of a URL string ?              

JDBC relies on a special syntax to specify connection URL's which differs from standard URL's (i.e. Internet addresses), the JDBC syntax is the following: jdbc:[driver_type]:[username/password]/[database_name], where [driver_type] and [username/password] are optional parameters. For example, in order to connect to an Oracle database using its thin driver you would use the following syntax: jdbc:oracle:thin:@database_name. Another example consisting of using a MySQL database would use the following syntax: jdbc:mysql://localhost:3306/database_name. As you can observe, the syntax is dependent on the database manufacturer and driver, in addition to being distinct standard URL's (i.e. Internet addresses) consisting of [protcol]://[host]/[path].

Where can I find info, frameworks and example source for writing a JDBC driver?              

The place to start is the JDBC specification. Once you do this, I recommend researching the particular database vendor for which you want to implement a JDBC driver, for documentation on native libraries and API's for interfacing with their product. Finally, a detailed look at the JDBC's Service Provider Interface (SPI) which details the mapping requirements between JDBC and a database's interface.

How do I set properties for a JDBC driver and where are the properties stored ?           

JDBC properties can be set in many places. For starters, they can be hard-coded as a String inside an application class and then used to establish a database connection. However, for maintenance purposes they can also be defined inside a text file (i.e. a single location) and read from various application classes using Java's standard Properties class. In addition, another variation for defining JDBC driver properties is inside a web container's configuration files(e.g. web.xml), the last of which is almost the norm for Java EE applications.

 

Published at DZone with permission of its author, Daniel Rubio.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Manuel Jordan replied on Mon, 2010/05/24 - 4:02pm

Hello Daniel

Interesting work, thanks for sharing

My Best Regards

-Manuel

Eric Jablow replied on Tue, 2010/05/25 - 10:41am

Please reformat your examples. The <pre> tags are showing up.

Nitin Bharti replied on Tue, 2010/05/25 - 2:06pm in response to: Eric Jablow

Hi Eric, thanks for catching that. The <pre> tags have been removed.

Nitin

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.