JDBC PreparedStatements vs Objects - Where to put initialization

Question

What is the best place to put PreparedStatement initialization, when i want to use it for all instances of given class?

My solution is so far to create static methods for opening and closing, but i don't find it quite the right choice:

class Person {
    protected static PreparedStatement stmt1;
    protected static PreparedStatement stmt2;

    protected static void initStatements(Connection conn) {
        stmt1 = conn.PrepareStatement("select job_id from persons where person_id=:person_id");
        stmt2 = conn.PrepareStatement("update persons set job_id=:job_id where person_id=:person_id");
    }

    protected static void closeStatements() {
        stmt1.close();
        stmt2.close();
    }
    public void increaseSalary() {
        stmt1.execute(); // just a example 
        stmt2.execute();
    }
}

void main {
    // create prepared statements
    Person.initStatements(conn);

    // for each person, increase do some action which require sql connection
    for (Person p : getAllPersons()) {
        p.increaseSalary();
    }

    // close statements
    Person.closeStatements();
}

Isn't there any other way how to use PreparedStatements inside multiple instances of class?

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

Will person be your domain logic class? Then I recommend not to put the data access methods and PreparedStatements in there but in a separate data access object.

Will the DAO methods be called asynchronously for example in a web application? Then I recommend to not reuse either PreparedStatements or Connections between those calls at all. For Connections I'd use a Connection pool. More on reusing PreparedStatements: Reusing a PreparedStatement multiple times

edited May 23 '17 at 10:34

Community

1
1

answered Sep 23 '11 at 12:53

Gandalf

2,350
20
28

Thanks, since this was just a small program, i put all my database requests into common dao object, which i pass into domain logic classes (e.g. person). – Franto Sep 27 '11 at 06:10

score 0 · Answer 2 · answered Sep 23 '11 at 12:48

Usually it is better to use a ConnectionSurvivalPack and give this to everyone involved:

Class SurvivalPack {
    private Connection connection;
    private PreparedStatement st1;
    // add constructor and appropriate getter/setter
    // getter for PreparedStatements could create statements on demand
    void close(){
        st1.close();
        con.close();
    }
}

void main(...){
   SurvivalPack survivalPack = new SurvivalPack(conn);
   for(Person p: getAllPersons()){
       p.increaseSalary(survivalPack);
   }
   survivalPack.close();
}

Pros:

Multithreading is no problem, since the resources are not shared between threads.
All database resources are bundled in one place. This makes management of resources easier and more consistent.
It is much easier to follow the flow of the code and the involved resources because no side effects from semiglobal variables can happen.

JDBC PreparedStatements vs Objects - Where to put initialization

2 Answers2