# jsonsql

**Repository Path**: oscliaoxin/jsonsql

## Basic Information

- **Project Name**: jsonsql
- **Description**: 构建SQL语句，像Json一样简单 
- **Primary Language**: Unknown
- **License**: MulanPSL-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 11
- **Forks**: 2
- **Created**: 2021-08-09
- **Last Updated**: 2024-09-11

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

<h1 align="center" style="text-align:center;">
  JSONSQL
</h1>

<p align="center">零代码、热更新Table、自动化 ORM 库<br />🚀 后端接口零代码，前端(客户端) 定制返回 JSON 的数据和结构</p>

<p align="center" >
    </p>
    <h1 align="center" style="text-align:center;">
    <font size = 8>
    {sql@}
    </font>
    </h1>
    </p>
</p>


# 极简的Json 转换SQL 查询风格
最简的demo, 需要在config.json -> Table中添加配置映射关系。例如：
```
{
    "Table":{
        "Test":{
            "TableName":"mf_test",
        }

    },
    "Debug": true
}
```
postman 请求连接 http://localhost:8080/get ，请求方式 Post Body 请求json内容，如下： 
```
{
    "Test":{
        "@w":{
            "id": 1
        }
    }
}
```
生成的Mysql SQL语句:
```
SELECT * FROM mf_test AS test WHERE  id = ?
```
返回的结果:
```
{"Test":[{"date":"2021-08-02 13:45:16","detail":"test","id":1}],"code":200}
```
# JsonSql 关键核心转义对象

|  关键字符号   | 代表的sql查询转义  |
|  ----  | ----  |
| xxx@  | Json一级对象转义为查询的映射对象, 带@是引用对象， 带|为关联表引用 |
| @column 简写:@c  | Sql 语句SELECT 要返回的字段,字段可以挟带sql语句。 |
| @join 简写:@j | JOIN 可关联引用表@ |
| @where 简写:@w  | WHERE 语句后的条件生成对象 |
| @union 简写:@u  | UNION 中Type表示类型,table表示关联的表，使用引用表@|
| @set 简写:@s  | INSERT和UPDATA中使用
| @group 简写:@g  | GROUP BY  |
| @order 简写:@o  | ORDER BY |
| @limit 简写:@l  | LIMIT |
| @offset 简写:@os  | OFFSET |

# JsonSql 相关符号使用含义

|  @where value 符号   | 转义  |
|  ----  | ----  |
| @  | 引用符号，可以是引用或者是构建[table]查询生成对象,同时也会触发对value值的含义转换，如: User/id -> user.id |
| /  | 转义，@column 中转义为 AS ,@where 中转义为 . 注意：@where中字段末尾添加@|
| &  | 转义 AND |
| \| | 转义OR |
| %  | 应用与字段后缀， 转义 LIKE '%%' |
| >=,>,<,<=  | 与sql语句 中的含义一样 |
| []  | 转义 IN  ,允许写法  [1,2] ,["user1",user2] , "sql@" |


# idea 还可以像sql 语句的逻辑，构造关联查询语句。
子查询
```
{
    "sql@":{
        "Test":{
            "@column": "nick_name",
            "@where":{
                "id": 1
            }
        }
    },
    "User|Test":{
        "@column":"id,detail AS Detail,sql@/NickName",
        "@where":{
            "User/id@": "Test/id",
            "&User/sex@": "boy",
            "|kks": 100, 
            "&detail%": "搜索内容",
            "&id": 12345
        },
        "@limit": 10,
        "@offset" : 2,
        "@order":"date-"
    }
}
```
生成sql语句:
```
SELECT id,detail AS Detail,(SELECT nick_name FROM mf_test AS test WHERE  id = ?) AS NickName FROM apijson_user AS user, mf_test AS test WHERE  user.id = test.id AND  user.sex = ? OR  kks = ? AND  detail LIKE '%?%' AND  id = ? ORDER BY date DESC LIMIT 10 OFFSET  2
```
多种复杂组合， bsql@ 复合引用 asql@, 命名规则已 ASCII 为优先级排序生成 sql语句。
```
{
    "asql@":{
        "Test":{
            "@column": "nick_name",
            "@where":{
                "id": 1
            }
        }
    },
    "bsql@":{
        "Test":{
            "@column": "nick_name_2",
            "@where":{
                "id@": "asql@",
                "Test/name@":"klkk"
            }
        }
    },
    "User|Test":{
        "@column":"id,detail AS Detail,bsql@/NickName",
        "@where":{
            "User/id@": "Test/id",
            "User/sex@": "boy",
            "|kks": 100, 
            "&detail%": "搜索内容",
            "&id": 12345
        },
        "@limit": 10,
        "@offset" : 2,
        "@order":"date-"
    }
}
```
生成的sql语句：
```
SELECT id,detail AS Detail,(SELECT nick_name_2 FROM mf_test AS test WHERE  id IN (SELECT nick_name FROM mf_test AS test WHERE  id = ?) AND  test.name = ?) AS NickName FROM apijson_user AS user, mf_test AS test WHERE  user.id = test.id AND  user.sex = ? OR  kks = ? AND  detail LIKE '%?%' AND  id = ? ORDER BY date DESC LIMIT 10 OFFSET  2
```

还可以做为关联表
```
{
    "asql@":{
        "Test":{
            "@column": "nick_name",
            "@where":{
                "id": 1
            }
        }
    },
    "bsql@":{
        "Test":{
            "@column": "nick_name_2",
            "@where":{
                "id@": "asql@",
                "Test/name@":"klkk"
            }
        }
    },
    "User|asql@":{
        "@column":"id,detail AS Detail,NickName",
        "@where":{
            "User/id@": "asql/id",
            "User/sex@": "boy",
            "|kks": 100, 
            "&detail%": "搜索内容",
            "&id": 12345
        },
        "@limit": 10,
        "@offset" : 2,
        "@order":"date+"
    }
}
```
生成的sql语句：
```
SELECT id,detail AS Detail,NickName FROM apijson_user AS user, (SELECT nick_name FROM mf_test AS test WHERE  id = ?) AS asql WHERE  user.sex = ? AND  user.id = asql.id OR  kks = ? AND  detail LIKE '%?%' AND  id = ? ORDER BY date ASC LIMIT 10 OFFSET  2
```
[] In条件查询的特殊操作:
```
{
    "sql@":{
        "User":{
            "@c": "id",
            "@w":{
                "userid":1
            }
        }
    },
    "Test":{
        "@column": "*",
        "@where":{
            "detail[]": "sql@",
            "id[]":[1,2,3,4]
        }
    }
}
```
生成的sql语句：
```
SELECT * FROM mf_test AS test WHERE  detail IN (SELECT id FROM apijson_user AS user WHERE  userid = ?) AND  id IN (1,2,3,4)
```
UNION的操作：
> 关键字：@union,缩写为@u
> 字段含义：type表示union方式，为空表示默认DISTINCT，table表示后面要联接的表
> 需要将limit,offset,order等等条件写在sql@中
```
{
    "Test":{
        "@w":{
            "id": 1
        },
        "@u":{
            "type":"ALL",
            "table":"sql@"
        }
    },
    "sql@":{
        "User":{
            "@w":{
                "id": 2
            },
            "@limit": 10,
            "@offset" : 2,
            "@order":"date+"
        }
    }
}
```
生成的sql语句：
```
SELECT * FROM mf_test AS test WHERE  id = ? UNION ALL SELECT * FROM apijson_user AS user WHERE  id = ? ORDER BY date ASC LIMIT 10 OFFSET 2
```
JOIN的操作：
```
{
    "sql@":{
        "Test":{
            "@column": "nick_name",
            "@where":{
                "id": 1
            }
        }
    },
    "Test":{
        "@w":{
            "id": 1
        },
        "@j":{
          "1":{
            "type":"left",
            "table":"sql@",
            "condition":"User/id , Test/id"
          },
          "2":{
              "type":"right",
              "table":"User",
              "condition":"User/id , Test/id"
          },
          "3":{
              "type":"",
              "table":"test/tttt",
              "condition":"User/id , Test/id"
          }
        }
    }
}
```
生成的sql语句：
```
SELECT * FROM mf_test AS test JOIN LEFT (SELECT nick_name FROM mf_test AS test WHERE  id = ?) AS table[1] ON User.id = Test.id JOIN RIGHT apijson_user AS User ON 
User.id = Test.id JOIN test AS tttt ON User.id = Test.id WHERE  id = ?
```
# Inster 插入 请求连接 http://localhost:8080/set
```
{
    "Test":{
        "@column": "id,name,pwd",
        "@values": [121,"ssdfaf","paw1233456"]
    }
}
```
生成的sql语句：
```
INSERT INTO  mf_test (id,name,pwd) VALUES(?,?,?)
```
同时支持多值插入:
> 只有一个数据也支持，例如[[1, "a", "p"]]
```
{
    "Test":{
        "@column": "id,name,detail",
        "@values": [[1231,"ssdfaf","paw1233456"],[1323,"3ee","paw123"]]
    }
}
```
生成的sql语句：
```
INSERT INTO  mf_test (id,name,detail) VALUES (?,?,?),(?,?,?)
```
OR
```
{
    "sql@":{
        "User":{
            "@c": "id",
            "@w":{
                "userid":1
            }
        }
    },
    "Test":{
        "@column": "userId,detail",
        "User":{
            "@column": "id,desc",
            "@where":{
             "detail[]": "sql@",
            "id[]":[1,2,3,4]
            }
        }
    }
}
```
生成的sql语句：
```
INSERT INTO  mf_test (userId,detail) SELECT id,desc FROM apijson_user AS user WHERE  detail IN (SELECT id FROM apijson_user AS user WHERE  userid = ?) AND  id IN (1,2,3,4)
```
支持set操作：
```
{
    "Test":{
        "@set": {
            "id":1,
            "name": "test"
        }
    }
}
```
生成的sql语句：
```
INSERT INTO  mf_test SET id=1, name=test
```
# Update 更新 请求连接 http://localhost:8080/up
```
{
    "Test":{
        "@set": {
            "userId": 411,
            "detail": "kkkkkk"            
        },
        "@where": {
            "id": 1
        }
    }
}
```
生成的sql语句：
```
UPDATE  mf_test SET userId = ?,detail = ? WHERE  id = ?
```
支持内联表:
```
{
  "sql@":{
    "User":{
      "@c":"id",
      "@w":{
        "id": 2
      }
    }
  },
  "Test":{
    "@set": {
      "id": "sql@",
      "detail": "admin"            
    },
    "@where": {
      "id": 1
    }
  }
}
```
生成的sql语句：
```
UPDATE mf_test AS Test SET id = (SELECT id FROM apijson_user AS user WHERE  id = ?) , detail = ? WHERE  id = ?
```
OR
```
{
  "sql@":{
    "User":{
      "@c":"id",
      "@w":{
        "id": 2
      }
    }
  },
  "Test|sql@":{
    "@set": {
      "id": "1",
      "detail": "admin"            
    },
    "@where": {
      "id": 1
    }
  }
}
```
生成sql
```
UPDATE mf_test AS Test, (SELECT id FROM apijson_user AS user WHERE  id = ?) AS sql SET id = ?, detail = ? WHERE  id = ?
```
使用JOIN关联更新：
```
{
    "Test":{
        "@join":{
            "1":{
            "type":"",
            "table":"User",
            "condition":"User/id , Test/id"
          }
        },
        "@set": {
            "Test/detail@": "User/desc",
            "Test/id": 20
        }
    }
}
```
生成的sql语句：
```
UPDATE mf_test AS Test JOIN apijson_user AS User ON User.id = Test.id SET Test.detail = User.desc, Test/id = ?
```
# Delete 删除 请求连接 http://localhost:8080/del
```
{
    "Test":{
        "@where": {
            "id": 1
        }
    }
}
```
生成的sql语句：
```
DELETE FROM  mf_test WHERE  id = ?
```
## 加入社区

扫码加入即刻交流与反馈：

<img alt="Join the chat at dingtalk" src="./image/jsonsql@.jpg" width="200">