Frequently asked JDBC interview questions and answers

Frequently asked JDBC interview questions and answers

A good understanding of JDBC (Java Database Connectivity) API is required to understand and leverage many powerful features of Java. Questions on JDBC are integral part of any Java interview. Here are some of the frequently asked JDBC interview questions and answers for freshers as well as experienced Java developers.

What is JDBC?

JDBC stands for Java Database Connectivity, which is a standard Java API for communicating to relational database. The JDBC API has interfaces for connecting to the database and also to perform operations like querying and update. JDBC API uses JDBC drivers to connect with the database.

What is a JDBC Driver and what are the types of JDBC driver?

JDBC driver contains classes and interfaces that enables Java applications to interact with a database. There are 4 types of JDBC drivers:

  • Type 1 driver or JDBC-ODBC bridge driver – The JDBC-ODBC bridge driver uses native ODBC driver to connect to the database. It converts JDBC method calls into the ODBC function calls.
  • Type 2 driver or Native-API, partly Java driver – The Native API driver uses the client-side libraries of the database. The driver converts JDBC calls into database calls by using native API provided by database. This driver is database specific so once you switch from one database to another you need to change this driver. Native database library must be loaded on each client machine that uses this type of driver.
  • Type 3 driver or Network Protocol, pure Java driver – The Network Protocol driver uses server-side middleware that converts JDBC calls into the vendor-specific database protocol.
  • Type 4 driver or Native-protocol, pure Java driver – This is the most widely used driver nowadays. The driver converts JDBC calls directly into vendor-specific database protocol. Many of these protocols are proprietary, hence the database vendors themselves will be the primary source for this type of driver.

Which type of JDBC driver is the fastest one?

Type 4 driver or Native-protocol, pure Java driver is the fastest driver because it converts the JDBC calls into vendor specific protocol calls which directly interacts with the database.

What are the main steps to connect to database using JDBC connectivity

  • Load the Driver – First step is to load the database specific driver which communicates with database. The forName() method of Class is used to register the driver class. This method is used to dynamically load the driver class.
  • Create Connection – Next step is get connection from the database using connection object, which is used to send SQL statement and get result back from the database. The getConnection() method of DriverManager class is used to establish connection with the database.
  • Create Statement object – From connection object we can get statement object which is used to query the database. The createStatement() method of Connection object is used to create statement object. The statement object is responsible for executing the queries with database.
  • Execute the Query – Using statement object we can execute the SQL or database query and get result set from the query. The executeQuery() method of Statement object is used to execute queries to the database. This method returns an object of ResultSet which maintains a cursor pointing to its current row of data, can be used to get the column values from the current row.
  • Close the connection – After getting resultset and performing all required operation, the last step should be closing the database connection. The close() method of Connection object is used to close the connection. By closing connection object, statement and ResultSet will be closed automatically.

What are the types of statements in JDBC?

There are 3 JDBC statements.

  • Statement – used mostly for executing a static SQL statement at runtime. An object of Statement class can be created using Connection.createStatement() method.
  • PreparedStatement – used for executing same SQL statement multiple times. A SQL statement is pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. An object of PreparedStatement class can be created using Connection.prepareStatement() method.
  • CallableStatement – used to execute SQL stored procedures / functions. An object of CallableStatement class can be created using Connection.prepareCall() method.

What is the difference between Statement and PreparedStatement? Which one to use when?

This is one of the most frequently asked questions in Java interviews.

Statement is an object used for executing a static SQL statement.Used for executing same SQL statement multiple times.
PreparedStatement allows us to execute dynamic queries
with parameter inputs.
In case of Statement, query is complied each time.PreparedStatement is a SQL statement which is
pre-compiled and stored in a PreparedStatement object.
This object can then be used to efficiently execute this
statement multiple times. Thus in case of PreparedStatements,
pre-compilation and DB-side caching of the SQL statement leads
to overall faster execution and the ability to reuse the same SQL statement in batches. So performance wise PreparedStatements are better.
SQL injection attacks are possible with StatementsSQL injection attacks can be avoided by using PreparedStatements over Statements.
Statement is suitable for DDLPrepared statements are suitable for DML.

What is the difference between preparedstatement and callablestatement?

PreparedStatement is used for executing a precompiled SQL statement multiple times. The CallableStatement is used to execute SQL stored procedures, Functions and cursors. CallableStatement extends the PreparedStatement Interface.

What is database connection pooling? What are the advantages of using a connection pool?

Connection pooling is the mechanism by which we reuse connection objects which are used to make connection with the database. It allows multiple clients to share a cached set of connection objects. Getting connection and disconnecting are costly operation, which can affect the application performance, so we should avoid creating multiple connection objects during multiple database interactions.

A pool contains set of database connection objects which are already connected to the database, and any client who wants to use it can take it from pool and it can be returned back to the pool when done with using.

Apart from performance this also saves you resources as there may be limited database connections available for your application.

How do you iterate ResultSet in the reverse order?

You can traverse a ResultSet backwards if you have a scrollable resultset. You can get this by passing ResultSet.TYPE_SCROLL_INSENSITIVE and ResultSet.CONCUR_READ_ONLY parameters when creating the statement object.

Then you can use resultset.afterLast() to move the cursor to the end of the ResultSet object, just after the last row and traverse backwards using resultset.previous() method.

Here is a sample code.

What are stored procedures? How to call stored procedure using JDBC API?

Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. The stored procedure is pre-compiled and stored in the database. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of IN/OUT parameters. Stored procedures can be called using CallableStatement class in JDBC API. Below code snippet shows how to call a stored procedure.

What is the function of DriverManager class?

DriverManager class is used for managing set of JDBC drivers. It acts as an interface between application and drivers. It keeps track of the drivers that are available for an application to use and handles establishing a connection between a database and the appropriate driver. It matches connection requests from the Java application with the proper database driver using communication sub protocol.

What is a ResultSet?

ResultSet object contains a table of data representing a database result set, which is usually generated by executing a statement that queries the database. A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The ResultSet object provides getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Values can be retrieved using either the index number of the column or the name of the column.

What is the use of setAutoCommit() method?

When a connection is created, it is in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. By setting auto-commit to false no SQL statements will be committed until you explicitly call the commit() method.

What is a RowSet?

A RowSet is an object that encapsulates a set of rows from either java Database Connectivity (JDBC) result sets or tabular data sources. RowSets support component-based development models like JavaBeans, with a standard set of properties and an event notification mechanism. A JDBC RowSet object holds tabular data in a way that makes it more flexible and easier to use than a result set.

The RowSet interface provides a set of JavaBeans properties that allow a RowSet instance to be configured to connect to a JDBC data source and read some data from the data source. A group of setter methods (setInt, setBytes, setString, and so on) provide a way to pass input parameters to a rowset’s command property. This command is the SQL query the rowset uses when it gets its data from a relational database, which is generally the case.

The RowSet interface supports JavaBeans events, allowing other components in an application to be notified when an event occurs on a rowset, such as a change in its value.

A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset. A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so.

What is a “dirty read”?

In typical database transaction, say one transaction reads and changes the value while the second transaction reads the value before commit or roll back by the first transaction. This reading process is called as ‘dirty read‘. Because there is always a chance that the first transaction might rollback the change which causes the second transaction reads an invalid value.

What is Metadata?

JDBC API has two Metadata interfaces DatabaseMetaData and ResultSetMetaData. The DatabaseMetaData provides comprehensive information about the database (such as database product name, database product version, driver name, driver version etc.). The implementation of this interfaces is provided by database driver vendors to let users know the capabilities of a Database Management System (DBMS) in combination with the JDBC driver that is used with it.

The ResultSetMetaData is used to get information about the types and properties of the columns in a ResultSet object. It can be used to get information like table name, total number of columns, column name, column type etc.

That’s all about some of the frequently asked interview questions in JDBC. Hope you find it useful. If you have any doubts, ask in comments in section.

The following two tabs change content below.
Working as a Java developer since 2010. Passionate about programming in Java. I am a part time blogger.

Add Comment

Required fields are marked *. Your email address will not be published.