File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Closing Database connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Closing Database connections" Watch "Closing Database connections" New topic
Author

Closing Database connections

Mike Jenkins
Ranch Hand

Joined: Jul 23, 2006
Posts: 57
I have a Pagination using Scriptlet that works great where I am using two database connections to the MySQL database. Now I want to close the connections correctly but cant seem to figure out the best way to do it. I assume for JSP this is okay to open up the database connections the way this program is running but was wondering how do I correctly close the connections.

My attempt below seems to freeze the page where it wont come up.
Please advise because I have tried several attempts of closing the connections but everytime the page doesnt come up. If I get rid of my try/catch/finally blocks the page works great.


[ May 12, 2007: Message edited by: Mike Jenkins ]
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Mike,

you code does not compile anymore if you define ocon etc inside the catch block. Define them before the try block.

Herman
PS: this page is just for studying right?
If not have a look at http://displaytag.sourceforge.net/11/
Mike Jenkins
Ranch Hand

Joined: Jul 23, 2006
Posts: 57
Thanks,

It now works great and thanks for solving my problem. This is a real web page and is not for study. I working with a very small MySQL database that has 1000 records taking up less than 1 MB of space. I cant download any display tag library due to the environment and limitations I am in so I have to manually create this Pagination.
Can someone advise if the way I am doing my Pagination is efficient where I connect to the database 2 times and have to close it 2 times. Is there a better way to do it?
[ May 14, 2007: Message edited by: Mike Jenkins ]
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Mike,

You can use the first connection object ("ocon") for both select-statements, so you don't need the second one (the "con").

If the data in your database doesn't change much, you could store the results in the session-scope from the user or even in the applicationcontext-scope (if you have enough memory for that).
So you just have to do the sql-query once.

Herman

PS: your code would be even more efficient if you would use connection-pooling. Your application-server probably provides that.
Or use the JSP for the view only, instead of using it for backend-stuff

And what about those limitations? Does your company not allow you to use well-tested and well-known open-source software?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Closing Database connections
 
Similar Threads
Unsure how to do subtraction using joined table values
About ResultSet problem!
sql error
Connection Pooling example appears to create more than one pool
insertion of checkbox values from jsp to database