aspose file tools*
The moose likes JDBC and the fly likes PreparedStatement in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement in SQL" Watch "PreparedStatement in SQL" New topic
Author

PreparedStatement in SQL

Phil Harron
Greenhorn

Joined: Jun 21, 2002
Posts: 29
Hello all,
Could anyone please tell me if I use a statement like this i.e.
insertMarketDayTrade = connection.prepareStatement(insertMarketDayTradeString);
insertMarketDayTrade.setString(1, marketRef);
insertMarketDayTrade.setInt(2,tradeVolume);
insertMarketDayTrade.setInt(3,tradePrice);
insertMarketDayTrade.executeUpdate();
insertMarketDayTrade.close();
more than once will the database try to compile the statement again or will it already be aware it has one??
Is there anywhere in SQL where I can view what statements have been compiled?
Thanks for your help.


SCJP 1.4<br />SCJD (Ongoing)
Veena Rani
Ranch Hand

Joined: Mar 09, 2000
Posts: 34
Prameterized preparedStatements are compiled only once by the database engine. If the same prepared statement is executed more than once then the next execution onwards database uses the previously parsed and compiled statement.Because of this it gives better performance than the Statement.
Veena
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

more than once will the database try to compile the statement again or will it already be aware it has one??
It shouldn't try to recompile the statement, but it's really up the database. I guess the best you can do is assume the DB will reuse the Statement and just try to support things from your end...
Is there anywhere in SQL where I can view what statements have been compiled?
Check out the DebuggableStatement from JavaWorld. I'm a big fan - it makes PreparedStatements heaps easier to use.
Dave
Phil Harron
Greenhorn

Joined: Jun 21, 2002
Posts: 29
Thanks for your help - I will try the above.
Regards
Phil
Chiang Guo
Greenhorn

Joined: Aug 06, 2002
Posts: 7
If you want to reuse the same PreparedStatement object, then you should not call close() method, instead call clearParameters() on PreparedStatement object everytime you reuse it.
Otherwise, calling close() is telling JVM to release the object and it won't be able to be referenced again. Some JDBC driver(like oracle) can cache Connection object but I do not know if there is any that caches Statement objects.
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
Originally posted by cguo:
If you want to reuse the same PreparedStatement object, then you should not call close() method, instead call clearParameters() on PreparedStatement object everytime you reuse it.
Otherwise, calling close() is telling JVM to release the object and it won't be able to be referenced again. Some JDBC driver(like oracle) can cache Connection object but I do not know if there is any that caches Statement objects.

It is true that calling the close() method will prevent the OBJECT from being used again. On the other hand, the SQL command is still held by the database, so creating another PreparedStatement object using the same SQL command will still obtain the benefits of the PreparedStatement.
You should not necessarily call clearParameters() everytime you reuse the object. You should only call clearParameters() if you want to erase all the data that has been set for the PreparedStatement. If however, some data is the some from SQL command to SQL command, you can simply call setXXX for the data that has changed. Once you set a placeholder in a PreparedStatement object, that data persists and can be reused until it is reset or until clearParameters() is called.
Chiang Guo
Greenhorn

Joined: Aug 06, 2002
Posts: 7
If objects on database server corresponding to PreparedStatement is resusable is really up to implementation of JDBC driver and dbms.
On the other hand, if a new PreparedStatement is created using a new connection instead of the one previous PreparedStatement is created from, most likely the sql command in database won't get reused, take oracle for example, each connection represents a different database session and sql commands for different sessions are allocated on different memory locations.
 
Don't get me started about those stupid light bulbs.
 
subject: PreparedStatement in SQL