Q. What is the JDBC API?
A. The JDBC API provides a standard database-independent interface to interact with any tabular data source. Mostly, it is used to interact with a relational database management system (RDBMs). However, using the JDBC API, it is possible to interact with any tabular data source. So with the use of JDBC API you can connect to a database, query the data, and update the data. It also lets you execute SQL stored procedures in a database using a database independent syntax.

Q. What is the main purpose of using a database?
A. The main purpose of using a database is to manage business data. To manage data every database provides  the following three things :
• A standard SQL syntax.
• An extension to the standard SQL syntax called a proprietary SQL  syntax.
• A proprietary programming language.

For example, Oracle databases use PL/SQL as a programming language  to write stored procedures, functions, and triggers. Microsoft SQL Server uses Transact-SQL (T-SQL) as the programming language to write stored procedures, functions, and triggers.As it relieves you to learn a different syntax to process a cursor in different databases. It requires you to write a query(a SELECT statement) using a standard SQL syntax. It provides Java APIs to process the result set of that query in a database-independent manner.

Q. What are the new features added to JDBC 4.1?
A. The JDBC 4.1 API introduces new material and changes in the following areas:
■ Support for try-with-resource statements
Connection, ResultSet and Statement now extend java.lang.AutoCloseable.
■ Enhanced Timestamp.valueOf and Date.valueOf.
For a month and day the leading zero may now be omitted.
■ Mapping Java Object to JDBC Types
Added support to map  java.util.Calendar and java.util.Date   to JDBC
TIMESTAMP. Support was also added to map java.lang.BigInteger to JDBC BIGINT.
■ Mapping performed by setObject and setNull between
Java Object Types and Target JDBC Types
Allow conversion of java.util.Date and java.util.Calendar to
VARCHAR, LONGVARCHAR, CHAR, DATE, TIME, and TIMESTAMP.

■ SQLException Enhancements
Added support for the Permission target names callAbort, setNetworkTimeout,setSyncFactory,Clarified that SQLDataException, SQLIntegrityConstraintViolationException,
InvalidAuthorizationSpecException,
SQLNonTransientConnectionException,
SQLSyntaxErrorException, SQLTransactionRollbackException,and SQLTransientConnectionException can be thrown for vendor
specific reasons.
■ New Escape Functions
Added the Limited Returned Rows Escape.
■ New Scalar Funtions
Enhanced the String functions CHARACTER_LENGTH,CHAR_LENGTH, LENGTH,SUBSTRING, and POSITION to provide support for OCTETS when a Database supports SQL 2003 Feature, T061, “UCS Support”.

■ JDBC API changes
The following changes were made to existing JDBC interfaces..
■ Connection
Added the methods abort, getSchema, getNetworkTimeout,
setNetworkTimeout, setSchema.

■ CallableStatement
Overloaded the getObject methods.
■ Driver and CommonDataSource
Added the method getParentLogger.
■ DatabaseMetaData
Added the methods getPseudoColumns,
generatedKeyAlwaysReturned.
Clarified the, getColumns getProcedureColumns methods .
■ PreparedStatement
Clarified the methods executeQuery and executeUpdate.
■ ResultSet
Overloaded the methods getObject.
Clarified the absolute method.
■ Statement
Added the methods  isCloseOnCompletion and closeOnCompletion.
Clarified the  execute, addBatch, executeQuery, executeUpdate,
setQueryTimeout methods.
■ Timestamp
Clarified the method compareTo.

Q. How many packages JDBC API consists of?
A. The JDBC API consists of two packages:

1. java.sql

2. javax.sql.

Both packages are added in the Java SE and Java EE platforms.

Q. What are the approaches for registering the driver?
A. There are two approaches for registering the driver-
1. Dynamically Loads Driver -Class.forName():  This method dynamically loads the driver’s class file into memory, that automatically registers it. This  is a preferable method because it allows you to make the driver registration configurable and portable.

2. By Using static method DriverManager.registerDriver(): As part of its initialization, the DriverManager class attempt to load the driver classes referenced in the “jdbc.drivers” system property. This allows a user to customize the JDBC Drivers used by their applications.

Q. Name the common JDBC API components?
A.  JDBC API consists of the following interfaces and classes- DriverManager, Driver,  Statement, Connection, ResultSet, SQLException.

Q. When running out of cursors problem arises?
A. Running out of cursors or running out of memory problem arises when database connection is not closed explicitly.So all the valuable resources close in a try{} and finally{} block. The finally{} block is always executed even if there is an exception thrown from the catch {} block and the resources like connections and statements should be closed in a finally {} block.

For example-


public void executeSQL() throws SQLException{
try{
   Connection con = DriverManager.getConnection(........);
   .....
   Statement stmt = con.createStatement();
   ....
   ResultSet rs = stmt.executeQuery("SELECT * from Table");
   .....
   }
finally{

       try {
            if(rs != null) rs.close();
            if(stmt != null) stmt.close();
            if(con != null) con.close();
           }
           catch(Exception e){}
       }
}

Note: The finally clause is called before the exception is thrown to the method if an exception is thrown at line 12 .

Q. Name the fastest JDBC driver along with their function?
A. JDBC Net pure Java driver(Type 4) is the fastest driver and their function is to convert the JDBC calls into vendor specific protocol calls as it directly interacts with the database.

Q. List the standard isolation levels defined by JDBC?
A. The standard isolation levels defined by JDBC are-

TRANSACTION_NONE

TRANSACTION_READ_COMMITTED

TRANSACTION_READ_UNCOMMITTED

TRANSACTION_REPEATABLE_READ

TRANSACTION_SERIALIZABLE

Q. Describe briefly ‘A Transaction Isolation Level’?
A. In a multi-user database, you will often come across the following two terms:
• Data concurrency
• Data consistency
Data concurrency refers to the ability of multiple users to use the same data concurrently. Data consistency refers to the accuracy of the data that is maintained when multiple users are manipulating the data concurrently. As the data concurrency increases (i.e. more users work on the same data), care must be taken to maintain a desired level of data consistency. A database maintains data consistency using locks and by isolating one transaction from another.Let’s look at three phenomena where data consistency may be compromised in a multi-user environment where multipleconcurrent transactions are supported.

Dirty Read
In a dirty read, a transaction reads uncommitted data from another transaction.Consider the following sequence of steps, which results in inconsistent data because of a dirty read:
• Transaction A inserts a new row in a table and it has not committed it yet.
• Transaction B reads the uncommitted row inserted by the transaction A.
• Transaction A rollbacks the changes.
• At this point, transaction B is left with data for a row that does not exist.

Non-Repeatable Read
In a non-repeatable read, when a transaction re-reads the data, it finds that the data has been modified by another transaction that has been already committed. Consider the following sequence of steps, which results in inconsistent data because of a non-repeatable read:
• Transaction A reads a row.
• Transaction B modifies or deletes the same row and commits the changes.
• Transaction A re-reads the same row and finds that the row has been modified or deleted.


// Get a Connection object

Connection conn = get a connection object...;

// Set the transaction isolation level 

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

You can use the getTransactionIsolation() method of the Connection interface to get the current setting for the transaction isolation level for the connection. The default transaction isolation level is JDBC driver dependent.You can also use the following three methods of the DatabaseMetaData interface to get more insight about the transaction isolation levels supported by a DBMS. The method names are self-explanatory.

• int getDefaultTransactionIsolation() throws SQLException

• boolean supportsTransactions() throws SQLException

• boolean supportsTransactionIsolationLevel(int level) throwsSQLException

The Connection interface defines a TRANSACTION_NONE constant to indicate that a JDBC driver does not support transactions and it is not a JDBC-compliant driver.This constant is not used with the  set TransactionIsolation()method. The getTransactionIsolation() method may return this constant. You can change the transaction isolation for a Connection object any time. However, the effect of changing the transaction isolation of a connection is JDBC driver-dependent if it is changed when a transaction is in progress.