CODEWITHSUNDEEP
javamysqljdbcresultset
Peter Kazazes
Peter Kazazes

Reputation: 3628

Java ResultSet out of memory, can't update with PreparedStatement

I've been loading a growing and growing ResultSet into memory and until now it really hasn't been an issue. It is now too large to reasonably work with. I've looked into creating scrollable result sets, however I'm having some trouble implementing it. Here's what I've tried:

    Statement stmt = con.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(Integer.MIN_VALUE);
    try {

        if (stmt.execute("SELECT text, value FROM " + db)) {
            rs = stmt.getResultSet();
        } else {
            System.err.println("select failed");
        }

Is there some other parameter for the Statement that I'm missing? Or is there another option that I have to set somewhere else?

EDIT:

Turns out the issue wasn't the stream itself, but how I was updating the RS. Can I not update the RS if it's streaming? Console is popping off the following:

SQLException: Operation not supported for streaming result sets
SQLState: S1009
VendorError: 0

Here's the code I'm using to update the DB after they've been processed:

rs.getDouble("value");
if (rs.wasNull()) {
StringValue analysis = new StringValue(dict);
int id = rs.getRow();
String entry = rs.getString("text");
double val = analysis.analyzeString(entry);
pst.setDouble(1, val);
st.setInt(2, id);
System.out.println(id+": " + val + " : " + rs.getString(5));
pst.executeUpdate();

The prepared statement is structured like so:

pst = conn.prepareStatement("UPDATE "+ db +" SET value=? WHERE id=?");

Fairly straightforward, StringValue method analyzeString is essentially sentiment analysis of the input string.

Upvotes: 0

Views: 1238

Answers (3)

Choon Keat
Choon Keat

Reputation: 24

Limit your rows:

"select text, value from " + db  + " limit 100"

or

"select  text,value from " + db  + " where text like 'A%'"

Upvotes: 1

Yongxin Zhao
Yongxin Zhao

Reputation: 124

Statement stmt = con.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);

You should use Statement stmt = con.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE,java.sql.ResultSet.CONCUR_UPDATABLE);

Upvotes: 0

Jemyxu
Jemyxu

Reputation: 33

I advise you to add some parameters for JVM in order to enlarge JVM memory, such as -Xmx, -Xms, you can refer to JVM Memory Structure to get more details.

Upvotes: 0

Related Questions