Hi All, I am facing a problem which I am unbale to find root cause. But I think it is because of way I am querying DB. Here is my approach. I have singleton class that is responsible for handling all DB queries and is using Datasource. DS in instance variable and I am initializing it in static Initializer. Now I have to query depending upon userType and no of columns in result may vary.. like if(user_type=1) { //query to get 4 columns } else if(user_type=2) { //query to get 6 columns } and closing result sets. statements and connections in that order. Problem is most of the times query executes fine and occasionaly it throws some DB related exception like No more data available or outof bound exception. Is there ne problem in having singleton class and data source as instance variable ? Also I am getting connection from data source for each query and closing thereafter. Right now only one user is active so there is no question of simultaneous access. Pls drive me out . Also pls give ur feedback regarding pros and cons in above approach. TIA Manohar
There has beena lot of discussion of the problems of using a Singleton for this sort of thing in the forum before. I strongly recommend that you search for "singleton" and read some of the recent threads. In my experience, Singleton should hardly ever be used. Can you tell us more about the context of your problem? Maybe we can help by suggesting alternative ways of solving your overall problem without needing a Singleton.
Hi Frank, I appreciate your response. Let me tell why I went for singleton approach. I am using Datasource for pooling connections. I have to lookup for a Datasource which demands some performance overhead. So I am doing lookup operation once for all and using same DS for further queries. (However I am creating new conenctions and statements for every query and these are maintained as local variables) Secondly, As System involved querying more often overhead because of instatiating this DB Acess class will be minimized. There are no other member variables except Datasource and all other things like connection etc,, are maintained as local variables to avoid synchronization issues And all connections and related things are closed as soon as their usage is over.. Ne comments .. Thanks Manohar
If everything is as you describe, I can't imagine that it's the singleton that's causing your issues. Are you passing the connection or the statement object outside the singleton at any point? Is there a possibility that one or the other of these is not closed in all cases (e.g. are you doing a close() of both objects in a finally clause?) Kyle
Hi all, Thanks for your help. I resolved problem and this is not because of patterns or bugs in my application but bugs in App server.. I installed SP and now everything is fine. Even this solution is given by one of the Javaranchs. Manohar
I'm so happy! And I wish to make this tiny ad happy too:
a bit of art, as a gift, the permaculture playing cards