JDBC

The introduction of the Java language is required before introducing the concept of JDBC. Java is an object-oriented programming language. It incorporates all the objected-oriented concepts such as polymorphism, abstraction, encapsulation, etc. Java is a platform independent language because it can run on different operating systems. Java syntactically resembles C and C++, but it is simpler than the two, as it does not contain pointers and multiple inheritance. Both pointers and multiple inheritance seem complex to the application developer.

Java has some drawbacks too. It does not provide much facility for developing GUI (graphical user interface) applications. The earlier version of Java did not contain any support for database access. This was the major drawback of this language, as in today’s world, keeping and managing databases is quite important. To remove this drawback, Sun Microsystems has introduced a new interface known as Java Database Connectivity (JDBC). JDBC provides database connectivity to Java applications.

JDBC Interface

JDBC stands for Java Database Connectivity. It provides database access to Java applications. The JDBC API interfaces are used to connect to the database and execute all types of SQL statements. The JDBC API interfaces can execute normal SQL statements, dynamic SQL statements, and stored procedures that take both IN and OUT parameters. The section below provides a description of various JDBC API interfaces.

CallableStatement Interface: This interface provides methods for executing stored procedures that return OUT parameter values. The CallableStatement object inherits the PreparedStatement object, adds various methods for registering parameters to be OUT parameters, and provides methods to get the parameters passed back from the stored procedure.

Connection Interface: This provides database connection to Java applications. It can be used to create various Statement objects for executing SQL statements and stored procedures. It also provides the facility to set the transaction properties for the connection.

DatabaseMetaData: This interface provides various methods for obtaining the information about the database. It provides listing of tables for a specific database, primary keys, columns, and other information related to specific tables.

Driver Interface: This is a database specific Driver object, which the JDBC vendor provides. It contains information about connecting Java applications to the database. It also provides information about the database, such as version of the database, etc.

PreparedStatement Interface: This interface supports the execution of dynamic SQL statements and stored procedures. It allows setting various parameters in dynamic statements with specified data values.

Note: Dynamic statements are those statements whose values are not known at the time of creation.

ResultSet Interface: This object is used to get information from a SQL SELECT statement. The SQL SELECT statement returns the cursor, which is used by the ResultSet interface to search the results returned by the SELECT statement. It provides a set of methods for extracting information from different columns contained in the cursor.

ResultSetMetadata: This interface provides information about a returned result set. It is created from a ResultSet object and provides information specific to that object. It provides the information about the number of columns in the result set such as names, and types of the column.

Statement Interface: This is created from the Connection object. It is used to execute standard SQL statements and stored procedures. It provides two main methods, executeQuery() and executeUpdate(). These methods support execution of SQL queries and SQL updates. The executeQuery() method returns a ResultSet object.

JDBC API Objects

Java provides various objects that can be used in Java applications. These objects provide Java with some of the database specific data types available in most databases. The table below describes various JDBC API objects:

JDBC Exceptions

Whenever an error occurs in Java, an exception is thrown. The JDBC API introduces three new exceptions. These exceptions are discussed as follows:

DataTruncation Exception: This exception is thrown when JDBC unintentionally truncates a data value. The exception provides methods to get information about the data value that was truncated and the truncation error as well.

SQLException Exception: This is thrown by almost all the methods in the JDBC API. It provides methods for getting information about the error and the present state of the SQL transaction.

SQLWarning Exception: This exception is generated when the database issues a warning. The warnings are silently sent to the object whose method caused it to be reported.

JDBC Vs CGI

Before the introduction of JDBC, the Java applications used to call and access CGI (Common Gateway Interface) programs through streams in Java. The CGI scripts in Java call a separate program that provides database access and returns results. The CGI approach is slow and allows bugs to get into the applications. In addition to this, one must know both the technologies – Java and CGI – to make applications, whereas to access the database through JDBC, one must be a master in Java only.

Another strong reason to use JDBC is that it provides faster access than the CGI approach. In CGI approach, another program is called that accesses the database, processes the data, and returns the result back to the calling program in a stream. This requires multiple levels of processing that increase wait time and also allow more bugs to creep into the applications.

The figure below demonstrates the way CGI accesses the database.

When a CGI script is called, a new script is executed through a Web server, whereas execution of a JDBC statement against the database requires only some sort of server that passes the SQL commands through to the database. This increases the speed of execution of SQL statements. The CGI script first connects to the database and processes the results, whereas the JDBC allows the connection of the database to Java applications so that it can perform all the processing.

The figure below demonstrates how a JDBC statement is executed:

• Previous Entry: What does the COUNT method do?
• Next Entry: What is a query string?