Java-sql Parsing tool Fdb-sql-parser simple to use

Because you want to solve the efficiency problem with count queries in the MyBatis paging plug-in, because order by is very inefficient, you need a way to process SQL and remove the order by statement.

Several SQL parsing tools were tried, and Fdb-sql-parser was selected.

Maven dependencies:

<dependency>    <groupId>com.foundationdb</groupId>    <artifactid>fdb-sql-parser </artifactId>    <version>1.3.0</version></dependency>

Project Address: Https://github.com/FoundationDB/sql-parser

Parsing method:

Package Com.isea533.sql;import Com.foundationdb.sql.parser.sqlparser;import Com.foundationdb.sql.parser.statementnode;public class Parser {public    static void Main (string[] args) throws Exception {        Sqlparser parser = new Sqlparser ();        Statementnode stmt = parser.parsestatement (                "Select Userid,username,password" +                "from sys_user where username = ' isea533 ');        Stmt.treeprint ();    }}

Call Treeprint () to print out the structure of the SQL:

[Email protected]name:nullupdatemode:unspecifiedstatementtype:selectresultset: [email protected] Isdistinct:falseresultcolumns: [Email protected][0]:[email protected]exposedname:useridname: UseridtableName:nullisDefaultColumn:falsetype:nullexpression: [Email protected]columnname:useridtablename: Nulltype:null[1]:[email protected]exposedname:usernamename:usernametablename:nullisdefaultcolumn: Falsetype:nullexpression: [email protected]columnname:usernametablename:nulltype:null[2]:[email  Protected]exposedname:passwordname:passwordtablename:nullisdefaultcolumn:falsetype:nullexpression: [email  Protected]columnname:passwordtablename:nulltype:nullfromlist: [email protected][0]:[email protected] TableName:sys_userupdateOrDelete:nullnullcorrelation name:nullnullwhereclause: [Email protected]operator: = MethodName:equalstype:nullleftOperand: [Email protected]columnname:usernametablename:nulltype:nullrIghtoperand: [Email protected]value:isea533type:char (7) Not NULL 

The parsed result stmt structure is quite complex, like the above structure is composed of many different types of node nesting, want to do in this structure is more troublesome, and many properties do not provide a complete setter and getter method, using reflection processing is more troublesome. It's quite a hassle to judge different types of node. As for how much trouble, go to see nodetostring this class will understand.

In addition to parsing SQL, it is possible to convert the data in this structured form to SQL. Use the following methods:

nodetostring unparser = new nodetostring (); String sql = unparser.tostring (stmt);

I've spent a lot of time working on the node structure, and if I want to modify the structure to get SQL that ultimately doesn't have an order by, because SQL can have many variations and forms, it's quite complicated to deal with. Finally had to give up this way.

Later, when I saw nodetostring, I thought that since this class would output the structure into SQL, he must have also dealt with this complex structure. After entering the nodetostring, find the order by directly and find:

Protected String Orderbylist (orderbylist node) throws Standardexception {    return "ORDER by" + nodeList (node);}

This should be the place to deal with the order by in SQL, quickly copy the source code, only the above method to change to return "", and then test found that no problem.

After seeing a lot of protected methods in the class, I realized that we should extend it in the way of inheritance, and write the method of parsing SQL into this method:

Package Com.isea533.sql;import Com.foundationdb.sql.standardexception;import Com.foundationdb.sql.parser.orderbylist;import Com.foundationdb.sql.parser.sqlparser;import Com.foundationdb.sql.parser.statementnode;import Com.foundationdb.sql.unparser.nodetostring;public Class Removeorderbyunparser extends nodetostring {    private static final Sqlparser PARSER = new Sqlparser ();    public string Removeorderby (String sql) throws Standardexception {        Statementnode stmt = parser.parsestatement (sql);        return toString (stmt);    }    @Override    protected String orderbylist (orderbylist node) throws Standardexception {        return "";}    }

You can call the Removeorderby method when you want to remove the order by.

It's quite common to waste so much time from the beginning, and it's a simple way to do it in the end, but it's a complicated situation.

It's like encountering a problem, some people are racking up all sorts of ways to finally solve, some people directly see the answer. Things are different in both cases, and the process of solving the problem is sometimes more important than the solution.

The main point of this paper is to remove the order by, it is certainly not everyone parsing SQL for this purpose, but this is a way of thinking, using this sqlparser, you can start from nodetostring, by rewriting the method inside, should be able to meet most of the needs.

Finally, I want to say that the MyBatis page plug-in has been updated, adding the Count query to the SQL optimization, the optimization method is the content of this article.

MyBatis page plug-in is an effort to make mybatis physical paging easier open source projects, if you are using MyBatis, you might as well look at this page plug-in.

MyBatis Project Address:

Https://github.com/pagehelper/Mybatis-PageHelper

Http://git.oschina.net/free/Mybatis_PageHelper