JDBC Interview Questions
A list of top frequently asked JDBC interview questions and answers is given below.
1) What is JDBC?
JDBC is a Java API that is used to connect and execute the query to the database. JDBC API uses JDBC drivers to connect to the database. JDBC API can be used to access tabular data stored into any relational database.More details.
2) What is JDBC Driver?
JDBC Driver is a software component that enables Java application to interact with the database. There are 4 types of JDBC drivers:
3) What are the steps to connect to the database in java?
The following steps are used in database connectivity.
The forName() method of the Class class is used to register the driver class. This method is used to load the driver class dynamically. Consider the following example to register OracleDriver class.
The getConnection() method of DriverManager class is used to establish the connection with the database. The syntax of the getConnection() method is given below.
Consider the following example to establish the connection with the Oracle database.
The createStatement() method of Connection interface is used to create the Statement. The object of the Statement is responsible for executing queries with the database.
consider the following example to create the statement object
The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.
Syntax of executeQuery() method is given below.
Example to execute the query
However, to perform the insert and update operations in the database, executeUpdate() method is used which returns the boolean value to indicate the successful completion of the operation.
By closing connection, object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.
Syntax of close() method is given below.
Consider the following example to close the connection.
4) What are the JDBC API components?
The java.sql package contains following interfaces and classes for JDBC API.
5) What are the JDBC statements?
In JDBC, Statements are used to send SQL commands to the database and receive data from the database. There are various methods provided by JDBC statements such as execute(), executeUpdate(), executeQuery, etc. which helps you to interact with the database.
There is three type of JDBC statements given in the following table.
6) What is the return type of Class.forName() method?
The Class.forName() method returns the object of java.lang.Class object.
7) What are the differences between Statement and PreparedStatement interface?
8) How can we set null value in JDBC PreparedStatement?
By using setNull() method of PreparedStatement interface, we can set the null value to an index. The syntax of the method is given below.
9) What are the benefits of PreparedStatement over Statement?
The benefits of using PreparedStatement over Statement interface is given below.
10) What are the differences between execute, executeQuery, and executeUpdate?
11) What are the different types of ResultSet?
ResultSet is categorized by the direction of the reading head and sensitivity or insensitivity of the result provided by it. There are three general types of ResultSet.
12) What are the differences between ResultSet and RowSet?
13) How can we execute stored procedures using CallableStatement?
Following are the steps to create and execute stored procedures. Here, we are creating a table user420 by using a stored procedure and inserting values into it.
To call the stored procedure, you need to create it in the database. Here, we are assuming that the stored procedure looks like this.
The table structure is given below:
14) What is the role of the JDBC DriverManager class?
The DriverManager class acts as an interface between user and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver().More details.
15) What are the functions of the JDBC Connection interface?
The Connection interface maintains a session with the database. It can be used for transaction management. It provides factory methods that return the instance of Statement, PreparedStatement, CallableStatement, and DatabaseMetaData.More details.
16) What does the JDBC ResultSet interface?
The ResultSet object represents a row of a table. It can be used to change the cursor pointer and get the information from the database. By default, ResultSet object can move in the forward direction only and is not updatable. However, we can make this object to move the forward and backward direction by passing either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int, int) method.More details.
17) What does the JDBC ResultSetMetaData interface?
The ResultSetMetaData interface returns the information of table such as the total number of columns, column name, column type, etc.More details.
18) What does the JDBC DatabaseMetaData interface?
The DatabaseMetaData interface returns the information of the database such as username, driver name, driver version, number of tables, number of views, etc. Consider the following example.
Driver Name: Oracle JDBC Driver Driver Version: 10.2.0.1.0XE Database Product Name: Oracle Database Product Version: Oracle Database 10g Express Edition Release 10.2.0.1.0 -Production
19) Which interface is responsible for transaction management in JDBC?
The Connection interface provides methods for transaction management such as commit(), rollback() etc.More details.
20) What is batch processing and how to perform batch processing in JDBC?
By using the batch processing technique in JDBC, we can execute multiple queries. It makes the performance fast. The java.sql.Statement and java.sql.PreparedStatement interfaces provide methods for batch processing. The batch processing in JDBC requires the following steps.
Consider the following example to perform batch processing using the Statement interface.More details.
21) What are CLOB and BLOB data types in JDBC?
BLOB: Blob can be defined as the variable-length, binary large object which is used to hold the group of Binary data such as voice, images, and mixed media. It can hold up to 2GB data on MySQL database and 128 GB on Oracle database. BLOB is supported by many databases such as MySQL, Oracle, and DB2 to store the binary data (images, video, audio, and mixed media).
CLOB: Clob can be defined as the variable-length, character-large object which is used to hold the character-based data such as files in many databases. It can hold up to 2 GB on MySQL database, and 128 GB on Oracle Database. A CLOB is considered as a character string.
22) What are the different types of lockings in JDBC?
A lock is a certain type of software mechanism by using which, we can restrict other users from using the data resource. There are four type of locks given in JDBC that are described below.
23) How can we store and retrieve images from the database?
By using the PreparedStatement interface, we can store and retrieve images. Create a table which contains two columns namely NAME and PHOTO.
Consider the following example to store the image in the database.
Consider the following example to retrieve the image from the table.More details.
24) How can we store the file in the Oracle database?
The setCharacterStream() method of PreparedStatement interface is used to set character information into the parameterIndex. For storing the file into the database, CLOB (Character Large Object) datatype is used in the table. For example:
25) How can we retrieve the file in the Oracle database?
The getClob() method of PreparedStatement is used to get file information from the database. Let's see the table structure of the example to retrieve the file.
The example to retrieve the file from the Oracle database is given below.
26) What are the differences between stored procedure and functions?
The differences between stored procedures and functions are given below:
27) How can we maintain the integrity of a database by using JDBC?
To maintain the integrity of a database, we need to ensure the ACID properties. ACID properties mean Atomicity, Consistency, Isolation, and durability. In JDBC, Connection interface provides methods like setAutoCommit(), commit(), and rollback() which can be used to manage transaction. Let's see an example of transaction management in JDBC.
28) What is the JDBC Rowset?
JDBC Rowset is the wrapper of ResultSet. It holds tabular data like ResultSet, but it is easy and flexible to use. The implementation classes of RowSet interface are as follows:
29) What is the major difference between java.util.Date and java.sql.Date data type?
The major difference between java.util.Date and java.sql.Date is that, java.sql.Date represents date without time information whereas, java.util.Date represents both date and time information.
30) What does JDBC setMaxRows method do?
The setMaxRows(int i) method limits the number of rows the database can return by using the query. This can also be done within the query as we can use the limit cause in MySQL.