February 27, 2016

Below I’m presenting you the next set of question of JDBC

Q21. 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);
}

Q22. 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");

Q23. What are the differences between client and server database cursors?

What you see on the client side is the current row of the cursor which called a Result (ODBC) or ResultSet (JDBC). The cursor is a server-side entity only and remains on the server side.

Q24. Are prepared statements faster because they are compiled? if so, where and when are they compiled?

Prepared Statements aren’t actually compiled, but they are bound by the JDBC driver. Depending on the driver, Prepared Statements can be a lot faster – if you re-use them. Some drivers bind the columns you request in the SQL statement. When you execute Connection.prepareStatement(), all the columns bindings take place, so the binding overhead does not occur each time you run the Prepared Statement. For additional information on Prepared Statement performance and binding see JDBC Performance Tips on IBM’s website.


Q25. What are the advantages is there to using prepared statements if I am using connection pooling or closing the connection frequently to avoid resource/connection/cursor limitations?

The ability to choose the ‘best’ efficiency (or evaluate tradeoffs, if you prefer,) is, at times, the most important piece of a mature developer’s skillset. This is YAA (Yet Another Area,) where that maxim applies. Apparently there is an effort to allow prepared statements to work ‘better’ with connection pools in JDBC 3.0, but for now, one loses most of the original benefit of prepared statements when the connection is closed. A prepared statement obviously fits best when a statement differing only in variable criteria is executed over and over without closing the statement.

However, depending on the DB engine, the SQL may be cached and reused even for a different prepared statement and most of the work is done by the DB engine rather than the driver. In addition, prepared statements deal with data conversions that can be error prone in straight ahead, built on the fly SQL; handling quotes and dates in a manner transparent to the developer, for example.


Q26. Why do I get an UnsupportedOperationException?

JDBC 2.0, introduced with the 1.2 version of Java, added several capabilities to JDBC. Instead of completely invalidating all the older JDBC 1.x drivers, when you try to perform a 2.0 task with a 1.x driver, an UnsupportedOperationException will be thrown. You need to update your driver if you wish to use the new capabilities.







Q27. Can I reuse a Statement or must I creates a new one for each query?

When using a JDBC compliant driver, you can use the same Statement for any number of queries. However, some older drivers did not always “respect the spec.” Also note that a Statement SHOULD automatically close the current ResultSet before executing a new query, so be sure you are done with it before re-querying using the same Statement.

Q28. Will a call to PreparedStatement.executeQuery() always close the ResultSet from the previous executeQuery() if you don’t you yourself?

A ResultSet is automatically closed by the Statement that generated it when that Statement is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.


Q29. How do you convert a java.sql.Timestamp to a java.util.Date?

While Timestamp extends Date, it stores the fractional part of the time within itself instead of within the Date superclass. If you need the partial seconds, you have to add them back in.

Date date = new Date(ts.getTime() + (ts.getNanos() / 1000000 ));


Q30. What are the data access design patterns you use while writing the JDBC layers?

This is a very good and important question. The answer to the question is very long. In summary you need to describe the DAO pattern. you can use Factory + interface inheritance to write the data access layer. I would write sometime in future on this.

Q31. When I create multiple Statements on same Connection, only the current Statement appears to be executed. What’s the problem?

All JDBC objects are required to be thread safe. Some drivers, unfortunately, implement this requirement by processing Statements serially. This means that additional Statements are not executed until the preceding Statement is completed.

Q32. Can a single thread open up multiple connections simultaneously for the same database if yes how many?

The general answer to this is yes. If that were not true, connection pools, for example, would not be possible. This is derived from the underlying database setting the max connection limit set on the database.

Q33. What do you understand by connection timeout interval?

Every database connection has the timeout interval set (depends on database configuration). This is the max inactivity interval for any connection. If not statement is executed within this time the underlying database would invalidate the connection.