The JDBC questions and answers covered here are an extension to the frequently asked questions covered in "Java/J2EE Job Interview Companion" book. The questions discussed below are generally asked in online JEE technical tests.
Q. What will be the out put in the following scenario where the table "
Course" has the following records
and the code that is used to access the database is as follows.
public void executeSelectQuery(Connection con) {
PreparedStatement ps = null;
try {
ps = con.prepareStatement("SELECT COURSE_ID, NAME, COURSE FROM COURSE");
ResultSet rs = ps.executeQuery(); // read from database
rs.absolute(0); //moves the cursor to the given row number
rs.relative(2); //moves the cursor by 2 rows
while(rs.next()){
Integer id = rs.getInt("COURSE_ID");
String name = rs.getString("NAME");
String course = rs.getString("COURSE");
System.out.println("id:" + id + ", name:" + name + ", course:" + course);
}
}
catch (Exception e) {
System.out.println("ERROR executing query: ");
e.printStackTrace();
}
finally{
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
A. The above code should thrown an exception "
java.sql.SQLFeatureNotSupportedException: feature not supported".
The cause for this exception is in line where you prepare a statement. You are basically creating a cursor here.
ps = con.prepareStatement("SELECT COURSE_ID, NAME, COURSE FROM COURSE");
The above method by default values for the
resultSetType and
resultSetConcurrency. So, the above line is equivalent to
ps = con.prepareStatement("SELECT COURSE_ID, NAME, COURSE FROM COURSE",
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
The JDBC API definitions for the
resultSetType and
resultSetConcurrency are as follows
resultSetType - TYPE_FORWARD_ONLY: The result set cannot be scrolled; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database generates the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
- TYPE_SCROLL_INSENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
- TYPE_SCROLL_SENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open.
resultSetConcurrency - CONCUR_READ_ONLY: The ResultSet object cannot be updated using the ResultSet interface.
- CONCUR_UPDATABLE: The ResultSet object can be updated using the ResultSet interface.
Q. What will be the output for the following code snippet for the same "
Course" table?
public void executeSelectQuery(Connection con) {
PreparedStatement ps = null;
try {
ps = con.prepareStatement("SELECT COURSE_ID, NAME, COURSE FROM COURSE", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
ResultSet rs = ps.executeQuery(); // read from database
rs.absolute(0); //moves the cursor to the given row number
rs.relative(2); //moves the cursor by 2 rows
while(rs.next()){
Integer id = rs.getInt("COURSE_ID");
String name = rs.getString("NAME");
String course = rs.getString("COURSE");
System.out.println("id:" + id + ", name:" + name + ", course:" + course);
}
}
catch (Exception e) {
System.out.println("ERROR executing query: ");
e.printStackTrace();
}
finally{
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
A.
ResultSet.TYPE_SCROLL_INSENSITIVE type will allow the cursor to scroll forward or backward.
The output will be
id:3, name:paul, course:JSF
id:4, name:jonathan, course:Hibernate
id:5, name:james, course:Spring
id:6, name:Lewis, course:JDBC
The cursor will scroll to the first record when
rs.absolute(0); is executed. It will scroll another 2 records when
rs.relative(2); is executed. When it gets to the while(rs.next()){ ...} loop it will start printing from third record onwards. You can practice different scenarios with the
JDBC Tutorial.
Q. What is RowSet? What is the difference between
RowSet and
ResultSet? What are the advantages of using
RowSet over
ResultSet?
A.
RowSets are a JDBC 2.0 extension to the java.sql.ResultSet interface. Guess what, it makes life a lot easier for all JDBC programmers. No more Connection objects, statement objects, just a single RowSet will do everything for you. RowSet object follows the JavaBeans model for properties and event notification, it is a JavaBeans component that can be combined with other components in an application.
The ResultSet has an open connection to the database whereas a RowSet works in a disconnected fashion. It has the following
advantages over a ResultSet.
- Since a RowSet works in a disconnected mode, especially for "read-only" queries, it would have better performance in a highly concurrent system.
- Rowsets have many different implementations to fill different needs. These implementations fall into two broad categories, rowsets that are connected and those that are disconnected.
- Rowsets make it easy to send tabular data over a network. They can also be used to provide scrollable result sets or updatable result sets in special cases when the underlying JDBC driver does not support them.
RowSet disadvantages.
- Rowset keeps all the data from the query result in memory. This is very in-efficient for queries that return huge data.
There are 3 types of RowSets. The JdbcRowset
JdbcRowSet is a connected type of rowset as it maintains a connection to the data source using a JDBC driver
JdbcRowSet jdbcRowSet = new JdbcRowSetImpl();
jdbcRowSet.setCommand("SELECT * FROM Course);
jdbcRowSet.setURL("jdbc:hsqldb:hsql://localhost/mytestdb");
jdbcRowSet.setUsername("sa");
jdbcRowSet.setPassword("pwd");
jdbcRowSet.execute();
CachedRowSet and WebRoeSet are disconnected types of rowsets as they are connected to the data source only when reading data from it or writing data to it.
ResultSet rs = stmt.executeQuery("SELECT * FROM Course");
CachedRowSet crset = new CachedRowSetImpl();
crset.populate(rs);
WebRowSet wrs = new WebRowSetImpl();
wrs.populate(rs);
wrs.absolute(2)
wrs.updateString(1, "JNDI");
Q. What is
Metadata and why should you use it?
A. JDBC API has 2 Metadata interfaces --
DatabaseMetaData &
ResultSetMetaData. The meta data means data about data, and provides comprehensive information about the database as a whole. The implementation for this interface is implemented by database driver vendors to let users know the capabilities of a Database.
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData resultSetMeta = rs.getMetaData();
int numberOfColumns = resultSetMeta.getColumnCount();
boolean b = resultSetMeta.isSearchable(3);
Q. What are
database warnings and why do you need them?
A. Warnings are issued by a database to inform user of a problem which may not be very severe. Database warnings do not stop the execution of SQL statements. Warnings are silently chained to the object. You need warnings for the
reporting purpose. Warnings may be retrieved from
Connection,
Statement, and
ResultSet objects.
SQLWarning warning = conn.getWarnings();
QLWarning nextWarning = warning.getNextWarning();
conn.clearWarnings();
...
stmt.getWarnings();
stmt.clearWarnings();
...
rs.getWarnings();
...
