Jsql Guide

Syntax of data query, insert, update and delete on general form templates are listed in the guide.

info

Syntax that is not mentioned in this guide is not supported in supOS platform and all syntax can only apply to form templates.

Preparation

There are two form templates are used as examples in the guide: User template and absence template.

• user: Form template displaying staff information with a namespace of supos_employ. Attributes are as follows:

  • uid：String, staff ID
  • name: String, staff name
  • dep: String, staff department
  • age: Integer, staff age
  • employ_time: Date, entry time

• absence: Form template of note for leave with a namespace of supos_employ. Attributes are as follows:

• code: String, code of note for leave
• user: Object, pointing to user template and using uid as the foreign key
• reason: String, reason for leave
• absence_days: Float, days of leave

Printing SQL Generated by JSQL

Use sql.toSql() to check if the jsql you input is correct. The following is an example:

• Query

var user = jsql.template['system.user'];
var sql = jsql.select().from(user);
sql.toSql()

• Result

select * from system_user;

Selecting All Data of Template

Template must use object instead of string as string stands for constant.

Too much data may cause server crash. If no limit clauses are added, only the first 20 items will be returned. Thea maximum data volume is 5000 and exceeding it will induce error.

info

If the template namespace is not defined in the syntax, data query is happened inside the current app first and then in system. If no items can be found, an error will be reported.

// Get template
var user = jsql.template.user;
// Select staff，the first 20 items are returned by default
var sql = jsql.select().from(user);
// Execute search command
repo.query(sql);

// Designate template namespace
var absence = jsql.template["supos_work.absence"];
// Select staff，the first 20 items are returned by default
sql = jsql.select().from(absence);
// Execute search command
repo.query(sql);

Selecting Data with Designated Attributes

Attributes must be acquired by template object rather than using string because string stands for contant.

General Query

• Query

// Select the name and age of all the staff
var user = jsql.template.user;
var sql = jsql.select(user.name, user.age).from(user);

// Execute search command

repo.query(sql);

• Result

{
    "columns": [
        {
            "templateNamespace": "employ",
            "templateEnName": "user",
            "attributeEnName": "name",
            "fullName": "supos_employ.user.name",
        },
        {
            "templateNamespace": "employ",
            "templateEnName": "user",
            "attributeEnName": "age",
            "fullName": "supos_employ.user.age",
        }
    ],
    "items": [
        ["John", 40],
        ["Jack", 30]
    ]
}

• templateNamespace：Template namespace
• templateEnName：Template alias
• attributeEnName：Attribute alias
• fullName：Consisting above three fields. Template namespace.template alias.attribute alias (can be used as unique key)

info

Attributes with namespace are not supported presently. Attribute alias must be unique in the current template and parent templates. Otherwise, only attributes of the current template can be returned.

KV Format Query

Get data in the format of kv (key:value) to cater to front-end components of Tianfang.

• Query

// Get the name and age of all the staff
var user = jsql.template.user;
var sql = jsql.select(user.name, user.age).from(user);
// query can designate a particular dataFormat parameter to modify the format of returned data to cater to different scenarios,e.g. Tianfang forms
// Currently dataFormat only supports kv: the results are returned with the form of {"list":[],"pagination":{}} data is k-v struture, k is fullName, and v is the attribute value
repo.query(sql, "kv");

• Result

{
    "list": [
        {
            "supos_employ.user.name": "John",
            "supos_employ.user.age": 40
        },
        {
            "supos_employ.user.name": "Jack",
            "supos_employ.user.age": 30
        }
    ],
    "pagination": {
        "current": 1,
        "pageSize": 20,
        "total": 2
    }
}

Joint Query of Data from Multiple Templates

Join types:

• join(t, onCondition) // inner join
• leftJoin(t, onCondition)
• rightJoin(t, onCondition)
• fullJoin(t, onCondition)

// search for all notes for leave of R&D department and return staff ID, name and reason for leave
var absence = jsql.template.absence;
var user = jsql.template.user;
var sql = jsql.select(user.uid, user.name, absence.reason)
.from(absence)
.leftJoin(user, absence.user.eq(user.uid))
.where(user.dep.eq('R&D'));
// execute the query
repo.query(sql);

Conditional Query

Fuzzy Query

// Select all the staff with name starting with letter J
var sql = jsql.select().from(user).where(user.name.like("J%"));
// Select all the notes for leave with the reason for leave containing sick
var sql = jsql.select().from(absence).where(absence.reason.like("%sick%"));

Comparison Operation

• Query

// Select all the staff older than 20
var sql = jsql.select().from(user).where(user.age.gt(20));
// Select all the notes for leave with less than three days of absence 
var sql = jsql.select().from(absence).where(absence.absence_days.lt(3));

• Supported Operators

• eq means equal. For example, user.age.eq(10)
• gt means greater than. For example, user.age.gt(10)
• ge means greater than or equal. For example, user.age.ge(10)
• lt means less than. For example, user.age.lt(10)
• le means less than or equal. For example, user.age.le(10)
• ne means not equal. For example, user.age.ne(10)

Logical Operators

• and

// Select all the staff in department2 who are older than 20
var sql = jsql.select().from(user).where(user.age.gt(20).and(user.dep.eq('Department2')));

• or

// Select all the staff older than 20 or younger than 40
var sql = jsql.select().from(user).where(user.age.gt(20).or(user.age.lt(40)));

• not

// Select all the staff that are not from department two
var sql = jsql.select().from(user).where(user.dep.eq('department2').not());

Constant Expression

Sometimes constant expressions are needed to combine with sql, e.g. where true and user.age.gt(20).

General

// Input parameter to conbine with sql
// Two input parameters: min_age, name_keyword，both can be null. If the parameters are null，data will not be filtered.
var absence = jsql.template.absence;
var user = jsql.template.user;

var condition = jsql.inline(true);
// Use inline to generate constant expressions.Varous ways can be used to combine with sql.
if (min_age !== null) {
    condition = condition.and(user.age.gt(min_age));
}
if (name_keyword !== null && name_keyword !== '') {
    condition = condition.and(user.name.like('%' + name_keyword + '%'));
}

var sql = jsql.select().from(user).where(condition);

repo.query(sql);

Group

Complex expressions need to be grouped by using brackets.

//Select all the staff older than 20 and younger than 30 or older than 40 and younger than 50.

var sql = jsql.select().from(user).where(jsql.group(user.age.gt(20).and(user.age.lt(30))).or(jsql.group(user.age.gt(40).and(user.age.lt(50)))));
// Corresponding sql: select * from user where (user.age > 20 and user.age < 30) or (user.age > 40 and user.age < 50)

Range Query

in, notIn

//Select all the staff of department two or three
var sql = jsql.select().from(user).where(user.dep.in('department2','department3'));
//Select all the staff that are not from department two or three

var sql = jsql.select().from(user).where(user.dep.notIn('department2','department3'));

between, notBetween

// Select all the staff whose age is between 20 and 30 (including 20 and 30)

var sql = jsql.select().from(user).where(user.age.between(20, 30))

// Select all the staff whose age is not between 20 and 30
var sql = jsql.select().from(user).where(user.age.notBetween(20, 30))

Condition Matching

• caseWhen
• caseWhenEq

// Select the age range of all the staff，0-20: teenager，20-30: adolescence，30+ middle age

var sql = jsql.select(jsql.caseWhen(user.age.lt(20), 'teenager').when(user.age.between(20, 30), 'adolescence').el('middle age')).from(user);

// Select the age of all the staff. Return younger if the value equals 30; Return older if the value equals 40; Otherwise, return unknown.

Grouping Query Results

// Calculate the staff number of each department

var sql = jsql.select(user.dep, user.uid.count()).from(user).groupBy(user.dep);

Filter Grouping Results

// Calculate the staff number of each department and Return results with the number greater than 100

var sql = jsql.select(user.dep, 
user.uid.count()).from(user).groupBy(user.dep).having(user.uid.count().gt(100));

Sorting Results

// Select all the satff. Order by uid with ascending order(the default order is ascending order)
var sql = jsql.select().from(user).orderBy(user.uid);
// Select all the satff. Order by uid with descending order
var sql = jsql.select().from(user).orderByDesc(user.uid);
// Select all the satff. Order by uid with ascending order and by name with descending order

var sql = jsql.select().from(user).orderBy(user.uid).orderByDesc(user.name);

Limiting Data Range

• Without limit, the system adds limit (20) by default (i.e. return the first 20 items. The maximum value of limit is 50000).
• Limit(20,20) means to start from 20 and to get the data from 20-40. The first 20 stands for data address, the latter stands for the length of data.
• Pagination can also be used to limit data to meet front-end requirements.

// Select staff and return the data of first 20 staff; order the result by uid with  ascending order.
var sql = jsql.select().from(user).orderBy(user.uid).limit(20);
// Select staff, skip the first 20 staff, order the results by ascending order. Rerurn the staff from 20-40.
var sql = jsql.select().from(user).orderBy(user.uid).limit(20, 20);
//Select by paging 
var sql = jsql.select().from(user).orderBy(user.uid).pageBy(2, 20); 
// current = 2, pageSize = 20

Deduplicating Results

// Select staff with distinct name

var sql = jsql.select(user.name.distinct()).from(user);

// Select staff with distinct fields

var sql = jsql.select().distinctAll().from(user);

SQL Functiona and Operators

Arithmetic Operation

• Query

// Return the result with the age of the staff adding 10
var sql = jsql.select(user.age.add(10)).from(user);

• Supported Arithmetic Operator

• + means add. For example, user.age + 10
• - means subtract. For example, user.age - 10
• * means multiply. For example, user.age * 10
• / means divide. For example, user.age / 10
• % means remainder. For example, user.age % 10

Arithmetic Function

abs can get absolute value with no input parameters.

// Select all the notes for leave. Return the absolute value of absence days minus 7 

var sql = jsql.select(absence.absence_days.sub(7).abs()).from(absence);

String Function

substr(position, length) can subtract strings.

• position：Integer, the index of the character to be subtracted

• length: Integer, length of subtraction

// Select all the notes for leave. Subtract the first 30 characters of leave reason

var sql = jsql.select(absence.reason.substr(1, 30)).from(absence);

Aggregate Function

• count()

Calculate quantity with no input parameters.

// Get number of notes for leave 
var sql = jsql.select(absence.code.count()).from(absence);

• avg()

Get average value with no input parameters.

// Get average value of absentce days 

var sql = jsql.select(absence.absence_days.avg()).from(absence);

• max()

Get maximum value with no input parameters.

// Get the longest duration of absence days 

var sql = jsql.select(absence.absence_days.max()).from(absence);

• min()

Get minimum value with no input parameters.

// Get the longest duration of absence days 

var sql = jsql.select(absence.absence_days.max()).from(absence);

Date Function

dateFormat(format) can format the date.

format: Refer to mysql documents for details ：https://www.mysqltutorial.org/mysql-date/_format/

// Get the staff id and the employ time: date format: 2020-10-10 11:30:03
var sql =
jsql.select(user.employ_time.dateFormat('%Y-%m-%d %H:%i:%S')).from(user);

Inserting Data

// Insert two staff

// Fields and values correspond to each other in order

var sql = jsql.insert(user)

.columns(user.uid, user.name, user.age, user.dep, user.employ_time)
.values(
    ['1', "John", 20, 'department2', '2020-10-10 11:30:03'],
    ['2', "Jack", 30, 'department2', '2020-10-10 11:30:03']
);



// Return the number of the inserted items

repo.insert(sql);

Updating Data

info

Update command must have where condition to prevent the undesirable update of data.

// Plus one to the age of all staff from department two
var sql = jsql.update(user)
.set({age: user.age.add(1)})
.where(user.dep.eq('department two'));

// Return the number of updated items
repo.update(sql);

Deleting Data

info

Delete command must have where condition to prevent the undesirable delete of data.

// delete notes for leave whose ID is 001
var sql = jsql.delete(absence).where(absence.code.eq('001'));
// return the number of deleted items
repo.delete(sql);

Transaction

Transaction is usually used to manually control the consistancy of multiple data operations, to ensure complete success of data operations.

info

The current isolation level of transaction is repeatable-read, which eliminated non-repeatable read but phantom read still exists.

//start transaction
var tx = services.DataTableService.createTransaction();
//submit transaction
tx.commit();
// rollback transaction
tx.rollback();
// close transaction
tx.close();
// add 2 new employees and delete 1 note for leave and ensure complete success
// fields and values correspond to each other in order
// start transaction
var tx = services.DataTableService.createTransaction();
// update data
var sql = jsql.insert(user)
.columns(user.uid, user.name, user.age, user.dep, user.employ_time)
.values(
['1', "Jack", 20, 'department2', '2020-10-10 11:30:03'],
['2', "Jason", 30, 'department2', '2020-10-10 11:30:03']
);
repo.insert(sql);
sql = jsql.delete(absence).where(absence.code.eq('001'));
// submit transaction
tx.commit();