Master SQL Parsing in Java with JSQLParser: Real‑World Examples and Tips

JSQLParser is an open‑source Java library that parses SQL statements into an abstract syntax tree (AST), allowing developers to analyze, modify, and regenerate queries.

1. Introduction

JSQLParser supports multiple dialects such as MySQL, PostgreSQL, Oracle and SQL Server.

2. Practical Examples

2.1 Environment Setup

<code><dependency>
  <groupId>com.github.jsqlparser</groupId>
  <artifactId>jsqlparser</artifactId>
  <version>5.1</version>
</dependency></code>

After adding the dependency you can start using the parser.

2.2 Simple Parsing

<code>public static void example1() throws Exception {
  String sqlStr = "select id, name, age from user where id = 6";
  PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(sqlStr);
  List<SelectItem<?>> selects = select.getSelectItems();
  System.err.printf("select字段: %s%n", selects);
  Table table = (Table) select.getFromItem();
  System.err.printf("表名: %s%n", table.getName());
}</code>

The output shows the selected columns and the table name.

2.3 Find All Tables

<code>String sqlStr = "select id, (select name from role r where r.uid = u.id) from user u";
Set<String> tables = TablesNamesFinder.findTables(sqlStr);
System.err.println(tables);</code>

Result: [role, user]

2.4 Extract WHERE Clause

<code>public static void example3() throws Exception {
  String sqlStr = "SELECT id, name, role_name FROM USER u LEFT JOIN role r ON ( u.role_id = r.id ) WHERE name = 'pack' AND age = 20 AND (email = 'xxx' or state = 1)";
  PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(sqlStr);
  Expression where = select.getWhere();
  System.err.println(where);
  print(where);
}
private static void print(Expression expression) {
  if (expression instanceof ComparisonOperator) {
    System.out.println(expression);
    return;
  }
  if (expression instanceof BinaryExpression) {
    print(((BinaryExpression) expression).getLeftExpression());
    print(((BinaryExpression) expression).getRightExpression());
  } else if (expression instanceof ParenthesedExpressionList) {
    ((ParenthesedExpressionList) expression).stream().forEach(e -> print((Expression) e));
  }
}</code>

The visualizer below displays the full condition tree.

2.5 Build / Modify SQL

<code>public static void example5() throws Exception {
  Table table = new Table().withName("user").withAlias(new Alias("t", false));
  Column nameColumn = new Column().withColumnName("name");
  StringValue nameValue = new StringValue("admin");
  Expression whereExpression = new EqualsTo().withLeftExpression(nameColumn).withRightExpression(nameValue);
  PlainSelect select = new PlainSelect()
      .addSelectItems(new Column("id"), new Column("age"), new Column("name"))
      .withFromItem(table)
      .withWhere(whereExpression);
  System.err.println(select.toString());
}</code>

Resulting SQL: SELECT id, age, name FROM user t WHERE name = 'admin'

2.6 Error Handling

<code>CCJSqlParser parser = new CCJSqlParser("select * from user; select from; select * from role");
Statements statements = parser.Statements();
System.err.println(statements.size());
statements.stream().forEach(System.out::println);
</code>

Using withErrorRecovery(true) or withUnsupportedStatements() allows parsing to continue after errors, returning empty or original statements for the faulty parts.