CODEWITHSUNDEEP
javapostgresqlstored-proceduresjdbcconnection-pooling
insumity
insumity

Reputation: 5459

Calling Stored Procedures in Java

I'm using JDBC driver for PostgreSQL and I want to call stored procedures.

Assume I have a method that calls a stored procedure as follows:

public void callProcedure(int someValue)    {

    Connection con = null;
    try {
        con = connectionPool.getConnection();

        CallableStatement st = con.prepareCall("{ call some_procedure(?) }");
        st.setInt(1, someValue);
        st.execute();
        st.close();
    }
    catch (SQLException e)  {
        System.out.println(e.getMessage());
    }
    finally     {
        if (con != null) {
            try { con.close(); } // assure connection "goes" back to the pool
            catch (SQLException e)  { }
        }
    }
}

Now let's assume that this method callProcedure could possibly be called million of times. My questions are:

(1.) Would it be better (performance wise) if I create the connection in the constructor of my class and also prepare_the_Call in the constructor, so something like this:

CallableStatement st;

public Constructor() {
   Connection con = connectionPool.getConnection();
   st = con.prepareCall("{call some_procedure(?)}");
}

and then inside the method do:

   public void callProcedure(int someValue) {
    try {
        st.setInt(1, someValue);
        st.execute();
        st.close();
    }
    catch (SQLException e)  {
        System.out.println(e.getMessage());
    }
    finally     {
        if (con != null) {
            try { con.close(); } // assure connection "goes" back to the pool
            catch (SQLException e)  { }
        }
    }
    }

(2.) Does it make sense to close the connection always after executing a statement? Or should I leave it open? From my understanding, closing it gives the connection back to the connection pool so it could be used by someone else. Is that correct?

Upvotes: 0

Views: 385

Answers (1)

Keeley Hoek
Keeley Hoek

Reputation: 543

  1. Putting that statement in the constructor will not lead to an increase in performance. You are simply creating the Connection at a different point in the object life-cycle.

  2. You are right, closing the Connection will immediately return it to the connection pool, instead of waiting for that to happen automatically. It is best practice to do this.

Upvotes: 1

Related Questions