Should I close and create a new statement after every time I call execute()?

Question

If I create a statement with JDBC and execute a query, do I need to close said statement and create a new one before executing again? Eclipse doesn't complain about the second case.

try {
        connection = dataSource.getConnection();

        try {
            statement = connection.createStatement();
            statement.execute("set search_path to '...'");
        } finally {
            Utils.tryClose(statement);
        }

        try {
            statement = connection.createStatement();
            statement.execute("SET statement_timeout TO " + (QUERY_TIMEOUT_SECONDS * 1000));
        } finally {
            Utils.tryClose(statement);
        }

        try {
            statement = connection.createStatement();
            statement.execute(query);
        } finally {
            Utils.tryClose(statement);
        }
} finally {
    Utils.tryClose(connection);
}

As opposed to:

try {
    connection = dataSource.getConnection();

    statement = connection.createStatement();
    statement.execute("set search_path to '...'");
    statement.execute("SET statement_timeout TO " + (QUERY_TIMEOUT_SECONDS * 1000));
    statement.execute(query);
} finally {
    Utils.tryClose(statement);
    Utils.tryClose(connection);
}

Answer 1

That is not required you can use the same statement to query the DB multiple times, the only thing to remember is that each resultset returned with a statement execution will be closed after creating a new statemnet. Quoting from java docs:-

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

Hence you can do something like this:-

try {
     connection = dataSource.getConnection();

     statement = connection.createStatement();
     ResultSet rs1=statement.execute("....");

     //parse rs1
     //close rs1

     ResultSet rs2= statement.execute(....);
     //parse rs1
     //close rs1

  } finally {
    Utils.tryClose(statement);
    Utils.tryClose(connection);
  }

I am not sure why eclipse is complaining in case of PreparedStatements, the whole purpose of PreparedStatements is to define a query structure and execute the query multiple times by only changing the parameters. For example when you want to parse and insert a large text file into DB. Quoting from javadocs

If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.