CODEWITHSUNDEEP
javajdbc
Franto
Franto

Reputation: 3

JDBC PreparedStatements vs Objects - Where to put initialization

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?

Upvotes: 0

Views: 1165

Answers (2)

Gandalf
Gandalf

Reputation: 2348

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

Upvotes: 2

A.H.
A.H.

Reputation: 66223

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.

Upvotes: 0

Related Questions