Announcement Announcement Module
No announcement yet.
JDBC multiple preparedstatements, multiple DAOs Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC multiple preparedstatements, multiple DAOs


    I have an application that writes multiple rows to 4 tables, lets call them A,B,C and D. These tables are related and writes all happen in a single transaction. All this is working fine.

    Due to existing DAO usage, separate from my project, A and B are within one DAO class, whilst C and D are in a second one - each object (X) having a corresponding add(X) type call on the DAO. We have come to the stage where we need to optimise this operation - i.e. by using PreparedStatements. The writes of each set of objects need to be A1,B1,C1,D1,A2,B2,C2,D2,A3... (as opposed to A1,A2,A3,B1,B2,B3,C1,C2.....) - so I can't just add "list" add functions on the dao which I would normally do.

    The options I see are
    i) Make a new DAO which can handle the 4 prepared statements and copy the extra bit of logic and checking from the original DAOs (which need to retain their single add functions). Downside being code duplication.
    ii) Return the PreparedStatement objects back to the Service layer to then pass as variables on each particular add. (This has to be thread safe, so I don't think I can just store the PreparedStatement as a class instance member.) The downside being that doing this sounds ugly.

    Can I also ask, what is the lifetime of a PreparedStatement? I am used to some other (Delphi) code in which the PreparedStatement is only valid for the current database transaction / connection. So I am currently presuming the same rule applies with JDBC. It certainly looks that way from the many examples I have found.

    What are peoples thoughts? Unfortunately JPA is not an option at the moment!