JDBC DataBase Connection

[sh4dyPT]

Hello

What is the best way to manipulate a database connection?
I have a .properties file with all necessary information to create a DataSource, after reading this file once, I have instance of SQLServerDataSource dataSource available.

What I was doing was, for each method that interact with database , I create a Connection con = dataSource.getConnection() at start, and at the end I close that connection con.close().


Is this the right way? Or should I always use the same connection and close it in the end of "application"?

[travishein]

I think the convention of a long lived database connection (e.g. application lifetime, or connection for user logged in session lifetime) is generally useful for when a very small number of concurrent users is expected, like perhaps 6 to 50 users at once. ever.
or where perhaps the database is itself part of the application logic and it makes sense for the user to log into the database as their user name.

Typically in web-based applications the connection per use (like request scope) where a connection is fetched from a pool, used, and then 'closed', which really doesn't close it but returns it to the pool; is a much more scaleable mechanism to allow for more concurrent users, and possibly higher thruput or performance. This also assumes the database connection would be an application user (e.g. not the requesting user's credentials).
Though there are ways to have a connection per user logged in session in a web application as well. I would say to use the pool based connection per request unless there is a compelling reason to use conection per application.