Possible Duplicate:
when to close Connection, Statement, PreparedStatement and ResultSet in JDBC
I've written a simple wrapper for a JDBC connection and it works but I want to improve it with the best practices as possible. It basically has methods like open()
, close()
, isOpened()
, select()
, insert()
, update()
, delete()
and batch()
. For simplicity I will only post here the first 4 methods.
public class Query{
private Connection con;
private PreparedStatement ps;
private ResultSet rs;
//Database.open() returns a Connection ready to use
public void open (Database database) throws DatabaseException, SQLException{
if (!isOpened ()){
con = database.open ();
}
}
public void close () throws SQLException{
if (isOpened ()){
if (ps != null) ps.close ();
con.close ();
con = null;
}
}
public boolean isOpened (){
return con != null;
}
//The query string is the query without the word "select" and can use placeholders (?)
//The args param it's just an array owith the values of this placeholders
public ResultSet select (String query, Object[] args) throws SQLException{
if (ps != null) ps.close ();
if (isOpened ()){
ps = con.prepareStatement ("select " + query);
if (args != null){
for (int i=0; i<args.length; i++){
ps.setObject (i+1, args[i]);
}
}
rs = ps.executeQuery ();
}
return rs;
}
}
Notes:
Connection
, all the PreparedStatement
s and
their ResultSet
s are also closed, right?Usage:
Database database;
//Database initialization
Query query = new Query ();
query.open (database);
ResultSet rs = query.select ("* from user where name=?", new String[]{ "MyName" });
doSomethingWithResult1 (rs);
//Connection is not closed here
ResultSet rs = query.select ("coordx from point where coordy=? and coordz=?", new Float[]{ 0.1, 0.2 });
doSomethingWithResult2 (rs);
query.close ();
query.open (database);
ResultSet rs = query.select ("* from user where name=?", new String[]{ "MyName" });
doSomethingWithResult1 (rs);
//Connection is not closed here
ResultSet rs = query.select ("coordx from point where coordy=? and coordz=?", new Float[]{ 0.1, 0.2 });
doSomethingWithResult2 (rs);
query.close ();
What do you think? Should I close and open the connection after every query? Can I leave opened the PreparedStatement after every query on the same connection? It's a good design?
Connection mysqlCon = DriverManager. getConnection(mysqlUrl, "root", "password"); To connect to multiple databases in a single JDBC program you need to connect to the two (or more) databases simultaneously using the above steps.
The default is 60 seconds.
You have to close the PreparedStatement after you're done with it and before you create a new one on the same connection. I have had serious problems because I did not close PreparedStatements. It turned out that on the database server there were resources allocated that are only freed after an explicit call of PreparedStatement.close().
As bdares commented, the Connection should be opened and closed as infrequently as possible.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With