JSON DB API tutorial

The Java driver uses the builder pattern to configure the connection to the FairCom server.

The code in the example uses an insecure HTTP connection to connect to a FairCom server running on the same computer as the Java program.

ServerConnection serverConnection = new ServerConnection()
  .endpoint( "http://127.0.0.1:8080/api" )
  .configure();

The code in the example creates a new session with the FairCom server. The server authenticates an account using the username and password properties. A session also sets default values and behaviors, such as returning numbers in JSON formatted as numbers or strings, and returning data formatted as JSON objects. A user-friendly description can also be added to the session to easily identify it.

Session session = new Session()
  .serverConnection( serverConnection )
  .username( "admin" )
  .password( "ADMIN" )
  .numberFormat( Session.NumberFormat.NUMBER )
  .dataFormat( Session.DataFormat.OBJECTS )
  .description( "JSON Action test" )
  .build();

The DbApi class contains methods that manage all aspects of a FairCom database. Construct a dbApi object for each session to run database commands in that session.

DbApi dbApi = new DbApi( session );

The example creates a database named "JsonActionExample".

static final String DB_NAME = "JsonActionExample";
dbApi.createDatabase( DB_NAME );

The example uses the field definition built into the Athlete class to create the table.

final String FIELD_DEFINITIONS_STRING = """
      [
        { "name": "name",           "type": "varchar",  "length": 30 },
        { "name": "ranking",        "type": "smallint", "nullable": false },
        { "name": "birthDate",      "type": "date"      },
        { "name": "playerNumber",   "type": "number",   "length": 32, "scale": 6 },
        { "name": "livedPast2000",  "type": "bit"       },
        { "name": "earnings",       "type": "money",    "length": 32, "scale": 4 },
        { "name": "favoriteSaying", "type": "varchar",  "length": 500 }
      ]
    """;
Fields fieldDefinitions = new Fields( FIELD_DEFINITIONS_STRING );
dbApi.createTable( "ctreeSQL", "athlete", fieldDefinitions );

Records can be inserted in a variety of ways. A typical way is to use a POJO, such as the Athlete class. Construct a list of Athlete objects, map them into a Jackson ArrayNode, and pass the ArrayNode into the dbApi.insertRecords method.

// Create POJOs and add them to sourceData.
List<Athlete> athleteList = new ArrayList<>();

athleteList.add( new Athlete( "Michael Jordan", 1, "19630217", 23, true, 1700000000, "There is no 'i' in team but there is in win." ) );
athleteList.add( new Athlete( "Babe Ruth", 2, "18950206", 3, false, 800000, "Every strike brings me closer to the next home run." ) );
ArrayNode sourceData = objectMapper.valueToTree( athleteList );

// Insert the records from tableData.
dbApi.insertRecords( DB_NAME, TABLE_NAME, sourceData, Athlete.getFieldNames() );

Data can be queried in a variety of ways using a cursor to paginate the results. Thousands of records can be efficiently fetched forward or backward in a single call to a cursor. A backward fetch gives you data in reverse order. Records can be skipped forward or backward. Start at the beginning or end of the query results. For example, tail the last 50 records in a table by starting at the end of the results, skip backward 50 records and fetch forward 50 records.

The code in the example retrieves records one at a time, which is simple but less efficient than retrieving many records at a time.

// Get a curser that walks records in index order.
Cursor indexCursor = dbApi.getCursorFromIndex( DB_NAME, TABLE_NAME, "name_livedpast2000" );

// Walk the cursor while there are records.
do {
  indexCursor.fetchRecords( 1 );
  ArrayNode dataBlock = indexCursor.getData();
} while ( indexCursor.isMoreRecords() );

//Close the cursor when done
indexCursor.closeCursor();