Saturday, December 3, 2011

JDBC Interview Questions & Answers for Java Developers – Part 1



We have compiled list of some popular JDBC questions for Java Developers. Read these interview questions.

How many types of JDBC Drivers are present and what are they?  

JDBC drivers are divided into four types or levels. The different types of jdbc drivers are:

Type 1: JDBC-ODBC Bridge driver (Bridge)
Type 2: Native-API/partly Java driver (Native)
Type 3: All Java/Net-protocol driver (Middleware)
Type 4: All Java/Native-protocol driver (Pure)

What Class.forName will do while loading drivers?  

It is used to create an instance of a driver and register it with the Driver Manager. When you have loaded a driver, it is available for making a connection with a DBMS.

What are stored procedures? How is it useful?  

A stored procedure is a set of statements/commands which reside in the database. The stored procedure is pre-compiled and saves the database the effort of parsing and compiling sql statements every time a query is run. Each database has its own stored procedure language, usually a variant of C with a SQL preprocessor.

Before the advent of 3-tier/n-tier architecture it was pretty common for stored procedures to implement the business logic (A lot of systems still do it). The biggest advantage is of course speed. Also certain kinds of data manipulations are not achieved in SQL. Stored procedures provide a mechanism to do these manipulations. Stored procedures are also useful when you want to do Batch updates/exports kind of stuff on the db. The overhead of a JDBC Connection may be significant in these cases.

How to call a Stored Procedure from JDBC?  

The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure.

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.

Is the JDBC-ODBC Bridge multi-threaded?  

No. The JDBC-ODBC Bridge does not support concurrent access from different threads. 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.

What is the advantage of using PreparedStatement? 

If we are using PreparedStatement the execution time will be less. This is because PreparedStatement object contains SQL statement that has been precompiled. Thus, when the PreparedStatement is later executed, the DBMS does not have to recompile the SQL statement and prepared an execution plan - it simply runs the statement.

What is a "dirty read"?  

Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value.

What are different types of isolation levels in JDBC and explain where you can use them?  

Five types of isolation levels are as follows:

1. TRANSACTION_READ_COMMITED

If the application needs only committed records, then TRANSACTION_READ_COMMITED isolation is the good choice.

2. TRANSACTION_REPEATABLE_READ

If the application needs to read a row exclusively till you finish your work, then TRANSACTION_REPEATABLE_READ is the best choice.

3. TRANSACTION_SERIALIZABLE

If the application needs to control all of the transaction problems (dirty read, phantom read and unrepeatable read), you can choose TRANSACTION_SERIALIZABLE for maximum safety.

4. TRANSACTION_NONE

If the application doesn’t have to deal with concurrent transactions, then the best choice is TRANSACTION_NONE to improve performance.

5. TRANSACTION_READ_UNCOMMITED

If the application is searching for records from the database then you can easily choose TRANSACTION_READ_UNCOMMITED because you need not worry about other programs that are inserting records at the same time.

What are the different types of statements in JDBC?
 
The JDBC API has 3 Statements

1. Statement, 2. PreparedStatement, 3. CallableStatement.

The key features of these are as follows:

Statement

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

PreparedStatement

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

CallableStatement

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

What is JDBC?

JDBC may stand for Java Database Connectivity. It is also a trade mark. JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.

What are the common tasks of JDBC?

Common tasks of JDBC are as follows:

1. Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
2. Register a driver
3. Specify a database
4. Open a database connection
5. Submit a query
6. Receive result
7. Process result

What interfaces are used by JDBC?

List of interfaces used by JDBC with description are as follows:

Interface
Description
java.sql.Connection
Interface used to establish a connection to a database. SQL statements run within the context of a connection.
java.sql.DatabaseMetaData
Interface used to return information about the database.
java.sql.Driver
Interface used to locate the driver for a particular database management system.
java.sql.PreparedStatement
Interface used to send precompiled SQL statements to the database server and obtain results.
java.sql.ResultSet
Interface used to process the results returned from executing an SQL statement.
java.sql.ResultSetMetaData
Interface used to return information about the columns in a ResultSet object.
java.sql.Statement
Interface used to send static SQL statements to the database server and obtain results.
javax.sql.ConnectionEventListener
Receives events that a PooledConnection object generates.
javax.sql.ConnectionPoolDataSource
Factory for PooledConnection objects. A ConnectionPoolDataSource object is usually registered with a JNDI service.
javax.sql.DataSource
A factory for Connection objects. A DataSource object is usually registered with a JNDI service provider.
javax.sql.PooledConnection
A PooledConnection object represents a physical connection to a data source.

How can you Load the JDBC driver?

To load the driver, you need to load the appropriate class, make a driver instance and register it with the JDBC driver manager. Use Class.forName(String) method. This method takes a string representing a fully qualified class name and loads the corresponding class. Here is an example:

try {
          Class.forName("connect.microsoft.MicrosoftDriver");
} catch(ClassNotFoundException e) {
          System.err.println("Error loading driver: " + e);
}

The string taken in the Class.forName() method is the fully qualified class name. You should pay attention to the CLASSPATH setting. If the JDBC driver vendors distribute their drivers in a JAR file, be sure to include the JAR file in your CLASSPATH setting.

How can you make the connection?

Connection is made using following Method

getConnection(String url, String userName, String password);

This method establishes a connection to specified database url. It takes following three string types of arguments.

url: Database url where stored or created your database
userName: User name of MySQL
password: Password of MySQL

Include following code block to make a connection and create Statement.

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:dumy"; "user", "pass");
Statement stmt = con.createStatement();
}catch( Exception e ) {
e.printStackTrace();       
}

How can you create JDBC statements?

Using Statement interface we can create JDBC statements as follows 

Statement stmt = con.createStatement();  //con is the connection object reference

Different types of statements are:

Prepared Statement - whenever we want to execute single query multiple times then
we will go to this statement

Callable Statement - whenever we want to call the stored procedures from database
Server then we will use this statement

We've added more JDBC questions & answers in our next post. Click the link below to read.


4 Responses to “JDBC Interview Questions & Answers for Java Developers – Part 1”

Sandeep said...
December 6, 2011 at 12:12 PM

great job. I would add that some interviewers also try to play around to judge about design related questions w.r.t JDBC. Just see if you like my Database Interview Questions for Java Developers


Javin said...
December 12, 2011 at 1:59 PM

Good questions and crisp answers. Another important aspect is managing transaction in database which is asked.


Javin @ spring interview questions answers said...
December 14, 2011 at 5:06 PM

You can also see Some more interview questions here:

Top 10 multi-threading interview questions in Java
Top 20 core java interview questions and answers


Anonymous said...
May 22, 2012 at 5:47 PM

super


Post a Comment

© 2011 a2ztechguide.com • All rights reserved.
Blogger Template by Bloggermint