JSQLPARSER解析SQL知识入门

基础篇

JSQLParser是一个SQL语句解析器。它将SQL转换为Java类的可遍历层次结构。 支持Oracle，SQLServer，MySQL，PostgreSQL等常用数据库。但各种数据库系统的SQL语法都在动态变化，可以解析某些（不是全部）。

JSQLParser就是一个把SQL转换为JAVA对象操作的工具包，但是发现此类文章较少，文档也不太详细，所以写个博客供参考。 github地址

入门

添加POM

        
            com.github.jSQLparser
            jSQLparser
            3.2
        

编写简单的测试类

public void testSelect() throws JSQLParserException {
        // 使用工具类把SQL转换为Select对象
        Select select = (Select) CCJSQLParserUtil.parse("SELECT username,age,sex FROM user");
        SelectBody selectBody = select.getSelectBody();
        System.err.println(selectBody);
}

源码结构

expression：SQL构建相关类，比如EqualsTo、InExpression等表达式用于构建SQL。

parser： SQL解析相关类，比如CCJSQLParserUtil。

schema：主要存放数据库schema相关的类 ，比如表、列等。

statement：封装了数据库操作对象，create、insert、delete、select等

util： 各种工具类、不同DB版本、SQL标准等处理类，如SelectUtils、DatabaseType等。

JSQLParser支持WHERE及多表操作SQL构建

单表WHERE

    /**
     * 单表SQL查询
     *
     * @throws JSQLParserException
     */
    public void testSelectONetable() throws JSQLParserException {
        // 单表全量
        Table table = new Table("test");
        Select select = SelectUtils.buildSelectFromTable(table);
        System.err.println(select); // SELECT * FROM test
        // 指定列查询
        Select buildSelectFromTableAndExpressions = SelectUtils.buildSelectFromTableAndExpressions(new Table("test"), new Column("col1"), new Column("col2"));
        System.err.println(buildSelectFromTableAndExpressions); // SELECT col1, col2 FROM test
        // WHERE =
        EqualsTo equalsTo = new EqualsTo(); // 等于表达式
        equalsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
        equalsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody(); // 转换为更细化的Select对象
        plainSelect.setWhere(equalsTo);
        System.err.println(plainSelect);//  SELECT * FROM test WHERE test.user_id = '123456'
        // WHERE  != 
        NotEqualsTo notEqualsTo = new NotEqualsTo();
        notEqualsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
        notEqualsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
        PlainSelect plainSelectNot = (PlainSelect) select.getSelectBody();
        plainSelectNot.setWhere(notEqualsTo);
        System.err.println(plainSelectNot);//  SELECT * FROM test WHERE test.user_id  '123456'
        // 其他运算符, 参考上面代码添加表达式即可
        GreaterThan gt = new GreaterThan(); // ">"
        GreaterThanEquals geq = new GreaterThanEquals(); // ">="
        MinorThan mt = new MinorThan(); // "'22112') group by t.f1 order by t.f1 desc,tf2 asc limit 1,20";
    static String SQL2 = "insert into table(f1,f2) values (1,2)";
    static String SQL2_1 = "insert into table(f1,f2) (select f1,f2 from table1)";
    static String SQL3 = "update table set f1=2,f2=3 where f1=1212";
    static String SQL3_1 = "insert into table(f1,f2) (select f1,f2 from table1)";
    static String SQL4_1 = "delete from table where 1=1";
    static String SQL_5 = "create table table_name2 as select * from table_name1 t1 where t1.id = '333'";
    static String SQL5_1 = "CREATE TABLE `gen_table` (\n" +
            "  `table_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',\n" +
            "  `table_name` varchar(200) DEFAULT '' COMMENT '表名称',\n" +
            "  `table_comment` varchar(500) DEFAULT '' COMMENT '表描述',\n" +
            "  `sub_table_name` varchar(64) DEFAULT NULL COMMENT '关联子表的表名',\n" +
            "  `sub_table_fk_name` varchar(64) DEFAULT NULL COMMENT '子表关联的外键名',\n" +
            "  `class_name` varchar(100) DEFAULT '' COMMENT '实体类名称',\n" +
            "  `tpl_category` varchar(200) DEFAULT 'crud' COMMENT '使用的模板（crud单表操作 tree树表操作 sub主子表操作）',\n" +
            "  `package_name` varchar(100) DEFAULT NULL COMMENT '生成包路径',\n" +
            "  `module_name` varchar(30) DEFAULT NULL COMMENT '生成模块名',\n" +
            "  `business_name` varchar(30) DEFAULT NULL COMMENT '生成业务名',\n" +
            "  `function_name` varchar(50) DEFAULT NULL COMMENT '生成功能名',\n" +
            "  `function_author` varchar(50) DEFAULT NULL COMMENT '生成功能作者',\n" +
            "  `gen_type` char(1) DEFAULT '0' COMMENT '生成代码方式（0zip压缩包 1自定义路径）',\n" +
            "  `gen_path` varchar(200) DEFAULT '/' COMMENT '生成路径（不填默认项目路径）',\n" +
            "  `options` varchar(1000) DEFAULT NULL COMMENT '其它生成选项',\n" +
            "  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',\n" +
            "  `create_time` datetime DEFAULT NULL COMMENT '创建时间',\n" +
            "  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',\n" +
            "  `update_time` datetime DEFAULT NULL COMMENT '更新时间',\n" +
            "  `remark` varchar(500) DEFAULT NULL COMMENT '备注',\n" +
            "  PRIMARY KEY (`table_id`)\n" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代码生成业务表';";
    static String SQL1_1 = "select `t2a_cust_c`.`CUST_ID` AS `CUST_ID`,`t2a_cust_c`.`CUST_NAME` AS `CUST_NAME`,`t2a_cust_c`.`CUST_EN_NAME` AS `CUST_EN_NAME`,`t2a_cust_c`.`CUST_STS` AS `cust_sts`,`t2a_cust_c`.`CUST_TYPE` AS `CUST_TYPE`,`t2a_cust_c`.`CERT_TYPE` AS `cert_type`,`t2a_cust_c`.`CERT_NO` AS `CERT_NO`,`t2a_cust_c`.`ORG_ID` AS `ORG_ID`,`t2a_cust_c`.`BIZ_SCOPE` AS `BIZ_SCOPE`,'' AS `NATION_CD`,NULL AS `INCOME_AMT`,`t2a_cust_c`.`CREATE_DT` AS `CREATE_DT`,'' AS `IS_STAFF`,`t2a_cust_c`.`IS_FREE_TRADE` AS `IS_FREE_TRADE`,`t2a_cust_c`.`CUST_NAT` AS `CUST_NAT`,`t2a_cust_c`.`PBC_INDUS` AS `pbc_indus`,'' AS `pbc_ocp`,`t2a_cust_c`.`CERT_INVALID_DT` AS `CERT_INVALID_DT` from `t2a_cust_c` union all select `t2a_cust_i`.`CUST_ID` AS `CUST_ID`,`t2a_cust_i`.`CUST_NAME` AS `CUST_NAME`,`t2a_cust_i`.`CUST_EN_NAME` AS `CUST_EN_NAME`,`t2a_cust_i`.`CUST_STS` AS `cust_sts`,`t2a_cust_i`.`CUST_TYPE` AS `CUST_TYPE`,`t2a_cust_i`.`CERT_TYPE` AS `cert_type`,`t2a_cust_i`.`CERT_NO` AS `CERT_NO`,`t2a_cust_i`.`ORG_ID` AS `ORG_ID`,'' AS `BIZ_SCOPE`,`t2a_cust_i`.`NATION_CD` AS `NATION_CD`,`t2a_cust_i`.`INCOME_AMT` AS `INCOME_AMT`,`t2a_cust_i`.`CREATE_DT` AS `CREATE_DT`,`t2a_cust_i`.`IS_STAFF` AS `IS_STAFF`,`t2a_cust_i`.`IS_FREE_TRADE` AS `IS_FREE_TRADE`,`t2a_cust_i`.`CUST_NAT` AS `CUST_NAT`,'' AS `pbc_indus`,`t2a_cust_i`.`PBC_OCP` AS `pbc_ocp`,`t2a_cust_i`.`CERT_INVALID_DT` AS `CERT_INVALID_DT` from `t2a_cust_i`";
    public static void main(String[] args) {
        testSimpleSelectSQL(SQL1_1);
//        testSimpleInsertSQL(SQL2);
//        testSimpleInsertSQL(SQL2_1);
//        testSimpleUpdateSQL(SQL3);
//        testSimpleDeleteSQL(SQL4_1);
//        testSimpleCreateSQL(SQL5_1);
    }
    private static void testSimpleCreateSQL(String SQL_5) {
        try {
            Statement statement = CCJSQLParserUtil.parse(SQL_5);
            if (statement instanceof CreateTable) {
                Table table = ((CreateTable) statement).getTable();
                System.out.println(table);
                Select select = ((CreateTable) statement).getSelect();
                if (select != null){
                    String s = select.toString();
                    testSimpleSelectSQL(s);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //解析SQL
    public static void testSimpleSelectSQL(String SQL1) {
        System.out.println("=================测试查询==================");
        try {
            Select select = (Select) CCJSQLParserUtil.parse(SQL1);
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            List tableList = tablesNamesFinder.getTableList(select);
            // 获取到查询SQL中的所有表名,下面的逻辑是对SQL的细致拆分
            System.out.println("表名:" + tableList);
            if (select.getSelectBody() instanceof PlainSelect) { // 普通查询
                // 复杂SQL会多次调用此处方法，所以抽出作为公共类使用
               getSelectMsg(select);
            }else if (select.getSelectBody() instanceof WithItem){ // WITH语句
            }else if (select.getSelectBody() instanceof SetOperationList){ // INTERSECT、EXCEPT、MINUS、UNION语句
               SetOperationList setOperationList =  (SetOperationList)select.getSelectBody();
                List selects = setOperationList.getSelects();
                for (int i = 0; i