JDBC Interview Questions and Answers for 2023 Programming

This is one of the most frequently asked JDBC interview questions for freshers in recent times.

To interact with any of the database JDBC uses JDBC drivers. JDBC drivers are the software components which enable Java applications to interact with Database.  

There are mainly 4 types of JDBC drivers: 

1. JDBC-ODBC bridge driver: The JDBC-ODBC Bridge driver uses ODBC driver to connect to the database. The main objective of JDBC-ODBC bridge driver is to convert JDBC method calls to ODBC function calls. And this is no longer used now because it is a thin driver. Also, it is easy to use and can be connected to database easily. 
2. Native API driver: The Native API driver internally uses the client-side libraries of the database. This driver converts JDBC method calls into Native API calls to the database. To use this, the installation must be done in the local system. Also, the Native API driver is better than JDBC-ODBC bridge driver. 
3. Network Protocol driver: The network protocol driver uses application server or middleware servers, that convert JDBC calls directly or indirectly into client specific database protocols. Native protocol driver is written completely in Java. Also, there is no requirement for client-side libraries because the application server itself perform tasks like load balancing, auditing, logging etc., 
4. Thin driver: The Thin driver converts JDBC calls directly into client or vendor specific database protocols. So, it is called Thin Driver. It is also written completely in Java. The performance of Thin Driver is better than all the other drivers.

This is one of the most frequently asked JDBC interview questions for freshers in recent times.

The code to connect Java and MySQL database is as follows.

try { 
Class.forName ("com.mysql.jdbc.Driver"); 
Connection con=DriverManager.getConnection ("jdbc: mysql://localhost:3306/dbanme", "username", "password"); 
Statement st=con.createstatement (); 
Resultset rs=st.executeQuery ("select * from user"); 
rs.next (); 
} catch (ClassNotFoundException e) { 
System.err.println("ClassNotFoundException in get Connection," + e.getMessage());  
}catch (SQLException e) { 
System.err.println ("SQLException in getConnection," + e.getMessage ()); 
} 

Code to connect Java and Oracle database.

try { 
Class.forName ("oracle.jdbc.driver.OracleDriver"); 
Connection con= DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:dbname", "username", "password"); 
Statement st=con.createstatement(); 
Resultset rs=st.executeQuery("select * from user"); 
rs.next(); 
} catch (ClassNotFoundException e) { 
System.err.println("ClassNotFoundException in get Connection," +e.getMessage());  
} catch (SQLException e) { 
System.err.println("SQLException in getConnection, " + e.getMessage());  
}  
return con;  
} 

Explanation: 

The Class.forName is used to create instance of JDBC driver using DriverManager. 

The getConnection() of DriverManager is used to create connection to the database. 

Then we must create a Statement object using the connection object. The statement object will return the resultSet Object. The ResultSet.next() means the ResultSet is still returning the row.

Expect to come across this, one of the most important JDBC interview questions for experienced professionals in Java, in your next interviews.

There are mainly 4 types of JDBC API components. 

1. JDBC API 
2. JDBC Driver Manager 
3. JDBC Test Suite 
4. JDBC-ODBC Bridge 

A. JDBC API: The JDBC API provides various interfaces and methods to establish connection with different types of databases. These connecting methods will be present in different package, some of them are

javax.sql.*; 
java.sql.*; 

B. JDBC Driver Manager: The JDBC Driver Manager loads the database-specific driver into the application, to establish connection with the database. The JDBC Driver manager is also used to call databases to do the processing of user requests. 

C. JDBC Test Suite: The JDBC test suite provides required facilities to the application developer to test various functionalities like updating, deletion, insertion which are executed by the JDBC drivers. 

D. JDBC-ODBC Bridge: The JDBC-ODBC Bridge is used to connect the database drivers to the database. It converts JDBC method calls into ODBC method calls. It uses a package sun.jdbc.odbc provide access the ODBC (Open Database Connectivity) characteristics using native libraries

In JDBC, there are set of Statements, which are used to send SQL commands to the relational database and receive data from it. The JDBC statements provides several types of methods such as executeUpdate(), execute(), executeQuery() and many more, which helps to interact with the database to send and receive data. 

There are three types of JDBC statements.  

• Statement: Statement is known as the factory for resultSet. It is used for common interaction with the database. It executes static structured queries at run time.

Statement st = conn.createStatement( );
ResultSet rs = st.executeQuery();

• PreparedStatement: The main purpose of PreparedStatement is to provide input parameter to the queries at runtime.
  

String SQL = "Update item SET limit = ? WHERE itemType = ?"; 
 PreparedStatement  ps = conn.prepareStatement(SQL); 
 ResultSet rs = ps.executeQuery(); 

• CallableStatement: CallableStatement is used to access the stored procedures of database. It also accepts runtime parameters. 

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

Expect to come across this, one of the most important JDBC interview questions for experienced professionals in Java, in your next interviews. Use this table to state their differences.

• Prepared Statements are faster in performance when compared to Statements. Because Statements need to be compiled every time we run the program, but PreparedStatement need single time execution. 
• PreparedStatement requires parameterized queries, whereas statements can only execute static queries. 
• In PreparedStatement the cache reusage can be taken as the PreparedStatement reuse the previous access queries.  
• PreparedStatement is compiled only once per session or request even though it is executed ‘n’ number of times. 

Stored Procedures can be executed using JDBC callable statements. Below is a sample code snippet to execute StoredProcedure

Connection con = null; 
CallableStatement stmnt = con.prepareCall("{call myStoreproc(?, ?)}"); 
stmnt.setString(1, "abcdefg"); 

Yes, we can connect to multiple databases at the same time, but it also depends on the specific driver. If the JDBC driver which we are using supports to connect to multiple databases means, then we can configure that driver in DriverManager and we can access more than one database.

Also, we can use single statement to update and extract data from the different database. But it also depends on the middleware we configure. If the configuration of the middleware provides support single statement, then we can update two or three or many databases.

A must-know for anyone looking for Java Database Connectivity interview questions, this is one of the frequent questions to ask database engineer during their interview.

ResultSet is an interface, which is present in java.sql.ResultSet package. The resultSet object can be obtained after the execution of SQL query using Statement objects. Serialization of ResultSet cannot be done because it maintains the connection between the databases.

ResultSet object maintains a cursor that points to the current row of data in the result set. During initialization, the cursor will be pointed before the first row. Then when the next() is called the cursor will be moved to the next row. The next() method can be used to iterate through result set using while loop.

Example of creating ResultSet object:

ResultSet rs = con.executeQuery(sqlQuery);  

A must-know for anyone looking for Java Database Connectivity interview questions, this is one of the frequent questions to ask database engineer during their interview.

JDBC DriverManager, is the static class of Java. Using DriverManager we manage the set of JDBC drivers which are available for a JDBC application to use.

We can use multiple JDBC drivers concurrently for an application. The URL – Uniform Resource Locator we can specify the JDBC drivers for each application.

When we load JDBC driver class into an application, it registers automatically to the DriverManager using Class.forName() method or by using DriverManager.registerDriver().  

By providing required configuration details to DriverManager.getConnection() method the DriverManager will call the registered drivers to obtain connection and return the output result.

DSN stands for Data Source Name, in the below example we will be connecting java program with Database access. In the example program below, we have created a login table with one column named as Name column in the access database and will fetch all the names present in that column.

import java.sql.*;   
class Test{   
public static void main(String ar[]){   
 try{   
   String database = "student.mdb"; //Here database exists in the current directory   
   String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};   
    DBQ=" + database + ";DriverID=22;READONLY=true";   
 
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   
   Connection con = DriverManager.getConnection(url);   
   Statement stmt = con.createStatement();   
   ResultSet rs = stmt.executeQuery("select * from login");   
   while(rs.next()){   
System.out.println(rs.getString(1));   
   }   
}catch(Exception e){ 
                       System.out.println(e); 
                       }   
} 
}

• A connection interface is a session between a database and the Java Application. It mainly helps to establish connection with the database. 
• Connection interface is known as a factory of Statement, PreparedStatement, and DatabaseMetaData. Which says that by using the object of Connection we can get the object of Statement, PreparedStatement, and DatabaseMetaData. 
• The Connection interface provides many methods which are useful for transaction management some of them are like, commit(), rollback(), setAutoCommit(), setTransactionIsolation() etc. 
• The connection interface by default commits the changes after query execution. 

Commonly used methods of Connection interface are: 

1. public Statement createStatement() 
2. public Statement createStatement(int resultSetType,int resultSetConcurrency) 
3. public void setAutoCommit(boolean status) 
4. public void commit() 
5. public void rollback() 
6. public void close() 

Don't be surprised if this question pops up as one of the top JDBC interview questions in your next interview.

The main objective of Statement interface is to provide methods which are used to execute queries to the database. These types of questions we can expect in java database connectivity interview questions as well.

import java.sql.*;   
class FetchRecord{   
         public static void main(String args[])throws Exception{   
Class.forName("oracle.jdbc.driver.OracleDriver");   
Connection con = DriverManager.getConnection 
("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");   
Statement stmt=con.createStatement();     
//stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");   
//int  
result=stmt.executeUpdate("update emp765 set name='Vimal', salary=10000 where id=33");   
int result=stmt.executeUpdate("delete from emp765 where id=33");   
System.out.println(result+" records affected");   
con.close();   
} 
}   

There are three types of ResultSet available in java JDBC. If we do not declare any ResultSet then the default ResultSet type if called i.e., TYPE_FORWARD_ONLY 

The types of ResultSet are: 

1. TYPE_FORWARD_ONLY: In TYPE_ FORWARD_ONLY cursor can move only in forward direction. 
2. TYPE_SCROLL_INSENSITIVE: In TYPE_SCROLL_INSENSITIVE type of ResultSet, cursor can move in both forward and backward direction, but they are not sensitive. 
3. TYPE_SCROLL_SENSITIVE: In TYPE_SCROLL_SENSITIVE type of ResultSet, cursor can move both forward and backward direction, but they are sensitive.  

The getMetaData() method of ResultSet interface returns the object of ResultSetMetaData.

The syntax for getMetaData()

public ResultSetMetaData getMetaData()throws SQLException

Example program to get the object of ResultSetMetaData: 

import java.sql.*;  
     class ResultSetMetaData{   
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("select * from emp");   
          ResultSet rs = ps.executeQuery();   
          ResultSetMetaData rsmd = rs.getMetaData();     
          System.out.println("Total columns: "+rsmd.getColumnCount());   
          System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1));   
          System.out.println("Column Type Name of 1st column: "+ 
                                              rsmd.getColumnTypeName(1));   
con.close();   
}catch(Exception e){  
                         System.out.println(e); 
}   
}   
}   

Warning is nothing but the exceptions which provides information on database access. Warnings are internally connected to the objects, which conveys which method caused it to be reported, SQL warning or database warnings is the subclass of SQLException class. We can handle these warnings using getWarning() method on the Connection interface, Statement Interface, ResultSet Interface. We also have showWarnings() which provides diagnostic statement which displays information about the errors, warnings and notes by executing a statement in the current session.

But unlike SQL errors, SQL warnings do not cause JDBC methods to throw exception.

We can store images into a database using the PreparedStatement queries. The setBinaryStream() method of PreparedStatement is used to set Binary information into the parameter Index.

Syntax of set Binary information method

1. public void setBinaryStream(int paramIndex,InputStream stream)   throws SQLException   
2. public void setBinaryStream(int paramIndex,InputStream stream,long length)   throws SQLException

For storing image into the database, BLOB [Binary Large Object] datatype is used.

Example:  

CREATE TABLE  "IMAGETABLE"   (    "NAME" VARCHAR2(4000),   "PHOTO" BLOB  ) 

Now let us write a Java code to store Image into database:

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(?,?)");   
                                  ps.setString(1,"sonoo");   
   
                                  FileInputStream fin = new FileInputStream("d:\\g.jpg");   
                                  ps.setBinaryStream(2,fin,fin.available());   
                                  int I = ps.executeUpdate();   
                                  System.out.println(i+" records affected");   
                                 con.close();   
           }catch (Exception e) { 
     e.printStackTrace(); 
}   
       }   
}

Don't be surprised if this question pops up as one of the top JDBC interview questions in your next interview.

The class.forName() is a method that return java object which is associated with class or interface passed as a method parameter.

Syntax for class.forName()

public static Class<T> forName(String className) throws ClassNotFoundException 

Parameter: this method accepts the parameter className which is the class for which the instance is required. 

Return Value: This method return the instance of the specified class 

Exception: The forName() method throws the following exceptions 

1. LinkageError 
2. ExceptionInInitializerError 
3. ClassNotFoundException 

Example Program: 

public class Test { 
    public static void main(String[] args) 
        throws ClassNotFoundException 
    { 
        // get the Class instance using forName method 
        Class c1 = Class.forName("java.lang.String"); 
  
        System.out.print("Class represented by c1: " 
                         + c1.toString()); 
    } 
} 

There are two ways to connect to the database using JDBC: 

1. Using DriverManager 
2. Using Datasource 

a. Using DriverManager: The DriverManager will load the driver class with the help of Class.forName(driver class) and class.forName(). After loading it will pass the control to the DriverManager. The DriverManager.getConnection() method will now create connection to access the database. 

b. Using Datasource: For Datasource, we do not need to use the DriverManager.  The Datasource will look up the Datasource from the Naming service server. DataSource.getConnection() method will return Connection object to interact with the database.

The updated version of JDBC has provided a set of features that are helpful for easy development access. 

• Auto loading using JDBC Driver class 
• Enhanced Connection Management to Database 
• Enabled RowID SQL management. 
• Dataset implementation by SQL using JDBC annotations. 
• Enhancement of JDBC SQL Exception handling. 
• Supporting SQL files with XML format. 
• Provide new Subclass of SQL exception class for better readability. 
• Improvement of Database Metadata format.

There are a total of 8 main packages used in JDBC application.  

• SQL.Driver 
• Connection 
• Statement 
• PreparedStatement 
• CallableStatement 
• ResultSet 
• ResultSetMetaData 
• DatabaseMetaData 
• Core API 
• Javax 

These are the 8 main packages required to develop any application using JDBC and its Drivermanager classes. With the help of these packages, we can import these packages in the callable classes and interact with the database.

DataSource is an interface defined in javax.sql package. It is used for interaction with database to get the database connection. It is also known as the alternative for DriverManager class.  The driver that is accessed through Datasource object is not required to register itself with DriverManager. The DataSource object is retrieved through a lookup operation and then it can be used to create connection Object. 

Benefits of DataSource: 

• It is mainly used in PreparedStatement Caching for faster processing. 
• It enables logging features. 
• It provides connection timeout settings 
• It helps in connection pooling of servlet container with the support of JNDI registry. 

• DDL – stands for Data Definition Language, the DDL statements are used to define the Database Schema. The CREATE, ALTER, DROP, TRUNCATE, RENAME statements are known as the DDL statements. And in most cases, they do not return any results. 
• DML – stands for Data Manipulation Language, the DML statements are used to manipulate the data present in the database. The SELECT, INSERT, UPDATE DELETE and CALL etc. are some of the examples of DML statements.

Advantages of Batch Processing: 

• Batch processing will reduce the communication time and improve application performance. 
• Batch processing makes it easier to process large amounts of data and consistency of data is maintained. 
• It is faster than executing a single statement each time because of the smaller number of database calls. 

To perform batch processing, we should use addBatch() and executeBatch() methods. These two methods are available in the Statement and PreparedStatement classes of JDBC API. By using these methods in the JDBC connection we can execute set of queries in one go. 

One of the most frequently posed Java Database Connectivity questions and answers, be ready for this conceptual question.

Getter methods: The getter methods are used for retrieving the particular column values of the table from the ResultSet. It expects column index or column name as a parameter. The getter method is represented as getXXX() method. 

Example: int getInteger (String columnName) 

The above example is used to retrieve the value of the specified column index and the return type is an int datatype. 

Setter methods: The setter methods are used to set the value in the database. It is similar to the getter methods, but it requires a value to be passed for the particular column to insert that value into the database. Setter methods are represented as setXXX() method. 

Example: void setInt(int Column_Index, int Data_Value) 

The above statement is used to insert the value of the specified column Index with an int value. 

Connection pooling is a database connection mechanism where the data will be stored in the cache so the data can be reused if it is required in the future. So it is not required to make new connection every time for interacting with database. Because the connection objects are stored in connection pool so the client can get connection object from there. 

Advantages of using connection pool: 

• Connection pool helps in faster database transaction 
• Easier to analyze database connection. 
• Increase the performance of executing commands on a database. 

Locking system for a user means when multiple users accessing the same application and trying to update or adding the record into the database at the same time, in such situation we are going to lock so that the data will not be lost. 

There are two types of locking available in JDBC: 

1. Optimistic locking: It will only lock the record when an update takes place. This type of locking will not make exclusion when reading or selecting the records. 
2. Pessimistic locking: it will lock the record as soon as it selects the row to update. The advantage of this locking system is that the changes made are safe. 

Stored procedures contain the Set of SQL queries that are compiled in the database, and it will be executed from the JDBC API. For executing stored procedures in the database, we can use JDBC callable statement. The syntax for initializing the CallableStatement is:

CallableStatement cs = con.prepareCall("{call insertEmployee(?,?,?,?,?)}"); 
stmt.setInt(1, id); 
stmt.setString(2, name); 
stmt.setString(3, role); 
stmt.setString(4, address); 
stmt.setString(5, salary); 
//registering the OUT parameter before calling the stored procedure 
cs.registerOutParameter(5, java.sql.Types.VARCHAR); 
             
cs.executeUpdate();

One of the most frequently posed Java Database Connectivity questions and answers, be ready for this conceptual question.

As we understood what Batch processing and the advantages of Batch processing in the previous questions is, now let us understand them with an example program on how to run Batch processing. 

Batch Processing has the following steps: 

1. Load the driver class 
2. Create Connection 
3. Create Statement 
4. Add query in Batch 
5. Execute Batch 
6. Close Connection 

import java.sql.*;  
class FetchRecords{   
public static void main(String args[])throws Exception{   
            Class.forName("oracle.jdbc.driver.OracleDriver");   
            Connection con = DriverManager.getConnection 
                                                       ("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");   
            con.setAutoCommit(false);   
   
             Statement stmt=con.createStatement();   
stmt.addBatch("insert into user420 values(190,'abhi',40000)");   
stmt.addBatch("insert into user420 values(191,'umesh',50000)");   
   
stmt.executeBatch();//executing the batch   
   
con.commit();   
con.close();   
} 
}   

Some of the most common JDBC exceptions are: 

1. java.sql.SQLException - It is the base class for JDBC exceptions. When any of the query or SQL execution goes wrong, we get this exception. 
2. java.sql.BatchUpdateException – It occurs during the batch update operation. May depend on the JDBC driver type that the base SQLException may throw instead. 
3. java.sql.SQLWarning – It is displayed as a warning message of various SQL operations. 
4. java.sql.DataTruncation – This exception occurs when data values are unexpectedly truncated due to reasons independent of exceeding MaxFieldSize. 

The “No suitable driver error” occurs during a call to the DriverManager.getConnection() method. It occurs due to the following reasons: 

1. When the driver is unable to call the respective driver class before calling the getDriverManager() method. 
2. When user passes an invalid or wrong JDBC URL, where the driver is not able to identify JDBC driver. 
3. This error can also occur when one or more shared libraries required for the application might not load during program execution. 

This is a common yet one of the most important JDBC interview questions and answers for experienced professionals, don't miss this one. Here is the difference table for reference.

Closing any database connection is considered as one of the Best Coding standards. We must always choose to closet the ResultSet, the Statement and the Connection. In some scenarios, if the connection is coming from the connection Pool on closure, then the connection is sent back to the Connection pool for reuse. This scenario will be handled in finally{ } block of exception handling, because if any exception occurs then we can get the option to close the connection.

Dirty Read indicates that, the given read value may or may not be correct or it is considered as a temporary value. Consider in a scenario when a transaction is executing and changing the values in the fields, at the same time another transaction also executed before the first transaction is committed or rollback the value, which results in invalid value to the field. This scenario is known as Dirty Read.

source

The important features of JDBC 3.0 are: 

1. JDBC RowSet 
2. Savepoint in transaction management : it helps to create, rollback and release the savepoint by using Connection.setSavePoint(), Connection.rollBack() and Connection.relaeseSavePoint() methods 
3. Statement and ResultSet Caching for Connection Pooling 
4. Switching between Global and Local Transactions 
5. Retrieval of auto generated keys 

The important features of JDBC4.0 are: 

1. Automatic Loading of Driver class 
2. Subclasses of SQLException 
3. New methods introduced in Connection, PreparedStatement, CallableStatement, ResultSet etc. 
4. Improved DataSource 
5. Event Handling support in Statement for Connection Pooling 

One of the most frequently posed Java Database Connectivity questions and answers, be ready for this conceptual question.

JDBC connection is a connection connecting JDBC application into the database using driver classes provided by JDBC. 

There are mainly 5 steps to connect any Java application to the database using JDBC. They are as follows. 

1. Register the Driver class 
2. Create Connection 
3. Create Statement 
4. Execute Queries 
5. Close Connection 

source

Step 1: Register the Driver Class:   

The forName() method of the Class is used to register to the Driver Class. This method loads the driver class dynamically. Since after the JDBC update to JDBC 4 registering the driver class is optional. We just must put the jar in the classpath so the JDBC driver manager will detect and load the driver automatically.

Syntax: 

public static void forName(String className)throws ClassNotFoundException   

Example: 

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

Step 2: Create the Connection Object:  

For creating connection to the database, we use getConnection() of DriverManager class, it establishes connection to the database. 

Syntax: 

1. public static Connection getConnection(String url)throws SQLException   
2. public static Connection getConnection(String url,String name,String password)   

throws SQLException   

Example: 

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

Step 3: Create the Statement object: 

The statement object is used to execute queries with the database. We use createStatement() method of Connection interface. The statement object executes queries with the database.

Syntax: 

public Statement createStatement()throws SQLException   

Example: 

Statement stmt=con.createStatement();

Step 4: Execute Query 

The executeQuery() method is also from the Statement Interface. It is used to execute queries to the database. It returns the object of ResultSet, which is used to get all the records of a table.

Syntax: 

public ResultSet executeQuery(String sql)throws SQLException   

Example: 

ResultSet rs=stmt.executeQuery("select * from emp");   
while(rs.next()){   
System.out.println(rs.getInt(1)+" "+rs.getString(2));   
}   

Step 5: Close the Connection object: 

If we close the connection object of statement then ResultSet object will be closed automatically. The close() method of connection interface is used to close the JDBC database connection.

Syntax:

Public void close()throws SQLException   

Example:

con.close(); 

The architecture of JDBC includes, the application layer, the JDBC API layer, driver manager layer and the JDBC driver layer.

The application layer: The Application layer is a Java based servlet or applet-based application which contains data-sources and database management information.

The JDBC API layer: The JDBC API layer allows the application developer to run the Java programs to perform the execution of the SQL statements to get the expected output. Some of the widely used interfaces and classes present in JDBC API are: 

• Drivers 
• DriverManager 
• Statement 
• Connection 
• CallabaleStatement 
• PreparedStatement 
• ResultSet 
• SQL data

The DriverManager layer: The DriverManager layer plays an important role in the application development and database connectivity. It uses database specific drivers to connect the Java enterprise application to different of databases. 

JDBC drivers’ layer: The JDBC drivers acts the bridge between the application layer and the driver manager. It interacts with the data sources with the help of JDBC. And it interacts with respective drivers to provide specific application needs.  

One of the most frequently posed Java Database Connectivity questions and answers, be ready for this conceptual question. 

The architecture of the JDBC supports two 2 types of processing models, they are: 

1. Two-tier architecture 
2. Three-tier architecture 

a. Two-tier architecture: 

In two-tier architecture JDBC application interacts directly with the data source. The Java application programs interact directly with no third-party mediation. There is no requirement for mediators like application servers to connect with JDBC API drivers. The Two-tier architecture is also called client-server architecture. When a query is sent by the user to the data source, the response will be sent back directly to the user. 

b. Three-tier architecture:  

In Three-tier architecture, the interaction between Java application and Database will happen via middle-tier services. It is completely opposite to the Two-tier architecture. It will make an application server as a mediator to communicate between JDBC drivers or Java application and database.

The sequence execution of SQL statements served as a single unit is called Transaction. Transaction Management plays an important role in RDBMS applications to maintain data consistency and integrity of the application. RDBMS stands for – Relational Database Management System.  

Transaction Management can be described using the ACID properties. ACID stands for Atomicity, Consistency, Isolation and Durability. 

• Atomicity: In Atomicity, if and only if all the queries are successfully executed then only the data will be committed to database. 
• Consistency: It makes sure that the database is consistent after every transaction. 
• Isolation: The isolation process ensures that the transaction is isolated from other transactions. 
• Durability: In Durability, if the transaction is in the ongoing process, it will remain always committed, even if there occur any errors, communication or power loss situation etc.

import java.io.ByteArrayInputStream; 
import java.io.ByteArrayOutputStream; 
import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.IOException; 
import java.io.InputStream; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
public class TestApplication { 
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; 
   static final String USER = "guest"; 
   static final String PASS = "guest123"; 
   static final String QUERY = "SELECT Data FROM XML_Data WHERE id=100"; 
   static final String INSERT_QUERY="INSERT INTO XML_Data VALUES (?,?)"; 
   static final String CREATE_TABLE_QUERY = "CREATE TABLE XML_Data (id INTEGER, Data LONG)"; 
   static final String DROP_TABLE_QUERY = "DROP TABLE XML_Data"; 
   static final String XML_DATA = "<Employee><id>100</id><first>Zara</first><last>Ali</last><Salary>10000</Salary><Dob>18-08-1978</Dob></Employee>";   
   public static void createXMLTable(Statement stmt)  
      throws SQLException{ 
      System.out.println("Creating XML_Data table..." ); 
      //Drop table first if it exists. 
      try{ 
         stmt.executeUpdate(DROP_TABLE_QUERY); 
      }catch(SQLException se){ 
      } 
      stmt.executeUpdate(CREATE_TABLE_QUERY); 
   } 
} 

A staple in JDBC interview questions and answers, be prepared to answer this one using your hands-on experience.

The Connection Interface provides set of methods for JDBC Transaction Management. They are: 

• setAutoCommit() method 
• commit() method 
• rollback() method 
• setSavePoint() method 
• releaseSavePoint() method

setAutoCommit(): The default value of autoCommit is always set to TRUE. After the execution of SQL statement, the data will be committed automatically. By using this method we can set value for autoCommit().

Syntax: 

con.setAutoCommit(boolean_value) 

a. Commit(): The commit() is used for committing the data. After SQL statement execution, we can call commit() method. It will make sure to commit the changes made by SQL statements. 

Syntax: con.commit();

b. rollback() method: The rollback() is used to undo the changes made in the last commit. If any problem occurs during the execution of a statement flow, we can call rollback()  

Syntax: con.rollBack();

c. setSavePoint(): If you have setSavePoint() method in the transaction, you can use rollback() method to undo all the changes done in the previous transaction, this we can do till the savePoint or after the savePoint() we can set the savePoint value which refers to the current state of the database within transaction. 

Syntax: Savepoint sp= con.setSavepoint("MysavePoint");

d. releaseSavepoint() method: The main job of releasing save point is to release or delete the data created using savePoint. 

Syntax: con.releaseSavepoint("MysavePoint");

One of the limitations of PreparedStatement is that we cannot use it directly with IN Clause statement. Some of the alternative approaches to use PreparedStatement with IN clause are: 

• Execute Single Queries 
• Using Stored Procedure 
• Creating PreparedStatement Query dynamically 
• Using NULL in PreparedStatement Query

The Two-Phase commit is used for distributed environment where multiple processes take part Distributed Transaction Management. In simple words, we can say it as if the transaction is executing and it is affecting multiple databases then the Two-phase commit will be used to make sure that all the databases are synchronized to each other.  

In Two-Phase commit, rollback and commit are performed by two phases. 

1. Commit request phase: In this process, the coordinator process takes vote from all other process, that they have completed execution. If all the votes are ‘Yes’, then it continues to the next phase. 
2. Commit Phase: The commit phase depends on the given vote in the commit request phase. If all the votes are Yes, then commit is done. 

To Connect an excel spreadsheet using JDBC we can follow the below steps: 

1. First setup the new ODBC datasource 
2. Goto Administrative tools – then datasource (ODBC) - then choose system DSN tab – Click on Add – then choose the driver do Microsoft Excel (*.xls) – finally click on finish. 
3. Now provide the datasource name and description 
4. Then click select workbook and point it to the respective excel sheet. 

Let us code the above steps here: 

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); 
Connection con = DriverManager.getConnection(“jdbcodbcSampleExcelTest”,””,””); 
Statement stmt = con.createStatement(); 
Sql = “Select * from [Sheet1$]”; 
Result rs = stmt.executeQuery(sql); 

//import section 
import java.sql.*; 
import java.io.*;
public class CreateTableEx{ 
          public static void main(String[] args)throws Exception {
   //create an object of buffered reader 
               BufferedReader br = new BufferedReader(new InputStreamReader(System.in)); 
   //load and register the driver 
  Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); 
   //establish a connection 
 Connection con = DriverManager.getConnection 
                                   (“jdbc:odbc:nag”,”system”,”naveen”); 
   //create a statement object 
   Statement st = con.createStatement(); 
   //receive dynamic input as a table name 
   System.out.println(“Enter table name”); 
   String tablename = br.readLine(); 
   //execute SQL query 
   St.executeUpdate("create table"+tablename+"(empno number,empname varchar2(10),empsalary number,empaddress varchar2(20))"); 
   System.out.println(“Successfully created the table”); 
   //close the connection 
   con.close(); 
 } 
}

A RowSet is an object that encapsulates a set of rows from JDBC ResultSet or tabular data source. 

There are 5 types of RowSet available in JDBC: 

1. JDBCRowSet: RowSet is called the connected JDBCRowSet which means it always has an active database connection. This will be having live connection with database. The JDBC calls to the rowSet are mapped to the JDBC Connection, Statement, ResultSet. The Oracle implementation of rowSet is done using the package oracle.jdbc.rowset.OracleJDBCRowSet 
2. CachedRowSet: In CachedRowSet, the rows will always be cached and the RowSet is disconnected, which means it does not maintain an active database connection. The oracle.jdbc.rowset.OracleCachedRowSet class is used as the Oracle implementation of CachedRowSet. 
3. WebRowSet: WebRowSet is an extension to CachedRowSet and it represents a set of retrieved rows of tabular data that can be used between tiers and components so that no active data source connections need to be maintained. The Oracle implementation of WebRowSet is done by using oracle.jdbc.rowset.OracleWebRowSet. 
4. FilteredRowSet: It is an extension to WebRowSet, it gives application support to filter its content. This enables us to avoid the difficulty of queries and data processin. The Oracle implementation of FilteredRowSet is done by using oracle.jdbc.rowset.OracleFilteredRowSet. 
5. JoinRowSet: It is also an extension to WebRowSet. There is no proper way to establish a SQL JOIN operation between the disconnected RowSets. To handle this case a JoinRowSet is introduced. The Oracle implementation of JoinRowSet is done by using oracle.jdbc.rowset.OracleJoinRowSet class.

To get the first 100 rows and then again going back and retrieving the next 100 rows from the 1000 rows table we can use Statement.fetchSize() method to indicate the size of each database fetch.

import java.sql.CallableStatement; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
public class JDBCExample { 
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; 
   static final String USER = "guest"; 
   static final String PASS = "guest123"; 
   static final String QUERY = "{call getEmpName (?, ?)}"; 
   public static void main(String[] args) { 
      // Open a connection 
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); 
         CallableStatement stmt = conn.prepareCall(QUERY); 
      ) {       
         // Bind values into the parameters. 
         stmt.setInt(1, 1);  // This would set ID 
         // Because second parameter is OUT so register it 
         stmt.registerOutParameter(2, java.sql.Types.VARCHAR); 
         //Use execute method to run stored procedure. 
         System.out.println("Executing stored procedure..." ); 
         stmt.execute(); 
         //Retrieve employee name with getXXX method 
         String empName = stmt.getString(2); 
         System.out.println("Emp Name with ID: 1 is " + empName); 
      } catch (SQLException e) { 
         e.printStackTrace(); 
      }  
   } 
}

This, along with other interview questions on JDBC for freshers, is a regular feature in JDBC interviews, be ready to tackle it with the approach mentioned below.

• StoredProcedure is a group of SQL queries. The StoredProcedure are executed in a single logical unit of time, we do not need to execute each query separately.  
• The name of each StoredProcedure should be unique, since each procedure is represented by its name. 
• For example, consider a scenario where you need to fetch the details of an employee and then can written as a StoredProcedure as below: 

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

• These StoredProcedure are called using the CallableStatement available in the JDBC API.  

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

There are three types of Parameters used in the StoredProcedure: 

1. IN: It is used to pass input values to the StoredProcedure using setXXX() we can set values to IN parameters 
2. OUT: It is used to fetch values from the StoredProcedure using getXXX(), we can get values from the OUT parameters 
3. IN/OUT: It is used for passing the input and output values to/ from the StoredProcedure. We can use setXXX() to set values and getXXX() to get values from StoredProcedure.

This question may not relate to JDBC interview question, but the answer will be indirectly supports JDBC so we can expect this question in an interview. 

• In the cold back process, the backup of data files is taken before restarting the database or when the database is not started.
• In the hot backup process, the backup of data is taken during the database execution or when the database is running. 

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.Statement;
public class JDBCExample { 
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; 
   static final String USER = "guest"; 
   static final String PASS = "guest123";
   public static void main(String[] args) { 
      // Open a connection 
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); 
         Statement stmt = conn.createStatement(); 
      ) {       
         // Execute a query 
         System.out.println("Inserting records into the table...");           
         String sql = "INSERT INTO Registration VALUES (100, 'Zara', 'Ali', 18)"; 
         stmt.executeUpdate(sql); 
         sql = "INSERT INTO Registration VALUES (101, 'Mahnaz', 'Fatma', 25)"; 
         stmt.executeUpdate(sql); 
         sql = "INSERT INTO Registration VALUES (102, 'Zaid', 'Khan', 30)"; 
         stmt.executeUpdate(sql); 
         sql = "INSERT INTO Registration VALUES(103, 'Sumit', 'Mittal', 28)"; 
         stmt.executeUpdate(sql); 
         System.out.println("Inserted records into the table...");      
      } catch (SQLException e) { 
         e.printStackTrace(); 
      }  
   } 
} 

The SQL NULL and Java Null values will stand completely different to each other. There are three main things you can keep in mind. 

1. Avoid using getXXX() methods that return primitive datatypes. 
2. Use Wrapper classes for primitive datatypes and use the ResultSet object’s null() method to check whether the wrapper class variable that received the value returned by the getXXX() method is set to null. 
3. Use Wrapper classes for primitive datatypes and use the ResultSet object’s null() method to check whether the wrapper class variable that received the value returned by the getXXX() method is set to acceptable value which you have chosen to represent null. 

We can use `DatabaseMetaData` object to get the database server details. When the database connection is created successfully, we can get the meta data object by calling `getMetaData()` method. There are so many methods in DatabaseMetaData that we can use to get the database product name, it's version and configuration details.

DatabaseMetaData metaData = con.getMetaData();
String dbProduct = metaData.getDatabaseProductName();

This, along with other interview questions on JDBC for freshers, is a regular feature in JDBC interviews, be ready to tackle it with the approach mentioned below.

In the below example, we will perform the insert, update and delete operation on a table by using Java program. Here we are using the target database system as Oracle, but the same technique can be applied for another database as well because.

Following are the steps to create this application. 

Step 1: Create “Emp” table 

Create table emp (emp_id number, empname varchar2(10), email varchar2(30), city varchar2(10));  

Step 2: Develop the Java application  

import java.sql.*; 
class EmployeeRecord 
{ 
     public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:XE"; 
     public static final String DBUSER = "local"; 
     public static final String DBPASS = "test"; 
     public static void main(String args[]) 
     { 
          try 
          { 
               //Loading the driver 
               Class.forName("oracle.jdbc.driver.OracleDriver"); 
               //Create the connection object 
               Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPASS); 
               //Insert the record 
               String sql = "INSERT INTO emp (emp_id, empname, email, city) VALUES (?, ?, ?, ?)"; 
               PreparedStatement statement = con.prepareStatement(sql); 
               statement.setInt(1, 100); 
               statement.setString(2, "Prashant"); 
               statement.setString(3, "prasant@saxena.com"); 
               statement.setString(4, "Pune"); 
 
               int rowsInserted = statement.executeUpdate(); 
               if (rowsInserted > 0) 
               { 
                    System.out.println("A new employee was inserted successfully!\n"); 
               } 
               // Display the record 
               String sql1 = "SELECT * FROM Emp"; 
               Statement stmt = con.createStatement(); 
               ResultSet result = stmt.executeQuery(sql1); 
 
               while (result.next()) 
               { 
                    System.out.println (result.getInt(1)+" "+ 
                    result.getString(2)+" "+ 
                    result.getString(3)+" "+ 
                    result.getString(4)); 
               } 
 
               //Update the record 
               String sql2 = "Update Emp set email = ? where empname = ?"; 
               PreparedStatement pstmt = con.prepareStatement(sql2); 
               pstmt.setString(1, "Jaya@gmail.com"); 
               pstmt.setString(2, "Jaya"); 
               int rowUpdate = pstmt.executeUpdate(); 
               if (rowUpdate > 0) 
               { 
                    System.out.println("\nRecord updated successfully!!\n"); 
               } 
 
               //Delete the record 
               String sql3 = "DELETE FROM Emp WHERE empname=?"; 
               PreparedStatement statement1 = con.prepareStatement(sql3); 
               statement1.setString(1, "Prashant"); 
 
               int rowsDeleted = statement1.executeUpdate(); 
               if (rowsDeleted > 0) 
               { 
                    System.out.println("A Employee was deleted successfully!\n"); 
               } 
          } 
          catch(Exception ex) 
          { 
               ex.printStackTrace(); 
          } 
     } 
}

The best Practice to be followed while using JDBC application are listed below: 

• Use the PreparedStatement 
• Use the ConnectionPool  
• Disable auto-commit mode 
• Use JDBC Batch update methods 
• Access ResultSet using column name to avoid invalidColumnIndexError 
• Use data binding variables instead of String concatenation 
• Always close database Statements, PreparedStatement and Connections 
• Choose proper JDBC driver for your application as per the requirement 
• Use standard SQL statements and try to avoid DB specific queries 
• Use proper getXXX() methods and setXXX() methods