JDBC Interview Questions

Java Database Connectivity (JDBC) is an application programming interface (API) that the Java programming language uses, and it defines a procedure of clients' access to a database. It is a part of the Java Standard Edition platform offered by Oracle Corporation.

Here are some of the JDBC questions that give you clarity on the subject and take your confidence to the next level. The questions are sorted out according to the level of the candidates, i.e., the beginner's level and professional level.

We have divided this Interview questions blog into three sections for your easy understanding

• For Freshers
• For Experienced

Frequently Asked JBDC Interview Questions.

1. What is JDBC?
2. What is a JDBC Driver?
3. What are the different types of JDBC Statements?
4. What does set AutoCommit do?
5. What are the Metadata interfaces present in JDBC?
6. What are the ResultSet types?
7. Discuss the design pattern followed by JDBC
8. How do you handle SQL NULL values in Java?
9. What is JDBC SQL escape syntax?
10. What are the different types of RowSet objects?

JDBC Interview Questions for Freshers 

If you are new to this field, these are the fundamental questions mostly posed to freshers. Preparing these JDBC Interview Questions for Beginners first will help you face the interview confidently.

1. What is JDBC?

JDBC stands for Java Database Connectivity, a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

2. What is a connection?

A connection is an Interface that includes all methods for contacting a database. The connection object is a source of communication context, i.e., all communication with the database with the help of the connection object only.

3. What is a statement?

Statement abstracts an SQL statement that must be parsed, planned, compiled, passed and executed.

4. What is the use of a ResultSet?

ResultSet is the object that holds the data retrieved from a database after executing an SQL query with the use of Statement objects. ResultSet helps move through the data freely. The java.sql.ResultSet interface represents the result set of a database query.

Learn how to use JBOSS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enrol on Free JBOSS Training Demo!

5. What is a JDBC Driver?

JDBC driver is an interface that enables the interaction of Java applications with a database. Drivers are required for each database when JDBC connects with individual databases. The JDBC driver acts as a connection to the database, implementing the necessary protocols for transferring the query and result between client and database.

6. What is a JDBC DriverManager?

The class that handles a list of database drivers is called JDBC DriverManager. The connection requests are matched from the java application to the database driver using communication subprotocol.

7. What is a RowSet?

A RowSet is an object that holds tabular data in an easily adaptable way to use rather than a result set. JavaBeans components are the RowSet objects.

8. Describe a general JDBC Architecture

The architecture of JDBC consists of two layers JDBC Driver API (The layer supports JDBC Manager-to-Driver Connection) and JDBC API (This layer provides the application-to-JDBC Manager connection).

9. What are the components that are common in JDBC API?

The components and interfaces included in JDBC API are Driver, Connection, DriverManager, Statement, SQLException ResultSet.

10. Which type of JDBC driver is the fastest one?

JDBC Net pure Java driver(Type 4) is the fastest driver of all as it converts the JDBC calls into vendor-specific protocol calls and interacts directly with the database.

11. What are the different types of JDBC Statements?

Statements used in JDBC are

Statement − which is a regular SQL statement.
PreparedStatement − is more efficient than a SQL Statement.
CallableStatement − is used to call stored procedures in the database.

12. How does JDBC handle the data types of Java and database?

The JDBC driver converts the Java data type to the appropriate JDBC type. It uses default mapping for most data types. For example, a Java int is converted to an SQL INTEGER.

13. How is the fastness of a JDBC driver measured?

The size of the driver code, Quality of the driver code, database server and its load, Network topology, Number of times the request is translated to a different API are all the number of issues that decide the speed of a JDBC driver.

14. How can you view a result set?

To view the result set, we use the get methods. Each get method in the  ResultSet interface provides two versions.

• One accepts the column name.
• One accepts the column index.

Ex: getInt(String columnName), getInt(int columnIndex)

15. What does set AutoCommit do?

AutoCommit ensures that every individual SQL statement is committed right after its execution when it is set in auto-commit mode. When created, any connection is in auto-commit mode by default. When the auto-commit is false, no SQL statements will be committed until explicitly called by the commit method.

16. Why will you set the auto-commit mode to false?

Setting auto-commit mode to false

• Increases performance.
• Easy to maintain the integrity of business processes.
• Distributed transactions can be taken into use.

17. Why would you use a batch process?

Batch Processing allows us to group related SQL statements into one single batch and then call the whole batch with one call to the database.

Check out: Installing the JDBC driver

18. What is a transaction?

The logical unit of work is called a transaction. It needs several actions against a database to complete a logical unit of work. Transactions provide data integrity, correct application semantics, and a consistent view of data during concurrent access.

19. When will you get the message "No Suitable Driver"?

When a Connection request is issued, the DriverManager asks each loaded driver to understand the URL sent. When the URL is not properly constructed, the "No Suitable Driver" message is returned.

20. Why do you have to close database connections in Java?

A database connection should be closed to help release other database resources like handles, cursor, etc. With every execution, the resultset, the connection and the statement. If the connection is made with a pool, closing it sends it back to the pool for reuse. This is done in the finally{} block so that if an exception is thrown, we can still close it.

21. What is the use of blob, clob datatypes in JDBC?

The blob, clob datatypes are used to store large amounts of large-sized data into databases like movies, images, etc.

22. How does Resultset as an interface support rs.Next()?

The next() method of the ResultSet interface moves the pointer of the current (ResultSet) object to the next row, from the current position.

23. What are the Metadata interfaces present in JDBC?

JDBC API has two Metadata interfaces DatabaseMetaData & ResultSetMetaData. The metadata provides data about the data that is stored in the database. These interfaces are implemented by database driver vendors to tell the capabilities of a Database to the users.

24. What is SavePoint? Give an example.

A savepoint is a point that sets up the mark/point to which the current transaction can be rolled back. This means one can choose which extent or the point the data should be rolled back to. For example, observe the procedure and use of savepoint.

• Start a transaction.
• Insert ten rows into a table.
• Set a savepoint.
• Insert another five rows.
• Rollback to the savepoint.
• Commit the transaction.

The table will contain the first ten rows inserted at the start of these instructions. The rollback will have deleted the other five rows. A savepoint is just a marker to which the current transaction can rollback.

JDBC Interview Questions for Experienced

If you are an experienced candidate in this domain with experience of at least 2+ years, these are the most likely questions an interviewer. Preparing yourself with JDBC Interview questions will enhance your knowledge and confidence.

1. What are the ResultSet types?

Three constants in the ResultSet can move the cursor backwards, forward, and in a particular row when defined in the result set.

• ResultSet.TYPE_FORWARD_ONLY: The cursor moves only forward in the result set.
• ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can move forwards and backwards, and the result set is not sensitive to changes made by others to the database that occur after the result set was created.
• ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can move forwards and backwards, and the result set is sensitive to changes made by others to the database that occur after the result set was created.

2. How do you create a JDBC application?

Here are the steps to create a JDBC application

• Import packages containing the JDBC classes needed in the database programming.
• Register the JDBC driver to open a communication channel with the database.
• Open a connection using the DriverManager.getConnection () method.
• Execute a query using an object of type Statement.
• Extract data from the result set using the appropriate ResultSet.getXXX () method.
• Clean up the environment by closing all database resources using the (Java Virtual Machine) JVM's garbage collection.

3. What are JDBC driver types?

There are four types of JDBC drivers:

• JDBC-ODBC Bridge plus ODBC driver − also called Type 1 driver. This driver calls the native code of the locally available ODBC driver.
• Native-API, partly Java driver − also called Type 2. This driver calls database vendor native library on a client-side. This code then talks to the database over a network.
• JDBC-Net, pure Java driver − also called Type 3 driver. The pure-java driver that talks with the server-side middleware then talks to the database.
• Native-protocol, pure Java driver − also called Type 4 driver. The pure-java driver uses a database native protocol.

4. Mention the usage of each of the JDBC drivers.

Following are the application instances of the four types of drivers

• Type 4 database is used when accessing databases, such as Sybase, Oracle, or IBM.
• Type 3 driver is preferred when the Java application is accessing multiple types of databases simultaneously; type 3 is the preferred driver.
• Type 2 drivers are useful when a type 3 or type 4 driver is unavailable for the current database.
• The type 1 driver is not considered a deployment-level driver and is typically used only for development and testing.

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

No. Only one Statement object can be made per connection using the JDBC-ODBC Bridge.

6. What are the standard isolation levels defined by JDBC?

JDBC defines the following standard isolation levels:

• TRANSACTION_NONE
• TRANSACTION_READ_COMMITTED
• TRANSACTION_READ_UNCOMMITTED
• TRANSACTION_REPEATABLE_READ
• TRANSACTION_SERIALIZABLE

7. Discuss the design pattern followed by JDBC.

JDBC follows a bridge design pattern. The JDBC API provides the abstraction, and the JDBC drivers provide the implementation. New drivers can be plugged-in to the JDBC API without changing the client code.

8. What is the difference between a statement and a prepared statement?

A Statement is used to access the database. A Statement interface cannot accept parameters, but it is useful when using static SQL statements at runtime. If SQL queries are to be run only once, this Statement interface is preferred over PreparedStatement. 
Prepared statements are pre-compiled and offer better performance. Prepared statements reuse the same execution plan for different arguments rather than creating a new execution plan every time. Prepared statements use bind arguments, which are sent to the database engine. Prepared statements are more secure as they use bind variables to prevent SQL injection attacks.

9. How do you register a driver?

There are two ways described for registering the Driver

• Class.forName() − This method loads the driver's class file into memory dynamically, automatically registering it. This method is mostly used as it allows us to make the driver registration configurable and portable.
• DriverManager.registerDriver() − This static method is used when using a non-JDK compliant JVM, such as the one provided by Microsoft.

10. What are the benefits of JDBC 4.0?

Benefits of JDBC 4.0:

• Autoloading of JDBC driver class. Manually registering and loading drivers using class.forName is not required.
• Connection management enhancements: New methods can be added to javax.sql.PooledConnection.
• DataSet implementation of SQL using annotations.
• SQL XML support.

11. How do you create a connection object?

There are three overloaded DriverManager.getConnection() methods to create a connection object

getConnection(String URL, String user, String password)Using a database URL with a username and password. For example
String URL = "jdbcoraclethin@amrood1521EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);

getConnection(String Url)Using only a database URL. For example
String URL = "jdbcoraclethinusername/password@amrood1521EMP";
Connection conn = DriverManager.getConnection(URL);

getConnection(String URL, Properties prop)Using a database URL and a Properties object. For example
            String URL = "jdbcoraclethin@amrood1521EMP";
            Properties info = new Properties( );
            info.put( "user", "username" );
            info.put( "password", "password" );

12. How to decide whether a Statement and its ResultSet will be closed on a commit or rollback?

Check using the DatabaseMetaData methods supportsOpenStatementsAcrossCommit() and supportsOpenStatementsAcrossRollback() to check if the Statement and its ResultSet will be closed on a commit or rollback.

13. What is the limit of the number of SQL statements that can be added to an instance of a Statement object?

There is no size limit for Statement.addBatch(). It is dependent on the driver.

14. How does the cursor work in a scrollable result set?

Several methods decide how the cursor moves in the database like first(), last(), beforeFirst(), afterLast(), absolute(int row), relative(int row), previous(), next(), getRow(), moveToInsertRow(), moveToCurrentRow().

15. How do you update a result set?

The methods in the ResultSet change the columns of the current row in the ResultSet object but not in the underlying database. To update the changes to the row in the database, invoking of one of the following methods is required
updateRow(), deleteRow(), refreshRow(), cancelRowUpdates(), insertRow()

16. When is the "No suitable driver" error encountered?

"No suitable driver" occurs during a call to the DriverManager using the getConnection method. It may occur due to any of the following reasons.

• Failing to load the appropriate JDBC drivers before calling the getConnection method.
• When an invalid JDBC URL is specified and not recognised by the JDBC driver.
• When the bridge needed by one or more of the shared libraries cannot be loaded.

17. How do you handle SQL NULL values in Java?

SQL's use of NULL values is very different from Java's use of null values. These values can be handled in three ways.

• The use of getXXX( ) methods that return primitive data types must be avoided.

• Use wrapper classes for primitive data types, and use the ResultSet object's wasNull( ) method to test whether the wrapper class variable that received the value returned by the getXXX( ) method should be set to null.

• Use primitive data types and the ResultSet object's wasNull( ) method to test whether the primitive variable that received the value returned by the getXXX( ) method should be set to an acceptable value that you've chosen to represent a NULL.

18. What are SQL warnings?

SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do. They simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method.

19. What are the steps followed to create a batch process?

Batch Processing with Statement or PrepareStatement Object can be done using the following steps:

• When using the PrepareStatement object, SQL statements should be created with placeholders.
• Statement or PrepareStatement object should be created with the use of createStatement() or prepareStatement() methods.
• Set auto-commit to false using setAutoCommit().
• Unlimited SQL statements can be added to the batch using the addBatch() method on created statement object.
• Execute all the SQL statements using the executeBatch() method on created statement object.
• Finally, commit all the changes using the commit() method.

20. What is a Stored Procedure? How is it called in JDBC?

A stored procedure is a group of SQL statements that form a logical unit to perform a particular task. For example, operations on an employee database (hire, fire, promote, lookup) can be coded as stored procedures which are then executed by application code. These Stored procedures are called using CallableStatement class in JDBC API. The code to explain this is given below:

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

21. What is JDBC SQL escape syntax?

The escape syntax gives the flexibility to the user to use database-specific features which are not available when using standard JDBC methods and properties.

• The general SQL escape syntax format is as follows
  {keyword 'parameters'}.
• JDBC defines escape sequences that contain the standard syntax for the following language features
  Date, time, and timestamp literals (d, t, ts Keywords).
• Scalar functions such as numeric, string, and data type conversion functions(fn Keyword).
  Outer joins(oj Keyword)
• Escape characters for wildcards used in LIKE clauses(escape Keyword).
  Procedure calls(call Keyword).

22. How are multiple rows inserted into a database in a single transaction?

//turn off the implicit commit
Connection.setAutoCommit(false);
//..your insert/update/delete goes here
Connection.Commit();
//a new transaction is implicitly started.

23. Explain the difference between execute, executeQuery, executeUpdate.

• boolean execute() - Executes any SQL statement.
• ResultSet executeQuery() - This is used when reading the content of the database. The output will be in the form of ResultSet. The SELECT statement is used with the executeQuery() statement.
• Int executeUpdate() - This is generally used for altering the databases. Generally, DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements are used. The output will be int, which gives the number of rows affected by the query.

24. What is Connection Pooling?

Connection Pooling is a technique used for the reuse of physical connections. This reduces the overhead for the application. Connection pooling functionality minimises expensive operations in the creation and closing of sessions. Multiple clients can share a cached set of connection objects that provides access to a database with the help of database vendors. 

25. How do you implement connection pooling?

When using application servers like JBoss, WebLogic, Tomcat,  WebSphere, the application server provides the facilities to configure for connection pooling. Components like Apache Commons DBCP Component can be used when an application server is not involved.

26. When there is a need to manipulate data from a database which of these can be used Out of byte[] or a java.sql.Blob?

java.sql.Blob has better performance as it does not involve data extraction from the database until it is asked explicitly.

27. What does the Class.forName("MyClass") do?

It Loads the class MyClass.
Execute any static block code of MyClass.
Returns an instance of MyClass.

28. What should be the value of attribute Concurrency set to in ResultSet?

The value determines whether the ResultSet can be updated or only read. A ResultSet can have one of two concurrency levels
ResultSet.CONCUR_READ_ONLY − means that the ResultSet can only be read.
ResultSet.CONCUR_UPDATABLE − means that the ResultSet can be both read and updated.

29. What are the differences between setMaxRows(int) and SetFetchSize(int)?

The difference between setFetchSize(int) and setMaxRow(int) are
setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows. setFetchSize(int) affects how the database returns the ResultSet data.
setMaxRows(int) method of the ResultSet specifies how many rows a ResultSet can contain at a time. setMaxRows(int) affects the client-side JDBC object.

30. What are the different types of RowSet objects?

There are two types of RowSet

• Connected: A connected RowSet Object is permanent and doesn't terminate until the application is terminated.
• Disconnected: A disconnected RowSet object is ad-hoc in nature. Whenever retrieving data from the database, it establishes the connection and closes it upon finishing the required task. The data modified during the disconnected state is updated after the connection is re-established.

31. What is a "dirty read"?

When one transaction reads and changes the value while the second transaction reads the value before committing or rolling back by the first transaction, this reading process is called a 'dirty read'. Because there is always a chance that the first transaction might roll back the change, which causes the second transaction to read an invalid value.

32. Which isolation level prevents dirty read in JDBC, connection class?

TRANSACTION_READ_COMMITTED prevents dirty reads.

33. What is the difference between JDBC, JNDI and Hibernate?

• Hibernate is an Object−Relational Mapping tool mapping objects to the relational data.
• The Java Naming and Directory Interface (JNDI) is an API to access different naming and directory services. It can be used to access anything that is stored in a directory or naming service without having to code specifically to that naming or directory service.
• Java DataBase Connectivity (JDBC) API is an API to access different relational databases.

Conclusion:

Here in this blog, we have covered almost all of the topics related to JDBC to help you get yourself prepared. And these JDBC Interview Questions can come to your aid when attending an interview. Prepare well until you feel confident. All the best!