I have swing gui where in the logon screen the user enters user id and password and selects a database instance. After the LOGON button is clicked it calls a method called 'accessDB' where Connection object is used to connect to the database and do a query in a table with user information. After user is authenticated through this query a separate screen with a data entry form opens. User would then populate the data entry fields and hit the SAVE button. This would call another method called 'accessDBADD' to store the data into another table. Here in accessDBADD I would like to connect to the same database instance again execute the necessary sql statement to insert the data. Now, for connecting to the database again in accessDBADD I would like to use the same user id, password and url that was selected from the logon screen. What would be a good way to go about doing this?
The good practice says don't leave your connection open, close them immediately after use. And if you want to use same connection in 'accessDBADD' method then you will have to leave your connection otherwise you would get an error. Also if there are mulitiple user then there would be load on DB.I have two suggestion for you. 1. Close your connection after use. 2. Use connection pools(There are many open source project available for connection pools)
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
The definition of "good practice" is oftentimes relative. I have an application that currently has over 150 classes and does little more than interact with the database. To close the connection after every use only to have to open it again would add an enormous amount of unecessary code and processing to the application. Much to the chagrin of the purists in the development world, I open a connection at the beginning of the application and close it at the end. I suppose that I can get away with this because the user base will be about 6-10. I have a class called Shared that contains the database connection and when I need to create a statement, I have code like:
If I had to create a new connection or pass around a single connection object, I'd go nuts. My advice is to balance "good practice" with common sense and the needs of your particular application/project.