I always think of connection pools in the context of a web application. There, when a client does a POST or a GET, the web application takes a database connection, interacts briefly with the database, releases the connection and sends a page of information back to the browser. Many (hundreds, thousands) users are interacting briefly with the database, over and over again. It wouldn't make sense to maintain one Connection object per user because there are too many users -- it won't scale. It also probably wouldn't make sense to truly connect to the database every time:
Connection con = DriverManager.getConnection(url, user, password);
because creating a connection to a database can be slow. The connection pool is handy here because the objects in the pool maintain their connection to the database -- you aren't truly "connecting" every time, and users share connection objects as they take them from the pool and release them back to the pool, which allows the application to scale better.
Now look at it from the viewpoint of what you termed a "Swing-based application". Actually, that terminology doesn't indicate what you are doing! There's no reason a desktop application can't open a HttpURLConnection and do the GETs or POSTs I wrote about in the first paragraph. But what I think you meant is that your Java application runs on the client's machine (Swing or console-based, it doesn't matter) and it interacts with a database through the JDBC, rather than interacting indirectly with some middle tier, like a web service. Let's go back to the basics and analyse the situation. A connection pool in the client application doesn't really buy you anything unless the client app has many threads that are briefly interacting with the database -- in other words if it is acting like a server-based application, and I really doubt that is the case. So you have two basic choices in a client application in a two-tiered achitecture: either hold onto a single connection, or create a new connection and then release it, repeatedly. The first approach doesn't scale well, but then perhaps it doesn't need to, if your database will only need to maintain a new connections at a time, becaue it has few users. Then second approach may be sluggish (whip up a prototype and see), but some applications don't need to stay connected throughout their execution. Imagine an application that just lets you submit some form data -- there's no reason to connect until the data is entered.
One of the benefits of a connection pool is sharing N connections between more than N clients; that is not generally the case in a desktop application. There might still be a benefit in keeping a pool of size 1 in order to skip opening a connection every time you need to access the DB. But if you don't notice a performance bottleneck during DB access, that may not a problem for you.
Joined: May 29, 2002
Thank you so much for your replies ,It was very helpful.