• Post Reply Bookmark Topic Watch Topic
  • New Topic

Prepared JDBC statements in Servlets?  RSS feed

 
Carty Ellis
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is a "good" way to develop prepared statements in Servlets? My specific concern is the date in a JDBC statement. I need the servlet to work with Oracle, SQL Server and Sybase - hence the JDBC, but the date specifically needs to be "prepared".
 
Jeroen Wenting
Ranch Hand
Posts: 5093
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best way would be to put the exact formatting strings to be used into a ResourceBundle per database server and read them from there.
I'd go further and pull the actual SQL queries into an external file as well (I used XML for my mini-framework, which also contained data on things like datatypes of parameters etc.).
You can then either ship just the files for the platform your client is using or ship them all and determine at runtime which files to load based on the configured datastore.
 
Zkr Ryz
Ranch Hand
Posts: 187
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Carty Ellis:
What is a "good" way to develop prepared statements in Servlets? My specific concern is the date in a JDBC statement. I need the servlet to work with Oracle, SQL Server and Sybase - hence the JDBC, but the date specifically needs to be "prepared".

I think you shouldn't , I suggest you to use a separate class to access the database and let the servlet deal with the request/response part. Anyway you'll do this for your self when your servlet start to grow considerabily and become harder to matain.
About the prepared statement I have use the PreparedStatement.setTimestamp() method with the three databases having succesful results ( didn't I ? ).
 
Bryan Mac
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Probably what you want is a combination of the Factory, Singleton and Layered initialization patterns. You would have a DataQuery object request some implementation (DataQueryImplFactory) of a DataQueryFactory Interface to create a specific implementation (JDBCQuery, OracleQuery, DB2Query, etc..) of a DataQueryImpl Interface. Any of several mechanisms would make the factory type aware of the proper DataQueryImpl to create including <datasource> , <res-ref> etc.. entries in the web.xml document, some application specific properties etc.. The DataQueryFactory would do enough initial work to allow it to create the proper DataQueryImpl object, the DataQueryImpl object would then do enough initialzation work to pass back to the DataqueryFactoryImpl any additional info needed to complete initialization. Actually I would probably have the DataQuery sent to a singleton implementation of a DomainSpecificDBManager interface that would deal with platform specific implementations of DataQueryImpl interfaces as well as connections etc...
 
Ernest Friedman-Hill
author and iconoclast
Sheriff
Posts: 24217
38
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bryan,
Welcome to JavaRanch!
We don't have too many rules around here, but we do have our naming policy which requires that your display name consist of a first and last name. Please go here and update yours, pronto! Thanks, pardner.
[ April 13, 2004: Message edited by: Ernest Friedman-Hill ]
 
Carty Ellis
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In lots of further digging, I finally came upon JDBC escape sequences. That is the answer. I want code that is truly data base INSENSITIVE - very importnat to my application. Thanks for the replies.
 
Nicholas Cheung
Ranch Hand
Posts: 4982
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In order to cater for JDBC with different DBs, you may adopt the DAO pattern, so that each DAO class only handles 1 vendor of DB.
It makes your codes cleaner, and it is more easy for other to trace it.
Nick
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!