Top JDBC Interview Questions and Answers (2024)

Introduction

JDBC is a Java Application Programming Interface (API) useful for communicating with databases and running SQL queries. The acronym JDBC stands for Java Database Connectivity. JDBC drivers are used to establish a connection to the database. Access to tabular data kept in relational databases like Oracle, MySQL, MS Access, etc. is made possible through JDBC API.

Earlier, to establish a connection with the database and run the queries, the ODBC API was utilized as the database API. However, ODBC API for ODBC drivers employs C language (i.e. platform-dependent and unsecured). As a result, Java has developed its own JDBC API that uses JDBC drivers and provides a native Java interface for interacting with databases using SQL. For the creation of a "pure Java" solution, while employing Java programming, JDBC is necessary.

There are some JDBC Interview Questions that are important and need to be done before an interview. So let us go through some of the top JDBC Interview questions.

Most Asked JDBC Interview Questions

Here are some JDBC Interview Questions. These JDBC Interview Questions need to be done thoroughly before an interview.

1. What is JDBC in Java?

A Java API called JDBC (Java Database Connectivity) is used for interacting with a database to access, modify, and process data using SQL. JDBC drivers will be used to connect to the database. We may access tabular data held in many relational databases, like Oracle, MySQL, MS Access, etc., by utilizing JDBC.

2. What are the JDBC API components?

The following are some of the main API components of JDBC:-

• Driver Manager: The DriverManager class manages a list of database drivers. It is responsible for establishing a connection to the database.
   
• JDBC Drivers: JDBC drivers are platform-specific implementations that allow Java applications to connect to a particular database system.
   
• Connection Interface: The Connection interface represents a connection to a specific database. It is obtained from the DriverManager and used to create Statement objects and manage transactions.
   
• Statement Interface: The Statement interface is used to execute SQL queries on the database.
   
• ResultSet Interface: The ResultSet interface represents the result set of a SQL query. It provides methods for iterating through the rows of the result set.

3. What is DriverManager in JDBC?

Ans: Java's static JDBC DriverManager class is used to control the selection of JDBC drivers that are accessible to applications. An application can use many JDBC drivers at once if necessary. Each application identifies a JDBC driver by utilizing a Uniform Resource Locator (URL).
Using Class.forName() or DriverManager.registerDriver(), the JDBC Driver class registers itself with the DriverManager when it is loaded into an application (). You can examine the JDBC Driver classes' source code to verify this. After that, DriverManager will use registered drivers to retrieve the connection and deliver it to the calling program when we execute the DriverManager.getConnection() method and send the database configuration information to it.

4. Which JDBC driver is more effective and widely used?

The JDBC Net pure Java driver (Type 4 driver) directly communicates with the database by translating JDBC calls into vendor-specific protocol calls, making it the quickest driver for localhost and remote connections.

5. How can one connect to a database in Java, and what steps are involved?

To connect to a database in Java using JDBC, you need the following steps:-

• Load the JDBC Driver: Load the appropriate JDBC driver for your database. This step is usually optional in modern JDBC implementations, as drivers can be automatically loaded, but it's a good practice to include it.
   
• Establish a Connection: Use the DriverManager to create a database connection by providing the database URL, username, and password.

String url = "your database URL"
String username = "yourusername";
String password = "yourpassword";

Connection connection = DriverManager.getConnection(url, username, password);

With the returned connection, you can create statements to execute SQL queries.

6. What types of data are used in the database table to store the image and file?

The image is kept in the database using the BLOB data type. The BLOB data type can be used to hold audio and video files as well. Data of the binary kind is stored there. The file is kept in the database using the CLOB data type. It keeps data of the character type.

7. Why are we utilizing DatabaseMetaData, and what do you mean by it?

An interface called DatabaseMetaData offers ways to find out information about the database. This can be used to obtain database-related data such as the name of the database, its version, the name of the driver, the total number of tables or views, etc.

8. Explain JDBC Batch processing.

Executing several SQL statements simultaneously is known as batch processing. Take the scenario of importing data from CSV (Comma-Separated Values) files into relational database tables as an example. We can utilize batch processing, which executes the majority of queries for a database in one go, in place of Statement or PreparedStatement.

9. Advantages of Batch processing.

It will cut down on communication time while enhancing performance. Large amounts of data can be processed more quickly and consistently with batch processing. Due to the reduced amount of database calls, it is substantially faster than processing one statement at a time.

10. How to perform Batch processing?

The methods addBatch() and executeBatch() are used for batch processing. The Statement and PreparedStatement classes of the JDBC API contain these 2 methods.

11. What is DataSource in JDBC?

The javax.sql package contains an interface called DataSource that is used to retrieve the database connection. It can be utilized as a decent DriverManager class substitute since it gives your application program access to database information.
The DriverManager does not register a driver that is accessed through a DataSource object. Instead, a lookup operation is utilized to retrieve a DataSource object, which may then be used to generate a Connection object.

12. Benefits of DataSource

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

13. Explain the usage of the getter and setter methods in ResultSet.

Getter methods: These are used to get specific table column values from the ResultSet. Either the column name or the column index value should be given as a parameter. The getter method is typically represented by the getXXX() methods.
Example:
int getInt(string columnName)

Setter Methods: 
The value in the database is set using these techniques. It is almost identical to getter methods, but in this case, you must also give the name of the column or its index value in addition to the data or values for the specific column you want to insert into the database. Setter methods are typically represented as setXXX() methods.
Example:
void setInt(int columnIndex, int dataValue)

14. What is database connection pooling?

When connection pooling is implemented, database connections are saved in the cache and can be re-used for subsequent database requests. The client does not have to establish new connections each time to interact with the database with this approach. In its place, connection objects are kept in the connection pool and are obtained by the client from there.

15. Advantages of the connection pool.

It is faster.
Analyzing and diagnosing database connections is made simpler.
Improves the speed at which commands are carried out on databases. 

16. What is optimistic locking?

Only when an update occurs will the record be locked. When reading or selecting the record, this sort of locking will not use exclusive locks.

17. What is pessimistic locking?

As soon as it chooses the row to update, it locks the record. The design of this locking mechanism ensures that the modifications are carried out reliably and safely.

18. What are the types of JDBC architecture?

Two-tier Architecture: Here, the database and Java programs are explicitly linked. Except for the JDBC driver, no intermediary is needed to connect to the database, such as an application server. Another name for it is a client-server architecture.

Three-tier Architecture: In contrast to two-tier architecture, it is completely different. The Java application or JDBC driver won't explicitly communicate with the database. It will act as a bridge between them by using an application server. An application server will forward the request to the database via Java code, and the server will then get the response from the database.

19. What is JDBC Transaction Management?

A transaction is a name given to the series of operations (SQL statements) that made up the transaction. In RDBMS-oriented systems, transaction management plays a crucial role in preserving the consistency and integrity of the data.

20. Need for Transaction Management.

The auto-commit mode will be chosen by default when initiating a connection to the database. This implies that each time the request is processed, it will be immediately committed when finished.
The transaction may need to be committed once a few more SQL statements have been executed. We must set the auto-commit value to False in such a case. As a result, the data cannot commit until all of the queries have been run. If there is an exception during the transaction, we can rollback() the modifications and restore the previous state.

21. What is a Two-phase commit in JDBC?

In a distributed context where several processes participate in the distributed transaction process, a two-phase commit is beneficial. Simply put, a two-phase commit will be utilized to ensure that all databases are in synchronization with one another while a transaction is active and it affects several databases.

In a two-phase commit, the following two phases are used to accomplish the commit or rollback:
Commit request phase: In this phase, the primary process or coordinator process asks all other processes to vote on whether or not they have successfully finished their processes and are prepared to commit. If all the votes are "yes," they move on to the next phase. If "No," rollback will be carried out.
Commit Phase: If every vote is "yes," commit is completed.

Similar to this, whenever a transaction modifies several databases after it has been executed, it issues a pre-commit instruction to each database and requests an acknowledgment from each database. If all of the transactions are positive upon acknowledgment, the commit instruction will be sent; otherwise, a rollback will be carried out.

22. What are the isolation levels of connections in JDBC?

The transaction isolation level determines the threshold at which inconsistent data is allowed in a transaction, determining the degree to which one transaction is isolated from another. Data correctness will increase with increased isolation, but there is a chance that fewer concurrent transactions may occur. Comparably, although allowing for more concurrent transactions, a lesser level of isolation compromises data accuracy.
The DBMS uses locks to restrict access to the data that is involved in the transaction in order to maintain data integrity during JDBC transactions. These locks are required to protect the database from Dirty Read, Non-Repeatable Read, and Phantom-Read.
It can be set using the setTransactionIsolation() method and is used by DBMS as part of the locking mechanism. Utilizing the getTransactionIsolation() method, you may learn more about the isolation level that the connection is using.

23. What is Result set and its types?

A result set is an object that represents the data retrieved from a database after a query operation. Below are some common types of result sets:

• Single-Row Result Set: This type of result set contains a single row of data.
• Multi-Row Result Set: This result set contains multiple rows of data.
• Empty Result Set: An empty result set denotes that there are no rows in the database.
• Update Result Set: This result set provides you to read as well as modify the data in the result set.

24. Explain the distinctions among the execute, executeQuery, and executeUpdate methods?

• Execute: Use execute when there is a need to handle both the queries and updates and you want to execute any SQL query.
• ExecuteQuery: This query returns a ResultSet object with containing the query result. You can use this when you want to execute a SELECT statement.
• ExecuteUpdate: You can use this, when you want to execute SQL statements that modify the database.

25. What advantages does PreparedStatement offer compared to Statement?

PreparedStatement offers advantages as compared to Statement are performance improvement, preventing SQL injection attacks, query optimization, improving code readability, and batch processing.

26. Explain the differences between Statement and PreparedStatement interface?

• PreparedStatement allows parameter blinding and enables the use of placeholders for dynamic values. While Statement does not support parameterization.
• PreparedStatement can execute multiple times. On the other hand, Statement needs to optimize the SQL statement every time.
• PreparedStatement offers better performance than Statement.
• PreparedStatement support parameter binding, which prevents SQL injection attacks. While Statement does not offer this protection.

27. How can we execute stored procedures using CallableStatement?

To execute stored procedures using JDBC with a CallableStatement, you should follow these steps:-

Create a Connection

Establish a database connection using the driver manager.

Prepare the CallableStatement

Create a CallableStatement by specifying the SQL statement that calls the stored procedure. The following syntax uses “?” as the parameter placeholders.

CallableStatement callableStatement = connection.prepareCall("{call your_stored_procedure(?, ?, ?)}");

Set Input Parameters

If your stored procedure has input parameters, use the "set()" methods of the `CallableStatement` to set their values like the following.

callableStatement.setInt(1, parameter1Value);
callableStatement.setString(2, parameter2Value);
callableStatement.setDouble(3, parameter3Value);

Execute the CallableStatement

After setting the required input parameters, you can execute the stored procedure using the "execute()" or "executeQuery()" method of the CallableStatement.

callableStatement.execute();

28. Explain  the differences between ResultSet and RowSet?

29. Explain the differences between Statement and PreparedStatement interface?

30. What do you mean by JDBC statements?

JDBC stands for Java Database Connectivity. It refers to the objects that are used to execute SQL commands against a database. You can execute the SQL statement using methods like executeQuery(), execute(), or executeUpdate() once the JDBC statement is created.