Friday, June 29, 2012

Java Database Connectivity - JDBC

JDBC is Java application programming interface that allows the Java programmers to access database management system from Java code. It was developed by JavaSoft, a subsidiary of Sun Microsystems. The purpose of this article is to give you an introductory overview of this JDBC concept. Before we go any further, let me reiterate that JDBC in itself is a very large topic and what you see in this chapter is just a drop in the ocean. But, this should be sufficient to get you started into using JDBC in your day to day programs.

What is Java Database Connectivity or JDBC?

The Java application programming interface provides a mechanism for dynamically loading the correct Java packages and drivers and registering them with the JDBC Driver Manager that is used as a connection factory for creating JDBC connections which supports creating and executing statements such as SQL INSERT, UPDATE and DELETE. Driver Manager is the backbone of the jdbc architecture.

Generally all Relational Database Management System supports SQL and we all know that Java is platform independent, so JDBC makes it possible to write a single database application that can run on different platforms and interact with different Database Management Systems.

Java Database Connectivity is similar to Open Database Connectivity (ODBC) which is used for accessing and managing database, but the difference is that JDBC is designed specifically for Java programs, whereas ODBC is not depended upon any language.

In short JDBC helps the programmers to write java applications that manage these three programming activities:

1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database and
3. Retrieving and processing the results received from the database
Why is Java Database Connectivity Important?

Java, as you might be aware is a very powerful programming language which is widely used to create enterprise class applications. Any enterprise application that you may use has to display and manipulate data in some form and this data is invariably stored in some Relational Database (RDBMS) like Oracle, Sybase etc. So, the system or the application per say must connect and interact with the database in order to display the required information on screen. This is exactly where the JDBC layer fits in.

Without JDBC, there is no way that the application can effectively interact with a Relational Database like Oracle or Sybase and perform its functions. The latest version of the JDBC API is 4.0. Some of the new Features Added to JDBC 4.0 are:

1. Auto-loading of JDBC driver class
2. Connection management enhancements
3. Support for RowId SQL type
4. DataSet implementation of SQL using Annotations
5. SQL exception handling enhancements
6. SQL XML support

JDBC Architecture:

At a high level, the JDBC Architecture consists of two layers:

1. The JDBC API, which provides the application-to-JDBC Manager connection.
2. The JDBC Driver API, which supports the JDBC Manager-to-Driver Connection.

The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to different databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases. The location of the driver manager with respect to the JDBC drivers and the Java application is shown below:

Main Components of JDBC:

There are numerous components that work together as part of the JDBC API in order to allow an enterprise java application to connect to a database. However, the following are the main components:
1. DriverManager - Manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication sub-protocol. The first driver that recognizes a certain sub-protocol under JDBC will be used to establish a database Connection.
2. Driver - The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not call it explicitly every time.
3. Connection - Interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database happens through the connection object only.
4. Statement - Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
5. ResultSet - The ResultSet represents set of rows retrieved due to query execution which gets returned to the calling system.

A Sample Code to connect to a database using JDBC:

The following is a code snippet that you can use to connect to an Oracle database using a thin driver. The steps in connecting to a database and obtaining the output are:
1. Load the Driver Class
2. Create a Connection
3. Create a Statement
4. Execute the query and obtain the ResultSet
5. Access the output of the query from the ResultSet

Each line of code that represents each of the above steps is marked by the comment followed by a number.

import java.sql.*;
public class InheringJavaJDBCTest {
public static void main (String args []) throws Exception

Class.forName ("oracle.jdbc.driver.OracleDriver"); //1

Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@hostname:1526:testdb", "scott", "tiger"); //2

Statement stmt = conn.createStatement(); //3
ResultSet rs = stmt.executeQuery("select 'Hi' from dual"); //4
while (
System.out.println (rs.getString(1)); // 5

JDBC Driver Categories/Types:

If you saw the above code example correctly, we have loaded a driver class as the first step before we established the database connection. JDBC has 4 different categories and all drivers will invariably fit into either of these 4 categories. The four JDBC driver categories are:

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 1: JDBC/ODBC Bridge requires an ODBC (Open Database Connectivity) driver for the database to be installed. This type of driver works by translating the submitted queries into equivalent ODBC queries and forwards them via native API calls directly to the ODBC driver. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important.

Type2: Native API (partly-Java driver) type of driver uses a vendor-specific driver or database API to interact with the database. An example of such an API is Oracle OCI (Oracle Call Interface). It also provides no host redirection. A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

Type 3: A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products. This type of driver can access any database.

Type 4: Proprietary Protocol-Net (pure Java driver) has the same configuration as a type 3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor's database. Again this is all transparent to the client. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

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

Types of Statements you can use in a Java Program:

As you may have seen in the code example section, a Statement is used to pass or rather send the SQL Query that we wish to execute to the database. In our example we saw a simple “Statement”. The JDBC API has 3 types of statements that you can use in your Java Programs. They are:
1. Statement
2. PreparedStatement &
3. CallableStatement

Each of these types of statements has a different purpose and has to be used differently. Both Statement & PreparedStatement are used to execute basic SQL Queries while the CallableStatement is used to execute or invoke Stored Procedures.

A standard Statement is used to create a Java representation of a literal SQL statement and execute it on the database. The Statement has to verify its metadata against the database every time. If you want to execute the SQL statement once go for STATEMENT.

A PreparedStatement is a precompiled statement. This means that when the PreparedStatement is executed, the RDBMS can just run the PreparedStatement SQL statement without having to compile it first. If you want to execute a single SQL statement multiple number of times, then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with passing different values to the queries.

A CallableStatement extends the PreparedStatement Interface. It can be used to call Stored Procedures from java code.

Types of Result Sets you can use in a Java Program:

As you may have seen in the code example section, a ResultSet is the output that we will get once our query is executed. Typically, we will iterate through the result set and obtain all the values that are returned as the output of execution of the Statement. The JDBC API gives us an option to use 3 different types of Result Sets and this value can be defined while establishing the Connection. They are:

1. TYPE_FORWARD_ONLY - specifies that a resultset is not scrollable, that is, rows within it can be advanced only in the forward direction.
2. TYPE_SCROLL_INSENSITIVE - specifies that a resultset is scrollable in either direction but is insensitive to changes committed by other transactions or other statements in the same transaction.
3. TYPE_SCROLL_SENSITIVE - specifies that a resultset is scrollable in either direction and is affected by changes committed by other transactions or statements within the same transaction.

The TYPE_FORWARD_ONLY resultset is the default type of resultset that will get created and it is always insensitive.

I hope this chapter gave you a basic overview of the JDBC concepts. As I had said at the beginning of the chapter, JDBC is an ocean and we haven’t even scratched the surface of that vast topic. If you have any specific questions related to JDBC, visit the article on “JDBC Interview Questions” to see if it has already been answered. If you can’t find an answer to your question, either leave a comment in this page or on the “JDBC Interview Questions” page and I will try to answer them.

JDBC Interview Questions - Part 1

JDBC Interview Questions - Part 2

Java Collections Interview Questions - Part 2

As a continuation from the part 1 of questions on the Java Collections topic, this article is going to contain some more questions on the Java Collections area. If you want to visit the part 1 questions click here

Apart from the questions in the Part 1 and below, there are a few articles that I have put up (as part of the SCJP Certification series) on Collections that you might find useful. You can use them to revise/review your understanding of Java collections.

They are:

Introduction to Collections
ArrayList Basics
Sorting Collections
Searching Collections
Collections Summary


1. What is the difference between java.util.Iterator and java.util.ListIterator?

Iterator - Enables you to traverse through a collection in the forward direction only, for obtaining or removing elements
ListIterator - extends Iterator, and allows bidirectional traversal of list and also allows the modification of elements.

2. What is the difference between the HashMap and a Map?

Map is an Interface which is part of Java collections framework. It stores data as key-value pairs.
Hashmap is class that implements that using hashing technique.

3. What does synchronized means in Hashtable context?

Synchronized means only one thread can modify a hash table at one point of time. Any thread before performing an update on a hashtable will have to acquire a lock on the object while others will wait for lock to be released.
This adds an overhead which makes the Hashtable slower when compared to the Hashmap. You must choose a Hashmap when you want faster performance and choose the Hashtable when you want thread safety

4. Can we make a Hashmap thread safe? If so, how?

HashMap can be made thread safe by synchronizing it. Example:
Map m = Collections.synchronizedMap(hashMap);

5. Why should I always use ArrayList over Vector?
You should choose an ArrayList over a Vector because – not all systems are multi-threaded and thread safety is not a mandatory requirement. In such cases, using a Vector is not only overkill but also adds a lot of overhead which might slow down system performance. Even if you are just iterating through a vector to display its contents, the lock on it still needs to be obtained which makes it much slower. So, choose the ArrayList over a Vector unless your system is multi-threaded.

6. What is an enumeration?

An enumeration is an interface containing methods for accessing the underlying data structure from which the enumeration is obtained. It is a construct which collection classes return when you request a collection of all the objects stored in the collection. It allows sequential access to all the elements stored in the collection.

7. What is the difference between Enumeration and Iterator?

The functionality of Enumeration interface is duplicated by the Iterator interface. Iterator has a remove() method while Enumeration doesn't. Enumeration acts as Read-only interface, because it has the methods only to traverse and fetch the objects, whereas using Iterator we can manipulate the objects also like adding and removing the objects. So Enumeration is used whenever we want to make Collection objects as Read-only.

8. What is the difference between Sorting performance of Arrays.sort() vs Collections.sort()? Which one is faster? Which one to use and when?

Actually speaking, the underlying sorting algorithm & mechanism is the same in both cases. The only difference is type of input passed to them. Collections.sort() has a input as List so it does a conversion of the List to an array and vice versa which is an additional step while sorting. So this should be used when you are trying to sort a list. Arrays.sort is for arrays so the sorting is done directly on the array. So clearly it should be used when you have an array available with you and you want to sort it.

9. What is java.util.concurrent BlockingQueue? How it can be used?

The BlockingQueue is available since Java 1.5. Blocking Queue interface extends collection interface, which provides you the power of collections inside a queue. Blocking Queue is a type of Queue that additionally supports operations that wait for the queue to become non-empty when retrieving an element, and wait for space to become available in the queue when storing an element. A typical usage example would be based on a producer-consumer scenario. Note that a BlockingQueue can safely be used with multiple producers and multiple consumers.

10. What is the ArrayBlockingQueue?

An ArrayBlockingQueue is a implementation of blocking queue with an array used to store the queued objects. The head of the queue is that element that has been on the queue the longest time. The tail of the queue is that element that has been on the queue the shortest time. New elements are inserted at the tail of the queue, and the queue retrieval operations obtain elements at the head of the queue. ArrayBlockingQueue requires you to specify the capacity of queue at the object construction time itself. Once created, the capacity cannot be increased. This is a classic "bounded buffer" (fixed size buffer), in which a fixed-sized array holds elements inserted by producers and extracted by consumers. Attempts to put an element to a full queue will result in the put operation blocking; attempts to retrieve an element from an empty queue will be blocked.

11. Why doesn't Map interface extend Collection?

Though the Map interface is part of collections framework, it does not extend collection interface. This was done as a design decision by Sun when the Collection framework was created. Think of it this way – A Map contains key-value pair data while a general collection contains only a bunch of values. If the map were to extend the collection class, what will we do with the keys and how will we link the values to their corresponding keys? Alternately, if the collection were to extend the Map, then, where will we go to get the key information for all the values that are already in the Collection?

Get the idea? For all practically purposes a Map is considered a collection because it contains a bunch of data in key-value pairs but it does not explicitly extend the collection interface.

12. Which implementation of the List interface (ArrayList or LinkedList) provides for the fastest insertion of a new element into the list?


If you are surprised about the answer you see above, read the question again “Fastest Insertion of a new element into the list”. The question is about inserts and not reading data. So, the linkedlist is faster. Because, when an element is inserted into the middle of the list in an ArrayList, the elements that follow the insertion point must be shifted to make room for the new element. The LinkedList is implemented using a doubly linked list; an insertion requires only the updating of the links at the point of insertion. Therefore, the LinkedList allows for fast insertions and deletions.

13. Which implementation of the List Interface (ArrayList or LinkedList) provides for faster reads during random access of data?

ArrayList implements the RandomAccess interface, and LinkedList does not. The commonly used ArrayList implementation uses primitive Object array for internal storage. Therefore an ArrayList is much faster than a LinkedList for random access, that is, when accessing arbitrary list elements using the get method. Note that the get method is implemented for LinkedLists, but it requires a sequential scan from the front or back of the list. This scan is very slow. For a LinkedList, there's no fast way to access the Nth element of the list.

14. Which implementation of the List Interface (ArrayList or LinkedList) uses more memory space for the same amount (number) of data?

Each element of a linked list (especially a doubly linked list) uses a bit more memory than its equivalent in array list, due to the need for next and previous pointers. So, the LinkedList uses more memory space when compared to an ArrayList of the same size

15. Where will you use ArrayList and Where will you use LinkedList?

If you frequently add elements to the beginning of the List or iterate over the List to delete elements from its interior, you should consider using LinkedList. These operations require constant-time in a LinkedList and linear-time (depending on the location of the object being added/removed) in an ArrayList.

If the purpose of your collection is to populate it once and read it multiple times from random locations in the list, then the ArrayList would be faster and a better choice because Positional access requires linear-time (depending on the location of the object being accessed) in a LinkedList and constant-time in an ArrayList.

In short - If you need to support random access, without inserting or removing elements from any place other than the end, then ArrayList offers the optimal collection. If, however, you need to frequently add and remove elements from the middle of the list and only access the list elements sequentially, then LinkedList offers the better implementation.

16.Why insertion and deletion in ArrayList is slow compared to LinkedList?

ArrayList internally uses and array to store the elements, when that array gets filled by inserting elements a new array of roughly 1.5 times the size of the original array is created and all the data of old array is copied to new array.

During deletion, all elements present in the array after the deleted elements have to be moved one step back to fill the space created by deletion. In linked list data is stored in nodes that have reference to the previous node and the next node so adding element is simple as creating the node an updating the next pointer on the last node and the previous pointer on the new node. Deletion in linked list is fast because it involves only updating the next pointer in the node before the deleted node and updating the previous pointer in the node after the deleted node.

17.How do you traverse through a collection using its Iterator?

To use an iterator to traverse through the contents of a collection, follow these steps:
1. Obtain an iterator to the start of the collection by calling the collection’s iterator() method.
2. Set up a loop that makes a call to hasNext(). Have the loop iterate as long as hasNext() returns true.
3. Within the loop, obtain each element by calling next().

18.How do you remove elements during Iteration?

Iterator also has a method remove() when remove is called, the current element in the iteration is deleted.

19.What are the main implementations of the List interface?

The main implementations of the List interface are as follows :
ArrayList : Resizable-array implementation of the List interface. The best all-around implementation of the List interface.
Vector : Synchronized resizable-array implementation of the List interface with additional "legacy methods."
LinkedList : Doubly-linked list implementation of the List interface. May provide better performance than the ArrayList implementation if elements are frequently inserted or deleted within the list. Useful for queues and double-ended queues (deques).

20.What are the advantages of ArrayList over arrays?

Some of the advantages ArrayList has over arrays are:
It can grow dynamically
It provides more powerful insertion and search mechanisms than arrays.

21.How to obtain Array from an ArrayList?

Array can be obtained from an ArrayList using toArray() method on ArrayList.
List arrayList = new ArrayList();

Object a[] = arrayList.toArray();

22.Why are Iterators returned by ArrayList called Fail Fast?

Because, if list is structurally modified at any time after the iterator is created, in any way except through the iterator's own remove or add methods, the iterator will throw a ConcurrentModificationException. Thus, in the face of concurrent modification, the iterator fails quickly and cleanly, rather than risking arbitrary, non-deterministic behavior at an undetermined time in the future. So, it is considered fail fast.

23.How do you sort an ArrayList (or any list) of user-defined objects?

Create an implementation of the java.lang.Comparable interface that knows how to order your objects and pass it to java.util.Collections.sort(List, Comparator).

24.What is the Comparable interface?

The Comparable interface is used to sort collections and arrays of objects using the Collections.sort() and java.utils.Arrays.sort() methods respectively. The objects of the class implementing the Comparable interface can be ordered.
The Comparable interface in the generic form is written as follows:
interface Comparable
where T is the name of the type parameter.

All classes implementing the Comparable interface must implement the compareTo() method that has the return type as an integer. The signature of the compareTo() method is as follows:

int i = object1.compareTo(object2)

If object1 < object2: The value of i returned will be negative.

If object1 > object2: The value of i returned will be positive.

If object1 = object2: The value of i returned will be zero.

25.What are the differences between the Comparable and Comparator interfaces?

The Comparable uses the compareTo() method while the Comparator uses the compare() method
You need to modify the class whose instance is going to be sorted and add code corresponding to the Comparable implementation. Whereas, for the Comparator, you can have a separate class that has the sort logic/code.
In case of Comparable, Only one sort sequence can be created while Comparator can have multiple sort sequences.

If you have any questions that you want answer for - please leave a comment on this page and I will answer them.

If you have any more questions on Collections that you have faced during your interviews and wish to add them to this collection - pls drop a note to and I shall be glad to add them to this list

Java Collections Interview Questions - Part 1 - Click Here

More Java & J2EE Interview Questions with Answers - Click Here

© 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.


Google+ Followers