Forums Register Login

PreparedStatement in SQL

+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
Thanks for your help - I will try the above.
Regards
Phil
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
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.
a fool thinks himself to be wise, but a wise man knows himself to be a fool - shakespeare. foolish tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 1382 times.
Similar Threads
Servlet Displays Old DB Entries
JDBC and Stored Procedure
JSTL SQL tag related ..issue
desc command in java
SQL return substrings
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 07:35:37.