Solved

Creating Connections to MySQL Database

Posted on 2006-12-01
1
169 Views
Last Modified: 2010-04-01
Hi --
Suppose I have a web application developed using JSP that must use a MySQL database to run queries and update values in the database.
Then I would need to create a connection to the database. (I know how to do this).

Since connections to a database is "expensive", we only want to create a connection when it is absolutely necessary, correct? (in other words, we don't want to create say 10 connections just for one user to run one query).

So here's my question --
When do you create a new connection to a database?
Do you always create a connection to a database for each user that logs in to the web application?
Or do you only ever have ONE single connection and pass this connection along multiple users of the web application?

What is the industry norm?


Also, how do you close the connection to the database? (I know how to do it in code)
You want to close it whenever the user logs off, but the user may simply close the browser.
In such a case, how does the connection get closed other than timing out?


Thanks.
0
Comment
Question by:blast010
1 Comment
 
LVL 10

Accepted Solution

by:
mbvvsatish earned 50 total points
ID: 18066696
That is what the connection pool is for.

>>When do you create a new connection to a database?
never create a new connection to the database by youself. Instead, use a connection pool linked to a datasource of your application server, specify the max and min connections required, depending on the number of users of your application.
here a connection pool will create a new connection for you, when it is required. And these connections will not exeed the max connections given by you.

>>Do you always create a connection to a database for each user that logs in to the web application?
NO, absolutely not. use connection pool as specified above.

>>Or do you only ever have ONE single connection and pass this connection along multiple users of the web application?
if you use connection pool, it takes care of the passing the connection to multiple users whenever the connection is free.

>>Also, how do you close the connection to the database? (I know how to do it in code)
>>You want to close it whenever the user logs off, but the user may simply close the browser.
>>In such a case, how does the connection get closed other than timing out?
you should not close the connections, instead you should release the connections so that it goes back to the pool and can be used by some one else.

Here is the definition of a Connection Pool...
A cache of database connections maintained in the database's memory so that the connections can be reused when the database receives future requests for data.
Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven Web application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

http://java.sun.com/javaee/5/docs/tutorial/doc/Resources3.html
http://www.onjava.com/pub/a/onjava/2006/04/19/database-connection-pooling-with-tomcat.html
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
maven project import to eclipse problems 13 105
spring AOP 6 86
reflections, dynamic class loading 3 70
timeToIdleSeconds vs timeToLiveSeconds in hibernate second level cache 3 124
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Knowing where your website is hosted is as important as the features you receive, the monthly fee, and the support you receive. Due diligence should be done when choosing your next hosting provider.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question