This week's book giveaway is in the Cloud forum.
We're giving away four copies of The Business Blockchain and have William Mougayar on-line!
See this thread for details.
Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Making reusable and thread-safe databse connections

 
Sreyan Chakravarty
Ranch Hand
Posts: 65
Firefox Browser MyEclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am right now developing a simple web-app that supports chat functionality with servlets. Now, the chat functionality will need to support chat history which will be retrieved from a database. Now if there are multiple users on the site accessing the same database using the same database-userid and password. How can I make them thread safe ?

What I mean to say is -:


Lets say while User1 chat messages are being retrieved User2 comes along and requested for their chat messages, (the server will need to asynchronously handle requests since there maybe a lot of users requesting their chat messages from the same table) by trying to log in with the same user-id and password(user-id=ABC and pass=123) won't that result in an authentication error since the a user is already logged in with the same user-id and password ?

How are problems like this handled in real-life web-apps like Facebook or Twitter ?

Will connection pools solve this problem ? Connection pools are a cache of connections maintained according to the following article-: http://www.webopedia.com/TERM/C/connection_pool.html

Then in that case what happens when the number of users become larger than the number of available open connections ? How would you make it thread safe ?

Basically I am asking how would you go about handling connections in large-scale enterprise database intensive applications ?

After reading the following questions on SO I am able to get some idea but are these really best practices -:
http://stackoverflow.com/questions/6631739/proper-way-of-handling-database-connections-in-a-medium-sized-web-application
http://stackoverflow.com/questions/8238477/best-practice-of-managing-database-connections-in-a-web-app

What is a DAO ?
What are DataSources ? How are different from databases ? How do they help ?
How do you store your connections in Thread Local Storages ?


These are just some of the many questions I have while trying to get a solution.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A connection pool keeps a bunch of connections (all with the same database user id/password) available for use. A datasource is a way of getting to them.

One common approach is to get the connection from the connection pool for each query and "close" it when done. This doesn't actually close the connection, it merely returns it to the pool. These connection uses are extremely short lived as they do one query (delete/insert/update or select) and give back the connection. It's a good technique when you don't need transaction support.

If you do need transactions, you want the user to have the same connection for all queries in that request (servlet access.) Storing the connection is the thread local is an approach for that. Just make sure you always close it so you don't have a leak!

I think you can use approach #1 in your code.
 
Sreyan Chakravarty
Ranch Hand
Posts: 65
Firefox Browser MyEclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne Boyarsky wrote:A connection pool keeps a bunch of connections (all with the same database user id/password) available for use.


Does the database allow multiple logins with the same user-id/password ? Won't that cause an access violation ? I mean if a servlet accesses the database with userid=foo and pass=bar then a second servlet comes along and uses the same user-id/pass combo, I just don't understand how that works fine.
 
Claude Moore
Ranch Hand
Posts: 828
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sreyan Chakravarty wrote:

Does the database allow multiple logins with the same user-id/password ? Won't that cause an access violation ? I mean if a servlet accesses the database with userid=foo and pass=bar then a second servlet comes along and uses the same user-id/pass combo, I just don't understand how that works fine.

Yes, a DBMS typically allows more than one single connection per user. Each physical connection is separated from the others - usually, a connection is no more than a tcp-ip socket connection from your program to DBMS that the jdbc driver takes care to handle for you.
 
Sreyan Chakravarty
Ranch Hand
Posts: 65
Firefox Browser MyEclipse IDE Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Claude Moore wrote:
Yes, a DBMS typically allows more than one single connection per user. Each physical connection is separated from the others - usually, a connection is no more than a tcp-ip socket connection from your program to DBMS that the jdbc driver takes care to handle for you.


When implementing Connection Pooling is it dependent on the webcontainer that I am using ? I mean will setting up Connection Pooling in Apache tomcat be different from setting it up in JBoss for instance ?

If so then why ? Also what is Apache c3p0 used for in Connection Pooling ?
 
Claude Moore
Ranch Hand
Posts: 828
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Connection pooling is quite a complex mechanism, whose details depend upon both driver and application servers (they may introduce some extra feature to basic connection pool), so I suggest you to read your webserver specific documentation.
Beside specific details, as Jeanne wrote before, the way you use connection pooling via Data source usage is absolutely standard across any Java EE compliant web container.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic