dflib-jdbc: TableSaver to merge data by key about dflib HOT 4 CLOSED

Did some research on the existing DB / JDBC capabilities. TL;DR: none of them seem to fully address what we need.

1. DB "UPSERT" syntax: Some DBs support explicit syntax for it as a part of the INSERT clause (MySQL, PostgreSQL, SQLServer, Oracle, Derby) , though it only works when we are merging on explicit PK.

2. Updatable ResultSet. Tried it on MySQL (see below). Unfortunately this generates 2 operations per updated row (INSERT/UPDATE + SELECT). So this seems to be intended for GUI data editors, not for bulk operations.

String sql = "SELECT id, name FROM test";
try (PreparedStatement st = c.prepareStatement(
        sql,
        ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE)) {

    try (ResultSet rs = st.executeQuery()) {

        while (rs.next()) {
            String name = rs.getString(2);
            rs.updateString(2, name + "1");
            rs.updateRow();
        }

        rs.moveToInsertRow();
        rs.updateLong(1, new Random().nextLong());
        rs.updateString(2, "inserted");
        rs.insertRow();
    }
}

from dflib.

"UPSERT" operation is done. Current shortcomings of the UPDATE part:

1. We update rows that haven't changed
2. We update all non-key columns, not just the ones that have changed

from dflib.

Done skipping unchanged rows

from dflib.

Done organizing by UPDATE columns

from dflib.