Java return SQL statement

Question

I am doing code review, and in many cases I found result is not closed if SQL statement returns the records. The business rule for SQL statement is consider only the first record.

Below is the code where I am confused, Why is it returning the value without closing resultset and statement? Is this correct way?

if (rs.next()){
        return new Long(rs.getLong(1));
}

Below is sample code:

private static Long <MethodName>
     (
         oracle.sql.NUMBER[] o_errorCode,
         oracle.sql.CHAR[] o_errorText,
         Long portA,
         Long portZ) throws SQLException
     {
         String errorMessage = "";
         Long[] NROLE = new Long[1];
         Long[] pTask = new Long[1];
         Long dimObject = null;
         Long objectIDA = null;
         Long objectIDZ = null;
         Long relation = null;
         Connection tmpConn = null;
         Statement stmt = null;
         ResultSet rs = null;
         String SQL = null;
         try
         {
             // Retrieve the Epipe circuits that are on the specified ports
             stmt = DbUtil.getConn().createStatement();
             String query = "Select * from Circuit where ... Order by Id";
                 rs = stmt.executeQuery(query);
              if (rs.next()){
                     return new Long(rs.getLong(1));
                }
                 rs.close();
                 stmt.close();
             return null;
         }
         catch (SQLException ex)
         {
             o_errorCode[0] = new oracle.sql.NUMBER(1);
             o_errorText[0] = new oracle.sql.CHAR("SQLException - " + ex.getMessage(),
             oracle.sql.CharacterSet.make(oracle.sql.CharacterSet.DEFAULT_CHARSET));
             return(null);
         }
         catch (Exception e)
         {
             o_errorCode[0] = new oracle.sql.NUMBER(1);
             o_errorText[0] = new oracle.sql.CHAR("Exception - " + e.getMessage(), oracle.sql.CharacterSet.make(
             oracle.sql.CharacterSet.
             DEFAULT_CHARSET));
             return(null);
         }
}

How manage resource closing in finally block?

Wundwin Born
– Wundwin Born

2014-07-02 14:17:59 +00:00
Commented Jul 2, 2014 at 14:17 — Wundwin Born
– Wundwin Born, Commented Jul 2, 2014 at 14:17

Community · Accepted Answer · 2017-05-23 12:20:45Z

2

Not correct, and since Java 7 easily resolved when using try-with-resources:

try (ResultSet rs = statement.executeQuery()) {
    if ...
        return ...
}

That if rs.next() might do with a better SQL: a kind of limit or a MIN(Id) of a kind.

It is not good style too:

Use a consistent style: SELECT FROM WHERE ORDER BY or select from where by.
Do not use database specific classes; JDBC took much effort to not need it.
I almost suspect that here a PreparedStatement could have been used. Aside from preventing SQL injection it escapes parameters nicely.

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jul 2, 2014 at 14:19

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nikhil Talreja · Accepted Answer · 2014-07-02 14:18:37Z

0

Use finally.

    try {
            // Retrieve the Epipe circuits that are on the specified ports
            stmt = DbUtil.getConn().createStatement();
            String query = "Select * from Circuit where ... Order by Id";
            rs = stmt.executeQuery(query);
            if (rs.next()) {
                return new Long(rs.getLong(1));
            }

            return null;
        } catch (SQLException ex) {

        } catch (Exception e) {

        } finally {
            rs.close();
            stmt.close();
        }

answered Jul 2, 2014 at 14:18

Nikhil Talreja

2,7741 gold badge16 silver badges21 bronze badges

Comments

ovunccetin · Accepted Answer · 2014-07-02 14:24:23Z

0

The correct place to close database resources like result set, statement or connection is the finally block of a try-catch statement. Because, JVM visits this block in any condition.

The following usage is safe:

ResultSet rs = null;
Statement stmt = null;
Connection conn = null;
try {
    conn = SomeDBUtility.getConnection();
    stmt = conn.createStatement();
    rs = stmt.executeQuery("<Your SQL string>");
} catch (SQLException e) {
    // handle the exception
} finally {
    rs.close();
    stmt.close();
    conn.close();
}

answered Jul 2, 2014 at 14:24

ovunccetin

8,7635 gold badges46 silver badges53 bronze badges

Collectives™ on Stack Overflow

Java return SQL statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related