Json 2 Sql , json to sql

建JORM对象 5.0.9.2

JsonClient jsonToSqlClient = new JsonClient();
jsonToSqlClient.Context = new SqlSugarClient(new ConnectionConfig()
           {
               DbType = DbType.MySql,
               IsAutoCloseConnection = true,
               ConnectionString = "server=localhost;Database=db1;Uid=root;Pwd=123"
           });

1、查询功能

1.1  带有函数的查询

Json格式：

{
 "Table":"order",
      Select:[ [{SqlFunc_AggregateMin:["id"]},"id"], [{SqlFunc_GetDate:[]},"Date"] ]
}

代码：

jsonToSqlClient.Queryable(json).ToSql()
//Sql: SELECT MIN(`id`) AS `id` , NOW() AS `Date` FROM `Order`

2.2  带有条件的查询

{
  "Table":"order",
      Where:[  "name","=", "{string}:xxx"  ],
      Select:[ [{SqlFunc_AggregateMin:["id"]},"id"], [{SqlFunc_GetDate:[]},"Date"] ]
}

代码：

jsonToSqlClient.Queryable(json).ToSql()
//Sql
//SELECT MIN(`id`) AS `id` , NOW() AS `Date` FROM `Order`   WHERE `name` =  @p0

条件查询有2种语法

语法1：可以完美支持 SqlSugar表格查询语法

Where: [{ ""FieldName"":""id"",""ConditionalType"":""0"",""FieldValue"":""1""}]

语法2：逗号拼接方式  

Where:["name","=","{string}:a" , "&&" , "id" ,">", "{int}:1"] 

运算符号: ">", ">=", "<", "<=", "(", ")", "=", "||", "&&","&","|","null","is","isnot" ,"+","-","*","/","%" 

字段名：字母数字下划线　

参数值: {int}:1 表式类型为int值为1的参数

函数：{SqlFunc_AggregateMin:["id"]}  表式 min(id)

2.3 分页查询

Json格式:

{
  "Table":"order",
      PageNumber:"1",
      PageSize:"100"
}

代码:　

var sqls=jsonToSqlClient.Queryable(json).ToSqlList()
//SELECT COUNT(1) FROM `Order`
//SELECT * FROM `Order`      LIMIT 0,100

2.4 分组查询

Json

{
    "Table":  "order" ,
      GroupBy:["name"],
      Having: [{SqlFunc_AggregateAvg:["id"]},">","{int}:1" ],
      Select:[ [{SqlFunc_AggregateAvg:["id"]},"id"],"name" ]
}

代码

var sql= jsonToSqlClient.Queryable(json).ToSql()
//SELECT AVG(`id`) AS `id` , `name` AS `name` FROM `Order`  
//GROUP BY  `name`  HAVING AVG(`id`) > @p0

2.5 联表查询

Json格式:

{
    "Table":[ "order","o"],
    "LeftJoin01": ["orderdetail", "d", [  "d.orderid",">","o.id"  ]],
    "Select":["o.id" ,["d.itemid","newitemid"]]
}

//多表联查就是 LeftJoin02 LeftJoin03 慢慢加就行了InnerJoin也一样

代码:

  var sql= jsonToSqlClient.Queryable(json).ToSql();
//SELECT `o`.`id` AS `o_id` , `d`.`itemid` AS `newitemid` FROM `Order`
// o Inner JOIN `orderdetail` d ON `d`.`orderid` > `o`.`id`

2.6 排序

{Table:"order",OrderBy:[{FieldName:"id"},{FieldName:"name",OrderByType:"desc"}]}

2.7 授权查询　　

该功能目前还不完善，暂时不细解

var tableNames = jsonToSqlClient.GetTableNameList(json);//通过JSON获取JSON所有表
 var configs = GetConfigByUser(tableNames);//通过表获取行列过滤备注等信息
          var sqlList = jsonToSqlClient
            .Queryable(json)
            .UseAuthentication(configs)//查询启用行列过滤
            .ShowDesciption()//查询返回备注
            .ToResult();

2、插入　　

单条插入

{
    "Table":"order",
      Columns:{name:"{string}:1",price:"{decimal}:1"}
}
//C# jsonToSqlClient.Insertable(json).ToSql()

批量持入

{
    "Table":"order",
      Columns:[ {name:"{string}:2",price:"{decimal}:2"} , 
                 {name:"{string}:1",price:"{decimal}:1"}  ]
}
//C# jsonToSqlClient.Insertable(json).ToSql()

带自增列

{
  "Table":"order",
      Identity:"id",
      Columns:  {name:"{string}:2",price:"{decimal}:2"} 
}
//C# jsonToSqlClient.Insertable(json).ToSql()

3、更新　　

单个对象更新

{
    "Table":"order",
      Columns: { id:"{int}:1" ,name:"{string}:1" },
      WhereColumns:["id"]
}
//C# jsonToSqlClient.Updateable(json).ToSql()

多个对象更新

{
    "Table":"order",
      Columns:[ {id:2,name:"{string}:2",price:"{decimal}:2"}  , 
                 {id:1,name:"{string}:1",price:"{decimal}:1"}  ],
      WhereColumns:["id"]               
}
jsonToSqlClient.Updateable(json).ToSql()

Sql语句方式更新

{
    "Table":"order",
      Columns: {name:"{string}:2",price:"{decimal}:2"}  ,
      Where:["id","=","{int}:11"]               
}
//C# jsonToSqlClient.Updateable(json).ToSql()

4、删除　

Where中的用法和查询一样的

Json格式

{
    "Table":"order",
      Where:[ "id"," = ","{int}:1" ]
}

代码:

jsonToSqlClient.Deleteable(json).ToSqlList()
//DELETE FROM `order` WHERE `id` = @p0

5、API说明

List<SqlObjectResult> ToSqlList();//执行返回 多个Sql
SqlObjectResult ToSql(); //返回单个SQL
List<string> ToSqlString();//还未开发
T ToResult();//执行返回结果