Skip to main content

JSON DB API tutorial

Use the FairCom Java driver for the JSON DB API to process data in the FairCom


Use the FairCom Java driver for the JSON DB API to process data in the FairCom database engine

The driver provides DbApi and AdminApi classes to manage the database and its data. It uses the Jackson library to process JSON, and can run any JSON action in any FairCom JSON API.

The driver's source code is part of the downloadable Faircom server package. It is located in <faircom>\drivers\java.json.db\tutorials\ folder. The source code is well-documented for code completion. You can read the JavaDoc here.

The JSON DB API makes data management fast and easy for n-tier, client-server architectures. For ultimate speed during server-side bulk processing, use FairCom's CTDB API.

  • Connect: connect to a FairCom server

  • Create sessions: create one or more sessions with the server

  • Manage sessions and their settings: create, alter (change), delete, list, and ping sessions

  • Manage databases: create, delete, and list databases

  • Manage tables: create, alter (change), delete, list, describe, and rebuild tables

  • Manage indexes: create, alter (change), delete, list, and rebuild indexes

  • Manage data: insert, update, and delete records

  • Query data: get records using IDs, keys, partial keys, tables, indexes, key ranges, and SQL

  • Use cursors: efficiently paginate, walk, and skip forward and backward through query results

  • Run SQL: run statements and queries

  • Manage ACID transactions: create, commit, rollback, and list transactions and savepoints


You are not required to use FairCom's Java driver for the JSON DB API. Instead, you can use any Java HTTPS library to POST JSON payloads to the JSON DB API and process the JSON that it returns.

  1. Ensure Java version 15 or later is installed and executable from your command line.

  2. install FairCom DB, but do not run it.

  3. For a quick start, configure an insecure HTTP connection to the FairCom server.

    1. Open the settings.json file located in theFairCom DB\config\ folder.

    2. Find the listener with "serviceName": "http8080" and set "enabled": true.

        "listeners": [
            "serviceName": "http8080",
            "port": 8080,
            "protocol": "http",
            "enabled": true
  4. As a best practice, do not enable HTTP. Instead, configure the Java driver to use a secure HTTPS connection to the FairCom server.

  5. Start the FairCom server.

  1. Run your favorite Java IDE.

  2. Create a new project by opening the tutorial project located in the <faircom>\drivers\java.json.db\tutorials\ folder.

  3. Run the program.

This section provides tips and observations about key parts of the tutorial code.

Connect to the server

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( "" )


To create a secure HTTPS connection with a FairCom server, configure the ServerConnection object to connect over port 8443, using the certificate, keystore, or trustManagers method. See Use TLS in Java for JSON DB API.

Create a session

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" )


Within a session, the FairCom server runs actions in the order they are submitted. An application can create multiple sessions to run actions in parallel.

Send database commands to the server

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

Create a database

The example creates a database named "JsonActionExample".

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


Existing databases can also be listed, described, and deleted.

Create a table

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

        { "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 );


The tutorial also shows additional ways to specify the information needed to create a table, such as defining the table structure using JSON, a string, and a Jackson ObjectNode.

Existing tables can also listed, described, altered, and deleted.

Insert records

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


The tutorial also shows how to update and delete records in a table.

Query data and paginate through the results

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


The tutorial shows additional ways to query data, such as using SQL and looking up a record by its ID.

The tutorial is a command line program that outputs the following text.

Creating the 'athlete' table.
Creating the 'ranking' index.
Creating the 'earnings' index.
Creating the 'name_livedpast2000' index.
Inserting six records.

Athletes who lived past the year 2000:
  name: "Michael Jordan"
  ranking: 1
  birthDate: "1963-02-17"
  playerNumber: 23.0
  livedPast2000: true
  earnings: 1.7E9
  favoriteSaying: "There is no 'i' in team but there is in win."

  name: "Michael Schumacher"
  ranking: 6
  birthDate: "1969-01-03"
  playerNumber: 1.0
  livedPast2000: true
  earnings: 9.9E8
  favoriteSaying: "Once something is a passion, the motivation is there."

  name: "Muhammad Ali"
  ranking: 3
  birthDate: "1942-01-17"
  playerNumber: 1.0
  livedPast2000: true
  earnings: 6.0E7
  favoriteSaying: "Float like a butterfly, sting like a bee."

  name: "Pele"
  ranking: 4
  birthDate: "1940-10-23"
  playerNumber: 10.0
  livedPast2000: true
  earnings: 1.15E8
  favoriteSaying: "Everything is practice."

  name: "Wayne Gretzky"
  ranking: 5
  birthDate: "1961-01-26"
  playerNumber: 99.0
  livedPast2000: true
  earnings: 1720000.0
  favoriteSaying: "You miss 100 percent of the shots you never take."

Adding one more athlete.
New athlete: 
  name: "Bob Glidden"
  ranking: 6
  birthDate: null
  playerNumber: null
  livedPast2000: null
  earnings: 2500000.0
  favoriteSaying: null

Updating the new athlete.
Updated athlete:
  name: "Bob Glidden"
  ranking: 6
  birthDate: "1944-04-18"
  playerNumber: 1.0
  livedPast2000: true
  earnings: 3500000.0
  favoriteSaying: "Jenkins was running his jaws, so I pulled the chute on him at about 1000."

Deleting the new athlete.

Process finished with exit code 0