JDBC interview Questions

What are the steps involved in establishing a JDBC connection? 

This action involves two steps: loading the JDBC driver and making the connection.

 

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

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.

 

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:Jo”;
Connection con = DriverManager.getConnection(url, “jo”, “j8?);

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

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.

What are the different types of Statements?
Regular statement (use createStatement method), prepared statement (use prepareStatement method) and callable statement (use prepareCall)

 

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.

1.       PreparedStatement updateSales =

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

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

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.

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

4.                ResultSet rs = cs.executeQuery();

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

         }

}

1

2

3

4

5

6

7

8