JDBC Interview Questions and Answers

Java Database Connectivity (JDBC) is a standard Java API to interact with relational databases form Java. JDBC has set of classes and interfaces which can use from Java application and talk to database without learning RDBMS details and using Database Specific JDBC Drivers. The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage.

Connection to a database

Creating SQL or MySQL Database statements

Executing SQL or MySQL queries or built in SQL editor in the database

Viewing and modifying the resulting records

JDBC API has 3 Interfaces and their key features are as follows:

Statement: It is used to run simple SQL statements like select and update. Statement interfaces use for general-purpose access to your database. It is useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.

PreparedStatement: A SQL statement is pre-compiled and stored in a PreparedStatement object. It is used to run Pre compiled SQL. This object can then be used to efficiently execute this statement multiple times. The object of Prepared Statement class can be created using Connection.prepareStatement() method. This extends Statement interface.

CallableStatement: This interface is used to execute the stored procedures. This extends Prepared Statement interface. The object of Callable Statement class can be created using Connection.prepareCall() method.

The JDBC Driver provides vendor-specific implementations of the abstract classes provided by the JDBC API. This driver is used to connect to the database.

There are four types of JDBC drivers.

JDBC-ODBC Bridge plus ODBC Driver: It uses ODBC driver to connect to database. We should have ODBC drivers installed to connect to database, that’s why this driver is almost obsolete.

Native API partly Java technology-enabled driver: This driver converts JDBC class to the client API for the database servers. We should have database client API installed. Because of extra dependency on database client API drivers, this is also not preferred driver.

Pure Java Driver for Database Middleware: This driver sends the JDBC calls to a middleware server that can connect to different type of databases. We should have a middleware server installed to work with this driver. This adds to extra network calls and slow performance and thats why not widely used JDBC driver.

Direct-to-Database Pure Java Driver: This driver converts the JDBC calls to the network protocol understood by the database server. This solution is simple and suitable for database connectivity over the network. However, for this solution, we should use database specific drivers, for example OJDBC jars by Oracle for Oracle DB and MySQL Connector/J for MySQL databases.

There are 5 steps to connect any java application with the database in java using JDBC. They are as follows:

• Register the driver class
• Creating connection
• Creating statement
• Executing queries
• Closing connection

The java.sql package contains interfaces and classes for JDBC API.

Interfaces:

• Connection
• Statement
• PreparedStatement
• ResultSet
• ResultSetMetaData
• DatabaseMetaData
• CallableStatement etc.

Classes:

• DriverManager
• Blob
• Clob
• Types
• SQLException etc.

In case of Statement, query is complied each time whereas in case of PreparedStatement, query is complied only once. So performance of PreparedStatement is better than Statement.

Some of the benefits of PreparedStatement over Statement are:

• PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters.
• PreparedStatement allows us to execute dynamic queries with parameter inputs.
• PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries.
• PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.

One of the limitation of PreparedStatement is that we can’t use it directly with IN clause statements. Some of the alternative approaches to use PreparedStatement with IN clause are;

Execute Single Queries – very slow performance and not recommended

Using Stored Procedure – Database specific and hence not suitable for multiple database applications.

Creating PreparedStatement Query dynamically – Good approach but looses the benefit of cached PreparedStatement.

Using NULL in PreparedStatement Query – A good approach when you know the maximum number of variables inputs, can be extended to allow unlimited parameters by executing in parts.

There are different types of ResultSet objects that we can get based on the user input while creating the Statement. If you will look into the Connection methods, you will see that createStatement() and prepareStatement() method are overloaded to provide ResultSet type and concurrency as input argument.

There are three types of ResultSet object.

1. TYPE_FORWARD_ONLY: This is the default type and cursor can only move forward in the result set.
2. TYPE_SCROLL_INSENSITIVE: The cursor can move forward and backward, and the result set is not sensitive to changes made by others to the database after the result set was created.
3. TYPE_SCROLL_SENSITIVE: The cursor can move forward and backward, and the result set is sensitive to changes made by others to the database after the result set was created.

Based on the concurrency there are two types of ResultSet object.

1. CONCUR_READ_ONLY: The result set is read only, this is the default concurrency type.
2. CONCUR_UPDATABLE: We can use ResultSet update method to update the rows data.

We can use Connection object rollback() method to rollback the transaction. It will rollback all the changes made by the transaction and release any database locks currently held by this Connection object.

Sometimes a transaction can be group of multiple statements and we would like to rollback to a particular point in the transaction. JDBC Savepoint helps us in creating checkpoints in a transaction and we can rollback to that particular checkpoint.

Any savepoint created for a transaction is automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.

JDBC DataSource is the interface defined in javax.sql package and it is more powerful that DriverManager for database connections. We can use DataSource to create the database connection and Driver implementation classes does the actual work for getting connection. Apart from getting Database connection, DataSource provides some additional features such as:

• Caching of PreparedStatement for faster processing
• Connection timeout settings
• Logging features
• ResultSet maximum size threshold
• Connection Pooling in servlet container using JNDI support

A RowSet is an object that encapsulates a set of rows from either Java Database Connectivity (JDBC) result sets or tabular data sources like a file or spreadsheet. RowSets support component-based development models like JavaBeans, with a standard set of properties and an event notification mechanism.

There are two types of RowSet are there.

1. Connected – A connected RowSet object connects to the database once and remains connected until the application terminates.
2. Disconnected – A disconnected RowSet object connects to the database, executes a query to retrieve the data from the database and then closes the connection. A program may change the data in a disconnected RowSet while it is disconnected. Modified data can be updated in the database after a disconnected RowSet reestablishes the connection with the database.

RowSet objects are derived from ResultSet, so they have all the features of ResultSet with some additional features. One of the huge benefit of RowSet is that they can be disconnected and that makes it lightweight and easy to transfer over a network.

Whether to use ResultSet or RowSet depends on your requirements but if you are planning to use ResultSet for longer duration, then a disconnected RowSet is better choice to free database resources.

Some of the common JDBC Exceptions are:

java.sql.SQLException – This is the base exception class for JDBC exceptions.

java.sql.BatchUpdateException – This exception is thrown when Batch operation fails, but it depends on the JDBC driver whether they throw this exception or the base SQLException.

java.sql.SQLWarning – For warning messages in SQL operations.

java.sql.DataTruncation – when a data values is unexpectedly truncated for reasons other than its having exceeded MaxFieldSize.

Character Large OBjects (CLOBs) are character string made up of single-byte characters with an associated code page. This data type is appropriate for storing text-oriented information where the amount of information can grow beyond the limits of a regular VARCHAR data type (upper limit of 32K bytes).

Binary Large OBjects (BLOBs) are binary string made up of bytes with no associated code page. This data type can store binary data larger than VARBINARY (32K limit). This data type is good for storing image, voice, graphical, and other types of business or application-specific data.

1. Optimistic Locking – Locking the record only when update is taking place
2. Pessimistic Locking – Locking the record from the select to read, update and commit phase.

The values are defined in the class java.sql.Connection and are:

• TRANSACTION_NONE
• TRANSACTION_READ_COMMITTED
• TRANSACTION_READ_UNCOMMITTED
• TRANSACTION_REPEATABLE_READ
• TRANSACTION_SERIALIZABLE

The java.sql and javax.sql are the primary packages for JDBC 4.0.

• Automatic database driver loading.
• Exception handling improvements.
• Enhanced BLOB/CLOB functionality.
• Connection and statement interface enhancements.
• National character set support.
• SQL ROWID access.
• SQL 2003 XML data type support.