Q: What is JDBC?

A: JDBC stands for Java Database Connectivity, which is a standard Java API for databaseindependent connectivity between the Java programming language and a wide range of
databases.
Q: Describe a general JDBC Architecture.
A: General JDBC Architecture consists of two layers: JDBC API (This provides the
application-to-JDBC Manager connection) and JDBC Driver API (This supports the JDBC
Manager-to-Driver Connection).
Q: What are the common JDBC API components?
A: JDBC API consists of following interfaces and classes: DriverManager, Driver, Connection,
Statement, ResultSet, SQLException.
Q: What is a JDBC DriverManager?
A: JDBC DriverManager is a class that manages a list of database drivers. It matches
connection requests from the java application with the proper database driver using
communication subprotocol.
Q: What is a JDBC Driver?
A: JDBC driver is an interface enabling a Java application to interact with a database. To
connect with individual databases, JDBC requires drivers for each database. The JDBC driver
gives out the connection to the database and implements the protocol for transferring the query
and result between client and database
Q: What is a connection?
A: Connection interface consists of methods for contacting a database. The connection object
represents communication context.
Q: What is a statement?
A: Statement encapsulates an SQL statement which is passed to the database to be parsed,
compiled, planned and executed.
Q: What is a ResultSet?
A: These objects hold data retrieved from a database after you execute an SQL query using
Statement objects. It acts as an iterator to allow you to move through its data. The
java.sql.ResultSet interface represents the result set of a database query.
Q: What are types of ResultSet?
A: There are three constants which when defined in result set can move cursor in resultset
backward, forward and also in a particular row.
1. ResultSet.TYPE_FORWARD_ONLY: The cursor can only move forward in the result
set.
2. ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can scroll forwards and
backwards, and the result set is not sensitive to changes made by others to the database
that occur after the result set was created.

3. ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can scroll forwards and backwards,

and the result set is sensitive to changes made by others to the database that occur after
the result set was created.
Q: What are the basic steps to create a JDBC application?
A: Following are the basic steps to create a JDBC application:
1. Import packages containing the JDBC classes needed for database programming.
2. Register the JDBC driver, so that you can open a communications channel with the
database.
3. Open a connection using the DriverManager.getConnection () method.
4. Execute a query using an object of type Statement.
5. Extract data from result set using the appropriate ResultSet.getXXX () method.
6. Clean up the environment by closing all database resources relying on the JVM's garbage
collection.
Q: What are JDBC driver types?
A: There are four types of JDBC drivers:
1. JDBC-ODBC Bridge plus ODBC driver, also called Type 1: calls native code of the
locally available ODBC driver.
2. Native-API, partly Java driver, also called Type 2: calls database vendor native library on
a client side. This code then talks to database over network.
3. JDBC-Net, pure Java driver, also called Type 3 : the pure-java driver that talks with the
server-side middleware that then talks to database.
4. Native-protocol, pure Java driver, also called Type 4: the pure-java driver that uses
database native protocol.
Q: When should each of the JDBC driver type be used?
A: Following is a list as to when the four types of drivers can be used:
If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred
driver type is 4.
If your Java application is accessing multiple types of databases at the same time, type 3
is the preferred driver.
Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet
for your database.

 The type 1 driver is not considered a deployment-level driver and is typically used for
development and testing purposes only.
Q: Which type of JDBC driver is the fastest one?
A: JDBC Net pure Java driver(Type 4) is the fastest driver because it converts the JDBC calls
into vendor specific protocol calls and it directly interacts with the database.
Q: Does the JDBC-ODBC Bridge support multiple concurrent open statements per
connection?
A: No. You can open only one Statement object per connection when you are using the JDBCODBC Bridge.
Q: What are the standard isolation levels defined by JDBC?
A: The standard isolation levels are:
TRANSACTION_NONE
TRANSACTION_READ_COMMITTED
TRANSACTION_READ_UNCOMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE
Q: What is the design pattern followed by JDBC?
A: JDBC architecture decouples an abstraction from its implementation. Hence JDBC follows a
bridge design pattern. The JDBC API provides the abstraction and the JDBC drivers provide the
implementation. New drivers can be plugged-in to the JDBC API without changing the client
code.
Q: What are the different types of JDBC Statements?
A: Types of statements are:
Statement (regular SQL statement)
PreparedStatement (more efficient than statement due to pre-compilation of SQL)
CallableStatement (to call stored procedures on the database)
Q: What is difference between statement and prepared statement?
A: Prepared statements offer better performance, as they are pre-compiled. Prepared statements
reuse the same execution plan for different arguments rather than creating a new execution plan
every time. Prepared statements use bind arguments, which are sent to the database engine. This
allows mapping different requests with same prepared statement but different arguments to
execute the same execution plan. Prepared statements are more secure because they use bind
variables, which can prevent SQL injection attack.

Q: How do you register a driver?

A: There are 2 approaches for registering the Driver:
Class.forName(): This method dynamically loads the driver's class file into memory,
which automatically registers it. This method is preferable because it allows you to make
the driver registration configurable and portable.
DriverManager.registerDriver(): This static method is used in case you are using a nonJDK compliant JVM, such as the one provided by Microsoft.
Q: What are the benefits of JDBC 4.0?
A: Here are few advantages of JDBC 4.0
1. Auto loading of JDBC driver class. In the earlier versions we had to manually register
and load drivers using class.forName.
2. Connection
management
javax.sql.PooledConnection.

enhancements.

New

methods

added

to

3. DataSet Implementation of SQL using annotations.

4. SQL XML support.
Q: What do you mean by fastest type of JDBC driver?
A: JDBC driver performance or fastness depends on a number of issues: Quality of the driver
code, size of the driver code, database server and its load, Network topology, Number of times
your request is translated to a different API.
Q: In real time project which driver did you use?
A: Tell about your real time experience.
Q: How do you create a connection object?
A: There are 3 overloaded DriverManager.getConnection() methods to create a connection
object:
1. getConnection(String url, String user, String password):Using a database URL with a
username and password. For example:
2. String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
3. String USER = "username";
4. String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);
5. getConnection(String url):Using only a database URL. For example:
6. String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";

Connection conn = DriverManager.getConnection(URL);

7. getConnection(String url, Properties prop):Using a database URL and a Properties
object. For example:
8. String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
9. Properties info = new Properties( );
10. info.put( "user", "username" );
info.put( "password", "password" );
Q: How can I determine whether a Statement and its ResultSet will be closed on a commit
or rollback?
A: Use the DatabaseMetaData methods supportsOpenStatementsAcrossCommit() and
supportsOpenStatementsAcrossRollback() to check.
Q: Is there a practical limit for the number of SQL statements that can be added to an
instance of a Statement object
A: The specification makes no mention of any size limitation for Statement.addBatch(), this is
dependent, on the driver.
Q: How cursor works in scrollable result set?
A: There are several methods in the ResultSet interface that involve moving the cursor, like:
beforeFirst(), afterLast(), first(), last(), absolute(int row), relative(int row), previous(), next(),
getRow(), moveToInsertRow(), moveToCurrentRow().
Q: How can you view a result set?
A: ResultSet interface contains get methods for each of the possible data types, and each get
method has two versions:
1. One that takes in a column name.
2. One that takes in a column index.
For e.g.: getInt(String columnName), getInt(int columnIndex)
Q: How do you update a result set?
A: ResultSet interface contains a collection of update methods for updating the data of a result
set. Each update method has two versions for each data type:
1. One that takes in a column name.
2. One that takes in a column index.
These methods change the columns of the current row in the ResultSet object, but not in the
underlying database. To update your changes to the row in the database, you need to invoke one
of the following methods:

updateRow(), deleteRow(), refreshRow(), cancelRowUpdates(), insertRow()

Q: How does JDBC handle the data types of Java and database?
A: The JDBC driver converts the Java data type to the appropriate JDBC type before sending it
to the database. It uses a default mapping for most data types. For example, a Java int is
converted to an SQL INTEGER.
Q: What causes "No suitable driver" error?
A: "No suitable driver" is occurs during a call to the DriverManager.getConnection method,
may be of any of the following reason:
1. Due to failing to load the appropriate JDBC drivers before calling the getConnection
method.
2. It can be specifying an invalid JDBC URL, one that is not recognized by JDBC driver.
3. This error can occur if one or more the shared libraries needed by the bridge cannot be
loaded.
Q: How do you handle SQL NULL values in Java?
A: SQL's use of NULL values and Java's use of null are different concepts. There are three
tactics you can use:
1. Avoid using getXXX( ) methods that return primitive data types.
2. Use wrapper classes for primitive data types, and use the ResultSet object's wasNull( )
method to test whether the wrapper class variable that received the value returned by the
getXXX( ) method should be set to null.
3. Use primitive data types and the ResultSet object's wasNull( ) method to test whether the
primitive variable that received the value returned by the getXXX( ) method should be
set to an acceptable value that you've chosen to represent a NULL.
Q: What does setAutoCommit do?
A: When a connection is created, it is in auto-commit mode. This means that each individual
SQL statement is treated as a transaction and will be automatically committed right after it is
executed. By setting auto-commit to false no SQL statements will be committed until you
explicitly call the commit method.
Q: Why will you set auto commit mode to false?
A: Following are the reasons:
1. To increase performance.
2. To maintain the integrity of business processes.
3. To use distributed transactions

Q: What is SavePoint?Give an example.

A: A savepoint marks a point that the current transaction can roll back to. Instead of rolling all
of its changes back, it can choose to roll back only some of them. For example, suppose you:
start a transaction
insert 10 rows into a table
set a savepoint
insert another 5 rows
rollback to the savepoint
commit the transaction
After doing this, the table will contain the first 10 rows you inserted. The other 5 rows will have
been deleted by the rollback. A savepoint is just a marker that the current transaction can roll
back to.
Q: What are SQL warnings?
A: SQLWarning objects are a subclass of SQLException that deal with database access
warnings. Warnings do not stop the execution of an application, as exceptions do. They simply
alert the user that something did not happen as planned. A warning can be reported on a
Connection object, a Statement object (including PreparedStatement and CallableStatement
objects), or a ResultSet object. Each of these classes has a getWarnings method.
Q: Why would you use a batch process?
A: Batch Processing allows you to group related SQL statements into a batch and submit them
with one call to the database.
Q: What are the steps followed to create a batch process?
A: Typical sequences of steps to use Batch Processing with Statement or PrepareStatement
Object are:
1. In case of Batch processing using PrepareStatement object, create SQL statements with
placeholders.
2. Create a Statement or PrepareStatement object using either createStatement() or
prepareStatement() methods respectively.
3. Set auto-commit to false using setAutoCommit().
4. Add as many as SQL statements you like into batch using addBatch() method on created
statement object.
5. Execute all the SQL statements using executeBatch() method on created statement
object.

6. Finally, commit all the changes using commit() method.

Q: What is a Stored Procedure and how do you call it in JDBC?
A: A stored procedure is a group of SQL statements that form a logical unit and perform a
particular task. For example operations on an employee database (hire, fire, promote, lookup)
could be coded as stored procedures executed by application code. Stored procedures can be
called using CallableStatement class in JDBC API. For example the following code
demonstrates this:
CallableStatement cs = con.prepareCall("{call MY_SAMPLE_STORED_PROC}");
ResultSet rs = cs.executeQuery();
Q: What is JDBC SQL escape syntax?
A: The escape syntax gives you the flexibility to use database specific features unavailable to
you by using standard JDBC methods and properties.
The general SQL escape syntax format is as follows:
{keyword 'parameters'}.
JDBC defines escape sequences that contain the standard syntax for the following language
features:
1. Date, time, and timestamp literals (d, t, ts Keywords).
2. Scalar functions such as numeric, string, and data type conversion functions(fn
Keyword).
3. Outer joins(oj Keyword)
4. Escape characters for wildcards used in LIKE clauses(escape Keyword).
5. Procedure calls(call Keyword).
Q: What is a transaction?
A: A transaction is a logical unit of work. To complete a logical unit of work, several actions
may need to be taken against a database. Transactions are used to provide data integrity, correct
application semantics, and a consistent view of data during concurrent access.
Q: How will you insert multiple rows into a database in a single transaction?
A: Follow steps as below:
//turn off the implicit commit
Connection.setAutoCommit(false);
//..your insert/update/delete goes here
Connection.Commit();

a new transaction is implicitly started.

Q: When will you get the message "No Suitable Driver"?
A: When a Connection request is issued, the DriverManager asks each loaded driver if it
understands the URL sent. When the URL passed is not properly constructed, then the "No
Suitable Driver" message is returned.
Q: What is the difference between execute, executeQuery, executeUpdate?
A: boolean execute(): Executes the any kind of SQL statement
ResultSet executeQuery(): This is used generally for reading the content of the database. The
output will be in the form of ResultSet. Generally SELECT statement is used.
int executeUpdate(): This is generally used for altering the databases. Generally DROP TABLE
or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements
will be used in this. The output will be in the form of int which denotes the number of rows
affected by the query.
Q: Why do you have to close database connections in Java?
A: You need to close the resultset, the statement and the connection. If the connection has come
from a pool, closing it actually sends it back to the pool for reuse. We can do this in the
finally{} block, such that if an exception is thrown, you still get the chance to close this.
Q: What is the use of blob, clob datatypes in JDBC?
A: These are used to store large amount of data into database like images, movie etc which are
extremely large in size.
Q: Resultset is an interface, how does it support rs.Next()?
A: Every vendor of Database provides implementation of ResultSet & other interfaces, through
the Driver.
Q: What is Connection Pooling ?
A: Connection Pooling is a technique used for reuse of physical connections and reduced
overhead for your application. Connection pooling functionality minimizes expensive
operations in the creation and closing of sessions.Database vendor's help multiple clients to
share a cached set of connection objects that provides access to a database. Clients need not
create a new connection everytime to interact with the database.
Q: How do you implement connection pooling
A: If you use an application server like WebLogic, WebSphere, jBoss, Tomcat. , then your
application server provides the facilities to configure for connection pooling. If you are not
using an application server then components like Apache Commons DBCP Component can be
used.
Q: Out of byte[] or a java.sql.Blob, which has best performance when used to manipulate
data from database?
A: java.sql.Blob has better performance as it does not extract any data from the database until
you explicitly ask it to.

Q: Out of String or a java.sql.Clob, which has best performance when used to manipulate
data from database?
A: java.sql.Clob has better performance as it does not extract any data from the database until
you explicitly ask it to.
Q: Suppose the SELECT returns 1000 rows, then how to retrieve the first 100 rows, then go
back and retrieve the next 100 rows?
A: Use the Statement.setFetchSize method to indicate the size of each database fetch.
Q: What does the Class.forName("MyClass") do?
A: Class.forName("MyClass"):
1. Loads the class MyClass.
2. Execute any static block code of MyClass.
3. Returns an instance of MyClass.
Q: When you say Class.forName() loads the driver class, does it mean it imports the driver
class using import statement?
A: No, it doesn't. An import statement tells the compiler which class to look for.
Class.forName() instructs the Classclass to find a class-loader and load that particular Class
object into the memory used by the JVM.
Q: What we set the attribute Concurrency in ResultSet?
A: The ResultSet concurrency determines whether the ResultSet can be updated, or only read. A
ResultSet can have one of two concurrency levels:
1. ResultSet.CONCUR_READ_ONLY :means that the ResultSet can only be read.
2. ResultSet.CONCUR_UPDATABLE : means that the ResultSet can be both read and
updated.
Q: What are the differences between setMaxRows(int) and SetFetchSize(int)?
A: The difference between setFetchSize(int) and setMaxRow(int) are:
setFetchSize(int) defines the number of rows that will be read from the database when
the ResultSet needs more rows. setFetchSize(int) affects how the database returns the
ResultSet data.
setMaxRows(int) method of the ResultSet specifies how many rows a ResultSet can
contain at a time. setMaxRows(int) affects the client side JDBC object.

Q: What is a RowSet?
A: A JDBC RowSet object holds tabular data in a way that makes it more flexible and easier to
use than a result set. A RowSet objects are JavaBeans components.

Q: What are different types of RowSet objects?

A: There are two types of RowSet:
1. Connected: A connected RowSet Object is permanent in nature. It doesn't terminate until
the application is terminated.
2. Disconnected: A disconnected RowSet object is ad-hoc in nature. Whenever it requires
retrieving data from the database, it establishes the connection and closes it upon
finishing the required task. The data that is modified during disconnected state is
updated after the connection is re-established.
Q: What is a "dirty read"?
A: In typical database transactions, say one transaction reads and changes the value while the
second transaction reads the value before committing or rolling back by the first transaction.
This reading process is called as 'dirty read'. Because there is always a chance that the first
transaction might rollback the change which causes the second transaction reads an invalid
value.
Q: Which isolation level prevents dirty read in JDBC, connection class.
A: TRANSACTION_READ_COMMITTED prevents dirty reads.
Q: What is Metadata and why should you use it?
A: JDBC API has two Metadata interfaces : DatabaseMetaData & ResultSetMetaData. The
meta data provides comprehensive information about the database as a whole. The
implementation for these interfaces is implemented by database driver vendors to let users know
the capabilities of a Database.
Q: How to Connect to an Excel Spreadsheet using JDBC in Java ?
A: Follow the steps below:
First setup the new ODBC datasource. Goto Administrative Tools->Data Sources
(ODBC)->System DSN tab->Add->Driver do Microsoft Excel(*.xls)->Finish. Now give
the Data Source Name (SampleExcel) & Description. Next, click Select Workbook and
point to your excel sheet.
In the code make to following code additions:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn=DriverManager.getConnection("jdbc:odbc:SampleExcel","","");

stmt=conn.createStatement();

sql="select * from [Sheet1$]";

rs=stmt.executeQuery(sql);
Where Sheet1 is the excel sheet name.

Q: What is difference between JDBC, JNDI and Hibernate?

A: Hibernate is an Object-Relational Mapping tool. It maps Objects to relational data.
The Java Naming and Directory Interface (JNDI) is an API to access different naming and
directory services. You use it to access something stored in a directory or naming service
without haveing to code specifically to that naming or directory service.
Java DataBase Connectivity (JDBC) API is an API to access different relational databases.
You use it to access relational databases without embedding a dependency on a specific
database type in your code.
What are the types of statements in JDBC?
JDBC API has 3 Interfaces and their key features of these are as follows:
Statement: which is used to run simple SQL statements like select and update. Statement
interfaces use for general-purpose access to your database. It is useful when you are using static
SQL statements at runtime. The Statement interface cannot accept parameters.
Prepared Statement: A SQL statement is pre-compiled and stored in a Prepared Statement
object. It is used to run Pre compiled SQL. This object can then be used to efficiently execute
this statement multiple times. The object of Prepared Statement class can be created using
Connection.prepareStatement() method. This extends Statement interface.
Callable Statement: This interface is used to execute the stored procedures. This extends
Prepared Statement interface. The object of Callable Statement class can be created using
Connection.prepareCall() method.
What causes No suitable driver error?
"No suitable driver" is occurs during a call to the DriverManager.getConnection method, may be
of any of the following reason:

Due to failing to load the appropriate JDBC drivers before calling the getConnection
method.

It can be specifying an invalid JDBC URL, one that is not recognized by JDBC driver.

This error can occur if one or more the shared libraries needed by the bridge cannot be
loaded

What does setAutoCommit do?

setAutoCommit() invoke the commit state query to the database. To perform batch updation we
use the setAutoCommit() which enable us to execute more than one statement together, which in
result minimize the database call and send all statement in one batch.

setAutoCommit() allowed us to commit the transaction commit state manually the default values
of the setAutoCommit() is true.
Why Prepared Statements are faster?
Prepared execution is faster than direct execution for statements executed more than three or four
times because the statement is compiled only once. Prepared statements and JDBC driver are
linked with each other. We can bind drivers with columns by triggering the query into the
database. When we execute Connection.prepareStatement(), all the columns bindings take place,
in oder to reduce the time.
What restrictions are placed on method overriding?
The restriction on method overloading is the signature of the method.

The signature is the number, type, and order of the arguments passed to a method.

Overridden methods must have the same name, argument list, and return type.

Any method which has the same name cannot have the same signature.

They can have the same return types in the same scope.

The compiler uses the signature to detect which overloaded method to refer when a
overloaded method is called.

If two methods have the same name and signature the compiler will throw a runtime
error.

What are types of JDBC drivers?

There are four types of drivers defined by JDBC as follows:

JDBC/ODBC: These require an ODBC (Open Database Connectivity) driver for the
database to be installed. It is used for local connection.

Native API (partly-Java driver): This type of driver uses a database API to interact with
the database. It also provides no host redirection.

Network Protocol Driver: It makes use of a middle-tier between the calling program
and the database. The client driver communicates with the net server using a databaseindependent protocol and the net server translates this protocol into database calls.

Native Protocol Drive: This has a 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.

Is it possible to connect to multiple databases simultaneously? Using single statement can one
update or extract data from multiple databases?
Yes, it is possible but it depends upon the capabilities of the specific driver implementation, we
can connect to multiple databases at the same time. We doing following steps:

Minimum one driver will be used to handle the commits transaction for multiple
connections.

To update and extract data from the different database we use single statement for this we
need special middleware to deal with multiple databases in a single statement or to
effectively treat them as one database.

What are the differences between setMaxRows(int) and SetFetchSize(int)?

The difference between setFetchSize and setMaxRow are:

setFetchSize(int) defines the number of rows that will be read from the database when the
ResultSet needs more rows whereas setMaxRows(int) method of the ResultSet specifies
how many rows a ResultSet can contain at a time.

In setFetchSize(int), method in the java.sql.Statement interface will set the 'default' value
for all the ResultSet derived from that Statement whereas in setMaxRow(int) default
value is 0, i.e. all rows will be included in the ResultSet.

the setMaxRows affects the client side JDBC object while the setFetchSize affects how
the database returns the ResultSet data.

How can I manage special characters when I execute an INSERT query?

The special characters meaning in SQL can be preceded with a special escape character in
strings, e.g. "\". In order to specify the escape character used to quote these characters, include
the following syntax on the end of the query:
{escape 'escape-character'}
For example, the query
SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}
finds identifier names that begin with an underscore.
What is the benefit of having JdbcRowSet implementation? Why do we need a JdbcRowSet like
wrapper around ResultSet?

The JdbcRowSet implementation is a wrapper around a ResultSet object has following

advantages over ResultSet:

It makes possible to use the ResultSet object as a JavaBeans component.

A JdbcRowSet can be used as a JavaBeans component, thus it can be created and

configured at design time and executed at run time.

It can be used to make a ResultSet object scrollable and updatable. All RowSet objects
are by default scrollable and updatable.

Explain Basic Steps in writing a Java program using JDBC.

JDBC makes the interaction with RDBMS simple and intuitive. When a Java application needs
to access database :

Load the RDBMS specific JDBC driver because this driver actually communicates with
the database.

Open the connection to database, for sending SQL statements and get results back.

Create JDBC Statement object containing SQL query.

Execute statement which returns result set. ResultSet contains the tuples of database table
as a result of SQL query.

Process the result set.

Close the connection.

I have the choice of manipulating database data using a byte[] or a java.sql.Blob. Which has best
performance?
We use java.sql.Blob, because of following reason:

It does not extract any data from the database until we trigger a query to the databse.

We use byte[] for inserting data in the database when data is not upload in the database
till yet.

java.sql.Blob is used when extraction of the data is performed.

What are DML and DDL?

Data Manipulation Language (DDL) this portion of the SQL standard is concerned with
manipulating the data in a database as opposed to the structure of a database. The DML deals
with the SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK.
Data Definition Language (DDL) this portion of the SQL standard is concerned with the
creation, deletion and modification of database objects like tables, indexes and views. The core
verbs for DDL are CREATE, ALTER and DROP. While most DBMS engines allow DDL to be
used dynamically, it is often not supported in transactions.
How can you load the drivers?
It is very simple and involves just one line of code to load the driver or drivers we want to use.
For example, We want to use the JDBC-ODBC Bridge driver, the following code will load it:
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);
Driver documentation will give you the class name to use. For instance, if the class name is
jdbc.DriverHELLO, you would load the driver with the following line of code:
Class.forName(jdbc.DriverHELLO);
How do I insert an image file (or other raw data) into a database?
All raw data types should be read and uploaded to the database as an array of bytes, byte[].

Originating from a binary file.

Read all data from the file using a FileInputStream.

Create a byte array from the read data.

Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the

data.

1. Discuss the significances of JDBC.

The significances are given below:
JDBC is the acronym stands for Java Database Connectivity.
Java Database Connectivity (JDBC) is a standard Java API .
It's purpose is to interact with the relational databases in Java.
JDBC is having a set of classes & interfaces which can be used from any Java application.

 By using the Database Specific JDBC drivers, it interacts with a database without the
applications of RDBMS.
2. Name the new features added in JDBC 4.0.
The major features introduced in JDBC 4.0 are :
Auto-loading by JDBC driver class.
Enhanced Connection management
RowId SQL enabled.
DataSet implemented by SQL by using Annotations
Enhancements of SQL exception handling
Supporting SQL XML files.
3. How do Java applications access the database using JDBC?
Java applications access the database using JDBC by :
Communicating with the database for Loading the RDBMS specific JDBC driver
Opening the connection with database
Sending the SQL statements and get the results back.
Creating JDBC Statement object which contains SQL query.
Executing statement to return the resultset(s) containing the tuples of database table which is a
result of SQL query.
Processing the result set.
Closing the connection.
4. Briefly tell about the JDBC Architecture.
The JDBC Architecture consists of two layers:
1.The JDBC API
2.The JDBC Driver API
The JDBC API provides the application-JDBC Manager connection.

 The JDBC Driver API supports the JDBC Manager-to-Driver Connection.

The JDBC API interacts with a driver manager, database-specific driver for providing
transparent connectivity for the heterogeneous databases.
The JDBC driver manager authenticates that the correct driver has been used to access each
data source.
The driver manager supports multiple concurrent drivers connected to the multiple
heterogeneous databases.
5.Explain the life cycle of JDBC.
The life cycle for a servlet comprises of the following phases:
DriverManager : for managing a list of database drivers.
Driver : for communicating with the database.
Connection : for interfacing with all the methods for connecting a database.
Statement : for encapsulating an SQL statement for passing to the database which had been
parsed, compiled, planned and executed.
ResultSet: for representing a set of rows retrieved for the query execution.
6.Describe how the JDBC application works.
A JDBC application may be divided into two layers:
Driver layer
Application layer
The Driver layer consists of DriverManager class & the JDBC drivers.
The Application layer begins after putting a request to the DriverManager for the connection.
An appropriate driver is chosen and used for establishing the connection.
This connection is linked to the application layer.
The application needs the connection for creating the Statement kind of objects by which the
results are obtained.
7. How a database driver can be loaded with JDBC 4.0 / Java 6?

 By providing the JAR file , the driver must be properly configured.

The JAR file is placed in the classpath.
It is not necessary to explicitly load the JDBC drivers by using the code like Class.forName() to
register in the JDBC driver.
The DriverManager class looks after this, via locating a suitable driver at the time when the
DriverManager.getConnection() method is called.
This feature provides backward-compatibility, so no change is needed in the existing JDBC
code.
8. What does the JDBC Driver interface do?
The JDBC Driver interface provides vendor-specific customized implementations of the
abstract classes.
It is provided normally by the JDBC API.
For each vendor the driver provides implementations of the java.sql.Connection,
, PreparedStatement, Driver,Statement, ResultSet and CallableStatement.
9. What is represented by the connection object?
The connection object represents the communication context.
All the communication with the database is executed via the connection objects only.
Connection objects are used as the main linking elements.
10. What is a Statement ?
The Statement acts just like a vehicle via which SQL commands are sent.
By the connection objects, we create the Statement kind of objects.
Statement stmt = conn.createStatement();
This method returns the object, which implements the Statement interface.
11.Define PreparedStatement.
A Preparedstatement is an SQL statement which is precompiled by the database.
By precompilation, the prepared statements improve the performance of the SQL commands
that are executed multiple times (given that the database supports prepared statements).

 After compilation, prepared statements may be customized before every execution by the
alteration of predefined SQL parameters.
Code:
PreparedStatement pstmt = conn.prepareStatement("UPDATE data= ? WHERE vl = ?");
pstmt.setBigDecimal(1, 1200.00);
pstmt.setInt(2, 192);
12. Differentiate between a Statement and a PreparedStatement.
A standard Statement is used for creating a Java representation for a literal SQL statement and
for executing it on the database.
A PreparedStatement is a precompiled Statement.
A Statement has to verify its metadata in the database every time.
But ,the prepared statement has to verify its metadata in the database only once.
If we execute the SQL statement, it will go to the STATEMENT.
But, if we want to execute a single SQL statement for the multiple number of times, itll go to
the PreparedStatement.
13. What is the function of setAutoCommit?
When a connection is created, it is in auto-commit mode.
This means that each individual SQL statement is to be treated as a single transaction .
The setAutoCommit will be automatically committed just after getting executed.
The way by which two or more statements are clubbed into a transaction to disable the autocommit mode is :
con.setAutoCommit (false);
Once auto-commit mode is disabled, no SQL statements will be committed until we call the
method commit explicitly.
Code :
con.setAutoCommit(false);
PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEE SALES = ?
WHERE COF_NAME LIKE ?");

updateSales.setInt(1, 50); updateSales.setString(2, "Colombian");

updateSales.executeUpdate();
PreparedStatement updateTotal =
con.prepareStatement("UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME
LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);
14 How do we call a stored procedure from JDBC?
The foremost step is to create a CallableStatement object.
With the Statement and PreparedStatement object ,it is done with an open
Connection object.
A CallableStatement object contains a call to a stored procedure.
Code:
CallableStatement cs = con.prepareCall("{call SHOW_Sales}");
ResultSet rs = cs.executeQuery();
15. What is SQLWarning and discuss the procedure of retrieving warnings?
SQLWarning objects, a subclass of SQLException is responsible for the database access
warnings.
Warnings will not stop the execution of an specific application, as exceptions do.
It simply alerts the user that something did not happen as planned.
A warning may be reported on the Connection object, the Statement object (including
PreparedStatement and CallableStatement objects) or on the ResultSet object.
Each of these classes has a getWarnings method, which you must invoke in order to see the first
warning reported on the calling object:
Code :
SQLWarning waring = stmt.getWarnings();
if (warning != null)
{
System.out.println("n---Warning---n");
while (warning != null)
{

System.out.println("Message: " + warning.getMessage());

System.out.println("SQLState: " + warning.getSQLState());
System.out.println("Vendor error code: ");
System.out.println(warning.getErrorCode());
System.out.println("");
warning = warning.getNextWarning();
}
}
16. Explain the types of JDBC Drivers and name them.
The 4 types of JDBC Drivers are:
Pure Java Driver JDBC Net
Bridge Driver JDBC-ODBC
Network protocol Driver
Partly Java Driver Native API
17. How can we move the cursor in a scrollable result set?
The new features added in the JDBC 2.0 API are able to move a resultsets cursor backward &
forward also.
There are some methods that let you direct the cursor to a particular row and checking the
position of the cursor.
Code :
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(SELECT COF_NAME, Sale _COFFEE);
Three constants can be added to the ResultSet API for indicating the kind
of the ResultSet object. The constants are:
- TYPE_FORWARD_ONLY
- TYPE_SCROLL_INSENSITIVE
- TYPE_SCROLL_SENSITIVE.

 The ResultSet constants for specifying whether a resultset is read-only or updatable are:
- CONCUR_READ_ONLY
- CONCUR_UPDATABLE.
18. How do we load the drivers?
To Load the driver or drivers we need to use a very simple one line of code.
If we want to use the JDBC/ODBC Bridge driver, the following code will load it:
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);
The driver documentation gives the class name to use.
For example, if the class name is jdbc.DriverXYZ, we can load the driver using the below line
of code:
Code:
Class.forName(jdbc.DriverXYZ);
19. What Class.forName does, while loading the drivers?
It is used for creating an instance of a driver
It is used for registering with theDriverManager.
When we have loaded a driver, it connects with the DBMS.
20. How can you make a connection?
To establish a connection we need to have an appropriate driver, connected to the DBMS.
The below line of code illustrates the idea:
Code:
String url = jdbc:odbc: rima;
Connection con = DriverManager.getConnection(url, rima, J8?);
1. What are the factors that the JDBC driver performance depends on?
The JDBC driver performance depends on:

 The driver code quality

The driver code size
The database server & its load capability
The network topology
The number of times the request is being translated to a different API.
2. How do I find whether a parameter exists in the request object?
The following code implies it
boolean hasFo = !(request.getParameter("fo") == null
|| request.getParameter("fo").equals(""));
Or
boolean hasParameter =
request.getParameterMap().contains(theParameter);
3. Expalin the method of calling a stored procedure from JDBC.
PL/SQL stored procedures are called from the JDBC programs by creating the prepareCall() in
the Connection object.
A call to the method prepareCall() carries out variable bind parameters as input parameters,
output variables & makes an object instance of the CallableStatement class.
The following line of code implies this:
Callable Statement stproc_ stmt = conn.prepareCall("{call procname(?,?,?)}");
where conn is the instance of the Connection class.
4. Name the types of JDBC drivers.
The four types of drivers defined by JDBC are:
Type 1: JDBC/ODBCThis requires an ODBC (Open Database Connectivity) driver for the
databases to be installed. This type of drivers work by converting the submitted queries into an
equivalent ODBC queries and forwarding them via native API which invokes directly to the
ODBC driver. It provides host less redirection capability too.

 Type 2: Native type API (partly-Java driver)This type of driver uses a vendor-specific driver
or database API for interacting with the database. An example of such an API is Oracle OCI
(Oracle Call Interface).
Type 3: Open Net Protocol This is vendor non-specific and works by forwarding database
requests using a net server component. The net server accesses the database. The client driver
connects with the server using a database-indifferent protocol and the server translates this
protocol into database calls.
Type 4: Proprietary Protocol-Net(pure Java driver)This is same as per configuration as type 3
driver while it uses a wire protocol directed towards a particular vendor and so it can access only
that vendor's database.
5. Explain how to Make Updates to the Updatable ResultSets.
The JDBC 2.0 API can update rows in a ResultSet using the methods in the Java rather than
using a SQL command.
But before doing that, we create a ResultSet object which is updatable.
For doing this, we give the ResultSet CONCUR_UPDATABLE in the createStatement method.
Code:
Connection con =
DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs =
stmt.executeQuery("SELECT COF_NAME, PRICE ");
6.What are the utilities of the callable statements?
Callable statements are mainly used in the JDBC applications.
Callable statements are used to invoke stored procedures
This is mainly used in functions.
7. Differentiate between TYPE_SCROLL_INSENSITIVE and
TYPE_SCROLL_SENSITIVE.
We will get a scrollable ResultSet object if we specify either one of the ResultSet constants.
The difference between the two depends on, whether a resultset is showing fv changes or not.

 This difference depends on certain methods which are called to detect changes or not.
The resultset TYPE_SCROLL_INSENSITIVE does not show the change to it but the ResultSet
srs = TYPE_SCROLL_SENSITIVE will show the change.
The following code explains the difference :
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs =
stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
srs.afterLast();
while (srs.previous())
{
String name = srs.getString("COF_NAME");
float price 1= srs.getFloat("PRICE");
System.out.println(name + " " + price1);
}
8. How JDBC Statements are used?
A Statement is an object, by which we send an SQL statement to the DBMS.
We create a Statement object and then execute it.
For a SELECT Statement, the method used is executeQuery.
The Statement that creates or modifies a table is executeUpdate.
For creating a Statement object an instance of an active connection is required.
In the following code, we use our Connection object con to create the Statement object
Statement stmt = con . createStatement();
9. How can we retrieve data from the ResultSet?

 JDBC returns back the results in a ResultSet object.

So we have to declare an instance of the class ResultSet to keep the results.
The following code declares the ResultSet object rs.
ResultSet rs = stmt.executeQuery (SELECT COF_NAME, PRICE FROM COFFEES);
String s = rs.getString (NAME);
The method getString calls ResultSet object rs, So getString() retrieves the value stored in the
column NAME in the row Ssrs.
10. Name the different types of Statements? How we can you use
PreparedStatement.
The different types of Statements are
Regular statement (uses createStatement method)
prepared statement (uses prepareStatement method)
callable statement (uses prepareCall)
PreparedStatement :
PreparedStatement is derived from the class Statement.
To execute a Statement object several times a PreparedStatement object is required.
The PreparedStatement object contains not only a compiled SQL statement, but also a
precompiled SQL statement too.
Code:
PreparedStatement updateSales =con.prepareStatement("UPDATE account = ? WHERE
CON_NAME ?");
1.

What

is

JDBC?

JDBC technology is an API (included in both J2SE and J2EE releases) that provides crossDBMS connectivity to a wide range of SQL databases and access to other tabular data sources,
such as spreadsheets or flat files. With a JDBC technology-enabled driver, you can connect all
corporate
data
even
in
a
heterogeneous
environment
2.
What
are
stored
procedures?
A stored procedure is a set of statements/commands which reside in the database. The stored
procedure is precompiled. Each Database has it's own stored procedure language,

Read

Also

Types

3.
by

What

of

JDBC
is

Driver
JDBC

explained

with
Driver

Example
?

The JDBC Driver provides vendor-specific implementations of the abstract classes provided
the JDBC API. This driver is used to connect to the database.

4.
What are the steps required to execute a query in JDBC?
First we need to create an instance of a JDBC driver or load JDBC drivers, then we need to
register this driver with DriverManager class. Then we can open a connection. By using this
connection , we can create a statement object and this object will help us to execute the query.
5.
What
is
DriverManager
?
DriverManager is a class in java.sql package. It is the basic service for managing a set of
JDBC
drivers.
6.
What
is
a
ResultSet
?
A table of data representing a database result set, which is usually generated by executing a
statement
that
queries
the
database.
ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is
positioned before the first row. The next method moves the cursor to the next row, and because
it returns false when there are no more rows in the ResultSet object, it can be used in a while
loop
to
iterate
through
the
result
set.
7.
What
is
Connection?
Connection class represents a connection (session) with a specific database. SQL statements
are executed and results are returned within the context of a connection.
Connection object's database is able to provide information describing its tables, its supported
SQL grammar, its stored procedures, the capabilities of this connection, and so on. This
information
is
obtained
with
the
getMetaData
method.
8.
A

What
class

does
as

loaded

Class.forName
by
the

return?
classloader.

9.
What
is
Connection
pooling?
Connection pooling is a technique used for sharing server resources among requesting clients.
Connection pooling increases the performance of Web applications by reusing active database
connections instead of creating a new connection with every request. Connection pool manager
maintains
a
pool
of
open
database
connections.
10. What
There

are

are
the
mainly four

different
JDB
drivers
type of JDBC drivers available.

available?
They are:

Type 1 : JDBC-ODBC Bridge Driver - A JDBC-ODBC bridge provides JDBC API access via
one or more ODBC drivers. 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. For information on the JDBC-ODBC bridge
driver
provided
by
Sun.
Type 2: Native API Partly Java Driver- 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: Network protocol Driver- 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 technologybased 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 technologybased
drivers
to
their
existing
database
middleware
products.
Type 4: JDBC Net pure Java Driver - 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.
11.
What
is
the
fastest
type
of
JDBC
driver?
Type 4 (JDBC Net pure Java Driver) is the fastest JDBC driver. Type 1 and Type 3 drivers
will be slower than Type 2 drivers (the database calls are make at least three translations versus
two),
and
Type
4
drivers
are
the
fastest
(only
one
translation).
12.
Is
the
JDBC-ODBC
Bridge
multi-threaded?
No. The JDBC-ODBC Bridge does not support multi threading. The JDBC-ODBC Bridge
uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi-threaded
Java programs may use the Bridge, but they won't get the advantages of multi-threading.
13. What
is
cold
backup,
hot
backup,
warm
backup
recovery?
Cold backup means all these files must be backed up at the same time, before the database is
restarted. Hot backup (official name is 'online backup' ) is a backup taken of each tablespace
while the database is running and is being accessed by the users
14.
What
is
the
advantage
of
denormalization?
Data denormalization is reverse procedure, carried out purely for reasons of improving
performance. It maybe efficient for a high-throughput system to replicate data for certain data.

15.
How
do
you
handle
your
own
transaction
?
Connection Object has a method called setAutocommit ( boolean flag) . For handling our own
transaction we can set the parameter to false and begin your transaction . Finally commit the
transaction by calling the commit method.
Question 1 : Difference between SQL Date and java.util.Date in Java
Answer : This is one of JDBC Questions which I like to ask, because knowing how to correctly store and retrieve date
in a database is often confusing for new developers and its very critical for any application. Main difference between
SQL data i.e.java.sql.Date and util date i.e. java.util.Date is that SQL Date only contains date part and not
time part but util date contains both date and time part. See SQL Date vs Util Date in Java for more differences
between them.
Question 2: What is benefit of using PreparedStatement in Java
Answer : Another wonderful JDBC interview question which is very popular on telephonic as well as on early round of
Java Interviews. There are several benefits of using PreparedStatement while querying database from Java
program e.g. better performance and prevents from SQL Injection. I suggest to read Why use PreparedStatement in
Java for more benefits and details on JDBC PreparedStatement.
Question 3: What is JDBC database Connection Pool? How to setup in Java?
Answer : I have always seen at least one question related to database connection pool in JDBC Interview e.g. benefit
of using JDBC Connection pool. Well JDBC connection pool maintains pool of JDBC connection which is used by
application to query database. Since JDBC connection are expensive it take time to create them which can slow
response time of server if created during request time. Creating them on application start-up and reusing them result
in better performance. See How to setup JDBC Connection Pool in Java using Spring for more details on JDBC
connection pool and benefits it offer.
Question 4: What is difference between type 2 and type 4 JDBC drivers in Java
Answer : This JDBC Interview question is as old as Vector vs ArrayList or Hashtable vs HashMap. I remember
questions about JDBC ODBC drivers asked during almost every fresher level interview. Key difference between type
2 and type 4 JDBC driver is that you just need to include JAR file of JDBC driver in your classpath to connect
database. See this link for more difference between type 2 and type 4 JDBC drivers.
Question 5: What is difference between java.sql.Time and java.sql.TimeStamp in Java
Answer : This JDBC questions is similar to earlier JDBC interview question java.sql.Date vs java.util.Date.
Main difference is that java.sql.Time class doesn't contain any date information on it
while java.sql.TimeStamp contains date information. See 4 difference between Time and Timestamp in Java
JDBC for more differences.
Question 6: What happens if we call resultSet.getInt(0) when Select query result just have one column
of integer type?
Answer : This is one of the tricky Java question which comes from JDBC. you may think that it will return first column
as integer from Query result set but unfortunately it doesn't. It throws InvalidColumnIndexException in JDBC
because index forgetXXX() or setXXX() in JDBC starts with 1. See How to fix InvalidColumnIndexException in
JDBC for more details on this JDBC interview question.
Question 7: What is difference between RowSet and ResultSet in JDBC?

Answer : One of the popular JDBC interview question now days. RowSet extends ResultSet and add support for
JDBC API to Java bean component model. Main difference of ResultSet and RowSet is RowSet being connected
and disconnected, which is another follow-up JDBC question. RowSet makes it easy to use ResultSet but as I said
you only like to use it to get benefit of disconnected and connected RowSet.

Question 8: What is use of setAutoCommit(false) in JDBC ?

Answer : This is one of the JDBC Interview question I touched on Top 10 JDBC best practices for Java programmer.
makingsetAutoCommit(false) saves a lot of performance as it doesn't commit transaction automatically after
each query and we do batch update. It allows you to handle it using commit() and rollback(). This has result
in impressive performance gain in DAO layer.

Question 9: How to call stored procedure from JDBC in Java?

Answer : This JDBC Interview question is another one you can add on any frequently asked list and just can't afford
not to prepare. Mostly asked to Java developers with 2 to 4 years experience. In its simplicity you can just say
that CallableStatementis used to call stored procedure, which may lead questions like how do you pass
parameters to stored procedure from Java ordifference between IN and OUT parameters in JDBC etc. It's worth to
prepare this JDBC question in detail. By the way IN parameter is used to pass input to stored procedure and OUT
parameter is used to store output return from stored procedure. IF your stored procedure return multiple values than
you can also use ResultSet to traverse all results.
Question 10: What is difference between Connected and disconnected RowSet in JDBC?
Answer : I have seen this JDBC question asked as a follow-up question of previous JDBC interview
question RowSet vsResultSet. Main difference between connected and disconnected RowSet in JDBC is that
disconnected RowSet doesn't require JDBC Connection while it's on disconnected state. This makes
disconnected RowSet light and ideal to use in thin clients, while connected RowSet is just a wrapper
around ResultSet. JDBCRowSet and WebRowSet are two examples of connected RowSet
while a CachedRowSet is an example of disconnected RowSet which caches data in memory. Ideal for small data
set and thin Java clients with small memory foot print.
Question 11: What is difference between Statement, PreparedStatement and CallableStatement in Java?
Answer : One of the classical JDBC interview question. Main difference
between Statement and PreparedSatement is performance and avoiding SQL Injection as we have seen
in Benefits of using PreparedStatement in Java. While CallableStatement has very specific use in JDBC and used to
call stored procedure from Java program

3.Explain Basic Steps in writing a Java program using JDBC?

JDBC makes the interaction with RDBMS simple and intuitive. When a Java application needs to access database :

Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is
automatically loaded).

Open the connection to database which is then used to send SQL statements and get results back.

Create JDBC Statement object. This object contains SQL query.

Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.

Process the result set.

Close the connection.

4.Exaplain the JDBC Architecture.

The JDBC Architecture consists of two layers:

The JDBC API, which provides the application-to-JDBC Manager connection.

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 heterogeneous 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 in Figure 1.

Figure 1: JDBC Architecture

5.What are the main components of JDBC ?

The life cycle of a servlet consists of the following phases:

DriverManager: Manages a list of database drivers. Matches connection requests from the java application with the proper
database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under JDBC will be
used to establish a database Connection.

Driver: The database communications link, handling all communication with the database. Normally, once the driver is loaded,
the developer need not call it explicitly.

Connection : Interface with all methods for contacting a database.The connection object represents communication context,
i.e., all communication with database is through connection object only.

Statement : Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.

ResultSet: The ResultSet represents set of rows retrieved due to query execution.

6.How the JDBC application works?

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

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.

Figure 2: JDBC Application

7.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 backwardcompatible, so no changes are needed to the existing JDBC code.

8.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 thejava.sql.Connection,Statement,PreparedStatement, CallableStatement,
ResultSet and Driver.

9.What does the connection object represents?

The connection object represents communication context, i.e., all communication with database is through connection object only.

10.What is Statement ?
Statement acts like a vehicle through which SQL commands can be sent. Through the connection object we create statement kind of
objects.
Through the connection object we create statement kind of objects.

Statement stmt

= conn.createStatement();

This method returns object which implements statement interface.

11.What is PreparedStatement?
A prepared statement is an SQL statement that is precompiled by the database. 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.

PreparedStatement pstmt = conn.prepareStatement("UPDATE EMPLOYEES SET SALARY = ?

WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);
Here: conn is an instance of the Connection class and "?" represents parameters.These parameters must be specified before execution.

12.What is the difference between a Statement and a PreparedStatement?

Statement

PreparedStatement

A PreparedStatement is a precompiled statement. This means that when the

A standard Statement is used to create a Java representation of a literal SQL
PreparedStatement is executed, the RDBMS can just run the
statement and execute it on the database.
PreparedStatement SQL statement without having to compile it first.

Statement has to verify its metadata against the database every time.

While a prepared statement has to verify its metadata against the database
only once.

If you want to execute the SQL statement once go for STATEMENT

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

13.What are callable statements ?

Callable statements are used from JDBC application to invoke stored procedures and functions.

14.How to call a stored procedure from JDBC ?

PL/SQL stored procedures are called from within JDBC programs by means of the prepareCall() method of the Connection object created.
A call to this method takes variable bind parameters as input parameters as well as output variables and creates an object instance of the
CallableStatement class.
The following line of code illustrates this:

CallableStatement stproc_stmt = conn.prepareCall("{call

procname(?,?,?)}");

Here conn is an instance of the Connection class.

15.What are types of JDBC drivers?

There are four types of drivers defined by JDBC as follows:

Type 1: JDBC/ODBCThese require 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. It provides no host redirection capability.

Type2: Native API (partly-Java driver)This 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.

Type 3: Open Protocol-NetThis is not vendor specific and works by forwarding database requests to a remote database
source using a net server component. How the net server component accesses the database is transparent to the client. The
client driver communicates with the net server using a database-independent protocol and the net server translates this
protocol into database calls. This type of driver can access any database.

Type 4: Proprietary Protocol-Net(pure Java driver)This has a 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.