This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes access a database that has limited no of connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "access a database that has limited no of connections" Watch "access a database that has limited no of connections" New topic
Author

access a database that has limited no of connections

Arnav Velimala
Ranch Hand

Joined: Jun 04, 2007
Posts: 37
Hi,

We are having a (client) database from where the data has be fetched and displayed on a dashboard.

To minimize the impact on this client database, the user account will have a limited number(say, 5) of connections allowed. But the no of user who access the
dashboard will be more than 5.

Can anyone please suggest what is the best way to achieve this?
Is it a good idea to create our own database in between and populate with the client data. The data in client database gets generated every month.

in other words,
I've a DB where only 5 connections to it are allowed...but the no of Users accessing that DB might be more than 5.

Database : Oracle 9i
Dashboard is implemented in Java.

Thanks,
Arnav
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19655
    
  18

Use a (non-growing) Connection pool. That does mean that users will have to wait if no connections are available.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30130
    
150

Arnav Velimala wrote:I've a DB where only 5 connections to it are allowed...but the no of Users accessing that DB might be more than 5

It's not the number of users that matters. It how many are requesting something at the same time. As soon as you process a query, close the connection and it returns to the pool. 5 connections can support plenty of users.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Arnav Velimala
Ranch Hand

Joined: Jun 04, 2007
Posts: 37
Hi,

Thank you for the help

Yeah, agree that 5 Connections can serve plenty of Users. And its the *concurrent* users that matter.

This application will be hosted as an intranet site in our organization and will be having users across the globe.
Assuming that there will be more than 5 concurrent users, how should I tackle?

Also the data in the database will be changed on *monthly* basis. The total number of records in the table as of now is 6980 and it can grow maximum 10K.

Is it a better idea to cache all the records... storing in memory or flat file on the machine(where the program runs).

Please suggest.

thanks
arnav
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


This application will be hosted as an intranet site in our organization and will be having users across the globe.
Assuming that there will be more than 5 concurrent users, how should I tackle?

Speak to your DBA about providing more connections?


Also the data in the database will be changed on *monthly* basis. The total number of records in the table as of now is 6980 and it can grow maximum 10K.

Is it a better idea to cache all the records... storing in memory or flat file on the machine(where the program runs).

So is this database read-only as far as users of your application are concerned?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Carlos Angelim
Greenhorn

Joined: Jul 23, 2009
Posts: 4
Hi,

Can't it just be solved with a connection pool configured for a maximum of 5 connections?

For example, Tomcat uses DBCP as source for database connection pool. Here's a simple configuration for connecting to an Oracle database (Express) running on localhost, using a maximum of 5 connections:




In case you're not using a JEE container, you could put dbcp and its dependencies on the classpath and initialize it by code:

Arnav Velimala
Ranch Hand

Joined: Jun 04, 2007
Posts: 37
So is this database read-only as far as users of your application are concerned?


Yes Paul.

Carlos, Thank you very much for your reply. I was not aware that one can manually pool dataSource.
BTW, welcome to JavaRanch.
Keep Replying/Posting.

thanks
Arnav
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

If it is entirely read only I'd just cache it somewhere in your application. Then you only need one connection at startup time. You will however need to restart your application every month, is this likely to be an issue?
Carlos Angelim
Greenhorn

Joined: Jul 23, 2009
Posts: 4
Thanks for the welcoming


I think you can combine the connection pool with Paul's tip on the read-only characteristic you have and achieve a high performace connectiveness with automatic load balancing.

If you configure the connection pool by code, you'll need to pass additional options (props.setProperty(...)) to enforce your requiremets. It will become something like this:


Don't worry calling close() on the connection as dbcp will know how to handle it.

Cheers,
Carlos
Carlos Angelim
Greenhorn

Joined: Jul 23, 2009
Posts: 4
I recently revised the code and noted that the correct DBCP class using the Properties parameter was BasicDataSourceFactory. So, I changed my code to handle it correctly.



Cheers
Carlos
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: access a database that has limited no of connections
 
Similar Threads
HashMap (or sth else) to lock/unlock records : Is it really safe?
CHANGE HIBERNATE PROPERTIES ON THE FLY
No of allowed connections
Concurrent Request Processing in jBoss - Not working
Connection Pool Authentication