Scaling PreparedStatement Update DB?

[Michael Cropper]

Previously when I was using a normal Statement object to update DB I created a class "UpdateDatabase" which took arguments of (tablename, setdata, wheredata) which I then used to build the SQL statement.

So my question is, when using PreparedStatements instead, is it is a good or a bad idea to do something similar?

Since I have two options

Option 1
Build a separate class for each specific update statement I want to run.

Option 2
Build one flexible class which takes the arguments as my previous one did.

So the question is, which is the best option technically, and why?

Thanks
Michael

[Haina Minawa]

Michael Cropper wrote:Previously when I was using a normal Statement object to update DB I created a class "UpdateDatabase" which took arguments of (tablename, setdata, wheredata) which I then used to build the SQL statement.

So my question is, when using PreparedStatements instead, is it is a good or a bad idea to do something similar?

Since I have two options

Option 1
Build a separate class for each specific update statement I want to run.

Option 2
Build one flexible class which takes the arguments as my previous one did.

So the question is, which is the best option technically, and why?

Thanks
Michael

In my opinion, it's hard to tell which way is the best, but the option 2 is more flexible, using PreparedStatement.

[Bear Bibeault]

Please take the time to choose an appropriate forum for your posts. This forum is for questions on Servlets, not JDBC. For more information, please click this link ⇒ CarefullyChooseOneForum.

This post has been moved to a more appropriate forum.

[Michael Cropper]

I think I have just answered my own question. It doesn't seem like it is possible to build a flexible script using the same approach that I was taking before.

When I created a new class and used the following within a prepared statement it didn't quite work.

Class...

Update(String tablename, String setdata, String wheredata)

PreparedStatement bit....

String updateString = "update " + dbName + ". ? set ? where ?";

What happened is that when I was replacing the ?'s with the actual data from the arguments here is what was attempted to be put to the DB

Update 'tablename' set 'field = something' where 'field2 = somethingelse'

So it wrapped the ' ' around the whole section and not just the actual data that is to be updated.

Was worth a go, but if this is how PreparedStatements behave, then I guess I will have to create a more specific class each time I need to interact with the DB (which is potentially a better option longer term mind)

Thanks
Michael

[Paul Clapham]

Yes, writing generic database-updating code is generally a waste of time. It's one of those things which looks like a good idea at first but when you find out all of the details which said "generic" code has to deal with, it turns out to be not such a good idea.

[Paul Clapham]

... however, I believe that creating a separate class for each SQL query is a reaction which goes too far in the opposite direction. I tend to write a class which groups together all of the SQL queries related to a particular type of object.