JDBC(Java Database Connectivity) interview questions

Q) What is JDBC (Java Database Connectivity)?

JDBC is Java Database Connectivity. It allows you to have a single API for connecting to, manipulating, and retrieving information from a multiple Databases like MySQL, Oracle, DB2, etc.

Q) What is JDBC Driver ? 

JDBC driver is used to established a connection with the database so that you can fetch, update and maintain database tables using SQL queries, once the connection is made using these drivers.

Q) What are the differences between the 4 types of Drivers?

JDBC Driver Type Comparison
Category	Type 1	Type 2	Type 3	Type 4
Basic Description	JDBC to ODBC Bridge	JDBC using Native Methods	Middleware JDBC Drivers	Pure Java Driver
Requires native install on client	Yes	Yes	No	No
Cross platform	No	Mostly	Yes	Yes
Supports specific DB features e.g. OracleBlob	No	Yes	Usually No	Yes
Requires additional server	No	No	Yes	No

Q) What is the fastest type of JDBC driver? 

Type 4  (JDBC Net pure Java Driver) is the fastest JDBC driver.

Q) Is the JDBC-ODBC Bridge multi-threaded?

No. The JDBC-ODBC Bridge does not support multithreading (concurrent execution of several threads). It uses Synchronization technique to serialize all of the calls that it makes to ODBC.  They won’t get the advantages of multi-threading, However it can still face deadlock issues.

Q) Can a JDBC-ODBC Bridge support multiple concurrent open statements per connection?

No. JDBC-ODBC Bridge can only support one Statement object per connection.

Q) What are the steps required to execute a query in JDBC?

1) Create an instance of a JDBC driver or load JDBC drivers.

2) Register the driver using DriverManager class.

3) Establish a  connection.

4) Create Statement or Prepared Statement  which will be used to execute the query.

Q) What is a database URL?

A database URL (or JDBC URL) is a platform independent way of addressing a database. A database/JDBC URL is of the form

[code language=”java”]jdbc:[subprotocol]:[node]/[databaseName][/code]

If you are accessing a database called sample on the server cpsingh.sys.com using the myprot subprotocol, your database URL could be:

[code language=”java”]jdbc:myprot: cpsingh.sys.com /sample[/code]

If the database resides on the same computer node as the java program, the hostname part and the corresponding double slashes of the jdbc can be skipped as shown below –

[code language=”java”]jdbc:odbc:sample[/code]

All standard database URLs should prefixed with the string jdbc.

Q) What Class.forName ( ) method will do?

Class.forName()  loads the class dynamically and it returns the object of type class.

Q) Is it mandatory to close all  ResultSets, Statements and Connections?

Yes. When you are using JDBC in your application you are allocating resources not only in your program but in a database server as well. Failure to properly closed Statements(and PreparedStatements and CallableStatements), ResultSets and Connections can cause all of the following problems

– You will run out of connections that can be opened or used

– You will not be able to create any new result sets

– You will not be able to execute any queries of any kind

– Your program will get very slow

– The database server get very slow

– Your program will crash

– The database server will crash

It is advisable that you should always close all the JDBC resources you obtain in the reverse order that you obtained them to avoid these issues.

Q) Can I set ResultSet, Statements and Connections to null instead of closing them?

No. Setting references to null does not have the same result as calling close on the objects. Such kind of Codes will be leaking resources on the database server and can cause major issues. It can also create several issues for connection pooling.

Q) Which one can be used to supply query parameters at runtime? Statement OR PreparedStatement?

Using PreparedStatement, you can pass the parameters at the time of execution (runtime).

Q) What is the difference between a Statement and a PreparedStatement?

Most of the relational databases handles a JDBC / SQL query in four steps:

i) Parse the incoming SQL query.
ii) Compile the SQL query.
iii) Query optimization.
iv) Execute the optimized query to fetch and update the data for a given condition.

Statement: It always follow all of the four above statements for each sql query.
PreparedStatement: It pre-executes the first three steps, hence in case of prepared statement optimization is performed immediately.

Q) How to know that how many rows are there in a ResultSet ?

There are three ways to know the number of rows in ResultSet –
1) Use select count(*) on top of the result set to get the number of records.
2) Using below piece of code

[code language=”java”]
int count = 0;
while (rs.next()) {
count++;
}[/code]

3) If you are using JDBC 3 driver, you can use below code

[code language=”java”]
rs.afterLast()
// to move to the cursor at the last record.
rs.getRow()
// to fecth the curret row number.
[/code]

Note: This ResultSet MUST have a scrollable cursor(ScrollSensitive or Insensitive). Also, it won’t work with a Forward Only cursor.

Q) Can I get a null ResultSet ?

No, It is not possible to get null ResultSet. Database Drivers will always return a result set for a query. rs.next() can be used to see whether next record contains a row or not, here rs is a ResultSet object.

Q) What is the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?

These both attrbutes are for a ResultSet-
TYPE_SCROLL_INSENSITIVE: If a ResultSet is TYPE_SCROLL_INSENSITIVE then it would not reflect changes made when it is open.
TYPE_SCROLL_SENSITIVE: If a ResultSet is TYPE_SCROLL_SENSITIVE then it can reflect changes even if it’s open.

Q) What is metadata?

Metadata: It is nothing but a data definition of data.

In JDBC, we have two types of metadata.

1) MetaData for ResultSet: ResultSet is used to fetch data from tables and this MetaData keeps record like how many rows have been fetched, what are the column names etc.

2) MetaData for Database connections: It has the record of database connections.

Q) What is savepoint?                     

Savepoint is almost similar to checkpoint. It is used to track the status of a transaction, using this feature we can always traceback to see where the failure happened and what needs to be done to fix that.

Q) Why we need to use BatchUpdates?

Batch updates are used to reduce the Database workloads. for an example if you are going to insert 120 records in database, if you do it using 120 insert SQL statements, it would definitely consume more amount of time as for each INSERT statement a connection needs to be established between DB and resources. If you perform it through batch process then all 120 statements would be inserted in table in one go, which would save time and improves resource utilization.

Q) What is transaction?

Transaction: A transaction is atomic unit of Work. Which means a transaction is a collection of several tasks and if task fails then the effect will be rolled back to the previous state. In simple terms a transaction commits only when all the tasks specified in it executes successfully.

Q) Difference between local and global transaction?

Local transaction: A local transaction scope is limited to a single local JVM(java virtual machine). It can be implemented using JTA API.

Global transaction: It’s scope is not limited to a single JVM, rather it can use objects of different-2 JVMs. It can be implemented using TWO-PHASE-COMMIT protocol.

Q) How to call a Stored Procedure from JDBC?

Stored Procedures can be called by using the prepareCall() method, which returns a CallableStatement object.

[code language=”java”]
CallableStatement cstmt = con.prepareCall("{call Procedure_name}");
ResultSet res = cstmt.executeQuery();
[/code]

Q) What are warnings in JDBC? How to Retrieve them?

SQLWarning is a subclass of SQLException class. It is used to deal with database access warnings.
Exception: It terminates the execution of a program.
Warning: Unlike exceptions, it doesn’t intruppt the execution of program. They are just used to provide a notification to the user.

for example:

[code language=”java”]
…
SQLWarning sqlw = stmt.getWarnings();
if (sqlw != null) {
while (sqlw != null) {
System.out.println("Warning Message: " + sqlw.getMessage());
System.out.println("SQLState is: " + sqlw.getSQLState());
System.out.print("Error code is: ");
System.out.println(sqlw.getErrorCode());
sqlw = sqlw.getNextWarning();
}
}
…
[/code]

Q) What is Connection pooling?

Connection pooling is a technique which improves the utilization of shared resources. The purpose of this technique is to serve multiple requests made by clients  It increases the performance of Web applications by reusing active database connections instead of creating a new connection for every client’s request.

Q) What is the task of Connection Pool Manager?

Connection pool manager maintains the pool of open database connections.