JDBC Interview Questions and Answers

The significances are given below:

• JDBC is the acronym stands for Java Database Connectivity.
• Java Database Connectivity (JDBC) is a standard Java API.
• Its 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.

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.

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.

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.

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.

A JDBC application may be divided into two layers:

1. Driver layer
2. 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.

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.

• 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.

• 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.

• 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.

• 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);

• 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, it’ll go to the PreparedStatement.

• 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 auto-commit 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);

• 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();

• 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();

}

}

The 4 types of JDBC Drivers are:

• Pure Java Driver JDBC Net
• Bridge Driver JDBC-ODBC
• Network protocol Driver
• Partly Java Driver Native API

• The new features added in the JDBC 2.0 API are able to move a resultset’s 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 Result Set API for indicating the kind of the Result Set object. The constants are:

– TYPE_FORWARD_ONLY

– TYPE_SCROLL_INSENSITIVE

– TYPE_SCROLL_SENSITIVE.

The Result Set constants for specifying whether a result set is read-only or updatable are:

– CONCUR_READ_ONLY

– CONCUR_UPDATABLE.

• 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”);

• 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.

• 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?);