j_query结构

1、 j_query替换了原有的whereSql, 主要由两个数组属性组成：custom(where条件)、order(排序条件)。

• 例1：普通的whereSql条件与order条件改造

whereSql = "and NAME like '%张三%'  order by code desc"

改造后:

j_query = {
    "custom": [
        {
            "cn": "and",
            "code": "NAME",
            "type": "like",
            "value": "张三"
        }
    ],
    "order": [
        {
            "code": "code",
            "type": "desc"
        }
    ]
}

• 例2：普通的whereSql条件(不包含order时，j_query = custom数组即可）

whereSql = "and NAME like '%张三%' "

改造后:

j_query = [
    {
        "cn": "and",
        "code": "NAME",
        "type": "like",
        "value": "张三"
    }
]

2、custom数组（whereSql）说明

• custom为数组，数组中的元素包含以下属性, code,type,value,cn(非必填，默认and)

• type支持的类型有：

/**
 * 包含
 */
IN("in"),
/**
 * 包含
 */
IN_SELECT("inSelect"),
/**
 * 不包含
 */
NOT_IN("notIn"),
/**
 * 不包含
 */
NOT_IN_SELECT("notInSelect"),
/**
 * 不为空
 */
NOT_NULL("notNull"),
/**
 * 为空
 */
IS_NULL("isNull"),
/**
 * between
 */
BETWEEN("between"),
/**
 * 不等于
 */
NE("!="),
/**
 * 大于
 */
GT(">"),
/**
 * 大于等于
 */
GE(">="),
/**
 * 小于
 */
LT("<"),
/**
 * 小于等于
 */
LE("<="),
/**
 * 左模糊查询
 */
LIKE_LEFT("%like"),
/**
 * 右模糊查询
 */
LIKE_RIGHT("like%"),
/**
 * 模糊查询
 */
LIKE("like"),
/**
 * 等于
 */
EQ("="),
/**
 * and嵌套
 */
AND("and"),
/**
 * or嵌套
 */
OR("or");

• sql语句转换custom数组示例

例1: and name=’123’ and ( id =’111’ and code=’123’ )

转换后：

[
    {
        "code": "name",
        "type": "=",
        "value": "3"
    },
    {
        "type": "and",
        "value": [
            {
                "code": "id",
                "type": "=",
                "value": "111"
            },
            {
                "code": "code",
                "type": "=",
                "value": "123"
            }
        ]
    }
]

例2：ADMINUSERID IN (SELECT USERID FROM JE_CORE_ADMIN_USER WHERE ADMINID=’123’ or name =’1’)

转换后

{
    "code": "ADMINUSERID",
    "type": "inSelect",
    "value": {
        "table": "JE_CORE_ADMINUSER",
        "code": "USERID", 
        "conditions": [
            {
                "code": "ADMINID",
                "type": "=",
                "value": "123"
            },
            {
                "code":"name",
                "type":"=",
                "value":"1",
                "cn": 'or'
            }
        ]
    }
}

例3：AND user_name like ‘%宋爽爽%’

转换后

[
    {
        "code": "user_name",
        "type": "like",
        "value": "宋爽爽"
    },
]

例4：AND (APPFIELD_TYPE IS NULL or APPFIELD_TYPE = ‘form’) and APPFIELD_XTYPE not in (‘fieldset’)

转换后：

[
    {
        "type": "and",
        "value": [
            {
                "code": "APPFIELD_TYPE",
                "type": "isNull"
            },
            {
                "cn": "or",
                "code": "APPFIELD_TYPE",
                "type": "=",
                "value": "form"
            }
        ]
    },
    {
        "code": "APPFIELD_XTYPE",
        "type": "notIn",
        "value": ["fieldset"]
    }
]

例4：AND TEACHER_STATUS_CODE IN (‘S’,’A’)

[
    {
       "type":"in",
       "code":"TEACHER_STATUS_CODE",
       "value":"['S','A']",
       "cn":"and"
    }
]

3、order排序条件改造说明

• order 属性为order by条件数组包含以下属性，type为 asc/desc 默认为asc

• order语句转换示例：

例1：orderSql = “order by code desc”

转换结果如下

order:  [
    {
        "code": "code",
        "type": "desc"
    }
]