Good evening! I have a web app with login-logout, where you can buy or sell some products. My queries are stored in a java class, called
Opening a connection in the method contextInitialized and closing it in contextDestroyed is a bad solution. Imagine that your web site has two visitors at the same time. They would now share the same database connection. If you work with transactions, you would end up with unexpected results where one visitor commits an intermediate state of a transaction executed by the other visitor. And if the connection is ever lost (maybe because the DB server is restarted), your application will fail because it does not re-establish the connection (unless you have a very smart JDBC driver).
A very expensive but safe solution would be to open a new connection for every database operation, and close it again immediately after the operation.
For a perfect solution, you would use some kind of connection pool. Whenever you need to execute a database statement (or a sequence of statements), you would borrow a connection from the pool. Once you're finished, you would return the connection to the pool. Most pool implementations take care of things like validation (check whether the connection is still "alive"), and multiple threads (different HTTP requests send by different visitors at the same time) can execute statements in parallel.
If you want to go for this solution, maybe the Commons DbUtils library is something for you: http://commons.apache.org/dbutils/
Or you check out the documentation of your Java application server or servlet engine (Tomcat?) to see what built-in DB connection pooling features it provides.