This tutorial provides frequently asked JDBC interview questions and answers with explanations to help you prepare for the interview:
In the JDBC Batch Processing and Stored Procedure tutorial of the JDBC tutorial series, we learned methods to do batch processing in Java using JDBC driver and also learned to create stored procedures and call it from a Java program.
JDBC is the commonly used short form for Java Database Connectivity. By using JDBC, we can interact with different types of Relational Databases such as Oracle, MySQL, MS Access, etc.
This article will help you to crack the JDBC interview. We have explained here all the important JDBC concepts.
Table of Contents:
Frequently Asked JDBC Interview Questions
Q #1) What is JDBC?
Answer: Java Database Connectivity is unofficially known as JDBC. It is used to perform DB operations in Database from Java application. It supports interaction with any kind of DB like Oracle, MySQL, MS Access, etc.
Q #2) What is the use of the JDBC driver?
Answer: It is a software component and is used to make the Java application to interact with the Database.
Q #3) What are the different types of drivers in JDBC?
Answer: There are 4 different JDBC drivers out there in the market.
They are:
- Type I: JDBC – ODBC Bridge
- Type II: Native API – Half Java Driver
- Type III: Network Protocol– Totally Java Driver
- Type IV: Thin Driver- Totally Java Driver
Type I: JDBC-ODBC Bridge
JDBC-ODBC bridge is going to behave as an interface between the client and the DB server. The client should put the JDBC-ODBC driver in it. The database ought to support the ODBC driver. If we are not concerned about the driver installation within the client system, we will use this driver.
Type II: Native API: Half Java Driver
It is almost like a JDBC-ODBC driver. Rather than an ODBC driver, we are using native API here. Libraries of the client-side database are used.
Type III: Network Protocol
It works like a 3-tier approach to access the database. An intermediate server will be used to connect to DB. JDBC method calls send data to an intermediate server then the server will communicate with DB.
Type IV: Thin Driver
It is absolutely written in Java. It explicitly converts JDBC method calls into the vendor-specific database protocol. Nowadays, Database merchant itself is providing this type of driver for their customers. So programmers don’t rely on other sources. It gives higher performance than the other drivers.
Q #4) Which type of JDBC driver is used by most people?
Answer: Type IV Thin driver is used in most of the applications. It is developed by the database vendor itself so the developers can use it directly without depending on any other sources. It allows for simple and easy development. It gives higher performance than the other drivers.
Q #5) What are the types of JDBC Architecture?
Answer: JDBC supports 2 kinds of processing models to access the DB.
They are:
- Two-tier Architecture: Here Java programs explicitly connect with DB. We don’t need any mediator like applications server to connect with DB except the JDBC driver. It is also known as a client-server architecture.
- Three-tier Architecture: It is totally inverse of two-tier architecture. There will be no explicit communication between the JDBC driver or Java program and Database. An application server is used as a mediator between them. Java program will send the request to an application server, and the server will send it and receive the response to/ from DB.
Q #6) What are the components of JDBC?
Answer: There are 4 major components that are available in JDBC.
They are:
- JDBC API
- JDBC Driver Manager
- JDBC Test Suite
- JDBC – ODBC Bridge
Q #7) What are the steps to connect with JDBC?
Answer: There are 6 basic steps to connect with DB in Java. These are enlisted below:
- Import package
- Load driver
- Establish connection
- Creation and execution of the statement
- Retrieve results
- Close connection
Q #8) Which data types are used to store the image and the file in the database table?
Answer:
- BLOB data type is used to store the image in DB. We can store videos and audios as well in the BLOB data type. It is used to store the binary type of data.
- CLOB data type is used to store the file in DB. It is used to store the character type of data.
Q #9) What is DriverManager in JDBC?
Answer: DriverManager is an in-built class that is present in the java.sql package. It will be used as a mediator between the Java Application and DB, which we are connecting/using in our code. As a first step, we need to register or load the driver with DriverManager. Then the driver will be available to use in the application.
The main function of DriverManager is to load the driver class of the Database and create a connection with DB.
There are 2 ways to register or load the driver:
- Class.forName()
- DriverManager.registerDriver()
Q #10) What is the difference between Statement and PreparedStatement interfaces.
Answer: The below table explains the differences:
| STATEMENT | PREPARED STATEMENT |
|---|---|
| It will be mainly used for executing static SQL statements | It will be mainly used for executing pre-compiled SQL statements |
| It will not accept parameters at runtime | It will accept different parameters at runtime |
| Its performance is less compared to preparedStatement | Its performance is higher than Statement since it is executing the precompiled SQL statements |
| It is appropriate for executing DDL statements such as CREATE, DROP, ALTER and TRUNCATE | It is appropriate for executing DML statements such as INSERT, UPDATE, and DELETE |
| It can’t be used for storing or retrieving image and file in DB | It can be used for storing or retrieving image and file in DB |
| It enforces SQL Injection | It prevents SQL Injection |
Suggested reading =>> JDBC PreparedStatement and Statement
Q #11) Explain the difference between execute(), executeQuery() and executeUpdate().
Answer:
| executeQuery() | executeUpdate() | Execute() |
|---|---|---|
| It is used to execute the SQL statements which retrieve some data from DB | It is used to execute the SQL statements which will update or modify the data in DB | It is used to execute any kind of SQL statements |
| It returns the resultSet object | It returns an integer value which represents the no. of affected rows | It returns a Boolean value TRUE – returns a resultSet object FALSE – returns an int value or nothing |
| It is used to execute only SELECT Query | It is used to execute only a non-SELECT query | It is used to execute both SELECT and non-SELECT queries |
Q #12) How to call Stored Procedures in JDBC?
Answer: We can execute the SQL Stored procedures through the CallableStatement interface. The CallableStatement object can be created using the prepareCall() method of the Connection interface.
Q #13) What is the ResultSet interface?
Answer: ResultSet interface is used to store the output data after the SQL query execution. The object of ResultSet maintains the cursor point at the result data. As a default, the cursor points before the first row of the result data. We can traverse the data in the resultset objects as well.
Syntax:
Statement Interface:
Statement stmnt1 = conn.createStatement(); ResultSet resultset = stmnt1.executeQuery(“Select * from EMPLOYEE”);
PreparedStatement Interface:
PreparedStatement pstmnt1 = conn.prepareStatement(insert_query); ResultSet resultset = pstmnt1.executeQuery(“Select * from EMPLOYEE”);
Q #14) What are the types of ResultSet?
Answer: There are 3 types in ResultSet. These are:
- TYPE_FORWARD_ONLY: It is the default option. The cursor will move from start to end.
- TYPE_SCROLL_INSENSITIVE: In this type, the cursor will move in both forward and backward directions. Dataset has the data when the SQL query returns the data.
- TYPE_SCROLL_SENSITIVE: It is the same as TYPE_SCROLL_INSENSITIVE, the difference is that it will have the updated data while iterating the resultset object.
Q #15) What are the concurrency modes in ResultSet?
Answer: There are 2 different modes of Concurrency in ResultSet. They are:
- ResultSet.CONCUR_READ_ONLY: It is the default concurrency mode. A read-only option is available. Updation is not possible.
- ResultSet.CONCUR_UPDATABLE: Updation is possible.
Q #16) How to check whether the database supports the concurrency mode?
Answer: We have the supportsResultSetConcurrency() method which will be used to check whether the given type and concurrency modes are supported by the database or not.
Q #17) Can we get the data of the particular row from the resultset?
Note: ResultSet has the data of a set of rows
Answer: Yes, we can get the data of the particular row from the resultSet using the relative() method. It will move the cursor to the given row either in a forward or in a backward direction from the current row. If the positive value has been given, it will move in the forward direction. If the negative value has been given, it will move in the backward direction.
Q #18) What is the use of the getter and setter methods in ResultSet?
Answer:
Getter methods: These are used to retrieve the values of the particular column of the table from ResultSet. Either the Column Index value or Column Name should be passed as a parameter. Normally, we will represent the getter method as getXXX() methods.
Example:
- int getInt(string Column_Name): It is used to retrieve the value of the specified column Index and int data type as a return type.
Setter Methods: We can set the value in the database using ResultSet setter methods. It is similar to getter methods, but here we need to pass the values/data for the particular column to insert into the database and the index value or column name of that column. Normally we will represent the setter method as setXXX() methods.
Example:
- void setInt(int Column_Index, int Data_Value): It is used to insert the value of the specified column Index with an int value.
Q #19) What is the main purpose of the ResultSetMetaData interface?
Answer: This interface gives more information about ResultSet. Each ResultSet object has been associated with one ResultSetMetaData object.
This object will have the details of the properties of the columns like datatype of the column, column name, the number of columns in that table, table name, schema name, etc., getMetaData() method of ResultSet object is used to create the ResultSetMetaData object.
Syntax:
PreparedStatement pstmntobj = conn.prepareStatement(insert_query); ResultSet resultsetobj = pstmntobj.executeQuery(“Select * from EMPLOYEE”); ResultSetMetaData rsmd obj= resultsetobj.getMetaData();
Q #20) What is DatabaseMetaData?
Answer: The DatabaseMetaData interface gives information about the Database we are using. We will get the following information – DatabaseName, Database version, and so on.
Q #21) What is ACID property?
Answer:
- A–Atomicity -> If all the queries have executed successfully, then the data will be committed else won’t commit.
- C–Consistency -> Data should be consistent after any transaction.
- I–Isolation -> Each transaction should be isolated.
- D–Durability -> If the transaction is committed once, it should be available always (if no changes have happened)
Q #22) How to change the auto-commit mode value?
Answer: By default, the value of AutoCommit is TRUE. After the execution of the SQL statement, it will be committed automatically. Using the setAutoCommit() method, we can change the value to AutoCommit.
Q #23) What is the use of Commit and Rollback methods?
Answer:
Commit() method: We have the commit() method in Java to commit the data. Once the SQL execution is done, we can call the commit method.
Syntax: connectionobj.commit();
Rollback() method: We have the rollback() method in Java to rollback the data. Rollback means to undo the changes. If any of the SQL statements are failed, we can call the rollback method to undo the changes.
Syntax: connectionobj.rollback();
Q #24) What is savepoint and what are the methods we have in JDBC for savepoint?
Answer: Savepoint is used to create checkpoints in a transaction, and it allows us to perform a rollback to the specific savepoint. Once the transaction is committed or rolled backed, the savepoint that has been created for a transaction will be automatically destroyed and becomes invalid.
Methods for Savepoint:
- setSavepoint() method: It is used to create Savepoint, we can use the rollback() method to undo all the changes till the savepoint.
- releaseSavepoint() method: It is used to remove the given savepoint.
Q #25) List some exceptions that come under SQLException?
Answer:
- SQLNonTransientException
- SQLTransientException
- SQLRecoverableException
>> Click here for more information about the above exceptions.
Q #26) What is batch processing and how to do it in JDBC?
Answer: Batch processing is the process of executing several SQL statements in one transaction. Doing so will reduce communication time and increase performance. It makes processing a large amount of data much easier.
Advantages of Batch Processing:
- Improve performance
- Data consistency
How to perform Batch Processing:
We have addBatch() and executeBatch() methods in Java to perform Batch processing. These 2 methods are present in Statement and PreparedStatement classes.
Q #27) What is the stored procedure?
Answer: A group of SQL queries that are executed as a single unit to perform a particular task is known as a Stored Procedure. We can pass 3 different types of parameters. Each procedure is represented by its name. So the name of the procedure should be unique.
>> For more information about the Stored procedure, refer to this link.
Q #28) What are the parameter types in Stored Procedures?
Answer: There are three types of parameters available in Stored Procedures. They are:
- IN: Used to pass the input values to the procedure.
- OUT: Used to get the value from the procedure.
- IN/OUT: Used to pass the input values and get the value to/from the procedure.
Conclusion
These are some JDBC interview questions that cover both the basic and advanced levels. We hope that this tutorial will give you an overview of JDBC. The explanations which are given above will enrich your knowledge and increase your understanding of JDBC. All the Best!!!
<<PREV Tutorial | FIRST Tutorial