JDBC in SessionBean

[Dave Thunder]

Hey guys, I need your advice again

I created a class which has methods fir writing and reading database through jdbc.
Now I moved them to SessionBean, but for some reason it doesn't work anymore.
Looks like it doesn't get the connection object or statement object from connection(not likely).

Here is the code which should return statement to other reading/writing methods (and remember, exactly the same code worked while it wasn't in SessionBean):

public Statement getStatement()
{
Statement stmt;

try
    {
//Register the JDBC driver for MySQL.
      Class.forName("com.mysql.jdbc.Driver");
 
        //Define URL of database server
      String url = "jdbc:mysql://" + SERVERHOST + ":" + SERVERPORT + "/" + DATABASE;
 

Connection con = DriverManager.getConnection(url,"", "");
    
//Get a Statement object
  stmt = con.createStatement();
   
  return stmt;
    }
catch(Exception e)
{
return null;
}

} 

Any ideas ?

Thanks!

[Purushoth Thambu]

Check for the following
- JDBC Driver class loaded successfully?

What is the exact exception? Don't return null in the catch statement. Print the exception details.

Once the driver class is loaded there is no reason statement cannot be created. The issue may be with the Driver and the Connection.

[Purushoth Thambu]

I am curious to know why you want to create the connection at the code rather than configure JDBC at your app server and retrive the connection? Particularly in a session bean it doesn't sound a good idea to create the connection directly by skipping app server.

[Roger Chung-Wee]

Agreed, connection pooling should be used and the exception handling leaves a lot to be desired.

But to solve the immediate problem: it is possible that the JAR or ZIP file containing the driver software is not on the EJB server's classpath. If it isn't, do this, restart the server and rerun your EJB.

[Dave Thunder]

This is my schoolwork.
I just have to use sessionbean and some other technologies.

I am not very confident about configuring something on server, plus I can't be sure that I can configure the servewr at schools like I could at home. So I want to create something which works 100% when I go to show my application.

I have found out that the exception is caused by Class.forName("com.mysql.jdbc.Driver");

I am using Eclipse. I have included appropriate mysql connector jar in my project(exactly the same as I did it on my other project, where I used jdbc just in a class, not in sessionbean).


[ December 22, 2005: Message edited by: Dave Thunder ]

[Roger Chung-Wee]

As recommended by another poster, you must do something with any exception thrown. Print the stack trace and tell us what it says.

[Dave Thunder]

This what i could get from exception :

com.sun.enterprise.loader.EJBClassLoader.findClass(EJBClassLoader.java:505)
java.lang.ClassLoader.loadClass(ClassLoader.java:306)
java.lang.ClassLoader.loadClass(ClassLoader.java:251)
java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
java.lang.Class.forName0(Native Method)
java.lang.Class.forName(Class.java:164)
pakk1.DBBean.getStatement(DBBean.java:95)
pakk1.DBBean.testime(DBBean.java:177)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:585)
com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:147)
com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:128)
$Proxy47.testime(Unknown Source)
pakk1.Kontroller.doGet(Kontroller.java:56)
javax.servlet.http.HttpServlet.service(HttpServlet.java:747)
javax.servlet.http.HttpServlet.service(HttpServlet.java:860)
sun.reflect.GeneratedMethodAccessor144.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:585)
org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:249)
java.security.AccessController.doPrivileged(Native Method)
javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:282)
org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:165)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:257)
org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:55)
org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:161)
java.security.AccessController.doPrivileged(Native Method)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:263)
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:551)
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:225)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:173)
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:551)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:551)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:132)
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:551)
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:933)
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:185)
com.sun.enterprise.web.connector.grizzly.ProcessorTask.process(ProcessorTask.java:653)
com.sun.enterprise.web.connector.grizzly.ProcessorTask.process(ProcessorTask.java:534)
com.sun.enterprise.web.connector.grizzly.ProcessorTask.doTask(ProcessorTask.java:403)
com.sun.enterprise.web.connector.grizzly.WorkerThread.run(WorkerThread.java:55)
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
com.mysql.jdbc.Driver

[Dave Thunder]

Well, I started thinking that before, when I tried my jdbc not in sessionbean, but just in a class, i ran it in Eclipse internally.

But now, I am tring to use it in sessionbean, which is ran not in Eclipse but outside, by Sun AppServer.

Maybe it 's still classpath issue?
Can you tell me, how to set the classpath so my code would outside Eclipse too ?

Or, maybe it's enough when i copy mysql connector .jar to Sun Appserver lib directory? J2EE lib is there and it works..
[ December 22, 2005: Message edited by: Dave Thunder ]

[Dave Thunder]

Hey guys, It worked.

I copied connectorJ jar jnti sun/appserver/lib directory and included it in eclipse from there.
It works(wonder only if i am able to copy that file at shcool to appserver dir, probably not...).


Thank you all and have a nice christmastime.

[Roger Chung-Wee]

Yes, it was a classpath problem because you are using eclipse to connect to the DB. In a more robust environment, it is the server which would need the driver on its classpth in order to set up the JDBC connection pool.

[Dave Thunder]

Ok guys, but still..

If I have decided to create connection in SessionBean, how should I do this?

I was wondering, maybe I should create the connection in EJBcreate and close in EJBremove?

So, when 1 user uses my application, how many connections are opened then?
Only one (because I create one new instance of that sessionbean) or more?

Thanks.

[Roger Chung-Wee]

I was wondering, maybe I should create the connection in EJBcreate and close in EJBremove?

Never do this. The best practice is to declare a local Connection variable in your method, get the Connection as late as possible and close the Connection in the finally block (to guarantee that this code executes). The above also applies to other JDBC objects such as ResultSet and Statement. This will ensure the thread safety of the Connection, reduce the chances of emptying the Connection pool and reduce the chances of suffering a cursor leak.

[Sanjeev Verma]

In a Stateless Session Bean, a call to ejbCreate() does not correspond one-to-one with a call to create() by the client. So in fact, if you initialize your connection inside ejbCreate(), you will have the same number of connections open as your initial bean pool size (normally specified at deployment time inside vendor specific descriptor, or some default value for the container). This may not be the most optimum thing. Also, calls to remove() by client do not correspond one-to-one to calls of ejbRemove().

I think you should really consider looking into the configuration needed for setting up a connection pool - give it one hour, you will not be dissapointed. It is not that bad at all .

If you want to avoid doing that for some other reason, look into using a open source connection pool implementations - like C3P0 (http://sourceforge.net/projects/c3p0).

[Sivakumar Nachimuthu]

Hi,

 private Connection getConnection() throws SQLException {
                try {
                        Context ctx = new InitialContext();
                        DataSource ds = (DataSource) ctx.lookup("java:/DefaultDS");
                        return ds.getConnection();
                } catch (NamingException ne) {
                        throw new EJBException(ne);
                }
        }
 
  

Use this method to get a connection from the connection pool of appl server.

Use the configured datasource name for the look up.

Siva

[Roger Chung-Wee]

Can I make some comments on the getConnection() method. I don't like it for two reasons.

1. It does three things when it should do just one thing, ie get a Connection. Obtaining a Context and a DataSource should be done in separate methods. Incidentally, you only need one of each, so why not declare them static and also cache them.

2. There are two checked exceptions which can be thrown, but one is declared and the other is wrapped in EJBException. It is better that there should be consistency, so either delare both exceptions or wrap both. In my view, as these are low-level exceptions which a client would not expect, you should wrap both of them and throw EJBException.