SonicBase. In-memory embedded or distributed streaming sql database

Stored Procedures - Example 3

Overview

This example shows how you can return a small result set directly to the client. Records are added to the "StoredProcedureResponse" and returned to the server where the "finalize" method is being called. The results are aggregated in the finalize, then returned to the client. Note that the results are sorted in the finalize method before returning to the client.

This example is different than the first example in that records received in the RecordEvaluator are directly returned.

It is important to call SonicBasePreparedStatement.restrictToThisServer(true) before executing the query. This keeps traversal on this server.

Client Code


  @Test
  public void test3() throws SQLException {
      String query = "call procedure 'com.sonicbase.procedure.MyStoredProcedure3'";
      try (PreparedStatement procedureStmt = conn.prepareStatement(query);
          ResultSet rs = procedureStmt.executeQuery()) {
        int offset = 3;
        while (true) {
          if (!rs.next()) {
            break;
          }
          System.out.println("id=" + rs.getLong("id1") + ", socialsecuritynumber=" +
              rs.getString("socialsecuritynumber") + ", gender=" + rs.getString("gender"));
        }
        System.out.println("Finished");
      }
  }
        

Stored Procedure Code


public class MyStoredProcedure3 implements StoredProcedure {

  public void init(StoredProcedureContext context) {
  }

  @Override
  public StoredProcedureResponse execute(StoredProcedureContext context) {
    try {
      String query = "select * from persons where id1>1 and id1<500 and gender='m'";

      final StoredProcedureResponse response = context.createResponse();

      try (SonicBasePreparedStatement stmt = context.getConnection().prepareSonicBaseStatement(context, query)) {
        stmt.restrictToThisServer(true);

        try (ResultSet rs = stmt.executeQueryWithEvaluator(new RecordEvaluator() {
          @Override
          public boolean evaluate(final StoredProcedureContext context, Record record) {
            if (!record.getDatabase().equalsIgnoreCase("db") ||
                !record.getTableName().equalsIgnoreCase("persons")) {
              return false;
            }
            Long id = record.getLong("id1");
            if (id != null && id > 2 && id < 100 && passesComplicatedLogic(record)) {
              if (!record.isDeleting()) {
                response.addRecord(record);
              }
            }
            return false;
          }
        })) {
        }
      }
      return response;
    }
    catch (Exception e) {
      throw new DatabaseException(e);
    }
  }

  public StoredProcedureResponse finalize(StoredProcedureContext context,
                                          List<StoredProcedureResponse> responses) {
    List<Record> records = new ArrayList();
    for (StoredProcedureResponse currResponse : responses) {
      records.addAll(currResponse.getRecords());
    }

    Collections.sort(records, new Comparator<Record>() {
      @Override
      public int compare(Record o1, Record o2) {
        return Long.compare(o1.getLong("id1"), o2.getLong("id1"));
      }
    });

    StoredProcedureResponse response = context.createResponse();
    response.setRecords(records);
    return response;
  }

  private boolean passesComplicatedLogic(Record rs) {
    //put complicated logic here
    return true;
  }
}