Statement, Prepared Statement, and CallableStatement

Hi all,
Could anyone explain me, what is the difference between Statement, Prepared Statement, and CallableStatement. I have read one thing that, Prapared Statements are precompiled statements, what does it mean?

Any help would be appreciated.
Thanx in advance.

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 stmt :

Statement stmt = con.createStatement();

At this point stmt exists, but it does not have an SQL statement to pass on to the DBMS. We need to supply that to the method we use to execute stmt . For example, in the following code fragment, we supply executeUpdate with the SQL statement from the example above:

stmt.executeUpdate("CREATE TABLE COFFEES " +
"(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
"SALES INTEGER, TOTAL INTEGER)");

Using a Prepared Statement
If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead.

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. 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.

Although PreparedStatement objects can be used for SQL statements with no parameters, you will probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. You will see an example of this in the following sections.

A prepared statement should be used in cases where a particular SQL statement is used frequently. The prepared statement is more expensive to set up but executes faster than a statement. This example demonstrates a prepared statement for getting all rows from a table called ''mytable'' whose column COL_A equals ''Patrick Chan''. This example also demonstrates a prepared statement for updating data in the table. In particular, for all rows whose column COL_B equals 123, column COL_A is set to ''John Doe''.

try {

// Retrieving rows from the database.

PreparedStatement stmt = connection.prepareStatement(

"SELECT * FROM mytable WHERE COL_A = ?");

int colunm = 1;

stmt.setString(colunm, "Patrick Chan");

ResultSet rs = stmt.executeQuery();

// Updating the database.

stmt = connection.prepareStatement(

"UPDATE mytable SET COL_A = ? WHERE COL_B = ?");

colunm = 1;

stmt.setString(colunm, "John Doe");

colunm = 2;

stmt.setInt(colunm, 123);

int numUpdated = stmt.executeUpdate();

} catch (SQLException e) {

}

(Examplets TM provided by permission of the publisher, Addision-Wesley, and Author Patrick Chan. )

JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself.

"pravin_india",

We're pleased to have you here with us here on the Ranch, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
Forum Bartender

Thanx Hareesh, it was really good explaination, and looking forward your help in future also.

"pravin s",
Note that we require a last name and not just a last initial. Please update your display name to include a last name.

Thanks,
Jeanne
JDBC Forum Bartender
[ February 10, 2005: Message edited by: Jeanne Boyarsky ]

welcome pravin

"pravin java"
And the last name can't be obviously made up.

I appreciate your cooperation in this.

Thanks,
Jeanne