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

access a database that has limited no of connections

 
Arnav Velimala
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20550
57
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use a (non-growing) Connection pool. That does mean that users will have to wait if no connections are available.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34671
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Arnav Velimala
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?
 
Carlos Angelim
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic