织信低代码平台开发文档

Appearance

数据源-通过脚本获取 ​

概述 ​

对于一些复杂的数据获取场景，例如需要从关联系统通过接口的形式获取数据或者是对系统中数据做计算后获取的情况下可以使用通过脚本获取 来实现。开启后需要设置数据计算脚本的路径，脚本中需要导出以下函数

javascript
/**
 * 通过id查询记录详情,返回值为对象
 */
export function executeQueryById(ctx) {//TableFromScriptQueryContext
	const connection = informat.jdbc.tableConnection();
    const list = [];
    console.log(ctx);
    connection.select(`select * from script_and_view_source_dep_data where id = ? limit 1;`, (resultSet) => {
        list.push(handleData({
            id: resultSet.getString('id'),
            name: resultSet.getString('name'),
            sex: resultSet.getString('sex'),
            post: resultSet.getString('post'),
            score: resultSet.getDouble('score'),
            mobileNo: resultSet.getString('mobile_no'),
            createTime: resultSet.getString('create_time')
        }));
    }, ctx.id);
    if (list.length === 0) {
        informat.app.abort('数据不存在或已被删除');
        return;
    }
    return list[0];
}

/**
 * 通过条件查询记录列表,返回值为数组
 */
export function executeQueryList(ctx) {
    const {
        pageIndex,
        pageSize
    } = ctx.query;
    const connection = informat.jdbc.tableConnection();
    const list = [];
    let pageStart = (pageIndex - 1) * pageSize;
    if (Number.isNaN(pageStart) || pageStart < 0) {
        pageStart = 0;
    }
    connection.select(`select * from script_and_view_source_dep_data limit ? offset ?;`, (resultSet) => {
        list.push(handleData({
            id: resultSet.getString('id'),
            name: resultSet.getString('name'),
            sex: resultSet.getString('sex'),
            post: resultSet.getString('post'),
            score: resultSet.getDouble('score'),
            mobileNo: resultSet.getString('mobile_no'),
            createTime: resultSet.getString('create_time')
        }));
    }, pageSize, pageStart);
    return list;
}

/**
 * 通过条件查询记录数量,返回值为整数。如果返回值小于0，则页面不会使用分页模式渲染数据
 */
export function executeQueryListCount(ctx) {
     const connection = informat.jdbc.tableConnection();
    let count = 0;
    connection.select(`select count(1) as counts from script_and_view_source_dep_data;`, (resultSet) => {
        count = resultSet.getLong('counts');
    });
    return count;
}

/**
 * 创建记录
 */
export function executeInsert(ctx) {//TableFromScriptInsertContext
    console.log('executeInsert', ctx);
    return {
        'id':id
    }
}

/**
 * 编辑记录
 */
export function executeUpdate(ctx) {//TableFromScriptUpdateContext
    console.log('executeUpdate', ctx);
    return {
        'rowCount':1
    }
}

/**
 * 删除记录
 */
export function executeDelete(ctx) {//TableFromScriptDeleteContext
    console.log('executeDelete', ctx);
    return {
        'rowCount':1
    }
}
typescript
interface OrderBy {
    field: string; // 字段名
    order: 'ASC' | 'DESC'; // 排序方式，升序或降序
}

interface Filter {
    field: string; // 字段名
    condition: string; // 条件
    value: any; // 条件值
}

interface Query {
    id: string; // 记录ID
    pageIndex: number; // 起始页码，第一页从1开始
    pageSize: number; // 分页大小
    includeFields: Array<string>; // 查询字段列表
    orderByList: Array<OrderBy>; // 排序列表
    childrenFieldId?: string; // 属性结构字段的标识符，可以为空
    childrenShowParent?: boolean; // 树形结构的查询结果中是否包含父节点
    childrenRootRecordId?: string; // 树形结构的查询中根节点的ID
    filter?: Filter; // 过滤条件
    filterId?: string; // 组合筛选过滤器ID
    pathFilterId?: string; // 地址栏筛选过滤器ID
    pathFilterQuery?: object; // 地址栏筛选过滤器条件
}

interface TableFromScriptQueryContext {
    appId: string; // 应用ID
    tableId: string; // 数据表标识符
    id: string; // 记录ID，executeQueryById方法会使用
    query: TableFromScriptQuery;
}

interface TableFromScriptInsertContext {
    appId: string; // 应用ID
    tableId: string; // 数据表标识符
    rowData: object; // 记录
}

interface TableFromScriptUpdateContext {
    appId: string; // 应用ID
    tableId: string; // 数据表标识符
    rowData: object; // 记录
    updateFields: Array<string>;//更新的字段列表
}

interface TableFromScriptDeleteContext {
    appId: string; // 应用ID
    tableId: string; // 数据表标识符
    id: string; // 删除的记录ID
}

关于OrderBy Filter的定义请参见informat.table

脚本获取数据完整示例 ​

javascript

const dburl = 'jdbc:postgresql://127.0.0.1:5432/db_xxx';
const dbuser = 'root';
const dbpassword = 'xxxxxxxx';
const driverClassName = 'org.postgresql.Driver';

export const typeMapping = {
    UUID: 'String',
    SingleText: 'String',
    Date: 'Timestamp',
    Integer: 'Int',
    Double: 'Double',
    RelationRecord: 'String'
};

export function executeQueryById(ctx) {
    console.log('executeQueryById ctx',ctx)
    let tableName=ctx.tableId;
    const connection = informat.jdbc.createConnection({
        dburl,
        dbuser,
        dbpassword,
        driverClassName,
    })
    const tableInfo = informat.table.getTableInfo(tableName);
    let selectSql = `select * from ${tableName} where id=?`
    let bean = {};
    connection.select(selectSql,(row)=>{
        bean=rowHandler(tableInfo.tableFieldList, row);
    },ctx.id)
    return bean;
}

/**
 * 通过条件查询记录列表,返回值为数组
 */
export function executeQueryList(ctx){
    console.log('executeQueryList ctx',ctx)
    let tableName=ctx.tableId;
    const connection = informat.jdbc.createConnection({
        dburl,
        dbuser,
        dbpassword,
        driverClassName,
    })
    if(ctx.query.orderByList==null||ctx.query.orderByList.length==0){//默认排序
        ctx.query.orderByList=[{'field':'create_time','type':'desc'}]
    }
    const tableInfo = informat.table.getTableInfo(tableName);
    console.log('tableInfo---->', tableInfo);
    let args=[];
    let sql = `select * from ${tableName}`
    sql=sql+getSelectSql(tableInfo, ctx, args);
    console.log('select sql---->', sql, args);
    const list = [];
    connection.select(sql,(row)=>{
        list.push(rowHandler(tableInfo.tableFieldList, row));
    }, args)
    return list;
}
/**
 * 通过条件查询记录数量,返回值为整数。如果返回值小于0，则页面不会使用分页模式渲染数据
 */
export function executeQueryListCount(ctx){
    console.log('executeQueryListCount ctx', ctx)
    let tableName=ctx.tableId;
    const connection = informat.jdbc.createConnection({
        dburl,
        dbuser,
        dbpassword,
        driverClassName,
    })
    let count = 0;
    connection.select(`select count(*) as count from ${tableName}`,(row)=>{
        count = row.getInt('count');
    })
    return count;
}


const excludeFieldTypeList = ['Seq']

function rowHandler(tableField, row) {
    const rs = {};
    tableField.forEach(field => {
        const {type,key} = field;
        if(excludeFieldTypeList.includes(type)){
            return;
        }
        rs[key] = row[`get${typeMapping[type]}`](`${key}`);
        if(type === 'RelationRecord') {
            rs[`${key}_name`] = row.getString(`${key}_name`);
        }
    })
    return rs;
}

function getField(tableInfo, fieldId){
    let field=tableInfo.tableFieldList.find(item=>item.key==fieldId)
    if(field==null){
        informat.app.abort('字段不存在'+fieldId)
    }
    return field;
}

function getValue(field, value){
    if(value==null){
        return null;
    }
    let type=field.type;
    if(type=='Integer'||type=='Rate'){
        return parseInt(value);
    }else if(type=='Double'){
        return parseFloat(value);
    }else if(type=='Date'||type=='CreateTime'||type=='LastModifyTime'){
        return informat.date.parseDate(value);
    }else{
        return value;
    }
}

function getWhereSql(tableInfo, whereSql, filter, args){
    if(filter==null){
        return;
    }
    if(filter.conditionList!=null&&filter.conditionList.length>0){
        if(filter.opt==null||filter.opt=='and'){
            whereSql+=`and ( 1=1 `;
        }else{
            whereSql+=`or ( 1=2 `;
        }
        filter.conditionList.forEach(c=>{
            if(c.fieldId!=null){
                let field=getField(tableInfo, c.fieldId);
                let column=field.key;
                let value=getValue(field, c.value);
                if(c.opt=='eq'&&c.value!=null){
                    whereSql+=`and ${column} = ? `;
                    args.push(value);
                }
                if(c.opt=='ne'&&c.value!=null){
                    whereSql+=`and ${column} != ? `;
                    args.push(value);
                }
                if(c.opt=='isnull'){
                    whereSql+=`and ${column} is null `;
                }
                if(c.opt=='isnotnull'){
                    whereSql+=`and ${column} is not null `;
                }
                if(c.opt=='gt'&&c.value!=null){
                    whereSql+=`and ${column} > ? `;
                    args.push(value);
                }
                if(c.opt=='ge'&&c.value!=null){
                    whereSql+=`and ${column} >= ? `;
                    args.push(value);
                }
                if(c.opt=='lt'&&c.value!=null){
                    whereSql+=`and ${column} < ? `;
                    args.push(value);
                }
                if(c.opt=='le'&&c.value!=null){
                    whereSql+=`and ${column} <= ? `;
                    args.push(value);
                }
                if(c.opt=='contains'&&c.value!=null){
                    whereSql+=`and ${column} like concat('%',?,'%') `;
                    args.push(value);
                }
                if(c.opt=='notcontains'&&c.value!=null){
                    whereSql+=`and ${column} not like concat('%',?,'%') `;
                    args.push(value);
                }
                if(c.opt=='startswith'&&c.value!=null){
                    whereSql+=`and ${column} not like concat(?,'%') `;
                    args.push(value);
                }
                if(c.opt=='endswith'&&c.value!=null){
                    whereSql+=`and ${column} not like concat('%',?) `;
                    args.push(value);
                }
                if(c.opt=='in'&&c.value!=null&&c.value.length>0){
                    whereSql+=`and ${column} in ( `;
                    c.value.forEach(item=>{
                        whereSql+=`?,`;
                        args.push(getValue(field,item));
                    })
                    whereSql=whereSql.substr(0, whereSql.length - 1);
                }
                if(c.opt=='notin'&&c.value!=null&&c.value.length>0){
                    whereSql+=`and ${column} not in ( `;
                    c.value.forEach(item=>{
                        whereSql+=`?,`;
                        args.push(getValue(field,item));
                    })
                    whereSql=whereSql.substr(0, whereSql.length - 1);
                }
            }
        });
        whereSql+=`)`;
    }
    if(filter.children!=null&&filter.children.length>0){
        filter.children.forEach(item=>{
            whereSql=getWhereSql(tableInfo, whereSql, item, args);
        })
    }
    return whereSql;
}

function getSelectSql(tableInfo, ctx, args) {
    let whereSql='where 1=1 ';
    if(ctx.query.filter!=null){
       whereSql=getWhereSql(tableInfo, whereSql, ctx.query.filter, args)
    }
    const pageSize = ctx.query.pageSize || 50;
    const offset = (ctx.query.pageIndex - 1) * pageSize;
    let orderBy = (ctx.query.orderByList || []).map(item => `${item.field} ${item.type}`).join(',');
    if (orderBy) {
        orderBy = 'order by ' + orderBy
    }
    const sql = ` ${whereSql} ${orderBy} limit ${pageSize} offset ${offset}`;
    return sql
}