Link

Database Operation Templates

Working with the Database Operations in XtendM3

Table of contents

  1. Description
    1. CRUD Templates
      1. Create
      2. Read
        1. Read one record
        2. Read multiple records
        3. Read multiple records with filter
      3. Update
      4. Delete

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