JDBC Interview Questions

1.      What is JDBC?

JDBC (Java Database Connectivity) is a Java API that allows Java programs to interact with databases. It provides a set of classes and interfaces for connecting to databases, executing queries, and processing results.

2.      How does JDBC work?

JDBC works by providing a set of classes and interfaces that are used to connect to a database, send SQL statements, and process the results. The JDBC driver manager is responsible for managing the various JDBC drivers that are available, and the JDBC driver is responsible for communicating with the database.

3.      What is a JDBC driver?

A JDBC driver is a software component that enables a Java application to interact with a database. It provides the implementation of the JDBC API that allows the application to connect to a database, send SQL statements, and process the results.

4.      What are the different types of JDBC drivers?

There are four types of JDBC drivers: Type 1, Type 2, Type 3 and Type 4. Type 1 is JDBC-ODBC Bridge, Type 2 is Native-API driver, Type 3 is Network-Protocol driver and Type 4 is Pure Java driver (Thin driver)

5.      What is a connection in JDBC?

A connection in JDBC represents a session with a specific database. It is used to send SQL statements and retrieve results. The connection also provides information about the state of the connection, such as whether the connection is closed or open.

6.      What is a statement in JDBC?

A statement in JDBC is used to execute a specific SQL query or update. There are three types of statements: Statement, Prepared Statement and Callable Statement.

7.      What is a ResultSet in JDBC?

A ResultSet in JDBC represents the result of a query. It is a table of data that contains the rows retrieved by a SELECT statement. The ResultSet provides methods for navigating through the rows and retrieving the data in each column.

8.      How do you handle a SQLException in JDBC?

You can handle a SQLException in JDBC by catching the exception in a try-catch block and then using the methods of the SQLException class to get information about the error, such as the error message and the SQL state.

9.      How do you handle a transaction in JDBC?

You can handle a transaction in JDBC by using the setAutoCommit() method of the Connection class to disable the auto-commit feature, and then using the commit() and rollback() methods of the Connection class to control the transaction.

10.   How do you close a JDBC Connection, Statement and ResultSet?

You can close a JDBC Connection, Statement and ResultSet by using the close() method on each object. It's a good practice to close these resources in a finally block after they are used, so that they are closed even if an exception occurs.

11.   What is the difference between Statement and PreparedStatement?

A Condition is a simple statement that is executed and the results are returned immediately. A PreparedStatement is a pre-compiled statement that can be executed multiple times with different parameters. PreparedStatement is more efficient than a Statement as it is pre-compiled and can be reused.

12.   How can you improve the performance of JDBC?

Performance can be improved in JDBC by using PreparedStatements, using connection pooling, using a data-access framework such as Hibernate, using the correct JDBC driver, and by optimizing SQL queries.

13.   How can you ensure that a JDBC connection is closed properly?

You can ensure that a JDBC connection is closed properly by using a try-catch-finally block and closing the connection in the finally block. You can also use the try-with-resources statement, which automatically closes the resources when the program exits the block.

14.   How can you retrieve the value of an auto-generated key from a JDBC insert statement?

You can retrieve the value of an auto-generated key from a JDBC insert statement by using the getGeneratedKeys() method of the Statement or PreparedStatement class.

15.   What are the benefits of using a connection pool?

Connection pooling allows you to reuse connections to a database, reducing the overhead of creating a new connection each time a connection is needed. Connection pooling also improves performance by reducing the number of connections that need to be created and closed.

16.   How can you handle a database deadlock in JDBC?

You can handle a database deadlock in JDBC by using the setTransactionIsolation() method of the Connection class to set the transaction isolation level to "Serializable" and retrying the transaction until it is successful.

17.   How can you handle a database timeout in JDBC?

You can handle a database timeout in JDBC by using the setQueryTimeout() method of the Statement or PreparedStatement class to set the query

18.   How can you execute a stored procedure using JDBC?

You can execute a stored procedure using JDBC by creating a CallableStatement, setting the parameters using the setXXX() methods, and then calling the execute() method.

19.   How can you retrieve the metadata of a database using JDBC?

You can retrieve the metadata of a database using JDBC by using the getMetaData() method of the Connection class to get an instance of the DatabaseMetaData class. You can then use the methods of the DatabaseMetaData class to retrieve information about the database, such as the number of tables, columns, and primary keys.

20.   How can you fetch large data using JDBC?

You can fetch large data using JDBC by using the setFetchSize() method of the Statement or PreparedStatement class to specify the number of rows to be fetched at a time, and using the setMaxRows() method to limit the maximum number of rows to be retrieved.

21.   How can you handle a connection leak in JDBC?

You can handle a connection leak in JDBC by using a connection pool and properly closing the connection after use. You can also use a tool such as a profiler to detect connection leaks and fix the underlying problem.

22.   What is a JDBC batch update?

A JDBC batch update is a feature that allows you to execute multiple SQL statements as a single batch, rather than executing them one at a time. This can improve performance by reducing the number of round trips to the database.

23.   How can you use a DataSource in JDBC?

You can use a DataSource in JDBC to get a connection to a database. A DataSource is a Java object that provides a connection to a database. It can be configured with information such as the database URL, username, and password, and it can also be used for connection pooling.

24.   How can you handle a SQL injection attack in JDBC?

You can handle a SQL injection attack in JDBC by using PreparedStatements with parameterized queries and by properly escaping user input.

25.   What is the difference between Statement.execute() and Condition.executeQuery()?

The execute() method of the Statement class can be used to execute any type of SQL statement, including DDL and DML statements, whereas executeQuery() method should be used for SELECT statements and it returns a ResultSet object.

26.   What is the difference between executeUpdate() and execute() method?

executeUpdate() method is used for DDL and DML statements that return the number of rows affected and execute() method is used for DDL and DML statements that return a Boolean value indicating if the statement was successful or not.

27.   How can you handle a database disconnection in JDBC?

You can handle a database disconnection in JDBC by using a connection pool and properly handling the SQLException that may occur when a connection is lost. You can also use a tool such as a connection validation query to detect and fix the disconnected state.

28.   How can you handle a duplicate key exception in JDBC?

You can handle a duplicate key exception in JDBC by using the SQLException class to catch the exception and then using the getErrorCode() method to check if the error code corresponds to a duplicate key violation.

29.   How can you handle a data truncation exception in JDBC?

You can handle a data truncation exception in JDBC by using the SQLException class to catch the exception and then using the getErrorCode() method to check if the error code corresponds to a data truncation error.

30.   How do you use a DataSource to obtain a connection in a JNDI environment?

You can use a DataSource to obtain a connection in a JNDI environment by looking up the DataSource object in the JNDI tree and then using the getConnection() method to get a connection.

31.   How can you retrieve the generated keys after an insert statement?

You can retrieve the generated keys after an insert statement by using the getGeneratedKeys() method of the Statement or PreparedStatement class. This method returns a ResultSet object containing the generated keys.

32.   What is the purpose of the setFetchSize() method in JDBC?

The setFetchSize() method in JDBC is used to set the number of rows that should be retrieved from the database in each fetch. This can be used to optimize performance by reducing the number of round-trips to the database.

33.   How do you handle a database rollback in JDBC?

You can handle a database rollback in JDBC by using the rollback() method of the Connection class. This method undoes any changes that were made to the database during the current transaction.

34.   How can you handle a database timeout in JDBC?

You can handle a database timeout in JDBC by using the setQueryTimeout() method of the Statement or PreparedStatement class to set the number of seconds that a query should wait for a response before timing out.

35.   How can you handle a database commit in JDBC?

You can handle a database commit in JDBC by using the commit() method of the Connection class. This method makes all changes made to the database during the current transaction permanent.

36.   What is the purpose of the setMaxRows() method in JDBC?

The setMaxRows() method in JDBC is used to set the maximum number of rows that should be retrieved from a query. This can be used to limit the amount of data retrieved from the database, which can improve performance.

37.   How can you handle a database connection leak in JDBC?

You can handle a database connection leak in JDBC by properly closing all connections when they are no longer needed and monitoring the number of open connections to detect any leaks. Additionally, you can also use a connection pool to minimize the number of connections that need to be created.

38.   How can you handle a database lock in JDBC?

You can handle a database lock in JDBC by using the setTransactionIsolation() method of the Connection class to set the transaction isolation level to "Serializable", which will prevent multiple transactions from modifying the same data at the same time. Additionally, you can also use the setLockTimeout() method to specify the time a lock should wait before timing out.

39.   How can you handle a database deadlock in JDBC?

You can handle a database deadlock in JDBC by using the setTransactionIsolation() method of the Connection class to set the transaction isolation level to "Serializable", which will prevent multiple transactions from modifying the same data at the same time. Additionally, you can also use the setDeadlockTimeout() method to specify the time a deadlock should wait before timing out.

40.   How can you handle a database connection timeout in JDBC?

You can handle a database connection timeout in JDBC by increasing the connection timeout value using the setLoginTimeout() method of the DriverManager class. Additionally, you can also use connection pooling and monitoring tools to detect and fix any connection timeout issues.