jdbc software architecture design, best practice

hi all,

I hadn't used any jdbc for some time, but I had to come back at it most
recently. so I dug out some code of mine which I had used a few years
ago - both mysql (which I'm using) and jdbc connector have evolved
somewhat in the meantime. probably a good thing.
it also made me rethink the way I used to structure my applications. I
would encapsulate the code I'd need to set up the query strings, the
code needed to get the database connection and the code needed to
execute the queries themselves in different classes. ideally, I'd have
a few separate utility type classes that I can reuse at some point.
after executing a query, I'd get back a resultset and in my previous
apps, I'd just returned this resultset to the calling method, to then
display it to the user or do some further processing.
maybe I was just lucky or the API was just very forgiving - I now get
errors, indicating that a closed resultset is being read from (or
attempted to be read from). I looked in up in the docs, and yes, a
resultset is automatically being closed when either (or both) the
connection or the exectuting statement are being closed. and because an
unused open connection is bad thing (suposedly), I had always closed
any connection right after statement execution and then passed the
resultset on. wel, not anymore.
what is best practice in this case? read out all necessary data right
in my utility class? possibly writing it to a hashtable, enum or some
other type of suitable container and then passing that on?
a workaround I've been using for now is to leave the connection open
somewhat longer, but I'm not really pleased with it.
well, I'd appreciate some expert tips on this, thanks a lot!
anselm

.

Relevant Pages

  • Re: jdbc software architecture design, best practice
    ... anselm - I'm pretty new to Java but I suppose I should try to help whenever ... populate it from my resultset from query. ... execute the queries themselves in different classes. ... connection or the exectuting statement are being closed. ...
    (comp.lang.java.databases)
  • Re: How to use PreparedStatements on a servlet the most efficient way?
    ... PreparedStatement should be executed, and the time to create the ... PreparedStatements are tied to the connection that created them. ... you lose that ResultSet - it closes. ... Which further implies that if you have two clients who need to execute the ...
    (comp.lang.java.databases)
  • [Full-Disclosure] Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit ... Connection closed by foreign host. ... think what we want to execute. ...
    (Full-Disclosure)
  • Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit we are ... Connection closed by foreign host. ... think what we want to execute. ...
    (Bugtraq)
  • Re: looking for opinons regarding best practices (jdbc, resultsets, and servlet design)
    ... (switching from JDBC to JNDI or a Web service, ... of data entity value objects around without keeping a connection open. ... back non-Cached RowSets or ResultSets requires an active connection. ... In real life one would likely want to log this exception. ...
    (comp.lang.java.programmer)