File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB and other Java EE Technologies and the fly likes Database connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Database connection" Watch "Database connection" New topic
Author

Database connection

Eric Lim
Greenhorn

Joined: Apr 01, 2002
Posts: 19
Hi,
Is it safe to pass a Database Connection object from an EJB to any of its clients? For example, if I have a method in my EJB called getConnection() that returns a database connection, can the Connection object be safely passed or returned by value to the calling client?
Thanks for any help.
eric
Simon Brown
sharp shooter, and author
Ranch Hand

Joined: May 10, 2000
Posts: 1913
    
    6
I'm not sure on a "complete answer" for this, but I imagine that it depends on the JDBC driver that you are using.
One thing to have a think about is why you would pass the connection outside of an EJB. If you have a "service locator" type object and you want the connection to be located by a central component then this is probably okay - provided that you can ensure what will happen when you run in a cluster. A good solution here might be to use EJB 2.0 local interfaces.
The potential problem with passing connections around, and possibly outside of the EJB tier, is that it can easily break the layering and architecture of your system. For example, clients having access to the database means that it is possible to bypass all of the hard work that you've put into building the EJBs.
Just my 2 cents...
Simon
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
Originally posted by Eric Lim:
Hi,
Is it safe to pass a Database Connection object from an EJB to any of its clients? For example, if I have a method in my EJB called getConnection() that returns a database connection, can the Connection object be safely passed or returned by value to the calling client?
Thanks for any help.
eric

The answer is no. Remember that only Serializable objects can be returned from an EJB method -- database connections aren't serializable. Now -- why would you want to do that?
Kyle


Kyle Brown, Author of Persistence in the Enterprise and Enterprise Java Programming with IBM Websphere, 2nd Edition
See my homepage at http://www.kyle-brown.com/ for other WebSphere information.
Eric Lim
Greenhorn

Joined: Apr 01, 2002
Posts: 19
Thanks for all your reply.
The situation is that we have an existing application that we migrated to a J2EE compliant application server (iPlanet). We have an EJB that encapsulates all accesses to the database and one of the methods in this EJB is getConnection() which returns a Key object (that implements Serializable).
The EJB manages which Connection belongs to which Key. The client only maintains the Key and controls the transaction boundary. This means that if the client is ready to commit all changes to the database, it can then call commit(Key key) method in the EJB passing the Key. The EJB then locates the appropriate connection for that key and commits the database changes. This was the architecture we inherited from the old application.
But the problem is that the EJB maintains the list of Key/Connection entries in a static field in the stateless EJB. This is good only if the application server loads only one copy of the class but in our configuration the app server actually clusters several EJB servers running in different JVMs and each loads its own copy of the EJB class. This makes the static Key/Connection list a potential problem as it is not guaranteed that a set of transactions will always be served by the same instance of the EJB server.
In order to solve this problem without major impact to the existing application architecture, we decided to encapsulate the Connection object in the Key that is passed to the client and therefore eliminating the need for the EJB to maintain the Key/Connection list. The client doesn't have to know that a Connection object exists in the Key (e.g. making it a package scope) it receives from the EJB. When it passes back the Key to the EJB to commit the transaction for example, the EJB will get the connection object straight from the Key. This way, it doesn't matter which EJB server instance we end up with.
There will be no issue with Connection being not Serializable because it is encapsulated inside a Key class which implements Serializable.
Given the above scenario, does this make sense to you?
thanks
eric
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16065
    
  21

Urk. I'm not sure I got all that. Then again, I'm not sure I WANT to!
If a serializable class incorporates a non-serializable item, then it is no longer serializable just as a matter of course. The only way it can be is if it is aware it is being serialized and can dynamically recreate the missing items. And even then, it's not entirely trustworthy, since the re-synthesis could reference code that has changed, resulting in something completely different.
More to the point, java.sql.Connection is an interface, and interfaces are not serializable at all - though sometimes their implementating classes are. But there's no warranty expressed or implied, and if you're clustering or otherwise attempting cross-VM operations, there's some pretty good reasons why you'd NOT want your connection serializable from a jdbc designer's point of view.
There's a simpler alternative. Or at least usually simpler. If you are using Entity EJBs, the finder methods can return collections of primary keys - which ARE serializable and MUST be according to the spec. For Entity EJBs, the Connection is handled (presumably efficiently) by the container, as is caching.


Customer surveys are for companies who didn't pay proper attention to begin with.
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
There is a MUCH MUCH easier solution to your problem.
Remember the joke that says "Doc, it hurts when I do this!" So the Doctor says "Don't do that!"?
Don't do that. You're problem is you are trying to implement connection pooling on your own. But the container already DOES that. So track down and KILL all references in your code to where you call your getConnection() EJB method. Replace them with a standard call to a static method on a Plain old Java object that does this:
public static Connection getConnection() {
InitialContext ctx = new InitialContext();
DataSource source = (DataSource) ctx.lookup("jdbc/MyDatasource");
return source.getConnection();
}
I've left out the exception handling logic for space, but you get the idea. Just make sure that every time you need a connection that you get it the "right" way in J2EE as above. Also make sure that you always send close() to each connection when you're done with it.
Also let the standard EJB transaction management do your database commits and rollbacks for you. Every connection that you've enlisted in a transaction will be automatically committed or rolled back as appropriate. You don't have to do this programatically (or if you want to control it programatically, use the standard JTA interfaces). Yes, this means that you'll have to rewrite the code, but that's the point, isn't it!??
Kyle
[ April 23, 2002: Message edited by: Kyle Brown ]
Dieter Cailliau
Greenhorn

Joined: Jan 17, 2002
Posts: 11
This is the problem:
there is no use in saving the key-connection mapping in a stateless ejb: why is it necessairy to assure that one client receives the same connection, if the ejb is designed stateless? Stateless means there can be no relation between subsequent calls, of any kind.
If it is needed to remember the right connection for the right client, you have to use a statefull session bean. The connection would be created when the session ejb is created, and closed when the session bean is removed. If your client holds a reference to its statefull session bean, then the same connection will be used for that client, evrey time he calls a method.
If it is not required to give the same connection, you have to acquire the connection in the beginning of the method and close it in the end of the method.
I think a statfull session bean that does not release it's connection during it's entire life, is the worst scalable design you can create in ejb: not a good idea. Of course, in eai, sometimes you have no choice.
Eric Lim
Greenhorn

Joined: Apr 01, 2002
Posts: 19
Thanks for all your input. I missed the point that wrapping a non-serializable class in a serializable class doesn't make it serializable.
thanks
Giri Prasad
Ranch Hand

Joined: Mar 08, 2001
Posts: 56
Hi Kyle
About the dataSource you are reffering to:
Ia m using oracle thin driver to create a data Source in WSDStudio. When I am trying to get connection in my bean using the datasource I got the error
java.sql.SQLException: CONM7005E: The class (oracle.jdbc.driver.OracleDriver) does not implement javax.sql.ConnectionPoolDataSource or javax.sql.XADataSource
Can you help on this?
thank you
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
Originally posted by Giri Prasad:
Hi Kyle
About the dataSource you are reffering to:
Ia m using oracle thin driver to create a data Source in WSDStudio. When I am trying to get connection in my bean using the datasource I got the error
java.sql.SQLException: CONM7005E: The class (oracle.jdbc.driver.OracleDriver) does not implement javax.sql.ConnectionPoolDataSource or javax.sql.XADataSource
Can you help on this?
thank you

I'm going to need more information on this one. What have you done to configure the Oracle Driver, and add the datasource, and what does your client code look like?
Kyle
Giri Prasad
Ranch Hand

Joined: Mar 08, 2001
Posts: 56
Hi kyle
These are the steps
1. Open server_cfg.xml with WebSphere Configuration Editor , go to Data Source tab
2. Add a new JDBC driver
name: test
Inplementation Class: oracle.jdbc.driver.OracleDriver
ClassPath : path to the classes102.zip
3.Add new dataSource:
name : Oracle Data Source
JNDINAME : OracleDS
4. look for the dataSource using jndi and get connection
private Connection getConnection()
throws SQLException{
try{
Context jndiCntx = new InitialContext();
DataSource ds =
(DataSource)jndiCntx.lookup("OracleDS");
return ds.getConnection();
}catch(NamingException ne){
ne.printStackTrace()
}
It is giving the error
java.sql.SQLException: CONM7005E: The class (oracle.jdbc.driver.OracleDriver) does not implement javax.sql.ConnectionPoolDataSource or javax.sql.XADataSource
What I an not sure of is the Implementation class in step two
Giri Prasad
Ranch Hand

Joined: Mar 08, 2001
Posts: 56
Hi Kyle
I missed one step
I even binded with data source to EJB using the exb editor for ejb-jar.xml
Kyle Brown
author
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
    
    5
I think the classname you should use is:
oracle.jdbc.pool.OracleConnectionPoolDataSource
Also, by convention, all datasource names should start with jdbc/.
Kyle
Giri Prasad
Ranch Hand

Joined: Mar 08, 2001
Posts: 56
Hi Kyle
I used the implemention class u said. Now I am getting ClassNotFoundException
Here is the exception when I am trying to get the connection(after getting the DataSource abject using JNDI)
[4/26/02 15:38:04:147 PDT] 20ba2969 DataSourceFac X CONM1006E: The data source class name "oracle.jdbc.pool.OracleConnectionPoolDataSource" could not be found for data source (jdbc/OracleDataSource).
[4/26/02 15:38:04:225 PDT] 20ba2969 Helpers W NMSV0605E: A Reference object looked up from the context "domainRoots/UnspecifiedDomainName/legacyRoot" with the name "jdbc/OracleDS" was sent to the JNDI Naming Manager and an exception resulted.
Reference data follows:
Reference Factory Class Name: com.ibm.websphere.advanced.cm.factory.DataSourceFactory$ResourceReferenceObjectFactory
Reference Factory Class Location URLs: <null>
Reference Class Name: com.ibm.ejs.cm.portability.PortableDataSource
Type: name
Content: jdbc/OracleDataSource
Type: minimumPoolSize
Content: 1
Type: maximumPoolSize
Content: 10
Type: connectionTimeout
Content: 180
Type: idleTimeout
Content: 1800
Type: orphanTimeout
Content: 1800
Type: statementCacheSize
Content: 100
Type: disableAutoConnectionCleanup
Content: false
Type: oemId
Content: WebSphereSequeLinkClient
Type: informixLockModeWait
Content: 0
Type: oracleStmtCacheSize
Content: 0
Type: disable2Phase
Content: false
Type: dataSourceClassName
Content: oracle.jdbc.pool.OracleConnectionPoolDataSource
Type: user
Content: scott
Type: password
Content: {xor}LCg9
Type: databaseName
Content: orcl
Exception data follows:
javax.naming.NamingException: ClassNotFoundException: oracle.jdbc.pool.OracleConnectionPoolDataSource
at com.ibm.websphere.advanced.cm.factory.DataSourceFactory$ResourceReferenceObjectFactory.getObjectInstance(DataSourceFactory.java:705)
at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:308)
at com.ibm.ws.naming.util.Helpers.processSerializedObjectForLookup(Helpers.java:737)
at com.ibm.ejs.ns.jndi.CNContextImpl.cacheLookup(CNContextImpl.java:2682)
at com.ibm.ejs.ns.jndi.CNContextImpl.doLookup(CNContextImpl.java:1415)
at com.ibm.ejs.ns.jndi.CNContextImpl.lookup(CNContextImpl.java:1120)
at com.ibm.ws.naming.util.WsnInitCtx.lookup(WsnInitCtx.java:128)
at javax.naming.InitialContext.lookup(InitialContext.java:351)
at com.sbc.tis.LcaACESessionBean.getConnection(LcaACESessionBean.java:55)
at com.sbc.tis.LcaACESessionBean.ACE_activate_user(LcaACESessionBean.java:87)
at com.sbc.tis.EJSRemoteStatelessLcaACESession.ACE_activate_user(EJSRemoteStatelessLcaACESession.java:25)
at com.sbc.tis._LcaACESession_Stub.ACE_activate_user(_LcaACESession_Stub.java:258)
at com.sbc.tis.LcaTISSessionBean.TIS_activate_user(LcaTISSessionBean.java:124)
at com.sbc.tis.EJSRemoteStatelessLcaTISSession.TIS_activate_user(EJSRemoteStatelessLcaTISSession.java:25)
at com.sbc.tis._LcaTISSession_Stub.TIS_activate_user(_LcaTISSession_Stub.java:257)
at java.lang.reflect.Method.invoke(Native Method)
at com.ibm.etools.utc.model.ReflectionMethodModel.invoke(ReflectionMethodModel.java:68)
at com.ibm.etools.utc.servlet.InvokeServlet.invoke(InvokeServlet.java:104)
at com.ibm.etools.utc.servlet.InvokeServlet.doPost(InvokeServlet.java:208)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.servlet.engine.webapp.StrictServletInstance.doService(ServletManager.java:827)
at com.ibm.servlet.engine.webapp.StrictLifecycleServlet._service(StrictLifecycleServlet.java:167)
at com.ibm.servlet.engine.webapp.IdleServletState.service(StrictLifecycleServlet.java:297)
at com.ibm.servlet.engine.webapp.StrictLifecycleServlet.service(StrictLifecycleServlet.java:110)
at com.ibm.servlet.engine.webapp.ServletInstance.service(ServletManager.java:472)
at com.ibm.servlet.engine.webapp.ValidServletReferenceState.dispatch(ServletManager.java:1012)
at com.ibm.servlet.engine.webapp.ServletInstanceReference.dispatch(ServletManager.java:913)
at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:523)
at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:282)
at com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:112)
at com.ibm.servlet.engine.srt.WebAppInvoker.doForward(WebAppInvoker.java:91)
at com.ibm.servlet.engine.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:184)
at com.ibm.servlet.engine.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:67)
at com.ibm.servlet.engine.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:106)
at com.ibm.servlet.engine.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:125)
at com.ibm.servlet.engine.oselistener.OSEListenerDispatcher.service(OSEListener.java:315)
at com.ibm.servlet.engine.http11.HttpConnection.handleRequest(HttpConnection.java:60)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:323)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:252)
at com.ibm.ws.util.CachedThread.run(ThreadPool.java:122)
Giri Prasad
Ranch Hand

Joined: Mar 08, 2001
Posts: 56
I replaced the classes102.zip with the classes12.zip . Here is the info i am using to add the driver
Name: test
Description : description
Inplementation class: oracle.jdbc.pool.OracleConnectionPoolDataSource
Classpath: path where is classes12.zip is located.
Now I am getting the error:

[4/26/02 18:12:44:131 PDT] 4396bc99 WebGroup X Servlet Error: oracle.jdbc.dbaccess.DBError: method check_error(ILjava/lang/Object;)V not found: java.lang.NoSuchMethodError: oracle.jdbc.dbaccess.DBError: method check_error(ILjava/lang/Object V not found
at oracle.jdbc.pool.OracleDataSource.makeURL(OracleDataSource.java:422)
at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:105)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPhysicalConnection(OracleConnectionPoolDataSource.java:98)
at oracle.jdbc.pool.OracleConnectionPoolDataSource.getPooledConnection(OracleConnectionPoolDataSource.java:77)
at com.ibm.ejs.cm.pool.JDBC1xConnectionFactory.createConnection(JDBC1xConnectionFactory.java:42)
at com.ibm.ejs.cm.pool.ConnectionPool.createConnection(ConnectionPool.java:954)
at com.ibm.ejs.cm.pool.ConnectionPool.createOrWaitForConnection(ConnectionPool.java:896)
at com.ibm.ejs.cm.pool.ConnectionPool.findFreeConnection(ConnectionPool.java:814)
at com.ibm.ejs.cm.pool.ConnectionPool.findConnectionForTx(ConnectionPool.java:720)
at com.ibm.ejs.cm.pool.ConnectionPool.allocateConnection(ConnectionPool.java:654)
at com.ibm.ejs.cm.pool.ConnectionPool.getConnection(ConnectionPool.java:307)
at com.ibm.ejs.cm.DataSourceImpl$1.run(DataSourceImpl.java:122)
at java.security.AccessController.doPrivileged(Native Method)
at com.ibm.ejs.cm.DataSourceImpl.getConnection(DataSourceImpl.java:120)
at com.ibm.ejs.cm.DataSourceImpl.getConnection(DataSourceImpl.java:100)
at com.ibm.etools.utc.view.DataSourceObject.<init>(DataSourceObject.java:34)
Any Help !!!
sandy gupta
Ranch Hand

Joined: Jan 30, 2001
Posts: 228
Hi Giri,
First Thing get a tool called WINRAR, that would help u have a look into the jars that u are using( the classes that are packed in there in the package level ).
Now for your problem, it seems as if one of the classes that u are using is missing and more often than not what happens is that the DB API that you use is not compliant with your JRE classes. Please make sure that the driver that u are using is compliant with your JRE API....Use the tool above to find out whether the classes are there in the jars that you have....
HTH
Sahil


Adios
Giri Prasad
Ranch Hand

Joined: Mar 08, 2001
Posts: 56
Thank you all for ur responces. Now I could create DataSource and get connections.
Apart from adding driver and datasource I need to add resource property with name URL and and the value the url string to get connected to DB(say jdbc racle:thin: etc.) in the data sources tab
Thanks
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Database connection