Re: Database helper class with PreparedStatements
Are Nybakk wrote:
I started replying to this post, but I gave it up. I don't quite get
what you want answered here. I'll give you some tips on how to organize
things.
Make a class that takes care of the database connection:
public class DatabaseConnector {
private Connection con;
public DatabaseConnector(user, pass, ...) {
con = ...;
}
public Connection getConnection() {
return con;
}
public void close() {
con.close();
}
//...
}
What does that class provide that Connection does not ?
And a database handler class:
public class DatabaseHandler {
private DatabaseConnector dbCon;
private Connection con;
private PreparedStatement stmt1 = new PreparedStatement("...");
public DatabaseHandler(user, pass, ...) {
dbCon = new DatabaseConnector(user, pass, ...);
con = dbCon.getConnection();
}
public void insertSomeObject(SomeObject obj) {
stmt.setXxx(...);
//...
stmt.executeXxx();
}
/...
public void closeConnection() {
dbCon.close();
}
}
That DatabaseConnector is not needed is emphasized by the fact
that you have both the DatabaseConnector wrapper and the underlying
Connection as fields here.
Furthermore you should really open and close connection in the
insert method. If you keep the DatabaseHandler around then you
collect way to many database connections. If you construct and
close DatabaseHandler for every insert call, then you could
just as well do everything in the insert method.
Remember that the point of PreparedStatements is a way to declare
statements that are frequently used. To declare such a statement for
every method call removes the point entirely.
PreparedStatement provides other useful functionality including:
- handling of special characters like ' (both relevant for
scottish names and malicious SQL injection)
- date format handling
About closing connections, simply close them when you won't be using it
for a while. I don't think open connections would make any problem
unless the database is accessed by a lot of clients simultaneously.
If it does not cost anything to make the code scalable, then why'
not do it ?
You never know how long time some code will live and in how many
different contexts it will be used !
Arne