Meaningless Drivel is fun!*
The moose likes Tomcat and the fly likes Help in database connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Tomcat
Bookmark "Help in database connections" Watch "Help in database connections" New topic
Author

Help in database connections

Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Hello,

I am learning Servlets and JSP. I have trouble creating an object for the static factory method I had written for database connection. And I need some suggestions and helpful tips of developing my class in object oriented fashion.

The database connection code I had written:


I get an error when I call the instance method, and I need some help how after successful connection how do I use this connection to call the SQL query. I have the following code in AuthenticationServlet class which calls the static factory method: I get error at databaseConn dbconn = new databaseConn.getInstance().





I am not sure where my mistake is, I need some help. Thanks in advance !
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60780
    
  65

Welcome to the Ranch.

Revanth Sharma wrote:I get an error when I call the instance method,!

Keeping the error a secret doesn't help us help you.

Why are you not using container-manged connection pooling? Writing your own connection code is naive and a poor practice.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Ryan Beckett
Ranch Hand

Joined: Feb 22, 2009
Posts: 192
A few things:



You don't need and shouldn't use an instance to invoke a static factory method (or any static method). It should be:



Static factory methods are used in patterns where you want object creation control. In you case, it appears that you want to use the singleton pattern: allow only one instance to be created and used. To do so, your method should be:



You were probably getting an exception in your servlet, because the object that was returned was a databaseConn and not a Connection, which has the method preparedStatement(). After those changes your code should be working or close to it. And, yes, it's probably not the best idea to try and implement your own pooling, especially when reliable solutions exist.
Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
I am a newbie, I am learning could you please elaborate on container-oriented connection pooling ?

I know that writing own connection code is a poor practice, those are the basic level examples that I found on roseindia.net/servlets.
Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Thank you very much Ryan and Bear, I have another question. The connection is successful, how to make the connection persistent or available to other classes. I have another file RegisterServlet which handles user registration. Will I be able to use the same connection I created earlier in other classes ?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60780
    
  65

Revanth Sharma wrote: those are the basic level examples that I found on roseindia.net/servlets.

roseindia is usually a good example of how not to do things. Those are some of the poorest examples on the face of the planet.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60780
    
  65

Revanth Sharma wrote:I have another question. The connection is successful, how to make the connection persistent or available to other classes.

Take the time to look up connection pooling. ANything else is a waste of your time.

Are you using Tomcat? If so, how to set it up for container-managed pooling is well documented.
Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Thank you. I have read the documentation on connection pooling and also the container-managed pooling on tomcat website. I followed this link
http://tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html

However, the documentation says I can write script in context.xml and I have trouble connecting. I get the error
javax.naming.NameNotFoundException: Name jdbc is not bound in this Context. I am not sure what changes I have to make
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60780
    
  65

Moved to the Tomcat forum.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15952
    
  19

Roseindia used to be such a good site. I was very disappointed when it went downhill.

When you get the JNDI error "Name jdbc not found in context", that means that you haven't configured your connection pool properly. Usually it failed to construct the pool, although if there's a fault in your JNDI name in the Tomcat Context XML, that will cause it not to be found as well. Check also the definition in web.xml.

Above and beyond that, the only thing worse than writing your own login code is having it retrieve userid and password from the database.

In addition to having an enterprise-grade connection pooling subsystem, J2EE also supports an enterprise-grade security system. I HIGHLY recommend using it because in all my years in J2EE I've never seen a SINGLE Do-It-Yourself security manager that was actually secure, and most of them were about as durable as rice paper in monsoon season.

If you MUST do your own security code, the safer way to log someone in is like this:



This technique avoids bringing back passwords to the Java server where they can be snooped. A return value of 0 indicates failure to match userid/password. A return value of 1 indicates the user credential were OK. Any other value and there's something funny about your user table.


Customer surveys are for companies who didn't pay proper attention to begin with.
Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Thanks Tim..

I think the problem with the jndi error is because of my folders that are placed inappropriately. I am starting the tomcat from eclipse and my workspace is in "My Documents" instead of webapps folder in tomcat.
This following is the code i have put in context.xml


I would really appreciate if someone can help me getting off this burden. I am still learning stuff, so I have a lot ahead to do !

Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Now, I have a different error,

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'


I believe Tomcat is not able to recognize the path of the mysql-connector which is in the tomcat/lib/ directory. Do I have to add the path for the driver ? I am confused
Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Revanth Sharma wrote:Now, I have a different error,

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'


I believe Tomcat is not able to recognize the path of the mysql-connector which is in the tomcat/lib/ directory. Do I have to add the path for the driver ? I am confused
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15952
    
  19

No, you don't have to mess with the classpath. That message comes when you don't have the Context datasource definition coded EXACTLY right and it doesn't want to tell you what's wrong. I hate it when it does that. I hate it so much I looked at the source code hoping to fix it. And came to the conclusion that there simply was not easy fix.

Check the precise classname of the jdbc driver you're using and make sure that that driver is actually in the driver jar. Check the JDBC URL you're using. Check username and password. Those 4 are the critical ones. Especially check to make sure you have all the upper/lower case spellings correct. If you type "userName" instead of "username", for example, that will make it fail.

Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Yes, I have checked and everything seems right. I can't figure out any error.
Well, this has to be suggested for the tomcat group.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15952
    
  19

I'm afraid it only SEEMS right. Somewhere in there something is just slightly wrong, but it's not going to tell you where. Which means that you're going to have to check everything meticulously. And if you're not experienced setting these things up, it's going to be just that much harder since you don't have working examples to compare against.

If I seem insistent about this it's only because I've had your same problem over and over again for years and it always ends up with the same solution.
leo donahue
Ranch Hand

Joined: Apr 17, 2003
Posts: 327
Revanth,

A. I don't know why I keep coming back to this forum, perhaps I'm just stupid.

B. An example that works for SQL Server:

C. Place the driver in the WEB-INF/lib directory of your webapp, not in Tomcat's lib directory. Why? Because sometimes you want specific database settings per webapp, not for the whole container.

Good luck...





Thanks, leo
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15952
    
  19

Leo, PLEASE don't recommend doing that! JDBC drivers for connection pools should NEVER be placed inside the webapp. The pools are created by the appserver, and they must BE in the classpath of the appserver. In other words, TOMCAT_HOME/lib.

The Context database definition already only applies to only the one webapp. To make a shared database connection pool, you'd have to put it in server.xml, instead.
leo donahue
Ranch Hand

Joined: Apr 17, 2003
Posts: 327
Revanth didn't ask for pooled connection, Bear mentioned and Revanth picked up on it. Does Revanth know he wants pooled connection?
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15952
    
  19

leo donahue wrote:Revanth didn't ask for pooled connection, Bear mentioned and Revanth picked up on it. Does Revanth know he wants pooled connection?


Well, whther he knows depends on how well he understands this, since it's all fairly new to him. He's asking for one now, at any rate:

Revanth Sharma wrote:
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'


Definitely an error message from the Apache Tomcat Database Connection Pooler.

Your example, on the other hand was DEFINITELY for a connection pool, but your advice was to put the driver in the WAR, and that's likely to make a lot of appservers fail with a ClassNotFoundException.
leo donahue
Ranch Hand

Joined: Apr 17, 2003
Posts: 327
http://tomcat.apache.org/tomcat-7.0-doc/appdev/deployment.html

Standard Layout Directory:


/WEB-INF/lib/ - This directory contains JAR files that contain Java class files (and associated resources) required for your application, such as third party class libraries or JDBC drivers.

When you install an application into Tomcat (or any other 2.2/2.3-compatible server), the classes in the WEB-INF/classes/ directory, as well as all classes in JAR files found in the WEB-INF/lib/ directory, are made visible to other classes within your particular web application. Thus, if you include all of the required library classes in one of these places (be sure to check licenses for redistribution rights for any third party libraries you utilize), you will simplify the installation of your web application -- no adjustment to the system class path (or installation of global library files in your server) will be necessary.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15952
    
  19


... made visible to other classes within your particular web application


Note: other classes within your particular web application.

The JDBC connection pool defined in a webapp context is not within the web application, it's within the web application server. It's built by the server, owned by the server and registered via JNDI to be visible to the webapp. The webapp's classpath includes elements from its container, but the container's classpath does not contain elements from the individual webapps. Which is why when using a container-constructed connection pool, you must put the driver in the container's classpath, not the webapp's classpath.

If you'd like to discuss this in depth elsewhere, fine, but we're hijacking the thread here and not solving the original problem. I wouldn't have gotten as involved in this diversion as I have except that what you're recommending is likely to compound Revanth's problems, and he's got enough frustration as it is.
Revanth Sharma
Greenhorn

Joined: Jul 30, 2011
Posts: 14
Tim and Leo,

Thanks for the comments. I was unable to fix it and I used the bad practice to create a new connection everytime, but I have to finish my exercise on schedule so skipped using the pooled connections. But, yes I want to get involved to know the industry level practices. Lately I have been looking at setting up the tomcat and subscribing to the user and dev mailing list. Its little tough for me to understand which is quite complex for a newbie like me but I am pretty much interested to build my resume .

Coming to the pooled connection as suggested by Bear, java maintains a PooledConnection class which effectively utilizes the opened connections for various SQL calls instead of closing and opening new connections which can be expensive for a heavily loaded server. Correct me if I am wrong

Let me explain you the directories I have my tomcat and the mysql-connector.jar.

My webapp is in the eclipse workspace which is
c:\Documents And Settings\Administrator\workspace\UserRegistration.

I am not sure if my context.xml docBase is exactly looking at the UserRegistration folder in the workspace. As I have said my mysql-connector.jar is in the tomcat/lib directory.
For my exercise level proj I am working on MySQL but if I am working realtime I am not sure if the organization uses mysql or other databases that are available. So, my question is where exactly should the .jar file be placed if diff webapps use the same database or I might be wrong in the question.

I am really interested to know the realtime scenario.

Thank you very much for the comments.


Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15952
    
  19

You can get into problems in Java (and a lot of other places) when you have files or directories that have spaces in their names. So you might have fewer problems if you placed your webapp in some place like "C:\webapps\UserRegistration". Also, in Java, it's usually better to use Unix-style paths to avoid confusion with escape character processing, so the better way to code the webapp context path would be "C:/webapps/UserRegistration".

Tomcat doesn't actually maintain only one database connection pool. In fact, the connection pool isn't really even part of Tomcat itself. Tomcat supports plug-in connection pools. It ships with the Apache Database Connection Pool (dbcp), but if you wanted to do so, you could use some other pool package instead. People used to do that more often than they do now, though.

You can find complete details on the dbcp connection pooler on the apache.org website. Because Tomcat has its own "configuration language", it's sometimes a little difficult to match up the actual dbcp configuration options with Tomcat's options, but they are there, and it's not a bad a idea to read the dbcp documentation if you're confused. dbcp isn't strictly limited to Tomcat. Any application system, whether it's a webserver or something else entirely can use dbcp if it needs a connection pooler.

Sorry you had problems getting it to work. But at least you had the same kind of problems I often run into myself, and I've been doing this for years!

Leo did some experiments and some reflection and realized what he was advocating actually applies to a different situation. So now he and I are in agreement. Put the JDBC driver in TOMCAT_HOME/lib.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help in database connections
 
Similar Threads
Not able to navigate the user to another jsp in servlets using RequestDispatcher
weird functioning of servlet
problem calling servlet to build a list of data from a database and display a jsp page
My sql query returns zero rows
Help with this code!