sqlconds v1.0.0

Sqlconds 简介

前后台约定通用查询条件

功能点：
    ◇sql 通用查询条件组织
    ◇sql 通用统计条件组织
    ◇将数组对象转换为insert/update语句。

Install 安装

npm install sqlconds

Usage 使用

通用查询条件组织：

 // github仓库地址: https://github.com/aixinyongheng/sqlconds
 // cjs 引入方式
 const Sqlconds = require('sqlconds');
 // es6 引入方式
 import Sqlconds from "sqlconds";
 
 const sqlconds =new Sqlconds("postgres"); // postgres/mysql/oracle ...
 // 1.筛选条件组织
 const sqlres= sqlconds.condPackage( [{"operator":"EQ","field":"bm","value":"zrzhczt_ggfwss_xx"},{"operator":"OBD","field":"px"}] );

 console.log(sqlres.cond); // and   (  "bm"   =   'zrzhczt_ggfwss_xx' )
 console.log(sqlres.order); // order by   "px" desc

 console.log(`select * from tableA where 1=1 ${sqlres.cond} ${sqlres.order}`);

 // 2. 分组条件组织
 // 2.1 分组group by 字段
 const groupbyRes=sqlconds.groupCondPackage('[{ "type":"CG", "field":"field1", "rename":"newfield1" },{ "type":"SUB", "field":"field2,1,4", "rename":"newfield2" }]');

  console.log(groupbyRes.groupbycond); //  group by "field1",substring(field2,1,4) 
  console.log(groupbyRes.fields); //  "field1" AS "newfield1",substring(field2,1,4) AS "newfield2" 

  console.log(`select count(*),${groupbyRes.fields} from tableA where 1=1 ${sqlres.cond} ${groupbyRes.groupbycond}`);


 // 2.2 聚合统计函数组织
  const statiscondRes = sqlconds.statisCondPackage([{"field":"field1","type":"ZDZ","rename":"最大值","dpoint":0}]);
  console.log(statiscondRes.statiscond); //  max("field1") AS "最大值" 


  console.log(`select count(*),${groupbyRes.fields},${statiscondRes.statiscond} from tableA where 1=1 ${sqlres.cond} ${groupbyRes.groupbycond}`);

数值对象转为insert/update语句

 const Sqlconds = require('sqlconds');
 const sqlconds = new Sqlconds("postgres");
    const res = sqlconds.objtosql("tableA", [{
      "id": "111", "name": "test", "geom": {
        "coordinates": [112.3257164817677,
          35.342031483036436
        ],
        "type": "Point"
      }
    }], { geomfields: 'geom' });
    console.log(res.sql);// insert into "tableA" ("id","name","geom") values ('111','test',public.ST_SetSRID(public.st_geomfromgeojson('{"coordinates":[112.3257164817677,35.342031483036436],"type":"Point"}'),4490)) RETURNING *;

Grammer 参数语法

condPackage 查询过滤条件组织/排序条件

groupCondPackage 分组查询条件组织

statisCondPackage 聚合统计条件组织

objtosql 数组对象转为insert/update语句

example 示例

1.支持高级查询条件 2.支持pg的空间相交函数

复杂查询示例： |require|conds|result| |---|:--------:|-------------:| |查询表中field1为11，并且field2同时为1和2的条件| [{"operator":"EQ","field":"field1","value":"11","condition":{"operator":"EQ","field":"field2","value":"1"},{"whereLinker":"or","operator":"EQ","field":"field2","value":"2"}}] |and "field1" ='11' and ( "field2"='1' or field2='2' )| |pg中查询与114,32点位相交的数据|[{"operator":"GEOMINTER","field":"geom","value":{"type":"Point","coordinates":118.530982355499,28.6730332199371}}]| and ( st_intersects( "geom" , st_setsrid(st_geomfromgeojson('{"type":"Point","coordinates":118.530982355499,28.6730332199371}'),4490)) = true )|

待完善： sql拼接防止sql注入（todo） 支持更多数据库类型（todo） 支持拓展自定义查询插值条件（todo）