UpdateString not implemented by SQLite JDBC driver

Question

I have a table PERSON with more than 5 millions rows and I need to update field NICKNAME on each one of them based on the field NAME inside the same table.

ResultSet rs = statement.executeQuery("select NAME from PERSON");
while(rs.next())
{
    // some parsing function like:
    // Nickname = myparsingfunction(rs.getString("NAME"));
    rs.updateString( "NICKNAME", Nickname );
    rs.updateRow();
}

But I got this error:

not implemented by SQLite JDBC driver

I'm using sqlite-jdbc-3.8.11.2.jar downloaded at https://bitbucket.org/xerial/sqlite-jdbc/downloads.

I know I could use the following SQL query:

statement.executeUpdate("update PERSONS set NICKNAME = Nickname where ID = Id");

But that would take forever and I understand updating ResultSet would be faster. So what options do I have to update the table on the fastest way? Any other driver available? Should I move out of Java?

UPDATE

I was able to find a fast solution using below syntax. The block between CASE and END was a concatenated string that I built before executing the SQL query, so I could send all updates at once.

update  PERSON
set     NICKNAME= case ID                        
                        when 173567 then 'blabla'
                        when 173568 then 'bleble'
                        ...
                        when 173569 then 'blublu'
                    end
where ID in (173567, 173568, 173569)

Perhaps look into using a batch: http://stackoverflow.com/questions/14264953/how-is-jdbc-batch-update-helpful — tim, Oct 04 '15 at 12:27
Can the nickname parsing possibly be done in SQL rather than Java? Then the update statement would be a lot faster. — RealSkeptic, Oct 04 '15 at 12:32
Why do you say "that would take forever"? What do you think `updateRow()` does (on JDBC drivers that support it)? Both would send an `UPDATE` SQL statement to the server. Preparing an `UPDATE` statement and batching them would actually be *better* for performance, because `updateRow()` is *not* batched. — Andreas, Oct 04 '15 at 13:29

score 3 · Accepted Answer · edited May 23 '17 at 12:29

As you have encountered, the SQLite JDBC driver does not currently support the updateString operation. This can be seen in the source code for this driver.

I can think of three options:

As you stated in your question, you can select the name and ID of the person and then update the person by its ID. Those updates could be done in a batch (using PreparedStatement.addBatch()) to improve performance (tutorial).
Implement the method myparsingfunction in pure SQL so that the query could become UPDATE PERSONS SET NICKNAME = some_function(NAME).

Create an user-defined function (using org.sqlite.Function), implemented in Java, and call it inside the SQL. Example, taken from this answer:

Function.create(db.getConnection(), "getNickName", new Function() {
    protected void xFunc() throws SQLException {
        String name = value_text(0);
        String nickName = ...; // implement myparsingfunction here
        result(nickName);
    }
});

and use it like this: UPDATE PERSONS SET NICKNAME = getNickName(NAME);

SQLite does not support stored procedures so that option is out of the table.

I'm not sure which of these options would provide the best performance (certainly using pure SQL would be faster but that may not be a viable solution). You should benchmark each solution to find the one that fits you.

UpdateString not implemented by SQLite JDBC driver

1 Answers1