access a database that has limited no of connections

[Arnav Velimala]

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]

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

[Jeanne Boyarsky]

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]

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]

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]

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:

<Resource name="LocalDB" 
               auth="Container" type="javax.sql.DataSource"
               maxActive="5" maxIdle="1" maxWait="1000"
               username="carlos" password="carlos" 
               driverClassName="oracle.jdbc.OracleDriver"
               url="jdbc:oracle:thin:@localhost:1521:XE"
               removeAbandoned="true" removeAbandonedTimeout="60"
               logAbandoned="true"
               defaultAutoCommit="false"
               validationQuery="select 0 from dual"
 	  />

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

	public static DataSource createDbcpDataSource(String connUrl, String user,
			String pass, String dontCareThisValue) {
		// BasicDataSource ds = new BasicDataSource();
		// ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		// ds.setUsername(user);
		// ds.setPassword(pass);
		// ds.setUrl(connUrl);

		ObjectPool connectionPool = new GenericObjectPool(null);
		
		
		Properties props = new Properties();
		props.setProperty("user", user);
		props.setProperty("password", pass);
		props.setProperty("defaultAutoCommit", "false");
		props.setProperty("maxWait", "1000");//ms
		ConnectionFactory cf = new DriverManagerConnectionFactory(
				connUrl, props);

		// PoolableConnectionFactory poolableConnectionFactory =
		new PoolableConnectionFactory(cf, connectionPool, null,
				null, false, true);

		//
		// Finally, we create the PoolingDriver itself,
		// passing in the object pool we created.
		//
		PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
		dataSource.setLogWriter(new PrintWriter(System.out));
		//dataSource.setLoginTimeout(2);

		return dataSource;

	}

[Arnav Velimala]

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]

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]

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:

		Connection remoteConn = null;
		PreparedStatement p = null;
		try {
			remoteConn = getConnection(); //from the datasource

			p = remoteConn.prepareStatement(q, ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			
			ResultSet rs = p.executeQuery();
			rs.setFetchSize(10000);

			while (rs.next()) {
			   ...
			}

			return publishedItems;

		} finally {
			try {
				p.close();
			} catch (Exception e) {
			}
			try {
				remoteConn.close();
			} catch (Exception e) {
			}
		}

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

Cheers,
Carlos

[Carlos Angelim]

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.

    
	public static DataSource createDbcpDataSource(Properties props) {
		try {
			return BasicDataSourceFactory.createDataSource(props);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

Cheers
Carlos