CODEWITHSUNDEEP
javajdbc
Patrick Jeon
Patrick Jeon

Reputation: 1814

jdbc ResultSet closed

I have following source.

In insertMessage(..), it calls selectMessage to check whether duplicate record exists or not.

But this error occurs. In my brain, it works fine because datasource gives me new Connection...maybe

java.sql.SQLException: ResultSet closed
    at org.sqlite.RS.checkOpen(RS.java:63)
    at org.sqlite.RS.findColumn(RS.java:108)
    at org.sqlite.RS.getString(RS.java:317)
    at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263)
    at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263)
    at org.springframework.context.support.CachedMessageSourceDao.selectMessage(CachedMessageSourceDao.java:68)
    at org.springframework.context.support.CachedMessageSourceDao.insertMessage(CachedMessageSourceDao.java:94)
    at MessageSourceDemo.main(MessageSourceDemo.java:11)

public String selectMessage(String code, String language) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String value = null;

    String sql = "SELECT code, value, language FROM " + TABLE + " where code=? and language=? and flag = '" + FLAG_OK + "'";

    try {
        conn = dataSource.getConnection();
        conn.setAutoCommit(true);
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, code);
        pstmt.setString(2, language);
        rs = pstmt.executeQuery();
        rs.next();

        String _code = rs.getString("code");
        String _value = rs.getString("value");
        String _language = rs.getString("language");
        Locale _locale = new Locale(_language);
        value = _value;

    } catch(SQLException ex) {

        ex.printStackTrace();

    } finally {

        try {
            if(rs != null);
            if(pstmt != null) pstmt.close();
            if(conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    return value;
}

public synchronized void insertMessage(String code, String value, String language) throws SQLException {
    //Duplicate Message Check
    **if(selectMessage(code, language) != null) throw new SQLException("Duplicate message exists for code: " + code + " and" + "language: " + language);**

    String sql = "INSERT INTO " + TABLE + " (code, value, language, flag) values (?, ?, ?, '" + FLAG_OK + "')";

    Connection conn = null;
    PreparedStatement pstmt = null;

    try {
        conn = dataSource.getConnection();
        conn.setAutoCommit(true);
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, code);
        pstmt.setString(2, value);
        pstmt.setString(3, language);
        pstmt.execute();

    } catch(SQLException ex) {

        ex.printStackTrace();

    } finally {

        try {

            if(pstmt != null) pstmt.close();
            if(conn != null) conn.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    notifyMessageChange(); //Realtime apply to MessageSource
}

Upvotes: 4

Views: 15474

Answers (6)

JHS
JHS

Reputation: 7871

You should check what rs.next returns. If it is false means nothing was fetched.

Now if you use if(rs.next) then you are considering only the 1st row that the ResultSet has returned. If the query returns more than 1 rows and you want to consider all the rows then use while(rs.next).

Again even if you add while(rs.next) above your code then the _code, _value, _language _locale would have the values of the last row returned in the ResultSet. So you have to modify your code accordingly.

Upvotes: 2

Akhi
Akhi

Reputation: 2242

Refer this page. 

public boolean next()
                 throws SQLException
    Moves the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.
    ***If an input stream is open for the current row, a call to the method next will implicitly close it.*** A ResultSet object's warning chain is cleared when a new row is read.

    Returns:
    true if the new current row is valid; false if there are no more rows
    Throws:
    SQLException - if a database access error occurs

Upvotes: 1

Addicted
Addicted

Reputation: 1704

In place of this - 

rs.next();
String _code = rs.getString("code");
String _value = rs.getString("value");
String _language = rs.getString("language");

Use this - 

while(rs.next()) //or use if(if there is only one row in resultset)
{
    String _code = rs.getString("code");
    String _value = rs.getString("value");
    String _language = rs.getString("language"); 
}

Upvotes: 1

Simon Dorociak
Simon Dorociak

Reputation: 33495

You cannot only add rs.next() because ResultSet can be empty so you have to add condition and tests if result of next() is valid row, else it returns false.

conn = dataSource.getConnection();
        conn.setAutoCommit(true);
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, code);
        pstmt.setString(2, language);
        rs = pstmt.executeQuery();
        if (rs.next())

        String _code = rs.getString("code");
        String _value = rs.getString("value");
        String _language = rs.getString("language");
        Locale _locale = new Locale(_language);
        value = _value;
    }

Upvotes: 1

ejb_guy
ejb_guy

Reputation: 1125

Check the value of rs.next(); It must be returning false. You need to do this.

   if(rs.next()){
       //get data

   }

Upvotes: 2

Denys Séguret
Denys Séguret

Reputation: 382122

Your resultset probably didn't have any record, which is the reason why next() closed it.

next() returns a boolean, check it.

Upvotes: 7

Related Questions