Issue
I am making a web app in Java and I want users to connect to database. As a beginner, I came across term Connection Pooling. I understand its main use, which is connection reusability.
So, my code could looks like this:
public class Test extends HttpServlet {
@Resource(name = "poolName")
private DataSource dataSource;
protected void doGet(HttpServletRequest request, HttpServletResponse response) {
Connection connection = dataSource.getConnection();
//do some SQL with this local variable
//every user will have its own connection
}
}
But can someone tell me the following? Why should I get connection from Connection Pool on each user request, when I can declare instance variable of type Connection that each user (thread) would use?
To simplify, why should I give each user a connection, when users can share a single connection? Code follows:
public class Test extends HttpServlet {
@Resource(name = "poolName")
private DataSource dataSource;
private Connection connection;
public void init() {
connection = dataSource.getConnection();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) {
//do some SQL with referenced instance variable connection
//every user will share same connection
}
}
Can someone please teach me how do I write good code and what advantages does one approach give over another? My last example (that doesn't get connections from Connection Pool on new requests) looks better in my opinion (and I feel I am wrong).
Solution
Each connection makes its own transactions. If you have one shared connection you can’t have transactions spanning multiple statements because if you have concurrent users the calls will get interleaved and it isn’t predictable what will go into a transaction.
If each user gets their own connection each user can start their own transaction and commit it separately of the others and include only the statements they need. That means the users’ work is done in predictable chunks instead of being jumbled up. Transactions are a very useful feature of relational databases and any application design that precludes using them is likely to be a problem.
Database connections aren’t meant to be used by multiple threads, it’s likely that even if the connection is threadsafe it will lock out all but one user at a time. So your concurrency will suck. When there are multiple users they will pile up waiting to use the shared connection. And if you have an issue with one statement’s performance, that will impact all the other threads waiting.
Also database connections go bad. Network connections are fragile and they go down. If you use one connection then when the connection goes bad the whole application will have to be restarted. If you use a pool, it takes care of creating and handing out connections, and stale conections are something your application can recover from easily.
Answered By - Nathan Hughes
Answer Checked By - Terry (JavaFixing Volunteer)
