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