In this tutorial, we’ll have a quick look at some of the most important Interview questions and answers of JDBC for a Java Developer interview.

What is JDBC?

One of the first JDBC interview questions in most of the interviews. JDBC is java database connectivity as name implies it’s a java API for communicating to relational database, API has java classes and interfaces using that developer can easily interact with database. For this we need database specific JDBC drivers.

What are the main steps in java to make JDBC connectivity?

Another beginner level JDBC Interview question, mostly asked on telephonic interviews. Here are main steps to connect to database.

• Load the Driver: First step is to load the database specific driver which communicates with database.
• Make Connection: Next step is get connection from the database using connection object, which is used to send SQL statement also and get result back from the database.
• Get Statement object: From connection object we can get statement object which is used to query the database
• Execute the Query:Using statement object we execute the SQL or database query and get result set from the query.
• Close the connection:After getting resultset and all required operation performed the last step should be closing the database connection.

What is the mean of “dirty read“ in database?

This kind of JDBC interview question is asked on 2 to 4 years experience Java programmer, they are expected to familiar with database transaction and isolation level etc. As the name it self convey the meaning of dirty read “read the value which may or may not be correct”.

In database when one transaction is executing and changing some field value same time some another transaction comes and read the change field value before first transaction commit or rollback the value ,which cause invalid value for that field, this scenario is known as dirty read.

What is the 2 phase commit?

This is one of the most popular JDBC Interview question and asked at advanced level, mostly to senior Java developers on J2EE interviews. Two phase commit is used in distributed environment where multiple process take part in distributed transaction process.

In simple word we can understand like if any transaction is executing and it will effect multiple database then two phase commit will be used to make all database synchronized with each other.

In two phase commit, commit or rollback is done by two phases:

1. Commit request phase: in this phase main process or coordinator process take vote of all other process that they are complete their process successfully and ready to commit if all the votes are “yes” then they go ahead for next phase. And if “No “then rollback is performed.
2. Commit phase: according to vote if all the votes are yes then commit is done.

Similarly when any transaction changes multiple database after execution of transaction it will issue pre commit command on each database and all database send acknowledgement and according to acknowledgement if all are positive transaction will issue the commit command otherwise rollback is done .

What are different types of Statement?

This is another classical JDBC interview question. Variants are Difference between Statement, PreparedStatemetn and CallableStatement in Java. Statement object is used to send SQL query to database and get result from database, and we get statement object from connection object.

There are three types of statement:

1. Statement: it’s a commonly used for getting data from database useful when we are using static SQL statement at runtime. it will not accept any parameter.

             Statement   stmt = conn.createStatement( );
     ResultSet rs = stmt.executeQuery();

2. PreparedStatement: when we are using same SQL statement multiple time its is useful and it will accept parameter at runtime.

             String SQL = "Update stock SET limit = ? WHERE stockType = ?";
     PreparedStatement  pstmt = conn.prepareStatement(SQL);
     ResultSet rs = pstmt.executeQuery();

   To learn more about PreparedStatement, see What is PreparedStatement in Java and Benefits.

3. Callable Statement: when we want to access stored procedures then callable statement are useful and they also accept runtime parameter. It is called like this

     CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
     ResultSet rs = cs.executeQuery();

How cursor works in scrollable result set?

Another tough JDBC Interview question, not many Java programmer knows about using Cursor in Java. in JDBC 2.0 API new feature is added to move cursor in resultset backward forward and also in a particular row .

There are three constant define in result set by which we can move cursor.

• TYPE FORWARD ONLY: creates a nonscrollable result set, that is, one in which the cursor moves only forward
• TYPE SCROLL INSENSITIVE: a scrollable result set does not reflects changes that are made to it while it is open
• TYPE SCROLL SENSITIVE: a scrollable result set reflects changes that are made to it while it is open.

What is connection pooling?

This is also one of the most popular question asked during JDBC Interviews. Connection pooling is the mechanism by which we reuse the recourse like connection objects which are needed to make connection with database .

In this mechanism client are not required every time make new connection and then interact with database instead of that connection objects are stored in connection pool and client will get it from there. so it’s a best way to share a server resources among the client and enhance the application performance.

What do you mean by cold backup, hot backup?

This question is not directly related to JDBC but some time asked during JDBC interviews. The cold back is the backup technique in which backup of files are taken before the database restarted. In hot backup of files and table is taken at the same time when database is running. A warm is a recovery technique where all the tables are locked and users cannot access at the time of backing up data.

What are the locking system in JDBC?

One more tough JDBC question to understand and prepare. There are 2 types of locking in JDBC by which we can handle multiple user issue using the record. if two user are reading the same record then there is no issue but what if users are updating the record , in this case changes done by first user is gone by second user if he also update the same record .so we need some type of locking so no lost update.

Optimistic Locking: optimistic locking lock the record only when update take place. Optimistic locking does not use exclusive locks when reading

Pessimistic locking: in this record are locked as it selects the row to update

Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

No, we can open only one Statement object when using the JDBC-ODBC Bridge.

That’s all on this list of 10 JDBC Interview questions with answers. As I said JDBC API and their concepts are integral parts of any Java interview and there is always at least one question from JDBC. Since most application uses a database in the backend, JDBC becomes critical for any Java developer.

ResultSet vs RowSet: Which one to choose and when in JDBC?

RowSet

RowSet is almost always the right choice, it is more full featured and has all the benefits you listed as well as having specialized implementations for special purposes, like the disconnected CachedRowSet which is what I always use when the data will fit into memory, so I can release the connection back to the pool as quickly as possible to be reused.

ResultSet should never be part of a public contract.

A connected ResultSet/Rowset should never escape the method or at worst the object that created them. At least with a RowSet you can disconnect it and the client doesn’t have to care about the implementation. *Unless you are writing JDBC specific library code that interacts or relies on ResultSet specific features or contracts.

If you are just transferring the results of a query, JDBC specific classes should be part of your public contract.

Ideally you want to materialize RowSet/ResultSet contents to typesafe Domain Objects to pass around. In most cases you want to materialize a List/Set of domain objects to manipulate and work with instead of coupling your code directly to the JDBC api.

Many modern takes on a ResultSetMapper class exist to handle generating typesafe domain instances using a Visitor pattern because this is the idiomatic way of doing things.

What is connected and disconnected Rowset in JDBC 3.0 ?

Connected and Disconnected RowSets

There are two types of RowSet objects—connected and disconnected. A connected RowSet object connects to the database once and remains connected while the object is in use. 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’s disconnected. Modified data can be updated in the database after a disconnected RowSet reestablishes the connection with the database.

Package javax.sql.rowset contains two subinterfaces of RowSet—JdbcRowSet and CachedRowSet. JdbcRowSet, a connected RowSet, acts as a wrapper around a ResultSet object and allows you to scroll through.

A disconnected RowSet object is almost the same as connected RowSet object except they are:

• Lighter in weight compared to connected RowSet objects.
• Serializable.
• Able to send data to light weight clients such as mobiles etc.

JDBC provides four classes that represent disconnected RowSet objects.

• CachedRowSet: The CachedRowSet is the base implementation of disconnected row sets. It connects to the data source, reads data from it, disconnects with the data source and the processes the retrieved data, reconnects to the data source and writes the modifications.
• WebRowSet: A WebRowSet extends the CachedRowSet.
• JoinRowSet: This is able to send data to lightweight clients such as mobiles etc.
• FilteredRowSet: This enables you to cut down the number of rows that are visible in a RowSet.