JDBC Tutorials and Guide

java.sql.SQLException: Can’t call commit when autocommit=true Exception

May 3, 2014 by Krishna Srinivasan Leave a Comment

When you work with JDBC application, by default the auto commit is set to true, after the transaction is completed all the transactions are committed to the database. When it is set as true, you can not explicitly call the commit method. If you call the connection.commit() method, then the below exception will be thrown.

[code]
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Exception in thread "main" java.lang.RuntimeException: java.sql.SQLException: Can’t call commit when autocommit=true
at javabeat.net.spring.core.Country.insertCountryName(Country.java:54)
at javabeat.net.spring.core.SpringStandAloneExample.main(SpringStandAloneExample.java:11)
Caused by: java.sql.SQLException: Can’t call commit when autocommit=true
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:914)
at com.mysql.jdbc.Connection.commit(Connection.java:2275)
at javabeat.net.spring.core.Country.insertCountryName(Country.java:51)
[/code]

Connect To MySQL With JDBC Driver

November 11, 2013 by Krishna Srinivasan Leave a Comment

Here is an example to connect your JDBC code to MySQl database. You have to download mysql.jar for the JDBC driver class from here.

[code]
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
conn = DriverManager.getConnection("jdbc:mysql://hostname:port/dbname","username", "password");
conn.close();
[/code]

[code lang=”java”]
package javabeat.net.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySQLConnectionExample {
public static void main(String[] argv) {

try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("Problem in loading the MySQL Driver class!!!");
e.printStackTrace();
return;
}

Connection connection = null;

try {
//Get the connection object
connection = DriverManager
.getConnection("jdbc:mysql://<host>:3306/<dbname>","<username>", "<password>");

} catch (SQLException e) {
System.out.println("Problem in establishing connection!!");
e.printStackTrace();
return;
}

if (connection != null) {
System.out.println("Connection created successfully!!");
} else {
System.out.println("Problem in establishing connection!!");
}
}
}

[/code]

If you are not copying the mysql.jar file in the classpath, you will get the following error.

[code]
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:188)
at javabeat.net.db.MySQLConnectionExample.main(MySQLConnectionExample.java:11)
[/code]

New features in JDBC 3.0

June 18, 2012 by Krishna Srinivasan Leave a Comment

This article provides an introduction to the array of core new features available in JDBC 3.0. More specifically, the features ‘supporting save points’, ‘using parameter metadata’, ‘updating large objects’ and ‘auto generated keys’ are discussed. Wherever possible, to get a hang of it, relevant code samples have been provided in the respective sections.

Download Source Code: [download id=”1″]

Defining savepoints

It is possible to programmatically control the creation and releasing of save points through JDBC 3.0. But before doing it, one must check whether the underlying database supports the concepts of savepoints. Save points provide multiple-level-control of database commit or rollback operations within a single transaction. For example, if two unrelated database operations have to happen with a single transaction, these two save points can be created for the very purpose. Then based on various business conditions, commit/rollback can be done to any of these save points within the same transaction. To illustrate the usage, consider the following example,

also read:

Java Tutorials

Java EE Tutorials

Design Patterns Tutorials

Java File IO Tutorials

[code lang=”java”]
package net.javabeat.jdbc3.newfeatures.savepoint;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.Savepoint;

import net.javabeat.jdbc3.newfeatures.CommonUtils;

public class SavepointTest {
public static void main(String[] args) throws Exception{
Connection connection = CommonUtils.getConnection();
DatabaseMetaData databaseMetadata = connection.getMetaData();
if (databaseMetadata.supportsSavepoints()){
System.out.println("Savepoints supported by
the target database");
processTable(connection);
}
}

private static void processTable(Connection connection)
throws Exception{
PreparedStatement pStatement = connection.prepareStatement("
INSERT INTO CUSTOMER VALUES (?, ?)");
pStatement.setInt(1, 1);
pStatement.setString(2, "New Customer");
pStatement.execute();
Savepoint countrySavepoint =
connection.setSavepoint("country");
pStatement = connection.prepareStatement("
INSERT INTO COUNTRY VALUES (?, ?)");
pStatement.setInt(1, 1);
pStatement.setString(2, "India");
// Make this transaction to fail deliberately
connection.rollback(countrySavepoint);
connection.commit();
}
}
[/code]

As seen from the above example, the program checks whether save points are supported by the target database by querying methods available in DatabaseMetadata. Note for save points are created by calling the method setSavePoint() defined on the Connection object. A save point can be given a name so that at a later point of time, a transaction can be made to commit or rollback based on the name. In the example code, to illustrate the usage of save point, we deliberately rollback the operation related to customer table, whereas, the changes made to country table are committed.

Parameter Metadata

Construction of queries for statements such as prepared statements might involve specifying dynamic values through ‘?’. Previously, there was no support for the tools or for the applications to identify the parameter information embedded in the queries. Now, support has been added to retrieve the parameter information that is passed to prepared statements. Please refer the below code that illustrates the usage,

[code lang=”java”]
package net.javabeat.jdbc3.newfeatures.pmd;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;

import net.javabeat.jdbc3.newfeatures.CommonUtils;

public class PMDTest {
public static void main(String[] args) throws Exception{
Connection connection = CommonUtils.getConnection();
PreparedStatement pStatement = connection.prepareStatement(
"INSERT INTO CUSTOMER VALUES (?, ?)");
pStatement.setInt(1, 1);
pStatement.setString(2, "New Customer");
ParameterMetaData parameterMetadata = pStatement.getParameterMetaData();
int parameterCount = parameterMetadata.getParameterCount();
for (int index = 1; index <= parameterCount; index ++){
String parameterClassName = parameterMetadata.getParameterClassName(index);
int parameterMode = parameterMetadata.getParameterMode(index);
String paramterTypeName = parameterMetadata.getParameterTypeName(index);
System.out.println(parameterClassName + "/" + parameterMode + "/"
+ "/" + paramterTypeName);
}
}
}
[/code]

Auto Generated Keys

Execution of queries sometimes results in the automatic generation of fields or values for tables. The behavior is entirely dependent on the underlying database implementation. Previously, there was no standard mechanism for retrieving such information once the query is executed. Application developers are forced to use non-standard mechanisms for fetching information that happens outside the scope of query execution. For example, consider a table column containing a particular column, say ‘name’, and an equivalent upper column, let’s say ‘upper_name’. The value for the ‘upper_name’ will be automatically populated as soon as the value is populated for the ‘name’ column. This is one of the examples of auto generation feature and the newer specification supports this feature. Refer the code sample below,

[code lang=”java”]
package net.javabeat.jdbc3.newfeatures.autogenkey;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;

import net.javabeat.jdbc3.newfeatures.CommonUtils;

public class AutoGeneratedKeyTest {
public static void main(String[] args) throws Exception{
Connection connection = CommonUtils.getConnection();
DatabaseMetaData databaseMetadata = connection.getMetaData();
if (databaseMetadata.supportsGetGeneratedKeys()){
processAutoGeneratedKeys(connection);
}
}

private static void processAutoGeneratedKeys(Connection connection)
throws Exception{
System.out.println("Auto generated keys can be retrived");
Statement statement = connection.createStatement();
statement.execute("INSERT INTO ADDRESSINFORMATION (ADDRESSDATA ) " +
"VALUES (‘Address Text’)", Statement.RETURN_GENERATED_KEYS);
ResultSet resultSet = statement.getGeneratedKeys();
java.sql.ResultSetMetaData rsMetadata = resultSet.getMetaData();
for (int index = 1; index <= rsMetadata.getColumnCount(); index ++){
while (resultSet.next()){
String columnLabel = rsMetadata.getColumnName(index);
System.out.println(resultSet.getObject(columnLabel));
}
}
}

}
[/code]

Note that, while executing the statement, the application has to provide indication to the underlying engine that, if any auto generation columns are applicable once the query is executed, those information have to be made available in the equivalent statement object. This is done through the method Statement.execute() where the second parameter specifies this option. Next, the method getGeneratedKeys() is added to the Statement object which returns a ResultSet containing the desired values. Because, this feature may or may not be supported by the database engine, the API supportsGeteGeneratedKeys() can be used to check the feature availability.

Updating clob/blob objects

The support to update clob objects is directly available on the Clob/Blob objects. The method updateClob() is added to the ResultSet object. Previously, there is no standard way to update large data objects. Note to ensure that this works and because the operation is defined on the ResultSet, the underlying ResultSet object should support updating the record. This is possible if the ResultSet is a flavor of CONCUR_UPDATABLE. Please refer the below code,

[code lang=”java”]
package net.javabeat.jdbc3.newfeatures.clblupdate;

import java.io.StringReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import net.javabeat.jdbc3.newfeatures.CommonUtils;

public class ClobTest {

public static void main(String[] args) throws Exception{
Connection connection = CommonUtils.getConnection();
int key = 1;
insertClob(connection, key);
readAndUpdateClob(connection, key);
}

private static void insertClob(Connection connection, int key)
throws Exception{
String sql = "INSERT INTO CLOBTEST VALUES (?, ?)";
PreparedStatement pStatement = connection.prepareStatement(sql);
pStatement.setInt(1, key);
StringReader reader = new StringReader("som big data");
pStatement.setClob(2, reader);
pStatement.execute();
}

private static void readAndUpdateClob(Connection connection, int key)
throws Exception{
String sql = "SELECT DATA FROM CLOBTEST WHERE ID = ?";
PreparedStatement pStatement = connection.prepareStatement(
sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
pStatement.setInt(1, key);
ResultSet resultSet = pStatement.executeQuery();
if (resultSet.next()){
Clob clobObject = resultSet.getClob(1);
clobObject.setString(1, "new big data");
resultSet.updateClob(1, clobObject);
}
}
}
[/code]

Conclusion

Download Source Code: [download id=”1″]

The new features in JDBC 3.0 have addressed most of the common problems that tools or developer community is encountering, thereby providing a unified way of solution. There are other minor updates done to JDBC 3.0, such as ‘configuring connection pools’, ‘adding of new data types as Boolean, Datalink, URL’ etc.

JDBC Interview Questions

February 13, 2009 by Krishna Srinivasan Leave a Comment

1) I have the choice of manipulating database data using a byte[] or a java.sql.Blob. Which has best performance?

java.sql.Blob, since it does not extract any data from the database until you explicitly ask it to. The Java platform 2 type Blob wraps a database locator (which is essentially a pointer to byte). That pointer is a rather large number (between 32 and 256 bits in size) – but the effort to extract it from the database is insignificant next to extracting the full blob content. For insertion into the database, you should use a byte[] since data has not been uploaded to the database yet. Thus, use the Blob class only for extraction.

Conclusion: use the java.sql.Blob class for extraction whenever you can.

2) I have the choice of manipulating database data using a String or a java.sql.Clob. Which has best performance?

java.sql.Clob, since it does not extract any data from the database until you explicitly ask it to. The Java platform 2 type Clob wraps a database locator (which is essentially a pointer to char). That pointer is a rather large number (between 32 and 256 bits in size) – but the effort to extract it from the database is insignificant next to extracting the full Clob content. For insertion into the database, you should use a String since data need not been downloaded from the database. Thus, use the Clob class only for extraction.

Conclusion: Unless you always intend to extract the full textual data stored in the particular table cell, use the java.sql.Clob class for extraction whenever you can.

3) Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?

If your autoCommit flag (managed by the Connection.setAutoCommit method) is false, you are required to call the commit() method – and vice versa.

4) How can I retrieve only the first n rows, second n rows of a database using a particular WHERE clause ? For example, if a SELECT typically returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows and so on ?

Use the Statement.setFetchSize method to indicate the size of each database fetch. Note that this method is only available in the Java 2 platform. For Jdk 1.1.X and Jdk 1.0.X, no standardized way of setting the fetch size exists. Please consult the Db driver manual.

5) What does ResultSet actually contain? Is it the actual data of the result or some links to databases? If it is the actual data then why can’t we access it after connection is closed?

A ResultSet is an interface. Its implementation depends on the driver and hence ,what it “contains” depends partially on the driver and what the query returns.

For example with the Odbc bridge what the underlying implementation layer contains is an ODBC result set. A Type 4 driver executing a stored procedure that returns a cursor – on an oracle database it actually returns a cursor in the database. The oracle cursor can however be processed like a ResultSet would be from the client.

Closing a connection closes all interaction with the database and releases any locks that might have been obtained in the process.

6) What are SQL3 data types?

The next version of the ANSI/ISO SQL standard defines some new datatypes, commonly referred to as the SQL3 types. The primary SQL3 types are:

STRUCT: This is the default mapping for any SQL structured type, and is manifest by the java.sql.Struct type.

REF: Serves as a reference to SQL data within the database. Can be passed as a parameter to a SQL statement. Mapped to the java.sql.Ref type.

BLOB: Holds binary large objects. Mapped to the java.sql.Blob type.

CLOB: Contains character large objects. Mapped to the java.sql.Clob type.

ARRAY: Can store values of a specified type. Mapped to the java.sql.Array type.

You can retrieve, store and update SQL3 types using the corresponding getXXX(), setXXX(), and updateXXX() methods defined in ResultSet interface

7) How can I manage special characters (for example: ” _ ‘ % ) when I execute an INSERT query? If I don’t filter the quoting marks or the apostrophe, for example, the SQL string will cause an error.

The characters “%” and “_” have special meaning in SQL LIKE clauses (to match zero or more characters, or exactly one character, respectively). In order to interpret them literally, they can be preceded with a special escape character in strings, e.g. “\”. In order to specify the escape character used to quote these characters, include the following syntax on the end of the query:
[code lang=”java”] {escape ‘escape-character’}[/code]
For example, the query

SELECT NAME FROM IDENTIFIERS WHERE ID LIKE ‘\_%’ {escape ‘\’}

finds identifier names that begin with an underbar.

8) What is SQLJ and why would I want to use it instead of JDBC?

SQL/J is a technology, originally developed by Oracle Corporation, that enables you to embed SQL statements in Java. The purpose of the SQLJ API is to simplify the development requirements of the JDBC API while doing the same thing. Some major databases (Oracle, Sybase) support SQLJ, but others do not. Currently, SQLJ has not been accepted as a standard, so if you have to learn one of the two technologies, I recommend JDBC.

9) How do I insert an image file (or other raw data) into a database?

All raw data types (including binary documents or images) should be read and uploaded to the database as an array of bytes, byte[]. Originating from a binary file,

Read all data from the file using a FileInputStream.
Create a byte array from the read data.
Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.

10) How can I pool my database connections so I don’t have to keep reconnecting to the database?

you gets a reference to the pool
you gets a free connection from the pool
you performs your different tasks
you frees the connection to the pool

Since your application retrieves a pooled connection, you don’t consume your time to connect / disconnect from your data source.

11) Will a call to PreparedStatement.executeQuery() always close the ResultSet from the previous executeQuery()?

A ResultSet is automatically closed by the Statement that generated it when that Statement is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.

12) How do I upload SQL3 BLOB & CLOB data to a database?

Although one may simply extract BLOB & CLOB data from the database using the methods of the java.sql.CLOB and java.sql.BLOB, one must upload the data as normal java datatypes. The example below inserts a BLOB in the form of a byte[] and a CLOB in the form of a String into the database
Inserting SQL3 type data [BLOB & CLOB]
[code lang=”java”]
private void runInsert() {
try {
// Log
this.log("Inserting values … ");
// Open a new Statement
PreparedStatement stmnt = conn.prepareStatement( "insert Lobtest (image, name) values (?, ?)"); // Create a timestamp to measure the insert time
Date before = new java.util.Date(); for(int i = 0; i < 500; i++) {
// Set parameters
stmnt.setBytes(1, blobData); stmnt.setString(2, "i: " + i + ";" + clobData);
// Perform insert
int rowsAffected = stmnt.executeUpdate(); }
// Get another timestamp to complete the time measurement
Date after = new java.util.Date();
this.log(" … Done!");
log("Total run time: " + ( after.getTime() – before.getTime()));
// Close database resources
stmnt.close(); }
catch(SQLException ex) { this.log("Hmm… " + ex); } } [/code]

13) What is the difference between client and server database cursors?

What you see on the client side is the current row of the cursor which called a Result (ODBC) or ResultSet (JDBC). The cursor is a server-side entity only and remains on the server side.

14) Are prepared statements faster because they are compiled? if so, where and when are they compiled?

Prepared Statements aren’t actually compiled, but they are bound by the JDBC driver. Depending on the driver, Prepared Statements can be a lot faster – if you re-use them. Some drivers bind the columns you request in the SQL statement. When you execute Connection.prepareStatement(), all the columns bindings take place, so the binding overhead does not occur each time you run the Prepared Statement. For additional information on Prepared Statement performance and binding see JDBC Performance Tips on IBM’s website.

15) Is it possible to connect to multiple databases simultaneously? Can one extract/update data from multiple databases with a single statement?

In general, subject, as usual, to the capabilities of the specific driver implementation, one can connect to multiple databases at the same time. At least one driver ( and probably others ) will also handle commits across multiple connections. Obviously one should check the driver documentation rather than assuming these capabilities.

As to the second part of the question, one needs special middleware to deal with multiple databases in a single statement or to effectively treat them as one database. DRDA ( Distributed Relational Database Architecture — I, at least, make it rhyme with “Gerta” ) is probably most commonly used to accomplish this.

Oracle has a product called Oracle Transparent Gateway for IBM DRDA and IBM has a product called DataJoiner that make multiple databases appear as one to your application. No doubt there are other products available. XOpen also has papers available regarding DRDA.

16) Why do I get an UnsupportedOperationException?

JDBC 2.0, introduced with the 1.2 version of Java, added several capabilities to JDBC. Instead of completely invalidating all the older JDBC 1.x drivers, when you try to perform a 2.0 task with a 1.x driver, an UnsupportedOperationException will be thrown. You need to update your driver if you wish to use the new capabilities.

17) What advantage is there to using prepared statements if I am using connection pooling or closing the connection frequently to avoid resource/connection/cursor limitations?

The ability to choose the ‘best’ efficiency ( or evaluate tradeoffs, if you prefer, ) is, at times, the most important piece of a mature developer’s skillset. This is YAA ( Yet Another Area, ) where that maxim applies. Apparently there is an effort to allow prepared statements to work ‘better’ with connection pools in JDBC 3.0, but for now, one loses most of the original benefit of prepared statements when the connection is closed. A prepared statement obviously fits best when a statement differing only in variable criteria is executed over and over without closing the statement.

However, depending on the DB engine, the SQL may be cached and reused even for a different prepared statement and most of the work is done by the DB engine rather than the driver. In addition, prepared statements deal with data conversions that can be error prone in straight ahead, built on the fly SQL; handling quotes and dates in a manner transparent to the developer, for example.

18) What is JDBC, anyhow?

JDBC is Java’s means of dynamically accessing tabular data, and primarily data in relational databases, in a generic manner, normally using standard SQL statements.

19) Can I reuse a Statement or must I create a new one for each query?

When using a JDBC compliant driver, you can use the same Statement for any number of queries. However, some older drivers did not always “respect the spec.” Also note that a Statement SHOULD automatically close the current ResultSet before executing a new query, so be sure you are done with it before re-querying using the same Statement.

20) What is a three-tier architecture?

A three-tier architecture is any system which enforces a general separation between the following three parts:

Client Tier or user interface
Middle Tier or business logic
Data Storage Tier

Applied to web applications and distributed programming, the three logical tiers usually correspond to the physical separation between three types of devices or hosts:

21) What separates one tier from another in the context of n-tiered architecture?

It depends on the application.

In a web application, for example, where tier 1 is a web-server, it may communicate with a tier 2 Application Server using RMI over IIOP, and subsequently tier 2 may communicate with tier 3 (data storage) using JDBC, etc.

Each of these tiers may be on separate physical machines or they may share the same box.

The important thing is the functionality at each tier.

Tier 1 – Presentation – should be concerned mainly with display of user interfaces and/or data to the client browser or client system.
Tier 2 – Application – should be concerned with business logic

Tier 3+ – Storage/Enterprise Systems – should be focused on data persistence and/or communication with other Enterprise Systems.

22) What areas should I focus on for the best performance in a JDBC application?

These are few points to consider:

Use a connection pool mechanism whenever possible.
Use prepared statements. These can be beneficial, for example with DB specific escaping, even when used only once.
Use stored procedures when they can be created in a standard manner. Do watch out for DB specific SP definitions that can cause migration headaches.
Even though the jdbc promotes portability, true portability comes from NOT depending on any database specific data types, functions and so on.
Select only required columns rather than using select * from Tablexyz.
Always close Statement and ResultSet objects as soon as possible.
Write modular classes to handle database interaction specifics.
Work with DatabaseMetaData to get information about database functionality.
Softcode database specific parameters with, for example, properties files.
Always catch AND handle database warnings and exceptions. Be sure to check for additional pending exceptions.
Test your code with debug statements to determine the time it takes to execute your query and so on to help in tuning your code. Also use query plan functionality if available.
Use proper ( and a single standard if possible ) formats, especially for dates.
Use proper data types for specific kind of data. For example, store birthdate as a date type rather than, say, varchar.

23) How can I insert multiple rows into a database in a single transaction?

[code lang=”java”] //turn off the implicit commit
Connection.setAutoCommit(false);
//..your insert/update/delete goes here
Connection.Commit(); a new transaction is implicitly started. [/code]

24) How do I convert a java.sql.Timestamp to a java.util.Date?

While Timesteamp extends Date, it stores the fractional part of the time within itself instead of within the Date superclass. If you need the partial seconds, you have to add them back in.
[code lang=”java”] Date date = new Date(ts.getTime() + (ts.getNanos() / 1000000 )); [/code]

25) What is SQL?

SQL is a standardized language used to create, manipulate, examine, and manage relational databases.

26) Is Class.forName(Drivername) the only way to load a driver? Can I instantiate the Driver and use the object of the driver?

Yes, you can use the driver directly. Create an instance of the driver and use the connect method from the Driver interface. Note that there may actually be two instances created, due to the expected standard behavior of drivers when the class is loaded.

27) What’s new in JDBC 3.0?

Probably the new features of most interest are:

Savepoint support
Reuse of prepared statements by connection pools
Retrieval of auto-generated keys
Ability to have multiple open ResultSet objects
Ability to make internal updates to the data in Blob and Clob objects
Ability to Update columns containing BLOB, CLOB, ARRAY and REF types
Both java.sql and javax.sql ( JDBC 2.0 Optional Package ) are expected to be included with J2SE 1.4.

28) Why do I get the message “No Suitable Driver”?

Often the answer is given that the correct driver is not loaded. This may be the case, but more typically, the JDBC database URL passed is not properly constructed. When a Connection request is issued, the DriverManager asks each loaded driver if it understands the URL sent. If no driver responds that it understands the URL, then the “No Suitable Driver” message is returned.

29) When I create multiple Statements on my Connection, only the current Statement appears to be executed. What’s the problem?

All JDBC objects are required to be threadsafe. Some drivers, unfortunately, implement this requirement by processing Statements serially. This means that additional Statements are not executed until the preceding Statement is completed.

30) Can a single thread open up mutliple connections simultaneously for the same database and for same table?

The general answer to this is yes. If that were not true, connection pools, for example, would not be possible. As always, however, this is completely dependent on the JDBC driver.

You can find out the theoretical maximum number of active Connections that your driver can obtain via the DatabaseMetaData.getMaxConnections method.

31) Can I ensure that my app has the latest data?

Typically an application retrieves multiple rows of data, providing a snapshot at an instant of time. Before a particular row is operated upon, the actual data may have been modified by another program. When it is essential that the most recent data is provided, a JDBC 2.0 driver provides the ResultSet.refreshRow method.

32) What does normalization mean for java.sql.Date and java.sql.Time?

These classes are thin wrappers extending java.util.Date, which has both date and time components. java.sql.Date should carry only date information and a normalized instance has the time information set to zeros. java.sql.Time should carry only time information and a normalized instance has the date set to the Java epoch ( January 1, 1970 ) and the milliseconds portion set to zero.

33) What’s the best way, in terms of performance, to do multiple insert/update statements, a PreparedStatement or Batch Updates?

Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.

A CallableStatement object provides a way to call stored procedures in a standard manner for all DBMSes. Their execution can be faster than that of PreparedStatement object.

Batch updates are used when you want to execute multiple statements together. Actually, there is no conflict here. While it depends on the driver/DBMS engine as to whether or not you will get an actual performance benefit from batch updates, Statement, PreparedStatement, and CallableStatement can all execute the addBatch() method.

34) What is JDO?

JDO provides for the transparent persistence of data in a data store agnostic manner, supporting object, hierarchical, as well as relational stores.

35) What is the difference between setMaxRows(int) and SetFetchSize(int)? Can either reduce processing time?

setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows. The method in the java.sql.Statement interface will set the ‘default’ value for all the ResultSet derived from that Statement; the method in the java.sql.ResultSet interface will override that value for a specific ResultSet. Since database fetches can be expensive in a networked environment, fetch size has an impact on performance.

setMaxRows(int) sets the limit of the maximum nuber of rows in a ResultSet object. If this limit is exceeded, the excess rows are “silently dropped”. That’s all the API says, so the setMaxRows method may not help performance at all other than to decrease memory usage. A value of 0 (default) means no limit.

36) What is DML?

DML is an abbreviation for Data Manipulation Language. This portion of the SQL standard is concerned with manipulating the data in a database as opposed to the structure of a database. The core verbs for DML are SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK.

37) What is DDL?

DDL is an abbreviation for Data Definition Language. This portion of the SQL standard is concerned with the creation, deletion and modification of database objects like tables, indexes and views. The core verbs for DDL are CREATE, ALTER and DROP. While most DBMS engines allow DDL to be used dynamically ( and available to JDBC ), it is often not supported in transactions.

38) How can I get information about foreign keys used in a table?

DatabaseMetaData.getImportedKeys() returns a ResultSet with data about foreign key columns, tables, sequence and update and delete rules.

39) How do I disallow NULL values in a table?

Null capability is a column integrity constraint, normally aplied at table creation time. Note that some databases won’t allow the constraint to be applied after table creation. Most databases allow a default value for the column as well. The following SQL statement displays the NOT NULL constraint:
[code] CREATE TABLE CoffeeTable ( Type VARCHAR(25) NOT NULL, Pounds INTEGER NOT NULL, Price NUMERIC(5, 2) NOT NULL ) [/code]

40) What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?

The answer depends on both your code and the DBMS. If the program does not explicitly set the isolation level, the DBMS default is used. You can determine the default using DatabaseMetaData.getDefaultTransactionIsolation() and the level for the current Connection with Connection.getTransactionIsolation(). If the default is not appropriate for your transaction, change it with Connection.setTransactionIsolation(int level).

JDBC connection in JDeveloper with MySQL database

November 25, 2008 by Krishna Srinivasan Leave a Comment

The Java Database Connectivity (JDBC) API is used to access a SQL database from a Java application. JDBC also supports tabular data sources, such as a spreadsheet. Oracle JDeveloper is a free Integrated Development Environment (IDE) for modeling, developing, debugging, optimizing, and deploying Java applications. JDeveloper 10g is used to develop J2EE applications comprising the JSPs, EJBs, Struts, Servlets, and the Java classes that may require accessing a database table in the Oracle 10g Database, or a third-party database. In this article by Deepak Vohra, we will see how to configure JDBC in the JDeveloper IDE.

also read:

Java Tutorials

Java EE Tutorials

Design Patterns Tutorials

Java File IO Tutorials

Unlike Eclipse IDE, which requires a plug-in, JDeveloper has a built-in provision to establish a JDBC connection with a database. JDeveloper is the only Java IDE with an embedded application server, the Oracle Containers for J2EE (OC4J). This database-based web application may run in JDeveloper without requiring a third-party application server. However, JDeveloper also supports third-party application servers. Starting with JDeveloper 11, application developers may point the IDE to an application server instance (or OC4J instance), including third-party application servers that they want to use for testing during development. JDeveloper provides connection pooling for the efficient use of database connections. A database connection may be used in an ADF BC application, or in a JavaEE application.

A database connection in JDeveloper may be configured in the Connections Navigator. A Connections Navigator connection is available as a DataSource registered with a JNDI naming service. The database connection in JDeveloper is a reusable named connection that developers configure once and then use in as many of their projects as they want. Depending on the nature of the project and the database connection, the connection is configured in the bc4j.xcfg file or a JavaEE data source. Here, it is necessary to distinguish between data source and DataSource. A data source is a source of data; for example an RDBMS database is a data source. A DataSource is an interface that represents a factory for JDBC Connection objects. JDeveloper uses the term Data Source or data source to refer to a factory for connections. We will also use the term Data Source or data source to refer to a factory for connections, which in the javax.sql package is represented by the DataSource interface. A DataSource object may be created from a data source registered with the JNDI (Java Naming and Directory) naming service using JNDI lookup. A JDBC Connection object may be obtained from a DataSource object using the getConnection method. As an alternative to configuring a connection in the Connections Navigator a data source may also be specified directly in the data source configuration file data-sources.xml. In this article we will discuss the procedure to configure a JDBC connection and a JDBC data source in JDeveloper 10g IDE. We will use the MySQL 5.0 database server and MySQL Connector/J 5.1 JDBC driver, which support the JDBC 4.0 specification. In this article you will learn the following:

Creating a database connection in JDeveloper Connections Navigator.
Configuring the Data Source and Connection Pool associated with the connection configured in the Connections Navigator.
The common JDBC Connection Errors
Before we create a JDBC connection and a data source we will discuss connection pooling and DataSource.

Connection Pooling and DataSource

The javax.sql package provides the API for server-side database access. The main interfaces in the javax.sql package are DataSource, ConnectionPoolDataSource, and PooledConnection. The DataSource interface represents a factory for connections to a database. DataSource is a preferred method of obtaining a JDBC connection. An object that implements the DataSource interface is typically registered with a Java Naming and Directory API-based naming service. DataSource interface implementation is driver-vendor specific. The DataSource interface has three types of implementations:

Basic implementation: In basic implementation there is 1:1 correspondence between a client’s Connection object and the connection with the database. This implies that for every Connection object, there is a connection with the database. With the basic implementation, the overhead of opening, initiating, and closing a connection is incurred for each client session.

Connection pooling implementation: A pool of Connection objects is available, from which connections are assigned to the different client sessions. A connection pooling manager implements the connection pooling. When a client session does not require a connection, the connection is returned to the connection pool and becomes available to other clients. Thus, the overheads of opening, initiating, and closing connections are reduced.

Distributed transaction implementation: Distributed transaction implementation produces a Connection object that is mostly used for distributed transactions and is always connection pooled. A transaction manager implements the distributed transactions.

An advantage of using a data source is that code accessing a data source does not have to be modified when an application is migrated to a different application server. Only the data source properties need to be modified. A JDBC driver that is accessed with a DataSource does not register itself with a DriverManager. A DataSource object is created using a JNDI lookup and subsequently a Connection object is created from the DataSource object. For example, if a data source JNDI name is jdbc/OracleDS a DataSource object may be created using JNDI lookup. First, create an InitialContext object and subsequently create a DataSource object using the InitialContext lookup method. From the DataSource object create a Connection object using the getConnection() method:

[code lang=”java”]
InitialContext ctx=new InitialContext();
DataSource ds=ctx.lookup("jdbc/OracleDS");
Connection conn=ds.getConnection();
[/code]

The JNDI naming service, which we used to create a DataSource object is provided by J2EE application servers such as the Oracle Application Server Containers for J2EE (OC4J) embedded in the JDeveloper IDE.

A connection in a pool of connections is represented by the PooledConnection interface, not the Connection interface. The connection pool manager, typically the application server, maintains a pool of PooledConnection objects. When an application requests a connection using the DataSource.getConnection() method, as we did using the jdbc/OracleDS data source example, the connection pool manager returns a Connection object, which is actually a handle to an object that implements the PooledConnection interface.

A ConnectionPoolDataSource object, which is typically registered with a JNDI naming service, represents a collection of PooledConnection objects. The JDBC driver provides an implementation of the ConnectionPoolDataSource, which is used by the application server to build and manage a connection pool. When an application requests a connection, if a suitable PooledConnection object is available in the connection pool, the connection pool manager returns a handle to the PooledConnection object as a Connection object. If a suitable PooledConnection object is not available, the connection pool manager invokes the getPooledConnection() method of the ConnectionPoolDataSource to create a new PooledConnection object. For example, if connectionPoolDataSource is a ConnectionPoolDataSource object a new PooledConnection gets created as follows:

[code lang=”java”]
PooledConnection pooledConnection=connectionPoolDataSource.getPooledConnection();
[/code]

The application does not have to invoke the getPooledConnection() method though; the connection pool manager invokes the getPooledConnection() method and the JDBC driver implementing the ConnectionPoolDataSource creates a new PooledConnection, and returns a handle to it. The connection pool manager returns a Connection object, which is a handle to a PooledConnection object, to the application requesting a connection. When an application closes a Connection object using the close() method, as follows, the connection does not actually get closed.

[code lang=”java”]
conn.close();
[/code]

The connection handle gets deactivated when an application closes a Connection object with the close() method. The connection pool manager does the deactivation. When an application closes a Connection object with the close () method any client info properties that were set using the setClientInfo method are cleared. The connection pool manager is registered with a PooledConnection object using the addConnectionEventListener() method. When a connection is closed, the connection pool manager is notified and the connection pool manager deactivates the handle to the PooledConnection object, and returns the PooledConnection object to the connection pool to be used by another application. The connection pool manager is also notified if a connection has an error. A PooledConnection object is not closed until the connection pool is being reinitialized, the server is shutdown, or a connection becomes unusable.

In addition to connections being pooled, PreparedStatement objects are also pooled by default if the database supports statement pooling. It can be discovered if a database supports statement pooling using the supportsStatementPooling() method of the DatabaseMetaData interface. The PeparedStatement pooling is also managed by the connection pool manager. To be notified of PreparedStatement events such as a PreparedStatement getting closed or a PreparedStatement becoming unusable, a connection pool manager is registered with a PooledConnection manager using the addStatementEventListener() method. A connection pool manager deregisters a PooledConnection object using the removeStatementEventListener() method. Methods addStatementEventListener and removeStatementEventListener are new methods in the PooledConnection interface in JDBC 4.0. Pooling of Statement objects is another new feature in JDBC 4.0. The Statement interface has two new methods in JDBC 4.0 for Statement pooling: isPoolable() and setPoolable().

The isPoolable method checks if a Statement object is poolable and the setPoolable method sets the Statement object to poolable. When an application closes a PreparedStatement object using the close() method the PreparedStatement object is not actually closed. The PreparedStatement object is returned to the pool of PreparedStatements. When the connection pool manager closes a PooledConnection object by invoking the close() method of PooledConnection all the associated statements also get closed. Pooling of PreparedStatements provides significant optimization, but if a large number of statements are left open, it may not be an optimal use of resources. Thus, the following procedure is followed to obtain a connection in an application server using a data source:

Create a data source with a JNDI name binding to the JNDI naming service.
Create an InitialContext object and look up the JNDI name of the data source using the lookup method to create a DataSource object. If the JDBC driver implements the DataSource as a connection pool, a connection pool becomes available.
Request a connection from the connection pool. The connection pool manager checks if a suitable PooledConnection object is available. If a suitable PooledConnection object is available, the connection pool manager returns a handle to the PooledConnection object as a Connection object to the application requesting a connection.
If a PooledConnection object is not available the connection pool manager invokes the getPooledConnection() method of the ConnectionPoolDataSource, which is implemented by the JDBC driver.
The JDBC driver implementing the ConnectionPoolDataSource creates a PooledConnection object and returns a handle to it.
The connection pool manager returns a handle to the PooledConnection object as a Connection object to the application requesting a connection.
When an application closes a connection, the connection pool manager deactivates the handle to the PooledConnection object and returns the PooledConnection object to the connection pool.

ConnectionPoolDataSource provides some configuration properties to configure a connection pool. The configuration pool properties are not set by the JDBC client, but are implemented or augmented by the connection pool. The properties can be set in a data source configuration. Therefore, it is not for the application itself to change the settings, but for the administrator of the pool, who also happens to be the developer sometimes, to do so. Connection pool properties supported by ConnectionPoolDataSource are discussed in following table:

Connection Pool Property	Type	Description
maxStatements	int	Maximum number of statements the pool should keep open. 0 (zero) indicates that statement caching is not enabled.
initialPoolSize	int	The initial number of connections the pool should have at the time of creation.
minPoolSize	int	The minimum number of connections in the pool. 0 (zero) indicates that connections are created as required.
maxPoolSize	int	The maximum number of connections in the connection pool. 0 indicates that there is no maximum limit.
maxIdleTime	int	Maximum duration (in seconds) a connection can be kept open without being used before the connection is closed. 0 (zero) indicates that there is no limit.
propertyCycle	int	The interval in seconds the pool should wait before implementing the current policy defined by the connection pool properties.
maxStatements	int	The maximum number of statements the pool can keep open. 0 (zero) indicates that statement caching is not enabled.

Setting the Environment

Before getting started, we have to install the JDeveloper 10.1.3 IDE and the MySQL 5.0 database. Download JDeveloper from: http://www.oracle.com/technology/software/products/jdev/index.html. Download the MySQL Connector/J 5.1, the MySQL JDBC driver that supports JDBC 4.0 specification. To install JDeveloper extract the JDeveloper ZIP file to a directory. Log in to the MySQL database and set the database to test. Create a database table, Catalog, which we will use in a web application. The SQL script to create the database table is listed below:

[code]
CREATE TABLE Catalog(CatalogId VARCHAR(25)
PRIMARY KEY, Journal VARCHAR(25), Publisher VARCHAR(25),
Edition VARCHAR(25), Title Varchar(45), Author Varchar(25));
INSERT INTO Catalog VALUES(‘catalog1’, ‘Oracle Magazine’,
‘Oracle Publishing’, ‘Nov-Dec 2004’, ‘Database Resource Manager’, ‘Kimberly Floss’);
INSERT INTO Catalog VALUES(‘catalog2’, ‘Oracle Magazine’, ‘Oracle Publishing’,
‘Nov-Dec 2004’, ‘From ADF UIX to JSF’, ‘Jonas Jacobi’);
[/code]

MySQL does not support ROWID, for which support has been added in JDBC 4.0. Having installed the JDeveloper IDE, next we will configure a JDBC connection in the Connections Navigator. Select the Connections tab and right-click on the Database node to select New Database Connection.

Click on Next in Create Database Connection Wizard. In the Create Database Connection Type window, specify a Connection Name—MySQLConnection for example—and set Connection Type to Third Party JDBC Driver, because we will be using MySQL database, which is a third-party database for Oracle JDeveloper and click on Next. If a connection is to be configured with Oracle database select Oracle (JDBC) as the Connection Type and click on Next.

In the Authentication window specify Username as root (Password is not required to be specified for a root user by default), and click on Next. In the Connection window, we will specify the connection parameters, such as the driver name and connection URL; click on New to specify a Driver Class. In the Register JDBC Driver window, specify Driver Class as com.mysql.jdbc.Driver and click on Browse to select a Library for the Driver Class. In the Select Library window, click on New to create a new library for the MySQL Connector/J 5.1 JAR file. In the Create Library window, specify Library Name as MySQL and click on Add Entry to add a JAR file entry for the MySQL library. In the Select Path Entry window select mysql-connector-java-5.1.3-rcmysql-connector-java-5.1.3-rc-bin.jar and click on Select. In the Create Library window, after a Class Path entry gets added to the MySQL library, click on OK. In the Select Library window, select the MySQL library and click on OK. In the Register JDBC Driver window, the MySQL library gets specified in the Library field and the mysql-connector-java-5.1.3-rcmysql-connector-java-5.1.3-rc-bin.jar gets specified in the Classpath field. Now, click on OK. The Driver Class, Library, and Classpath fields get specified in the Connection window. Specify URL as jdbc:mysql://localhost:3306/test, and click on Next.

In the Test window click on Test Connection to test the connection that we have configured. A connection is established and a success message gets output in the Status text area. Click on Finish in the Test window. A connection configuration, MySQLConnection, gets added to the Connections navigator.

The connection parameters are displayed in the structure view. To modify any of the connection settings, double-click on the Connection node. The Edit Database Connection window gets displayed. The connection Username, Password, Driver Class, and URL may be modified in the Edit window. A database connection configured in the Connections navigator has a JNDI name binding in the JNDI naming service provided by OC4J. Using the JNDI name binding, a DataSource object may be created in a J2EE application. To view, or modify the configuration settings of the JDBC connection select Tools | Embedded OC4J Server Preferences in JDeveloper. In the window displayed, select Global | Data Sources node, and to update the data-sources.xml file with the connection defined in the Connections navigator, click on the Refresh Now button. Checkboxes may be selected to Create data-source elements where not defined, and to Update existing data-source elements.

The connection pool and data source associated with the connection configured in the Connections navigator get listed. Select the jdev-connection-pool-MySQLConnection node to list the connection pool properties as Property Set A and Property Set B.

The tuning properties of the JDBC connection pool may be set in the Connection Pool window. The different tuning attributes are listed in following table:

Tuning Attribute	Attribute Description	Default Value
Abandoned Connection Timeout	Interval (seconds) after which a connection acquired by a user that has been inactive is returned to the cache.	-1 implies that the feature is not in effect.
Retry Interval	Interval (seconds) after which a failed connection attempt is retried. Used with Max Connect Attempts.	1
Disable Connection Pooling	Specifies if application server’s connection pooling is to be disabled. This attribute is available because some drivers provide connection pooling inside the driver.	False
Inactivity Timeout	The number of seconds of inactivity after which an unused connection is removed from the pool.	Inactivity Timeout
Initial Limit	The initial number of connections in the connection pool. If value is greater than 0, the specified number of connections are pre-created and available in the connection cache, thus reducing the time required to build the cache to its optimal size.	0
Login Timeout	The number of seconds after which a login attempt is timed out. 0 implies that the system timeout value is used. If a system timeout is not defined, a login attempt is not timed out.	0
Max Connect Attempts	The maximum number of connection attempts to a database. Used in conjunction with retry interval.	3
Max Connections	The maximum number of available database connections in the connection pool. A value of 0 or less implies that there is no maximum limit.	0
Min Connections	The minimum number of database connections in the connection pool.	0

Select Property Set B to specify additional connection pool properties.

The connection pool properties in Property Set B are discussed in the following table:

Property	Description		Default Value
Num Cached Statements	Specifies the maximum number of prepared and callable statements that should be cached for each connection in each connection pool. Statement caching increases system performance. A value greater than 0 enables statement caching.		0
Property Check Interval	Specifies the time interval (seconds) after which property values are checked for new values, and time out limits are implemented.		900
Time to Live Timeout	Specifies the maximum number of seconds a used connection may be active, after which it is closed and returned to the connection pool. -1 indicates that the feature is not enabled.		-1
Used Connection Wait Timeout		Number of seconds for which a used connection remains unused before being returned to the connection pool. Only applies if the maximum numbers of connections that a connection pool may cache have been acquired by clients, and a client requests a connection.	60
Validate Connections		Specifies if connections are to be validated, when given to a client. Used in conjunction with Validate Connection Statements.	False
Validate Connection Statements		Specifies the SQL statements used to validate connections before being acquired by a client.	None

The Connection Factory node specifies the Factory Class, User name, Password, Login Timeout, and connection URL. The factory class must implement one of the following interfaces: java.sql.Driver, javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, javax.sql.XADataSource.

The Managed DataSource node specifies the managed data sources associated with the connection, and which are data sources managed by the OC4J. A managed data source is an OC4J implementation of the javax.sql.DataSource interface that wraps a JDBC driver class, or data source class. Even if the factory class does not implement the javax.sql.DataSource interface, the OC4J implementation of the factory class implements the javax.sql.DataSource interface. A managed data source supports connection caching, global transaction management, and error handling, all provided by the OC4J. A managed data source is associated with a connection pool, and thus has the advantage of being able to specify the tuning parameters. The JNDI Name of the data source is specified in the managed data source window. The JNDI Name is in the jdbc/MySQLConnectionDS format, with MySQLConnection being the connection name configured in the Connections navigator.

A connection MySQLConnection in the Connections navigator is available as a data source with the JNDI Name binding jdbc/MySQLConnectionDS. To obtain a connection from the data source, add a resource-ref element to the web application in which a connection is to be obtained. In a Servlet or JSP application, a connection may be obtained with the data source JNDI Name.

[code lang=”java”]
InitialContext initialContext = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)
initialContext.lookup("java:comp/env/jdbc/MySQLConnectionDS");
java.sql.Connection conn = ds.getConnection();
[/code]

JavaEE 5 defines annotations to support resource injection. Resource injection is the injection of external resources, such as a data source in a JEE 5 application using the javax.annotation.Resource annotation. JDeveloper 11 will support resource injection with annotations to obtain a handle of a data source. For example, define a catalogDS resource of the javax.sql.DataSource type, as shown below:

[code lang=”java”]
private @Resource DataSource catalogDS;
[/code]

The catalogDS field of type javax.sql.DataSource is annotated with the @Resource annotation. JNDI lookup is not required with resource injection, and the DataSource resource is also not defined in the web.xml deployment descriptor.

JDBC Configuration Errors

You might get errors while configuring a JDBC connection. If you are using MySQL, and the connection URL is incorrect, or the MySQL database is not running, the following error message is generated:

Communications link failure

If you are using Oracle database, some possible connection configuration errors are listed below:

IO exception: The Network Adapter could not establish the connectionM
IO exception: Connection refused

The Network Adapter could not establish the connection exception is caused by one or more of the following configuration errors:

The database host name, port number, or database instance name is wrong.
The database TNSListener has not been started. The TNSListener may be started with the lsnrctl utility.

[code]
C:>lsnrctl start
The Connection refused exception is caused by one or more of the following configuration errors:
1. The database SID specified is incorrect.
2. The database instance has not been started. To start the database instance connect to SQL*Plus as SYSDBA.
C:>sqlplus SYS/<pwd> AS SYSDBA
At the SQL prompt, start the database instance with the startup command.
SQL>startup
[/code]

Summary

JDeveloper IDE provides a built-in Connections navigator to configure a connection with any relational database for which a JDBC driver is available. A connection configured in the Connections navigator is also available as a data source. In this article, we have configured a JDBC connection in JDeveloper with MySQL database using the MySQL Connector/J 5.1 JDBC 4.0 driver.

JDBC 4.0 API in Java 6.0

June 7, 2007 by Krishna Srinivasan Leave a Comment

1) Introduction

Java Database Connectivity allows Application Programs to interact with the Database to access the Relational Data. JDBC provides the Pluggable Architecture wherein any type of Java Compliant Drivers can be plugged-in even during the run-time. The JDBC API provides functionality to establish Connection to the back-end Database session which can execute the Queries to get the Results. The new version of JDBC that comes along with Mustang (Java 6.0) is JDBC 4.0. JDBC 4.0 is one of the areas that were great affected with the new set of features. Let us look into the major features one by one. The specification of the JDBC 4.0 is maintained under JSR 221.

also read:

New Features in Java 6.0 – Par 1

New Features in Java 6.0 – Par 2

Java 6.0 Compiler API

There about twenty features are introduced in the JDBC 4.0. The overall features can be categorize under the following four topics:

Driver and connection management
Exception handling
Data type support
API changes

In this article we explore some of the features mostly used by the programmers. If you have any questions, please post it in the comments section.

2) No need for Class.forName(“DriverName”)

Before the advent of JDBC 4.0, Application Programmers have to manually load the Database Driver before making any Database Calls. The following code shows how to load a Database Driver before JDBC 4.0

[code lang=”java”]Class.forName("FullyQualifiedNameOfTheDriverClass");[/code]
This has one major dis-advantage. Suppose if the Database Driver Vendor wishes to change the class name of the Driver, then it will result in Code change. But with the help of Service Provider Mechanism (which is available from Java 5), there is no need for the programmers to manually load the Driver. The Driver will be Loaded Automatically by the JVM if the Jar files that correspond to the Driver Class are in the appropriate class path.
Let us explain how the Service Provider Mechanism works. Two entities are of much interest in the Service Provider Mechanism. One is the Service itself which mostly contains a set of interfaces that deals with what the Service is all about and the other one is the Provider who will give a concrete and a well-defined Implementation for the Service. This Mechanism forces the Provider to package the service as a Jar which has to maintain a Directory Structure like this,

[code lang=”java”]\\META-INF\\services\\FullNameOfTheService[/code]
Suppose if the JDBC Driver Provider wishes to make his implementation as a Service, then the Vendor should maintain a Directory Structure along with the file name like this,

[code lang=”java”]\\META-INF\\services\\java.sql.Driver[/code]
Note that java.sql.Driver is the name of the File, and the file should contain only one entry which is the Name of the Provider Class that implements the Driver. For example, in the case of My SQL, the content of the file will be looking like the following,
File – Java.sql.Driver

[code lang=”java”]com.mysql.jdbc.Driver # Class name of the Driver[/code]
Through this mechanism, the JDBC Drivers will get Automatically Loaded by the Java Program. If there are Multiple Drivers in the Application’s class-path, then the first matching Driver will be taken into consideration and the rest of them will just be ignored.

3) Changes in Connection and Statement Interface

Certain new useful methods are added to the Connection interface. The following sections list down the commonly used methods. To check whether an Connection object is still valid/active or not, then the following method can be used,

[code lang=”java”]int tenSeconds = 10;
Connection.isValid(tenSeconds);[/code]
The argument to Connection.isValid(int) is the Timeout value, which specifies the time duration the API should take to find out whether the Connection is valid. If the timeout interval exceeds, then false will be returned. A value of 0 for the time-out interval specifies an Infinite Time-out Duration.
Also there are methods for populating and retrieving the Client Information that a particular Driver supports with the help of Connection.getClientInfo() and Connection.setClientInfo(Properties) methods. Client information can also be set and retrieved individually using Connection.setClientInfo(String,String) and Connection.getClientInfo(String) methods.
The following methods are added into the Statement interface, Statement.isClosed(), Statement.setPoolable(boolean) and Statement.isPoolable(). Statement.isClosed() will return true if the Statement Object is closed by calling the Statement.close() method. By default, only PreparedStatement and CallableStatement are pooled by the Implementation. The call to Statement.setPoolable(true) will only act as a request to the Implementation to pool this Statement Object for better performance. Not all the implementations may support this feature and hence Applications should not depend on this feature always.

4) Enhanced SQL Exception Handling

Exception handling in JDBC has been enhanced a lot in JDBC 4.0. The following are the major areas where the enhancements are done.

Iterable SQL Exception
Concreate Sub Classes for specific SQL Exception

4.1) Iterable SQL Exception

The java.sql.SQLException now implements the Iterable<Throwable> interface which means that it can be iterated over to get the Next Set of Exceptions and can also be used in Enhanced For-Loops (available from Java 5.0). Consider the following piece of code,

[code lang=”java”]try{

// Some Database Access Code

}catch(SQLException exception){

for(Throwable throwable: exception){
System.out.println(throwable.getMessage());

}
}[/code]
Before JDBC 4.0, Applications depended on SQLException.getNextException() to get the next Exception and looped over until the returned exception is not null.

[code lang=”java”]try{

// Some Database Access Code

}catch(SQLException exception){

System.out.println(exception.getMessage());
SQLException nextException = exception.getNextException();

while (nextException != null){
System.out.println(nextException.getMessage());
nextException = nextException.getNextException();
}
}[/code]

4.2) Concreate Sub Class for specific SQL Exception

More number of SQL Exception related Sub-Classes are added to the API which encapsulates the State of the SQL Error. In general, these Exceptions fall into 3 categories namely Transient, Recoverable and Non-Transient.
Assume that some kind of SQL Exception has occurred during the Program run-time. If again when a try is made and if there is a possibility such that the program can recover from the Exception without making any application specific change, then it is a kind of Transient Exception. This exception is represented by the java.sql.TransientException class and the following are the identified Transient Exceptions in the JDBC 4.0 API.

SQLTimeoutException
SQLTransactionRollbackException
SQLTransientConnectionException

Minute variation of this Transient Exception is the Recoverable Exception which is represented by java.sql.SQLRecoverableException. Here the exception that has happened can be recovered by performing some Application Specific Recovery.
The last flavour of SQL Exception is the Non-Transient Exception and it is represented by java.sql.TransientException. It tells that an exception can never be recovered even though Infinite Number of Tries is performed by the Application Program. The following are the Exceptions available in this Category,

SQLDataException
SQLFeatureNotSupportedException
SQLIntegrityConstraintViolationException
SQLInvalidAuthorizationSpecException
SQLNonTransientConnectionException
SQLSyntaxErrorException

4.3) Support for SQL RowId

Most of the popular Databases support the concept of a Row Id. A Row Id is an unique Identifier which is used to identify a Row. Support for Retrieving and setting the Row Id for a row is made available from JDBC 4.0 version. For example, consider the following piece of code, which will retrieve the value of the Row Id for a particular Row.

[code lang=”java”]String selectQuery = " select rowid from Employees where id =’123′ ";
ResultSet resultSet = statement.executeQuery("selectQuery");
java.sql.Rowid rowId = resultSet.getRowId();[/code]
The Row Id is represented by java.sql.RowId class. Before doing any kind of manipulation on the Row Id, it is wise to check whether the under-lying Database implementation provides Support for Row Id(s), as well as the Lifetime of the Row Id objects. The life-time of the Row Id determines how long the Row Id is valid, whether the Row Id exists only for a particluar Session or for a Set of Transactions within a Session or Outside the Database Session etc. This availability can be known with the help of DatabaseMetaData like the following,

[code lang=”java”]RowIdLifeTime rowIdLifeTime = DatabaseMetaData.getRowIdLifetime();

if (rowIdLifeTime != ROWID_UNSUPPORTED){
// Row Id support is there for this Data source.
}[/code]
The returned RowIdLifeTime is an Enum which tells about the Life-Time of the Row Id object. Possible values are ROWID_UNSUPPORTED, ROWID_VALID_FOREVER, ROWID_VALID_OTHER, ROWID_VALID_SESSION, ROWID_VALID_TRANSACTION and ROWID_VALID_FOREVER.

5) Conclusion

The new existing features of JDBC 4.0 like the Automatic Driver Loading, various new methods added to em>Connection/Statement interface and Enhanced SQL Exception Handling are also explained.
Please refer our recommended java books section for the complete list of books. Hope this helps. If you are interested in receiving our future Java articles, please subscribe here.