JDBC Interview Questions with Answers

What is JDBC? Explain the types of JDBC drivers?

JDBC(Java Database Connectivity) is a java api which is used to connect and execute the query to the database. This api uses JDBC drivers to connect to the database. There are 4 types of JDBC drivers :

JDBC-ODBC bridge driver uses ODBC driver to connect to the database. JDBC method calls are converted to ODBC function calls by the JDBC-ODBC bridge driver. Because of the thin driver, this is now discouraged. It is easy to use and can be easily connected to any database.
Native-API driver uses the client-side libraries of the database. The driver translates JDBC method calls to database API native calls. It is not totally written in Java. It has a greater performance than the JDBC-ODBC bridge driver. However, the native driver must be installed on each client machine.
Network Protocol driver uses middleware that converts JDBC calls into the vendor-specific database protocol, either directly or indirectly. It’s entirely written in Java. The application server, which can do numerous activities such as auditing, load balancing, logging, and so on, eliminates the need for a client-side library.
Thin driver converts JDBC calls directly into the vendor-specific database. It’s entirely written in Java. Its performance is better than all other drivers however these drivers depend upon the database.

What are the steps to connect to the database in Java?

steps for data connectivity in java :

import the packages to make the JDBC API classes immediately available to the application program.

import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

Load and Register the JDBC Driver for establishing a communication between the JDBC program and the Oracle database. The forName() method of the java.lang.Class class can be used to load and register the JDBC drive.

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

Establish the Connection, this is done by using the getConnection() method of the DriverManager class. A call to this method creates an object instance of the java.sql.Connection class. The getConnection() requires three input parameters, namely, a connect string, a username, and a password.
Connection conn = DriverManager.getConnection(URL, username, password); Connection conn = DriverManager.getConnection(URL);

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");

Creating the Statement is to instantiate objects that run the query against the database connected to. This is done by the createStatement() method of the conn Connection object created above. A call to this method creates an object instance of the Statement class.

Statement stmt = conn.createStatement();

Executing the Queries is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

ResultSet rset = stmt.executeQuery
      ("SELECT empno, ename, sal, deptno FROM emp ORDER BY ename");

Processing the Results of a Database Once the query has been executed, there are two steps to be carried out, (i) Processing the output resultset to fetch the rows, (ii) Retrieving the column values of the current row.
The first step is done using the next() method of the ResultSet object. A call to next() is executed in a loop to fetch the rows one row at a time, with each call to next() advancing the control to the next available row. The next() method returns the Boolean value true while rows are still available for fetching and returns false when all the rows have been fetched.
The second step is done by using the getXXX() methods of the JDBC rset object. Here getXXX() corresponds to the getInt(), getString() etc with XXX being replaced by a Java datatype.

String str;
while (rset.next()){
    str = rset.getInt(1)+ " "+ rset.getString(2)+ " "+rset.getFloat(3)+ " "rset.getInt(4)+ "\n";
}
byte buf[] = str.getBytes();
OutputStream fp = new FileOutputStream("query1.lst");
fp.write(buf);
fp.close();

Closing the Connection is the last step. This is done by a call to the close() method of the Connection class.

conn.close()

What is JDBC Statement? Explain the different types of JDBC statements?

Statements are used to send SQL commands to the database and receive data from the database. execute(), executeUpdate(), executeQuery() are some methods which helps you to interact with the database.

There are three types of JDBC Statements :

Statement is used when you are using static SQL statement at runtime. To create a Statement object :

Statement stmt = null;
try {
   stmt = conn.createStatement( );
   # code
}
catch (SQLException e) {
   # code
}
finally {
   # code
}

Once you’ve created a Statement object, you can then use it to execute an SQL statement with one of its three execute methods i.e., execute(), executeUpdate(), executeQuery().

PreparedStatement is used when you plan to execute prepared statement many times. The PreparedStatement interface extends the Statement interface, which gives you added functionality with a couple of advantages over a generic Statement object.

PreparedStatement p_stmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   p_stmt = conn.prepareStatement(SQL);
   # code
}
catch (SQLException e) {
   # code
}
finally {
   # code
}

All of the Statement object’s methods for interacting with the database execute(), executeQuery(), and executeUpdate() also work with the PreparedStatement object. However, the methods are modified to use SQL statements that can input the parameters.

CallableStatement is used when you want to access the database stored procedures. Just as a Connection object creates the Statement and PreparedStatement objects, it also creates the CallableStatement object, which would be used to execute a call to a database stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END $$

DELIMITER ;

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

What is an ACID property?

Atomicity If all the queries have executed successfully, then the data will be committed else won’t commit.

Consistency Data should be consistent after any transaction.

Isolation Each transaction should be isolated.

Durability If the transaction is committed once, it should be available always (if no changes have happened)

What are the differences between execute(), executeQuery(), executeUpdate()?

execute()	executeQuery()	executeUpdate()
This method can be used for any SQL statements.	This method is used to execute the SQL statements which retrieve some data from database.	This is used to execute SQL statements which updates or modify data.
This method returns boolean type value.	This method returns resultSet object.	This method returns an integer value.
The return value of true indicates that the ResultSet is returned which can later be extracted and false indicates that the integer or void value is returned.	The ResultSet object contains the data retrieved by the select statement.	The integer value represents the number of records affected where 0 indicates that query returns nothing.
This method is used to execute select and non-select queries.	This method is used to execute select query.	This method is used to execute non-select query.
`Ex.` All SQL statements	`Ex.` select	`Ex.` insert, update, delete, alter

Write the steps to create and execute stored procedures?

Create the procedure in the database

create or replace procedure f_proj1(x in number, y out number)as
begin
y := x*x;
end;
/

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

Establish the connection

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");

Create Callable statement object

CallableStatement cst = conn.prepareCall("{CALL f_proj1(?,?)}");

cst.registerOutParameter(2,Types.INTEGER);

Here, 2 is the parameter index and Types.INTEGER is the sql type

Set values to IN parameter

cst.setInt(1,20)

Call SQL procedure

cst.execute();

Gather result from OUT parameters

int result = cst.get(2);
System.out.println("Result is " + result);

Close JDBC object

cst.close();
conn.close();

What is JDBC DriverManager Class?

The DriverManager class serves as a user-to-driver interface. It maintains track of the available drivers and manages the process of connecting a database to the proper driver. The DriverManager class keeps track of which Driver classes have registered themselves by using the DriverManager.registerDriver() function.

What is ResultSet and what are the types of ResultSet?

The output data from a SQL query is stored using the ResultSet interface. The cursor point at the result data is maintained by the ResultSet object. As a default, the cursor points before the first row of the result data. The data in the resultset objects can also be traversed.

There are three types of ResultSet :

TYPE_FORWARD_ONLY : It is the default option. The cursor will move from start to end.
TYPE_SCROLL_INSENSITIVE : In this type, the cursor will move in both forward and backward directions. Dataset has the data when the SQL query returns the data.
TYPE_SCROLL_SENSITIVE : It is the same as TYPE_SCROLL_INSENSITIVE, the difference is that it will have the updated data while iterating the resultset object.

What is batch processing and how to perform batch processing in JDBC?

Batch Processing allows you to compile a set of similar SQL statements into a single call to the database. When you submit many SQL statements to the database at the same time, you reduce communication cost and improve performance.

The batch processing in JDBC requires the following steps :

Load the driver class
Create Connection
Create Statement
Add query in the batch
Execute the Batch
Close Connection

import java.sql.*;
import java.util.*;
public class BatchTest{
   public static void main(String[] args) throws Exception{
      Scanner sc = new Scanner(System.in);
      System.out.println("Enter the source account number: ");
      int srcno = sc.nextInt();
      System.out.println("Enter the destination account number: ");
      int destno = sc.nextInt();
      System.out.println("Enter the amount to transfer: ");
      int amt = sc.nextInt();

      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");

      Statement stmt = conn.createStatement();

      //disable autocommit
      conn.setAutoCommit(false);       

      //Withdrawal operation
      stmt.addBatch("update account set balance=balance-"+amt+" where acid="+srcno);
      //Deposit operation
      stmt.addBatch("update account set balance=balance+"+amt+" where acid="+destno);

      //perform transaction management
      int res[] = stmt.executeBatch();
      boolean flag = false;
      for(int i=0;i<=res.length;++i>){
         if(res[i]==0){
            flag=true;
            break;
         }
      }
      if(flag == true){
         conn.rollback();
         System.out.println("Transaction is rollback, Amount is not transferred");
      }
      else{
         conn.commit();
         System.out.println("Transaction is committed Amount is transferred successfully");
      }
      stmt.close();
      conn.close();
   }
}

What is the use of commit() and rollback() methods?

commit() method in java is to commit the data. Once the SQL execution is done, we can call the commit method.

Syntax : connectionobject.commit();

rollback() method in java is to rollback the data. Rollback means to undo the changes. If any of the SQL statements are failed, we can call the rollback method to undo the changes.

Syntax : connectionobject.rollback();

Java Interview