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
}

回答1:

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

next() returns a boolean, check it.

回答2:

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

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

   }

回答3:

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.

回答4:

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;
    }

回答5:

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"); 
} 

回答6:

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