The Statement variable here CANNOT be a class
variable unless you're taking other steps to
synchronize access. Otherwise, you risk having
two different threads trying to manipulate the
same statement object at the same time.
The Connection object is what holds the connection
to the database. Whether or not that can safely
be class scope depends on your particular JDBC driver
and how you're using it. This might work.
If you're not using auto-commit, this won't work,
since each connection is a single transaction
environment, and you'll have multiple transactions
interfering with one another.
Another ugly problem you'll encounter: many database
servers don't like long-lived connections, and will
spontaneously drop them after a few hours. At the very
least, you should timestamp when you opened the connection
(long timestamp = System.currentTimeMillis();)
and close/reopen it every 30 minutes or so. Also,
you'll want to be sure to ping the connection regularly
in case something goes down (like a bad network cable).
If you have a relatively low-traffic site, opening
one new connection for each request is not a real
problem. I've measured connection opens at around 0.1-0.2
seconds on local MySQL and networked Oracle, which isn't at
all prohibitive for a lot of applications. Plus, that
approach is easy to understand and very reliable.
If you have a higher-traffic site, look into connection
pooling. A good connection pool will cycle the connections,
open more if you need them, and can deal with a lot of other
issues as well.
- Tim Kientzle
> public class ServletSQLClass
> {
> private Connection con;
> private Statement stmt;
>
> ...
> con = DriverManager.getConnection(url,user,passwd);
> stmt = con.createStatement();
> ...
> }
>
> con and stmt have to be class scope to hold the connection to the
> database and don't have to reopen over and over. Or did I understand
> something wrong?