SonicBase. In-memory embedded, standalone or distributed sql database

Stored Procedures - Example 1

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.

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.MyStoredProcedure1'";
      try (PreparedStatement procedureStmt = conn.prepareStatement(query);
          ResultSet rs = procedureStmt.executeQuery()) {
        while (rs.next()) {
          System.out.println("id=" + rs.getLong("id1") + ", socialsecuritynumber=" +
              rs.getString("socialsecuritynumber") + ", gender=" + rs.getString("gender"));
        }
        System.out.println("Finished");
      }
  }

Stored Procedure Code


public class MyStoredProcedure1 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'";

      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()) {
                return true;
              }
            }
            return false;
          }
        })) {

          while (rs.next()) {
            Record record = context.createRecord();
            record.setLong("id1", rs.getLong("id1"));
            record.setString("socialsecuritynumber", rs.getString("socialsecuritynumber"));
            record.setString("gender", rs.getString("gender"));
            response.addRecord(record);
          }
        }
      }
      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;
  }
}