当前位置：首页 > news >正文

JSqlParser 解析 sql

news 来源：原创 2024/9/20 17:55:18

前言

JSqlParser是一个 SQL 语句解析器。它将 SQL 语句转换为可遍历的 Java 类层次结构，可以方便的用代码对 SQL 语句进行解析，修改等操作。

官网 api 文档和 github 地址如下：

jsqlparser API 文档

jsqlparser github 地址

一、Maven依赖

        <!-- 4.9版本是适用于jdk8的最后一个版本--><dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>4.9</version></dependency>

二、获取sql中的表名

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.util.TablesNamesFinder;
import java.util.Set;public class MainServer {public static void main(String[] args) throws JSQLParserException {String sql = "SELECT e.employee_id, e.first_name, e.last_name, d.department_name, e.salary " +"FROM employees e " +"LEFT JOIN departments d ON e.department_id = d.department_id " +"WHERE e.salary > ( " +"    SELECT AVG(salary) " +"    FROM staff " +"    WHERE department_id = e.department_id " +");";Set<String> tableNames = TablesNamesFinder.findTables(sql);//输出： staff, departments, employeesSystem.out.println(tableNames);}
}

三、获取sql中的具体信息

import javassist.CannotCompileException;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;public class MainServer {public static void main(String[] args) throws CannotCompileException, JSQLParserException {String sql = "SELECT DISTINCT e.employee_id, e.first_name, e.last_name, d.department_name, e.salary " +"FROM employees e " +"LEFT JOIN departments d ON e.department_id = d.department_id " +"WHERE e.salary > ( " +"    SELECT AVG(salary) " +"    FROM staff " +"    WHERE department_id = e.department_id " +") " +"GROUP BY d.department_id, e.employee_id, e.first_name, e.last_name, d.department_name, e.salary " +"HAVING COUNT(*) = 1  " +"ORDER BY d.department_name " +"LIMIT 10 OFFSET 20;";Statement statement = CCJSqlParserUtil.parse(sql);//如果是 select 语句if (statement instanceof Select) {Select selectStatement = (Select) statement;PlainSelect plainSelect = selectStatement.getPlainSelect();System.out.println("【DISTINCT 子句】：" + plainSelect.getDistinct());System.out.println("【查询字段】：" + plainSelect.getSelectItems());System.out.println("【FROM 表】：" + plainSelect.getFromItem());System.out.println("【JOIN 子句】：" + plainSelect.getJoins());System.out.println("【WHERE 子句】：" + plainSelect.getWhere());System.out.println("【GROUP BY 子句】：" + plainSelect.getGroupBy());System.out.println("【HAVING 子句】：" +plainSelect.getHaving());System.out.println("【ORDER BY 子句】：" + plainSelect.getOrderByElements());System.out.println("【LIMIT 子句】：" + plainSelect.getLimit());System.out.println("【OFFSET 子句】：" + plainSelect.getOffset());}}
}

输出：

【DISTINCT 子句】：DISTINCT
【查询字段】：[e.employee_id, e.first_name, e.last_name, d.department_name, e.salary]
【FROM 表】：employees e
【JOIN 子句】：[LEFT JOIN departments d ON e.department_id = d.department_id]
【WHERE 子句】：e.salary > (SELECT AVG(salary) FROM staff WHERE department_id = e.department_id)
【GROUP BY 子句】：GROUP BY d.department_id, e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
【HAVING 子句】：COUNT(*) = 1
【ORDER BY 子句】：[d.department_name]
【LIMIT 子句】： LIMIT 10
【OFFSET 子句】： OFFSET 20