Monday, July 2, 2012

Java Database Connectivity JDBC Interview Questions Part 1

I had posted an article titled “Java Database Connectivity - JDBC” last week on Friday and was planning to post the article on JDBC Interview questions today. But, before I could do so, one of our blog readers pointed out the fact that I haven’t posted it yet but have referenced it already…

Well, here we go. The following are some questions that you may encounter on JDBC concepts during your interviews. Remember that if you are a junior level developer (with less than 3 years working experience) many of these questions may seem too complicated for you and frankly you won’t be expected to know them either.

You cannot think of an Enterprise Java Application that does not connect to a database. So, a strong understanding of JDBC concepts is crucial to your success as a potential candidate during an interview.

Questions – Part 1:

1. What is the JDBC?

Java Database Connectivity (JDBC) is a standard Java API that is used to interact with relational databases from within a Java application. JDBC has set of classes and interfaces which can use from our Java application and interact with the database.

2. What are the Basic Steps in writing a Java program to connect to a database using JDBC?

The basic steps in connecting to a database using JDBC are:

1. Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).
2. Open the connection to database which is then used to send SQL statements and get results back.
3. Create JDBC Statement object. This object contains SQL query.
4. Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.
5. Process the result set.
6. Close the connection.

3. What are the main components of JDBC ?

The five main components of JDBC are:

1. Driver Manager
2. Driver
3. Connection
4. Statement &
5. ResultSet

4. How does a JDBC application work?

A JDBC application can be logically divided into two layers:
1. Driver layer
2. Application layer

First the Driver layer consists of DriverManager class and the available JDBC drivers. The application begins with requesting the DriverManager for the connection. An appropriate driver is choosen and is used for establishing the connection. This connection is given to the application which falls under the application layer. The application uses this connection to create Statement kind of objects, through which SQL commands are sent to backend and obtain the results.

5. How do I load a database driver with JDBC 4.0 / Java 6?

Provided the JAR file containing the driver is properly configured, just place the JAR file in the classpath. Java developers NO longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver.The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.

Remember - if the correct JAR Files are missing in the classpath, this auto feature will not work

6. What is JDBC Driver interface?

The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor driver must provide implementations of the java.sql.Connection, Statement, PreparedStatement, CallableStatement, ResultSet and Driver so that the Java classes that connect to the database can use them.

7. What does the connection object represents?

The connection object represents a literal connection or a communication context through which our Java code can interact with a database. i.e., all communication with database is through connection object only.

8. What is a Statement?

The Statement acts like a vehicle through which SQL commands can be sent. You can create a Statement as follows:

Statement stmt = conn.createStatement();

This method returns an object which implements statement interface which can be executed.

9. What is a PreparedStatement?

A prepared statement is an SQL statement that is precompiled by the database. It is a sub-set of the Statement. Through precompilation, prepared statements improve the performance of SQL commands that are executed multiple times (given that the database supports prepared statements). Once compiled, prepared statements can be customized prior to each execution by altering predefined SQL parameters.

You can create a prepared statement as follows:

PreparedStatement pstmt = conn.prepareStatement("UPDATE Emp_Salary SET Salary = ? WHERE Emp_Id = ?");
pstmt.setBigDecimal(1, 75000.00);
pstmt.setInt(2, 12345);

Note that conn is an instance of the collection class and the "?" represent the parameters that are to be passed to the query/prepared statement before it can be executed.

10. What are callable statements?

Callable statements are used from JDBC application to invoke stored procedures and functions objects of a database.

11. Can you call a stored procedure using JDBC? If so, how?

Yes, you can call Stored Procedures using JDBC. we have to use the CallableStatement to do so.


CallableStatement stproc_stmt = conn.prepareCall("{call procname(?,?,?)}");

Here conn is an instance of the Connection class.

12. How many types of JDBC drivers are there and What are they?

There are four types of drivers defined by JDBC as follows:
1. Type 1 – JDBC-ODBC Bridge
2. Type 2 – Native API (Partly Java) Driver
3. Type 3 – Net-Protocol Fully Java Driver
4. Type 4 – Pure Java Driver

Type 4 JDBC driver is most preferred kind of approach in JDBC.

13. Which type of JDBC driver is the fastest one?

JDBC Net pure Java driver(Type IV) is the fastest driver because it converts the JDBC calls into vendor specific protocol calls and it directly interacts with the database.

14. Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.

15. What are the standard transaction isolation levels defined by JDBC?

The values are defined in the class java.sql.Connection and are:


You need to double check that the database you are connecting to, supports these isolation levelts.

16. What is a ResultSet?

The ResultSet represents set of rows retrieved due to query execution. You get a result set object as the output when you execute a query using the Statement or the PreparedStatement objects.

ResultSet rs = stmt.executeQuery(sqlQuery);

17. What are the types of resultsets?

JDBC supports 3 types of ResultSets. They are:


18. What are the types of statements in JDBC?

The JDBC API has 3 types of Statement Interfaces. They are:

1. Statement
2. PreparedStatement
3. CallableStatement

19. What are the differences/key features between the 3 different types of Statements in JDBC?

* This interface is used for executing a static SQL statement and returning the results it produces.
* The object of Statement class can be created using Connection.createStatement() method.

* A SQL statement is pre-compiled and stored in a PreparedStatement object.
* This object can then be used to efficiently execute this statement multiple times.
* The object of PreparedStatement class can be created using Connection.prepareStatement() method. This extends Statement interface.

* This interface is used to execute SQL stored procedures.
* This extends PreparedStatement interface.
* The object of CallableStatement class can be created using Connection.prepareCall() method.

20. What is Connection pooling? What are the advantages of using a connection pool?

Connection Pooling is a technique used for sharing the server resources among requested clients. It was pioneered by database vendors to allow multiple clients to share a cached set of connection objects that provides access to a database. Getting connection and disconnecting are costly operation, which affects the application performance, so we should avoid creating multiple connection during multiple database interactions. A pool contains set of Database connections which are already connected, and any client who wants to use it can take it from pool and when done with using it can be returned back to the pool. Apart from performance this also saves you resources as there may be limited database connections available for your application.

21. What does the Class.forName() method do?

Method forName() is a static method of java.lang.Class. This can be used to dynamically load a class at run-time. Class.forName() loads the class if its not already loaded. It also executes the static block of loaded class. Then this method returns an instance of the loaded class. So a call to Class.forName('MyClass') is going to do following
- Load the class MyClass.
- Execute any static block code of MyClass.
- Return an instance of MyClass.

JDBC Driver loading using Class.forName is a good example of best use of this method. The driver loading is done like this


All JDBC Drivers have a static block that registers itself with DriverManager and DriverManager has static initializer method registerDriver() which can be called in a static blocks of Driver class. A MySQL JDBC Driver has a static initializer which looks like this:

static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");

Class.forName() loads driver class and executes the static block and the Driver registers itself with the DriverManager.

22. When to use a PreparedStatement and when to use a Statement?

Statement is a object used for executing a static SQL statement and returning the results it produces. PreparedStatement is a SQL statement which is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

There are few advantages of using PreparedStatements over Statements
* Since its pre-compiled, Executing the same query multiple times in loop, binding different parameter values each time is faster.
* In PreparedStatement the setDate()/setString() methods can be used to escape dates and strings properly, in a database-independent way.
* SQL injection attacks on a system are virtually impossible while using PreparedStatements.

23. What do you mean by the term pre-compiled from a PreparedStatemnet perspective?

The prepared statement(pre-compiled) concept is not specific to Java, it is a database concept. Statement precompiling means: when you execute a SQL query, database server will prepare a execution plan before executing the actual query, this execution plan will be cached at database server for further execution which makes it much faster than executing a fresh query that has to be compiled before execution.

24. What does setAutoCommit(false) do?

A JDBC connection is created in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and will be automatically committed as soon as it is executed. If you require two or more statements to be grouped into a transaction then you need to disable auto-commit mode manually as follows:


Once auto-commit mode is disabled, no SQL statements will be committed until you explicitly call the commit method as follows:


Alternately, if you want to ensure that all further transactions/queries are auto-committed then you can enable auto-commit as follows:


25. What are database warnings and How can I get them?

Warnings are issued by database to notify user of a problem which may not be very severe. Database warnings do not stop the execution of SQL statements. In JDBC SQLWarning is an exception that provides information on database access warnings. Warnings are silently chained to the object whose method caused it to be reported. Warnings may be retrieved from Connection, Statement, and ResultSet objects.

The call to the getWarnings() method of either of these objects retrieves the first warning reported by calls on this object. If there is more than one warning, subsequent warnings will be chained to the first one and can be retrieved by calling the method SQLWarning.getNextWarning on the warning that was retrieved previously.

JDBC Interview Questions - Part 2

Java/J2EE Interview Questions - Home Page

No comments:

Post a Comment

© 2013 by All rights reserved. No part of this blog or its contents may be reproduced or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without prior written permission of the Author.