Updating column not working in mariadb from java

Question

I am facing problem with updating column value by resultset in mariadb from java code. Looks like resultset.updateString() method is not supported in mariadb JDBC connector, can anyone please send me the alternate way to do this process.

MariaDB connector version : mariadb-java-client-1.5.8.jar, MariaDB version : mariadb-10.1.20-winx64

Following are the code snippet: Java Code Snippet

Following Exception thrown: Exception Trace

Answer 1

You could use the Statement.executeUpdate() instead. Of cause you will need to change your SELECT statement to an UPDATE statement, too.

The disadvantage is that you loose access to the single row data, because you do not select it at all. If you need this, e.g. to calculate the updated value (in your case test@<localipaddress>) you may have to first fire the select as you did, calculate your updates in memory and then use PreparedStatement or Batch Update to execute the according UPDATE statements.

Prepared Statement example:

public static int preparedUpdate(Connection conn, String localIPAddress) throws SQLException {
        int numChangedRows = 0;

            try (Statement stmt = conn.createStatement()) {
                ResultSet rs = stmt.executeQuery("SELECT * FROM table1");
                while (rs.next()) {
                    // id => something unique for this row within the table,
                    // typically the primary key
                    String id = rs.getString("id");
                    String jid = rs.getString("column1");
                    if("abc".equals(jid)) { // just some nonsense condition
                        try (PreparedStatement batchUpdate = conn.prepareStatement("UPDATE table1 SET column1 = ? where id = ?")) {
                            batchUpdate.setString(1, localIPAddress);
                            batchUpdate.setString(2, id);
                            numChangedRows = batchUpdate.executeUpdate();
                        }
                    }
                }
        }
        return numChangedRows;
    }

Batch Update example:

public static int[] batchUpdate(Connection conn, String localIPAddress) throws SQLException {
        int[] changedRows = null;
        try (PreparedStatement batchUpdate = conn.prepareStatement("UPDATE table1 SET column1 = ? where id = ?")) {
            try (Statement stmt = conn.createStatement()) {
                ResultSet rs = stmt.executeQuery("SELECT * FROM table1");
                while (rs.next()) {
                    // id => something unique for this row within the table,
                    // typically the primary key
                    String id = rs.getString("id");

                    String jid = rs.getString("column1");
                    if("abc".equals(jid)) { // just some nonsense condition
                        batchUpdate.setString(1, localIPAddress);
                        batchUpdate.setString(2, id);
                        batchUpdate.addBatch();
                    }
                }
            }
            changedRows = batchUpdate.executeBatch();
        }
        return changedRows;
    }