Updating and Detecting Conflicts in a CachedRowSet

Description

Demo Code

import static javax.sql.rowset.spi.SyncResolver.DELETE_ROW_CONFLICT;
import static javax.sql.rowset.spi.SyncResolver.INSERT_ROW_CONFLICT;
import static javax.sql.rowset.spi.SyncResolver.UPDATE_ROW_CONFLICT;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
import javax.sql.rowset.spi.SyncProviderException;
import javax.sql.rowset.spi.SyncResolver;

public class Main {
  public static void main(String[] args) throws SQLException {
    RowSetFactory factory = RowSetUtil.getRowSetFactory();
    CachedRowSet cachedRs = factory.createCachedRowSet();

    try {//  ww  w . j  av  a 2  s. com
      RowSetUtil.setConnectionParameters(cachedRs);
      String sqlCommand = "select person_id, first_name, last_name, "
          + "gender, dob, income " + "from person "
          + "where person_id between 1 and 3";

      cachedRs.setKeyColumns(new int[] { 1 });
      cachedRs.setCommand(sqlCommand);
      cachedRs.execute();
      System.out.println("Row Count: " + cachedRs.size());
      RowSetUtil.printPersonRecord(cachedRs);
      if (cachedRs.size() > 0) {
        updateRow(cachedRs, 1, 3.00);
      }
      insertNewRow(cachedRs);
      cachedRs.acceptChanges();
      System.out.println("Row Count: " + cachedRs.size());
      cachedRs.beforeFirst();
      RowSetUtil.printPersonRecord(cachedRs);
    } catch (SyncProviderException spe) {
      SyncResolver resolver = spe.getSyncResolver();
      printConflicts(resolver, cachedRs);
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      if (cachedRs != null) {
        try {
          cachedRs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }

  public static void insertNewRow(CachedRowSet cachedRs) throws SQLException {
    cachedRs.moveToInsertRow();
    cachedRs.updateInt("person_id", 1);
    cachedRs.updateString("first_name", "Mason");
    cachedRs.updateString("last_name", "Baker");
    cachedRs.updateString("gender", "M");
    cachedRs.updateDate("dob", java.sql.Date.valueOf("2017-01-02"));
    cachedRs.updateDouble("income", 0.00);
    cachedRs.insertRow();
    cachedRs.moveToCurrentRow();
  }

  public static void updateRow(CachedRowSet cachedRs, int row, double newIncome)
      throws SQLException {
    cachedRs.absolute(row);
    cachedRs.updateDouble("income", newIncome);
    cachedRs.updateRow();
  }

  public static void printConflicts(SyncResolver resolver, CachedRowSet cachedRs) {
    try {
      while (resolver.nextConflict()) {
        int status = resolver.getStatus();
        String operation = "None";
        if (status == INSERT_ROW_CONFLICT) {
          operation = "insert";
        } else if (status == UPDATE_ROW_CONFLICT) {
          operation = "update";
        } else if (status == DELETE_ROW_CONFLICT) {
          operation = "delete";
        }
        Object oldPersonId = resolver.getConflictValue("person_id");
        int row = resolver.getRow();
        cachedRs.absolute(row);
        Object newPersonId = cachedRs.getObject("person_id");

        System.out.println("Conflict detected in row #" + row + " during "
            + operation + " operation." + " person_id in database is "
            + oldPersonId + " and person_id in rowset is " + newPersonId);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

class RowSetUtil {
  private static boolean driverLoaded = false;

  public static void setConnectionParameters(RowSet rs) throws SQLException {
    if (!driverLoaded) {
      Driver derbyEmbeddedDriver = null;// new
                                        // org.apache.derby.jdbc.EmbeddedDriver();
      DriverManager.registerDriver(derbyEmbeddedDriver);

      driverLoaded = true;
    }

    // Set the rowset database connection properties
    String dbURL = "jdbc:derby:beginningJavaDB;create=true;";
    String userId = "root";
    String password = "password";
    rs.setUrl(dbURL);
    rs.setUsername(userId);
    rs.setPassword(password);
  }

  public static RowSetFactory getRowSetFactory() {
    try {
      RowSetFactory factory = RowSetProvider.newFactory();
      return factory;
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }

  // Print person id and name for each person record
  public static void printPersonRecord(RowSet rs) throws SQLException {
    while (rs.next()) {
      int personId = rs.getInt("person_id");
      String firstName = rs.getString("first_name");
      String lastName = rs.getString("last_name");
      System.out.println("Row #" + rs.getRow() + ":" + " Person ID:" + personId
          + ", First Name:" + firstName + ", Last Name:" + lastName);
    }

    System.out.println();
  }
}
Updating and Detecting Conflicts in a CachedRowSet - Java JDBC

Description

Demo Code

Related Tutorials
