Closes connection and creates a new one not to exceed max cursors after 295 inserts

Question

A previous developer left this message in code.

It runs create statement and execute query and after 295 records it creates new connection.

Following is java code:

private void dbUpdate() throws SQLException, Exception {
        Statement st = null;
        String sql = "";
        int count = 0;
        try {
            getNewConnection();
            conn.setAutoCommit(false);
            for (Iterator it = sqlList.iterator(); it.hasNext();) {
                if (count < 295) { //Closes connection and creates a new one so as not to exceed max cursors
                    count++;
                } else {
                    st.close();
                    conn.close();
                    getNewConnection();
                    count = 0;
                }
                sql = (String) it.next();
//                System.out.println(sql + " insert count=" + count);
                st = conn.createStatement();
                try {
                    st.executeQuery(sql);
                } catch(Exception ex) {
                    Logger.getLogger(LoadMain.class.getName()).log(Level.SEVERE, sql);
                    Logger.getLogger(LoadMain.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
                    sb.append("\n").append("Error SQL:" + sql + "|LocalizedMessage:" +ex.getLocalizedMessage());
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(LoadMain.class.getName()).log(Level.INFO, sql);
            Logger.getLogger(LoadMain.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
            throw new SQLException(ex);
        } catch (Exception ex) {
//            Logger.getLogger(loadMain.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
            throw new Exception(ex);
        } finally {
            try {
                st.close();
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(LoadMain.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

Is there any logic behind re connection? (Also developer set autocommit false but not seen committing or roll-backing but only st.close() methods.)

Could anyone please enlighten

@user7294900: Also any idea why is autocommit set to false but it is not committing or rollbacking so how things are getting committed? does it commit on close? — fatherazrael, Oct 22 '20 at 07:16
Why do you do `executeQuery` when you don't need a ResultSet? If you did `executeUpdate` instead it would most likely not even accumulate all those cursors that you never close in the first place. — Thilo, Oct 22 '20 at 08:08
@Thilo: It is very very old code and i too thinking why use executeQuery over executeUpdate — fatherazrael, Oct 22 '20 at 08:19
@fatherazrael IIRC, the Oracle JDBC driver commits (instead of rollback) on connection close. It looks like the code is relying on that. — Mark Rotteveel, Oct 22 '20 at 09:19
Why is that code using `executeQuery()` when it doesn't use the result set? — Mark Rotteveel, Oct 22 '20 at 09:22

Ori Marko · Answer 1 · 2020-10-22T08:33:16.883

0

Seems as the developer tried to implement connection pooling, which now can be integrated easily with DBCP/Hikari/other database connection pool.

You don't need to commit on DAO level/method, for example if the code is called by a method with @Transactional or commit is handled on service level.

Also you can't rely that close will commit or rollback, there can be different results with different oracle drivers

According to the javadoc, you should try to either commit or roll back before calling the close method. The results otherwise are implementation-defined.

edited Oct 22 '20 at 08:33

answered Oct 22 '20 at 07:20

Ori Marko

56,308
23
131
233

There is no @Transactionsl. It is complete Core Java Code – fatherazrael Oct 22 '20 at 07:36
@fatherazrael the calling method(s) doesn't execute a commit? also maybe `getNewConnection()` is committing once per 256 – Ori Marko Oct 22 '20 at 07:37
No it does not. But data is there in table. So i was wonderous if close() executes commit? – fatherazrael Oct 22 '20 at 07:38
@fatherazrael https://stackoverflow.com/questions/218350/does-java-connection-close-rollback – Ori Marko Oct 22 '20 at 07:42
It says -> Oracle's JDBC driver commits on close() by default. You should not rely on this behaviour if you intend to write multi-platform JDBC code. – fatherazrael Oct 22 '20 at 08:18
Close documentation says -> It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined. (WHAT IS MEANING OF implementation defined?) – fatherazrael Oct 22 '20 at 08:22
@fatherazrael means that you can't rely that close will commit or rollback, accepted answer is *According to the javadoc, you should try to either commit or roll back before calling the close method. The results otherwise are implementation-defined.* – Ori Marko Oct 22 '20 at 08:23
1

@fatherazrael Implementation defined means it depends on the specific implementation of your JDBC driver and database what happens on connection close. – Mark Rotteveel Oct 22 '20 at 09:20
@MarkRotteveel: O got it. So questions arises to which implementations do the same and which not. So it is bad practice then. – fatherazrael Oct 23 '20 at 07:33

Closes connection and creates a new one not to exceed max cursors after 295 inserts

1 Answers1