Blog : Open Database Connectivity

Open Database Connectivity

Bridging configurations
JDBC-ODBC bridges

A JDBC-ODBC bridge consists of a JDBC driver which employs an ODBC driver to connect to a target database. This driver translates JDBC method calls into ODBC function calls. Programmers usually use such a bridge when a particular database lacks a JDBC driver. Sun Microsystems included one such bridge in the JVM, but viewed it as a stop-gap measure while few JDBC drivers existed. Sun never intended its bridge for production environments, and generally recommends against its use. As of 2008[update] independent data-access vendors deliver JDBC-ODBC bridges which support current standards for both mechanisms, and which far outperform the JVM built-in.[citation needed]
ODBC-JDBC bridges

An ODBC-JDBC bridge consists of an ODBC driver which uses the services of a JDBC driver to connect to a database. This driver translates ODBC function-calls into JDBC method-calls. Programmers usually use such a bridge when they lack an ODBC driver for a particular database but have access to a JDBC driver.
 OLE DB

Microsoft provides an OLE DB-ODBC bridge for simplifying development in COM aware languages (i.e. Visual Basic). This bridge forms part of the MDAC system component bundle, together with other database drivers.
Java Database Connectivity

JDBC is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the JVM host environment.
Functionality

JDBC allows multiple implementations to exist and be used by the same application. The API provides a mechanism for dynamically loading the correct Java packages and registering them with the JDBC Driver Manager. The Driver Manager is used as a connection factory for creating JDBC connections.

JDBC connections support creating and executing statements. These may be update statements such as SQL's CREATE, INSERT, UPDATE and DELETE, or they may be query statements such as SELECT. Additionally, stored procedures may be invoked through a JDBC connection. JDBC represents statements using one of the following classes:

  Statement – the statement is sent to the database server each and every time.
  PreparedStatement – the statement is cached and then the execution path is pre determined on the database server allowing it to be executed multiple times in an efficient manner.
  CallableStatement – used for executing stored procedures on the database.

Update statements such as INSERT, UPDATE and DELETE return an update count that indicates how many rows were affected in the database. These statements do not return any other information.

Query statements return a JDBC row result set. The row result set is used to walk over the result set. Individual columns in a row are retrieved either by name or by column number. There may be any number of rows in the result set. The row result set has metadata that describes the names of the columns and their types.

There is an extension to the basic JDBC API in the javax.sql.

JDBC connections are often managed via a connection pool rather than obtained directly from the driver. Examples of connection pools include BoneCP, C3P0 and DBCP
Examples

The method Class.forName(String) is used to load the JDBC driver class. The line below causes the JDBC driver from some jdbc vendor to be loaded into the application. (Some JVMs also require the class to be instantiated with .newInstance().)

<span>Class</span>.<span>forName( "com.somejdbcvendor.TheirJdbcDriver" );</span>

In JDBC 4.0, it's no longer necessary to explicitly load JDBC drivers using Class.forName(). See JDBC 4.0 Enhancements in Java SE 6.

When a Driver class is loaded, it creates an instance of itself and registers it with the DriverManager. This can be done by including the needed code in the driver class's static block. e.g. DriverManager.registerDriver(Driver driver)

Now when a connection is needed, one of the DriverManager.getConnection() methods is used to create a JDBC connection.

<span>Connection</span> conn = <span>DriverManager.getConnection(</span>

<span>  "jdbc:somejdbcvendor:other data needed by some jdbc vendor",</span>

<span>  "myLogin",</span>

<span>  "myPassword" );</span>

The URL used is dependent upon the particular JDBC driver. It will always begin with the "jdbc:" protocol, but the rest is up to the particular vendor. Once a connection is established, a statement must be created.

<span>Statement</span> stmt = conn.<span>createStatement();</span>

<span>try</span> <span>{</span>

<span>  stmt.executeUpdate( "INSERT INTO MyTable( name ) VALUES ( 'my name' ) " );</span>

<span>}</span> <span>finally {</span>

<span>  //It's important to close the statement when you are done with it</span>

<span>  try { stmt.close(); } catch (SQLException ignore) { /* Propagate the original exception instead of this one that you may want</span>

<span>just logged */</span> <span>}</span>

<span>}</span>

Note that Connections, Statements, and ResultSets often tie up operating system resources such as sockets or file descriptors. In the case of Connections to remote database servers, further resources are tied up on the server, e.g., cursors for currently open ResultSets. It is vital to close() any JDBC object as soon as it has played its part; garbage collection should not be relied upon. Forgetting to close() things properly results in spurious errors and misbehaviour. The above try-finally construct is a recommended code pattern to use with JDBC objects.

Data is retrieved from the database using a database query mechanism. The example below shows creating a statement and executing a query.

<span>Statement</span> stmt = conn.<span>createStatement();</span>

<span>try</span> <span>{</span>

<span>  ResultSet rs = stmt.executeQuery( "SELECT * FROM MyTable" );</span>

<span>  try {</span>

<span>  while ( rs.next() ) {</span>

<span>  int numColumns = rs.getMetaData().getColumnCount();</span>

<span>  for ( int i = 1 ; i <= numColumns ; i++ ) {</span>

<span>  // Column numbers start at 1.</span>

<span>  // Also there are many methods on the result set to return</span>

<span>  //<span>  the column as a particular type. Refer to the Sun documentation</span></span>

<span>  //<span>  for the list of valid conversions.</span></span>

<span>  System.out.println( "COLUMN " + i + " = " + rs.getObject(i) );</span>

<span>  }</span>

<span>  }</span>

<span>  } finally {</span>

<span>  try { rs.close(); } catch (SQLException ignore) { /* Propagate the original exception instead of this one that you may want just logged */ }</span>

<span>  }</span>

<span>}</span> <span>finally {</span>

<span>  try { stmt.close(); } catch (SQLException ignore) { /* Propagate the original exception instead of this one that you may want just logged */ }</span>

<span>}</span>

Typically, however, it would be rare for a seasoned Java programmer to code in such a fashion. The usual practice would be to abstract the database logic into an entirely different class and to pass preprocessed strings (perhaps derived themselves from a further abstracted class) containing SQL statements and the connection to the required methods. Abstracting the data model from the application code makes it more likely that changes to the application and data model can be made independently.

An example of a PreparedStatement query, using conn and class from first example.

<span>PreparedStatement</span> ps = conn.<span>prepareStatement( "SELECT i.*, j.* FROM Omega i, Zappa j WHERE i.name = ? AND j.num = ?" );</span>

<span>try</span> <span>{</span>

<span>  // In the SQL statement being prepared, each question mark is a placeholder</span>

<span>  // that must be replaced with a value you provide through a "set" method invocation.</span>

<span>  // The following two method calls replace the two placeholders; the first is</span>

<span>  // replaced by a string value, and the second by an integer value.</span>

<span>  ps.setString(1, "Poor Yorick");</span>

<span>  ps.setInt(2, 8008);</span>

<span>// The ResultSet, rs, conveys the result of executing the SQL statement.</span>

<span>  // Each time you call rs.next(), an internal row pointer, or cursor,</span>

<span>  // is advanced to the next row of the result.<span>  The cursor initially is</span></span>

<span>  // positioned before the first row.</span>

<span>  ResultSet rs = ps.executeQuery();</span>

<span>  try {</span>

<span>  while ( rs.next() ) {</span>

<span>  int numColumns = rs.getMetaData().getColumnCount();</span>

<span>  for ( int i = 1 ; i <= numColumns ; i++ ) {</span>

<span>  // Column numbers start at 1.</span>

<span>  // Also there are many methods on the result set to return</span>

<span>  // the column as a particular type. Refer to the Sun documentation</span>

<span>  // for the list of valid conversions.</span>

<span>  System.out.println( "COLUMN " + i + " = " + rs.getObject(i) );</span>

<span>  } // for</span>

<span>  } // while</span>

<span>  } finally {</span>

<span>  try { rs.close(); } catch (SQLException ignore) { /* Propagate the original exception instead of this one that you may want just logged */ }</span>

<span>  }</span>

<span>}</span> <span>finally {</span>

<span>  try { ps.close(); } catch (SQLException ignore) { /* Propagate the original exception instead of this one that you may want just logged */ }</span>

<span>}</span> <span>// try</span>

When a database operation fails, an SQLException is raised. There is typically very little one can do to recover from such an error, apart from logging it with as much detail as possible. It is recommended that the SQLException be translated into an application domain exception (an unchecked one) that eventually results in a transaction rollback and a notification to the user.

Here are examples of host database types which Java can convert to with a function.

setXXX() Methods

Oracle Datatype
   

setXXX()

CHAR
   

setString()

VARCHAR2
   

setString()

NUMBER
   

setBigDecimal()

setBoolean()

setByte()

setShort()

setInt()

setLong()

setFloat()

setDouble()

INTEGER
   

setInt()

FLOAT
   

setDouble()

CLOB
   

setClob()

BLOB
   

setBlob()

RAW
   

setBytes()

LONGRAW
   

setBytes()

DATE
   

setDate()

setTime()

setTimestamp()

For an example of a CallableStatement (to call stored procedures in the database), see the JDBC API Guide.
[edit] JDBC drivers

Main article: JDBC driver

JDBC drivers are client-side adapters (installed on the client machine, not on the server) that convert requests from Java programs to a protocol that the DBMS can understand.
[edit] Types

There are commercial and free drivers available for most relational database servers. These drivers fall into one of the following types:

  Type 1 that calls native code of the locally available ODBC driver.
  Type 2 that calls database vendor native library on a client side. This code then talks to database over network.
  Type 3, the pure-java driver that talks with the server-side middleware that then talks to database.
  Type 4, the pure-java driver that uses database native protocol.

There is also a type called internal JDBC driver, driver embedded with JRE in Java-enabled SQL databases. It's used for Java stored procedures. This does not belong to the above classification, although it would likely be either a type 2 or type 4 driver (depending on whether the database itself is implemented in Java or not). An example of this is the KPRB driver supplied with Oracle RDBMS. "jdbc:default:connection" is a relatively standard way of referring making such a connection (at least Oracle and Apache Derby support it). The distinction here is that the JDBC client is actually running as part of the database being accessed, so access can be made directly rather than through network protocols.

Type 5 - The Next Step?

There is increasing conversation about what the future holds for JDBC and what the next specification might look like (i.e. what Type 5 will look like). Discussions in Java forums seem to focus on retaining the strengths of Type 4 drivers but enhancing the architecture with improved capabilities such as:

  Accelerated driver performance regardless of number of simultaneous users or JVM
  Ability to enable or tune features such as high availability, and statement pooling, and without needing to add or change application code
  Reduced driver CPU and memory consumption and footprint
  Simplified driver deployment, reducing the requirements for extra JAR files or external dependencies based on the functionality required
  Elimination of the need for proprietary extensions to the JDBC specification

Progress Software recently announced the general availability of ‘Type 5’ JDBC drivers. While the term ‘Type 5’ is by no means part of the official JDBC specification, the availability of these new drivers does add fuel to the ongoing debate about the future of JDBC within the ever-evolving Java ecosystem.
Swing (Java)

The Swing toolkit, shipped as part of the Java SE platform, provides a rich set of GUI components. But Swing offers much more functionality than a collection of standard widgets. This section takes a look at Swing's rich functionality.

Swing is the primary Java GUI widget toolkit. It is part of Sun Microsystems' Java Foundation Classes (JFC) — an API for providing a graphical user interface (GUI) for Java programs.

Swing was developed to provide a more sophisticated set of GUI components than the earlier Abstract Window Toolkit. Swing provides a native look and feel that emulates the look and feel of several platforms, and also supports a pluggable look and feel that allows applications to have a look and feel unrelated to the underlying platform.

To create a Java program with a graphical user interface (GUI), you'll want to learn about Swing.

The Swing toolkit includes a rich set of components for building GUIs and adding interactivity to Java applications. Swing includes all the components you would expect from a modern toolkit: table controls, list controls, tree controls, buttons, and labels.

Swing is far from a simple component toolkit, however. It includes rich undo support, a highly customizable text package, integrated internationalization and accessibility support. To truly leverage the cross-platform capabilities of the Java platform, Swing supports numerous look and feels, including the ability to create your own look and feel. The ability to create a custom look and feel is made easier with Synth, a look and feel specifically designed to be customized. Swing wouldn't be a component toolkit without the basic user interface primitives such as drag and drop, event handling, customizable painting, and window management.

Swing is part of the Java Foundation Classes (JFC). The JFC also include other features important to a GUI program, such as the ability to add rich graphics functionality and the ability to create a program that can work in different languages and by users with different input devices.

The following list shows some of the features that Swing and the Java Foundation Classes provide.

Swing GUI Components

The Swing toolkit includes a rich array of components: from basic components, such as buttons and check boxes, to rich and complex components, such as tables and text. Even deceptively simple components, such as text fields, offer sophisticated functionality, such as formatted text input or password field behavior. There are file browsers and dialogs to suit most needs, and if not, customization is possible. If none of Swing's provided components are exactly what you need, you can leverage the basic Swing component functionality to create your own.

Java 2D API

To make your application stand out; convey information visually; or add figures, images, or animation to your GUI, you'll want to use the Java 2DTM API. Because Swing is built on the 2D package, it's trivial to make use of 2D within Swing components. Adding images, drop shadows, compositing — it's easy with Java 2D.

Pluggable Look-and-Feel Support

Any program that uses Swing components has a choice of look and feel. The JFC classes shipped by Sun and Apple provide a look and feel that matches that of the platform. The Synth package allows you to create your own look and feel. The GTK+ look and feel makes hundreds of existing look and feels available to Swing programs.

A program can specify the look and feel of the platform it is running on, or it can specify to always use the Java look and feel, and without recompiling, it will just work. Or, you can ignore the issue and let the UI manager sort it out.

Data Transfer

Data transfer, via cut, copy, paste, and drag and drop, is essential to almost any application. Support for data transfer is built into Swing and works between Swing components within an application, between Java applications, and between Java and native applications.

Internationalization

This feature allows developers to build applications that can interact with users worldwide in their own languages and cultural conventions. Applications can be created that accept input in languages that use thousands of different characters, such as Japanese, Chinese, or Korean.

Swing's layout managers make it easy to honor a particular orientation required by the UI. For example, the UI will appear right to left in a locale where the text flows right to left. This support is automatic: You need only code the UI once and then it will work for left to right and right to left, as well as honor the appropriate size of components that change as you localize the text.

Accessibility API

People with disabilities use special software — assistive technologies — that mediates the user experience for them. Such software needs to obtain a wealth of information about the running application in order to represent it in alternate media: for a screen reader to read the screen with synthetic speech or render it via a Braille display, for a screen magnifier to track the caret and keyboard focus, for on-screen keyboards to present dynamic keyboards of the menu choices and toolbar items and dialog controls, and for voice control systems to know what the user can control with his or her voice. The accessibility API enables these assistive technologies to get the information they need, and to programmatically manipulate the elements that make up the graphical user interface.

Undo Framework API

Swing's undo framework allows developers to provide support for undo and redo. Undo support is built in to Swing's text component. For other components, Swing supports an unlimited number of actions to undo and redo, and is easily adapted to an application. For example, you could easily enable undo to add and remove elements from a table.

Flexible Deployment Support

If you want your program to run within a browser window, you can create it as an applet and run it using Java Plug-in, which supports a variety of browsers, such as Internet Explorer, Firefox, and Safari. If you want to create a program that can be launched from a browser, you can do this with Java Web Start. Of course, your application can also run outside of browser as a standard desktop application.

For more information on deploying an application, see the Deployment trail in this tutorial.

This trail provides an overview of Swing capabilities, beginning with a demo that showcases many of these features. When you are ready to begin coding, the Creating a GUI With JFC/Swing trail provides the programming techniques to take advantage of these features.

Next, a demo shows many of these features.

public class JFrame

extends Frame

implements WindowConstants, Accessible, RootPaneContainer

An extended version of java.awt.Frame that adds support for the JFC/Swing component architecture. You can find task-oriented documentation about using JFrame in The Java Tutorial, in the section How to Make Frames.

The JFrame class is slightly incompatible with Frame. Like all other JFC/Swing top-level containers, a JFrame contains a JRootPane as its only child. The content pane provided by the root pane should, as a rule, contain all the non-menu components displayed by the JFrame. This is different from the AWT Frame case. As a conveniance add and its variants, remove and setLayout have been overridden to forward to the contentPane as necessary. This means you can write:

  frame.add(child);

And the child will be added to the contentPane. The content pane will always be non-null. Attempting to set it to null will cause the JFrame to throw an exception. The default content pane will have a BorderLayout manager set on it. Refer to RootPaneContainer for details on adding, removing and setting the LayoutManager of a JFrame.

Unlike a Frame, a JFrame has some notion of how to respond when the user attempts to close the window. The default behavior is to simply hide the JFrame when the user closes the window. To change the default behavior, you invoke the method setDefaultCloseOperation(int). To make the JFrame behave the same as a Frame instance, use setDefaultCloseOperation(WindowConstants.DO_NOTHING_ON_CLOSE).

For more information on content panes and other features that root panes provide, see Using Top-Level Containers in The Java Tutorial.

In a multi-screen environment, you can create a JFrame on a different screen device. See Frame for more information.

Reading, Writing, and Creating Files

This page discusses the details of reading, writing, creating, and opening files. Three of the methods used to create a new file enable you to specify an optional set of initial attributes for the file. For example, on a file system that supports the POSIX set of standards (such as UNIX), you can specify a file owner, group owner, or file permissions at the time the file is created. Before reading this page, first familiarize yourself with file attributes in Managing Metadata.
File I/O

The java.nio.file package supports stream I/O by using regular input stream and output stream API for reading and writing. This package is fully interoperable with the java.io package. The following sections cover this form of I/O:

  Reading a File by Using Stream I/O
  Creating and Writing a File by using Stream I/O

The java.nio.file package also supports channel I/O, which moves data in buffers, bypassing some of the layers that can bottleneck stream I/O. The channel I/O methods are covered in Reading and Writing Files by Using Channel I/O.

The other topic covered in this page is Creating Files.
Creating Files

You can create an empty file with an initial set of attributes by using the createFile(FileAttribute) method. For example, if, at the time of creation, you want a file to have particular set of file permissions, use the createFile method to do so. If you do not specify any attributes, the file is created with default attributes. If the file already exists, createFile throws an exception.

In a single atomic operation, the createFile method checks for the existence of the file and creates that file with the specified attributes, which makes the process more secure against malicious code.

The following code snippet creates a file with default attributes:

Path file = ...;

try {

  file.createFile();  //Create the empty file with default permissions, etc.

} catch (FileAlreadyExists x) {

  System.err.format("file named %s already exists%n", file);

} catch (IOException x) {

  //Some other sort of failure, such as permissions.

  System.err.format("createFile error: %s%n", x);

}

POSIX File Permissions has an example that uses createFile(FileAttribute) to create a file with pre-set permissions.

You can also create a new file by using the newOutputStream methods, as described in Creating and Writing a File using Stream I/O. If you open a new output stream and close it immediately, an empty file is created.
Reading a File by Using Stream I/O

To open a file for reading, you can use the newInputStream(OpenOption...) method. This method returns an unbuffered input stream for reading bytes from the file.

Path file = ...;

InputStream in = null;

try {

  in = file.newInputStream();

  BufferedReader reader = new BufferedReader(new InputStreamReader(in));

  String line = null;

  while ((line = reader.readLine()) != null) {

  System.out.println(line);

  }

} catch (IOException x) {

  System.err.println(x);

} finally {

  if (in != null) in.close();

}

Creating and Writing a File by Using Stream I/O

You can create a file, append to a file, or write to a file by using one of the newOutputStream methods:

  newOutputStream(OpenOption...)
  newOutputStream(Set, FileAttribute...)

These methods open or create a file for writing bytes and return an unbuffered output stream. Both methods take a list of OpenOption options. The second method enables you to specify initial file attributes, similar to the createFile(FileAttribute) method. The first method takes the options as a varargs argument, and the second method takes the options as a Set. This difference might seem confusing, but a method can only accept a single varargs argument.

The following StandardOpenOptions enums are supported:

  WRITE – Opens the file for write access.
  APPEND – Appends the new data to the end of the file. This option is used with the WRITE or CREATE options.
  TRUNCATE_EXISTING – Truncates the file to zero bytes. This option is used with the WRITE option.
  CREATE_NEW – Creates a new file and throws an exception if the file already exists.
  CREATE – Opens the file if it exists or creates a new file if it does not.
  DELETE_ON_CLOSE – Deletes the file when the stream is closed. This option is very useful for temporary files.
  SPARSE – Hints that a newly created file will be sparse. This advanced option is honored on some file systems, such as NTFS, where large files with data "gaps" can be stored in a more efficient manner where those empty gaps do not consume disk space.
  SYNC – Keeps the file (both content and metadata) synchronized with the underlying storage device.
  DSYNC – Keeps the file content synchronized with the underlying storage device.

If no open options are specified, and the file does not exist, a new file is created. If the file exists, it is truncated. This optoin is equivalent to invoking the method with the CREATE and TRUNCATE_EXISTING options.

The following code snippet opens a log file. If the file does not exist, it is created. If the file exists, it is opened for appending.

import static java.nio.file.StandardOpenOption.*;

Path logfile = ...;

//Convert the string to a byte array.

String s = ...;

byte data[] = s.getBytes();

OutputStream out = null;

try {

  out = new BufferedOutputStream(logfile.newOutputStream(CREATE, APPEND));

  ...

  out.write(data, 0, data.length);

} catch (IOException x) {

  System.err.println(x);

} finally {

  if (out != null) {

  out.flush();

  out.close();

  }

}

Reading and Writing Files by Using Channel I/O

While stream I/O reads a character at a time, channel I/O reads a buffer at a time. The ByteChannel interface provides basic read and write functionality. A SeekableByteChannel is a ByteChannel that has the capability to maintain a position in the channel and to change that position. A SeekableByteChannel also supports truncating the file associated with the channel and querying the file for its size.

The capability to move to different points in the file and then read from or write to that location makes random access of a file possible. See Random Access Files for more information.

There are two methods for reading and writing channel I/O. The method signatures for the newByteChannel methods are almost identical to the newOutputStream methods, and they are invoked similarly.

  newByteChannel(OpenOption...)
  newByteChannel(Set, FileAttribute...)

Note: The newByteChannel methods return an instance of a SeekableByteChannel. With a default file system, you can cast this seekable byte channel to a FileChannel providing access to more advanced features such mapping a region of the file directly into memory for faster access, locking a region of the file so other processes cannot access it, or reading and writing bytes from an absolute position without affecting the channel's current position.

Both newByteChannel methods enable you to specify a list of OpenOption options. The same open options used by the newOutputStream methods are supported, in addition to one more option: READ is required because the SeekableByteChannel supports both reading and writing.

Specifying READ opens the channel for reading. Specifying WRITE or APPEND opens the channel for writing. If none of these options is specified, the channel is opened for reading.

The following code snippet reads a file and prints it to standard output:

SeekableByteChannel sbc = null;

try {

  sbc = file.newByteChannel();  //Defaults to READ

  ByteBuffer buf = ByteBuffer.allocate(10);

  //Read the bytes with the proper encoding for this platform.

  //If you skip this step, you might see something that looks like Chinese

  //characters when you expect Latin-style characters.

  String encoding = System.getProperty("file.encoding");

  while (sbc.read(buf) > 0) {

  buf.rewind();

  System.out.print(Charset.forName(encoding).decode(buf));

  buf.flip();

  }

} catch (IOException x) {

  System.out.println("caught exception: " + x);

} finally {

  if (sbc != null) sbc.close();

}

The following code snippet, written for UNIX and other POSIX file systems, creates a log file with a specific set of file permissions. This code creates a log file or appends to the log file if it already exists. The log file is created with read/write permissions for owner and read only permissions for group.

import static java.nio.file.StandardCopyOption.*;

//Create the set of options for appending to the file.

Set<OpenOptions> options = new HashSet<OpenOption>();

options.add(APPEND);

options.add(CREATE);

//Create the custom permissions attribute.

Set<PosixFilePermission> perms = PosixFilePermissions.fromString("rw-r------");

FileAttribute<Set<PosixFilePermission>> attr = PosixFilePermissions.asFileAttribute(perms);

//Convert the string to a ByetBuffer.

String s = ...;

byte data[] = s.getBytes();

ByteBuffer bb = ByteBuffer.wrap(data);

SeekableByteChannel sbc = null;

try {

  sbc = file.newByteChannel(options, attr);

  sbc.write(bb);

} catch (IOException x) {

  System.out.println("exception thrown: " + x);

} finally {

  if (sbc != null) sbc.close();

}