Database Operation Templates

Working with the Database Operations in XtendM3

Description
1. CRUD Templates

Description

This document contains diffrent templates for various database operations.

CRUD Templates

These templates outline best practices for Create, Read, Update and Delete operations.

Create

Creating, or adding one or several new records to table.

void create() {
  DBAction query = database.table("TABLENAME")
    .index("00")
    .build();
  DBContainer container = query.getContainer();
  container.setString("FIELD1", "DATA1");
  container.setString("FIELD2", "DATA2");
  container.setInt("FIELD3", 3);
  container.setChar("FIELD4", '4');
  query.insert(container);
}

Read

Reading one or several records from table.

Read one record

void read() {
  DBAction query = database.table("TABLENAME")
    .index("00")
    .selection("FIELD3", "FIELD4")
    .build();
  DBContainer container = query.getContainer();
  container.setString("FIELD1", "DATA1");
  container.setDouble("FIELD2", 2.0);
  if(query.read(container)) {
    String message = container.getString("FIELD3");
    Character character = container.getDouble("FIELD4");
    //Use found record(s) as intended
  }
}

Read multiple records

void multiRead() {
  DBAction query = database.table("TABLENAME")
    .index("00")
    .selection("FIELD3", "FIELD4")
    .build();
  DBContainer container = query.getContainer();
  container.setString("FIELD1", "DATA1");
  container.setInt("FIELD2", 2);
  int nrOfKeys = 2;
  int nrOfRecords = mi.getMaxRecords() <= 0 || mi.getMaxRecords() >= 10000? 10000: mi.getMaxRecords();
  query.readAll(container, nrOfKeys, nrOfRecords, callback);
}
Closure<?> callback = { DBContainer container ->
  String message = container.getString("FIELD3");
  Integer data = container.getInt("FIELD4");
    //Note: Integer can store null value, while int can store null value,
      // keep that in mind while designing your extension  
  //Use found record(s) as intended
}

Read multiple records with filter

void filteredMultiRead() {
  ExpressionFactory expression = database.getExpressionFactory("TABLENAME");
  expression = expression.eq("FIELD3", "DATA3").and(expression.lt("FIELD1", "DATA1"));
  DBAction query = database.table("TABLENAME")
    .index("00")
    .matching(expression)
    .selection("FIELD3", "FIELD4")
    .build();
  DBContainer container = query.getContainer();
  container.setString("FIELD1", "DATA1");
  container.setInt("FIELD2", 2);
  int nrOfKeys = 2;
  int nrOfRecords = mi.getMaxRecords() <= 0 || mi.getMaxRecords() >= 10000? 10000: mi.getMaxRecords();  
  query.readAll(container, nrOfKeys, nrOfRecords, callback);
}
Closure<?> callback = { DBContainer container ->
  String message = container.getString("FIELD3");
  Integer data = container.getInt("FIELD4");
    //Note: 'Integer' can store null value, while 'int' cannnot.
      // Keep that in mind while designing your extension
  //Use found record(s) as intended
}

Update

Update existing record(s) in table

void update() {
  DBAction query = database.table("TABLENAME")
    .index("00")
    .build();
  DBContainer container = query.getContainer();
  container.setString("FIELD1", "DATA1");
  container.setInt("FIELD2", 2);
  query.insert(container, { LockedResult updateRecord ->
    updateRecord.setChar("FIELD3", '3');
    updateRecord.update();
  });
}

Delete

Delete record(s) from table

void delete() {
  DBAction query = database.table("TABLENAME")
    .index("00")
    .build();
  DBContainer container = query.getContainer();
  container.setString("FIELD1", "DATA1");
  container.setInt("FIELD2", 2);
  query.readLock(container, callback);
}
Closure<?> callback = { LockedResult lockedResult ->
  lockedResult.delete();
}