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.
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.
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:
Joined: Jun 04, 2007
So is this database read-only as far as users of your application are concerned?
Carlos, Thank you very much for your reply. I was not aware that one can manually pool dataSource.
BTW, welcome to JavaRanch.
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?
Joined: Jul 23, 2009
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.
Joined: Jul 23, 2009
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.