Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement in SQL

 
Phil Harron
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Veena Rani
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your help - I will try the above.
Regards
Phil
 
Chiang Guo
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic