Using a FilteredRowSet - Java JDBC

Java examples for JDBC:FilteredRowSet

  1. HOME
  2. Java
  3. JDBC
  4. FilteredRowSet

Description

Using a FilteredRowSet

Demo Code

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

import javax.sql.RowSet;
import javax.sql.rowset.FilteredRowSet;
import javax.sql.rowset.Predicate;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

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

    try (FilteredRowSet filteredRs = factory.createFilteredRowSet()) {

      RowSetUtil.setConnectionParameters(filteredRs);
      String sqlCommand = "select person_id, first_name, last_name "
          + "from person";
      filteredRs.setCommand(sqlCommand);
      filteredRs.execute();//  w w  w.j  a  v a  2s  .c  o  m

      System.out.println("Before Filter - Row count: " + filteredRs.size());
      RowSetUtil.printPersonRecord(filteredRs);

      Predicate filter = new RangeFilter(1, "person_id", 101, 102);
      filteredRs.setFilter(filter);

      System.out.println("After Filter - Row count: " + filteredRs.size());
      filteredRs.beforeFirst();
      RowSetUtil.printPersonRecord(filteredRs);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

class RangeFilter implements Predicate {
  int columnIndex;
  String columnName;
  double min;
  double max;

  public RangeFilter(int columnIndex, String columnName, double min, double max) {
    this.columnIndex = columnIndex;
    this.columnName = columnName;
    this.min = min;
    this.max = max;
  }

  @Override
  public boolean evaluate(RowSet rs) {
    try {
      if (rs.getRow() <= 0) {
        return false;
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }

    boolean showRow = false;
    Object value = null;

    try {
      value = rs.getObject(columnName);
      if (value instanceof Number) {
        double num = ((Number) value).doubleValue();
        showRow = (num >= min && num <= max);
      }
    } catch (SQLException e) {
      showRow = false;
      e.printStackTrace();
      throw new RuntimeException(e);
    }
    return showRow;
  }

  @Override
  public boolean evaluate(Object value, int columnIndex) {
    boolean showRow = false;
    if (columnIndex == this.columnIndex && value instanceof Number) {
      double num = ((Number) value).doubleValue();
      showRow = (num >= min && num <= max);
    }
    return showRow;
  }

  @Override
  public boolean evaluate(Object value, String columnName) {
    boolean showRow = false;
    if (this.columnName.equalsIgnoreCase(columnName) && value instanceof Number) {
      double num = ((Number) value).doubleValue();
      showRow = (num >= min && num <= max);
    }
    return showRow;
  }
}

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();
  }
}

Related Tutorials