Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

If database connection not open JSP fails

 
Dean Chester
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have written an application that works if the there is a connection open to the database, but if there are no connections open to the database then it fails. If i refresh the page after a failure it works. Has anyone got any suggestions to fix this issue it has been bugging me for some time now. I have moved my program to using a connection pool instead of opening a connection every time hoping that this would resolve the issue yet it hasn't. Another solution that was put to me was to use a cronjob every hour and refresh the webpage yet i don't like this approach. Has anyone got any other suggestions?
Thanks in Advance
Dean
P.S I know that this maybe in the wrong place yet it happens when i try to load a JSP.
 
Leonardo Carreira
Ranch Hand
Posts: 494
Eclipse IDE Java Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi..

Please show me the code that you implemented..
Yes, JSP shouldn't handle Open/Close Database Connection, JSP only used for View (which render/obtain data)..
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64971
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the sort of check that should happen long before the JSP is invoked in the controller.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dean, use listeners to open connection to a database, it will be opened once and the connection will be available at the application level till the server shut downs.

Hope this may fix your problem.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64971
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
anusha chaitanya wrote:Hi Dean, use listeners to open connection to a database

Bad idea.

He's already using a connection pool, which is the right way to go. Holding a single connection open for the duration of an application is one of the worst ways to structure database access in a web app.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote:
anusha chaitanya wrote:Hi Dean, use listeners to open connection to a database

Bad idea.

He's already using a connection pool, which is the right way to go. Holding a single connection open for the duration of an application is one of the worst ways to structure database access in a web app.


Hi Bear, I am reading HFSJs book. So far I completed 8 chapters, from that I learned that it is better if I open a connection using listener. So I suggested him to do that. Sorry, if I given a bad idea.

Thank you for informing me. Can you tell me what is a connection pool? How to do that?

Thank you in advance.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64971
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rather than hijack this post, if you want to know all about connection pooling, you can look up the concept and open a new topic on it.
 
Dean Chester
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is what is in my JSP:

I get data from the bean and then post it on the webpage. I pass an array list as which is erroring as it is saying its empty(when its not if it queried the database). The arraylist is full of years and all i do is sort them so that i can display them as headers in the JSP, like this


That is achieved via a for loop that just counts downwards from the highest value in the arraylist to the lowest value in the arraylist. I have tried just the single connection approach as i was advised to go for by my brother, then this came up so I said I was moving the application to connection pooling. I think that it opens a connection the first time in the pool then the JSP loading fails, next time as that connection is already open it is ok to retrieve the data from the database.

To correct anusha
it will be opened once and the connection will be available at the application level till the server shut downs
Mysql has a default time out on connections of 8 hours then it will close all connections if not in use.

If you need anything else let me know.
Dean Chester
 
Charles 'King
Ranch Hand
Posts: 56
Eclipse IDE Postgres Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Dean, a true connection pool should solve your issue. There are a couple of possible solutions. If your data doesn't change often, you could cache the data in a POJO; then listen for a change to that data and update your cache accordingly. That would eliminate hitting the db often. Additionally, following a DAO -> factory design for pooling your connections is the optimal solution. Your view should be completely separated from the model/data access layer. Meaning, if I replace your entire view (JSP's), I should still be able to access your model without issue. Your JSP should only know if data is not available, e.g. empty list. JSP's should not know if a DB connection is available. If your connection pool is failing, check your DAO layer or RDBMS config files. Maintaining a connection should be a simple process via connection pooling. For example, if you tell the connection pool to grab 30 connections, then thats what you should have before your app is destroyed or shutdown.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic