JDBC interview questions and answers

Thanks to Sachin Rastogi for sending in Java database interview questions.

  1. What are the steps involved in establishing a JDBC connection? This action involves two steps: loading the JDBC driver and making the connection.
  2. 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:

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

  3. 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.
  4. 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?);

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

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

  7. What are the different types of Statements?
    Regular statement (use createStatement method), prepared statement (use prepareStatement method) and callable statement (use prepareCall)
  8. 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 ?");
    
  9. 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);
    
  10. 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();
    
  11. 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();
    	}
    }
    
  12. 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.

  13. 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);
    }
    
  14. 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");
    
This entry was posted in Database, Java. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

17 Comments on JDBC interview questions and answers

  1. Posted 9/23/2004 at 1:29 am | Permalink

    Good questions. I want to know about JDBC KPRB driver which is used for database connectivity. Can someone tell me.

  2. Posted 9/25/2004 at 3:13 am | Permalink

    Hi Annie,

    Oracle’s JDBC KBPR driver is mainly used for writing Java stored procedures, triggers and database JSPs. It uses the default/ current database session and thus requires no additional database username, password or URL.

    To obtain current connection one can call the OracleDriver.defaultConenction() method. Following code snippet explains this:

    import java.sql.*;
    class dbAccess {
    public static void main (String args []) throws SQLException
    {
    Connection conn = (new oracle.jdbc.driver.OracleDriver()).defaultConnection();

    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery(”select ename from emp”);
    while (rset.next())
    System.out.println (rset.getString(1)); stmt.close();
    }
    }

    Cheers
    Sachin Rastogi

  3. swapnavellani
    Posted 12/9/2004 at 1:56 am | Permalink

    Good set of questions. I would like to know what will happen if the driver does not support updateable result sets? Will it throw an exception or a warning?

  4. Posted 3/19/2005 at 10:51 pm | Permalink

    1.in jdbc api why interfece are used?

  5. cs!!
    Posted 4/11/2005 at 2:26 pm | Permalink

    because the vendors have to give the definitions..

  6. Arvind Kumar Avinash
    Posted 9/13/2005 at 4:28 am | Permalink

    What is difference between these two:
    (1)DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    (2)Class.forName(”oracle.jdbc.driver.OracleDriver”);

  7. Arvind Kumar Avinash(ak_avinash@rediffmail.com)
    Posted 9/13/2005 at 4:32 am | Permalink

    What is Connection Pooling and how is it advantageous?

  8. Arvind Kumar Avinash(ak_avinash@rediffmail.com)
    Posted 9/13/2005 at 4:34 am | Permalink

    What is difference between these two:
    (1)DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    (2)Class.forName(”oracle.jdbc.driver.OracleDriver”);

  9. dhanu
    Posted 1/16/2006 at 9:03 am | Permalink

    Hi this is my question
    can i create the database table through the JDBC program?

  10. Ashok Sharma
    Posted 2/5/2006 at 11:33 am | Permalink


    #

    What is difference between these two:
    (1)DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    (2)Class.forName(”oracle.jdbc.driver.OracleDriver”);

    Tech Interviews comment by Arvind Kumar Avinash

    These are two different ways of implementing the scenario. “Class.forName” creates a Class object and then registers the instance with the driver manager. DriverManager.registerDriver registers an instance of the with driver manager.

  11. Giridhar
    Posted 2/13/2006 at 4:37 am | Permalink

    Hi,
    1.What are the different types of Transaction Isolation Levels?
    2.What are the differrent ways of accessing database otherthan using (DriverManager.getConnection……)?

  12. Posted 7/3/2006 at 6:12 am | Permalink

    what is the diff. between 2-tier & 3-tier

  13. Ramesh
    Posted 7/24/2006 at 10:47 am | Permalink

    Hai,

    My question is How can we pass parameters to JSP page from Servlet?

  14. shyam
    Posted 10/16/2006 at 11:43 pm | Permalink

    answer for praveen—-diff. btw. 2-tier & 3-tier

    ……….
    request
    2-tier… CLIENT SERVER
    response

    request request
    3-tier…CLIENTSERVERDATABASE
    response response

  15. shyam
    Posted 10/16/2006 at 11:48 pm | Permalink

    sorry ……..the diagram didnt print correctly.
    the answer is…..
    Two-tier architecture is a client/server architecture , where a request is sent to the server and the server responds by performing the task. In three-tier or a multi tier architecture, the client request is sent to the server and the server in turn sends the request to the database. The database sends back the information required to the server which inturn sends it to the client

  16. Posted 12/12/2006 at 2:29 am | Permalink

    What is the Differnce between cached rowset and resultset?

  17. sazi
    Posted 4/29/2007 at 6:25 am | Permalink

    diff between tier :
    tier is nothing but a physical layer, physical layer is nothing but a 6 logical layer( database manager, dblogic,business logic,application logic, presentation logic,Presentation manager)

    1-tire —> all process is there in sigle machine

    2-tire –>contain the two machine ( client & server )
    3-tire –> Contain client(Presentation logic& manager),server(DB logic & manager),businesslogic ( business Process logic)

    n-tire –> all process is run on the diff machine

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*