Re: Database helper class with PreparedStatements
Arne VajhHj wrote:
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 ?
Let me quote from a thread in comp.lang.java.databases:
"Lew wrote:
>> Which is worse, to take a while to construct a connection, or to run
out of connections because your objects never let them go?
David Harper wrote:
> Better *not* to let each object create its own connection. That way,
madness lies. As you say, this argues strongly for a connection pool.
The application code doesn't change when you switch to a connection
pool. The semantics of the close() call change to "release to the pool"
instead of "destroy"."
Now I better understand why I've learned to do it this way. This wrapper
class is a connection pool of sorts. One with only one connection.
The pool could contain more connections if needed (several databases
perhaps) and a time-out for closing unused, open connections. It should
also have a way of "locking" the connection.
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.
Then rather, get a reference to a connection from the pool and "free" it
afterwards.
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