JDBC Interview Questions and Answers

1) What is JDBC ?
Ans). JDBC is nothing but Java Database Connectivity, which consist of classes and interfaces for connecting Java applications with databases in a platform and database independent manner.

2) What is JDBC Driver ?
Ans). JDBC driver is use to connect with database

1. A JDBC Driver is a software, it provides the implementation classes for interfaces of JDBC API.
2. A JDBC Driver is a mediator between a Java application and a database.
3. A JDBC Driver is a product, created by vendor.

3) What is JDBC Specification ?
Ans). JDBC specification is a document, it contains the information needed by programmers and vendors. Programmer reads the specification, to develop the applications to connect with database and the vendor reads the specification to creating the JDBC products (Drivers).

4) What are the different types of JDBC Drivers ?
Ans). In JDBC we have 4 different types of drivers.

1. 1). JDBC-ODBC bridge Driver (Type-1).
2. 2). Native-API partly Java Driver (Type-2).
3. 3). Net-Protocol Pure Java Driver (Type-3).
4. 4). Native-Protocol Pure Java Driver (Type-4).

5) What is type 1 Driver ?
Ans). The Type-1 driver is given by the sun micro systems as a part of the JDK. When the JDBC technology was introduced, there were no vendor’s drivers. So the Type-1 Driver is given by the sun, is used to connect Java program with database.
JDBC-ODBC bridge is considered to be the Type-1 Driver, The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls.
The Type-1 Driver connects our Java program to ODBC driver and then connects ODBC driver connects Database.

6) What is DSN ?
Ans). Internally Type-1 Driver uses JDBC-ODBC bridge driver. The JDBC-ODBC bridge driver needs database name and its location to connect with database. JDBC-ODBC bridge driver gathers all the required information from a file called DSN.
These DSNs are 3 types :

1. 1). System DSN
2. 2).User DSN
3. 3).FileDSN

A System DSN is a sharable DSN for multiple user accounts of a system.
User DSN is a non sharable DSN for multiple user accounts. It is a local DSN and it is only accessible for a particular user account.
System DNS and User DSN both are stored in a registry called File DSN.

7) What is type 2 Driver ?
Ans). Native-API partly Java Driver is considered to be the Type-2 driver. Here Native means a set of functions written in C , C++ languages.
Type-2 driver uses Native API supplied by a database vendor to connect with the database.
Type-2 driver converts JDBC calls into native calls to connect a Java program with database.
Oracle oci driver is a Type-2 driver. It was given by Oracle.

8) What is type 3 Driver ?
Ans). Net-Protocol Pure Java Driver is considered to be the Type-3 driver. Type-3 driver is a pure Java driver, it means it is a platform independent driver.
Type-3 driver first connects Java program with server, instead of directly connected with database. In the past, Type-3 driver is used for security reasons. But today we are using connection pooling technique.

9) What is type 4 Driver ?
Ans). Native-Protocol Pure Java Driver is considered to be the Type-4 driver. This Type-4 driver uses native protocol accepted by a database server to establish connectivity between a Java program and a database.
It is pure Java driver, it means the driver is 100% implemented by using the Java Language.
Native protocol means, it is not a common protocol.
Each Database server has a separate protocol to accept a request.
Example: TTC (Two Task Common) is a native protocol accepted by Oracle.
Mysql is a native protocol accepted by Mysql.

10) What are the steps to connect the Database in Java ?
Ans). Typically we have 6 different steps to develop a JDBC, to connect Java application with database.

1. 1). Load a JDBC Driver Class.
2. 2). Establish a Connection.
3. 3). Create a Statement.
4. 4). Execute SQL Queries.
5. 5). Process ResultSet.
6. 6). Close the Connection.

11) What is a Statement Object in JDBC, and what does it ?
Ans). Statement is an interface coming from the java.sql package. We can get the Statement object by calling the createStatement() method on connection object. In order to send SQL commands from Java program to database we need a Statement object.

12) What is PreparedStatement in JDBC ?
Ans). PreparedStatement is an interface coming from the java.sql package, it extends the Statement interface.
In an application, if we want to run or execute same query for multiple times with different values on a database, then we can choose the PreparedStatement.
If we use the Statement, then it will compile the command each time before its going to run. If the same command is compiled again and again, then the performance of an application is going to decreased.
In case of PreparedStatement, first command will be send to database for compilation, the compiled code will be stored in PreparedStatement object. Then the code will be executed for any number of times by with out re-compiling the command again and again.

13) Can we transfer the binary data using Statement Object ?
Ans). No, we can’t transfer the binary data (BLOB) using Statement object. This is one of the disadvantage of Statement object. For doing this, we can go with PreparedStatement Object by calling the preparedStatement() method on connection object.

14) What is CallableStatement in JDBC ?
Ans). CallableStatement is an interface coming from the java.sql package. And it is sub interface of PreparedStatement.
CallableStatement of JDBC has all the benefits of PreparedStatement and also it has one additional feature. That is, by using the CallableStatement we can call the procedures or functions of a database.
CallableStatement has two types of syntax. One is for executing the commands and another is for calling the procedures or functions.

15) Can we create a procedure or function using CallableStatement ?
Ans). No, we can’t create functions or procedures using CallableStatement. CallableStatement is only for calling the procedures or functions. Typically procedures and functions in database are created by DBA. As a Java programmers, we just call those procedures and functions.

16) Why we need to register out parameters in callable statement ?
Ans). A procedure contains 3 modes of parameters.

1. 1). In (default)
2. 2). Out
3. 3). Inout

In parameter: Accepts input, and Out parameter: written output. If a same parameter takes input and written output then we make it as Inout parameter.
If we do not register out parameter then CallableStatement by default assumes all the parameters are as In Parameters. So to inform CallableStatement that is an Out Parameter then we register it as out parameter.

17) What is ResultSet in JDBC and what does this ?
Ans). ResultSet is interface coming from the java.sql package. ResultSet is a table of data which represents the database result set. Which is usually generated by executing the Statement object (executeQuery()). A ResultSet object maintains a cursor to pointing the current row. Initially the cursor is pointed at the before first row. we can move the cursor position by using the next() method available in ResultSet.
The default ResultSet is not updatable and the cursor moves forward direction only. We can alter the ResultSet type by passing the parameters to connection object.
Ex: Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(“SELECT * FROM emp”); // rs will be scrollable and will be updatable

18) What is difference between Sensitive and InSensitive ResultSet ?
Ans). In JDBC, we have 2 types of ResultSet are available. Those are Scrollable and Non-Scrollable.
Scrollable ResultSet again 2 types. Sensitive and InSensitive.

InSensitive Resultset doesn’t allow the changes made on data at database. Where as Sensitive ResultSet allows the changes on the data.

19) What is read only and updatable mode in ResultSet ?
Ans). ReadOnly mode only allows read operations on ResultSet. Where as Updatable mode allows Read + Write operations on ResultSet.

20) What happen when cursor is moved to unavailable row ?
Ans). If the cursor is moved to unavailable row, we will get the exception called java.sql.SQLException: Exhausted Resultset

21) Can we create a Scrollable ResultSet using PreparedStatement ?
Ans). Yes, we can create Scrollable ResultSet using PreparedStatement like below.
PreparedStatement pstmt = con.prepareStatepemt(“select * from emp”, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery();
Here “rs” will be the ScrollableResultSet type.

22) What is batch processing in JDBC ?
Ans). If there are multiple SQL operations in a JDBC program, those are all operations will be executed on database sequentially.
If the commands are executed sequentially one by one then number of trips (hits) between an application to database will be increased.
If number of trips are increased then the performance of the application will be decreased. In order to improve the performance, by reducing the number of trips, we can use batch processing technique in JDBC.
In batch processing, the SQL operations of a program will be constructed as a batch and then the batch will be send to a database in a single trip.

23) How to do batch processing in JDBC ?
Ans). To do the batch processing in JDBC, we have 2 methods

1. addBatch();
2. executeBatch();
3. addBatch() method is used for constructing a batch.

constructing a batch means, storing the SQL commands in a buffer which is maintained by the Statement object.
executeBatch() method is used to transfer the commands from the buffer to database at a time.
While a batch is executing at database, in the middle if a command fails then all commands after that commands are canceled and finally java.sql.BatchUpdateException will be thrown.

24) What is difference between batch processing with statement and prepared statement ?
Ans). In case of Statement, we can add different commands to a batch. But in case of PreparedStatement, we can add same commands for multiple times to a batch.

25) What is a Transaction ?
Ans). A transaction is considered to be a set of commands, it will take our database from one consistent state to another consistent state. A transaction means, it is a group of operations, used to perform a particular task.
A transaction can be reach to either success or fail.
If all operations are completed successfully then transaction become success. If any one of the operation fail then all operations are canceled and finally transaction reach fail.

26) Tell me different types of Transactions ?
Ans). Typically transactions are of 2 types.

1. Local Transactions.
2. Global or Distributed Transactions

If all operations are executed on one or same database, then it is called as a Local Transaction.
For Example, if we transfer the money from account1 to account2 with in the same bank, then it is called as Local Transaction.
If the operations are executed on more than one database then it is called as Global Transaction.
For Example, if we transfer the money from account1 to account2 of different bank, then it is called as Global Transaction.

27) What is ResultSetMetaData ?
Ans). ResultSetMetaData is an interface coming from the java.sql package. If we want to read the data of ResultSet, then we should have the knowledge about the data which is stored in the ResultSet. If we don’t know about the data stored in the ResultSet, then we can read that data through ResultSetMetaData object.
To get ResultSetMetaData obejct, we call getMetaData() method on ResultSet.
Ex : ResultSetMetaData rsmd = rs.getMetaData();

28) What is DatabaseMetaData?
Ans). DatabaseMetaData is an interface coming from the java.sql package. If we want to know the capabilities of database, we can use the DatabaseMetaData. Typically this DatabaseMetaData helps while creating the Database tools.
We can get the database capabilities by calling getMetaData() method on connection object.
Ex: DatabaseMetaData dbmd = con.getMetaData();

29) What is RowSet in JDBC ?
Ans). RowSet is a sub interface of ResultSet. A RowSet is considered to be an advanced ResultSet, which was introduced in JDBC 2.x version.
A RowSet object is a Serialized object, hence it can be transferable across the network.
A RowSet object is by default scrollable and updatable. If we want to scrollable and updatable feature then we can directly go with RowSet instead of ResultSet.
RowSet can work connected and disconnected modes of database connection.

30) Tell me 3 drawbacks about DriverManager in JDBC ?
Ans). In DriverManager to get the connection, we need to give the connection properties in source code. It means programmer should remember the connection properties for all cases.
DriverManager class takes around 3 to 4 seconds in the network to get the connection with database. so there is a performance drawback.
A connection returned by the DriverManager is a not reusable connection. It means it can not store or preserve the connection for reusing. It will take the burden on the server.

31) What are the different advantages of using DataSource ?
Ans). All the drawbacks of DriverManager are solved with DataSource mechanism of JDBC.
In DataSource mechanism, work is divided between administrator and programmer.
Administrator creates a DataSource object and then binds it with JNDI registry.
A programmer connects with registry and retrieves DataSource object from registry and then gets the connection with database. Here programmer no need to remember the connection properties.

32) What is connection pooling ?
Ans). Database connections are expensive objects. If the connections are opened and closed again and again it will increase the burden on database server. In order to reuse the database connections, instead of recreating them again and again, connection pooling technique is introduced.
In connection pooling technique, a group of reusable connections are opened and stored in a connection pool. Those connections are called pooled connections.

33) What is SavePoint in JDBC ?
Ans). SavePoint is an interface coming from the java.sql package. It was introduced in JDBC 3 version. The SavePoints are also called as CheckPoints and these are used to divide the primary and secondary operations of a transaction.
If we don’t want to cancel the primary operations of a transaction, if any problem occurred in the secondary operations. In this case we can divide transactions with primary and secondary operations by creating the SavePoint in the middle.