JDBC Interview Questions

How do you create JDBC statements ?

Connection con = null;
Statement st = null;
// Obtain connection here
st = con.createStatement();
ResultSet rs = null;
rs = st.executeQuery(“SELECT * FROM users”);
int recordsUpdated;

recordsUpdated = st.executeUpdate(“DELETE FROM users WHERE user_id =

1”);

How do you retrieve data from a result set, explain with an examplet?

Example:
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(SELECT COF_NAME, PRICE FROM

COFFEES_T);
while (rs .next() )
{
//Iam assuming there are 3 columns in the table.
System.out.println ( rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
//Never forget to close the resultset, statement & connection
rs.close(); //First
stmt.close(); //Second
con.close(); //Last
System.out.println(“You are done”);

Explain about stored procedure ?

A stored procedure is a group of SQL statements that form a logical unit and

perform a particular task. Stored procedures are used to encapsulate a set of operations or

queries to execute on a database server. For example, operations on an employee database (hire,

fire, promote, lookup) could be coded as stored procedures executed by application code. Stored

procedures can be compiled and executed with different parameters and results, and they may have

any combination of input, output, and input/output parameters.

What are the different tasks of JDBC ?

Following are the tasks of JDBC
Load the JDBC drivers
Register the drivers
Specify a database
Open a connection to database
Submit a query to database
Gets the results

When do we look for  batch updates ?

Let’s say there are 100 records need to be insert. If we execute normal

statemets the no of transactions will be 100 (in terms of connection making to DB). using batch

updates we can add 100 rec to batch and the no of transactions will be only one in this case.

This will reduce the burdon on db, which is very costly in terms of resources.

Explain the way in which you can invoke a stored procedure from java ?

You can call a stored procedure using Callable statements
CallableStatement cs = con.prepareCall(“{call StoredProc}”);
ResultSet rs = cs.executeQuery();
What packages are being used by JDBC
Following packages are used in JDBC
java.sql
javax.sql

How can you get the resultset of Stored procedure ?

CallableStatement cstmt;
ResultSet rs;
int i;
String s;

cstmt.execute();// Call the stored procedure 1
rs = cstmt.getResultSet();// Get the first result set 2
while (rs.next()) {               // Position the cursor 3
i = rs.getInt(1);          // Retrieve current result set value
System.out.println(“Value from first result set = ” + i);   // Print the

value
}
cstmt.getMoreResults();  // Point to the second result set 4a
// and close the first result set
rs = cstmt.getResultSet(); // Get the second result set 4b

while (rs.next()) {               // Position the cursor 4c
s = rs.getString(1);   // Retrieve current result set value
System.out.println(“Value from second result set = ” + s);
// Print the value
}
rs.close();                          // Close the result set
cstmt.close();                    // Close the statement

When do we set setAutoCommit(false) ?

The DML operations by default are committed. If we wish to avoid the commit by default,

setAutoCommit(false) has to be called on the Connection object.Once the statements are executed,

commit() has to be called on the Connection object explicitly