50 JDBC Interview Question

JDBC Interview Questions

1.What is the JDBC?

Ans. Java Database Connectivity (JDBC) is a standard Java API to interact with relational databases form Java. JDBC has set of classes and interfaces which can use from Java application and talk to database without learning RDBMS details and using Database Specific JDBC Drivers.

2. What are the new features added to JDBC 4.0?

Ans. The major features added in JDBC 4.0 include :

• Auto-loading of JDBC driver class
• Connection management enhancements
• Support for RowId SQL type
• DataSet implementation of SQL using Annotations
• SQL exception handling enhancements
• SQL XML support

3. Explain Basic Steps in writing a Java program using JDBC?

Ans. JDBC makes the interaction with RDBMS simple and intuitive. When a Java application needs to access database :

• Load the RDBMS specific JDBC driver because this driver actually communicates with the database (Incase of JDBC 4.0 this is automatically loaded).
• Open the connection to database which is then used to send SQL statements and get results back.
• Create JDBC Statement object. This object contains SQL query.
• Execute statement which returns resultset(s). ResultSet contains the tuples of database table as a result of SQL query.
• Process the result set.
• Close the connection.

4. Exaplain the JDBC Architecture.

Ans. The JDBC Architecture consists of two layers:

• The JDBC API, which provides the application-to-JDBC Manager connection.
• The JDBC Driver API, which supports the JDBC Manager-to-Driver Connection.

The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases. The location of the driver manager with respect to the JDBC drivers and the Java application is shown in Figure 1.

Figure 1: JDBC Architecture

5. What are the main components of JDBC ?

Ans. The life cycle of a servlet consists of the following phases:

• DriverManager: Manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication subprotocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection.

• Driver: The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not call it explicitly.

• Connection : Interface with all methods for contacting a database.The connection object represents communication context, i.e., all communication with database is through connection object only.

• Statement : Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.

• ResultSet: The ResultSet represents set of rows retrieved due to query execution.

6. How the JDBC application works?

Ans. A JDBC application can be logically divided into two layers:

1. Driver layer

2. Application layer

• Driver layer consists of DriverManager class and the available JDBC drivers.
• The application begins with requesting the DriverManager for the connection.
• An appropriate driver is choosen and is used for establishing the connection. This connection is given to the application which falls under the application layer.
• The application uses this connection to create Statement kind of objects, through which SQL commands are sent to backend and obtain the results.

7. How do I load a database driver with JDBC 4.0 / Java 6?

Ans.Provided the JAR file containing the driver is properly configured, just place the JAR file in the classpath. Java developers NO longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver.The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.

8. What is JDBC Driver interface?

Ans. The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.

9. What does the connection object represents?

Ans. The connection object represents communication context, i.e., all communication with database is through connection object only.

10. What is Statement ?

Ans. Statement acts like a vehicle through which SQL commands can be sent. Through the connection object we create statement kind of objects.
Through the connection object we create statement kind of objects.

               Statement stmt  = conn.createStatement();

This method returns object which implements statement interface.

11. What is PreparedStatement?

Ans. 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("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");

pstmt.setBigDecimal(1, 153833.00);

pstmt.setInt(2, 110592);

Here: conn is an instance of the Connection class and "?" represents parameters.These parameters must be specified before execution.

12. What is the difference between a Statement and a PreparedStatement?

Statement	PreparedStatement
A standard Statement is used to create a Java representation of a literal SQL statement and execute it on the database.	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.
Statement has to verify its metadata against the database every time.	While a prepared statement has to verify its metadata against the database only once.
If you want to execute the SQL statement once go for STATEMENT	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

13. What are callable statements ?

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

14. How to call a stored procedure from JDBC ?

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

15. What are types of JDBC drivers?

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

Note: Type 4 JDBC driver is most preferred kind of approach in JDBC.

16. Which type of JDBC driver is the fastest one?

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

17. Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

Ans. No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.

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

19. What are the standard isolation levels defined by JDBC?

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

Any given database may not support all of these levels.

20. What is resultset ?

Ans. The ResultSet represents set of rows retrieved due to query execution.

                               ResultSet rs = stmt.executeQuery(sqlQuery);

21. What are the types of resultsets?

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

22. What’s the difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE?

TYPE_SCROLL_INSENSITIVE	TYPE_SCROLL_SENSITIVE
An insensitive  resultset is like the snapshot of the data in the database when query was executed.	A sensitive resultset does NOT represent a snapshot of data, rather it contains points to those rows which satisfy the query condition.
After we get the resultset the changes made to data are not visible through the resultset, and hence they are known as insensitive.	After we obtain the resultset if the data is modified then such modifications are visible through resultset.
Performance not effected with insensitive.	Since a trip is made for every ‘get’ operation, the performance drastically get affected.

22. What is rowset?

Ans. A RowSet is an object that encapsulates a set of rows from either Java Database Connectivity (JDBC) result sets or tabular data sources like a file or spreadsheet. RowSets support component-based development models like JavaBeans, with a standard set of properties and an event notification mechanism.

24. What are the different types  of RowSet ?

Ans. There are two types of RowSet are there. They are:

• Connected - A connected RowSet object connects to the database once and remains connected until the application terminates.
• Disconnected - A disconnected RowSet object connects to the database, executes a query to retrieve the data from the database and then closes the connection. A program may change the data in a disconnected RowSet while it is disconnected. Modified data can be updated in the database after a disconnected RowSet reestablishes the connection with the database.

25. What is the need of BatchUpdates?

Ans. The BatchUpdates feature allows us to group SQL statements together and send to database server in one single trip.

26. What is a DataSource?

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

27. What are the advantages of DataSource?

Ans. The few advantages of data source are :

• An application does not need to hardcode driver information, as it does with the DriverManager.
• The DataSource 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.

28. What is connection pooling? what is the main advantage of using connection pooling?

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

29. how can we store and retrieve images from the database?

Ans. By using PreparedStaement interface, we can store and retrieve images.

import java.sql.*;

import java.io.*;

public class InsertImage {

public static void main(String[] args) {

try{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con=DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

                                   

PreparedStatement ps=con.prepareStatement("insert into imgtable values(?,?)");

                       

FileInputStream fin=new FileInputStream("d:\\g.jpg");

                       

ps.setString(1,"sonoo");

ps.setBinaryStream(2,fin,fin.available());

int i=ps.executeUpdate();

System.out.println(i+" records affected");

                       

con.close();

                                   

}catch (Exception e) {e.printStackTrace();}

}

}

30. What is JDBC Driver interface?

Ans. The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendors driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.

31. What Class.forName will do while loading drivers?

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

32. How can you retrieve data from the ResultSet?

Ans. First JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs.

E.g.

ResultSet rs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);

Second:

String s = rs.getString(”COF_NAME”);

The method getString is invoked on the ResultSet object rs , so getString will retrieve (get) the value stored in the column COF_NAME in the current row of rs.

33. How can you use PreparedStatement?

Ans. This special type of statement is derived from the more general class, Statement. If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement ’s SQL statement without having to compile it first.

E.g.

PreparedStatement updateSales = con.prepareStatement(”UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?”);

34. How to call a Stored Procedure from JDBC?

Ans. 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;

E.g.

CallableStatement cs = con.prepareCall(”{call SHOW_SUPPLIERS}”);

ResultSet rs = cs.executeQuery();

35. How to Retrieve Warnings?

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

E.g.

SQLWarning warning = stmt.getWarnings();

    if (warning != null) {

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

          warning = warning.getNextWarning();

        }

    }

36. How to Make Updates to Updatable Result Sets?

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

E.g.

Connection con = DriverManager.getConnection(”jdbc:mySubprotocol:mySubName”);

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

ResultSet.CONCUR_UPDATABLE);

ResultSet uprs = (”SELECT COF_NAME, PRICE FROM COFFEES”);

37. What is new in JDBC 2.0?

Ans. With the JDBC 2.0 API, you will be able to do the following:

* Scroll forward and backward in a result set or move to a specific row (TYPE_SCROLL_SENSITIVE,previous(), last(), absolute(), relative(), etc.)

* Make updates to database tables using methods in the Java programming language instead of using SQL commands.(updateRow(), insertRow(), deleteRow(), etc.)

* Send multiple SQL statements to the database as a unit, or batch (addBatch(), executeBatch())

* Use the new SQL3 datatypes as column values like Blob, Clob, Array, Struct, Ref.

38. How to move the cursor in scrollable resultsets?(new feature in JDBC 2.0)

Ans.

a. create a scrollable ResultSet object.

    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

         ResultSet.CONCUR_READ_ONLY);

    ResultSet srs = stmt.executeQuery("SELECT COLUMN_1,

 COLUMN_2 FROM TABLE_NAME");

b. Use a built in methods like afterLast(), previous(), beforeFirst(), etc. to scroll the resultset.

   srs.afterLast();

    while (srs.previous()) {

        String name = srs.getString("COLUMN_1");

        float salary = srs.getFloat("COLUMN_2");

        //...

c. to find a specific row, use absolute(), relative() methods.

       srs.absolute(4); // cursor is on the fourth row

       int rowNum = srs.getRow(); // rowNum should be 4

       srs.relative(-3);

       int rowNum = srs.getRow(); // rowNum should be 1

       srs.relative(2);

       int rowNum = srs.getRow(); // rowNum should be 3

d. use isFirst(), isLast(), isBeforeFirst(), isAfterLast() methods to check boundary status.

39.  How to update a resultset programmatically? (new feature in JDBC 2.0)

Ans.

a. create a scrollable and updatable ResultSet object.

Statement stmt = con.createStatement

         (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

   ResultSet uprs = stmt.executeQuery("SELECT COLUMN_1,

         COLUMN_2 FROM TABLE_NAME");

b. move the cursor to the specific position and use related method to update data and then, call updateRow() method.

uprs.last();

   uprs.updateFloat("COLUMN_2", 25.55);//update last row's data

   uprs.updateRow();//don't miss this method, otherwise,

    // the data will be lost.

40. How to insert and delete a row programmatically? (new feature in JDBC 2.0)

Make sure the resultset is updatable.

1. move the cursor to the specific position.

uprs.moveToCurrentRow();

2. set value for each column.

   uprs.moveToInsertRow();//to set up for insert

   uprs.updateString("col1" "strvalue");

   uprs.updateInt("col2", 5);

   ...

3. call insertRow() method to finish the row insert process.

uprs.insertRow();

To delete a row: move to the specific position and call deleteRow() method:

   uprs.absolute(5);

   uprs.deleteRow();//delete row 5

To see the changes call refreshRow();

uprs.refreshRow();

41. Variables Available in CallableStatement ?
Ans. Three types of parameters exist: IN, OUT, and INOUT. The PreparedStatement object only uses the IN parameter. The CallableStatement object can use all three.

Here are the definitions of each:

Parameter	Description
IN	A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
OUT	A parameter whose value is supplied by the SQL statement it returns. You retrieve values from theOUT parameters with the getXXX() methods.
INOUT	A parameter that provides both input and output values. You bind variables with the setXXX() methods and retrieve values with the getXXX() methods.