Java JDBC Interview Question Answers

Q1: What is JDBC?

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.
Architecture consists of two layers: JDBC API (application-to-JDBC Manager connection) and JDBC Driver API (JDBC Manager-to-Driver Connection).
JDBC API consists of following interfaces and classes: DriverManager, Driver, Connection, Statement, ResultSet, SQLException.

Q2: Explain Basic Steps in writing a Java program using JDBC?

JDBC makes the interaction with RDBMS simple and intuitive. When a Java application needs to access database :
Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).
Open the connection to database which is then used to send SQL statements and get results back.
Create JDBC Statement object. This object contains SQL query.
Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.
Process the result set.
Close the connection.

Q3: What are the main components of JDBC?

DriverManager: Manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection.
Driver: The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not call it explicitly.
Connection : Interface with all methods for contacting a database.The connection object represents communication context, i.e., all communication with database is through connection object only.
Statement : Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
ResultSet: The ResultSet represents set of rows retrieved due to query execution.

Q4: What is Statement?

Statement acts like a vehicle through which SQL commands can be sent. Through the connection object we create statement kind of objects. Through the connection object we create statement kind of objects.
Statement stmt = conn.createStatement();

Q5: What is benefit of using PreparedStatement in Java?

Better performance and prevents from SQL Injection.

Q6: Difference between SQL Date and java.util.Date in Java?

Main difference between SQL data i.e. java.sql.Date and util date i.e. java.util.Date is that SQL Date only contains date part and not time part but util date contains both date and time part.

Q7: How to call a stored procedure from JDBC ?

Stored procedures are called from within JDBC programs with prepareCall() method of the Connection object created. A call to this method takes variable bind parameters as input parameters as well as output variables and creates an object instance of the CallableStatement class.
CallableStatement stproc_stmt = conn.prepareCall("{call procname(?,?,?)}");

Q8: What are types of JDBC drivers?

Four types of drivers defined by JDBC as follows:
Type1: JDBC/ODBC—These require an ODBC (Open Database Connectivity) driver for the database to be installed. This type of driver works by translating the submitted queries into equivalent ODBC queries and forwards them via native API calls directly to the ODBC driver. It provides no host redirection capability.
Type2: Native API (partly-Java driver)—This type of driver uses a vendor-specific driver or database API to interact with the database. An example of such an API is Oracle OCI (Oracle Call Interface). It also provides no host redirection.
Type3: Open Protocol-Net—This is not vendor specific and works by forwarding database requests to a remote database source using a net server component. How the net server component accesses the database is transparent to the client. The client driver communicates with the net server using a database-independent protocol and the net server translates this protocol into database calls. This type of driver can access any database.
Type4: Proprietary Protocol-Net(pure Java driver)—This has a same configuration as a type 3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor's database. Again this is all transparent to the client.

Q9: What is a ResultSet?

It hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data. The java.sql.ResultSet interface represents the result set of a database query.
There are three constants which when defined in result set can move cursor in resultset backward, forward and also in a particular row.
ResultSet.TYPE_FORWARD_ONLY: The cursor can only move forward in the result set.
ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can scroll 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 scroll 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.

Q10: What are the standard isolation levels defined by JDBC?

The standard isolation levels are:

Q11: What is difference between RowSet and ResultSet in JDBC?

RowSet extends ResultSet and add support for JDBC API to Java bean component model. Main difference of ResultSet and RowSet is RowSet being connected and disconnected, which is another follow-up JDBC question. RowSet makes it easy to use ResultSet but as I said you only like to use it to get benefit of disconnected and connected RowSet.

Q12: What do you mean by cold backup, hot backup?

Cold back is the backup techniques in which backup of files are taken before the database restarted. In hot backup 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.

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

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

Q14: What are the locking system in JDBC?

Two 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

Q15: What is the difference between execute, executeQuery, executeUpdate?

Statement execute(String query) is used to execute any SQL query and it returns TRUE if the result is an ResultSet such as running Select queries. The output is FALSE when there is no ResultSet object such as running Insert or Update queries. We can use getResultSet() to get the ResultSet and getUpdateCount() method to retrieve the update count.

Statement executeQuery(String query) is used to execute Select queries and returns the ResultSet. ResultSet returned is never null even if there are no records matching the query. When executing select queries we should use executeQuery method so that if someone tries to execute insert/update statement it will throw java.sql.SQLException with message "executeQuery method can not be used for update".

Statement executeUpdate(String query) is used to execute Insert/Update/Delete (DML) statements or DDL statements that returns nothing. The output is int and equals to the row count for SQL Data Manipulation Language (DML) statements. For DDL statements, the output is 0.

Q16: 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.

Q17: What is batch processing?

Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.

Q18: What is SavePoint? Give an example.

A savepoint marks a point that the current transaction can roll back to. Instead of rolling all of its changes back, it can choose to roll back only some of them. For example, suppose you: start a transaction insert 10 rows into a table set a savepoint insert another 5 rows rollback to the savepoint commit the transaction After doing this, the table will contain the first 10 rows you inserted. The other 5 rows will have been deleted by the rollback. A savepoint is just a marker that the current transaction can roll back to.

Q19: What is Connection Pooling ?

Connection Pooling is a technique used for reuse of physical connections and reduced overhead for your application. Connection pooling functionality minimizes expensive operations in the creation and closing of sessions.Database vendor's help multiple clients to share a cached set of connection objects that provides access to a database. Clients need not create a new connection everytime to interact with the database.