Saturday, January 17, 2009

Jdbc Interview Questions

Q.What is the Java Database Connectivity (JDBC) ?

Java Database Connectivity (JDBC) is a standard Java API to interact with relational databases form Java.

Q)What are types of JDBC drivers?

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

Type 1: JDBC/ODBC—These 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-Net—This 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.

Q)Which type of JDBC driver is the fastest one?

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

Q)Explain Basic Steps in a JDBC Programming?

Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Open the connection to database which is then used to send SQL statements and get results back.
Connection con = DriverManager.getConnection( "jdbc:odbc:SSPer","Tiger","tictac");

Create JDBC Statement object. This object contains SQL query.

PreparedStatement ps = conn.prepareStatement(
"select * from Dept where deptid=? and dept=?");
ps.setString(1,deptid);
ps.setString(2,dept);

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

ResultSet rs = ps.executeQuery();

Process the result set.
while (rs.next()) {
}

//Close the connection.
con.close();

Q)What will Class.forName do while loading drivers?

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

Q)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("select * from EMPLOYEES where SALARY = ? and ID = ?");
pstmt.setBigDecimal(1, 2333.00);
pstmt.setInt(2, 4333);
ResultSet rs = pstmt.executeQuery();

Q)What is the difference between a Statement and a PreparedStatement?
Statement
Statement has to verify its metadata against the database every time.
If you want to execute the SQL statement once go for STATEMENT

PreparedStatement

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.
Prepared statement has to verify its metadata against the database only once.
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

Q)What does setAutoCommit do?

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. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:

con.setAutoCommit(false);

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

con.setAutoCommit(false);
PreparedStatement updateSales =
con.prepareStatement( "UPDATE ICECREAMS SET SALES = ? WHERE ICECREAM_NAME LIKE ?");
updateSales.setInt(1, 50);
updateSales.setString(2, "chocolate");
updateSales.executeUpdate();
PreparedStatement updateTotal =
con.prepareStatement("UPDATE ICECREAMS SET TOTAL = TOTAL + ? WHERE ICECREAM_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "chocolate");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);

Q)What are callable statements ?

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


Q)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.



Q)How do I retrieve warnings?

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, which you must invoke in order to see the first warning reported on the calling object:

SQLWarning warning = 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.print("Vendor error code: ");
System.out.println(warning.getErrorCode());
System.out.println("");
warning = warning.getNextWarning();
}
}

Q)Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.


Q)Which is the right type of driver to use and when?

Type I driver is handy for prototyping
Type III driver adds security, caching, and connection control
Type III and Type IV drivers need no pre-installation
Note: Preferred by 9 out of 10 Java developers: Type IV. Click here to learn more about JDBC drivers.

Q)What are the standard isolation levels defined by JDBC?

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

TRANSACTION_NONE
TRANSACTION_READ_COMMITTED
TRANSACTION_READ_UNCOMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE

Q)What are the types of resultsets?

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

TYPE_FORWARD_ONLY specifies that a resultset is not scrollable, that is, rows within it can be advanced only in the forward direction.
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.
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.
Note: A TYPE_FORWARD_ONLY resultset is always insensitive.

Q)What’s the difference between TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE?

You will get a scrollable ResultSet object if you specify one of these ResultSet constants.The difference between the two has to do with whether a result set reflects changes that are made to it while it is open and whether certain methods can be called to detect these changes. Generally speaking, a result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does. All three types of result sets will make changes visible if they are closed and then reopened:

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 = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}

Q)How to Make Updates to Updatable Result Sets?

Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.

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 FROM COFFEES");

Q)What is a DataSource?
A DataSource object is the representation of a data source in the Java programming language. In basic terms,

A DataSource is a facility for storing data.
DataSource can be referenced by JNDI.
Data Source may point to RDBMS, file System , any DBMS etc..


Q)What are the advantages of DataSource?
The few advantages of data source are :

An application does not need to hardcode driver information, as it does with the DriverManager.
The DataDource implementations can easily change the properties of data sources. For example: There is no need to modify the application code when making changes to the database details.
The DataSource facility allows developers to implement a DataSource class to take advantage of features like connection pooling and distributed transactions.

Q)What is connection pooling? what is the main advantage of using connection pooling?
A connection pool is a mechanism to reuse connections created. Connection pooling can increase performance dramatically by reusing connections rather than creating a new physical connection each time a connection is requested..

No comments:

Post a Comment

 
Your Ad Here ]]>