aspose file tools*
The moose likes Servlets and the fly likes where should i create the database connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "where should i create the database connection " Watch "where should i create the database connection " New topic
Author

where should i create the database connection

saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
hi, i am not using any franework over here ..
i am just using servlet, jsp, bean for developing an application

so, where should i write the database connection steps ???

can i do it in servlet?


A = HARDWORK B = LUCK/FATE If C=(A+B) then C=SUCCESSFUL IN LIFE else C=FAILURE IN LIFE
SCJP 1.4
Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6662
    
    5

Use a set of classes that will help you do all the DB work. One will get you the connection, another will execute queries etc. That way you increase cohesion


SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Originally posted by John Meyers:
Use a set of classes that will help you do all the DB work. One will get you the connection, another will execute queries etc. That way you increase cohesion


what do you mean by cohesion ?

so i shouldn't write databse connection steps inside a servlet
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


what do you mean by cohesion ?

"cohesion" basically means "placing together". Put code that does one common job in one place, rather than scattered throughout the rest of your code base.
[ October 05, 2006: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
so i shouldn't write databse connection steps inside a servlet


No, putting database connection code or business logic in a servlet is not very maintainable or reusable.

A better solution is to use a Model-view-controller design pattern. The servlet can act like a controller, the JSP is the view and a bean is the model. Your business logic or database code would go in the model.

Do a search on Google for "Model View Controller" or purchase a copy of Design Patterns by Erich Gamma, et al.
[ October 05, 2006: Message edited by: Scott Johnson ]
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Originally posted by Scott Johnson:


Your business logic or database code would go in the model.


[ October 05, 2006: Message edited by: Scott Johnson ]


so model means bean

but bean has got setters and getters if i write the connection there how can i use bean
Rahul Bhattacharjee
Ranch Hand

Joined: Nov 29, 2005
Posts: 2308
Let the deployer of your application create a connection pool at the server and then you can use that from within your application.You can even have the name of the connection pool in a external property file.So that if you deploy your applcation in some other server and the name of the connection pool is something else ..then also you do not need to make changes in your code ..just change the name in the property file.


Rahul Bhattacharjee
LinkedIn - Blog
Dinuka Arsakularatne
Ranch Hand

Joined: Aug 12, 2006
Posts: 198
i think it would be much better if you put a servletcontextlistener and in the contextInitialized() method you can get the DB connection and put clear the connection in contextDestroyed() method. this way you can be sure that before any client is served you have a DB connection ready for use.be sure to put your DB lookup name as a context param in the DD.

hope this helps


if who i am is what i have, and what i have is lost, then who am i?<br /> <br />SCJP 5.0<br />SCWCD 1.4<br />SCBCD preparing
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
so model means bean


A bean can be the entire model or it can be part of the model. That depends on the application requirements.

but bean has got setters and getters if i write the connection there how can i use bean


I don't understand the question.

Rahul is right about the connection pool. You should configure a pool and grab a connection when you need it from inside the model.

As for how to design your model classes, do a search on Google for "Domain Model Pattern".

You can also design your model using use a Data Access Object (DAO) design pattern. Here the DAO will handle all database activity and populate the bean.

Do some research on these two patterns and select the one that works best for your application.

I should have mentioned this book earlier, but...another excellent book that has extensive coverage of this subject is Core J2EE Design Patterns.
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
[Dinuka] it would be much better if you put a servletcontextlistener and in the contextInitialized() method you can get the DB connection

If I understand what you are saying, you'll be creating a single database connection for use by all requests. That isn't thread-safe and definitely won't scale.

As Rahul mentioned, Saikrishna is better off using a connection pool.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Originally posted by Scott Johnson:
[Dinuka] it would be much better if you put a servletcontextlistener and in the contextInitialized() method you can get the DB connection

If I understand what you are saying, you'll be creating a single database connection for use by all requests. That isn't thread-safe and definitely won't scale.

As Rahul mentioned, Saikrishna is better off using a connection pool.



no no i am not creating a single connection here in the servlet,
i am creating 10 connections
the reason why i am not using connection poolin is i dont have exact usage of connection pooling and it some times gives exceptions
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
i am creating 10 connections


Ok, that's good.

the reason why i am not using connection poolin is i dont have exact usage of connection pooling and it some times gives exceptions


I don't understand. Please explain. Under what conditions do you get exceptions?
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
the exception is realted to connection

connection not available ..

i think it is failing due to some illeagal code in the above post
Harshad Khasnis
Ranch Hand

Joined: Jun 06, 2006
Posts: 48
I have a query here.
Regarding connection pooling....
can we do that in Webservers like Tomcat.

As per my knowledge I think we require a application server to have services like connection pooling.... like weblogic or that sort of.

Can anybody please correct me if I am wrong??
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
[Harshad] Can anybody please correct me if I am wrong??

Yes, we'd be happy to correct you!

Tomcat does include a connection pool library. It's based on the Jakarta-Commons DBCP. See the Tomcat User Guide section 9 for how to configure it.
Richard Green
Ranch Hand

Joined: Aug 25, 2005
Posts: 536
i am creating 10 connections

the reason why i am not using connection poolin

please tell me that you are not creating a new connection for every sql you run


MCSD, SCJP, SCWCD, SCBCD, SCJD (in progress - URLybird 1.2.1)
Harshad Khasnis
Ranch Hand

Joined: Jun 06, 2006
Posts: 48

Originally Posted by Scott Johnson:

Yes, we'd be happy to correct you!

Tomcat does include a connection pool library. It's based on the Jakarta-Commons DBCP. See the Tomcat User Guide section 9 for how to configure it.


Thanks Scott I think had a misconception about connection Pooling & Tomcat, happy its clear now.
Marko Debac
Ranch Hand

Joined: Aug 21, 2006
Posts: 121
please tell me that you are not creating a new connection for every sql you run


how otherwise? when with every connection I must perform diferent work (with diferent inputs).

Ok. I have created class for establis Database parameter through ServletContextListener, and when I need connection (in servlet)I simple do

and that work only in the servlet under



but when I try this from anywhere else, I cant because "I need to write getServletContext() function there" - it doesnt heve sense
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Connections should be dealt with in a Data Access Layer. For the most part this data access layer should be agnostic to what type of app you are writing, whether it be a webapp or a desktop app. However, since we are obsviously dealing with a web application in this thread, we'll focus on that.

Creating a new connection to the database everytime you need one is very expensive. By expensive, I mean slow. A web application by nature will have more than a single user. Therefor, you should use a connection pool. A connection pool can be created many different ways. You can do it all yourself by using something like DBCP or C3P0 libraries, both of which are open source. You can let the app server (Tomcat, Websphere) create the connection pool for you (see relivent documentation) and access it via JNDI. Sometimes, another API like Spring can manage the connection pool for you, if you are using said API.

A connection pool will create n number of connections and depending on the configuration, those connections will grow and shrink depending on the load. This way, each time a connection is requested, your data access objects are handed a connection that is already open and ready to go. When you are done, calling connection.close() hands that connection back to the pool to be reused again by another request.

Creating connection in a context listener is a bad idea. Creating conenctions in a servlet is a bad idea. Worse case scenerio, create the connection in a helper class that the servlet calls to do the database work. At least that is going to be thread safe. So something like:

a bit of psuedo code to save myself from typing, so don't copy and paste this




GenRocket - Experts at Building Test Data
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Here's a thought experiment: Pretend that you won't know for a couple weeks whether you're using UDB or MySQL or flat files or remote calls to a partner system or Magic Memory Monkeys for persistence. That would lead you to a generic data access layer with create, retrieve, update and delete operations but no references to SQL, resultsets, SQLExceptions, etc. You could even make several different implementations of accessors and use all of those storage techniques at once. This is not fantasy - the system I work on uses at least a half dozen different access methods all behind the same API.

So my answer is put the database stuff as far down in the hidden depths of your system as possible, and don't let any other classes know you even have a database.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Richard Robbins
Ranch Hand

Joined: Mar 07, 2001
Posts: 66
Hi saikrishna,
It looks like there have been a host of various answers to your question about where to place the database connection code. Maybe a little word of advice from my experience will be helpful. When I'm writing a Java application, I determine where to handle the database connection and SQL statements based upon how complex the application is and how helpful it is to reuse the database code (which usually dependent upon the complexity of the application). For instance, I have seen programmers abstract database and other reusable code in simple applications so much that it becomes more difficult to find what's going on with the database connection than it is to accomplish what you want with it in the first place. On the other hand, if you have to keep opening and closing connections and manually handling the details and worrying about how many DB connections are open, you will probably want to follow the suggestions here about automated connection pooling.

Hope that helps some.


Richard Robbins is one of the founders of WebsiteTemplateDatabase.com, a resource for free website templates and Photoshop tutorials. He also consults for an online boutique selling baptism dresses and christening gowns.
 
Don't get me started about those stupid light bulbs.
 
subject: where should i create the database connection