JDBC interview Questions

What is PreparedStatement?

Prepared Statement is called as “pre compiled statement”. An SQL statement is compiled before hand and stored in the PreparedStatement object. The purpose of precompiled statement is that the statement can be executed repeatedly. The required parameter values are to be substituted from time to time.

The following example illustrates the PreparedStatement object usage.

PreparedStatement pStmt = con.prepareStatement("select * from ? where balance>?");

The first ? represents a table name and the second ? represents any numeric attribute. Now the values can be set as follows:

pStmt.setString(1,"emp"); // emp is the table name and substitutes first ?
pStmt.setInt(2,29809); // 29809 is a value for balance field of the database
pStmt.execute(); // executes select statement.

Now the same select statement can be used for another table say “BankAccounts”
The following statements are the examples which use the same prepared statement with another table and attribute value

pStmt.setString(1,”BankAccounts”); // BankAccounts is the table name for first  ?
pStmt.setInt(2,600000); // 600000 is the value for the second ?
pStmt.execute(); // executes the select statement

Thus a “single select” statement for multiple tables is used. As the PreparedStatement is precompiled, it is used for several select statements instead of every time execution.

JDBC Interview - Feb 27, 2009, 22:30 pm by Vidya Sagar

How do you call a Stored Procedure from JDBC?

A stored procedure is invoked by using CallableStatement object. It also requires an open Connection object, as in the case of Statement and Prepared Statement objects. This object contains the call to the stored procedure, but not the procedure itself. The following code snippet illustrates the invocation of a stored procedure using CallableStatement. The part which is enclosed in curly braces is the syntax for stored procedures.

CallableStatement clblStmt = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rltSet = clblStmt.executeQuery();

What is cold backup, hot backup, warm backup recovery?

Cold backup is a recovery technique, in which the files must be backed up before the database is restarted at the same time.

Hot backup is a recovery technique for each table space and is taken at the time of accessing and running the database.

Warm back is a recovery technique where all the tables are locked and users cannot access at the time of backing up data. The backup operations can be postponed in case the users are accessing the database.

What is the advantage of using PreparedStatement?

PreparedStatement objects are used to execute repetitive SQL statements. Compared to Statement object execution, Prepared Statement object creation is faster. The reason is the object is pre compiled, by eliminating the compilation task by DBMS. The PreparedStatement object can be used by just replacing the parameters.

What is a “dirty read”?

In typical database transactions, one transaction reads changes the value while the other 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.

Different types of Transaction Isolation Levels.

Transaction Isolation levels specifies the visible data to statements within a single transaction. These levels directly impact the various levels of the concurrent access.

Transaction isolation levels

There are five levels of transaction isolation which are as follows:

JDBC_TRANSACTION_NONE: A special constant that JDBC driver does not support transactions.

JDBC_TRANSACTION_READ_UNCOMMITTED: Allows looking after the uncommitted changes to the database.

JDBC_TRANSACTION_READ_COMMITTED: The transactions are externally invisible until the transaction is committed. The dirty reads prevention is possible with this level.

JDBC_TRANSACTION_REPEATABLE_READ: The readable rows retain locks by which another transaction can not change them until the transaction completes. It disallows dirty reads and non repeatable reads.

JDBC_TRANSACTION_SERIALIZABLE: The tables are locked for the transaction which makes the WHERE conditions not to be changed by other transactions. This process prevents all database anomalies.

To change the transaction levels, the setTransactionIsolation() method is utilized.

1

2

3

4

5

6

7

8