# sql-parse

**Repository Path**: xukui911/sql-parse

## Basic Information

- **Project Name**: sql-parse
- **Description**:        自己纯手写的一个sql解析引擎。在目前信创大环境下，针对数据库迁移项目的sql兼容问题，sql解析是一个比较完美的方案。
       此工具实现了完整的sql词法分析，并实现了增删改查操作的绝大部分语法解析（后面会通过案例具体说明）。没有实现的语法，框架支持无缝扩展，需要的用户可以自己去实现。
       不与其他框架作比较，复杂sql用户可以试用。
- **Primary Language**: Java
- **License**: Apache-2.0
- **Default Branch**: develop
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 2
- **Forks**: 0
- **Created**: 2023-11-27
- **Last Updated**: 2025-06-10

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# sql-parse

#### 介绍
       自己纯手写的一个sql解析引擎。在目前信创大环境下，针对数据库迁移项目的sql兼容问题，sql解析是一个比较完美的方案。
       此工具实现了完整的sql词法分析，并实现了增删改查操作的绝大部分语法解析（后面会通过案例具体说明）。没有实现的语法，框架支持无缝扩展，需要的用户可以自己去实现。
       不与其他框架作比较，复杂sql用户可以试用。

#### 软件架构
软件架构说明


#### 安装教程

1.  xxxx
2.  xxxx
3.  xxxx

#### 使用说明

##### 查询语句
```
String sql = "select abc + 5 as abc,def x,efg y from user a left join (select a,b,c from ddd) c on a=5 and (a>6 or a<7) group by a, b, c order by a, b, c desc";

sql = "SELECT column1, column2\r\n"
    			+ "\r\n"
    			+ "FROM table1\r\n"
    			+ "\r\n"
    			+ "WHERE NOT EXISTS (\r\n"
    			+ "\r\n"
    			+ "SELECT *\r\n"
    			+ "\r\n"
    			+ "FROM table2\r\n"
    			+ "\r\n"
    			+ "WHERE table2.column1 = table1.column1\r\n"
    			+ "\r\n"
    			+ ")";

sql = "SELECT COUNT(*) AS people_count\r\n"
    			+ "	, CASE \r\n"
    			+ "		WHEN age >= 18\r\n"
    			+ "		AND age <= 25 THEN '18-25'\r\n"
    			+ "		WHEN age >= 18\r\n"
    			+ "		AND age <= 25 THEN '18-25'\r\n"
    			+ "		WHEN age > 25\r\n"
    			+ "		AND age <= 35 THEN '25-35'\r\n"
    			+ "		WHEN age > 35\r\n"
    			+ "		AND age <= 45 THEN '36-45'\r\n"
    			+ "		ELSE '45+'\r\n"
    			+ "	END AS age_group\r\n"
    			+ "FROM people\r\n"
    			+ "GROUP BY age_group";
  
sql = "select a.eventid,\r\n"
    			+ "       a.createtime,\r\n"
    			+ "       a.commentlable,\r\n"
    			+ "       substring_index(a.commentlable, ',', b.help_topic_id + 1),\r\n"
    			+ "       substring_index(substring_index(a.commentlable, ',', b.help_topic_id + 1), ',', -1) single_label  \r\n"
    			+ "from event_xihuevent_star a\r\n"
    			+ "         join mysql.help_topic b\r\n"
    			+ "              on b.help_topic_id < (length(a.commentlable) - length(replace(a.commentlable, ',', '')) + 1)";
  
  解析代码（以上sql分别执行）：      
  SqlParse slqParse = new SqlParse();
  Token token = slqParse.parse(sql);  
  System.out.println(token.out());
```
#####   新增操作
    String sql = "insert into abc(a,b,c) values (1,2,3), (4,5,6), (7,8,9)";
    sql = "INSERT INTO table2 (column_name)\r\n select column_name FROM table1";
   
    解析代码（以上sql分别执行）：      
    SqlParse slqParse = new SqlParse();
    Token token = slqParse.parse(sql);  
    System.out.println(token.out());
    
#####   修改操作
    sql = "update student,students set students.sname=student2.sname,students.gender=students2.gender where students.sid=students2.sid";
    sql = "UPDATE users JOIN orders ON users.id = orders.user_id SET users.status = 'deleted' WHERE orders.order_date < '2020-01-01'\r\n";

    解析代码（以上sql分别执行）：      
    SqlParse slqParse = new SqlParse();
    Token token = slqParse.parse(sql);  
    System.out.println(token.out());
#####  删除操作
    String sql = "delete * aa from abc where 1=1";

    解析代码：
    SqlParse slqParse = new SqlParse();
    Token token = slqParse.parse(sql);  
    System.out.println(token.out());

#####  访问器使用
      
    可增删改引擎默认解析的token节点状态，注：用户根据自身需要，考虑访问器是使用单例还是多实例

###### 访问表名
  ```
    	String sql = "select count(*)\r\n"
    			+ "  from T_Main A\r\n"
    			+ " where 1 = 1\r\n"
    			+ "   AND A.IS_SEARCH = '1'\r\n"
    			+ "   AND EXISTS\r\n"
    			+ " (SELECT BRANCH.BRANCH_ID\r\n"
    			+ "          FROM T_BRANCHINFO BRANCH\r\n"
    			+ "         WHERE A.BRANCH_ID = BRANCH.BRANCH_ID\r\n"
    			+ "         START WITH BRANCH.BRANCH_ID = '1320900'\r\n"
    			+ "        CONNECT BY NOCYCLE PRIOR BRANCH.BRANCH_ID = BRANCH.BRANCH_ID_PARENT)\r\n"
    			+ "   AND A.HAPPEN_DATE >= TO_DATE('2022-08-01', 'YYYY-MM-DD')\r\n"
    			+ "   AND A.HAPPEN_DATE <= TO_DATE('2022-11-01', 'YYYY-MM-DD')";
    	
    	String sql2 = "with customer_total_return as\r\n"
    			+ "(select sr_customer_sk as ctr_customer_sk\r\n"
    			+ ",sr_store_sk as ctr_store_sk\r\n"
    			+ ",sum(SR_FEE) as ctr_total_return\r\n"
    			+ "from store_returns\r\n"
    			+ ",date_dim\r\n"
    			+ "where sr_returned_date_sk = d_date_sk\r\n"
    			+ "and d_year =2000\r\n"
    			+ "group by sr_customer_sk\r\n"
    			+ ",sr_store_sk)\r\n"
    			+ " select  c_customer_id\r\n"
    			+ "from customer_total_return ctr1\r\n"
    			+ ",store\r\n"
    			+ ",customer\r\n"
    			+ "where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2\r\n"
    			+ "from customer_total_return ctr2\r\n"
    			+ "where ctr1.ctr_store_sk = ctr2.ctr_store_sk)\r\n"
    			+ "and s_store_sk = ctr1.ctr_store_sk\r\n"
    			+ "and s_state = 'TN'\r\n"
    			+ "and ctr1.ctr_customer_sk = #{c_customer_sk}\r\n"
    			+ "order by c_customer_id";
     
      SqlParse slqParse = new SqlParse(new OracleTokens());
      //这里需要使用多实例，每个sql需要new一个新的访问器
      TableVisitor tableVisitor1 = new TableVisitor();  
      TableVisitor tableVisitor2 = new TableVisitor();  
      Token token = slqParse.parse(sql, new TokenVisitor[] {
    			tableVisitor1
      });
      Token token2 = slqParse.parse(sql2, new TokenVisitor[] {
    			tableVisitor2
      });
      System.out.println("sql1获取到表名"+tableVisitor1.getTableNames());
      System.out.println("sql2获取到表名"+tableVisitor2.getTableNames());
```

###### start with 语法兼容举例

```
    String sql = "select count(*)\r\n"
    			+ "  from T_Main A\r\n"
    			+ " where 1 = 1\r\n"
    			+ "   AND A.IS_SEARCH = '1'\r\n"
    			+ "   AND EXISTS\r\n"
    			+ " (SELECT BRANCH.BRANCH_ID\r\n"
    			+ "          FROM T_BRANCHINFO BRANCH\r\n"
    			+ "         WHERE A.BRANCH_ID = BRANCH.BRANCH_ID\r\n"
    			+ "         START WITH BRANCH.BRANCH_ID = '1320900'\r\n"
    			+ "        CONNECT BY NOCYCLE PRIOR BRANCH.BRANCH_ID = BRANCH.BRANCH_ID_PARENT)\r\n"
    			+ "   AND A.HAPPEN_DATE >= TO_DATE('2022-08-01', 'YYYY-MM-DD')\r\n"
    			+ "   AND A.HAPPEN_DATE <= TO_DATE('2022-11-01', 'YYYY-MM-DD')";    
   
   //添加oracle特有语法支持，继承Tokens可扩展更多语法支持
   SqlParse slqParse = new SqlParse(new OracleTokens());
   Token token = slqParse.parse(sql, new TokenVisitor[] {
    			new PrepareParamVisitor(),
    			new StartWithVisitor("a", "b")
    	});
   System.out.println(token.out());
 
```

######   类mybatis参数判断
```
  @Test
    public void testWhereCondition() {
    	String sql = "select count(*)\r\n"
    			+ "  from T_Main A\r\n"
    			+ " where\r\n"
    			+ "   A.IS_SEARCH = #{isSearch}\r\n"
    			+ "   AND EXISTS\r\n"
    			+ " (SELECT BRANCH.BRANCH_ID\r\n"
    			+ "          FROM T_BRANCHINFO BRANCH\r\n"
    			+ "         WHERE A.BRANCH_ID = BRANCH.BRANCH_ID\r\n"
    			+ "         START WITH BRANCH.BRANCH_ID = #{branchId}\r\n"
    			+ "        CONNECT BY NOCYCLE PRIOR BRANCH.BRANCH_ID = BRANCH.BRANCH_ID_PARENT)\r\n"
    			+ "   AND A.HAPPEN_DATE >= TO_DATE(#{startDate}, 'YYYY-MM-DD')\r\n"
    			+ "   AND A.HAPPEN_DATE <= TO_DATE(#{endDate}, 'YYYY-MM-DD')";
    	
    	SqlParse slqParse = new SqlParse(new OracleTokens());
    	Token token = slqParse.parse(sql, new TokenVisitor[] {
    			new StartWithVisitor(),
    			new PrepareParamVisitor(),
    			new WhereConditionVisitor()
    	});
    	OutContext context = new OutContext();
    	context.put("isSearch", "1");
    	context.put("endDate", "2022-08-01");
    	context.put("branchId", "1320900");
    	
    	System.out.println("输出SQL:"+token.out(context));
    	System.out.println("参数列表:"+context.get("#result"));
    }


输出SQL:SELECT count(*)
FROM T_Main A
WHERE A.IS_SEARCH = ?
	 AND EXISTS (
		WITH RECURSIVE tab1 AS (
			SELECT BRANCH.BRANCH_ID
			FROM T_BRANCHINFO BRANCH
			WHERE A.BRANCH_ID = BRANCH.BRANCH_ID
				 AND BRANCH.BRANCH_ID = ?
			UNION ALL
			SELECT tab2.*
			FROM tab1
			JOIN (
				SELECT BRANCH.BRANCH_ID
				FROM T_BRANCHINFO BRANCH
				WHERE A.BRANCH_ID = BRANCH.BRANCH_ID
			) tab2
			ON tab1.BRANCH_ID = tab2.BRANCH_ID_PARENT
		)
		SELECT *
		FROM tab1
	)
	 AND A.HAPPEN_DATE <= TO_DATE(?, 'YYYY-MM-DD')
参数列表:[isSearch, branchId, endDate]
```
 
###### 类mybatis简单表达式支持 #foreach #end  #if #end
  

#### 参与贡献

1.  Fork 本仓库
2.  新建 Feat_xxx 分支
3.  提交代码
4.  新建 Pull Request


#### 特技

1.  使用 Readme\_XXX.md 来支持不同的语言，例如 Readme\_en.md, Readme\_zh.md
2.  Gitee 官方博客 [blog.gitee.com](https://blog.gitee.com)
3.  你可以 [https://gitee.com/explore](https://gitee.com/explore) 这个地址来了解 Gitee 上的优秀开源项目
4.  [GVP](https://gitee.com/gvp) 全称是 Gitee 最有价值开源项目，是综合评定出的优秀开源项目
5.  Gitee 官方提供的使用手册 [https://gitee.com/help](https://gitee.com/help)
6.  Gitee 封面人物是一档用来展示 Gitee 会员风采的栏目 [https://gitee.com/gitee-stars/](https://gitee.com/gitee-stars/)