Connection handling in large web apps

[Ahmed Yehia M]

Howdy
I want to know what is the most efficient way to handle DB connections in a large and high traffic web app.

Currently I m using a DriverManager connection to execute DB queries and updates.

This *single* connection was created in my context listener and I m using it through out the application for all components to use.

I still haven't published the web app to the internet yet so I haven't fully tested it out..

I want to know what are the pros and cons of using my current method and would I get connection pooling? also if there is a better way to handle this issue.

my code looks like:

  
// connection created in listener
ConnectionClass conn = new ConnectionClass();
conn.driverName = "com.mysql.jdbc.Driver";
conn.url = "jdbc:mysql://localhost:3306/MyDB";
conn.connect(); // connect() instantiates DriverManager using the [I]diverName [/I]and makes the connection to the DB using the [I]url[/I].
event.getServletContext().setAttribute("conn", conn);

    
// later in some servlet
ConnectionClass conn = (ConnectionClass)getServletContext().getAttribute("conn");
 
// .. use the connection object to execute DB queries ..

[ December 05, 2008: Message edited by: Eric Damps ]

[Jeanne Boyarsky]

Eric,
This would not give you connection pooling - you need multiple connections for that. Connection pooling is critical for large apps. See DBCP for an open source one.

[Ahmed Yehia M]

Thanks Jeanne,
Probably I'd be using DataSource instead.

I've got another question about my current implementation. Can this single connection be used by more than one user at a time concurrently or is it synchronized? note that I'm actually establishing a connection to the DB before bounding the Conn. object into the context, and later just getting the Conn. object and use it to query DB.
[ December 06, 2008: Message edited by: Eric Damps ]

[Jeanne Boyarsky]

Originally posted by Eric Damps:
Can this single connection be used by more than one user at a time concurrently or is it synchronized?

It looks like multiple users can try to use it at the same time. That wouldn't go well for them though.