·    Overview of JDBC

·    Choosing the right Driver

·    Optimization with Connection

·         Set optimal row pre-fetch value

·         Use Connection pool

·         Control transaction

·         Choose optimal isolation level

·         Close Connection when finished

·    Optimization with Statement

·         Choose right Statement interface

·         Do batch update

·         Do batch retrieval using Statement

·         Close Statement when finished

·    Optimization with ResultSet

·         Do batch retrieval using ResultSet

·         Setup proper direction of processing rows

·         Use proper getxxx() methods

·         Close ResultSet when finished

·    Optimization with SQL Query

Overview of JDBC

JDBC defines how a Java program can communicate with a database. This section focuses  mainly on JDBC 2.0 API.  JDBC API provides two packages they are java.sql and javax.sql . By using JDBC API, you can connect virtually any database, send SQL queries to the database and process the results.

JDBC architecture defines different layers to work with any database and java, they are JDBC API interfaces and classes which are at top most  layer( to work with java ), a driver which is at middle layer (implements the JDBC API interfaces that maps java to database specific language) and a database which is at the bottom (to store physical data). The following figure illustrates the JDBC architecture.

JDBC API provides interfaces and classes to work with databases. Connection interface encapsulates database connection functionality, Statement interface encapsulates SQL query representation and execution functionality and ResultSet interface encapsulates retrieving data which comes from execution of SQL query using Statement.

The following are the basic steps to write a JDBC program

        1. Import java.sql and javax.sql packages

        2. Load JDBC driver

        3. Establish connection to the database using Connection interface

        4. Create a Statement by passing SQL query

        5. Execute the Statement

        6. Retrieve results by using ResultSet interface

        7. Close Statement and Connection

We will look at these areas one by one, what type of driver you need to load, how to use Connection interface in the best manner, how to use different Statement interfaces, how to process results using ResultSet and finally how to optimize SQL queries to improve JDBC performance.

Note1: Your JDBC driver should be fully compatible with JDBC 2.0 features in order to use some of the suggestions mentioned in this section.

Note2: This Section assumes that reader has some basic knowledge of JDBC.