How to Parse Dynamic SQL with Druid: A Practical Guide

Background

Dynamic SQL is difficult because the final query must be generated based on runtime parameters. MyBatis achieves this with many XML tags, which adds complexity. The author proposes using Alibaba's open‑source Druid parser to dynamically remove or keep WHERE clauses according to supplied parameters.

Druid SQL AST

Druid includes a high‑performance SQL parser used for SQL injection protection, SQL merging, formatting, and sharding. Compared with Antlr, Druid’s parser is fast enough for production use. It builds an abstract syntax tree (AST) for each statement.

AST Node Types

package com.alibaba.druid.sql.ast;

interface SQLObject {}
interface SQLExpr extends SQLObject {}
interface SQLStatement extends SQLObject {}
interface SQLTableSource extends SQLObject {}
class SQLSelect extends SQLObject {}
class SQLSelectQueryBlock extends SQLObject {}

Common SQLExpr Implementations

public interface SQLName extends SQLExpr, SQLIdentifier {}
// ID = 3 → SQLIdentifierExpr
class SQLIdentifierExpr implements SQLExpr, SQLName { String name; }
// A.ID = 3 → SQLPropertyExpr
class SQLPropertyExpr implements SQLExpr, SQLName { SQLExpr owner; String name; }
// ID = 3 → SQLBinaryOpExpr
class SQLBinaryOpExpr implements SQLExpr { SQLExpr left; SQLExpr right; SQLBinaryOperator operator; }
// SELECT * FROM ... WHERE id = ? → SQLVariantRefExpr
class SQLVariantRefExpr extends SQLExprImpl { String name; }
// ID = 3 → SQLIntegerExpr
class SQLIntegerExpr extends SQLNumericLiteralExpr implements SQLValuableExpr { Number number; @Override Object getValue() { return this.number; } }
// NAME = 'jobs' → SQLCharExpr
class SQLCharExpr extends SQLTextLiteralExpr implements SQLValuableExpr { String text; }

Common SQLStatement Classes

package com.alibaba.druid.sql.ast.statement;

class SQLSelectStatement implements SQLStatement { SQLSelect select; }
class SQLUpdateStatement implements SQLStatement { SQLExprTableSource tableSource; List<SQLUpdateSetItem> items; SQLExpr where; }
class SQLDeleteStatement implements SQLStatement { SQLTableSource tableSource; SQLExpr where; }
class SQLInsertStatement implements SQLStatement { SQLExprTableSource tableSource; List<SQLExpr> columns; SQLSelect query; }

SQLTableSource Variants

Typical sources include SQLExprTableSource, SQLJoinTableSource, SQLSubqueryTableSource, and SQLWithSubqueryClause.Entry. Examples illustrate how a simple FROM emp becomes an SQLExprTableSource, while a join creates an SQLJoinTableSource, and a sub‑query creates an SQLSubqueryTableSource.

SQLSelect and Query Blocks

class SQLSelect extends SQLObjectImpl { SQLWithSubqueryClause withSubQuery; SQLSelectQuery query; }
interface SQLSelectQuery extends SQLObject {}
class SQLSelectQueryBlock implements SQLSelectQuery { List<SQLSelectItem> selectList; SQLTableSource from; SQLExpr where; SQLSelectGroupByClause groupBy; SQLOrderBy orderBy; SQLLimit limit; }
class SQLUnionQuery implements SQLSelectQuery { SQLSelectQuery left; SQLSelectQuery right; SQLUnionOperator operator; }

Visitor Usage

Druid provides default visitors. To collect tables used in a statement, the author creates a MySqlSchemaStatVisitor, lets the AST accept it, and then calls visitor.getTables(). The visitor also formats the SQL via SQLUtils.toSQLString(statement).

public String parseSQL(String sql) {
    // New MySQL parser
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL);
    // Parse to AST (SQLSelectStatement)
    SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();
    // Visit AST
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    statement.accept(visitor);
    // Format SQL
    String beautySQL = SQLUtils.toSQLString(statement);
    return beautySQL;
}

Dynamic SQL Example

The author builds a complex SELECT with placeholders like @depId, @orgId, etc., and stores the values in a Map<String, String> params. The DynamicSqlParser parses the SQL, extracts all @var conditions, and replaces them with either the supplied value or the constant condition 1=1 when the parameter is missing.

public static void main(String[] args) {
    String sql = "SELECT * FROM (SELECT id, org_id, NAME, age, phone, email, ( SELECT dep_name FROM dept WHERE dep_id = @depId ) FROM USER t 
" +
                 "WHERE t.is_delete = 0 AND t.id IN ( SELECT id FROM USER WHERE org_id = @orgId ) AND create_time>= @createTime AND age = @age AND type IN @type AND state <> @state 
" +
                 "AND ( name = @name OR user_name = @name ) ) AS a 
" +
                 "WHERE a.org_id = @orgId AND id IN @id AND email LIKE '%@163.com' AND phone = @phone";
    Map<String, String> params = new HashMap<>();
    params.put("@depId", "567");
    params.put("@orgId", "432");
    params.put("@createTime", "'2022-06-30 19:00:00'");
    params.put("@type", "6,7,8,9,10000");
    params.put("@name", "张三");
    params.put("@phone", "'1234567789'");
    DynamicSqlParser dynamicSqlParser = new DynamicSqlParser();
    String beautySQL = dynamicSqlParser.parseSQL(sql, params);
    System.out.println(beautySQL);
}

The output shows that supplied parameters are substituted correctly, while missing ones become 1=1, achieving true dynamic‑SQL behaviour.

Implementation Details

A thread‑local map varToWhere stores each @var together with the set of WHERE clauses that contain it. Because a variable may appear in multiple places (e.g., @orgId in two conditions), a Set<String> is used. Debug output of the map is shown in the article.

{
  @createTime=[create_TIME >= @createTime],
  @age=[age = @age],
  @state=[state <> @state],
  @name=[user_name = @name, name = @name],
  @type=[type IN (@type)],
  @orgId=[org_id = @orgId, a.org_id = @orgId],
  @id=[id IN (@id)],
  @depId=[dep_id = @depId],
  @phone=[phone = @phone]
}

The final parseSQL(String sql, Map<String, String> params) method iterates over this map, replaces each condition with the concrete value or with the constant 1=1, and returns the fully rendered SQL. The author notes that the approach works well for many cases but cannot guarantee handling of every possible SQL edge case.