JDBC interview questions and answers

1. What is JDBC?
   Answer: JDBC is an acronym for Java Data Base Connectivity. It is an API for communicating with relational data base (where data is stored in tables with rows and columns). A developer can make use of the API which has classes (java.sql.DriverManager, java.sql.Date etc) and interfaces (Connection, Driver, Statement, ResultSet), to persist the data.
2. Explain the general procedure for establishing a connection with the data base.
   Answer:

   • Register the driver. Below piece of code is an example of registering oracle driver.

       Driver driver = new oracle.jdbc.driver.OracleDriver();
       DriverManager.registerDriver(driver);
       

   • Use DriverManager.getConnection method to establish the connection with the data base as shown in the below code.

      Connection conn = DriverManager.getConnection("dbc:oracle:thin:@localhost:1521:xe","hr","hr");
      

3. Connection is an interface. How can you create an object to Connection interface?
   Answer: We cannot directly create an object to Connection interface. When we execute Connection c = DriverManager.getConnection(); it internally executes the code that is provided by JDBC driver vendor. This code creates an object based on the class provided by the vendor that implements connection interface.
4. What are the different types of statements in JDBC?
   Answer: JDBC API provides us with 3 different statements (interfaces) using which we can persist data after establishing the connection.

   • Statement: It is used to implement simple SQL statements with no parameters.

      Statement stmt = null;
      stmt = con.createStatement();
      

     
     
   • PreparedStatement: This is used for precompiling SQL statements that may or may not contain input parameters. A sample code of how to use PreparedStatement is given below.

      PreparedStatement pstmt = conn.prepareStatement("insert into sample values(?)");
       for(int i=0; i<10; i++){
        pstmt.setInt(1, i);
        pstmt.executeUpdate();
      

     To see the complete example for PreparedStatement ... "Click here"
     
   • CallableStatement: This is used to execute stored procedures (a stored procedure is a group of SQL statements that form a logical unit and perform a particular task) that may contain both input and output parameters.

      callStmt = conn.prepareCall("{call procx(?,?,?)}");
       // set input parameters
       callStmt.setInt(1,10);
       callStmt.setInt(2, 20);
     
       //register output types
       callStmt.registerOutParameter(3, Types.INTEGER);
       callStmt.execute();
      

     To see the complete example for CallableStatement ... "Click here"
5. Explain about the different JDBC drivers.
   Answer: There are four types of drivers. They are:

   • Type I driver or JDBC-ODBC Bridge Driver
   • Type II driver or JDBC-Native API Driver
   • Type IV driver or Pure java Driver
   • Type III driver or JDBC-Net pure Java

   To learn more about each driver ... "Click here"
6. Can we execute non select statement using execute query?
   Answer: Yes we can execute non select statement using execute query but it is not recommended. But at the end data base will be updated. Some drivers give exceptions like inetGate.jar
7. What is ResultSet?
   Answer: ResultSet object is returned as a result of executing the executeQuery. ResultSet represents a set of rows. ResultSet is an interface.

    String query = "select employee_id, first_name from employees";
     ResultSet rs = stmt.executeQuery(query);
     
     while(rs.next()){
      System.out.print("Employee_Id = "+rs.getString(1));
      System.out.println("  First_Name = "+rs.getString("first_name"));
     }
    

   Have a look at the complete program by ... Clicking here
8. What is ResultSetMetaData?
   Answer: ResultSetMetaData is used to give more information about the ResultSet objects like data type of the columns, name of the columns etc. For example ResultSetMetaDate example
9. How can you improve the performance of an application?
   Answer: We can improve the performance of the application by choosing appropriate value for fetch size. "statement.setFetchSize()" sets the fetch size used by the jdbc driver.
   The default fetch size of oracle driver is "10". In some drivers like JdbcOdbc by Sun Micro Systems the default fetch size id "1" and we cannot increase the fetch size.
10. How do we know the fetch size of the driver we are using?
    Answer: To know the fetch size, we can use the below statement.

     System.out.println(statement.getFetchSize());
     

11. How do you commit your transactions?
    Answer: By default all the jdbc drivers will be running in "autocommit" mode. To find out whether the driver is running in autocommit mode or not we can use the below statement.

      System.out.println(connection.getAutoCommit());
      

    If you want to explicitly commit a transaction then use:
    connection.commit();
12. How do you control transactions in JDBC?
    Answer: We can control the transactions in JDBC by setting the autoCommit to "false".
13. What is connection pooling?
    Answer: Opening a connection to a database is a time-consuming process. For short queries, it can take much longer to open the connection than to perform the actual database retrieval. Consequently, it makes sense to reuse Connection objects in applications that connect repeatedly to the same database. A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. It reduces the overhead for the application and increases performance.
14. What is the use of Class.forName("SomeClass")?
    Answer: Class.forName("SomeClass") does the following things.

    • It loads the "SomeClass".
    • Returns an instance of SomeClass

    For example:

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