foreword
I’ve been unhappy with GraphQL for a long time, and I always thought it was aThe tasteless technology is too complicated, has limited functions, unclear positioning, and has security problems. Personally, I think the main value of GraphQL is two points. One is to provide a mode that pushes business logic to the front end and allows the front end to query dynamically. The second is structured query. The output result is the same as the input structure, and what you see is what you get. The former personally believes that only the development mode of MyServerless can ideally solve the problems of security and development efficiency at the same time, while the latter is the content of this update, that is, adding a structured query function similar to GraphQL to the back-end ORM tool jSqlBox, but to achieve Not as complex as GraphQL, to minimize learning and usage costs.
By the way, let me introduce jSqlBox itself, which is a full-featured open source Java database persistence layer tool. As long as it is a function related to database operations, jSqlBox already has it, such as DDL operations, paging, sub-database and sub-table, declarative transactions, and associated mapping queries. , ActiveRecord, etc., all these functions are included in a jar package of 1M size, without relying on any third-party libraries. The main feature of jSqlBox is that Java and SQL are mixed, and SQL is written out, including this master-slave table structured query.
To use jSqlBox just add the following dependencies to the project:
<dependency>
<groupId>com.github.drinkjava2</groupId>
<artifactId>jsqlbox</artifactId>
<version>5.0.15.jre8</version> <!-- 或最新版 -->
</dependency> The content of this update
This 5.0.15.jre8 update adds a structured query function similar to GraphQL. This function is very simple when programming. On the basis of the original jSqlBox, only 300 lines of code can be implemented.
The master-slave table structured query of jSqlBox still adopts the Java/SQL mixing method of jSqlBox, but this time, the query is written in a method nested structure, and a structured query similar to GraphQL can be realized, and the tree structure of input and output is consistent. , what you see is what you get.
The main advantages of jSqlBox master-slave table structured query are:
1. Only need to write SQL for single table query, it will automatically generate SQL fragments similar to “id in (?, ?…?)” according to the master-slave associated column name, and assemble the final query result into a master-slave table tree like structure.
2. It adopts pure Java and native SQL mixed writing, with strong functions and low learning cost, and can use Java to execute complex parameters, security checks, database writing and other business logic at the same time.
3. Instead of directly outputting JSON, it outputs Map/List objects or Java entity objects. The query results can be further modified before sending JSON to the front end.
4. You can directly use Java’s IDE formatting and syntax checking functions without third-party tools. The formatting function can visualize the nesting level of the tree structure.
5. The embedded SQL parameters, paging, sub-database and sub-tables, interceptors, transactions, etc. of jSqlBox can still be used directly.
6. It does not provide security, permission functions, and no learning costs. The functions of security and permissions do not belong to the functions of ORM tools, and should be provided by the backend SpringSecurity/Shiro toolkit or an independent Serverless/JsonAPI server.
7. If combined with my MyServerless open source project, the front-end can directly write Java in html, customize the multi-level query of the master-slave table and return json, and move the business logic to the front-end.
8. The performance is good, and the “in” method is used to perform the associated query of the database table, and there is no 1+N problem.
9. The source code is concise (only 300 lines of source code are used to realize this function, see GraphQuery.java), and the scalability is good.
Example of use:
GraphQuery q1 = //
$("addresstb as addresses", "where id>", que("a1"), " and id<", que("a5"), pagin(1, 10), //
$1("usertb", key("user"), ms("userId", "id"), $("userroletb as userRoleList", ms("id", "userId"), //
$("roletb as roleList", ms("rid", "id"), // ms方法也可以写成DB.masterSlave()
$("roleprivilegetb as rolePrivilegeList", ms("id", "rid"), //
$1("privilegetb as privilege", ms("pid", "id")) //
)//
)//
), //
$1("select * from emailtb as email", ms("id", "userId")), //
$("addresstb as addressList", ms("id", "userId"), "and addressName like ?", par("addr%"))//
)//
);
GraphQuery q2 = //
$("usertb as u", "where id>", que("u2"), pagin(1, 10), entity(User.class), //映射成User实体Bean
$1("emailtb as emailMap", ms("id", "userId")), //$1表示是单个元素，而不是一个List
$("addresstb as addressList", ms("id", "userId"))//
);
Object result = DB.graphQuery(q1, q2); //result是查询结果
String json = JsonUtil.toJSONFormatted(result); //输出为JSON文本
For the above example, see GraphQueryTest.java under unit testing. The output is as follows:
{
"addresses":[
{
"addressName":"address2",
"id":"a2",
"userId":"u2",
"user":{
"id":"u2",
"userName":"user2",
"userRoleList":[
{
"id":"3i6yaxy2fusjkgisyfhypkti9",
"rid":"r1",
"userId":"u2",
"roleList":[
{
"id":"r1",
"roleName":"role1",
"rolePrivilegeList":[
{
"id":"b484ze4k44xemtkstehnprhxq",
"pid":"p1",
"rid":"r1",
"privilege":{
"id":"p1",
"privilegeName":"privilege1"
}
}
]
}
]
},
{
"id":"e41dln9m4jehmc7somvu5s2pf",
"rid":"r2",
"userId":"u2",
"roleList":[
{
"id":"r2",
"roleName":"role2",
"rolePrivilegeList":[
{
"id":"dhrh5kgsod6w76e6xtl36u8b9",
"pid":"p1",
"rid":"r2",
"privilege":{
"id":"p1",
"privilegeName":"privilege1"
}
},
{
"id":"b9h2aenn6jjacns9ng5vwhaiq",
"pid":"p3",
"rid":"r2",
"privilege":{
"id":"p3",
"privilegeName":"privilege3"
}
}
]
}
]
},
{
"id":"994a5o65pfa7wx8vq99gi1lkg",
"rid":"r3",
"userId":"u2",
"roleList":[
{
"id":"r3",
"roleName":"role3",
"rolePrivilegeList":[
{
"id":"7qf9us50mw95hijwkfvuzus4q",
"pid":"p3",
"rid":"r3",
"privilege":{
"id":"p3",
"privilegeName":"privilege3"
}
}
]
}
]
}
],
"email":{
"emailName":"email3",
"id":"e3",
"userId":"u2"
},
"addressList":[
{
"addressName":"address2",
"id":"a2",
"userId":"u2"
}
]
}
},
{
"addressName":"address4",
"id":"a4",
"userId":"u4",
"user":{
"id":"u4",
"userName":"user4",
"userRoleList":[
{
"id":"bb2d1kuwvii0gpa0pxgaph8zr",
"rid":"r1",
"userId":"u4",
"roleList":[
{
"id":"r1",
"roleName":"role1",
"rolePrivilegeList":[
{
"id":"b484ze4k44xemtkstehnprhxq",
"pid":"p1",
"rid":"r1",
"privilege":{
"id":"p1",
"privilegeName":"privilege1"
}
}
]
}
]
}
],
"addressList":[
{
"addressName":"address4",
"id":"a4",
"userId":"u4"
}
]
}
}
],
"u":[
{
"id":"u3",
"userName":"user3",
"addressList":[
{
"addressName":"address3",
"id":"a3",
"userId":"u3"
}
],
"emailMap":{
"emailName":"email5",
"id":"e5",
"userId":"u3"
}
},
{
"id":"u5",
"userName":"user5",
"addressList":[
{
"addressName":"address5",
"id":"a5",
"userId":"u5"
}
]
}
]
}
Detailed usage (below is all the documents, there are 10 in total, you can learn it after reading it, and see how much simpler it is than GraphQL!)
每个数据库表格对应一个SQL查询，写在$()或$1()方法中$()方法的第一个参数如果没有空格，则系统自动转换为 select * from xxx$()方法的第一个参数如果有空格，如"select id, name from tb"，则系统不转换$()方法的第一个参数的最后一个单词，将作为输出结果的键名。键名也可以用key("键名")来手工指定。ms()方法也可以写成DB.masterSlave(),它的参数是主表和从表的键名，参数个数必须是2的倍数， ms()支持复合主键，如ms("m1", "m2", "c1","c2" )表示主表的(m1，m2)列关联到从行的(c1,c2)列, 主表还是从表的判定与数据库定义无关，而是:如果一个$()方法写在另一个$()方法里，则它就是从表, ms()方法会被编译成 " where xxId in (?, ?,...,?) " 片段。问号是根据主表的所有关联列值填充为SQL参数$1()方法表示仅输出单个元素而不是一个列表，$1()也可以写成$("xxxxx", DB.one)从第二个参数起，即可使用jSqlBox的内嵌sql式语法，普通文本解析为SQL片段，pagin、par、que等方法都可以使用缺省情况下，输出结果为Map/List结构，但是如果出现DB.entity(XxxClass)参数后，这个SQL的输出结果被转换为一 个实体Bean对象。实体Bean也可以嵌套从表的内容，但是要注意Bean里要有相应的字段定义。使用DB.graphQuery($(), $()...)可以对一个或多个$()方法进行查询。输出对象需要输出为Json时，需要使用者自行在pom中添加JSON工具依赖，并手工进行转换，jSqlBox是个ORM工具，本身并不提供JSON工具
#jSqlBox #released #lines #code #kill #GraphQL #Programmer #Sought