JDBC Best Interview Questions

1.What is the JDBC?

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?

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?

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.

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 ?

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?

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.

Figure 2: JDBC Application

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

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?

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?

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

10.What is Statement ?

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?

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 ?

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

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

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

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

16: What are the main steps in java to make JDBC connectivity?

Answer : Another beginner level JDBC Interview question, mostly asked on telephonic interviews. Here are main steps to connect to database.

·          Load the Driver: First step is to load the database specific driver which communicates with database.

·          Make Connection: Next step is get connection from the database using connection object, which is used to send SQL statement also and get result back from the database.

·          Get Statement object: From connection object we can get statement object which is used to query the database

·          Execute the Query:Using statement object we execute the SQL or database query and get result set from the query.

·          Close the connection:After getting resultset and all required operation performed the last step should be closing the database connection.

17: What is the mean of “dirty read“ in database?

Answer : This kind of JDBC interview question is asked on 2 to 4 years experience Java programmer, they are expected to familiar with database transaction and isolation level etc. As the name it self convey the meaning of dirty read “read the value which may or may not be correct”. in database when one transaction is executing and changing some field value same time some another transaction comes and read the change field value before first transaction commit or rollback the value ,which cause invalid value for that field, this scenario is known as dirty read.

18: What is 2 phase commit?

Answer : This is one of the most popular JDBC Interview question and asked at advanced level, mostly to senior Java developers on J2EE interviews. Two phase commit is used in distributed environment where multiple process take part in distributed transaction process. In simple word we can understand like if any transaction is executing and it will effect multiple database then two phase commit will be used to make all database synchronized with each other.

In two phase commit, commit or rollback is done by two phases:

1.       Commit request phase: in this phase main process or coordinator process take vote of all other process that they are complete their process successfully and ready to commit if all the votes are “yes” then they go ahead for next phase. And if “No “then rollback is performed.

2.       Commit phase: according to vote if all the votes are yes then commit is done.

Similarly when any transaction changes multiple database after execution of transaction it will issue pre commit  command on each database and all database send acknowledgement and according to acknowledgement if all are positive transaction will issue the commit command otherwise rollback is done .

19: What are different types of Statement?

Answer :  This is another classical JDBC interview question. Variants are Difference between Statement, PreparedStatemetn and CallableStatement in Java. Statement object is used to send SQL query to database and get result from database, and we get statement object from connection object.

There are three types of statement:

1. Statement: it’s a commonly used for getting data from database useful when we are using static SQL statement at runtime. it will not accept any parameter.

              Statement   stmt = conn.createStatement( );

      ResultSet rs = stmt.executeQuery();

2. PreparedStatement: when we are using same SQL statement multiple time its is useful and it will accept parameter at runtime.

   

              String SQL = "Update stock SET limit = ? WHERE stockType = ?";

      PreparedStatement  pstmt = conn.prepareStatement(SQL);

      ResultSet rs = pstmt.executeQuery();

3. Callable Statement: when we want to access stored procedures then callable statement are useful and they also accept runtime parameter. It is called like this

           

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

      ResultSet rs = cs.executeQuery();

20: How cursor works in scrollable result set?

Answer : Another  tough JDBC Interview question, not many Java programmer knows about using Cursor in Java.

in JDBC 2.0 API new feature is added to move cursor in resultset backward forward and also in a particular row .

There are three constant define in result set by which we can move cursor.

·          TYPE_FORWARD_ONLY: creates a nonscrollable result set, that is, one in which the cursor moves only forward

·          TYPE_SCROLL_INSENSITIVE : a scrollable result set does not reflects changes that are made to it while it is open

·          TYPE_SCROLL_SENSITIVE: a scrollable result set  reflects changes that are made to it while it is open

21:  What is connection pooling?

Answer : This is also one of the most popular question asked during JDBC Interviews. Connection pooling is the mechanism by which we reuse the recourse like connection objects  which are  needed to make connection with database .In this mechanism client are not required every time make new connection and then interact with database instead of that connection objects are stored in connection pool and client will get it from there. so it’s a best way to share a server resources among the client and enhance the application performance. If you use Spring framework, then you can also refer 

22: What do you mean by cold backup, hot backup?

Answer : This question is not directly related to JDBC but some time asked during JDBC interviews. Cold back is the backup techniques in which backup of files are taken before the database restarted. In hot backup backup of files and table is taken at the same time when database is running. A warm is a recovery technique where all the tables are locked and users cannot access at the time of backing up data.

23: What are the locking system in JDBC

Answer : One more tough JDBC question to understand and prepare. There are 2 types of locking in JDBC by which we can handle multiple user issue using the record. if two user are reading the same record then there is no issue but what if users are updating the record , in this case changes done by first user is gone by second user if he also update the same record .so we need some type of locking so no lost update.

Optimistic Locking: optimistic locking lock the record only when update take place. Optimistic locking does not use exclusive locks when reading

Pessimistic locking: in this record are locked as it selects the row to update

24: Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

Answer: No, we can open only one statement object when using JDBC-ODBC Bridge.

That’s all on this list of 10 JDBC Interview question with answer. As I said JDBC API and there concepts are integral part of any Java interview and there is always atleast one question from JDBC. Since most application uses datbase in backend, JDBC becomes critical for any Java developer.

 25:What are the steps involved in establishing a JDBC connection?
This action involves two steps: loading the JDBC driver and making the connection. 

26:How can you load the drivers? 

       Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for      example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:

1. Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);

   Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ, you would load the driver with the following line of code:
   

   Class.forName(”jdbc.DriverXYZ”);



2. 27 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.
3. 28 How can you make the connection? To establish a connection you need to have the appropriate driver connect to the DBMS.
   The following line of code illustrates the general idea:

   String url = “jdbc:odbc:Fred”;
   Connection con = DriverManager.getConnection(url, “Fernanda”, “J8?);

4. 29 How can you create JDBC statements and what are they?
   A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object

   Statement stmt = con.createStatement();

5. 30 How can you retrieve data from the ResultSet?
   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.

   ResultSet rs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);
   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.
6. What are the different types of Statements?
   Regular statement (use createStatement method), prepared statement (use prepareStatement method) and callable statement (use prepareCall)
7. How can you use PreparedStatement? This special type of statement is derived from class Statement.If you need a
   Statement object to execute many times, it will normally make sense 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.

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

8. 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 COFFEES SET 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);
   

9. How do you call a stored procedure from JDBC?
   The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open
   Connection object. A CallableStatement object contains a call to a stored procedure.

    CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
    ResultSet rs = cs.executeQuery();
   

10. 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();
     }
    }
    

11. How can you move the cursor in scrollable result sets?
    One of the new features in the JDBC 2.0 API is the ability to move a result set’s cursor backward as well as forward. There are also methods that let you move the cursor to a particular row and check the position of the cursor.

    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet srs = stmt.executeQuery(”SELECT COF_NAME, PRICE FROM COFFEES”);

    The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE , and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE. The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int , the compiler will not complain if you switch the order. Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
    
12. 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);
    }
    

13. 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");
    

Socializer Widget

SOCIALIZE IT →

Tweet

FOLLOW US →

SHARE IT →