Java database connection

Question

I've been approached by a programmer that has background in Oracle forms and is moving into the Java realm. He asked me a question that I didn't have a good answer for. Instead of answering that; I always do it that way or that's how I was taught. I figured I'd do some research.

Question: With Java multi-thread capabilities; why don't you setup a JDBC connection to the database for each user, each on it's own thread? Instead of setting up a connection pool and applying security to which users can access the pool?

What do you mean by "user" in this context? An Oracle user? An Application user? — Jon
– Jon, Commented Mar 27, 2012 at 15:14
One thread per application user? So 500 concurrent users would equal 500 dedicated threads in the JVM, at one thread per user, many of which would be sitting idle during times when the user did nothing at all... can you see why a thread pool is preferable now? For any non-trivial application the approach would not scale. — Jon
– Jon, Commented Mar 27, 2012 at 15:31
Agree with Jon. How would that work anyway? A web application is not client-server from the user to the database like a Forms app is. HTTP is request/response, so how would you propose to maintain a connection for a user who may or may not make another request? Keep their DB connection as a session attribute? I suppose you could do that, but a connection pool is definitely the way to go so as not to waste DB resources. — GriffeyDog
– GriffeyDog, Commented Mar 27, 2012 at 16:13

nwinkler · Accepted Answer · 2012-03-27 15:17:49Z

3

A connection pool scales better. If you have a dedicated connection per user, you will need 50 connections for 50 users. With a pool, you can probably do with something like 10 - 20 connections to handle the 50 users (depending on the use case). Now take a look at a bigger group and think about handling 500, 5000 or 50000 users and you will see that the 1 connection per user model does not scale.

With a connection pool (and a thread pool), each request will still be handled by one thread and by one database connection, but they will be taken from a pool instead of a dedicated one per user.

answered Mar 27, 2012 at 15:17

nwinkler

54.9k23 gold badges166 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brad · Accepted Answer · 2012-03-27 15:17:07Z

1

Because the overhead of maintaining a lot of threads is not efficient. What happens when there are more users than your application server or database can maintain? What happens when there's a lot of contention on a database table?

By using a connection pool, you're not having to "connect" to the database again from scratch so you get a big performance boost from re-using database connections from a pool.

Something like Apache's DBCP is a good place to start if you're not using an application server.

answered Mar 27, 2012 at 15:17

Brad

16k11 gold badges62 silver badges75 bronze badges

Collectives™ on Stack Overflow

Java database connection

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related