Datasources On Different Systems

[mindschreck]

Greetings,

I have a question regarding data sources being hosted on different systems.

Imagine that I have a JBoss/Tomcat server. On it, I have all of my beans, servlets, jsps, etc. However, I have my database running on an Oracle database located elsewhere.

If I set up a J2EE container on Oracle, and created the connection pool there, how would my beans, servlets, etc, get access to that connection pool? I am assuming that the data source would be confined to java:/OracleDS (or whatever I named it). How would the applications and components in one container get the resources in another?

Can I do this, or should I stick the connection pool in JBoss and fetch the connections for Oracle from it? Is there any benefit for Oracle hosting the connection pool? If I do want to go with Orcale hosting the data source, do I then have to put my DAOs on Oracle and make them remote objects for my components running on JBoss?

Cheers,
Jason

[jonlee]

In your first scenario, the local JBoss components act like a remote client. You would need the Oracle J2EE container to perform the business logic and use it's database connection pool. It would pass the processed data back to the JBoss located components.

A DataSource, connection pool or similar strategy is meant to improve access performance. The rationale is that connecting to a database and disconnecting to a database is a costly operation for both the database and the remote client.

A connection pool holds open a number of permanent connections to the database server. For a client, this cuts the time to connect and disconnect when performing a transaction. The database server benefits by not spending time connecting and disconnecting, so is has more time actually performing database operations.

When a client requires a database connection, it makes the request to the pool and receives a connection from the pool. Usually this connection is a pre-existing connection, although a pool is usually configured to expand the pool size if the demand requires it. With proper tuning (so the normal number of connections is adequate for demand), you would normally get an existing connection.

You lose this benefit if the component that requires the database connection has to in turn, connect to a remote service to obtain the connection. A remote service in the Java world usually means something external to the local JVM. In essence, you reach the same point you were trying to avoid with the connection pool. Except you now have a case where you have a connection/disconnection to the remote connection pool - which is still an expensive operation, not to mention the delay component in going via the "proxy".

This is why connection pools rarely implement the capability for remote clients to access them - AFAIK, WebLogic is the only exception.

So you obtain the best performance when the actual DataSource/connection pool is implemented in the same JVM as the components that will use it.

Hope that goes some way to answering the questions.