So what do you understand by IMMUTABLE ?

but how do we define mutable, or immutable? I searched various Java texts but could not find a definition of what is meant by that. Let's try and define it for our use:

Immutable Class: A class where the state cannot be changed.

Simple enough, but what does state refer to? Is it a state descriptor of all the data members in the class? Or is it just the identity of the class? For example, let's consider the class Employee, which is part of the middle class ...

public class Employee {

  private final String name;

  private final double salary;

  private final Date dob;

  public Employee(String name, double salary, Date dob) {

    this.name = name;

    this.salary = salary;

    this.dob = dob;

  }

  public String getName() { return name; }

  public double getSalary() { return salary; }

  public Date getDOB() { return dob; }

}

Is the Employee class mutable?

If we say that the state consists of a combination of all the data members then the answer is "Yes". We need to be careful examining the object state here. Do you think its possible to change the object state from outside ?

If not then the object definition is immutable however if you are able to change then the object is not immutable?

General assumption is if the member variables are declared final and we only provide the accessers then the class is immutable. So let us use the above class in our code.

public class ImmutabilityTest {

  public static void main(String[] str) {

      Employee emp = new Employee(“john”, 10000, new Date());

    //lets fetch the emp date of birth;

        Date empdob = emp.getDOB();

    //so I got the reference to the final object stored within the

    //Employee object.

        empdob.setMonth(10);

        //can I do this ?

        //how about the state of the original object ?? 

        }

}

So I created the emp object and got the dob date object reference and changed the date value empdob.setMonth(10); so I am able to change the object state.

The key thing worth keeping in mind is the final keyword is making the refernce final. The objects state of members is still changeable through refernces.

How you would address such issues ?

You need to make sure that whenever you return the object, you return the copy of the object. E.g. modify the getDOB() in this case to do something like

public Date getDOB() { return dob.clone(); }

I am keeping the original reference within the object itself and returning the deep copy of the object to the caller.

Some fairly good JDBC Questions 24-33

[JDBC Questions 1-12], [JDBC Questions 13-24]

Q25 What advantage 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 create 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 dont 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 Timesteamp 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 ?
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 threadsafe. 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 mutliple 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 drived from the underlying database setting the max connection limit set on the database.

Q33. What do you understand by connection timeout intervaal ?

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.

The Good to Great performance in a JDBC application

Few points to consider:

* Use a connection pools wherever possible.

* Use prepared statements. These can be beneficial, for example with DB specific escaping, even when used only once. use prepare once and use always strategy.

* Use stored procedures when they can be created in a standard manner. Do watch out for DB specific SP definitions that can cause migration headaches, but only if you need to support multiple database types. Sometimes over-engineering is a overhead. Don't do something just because you can think :) . Sometimes keeping the things simple is the most desirable and admirable thing on the earth.

* Even though the jdbc promotes portability, true portability comes from NOT depending on any database specific data types, functions and so on.

* Select only required columns rather than using select * from Tablexyz.

* Always close Statement and ResultSet objects as soon as possible.

* Write modular classes to handle database interaction specifics.

* Work with DatabaseMetaData to get information about database functionality.

* Softcode database specific parameters with, for example, properties files.

* Always catch AND handle database warnings and exceptions. Be sure to check for additional pending exceptions.

* Test your code with debug statements to determine the time it takes to execute your query and so on to help in tuning your code. Also use query plan functionality if available.

* Use proper ( and a single standard if possible ) formats, especially for dates.

* Use proper data types for specific kind of data. For example, store birth date as a date type rather than, say, varchar.

*Last but not the least optimize you queries understand from db point of view how the execution plan would be. Identify the queries which executed frequently and could prove bottlenecks.

and the list goes on :)

Some fairly good JDBC Questions 13-24

We have the first list of 1-20 JDBC questions. here we go with another 20 :)

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

Q 14. 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.

Q15. What are the different types of Statements?

Regular statement (use createStatement method), prepared statement (use prepareStatement method) and callable statement (use prepareCall)

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

Q17. 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 = 10 WHERE COF_NAME ='capacinno' ");
updateTotal.executeUpdate();
con.commit();

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

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

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

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 is the difference 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.

Some fairly good JDBC Questions 1-12

I am quite excited with the response from my servlet questions series [Questions 1-25, Questions 26-50, Questions 51-75 ].

So I thought of writing another one on JDBC and here we go ..

Q1. What are four types of JDBC driver?

Type 1 Drivers : Bridge drivers such as the jdbc-odbc bridge. They rely on an intermediary such as ODBC to transfer the SQL calls to the database and also often rely on native code. It is not a serious solution for an application

Type 2 Drivers : Use the existing database API to communicate with the database on the client. Faster than Type 1, but need native code and require additional permissions to work in an applet. Client machine requires software to run.

Type 3 Drivers : JDBC-Net pure Java driver. It translates JDBC calls to a DBMS-independent network protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code.

Type 4 Drivers : Native-protocol pure Java driver. It converts JDBC calls directly into the network protocol used by DBMSs. This allows a direct call from the client machine to the DBMS server. It doesn't need any special native code on the client machine.
Recommended by Sun's tutorial, driver type 1 and 2 are interim solutions where direct pure Java drivers are not yet available. Driver type 3 and 4 are the preferred way to access databases using the JDBC API, because they offer all the advantages of Java technology, including automatic installation. For more info, visit Sun JDBC page

Q2. Which type of JDBC driver is the fastest one?

JDBC Net pure Java driver(Type IV) is the fastest driver because it converts the jdbc calls into vendor specific protocol calls and it directly interacts with the database.

Q3. Are all the required JDBC drivers to establish connectivity to my database part of the JDK?

No. There aren't any JDBC technology-enabled drivers bundled with the Java Platform releases other than the JDBC-ODBC Bridge. So, developers need to get a driver and install it before they can connect to a database.

Q4. Is the JDBC-ODBC Bridge multi-threaded?

No.

Q5. What is the fastest type of JDBC driver?

JDBC driver performance will depend on a number of issues:
(a) the quality of the driver code,
(b) the size of the driver code,
(c) the database server and its load,
(d) network topology,
(e) the number of times your request is translated to a different API.
In general, all things being equal, you can assume that the more your request and response change hands, the slower it will be. This means that Type 1 and Type 3 drivers will be slower than Type 2 drivers (the database calls are make at least three translations versus two), and Type 4 drivers are the fastest (only one translation).

Q6. What causes the "No suitable driver" error?

"No suitable driver" is an error that usually occurs during a call to the DriverManager.getConnection method. The cause can be failing to load the appropriate JDBC drivers before calling the getConnection method, or it can be specifying an invalid JDBC URL--one that isn't recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver.
In addition, when you are using the JDBC-ODBC Bridge, this error can occur if one or more the the shared libraries needed by the Bridge cannot be loaded. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge.

Q7. Why isn't the java.sql.DriverManager class being found?
Some one may ask you questions to confuse you also :) simple answer is the classpath does not contain the necessary jdk jars.

Q8. What are the common steps to execute a Query in JDBC?

Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
Register a driver
Specify a database
Open a database connection
Submit a query
Receive results
Process results

Q9. What are the steps involved in establishing a JDBC connection?
This action involves two steps: loading the JDBC driver and making the connection.

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

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

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