• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

The best way to connect\disconnect to Database

 
milan doshi
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello ,
I have been doing servlets programming but do not really have any great knowledge of the DataBase.
Whenever my Servlets require to be connected to the Database I do it by writting it in the init() method the following:
1)Loading of the Driver and
2)Getting the connection
i.e:
DriverManager.registerDriver(new Oracle:jdbc.driver. OracleDriver ());
DriverManager.getConnection("jdbc racle:thin@netserv:2345:ORCL"
" "," ");
Is this always the way it should be done?
I mean is it OK to keep a connection ON with the database always even if the Servlet may not be invoked many times.This may be alright if the Servlet page gets a huge number of hits, but otherwise ?
What I would like to know is that what is the usual rule or criterions one should adopt for Database connectivity.
Thanks in advance,
Milan Doshi.
[This message has been edited by milan doshi (edited November 30, 2000).]
 
Carl Trusiak
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, using the technique that you have laid out can result in a lot of problems. Servlet Engines can operate multi-threaded that means that more than one client can be serviced by the same servlet at the same time. You can(and will under heavy loads) wind up with sharing problems because you have 1 connection for all the threads. Take a look at dbConnectionBroker by JavaExchange. It's free and there are examples on how to use this with your servlet.
Hope this helps
 
milan doshi
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Carl for your valuable suggestion.
Frankly, I am not much aware about the DbConnectionBroker, hence thanks for suggesting the website. I`ll go through it.
In the meantime, I would like to ask that what should be the design criterion in absence of the DbConnectionBroker.
I mean, should I create a connection in the doPost (or the doGet) method ? and in the process create a connection for each and every Servlet request.
Further, in professional design and Development of Servlets and Database connectivity, how does one usually connect with the Database, (is it always through the DbConnectionBroker ).
Awaiting all valuable suggestions,
Thanks,
Milan Doshi
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some servlet containers (for example Resin, from http://www.caucho.com/ ) have a connection pool built in which implements the DataSource specification in recent JDBC APIs, so you may not need to install another connection pool. If you don't have any connection pool available, you will have to create a new connection in each doPost or doGet, but this will probably slow down your servlet.
Connection pooling is so useful it should be very seriously considered for every new database project, not just servlets.
 
le taylom
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I agree, I have recently developed a n-tier solution using servlets to database comms. I was using bea weblogic and at the start I was not using their connection pool facility (d'oh). Instead I was (like the first poster) doing my connecting in the init method - as some texts suggest - but this caused me numerous connection - and other - problems.
I then outclassed (sorry, my name for splitting functionality from one class to a new one) the connection-specific code into a new class (bean?) and just called that class each time I wanted to make a connection.
This was better.......until I needed more functionality from the connections I was making. With most of this built into the server I then moved to connecting to the connection pool and letting it do the work.
Result: much more efficient and better code
T
 
Carl Trusiak
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, all the servlet running programs I've worked with, Oracle Application Server, WebLogic, Resin, JRun have there own connection pooling. The problem I've had is once you code for that, you have to modify code if you want to change servers(or in some cases as with Oracles new web app server if you upgrade)you have to modify the code. I also use the dbConnectionBroker in a few stand alone applications and have continuity across all. I wish this company would select one platform and stick with it. Remembering all the diferrences gets to be a hassle
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This difference between applications was the drive for including connection pooling via the DataSource class in recent versions of JDBC. I have recently reworked my own connection pool to implement the DataSource interface, and many of the container vendors are doing the same.
 
Carl Trusiak
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the tip Frank, I'll look that up in the APIs
 
milan doshi
Ranch Hand
Posts: 43
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Trusiak, Taylom and Frank.
All your valued suggestions are highly appreciated and with your suggestions I have started with DbConnectionBroker.
Regards,
Milan
 
Milan
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Friends,
I have now started with JSP.
It would be intresting to know what is the best way to connect in JSP. There is first of all no init() method and in any case how do you optimise the DataBase connectivity?
should we use beans to optimise it?
All help will be highly appreciated.
Regards,
Milan Doshi
[This message has been edited by Milan (edited March 06, 2001).]
 
Bharatesh H Kakamari
Ranch Hand
Posts: 198
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even though there is no init() method hand-crafted by the developer this JSP page will eventually be compiled to a servlet by the jsp engine.
And with regards to connecting to database thru jsp you can embed the JDBC code in scriptlets or have a method defined in the 'declaration' and then call this method from a scriptlet as follows :
Method 1 :
<% Class.forName (" ... "); // or DriverManager.RegisterDriver(..);
....
%>

Method 2 :
<%! public getConnection ()
{
Class.forName (...);
Connection conn = DriverManager.getConnection (...);
....
%>
.....// More Jsp Code
<%
PrepareStatement pstmt = conn. .....
%>
HTH
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It may be useful to add some little tidbits to the excellent comments that have already been made.
There are two reasons why connection pools are good.
The first is that opening a database connection takes quite some time -- it can easily be 250ms, which is eternity in a web-app with a bit of volume. Definitely not something you would want to do while servicing a request.
The second is that not all databases are particularly happy managing hundreds or thousands of connections at once. Even heavyweights such as Oracle start to degrade eventually.
But, for low volume purposes or small sites it may be acceptable to simply open a connection in the servlet. From the above it is hopefully clear that you really don't want to open it in the service() method itself (and, by extension, in doGet() or doPost()). You would want to open it in init() and close it in destroy() as suggested in the very first article. But that is not threadsafe; multiple simulaneous requests would try to use the same connection and your application collapses in a big heap.
This can be easily addressed. The Servlet specification actually allows you to tag non-threadsafe servlets with the SingleThreadModel interface. If you implement this (empty) interface, the servlet engine will not let your servlet handle multiple requests. Instead, it will instantiate the servlet multiple times and use different instances to handle different requests. The effect is that there will be a pool of instances of your servlet, each of which has its own connection to the database. A kind of servlet-level connection pooling, really.
If your needs are simple, by all means just implement SingleThreadModel. But be aware that it doesn't scale terribly well.
Why doesn't this scale? First, since your connections are tied to one particular servlet instance, most of the time they will be doing nothing. To handle the database access you will be opening more connections than you actually need at any given time. Second, the server will respond to high loads by instantiating your servlet lots of times, which eats resources. The server scales better with threadsafe servlets which have to be instantiated just once.
- Peter
PS. All of the above equally applies to JSPs.
[This message has been edited by Peter den Haan (edited March 06, 2001).]
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ANother major problem with opening anything in "init" is that most services (such as databases) will time out eventually. Ina stable system, a servlet can be loaded and running for months at a stretch. Would you like to bet your application that the database connection you opened last August still available when you run your servlet today!
A good connection pool will manage that for you, shutting down redundant connections and reopening closed connections as appropriate.
 
Milan
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Frank, Peter and everyone else.
I am sure that this topic will have helped a lot of Programmers.
Further Frank, I had my Username registered as milan Doshi and suddenly when I post a Reply or a new Question it says no Such UserName exists ( I alsready recieved an Email conf my Password for the above userName) , hence I tried to rergister with the same name (as I cannot use this name convention ) than it says that such a userName already exists. Just a trivia , thought about bringing to your notice.
Coming back to the topic someone has also suggested me RMI for the solution.
Milan
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you like, I can delete the "Milan Doshi" user name, which will let you try again to create it. Would you like that?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic