Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Update in a method Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update in a method" Watch "Update in a method" New topic
Author

Update in a method

Anupam Sinha
Ranch Hand

Joined: Apr 13, 2003
Posts: 1088
Hi All

I was thinking would it be a wise idea to put the code that updates the database in a method and then call that method whenever required. Same can be done for delete, etc. Parameters to the method may be like a connection ref., tablename and query string. I just wanted to know whether its a wise idea or can you foresee any problems I may encounter.

Can this also be used for select query I may have to return the resultset?
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Hi Anupam
Based on your design you need to take a decision.

1. Most people prefer to keep java code and sql business logic seperate. inorder to do that they use store procedure.

2. Second way could be to have your update statement in a java constant file. Only the paramaters can be dynamic which could be passed in the method at runtime. See that you use prepared statement when implementing this design.

About connection object, i would not prefer to pass it across methods because this way again i have to take care of exception handling at all the places where i pass the java.sql objects. I would suggest you to use connection pooling, create a class which looks up the pool and returns you the connection. In the method, call your own connection class method which will give you a connection, work on the sql call and then return the connection. The return of the connection should be the same way as you got the conenction.


Let me know if it makes sense.
More inputs and suggestions are welcomed

Regards
Makarand Parab
Anupam Sinha
Ranch Hand

Joined: Apr 13, 2003
Posts: 1088
Hi Makarand

Thanks for such a speedy reply. I would search more about connection pooling. I also wanted to know whether there would be a peformance hit incase I use methods instead of straight away writing the code or would the Java compiler be smart enough to inline the code and not repeatedly call the method. I would do some benchmarks and let you know. I will also google on connection pooling.

Thanks for your input.
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Which database and application server are you using. If it is oracle and IBM websphere, let me know. I can help you out.


Regards
Makarand Parab
Anupam Sinha
Ranch Hand

Joined: Apr 13, 2003
Posts: 1088
I would be using java class and MS Server. Tomcat is what we use as the web server. I am just thinking about this idea its not a requirement as of now.
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Okay you are using tomcat, check if it provides connection pooling facility. Mostly application server provide this facility. Morever i haven't worked with MS SQL server. But let me know if you face problem. If you are not able to work with connection pooling, if tomcat doesn't support it, you can go for direct connection using DriverManager class in java.sql.*. But see that you release the connection no sooner you have used it. If you forget to release the connection at even one place, the whole application will hang/crash after some time.

Keep me posted and let me know if you need any help.

Regards
Makarand Parab
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Originally posted by Anupam Sinha:
Hi All

I was thinking would it be a wise idea to put the code that updates the database in a method and then call that method whenever required. Same can be done for delete, etc. Parameters to the method may be like a connection ref., tablename and query string. I just wanted to know whether its a wise idea or can you foresee any problems I may encounter.

Can this also be used for select query I may have to return the resultset?


Anupam, I do not know in what context you are writing this code.
In general, if you follow OO design, you will want to encapsulate the access to the database in an object.

Writing a method that accepts a Connection, tablename etc. is very procedural, which might be ok if that is what you want to do. If you are talking OO, you will want to have a class that can query/update the database, and talk to other classes in terms of Java objects instead of Database specific constructs like a ResultSet. In a class like that you may have a method that accepts the query criteria, executes a statement against the DB, iterates through the ResultSet, and returns a List of Objects populated from the data returned. Similary, another method that accepts an object as an argument, creates a SQL query based on it to insert or update a record in the database. And so on...

I suggest that you read up on DAO pattern, that will give you a lot to think about on how to structure your data access code.

HTH


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Anupam Sinha
Ranch Hand

Joined: Apr 13, 2003
Posts: 1088
Hi Sonny

Thanks for the help. We are using connection pool here. That was something that I wanted to do for my personal understanding.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update in a method
 
Similar Threads
Web Services Question of the Day..
Update more than one rows through one Submit/Update Button
java.sql.SQLException: Closed Connection
regarding abstract
scope in production support/maintenance projects