/jsonql

Render JSON as multi-platform SQL

Primary LanguageJava

JsonQL

Implementations

Currently only support hibernate5.

DML

Common fields

{
  "table": "table to operate",
  "operation": "select/update/insert/delete/logic_delete"
}

context

Parameters from the context can be referenced within the following content using a specific format for text fields, as follows: _{xxxx}_.

insert
{
  "table": "someTable",
  "operation": "insert",
  "data": {
    "title": "_{titleArg}_"
  }
}
update
{
  "table": "someTable",
  "operation": "update",
  "effectMaxRows": 1,
  "data": {
    "title": "_{titleArg}_"
  },
  "condition": {
    "id": 1
  }
}
Update/Delete limitation
{
  "table": "someTable",
  "operation": "update",
  "effectMaxRows": 1,
  "condition": {
    "id": 1
  }
}

effectMaxRows

The maximum number of rows affected during an update or delete operation. The default value is 1. If the number of affected rows exceeds this value, the transaction is rolled back.;

  • -1: Indicates no limit;
  • =0: Implies that the transaction will be rolled back regardless of the outcome;
  • >0: Specifies that the transaction should be rolled back if the number of affected rows exceeds this value;
Condition on update/delete/logic_delete/select
{
  "table": "someTable",
  "operation": "select",
  "page": 1,
  "pageSize": 10,
  "count": false,
  "distinct": false,
  "condition": {
    "updated_at": {
      "<": "_{serverTime}_"
    }
  }
}

Conditions

Operator description
= equal
> greater than
< less than
>= greater than or equal to
<= less than or equal to
contains %like%
starts like%
ends %like
null is null
and and group
or or group
! negative

Equal

// where id = 1
{
  "condition": {
    "id": 1
  }
}

Others

{
  "condition": {
    "id": {
      "Operator": 1
    }
  }
}

in

// where id in ('1','2','3')
{
  "condition": {
    "id": {
      "in": ["1", "2", "3"]
    }
  }
}

null/!null

// where id is/not null
{
  "condition": {
    "id": {
      "null": null
      "!null": null
    }
  }
}

Order by

// order by id desc, age asc
{
  "orderBy": [
    "-id",
    "+age"
  ]
}

Group by

// group by gender
{
  "groupBy": [
    "gender"
  ]
}

Join

// from xxx root inner join full_name fn on root.name_id = fn.id inner join family f on fn.last_name = f.title
{
  "table": "xxx",
  "alias": "root",
  "join": [
    {
      "referencedColumn": "name_id",
      "table": "full_name",
      "joinColumn": "id",
      "alias": "fn"
    },
    {
      "handleTable": "full_name",
      "referencedColumn": "last_name",
      "table": "family",
      "joinColumn": "title",
      "alias": "f"
    }
  ]
}