Build Your First SQL Analysis Tool with Java¶

This tutorial starts after installation. You will follow a SQL statement from input text to a database-specific parser, inspect the resulting statement and AST nodes, and turn parse failures into an explicit application outcome.

Use the Quick Start first if the Java dependency is not installed or your first parser does not run. The Quick Start remains the source for current package versions and environment troubleshooting.

What you will build¶

By the end, your Java program will be able to:

select the grammar that matches the source database;
parse a SQL statement and check the return code;
identify the parsed statement type;
inspect result columns, tables, joins, and the WHERE condition;
return a useful error when the SQL is invalid or the dialect is wrong.

These are the same building blocks used by SQL editors, CI validators, query gateways, catalog scanners, and migration tools.

Before you begin¶

Complete the Java Quick Start.
Use Java 8 or later and Maven or Gradle.
Be comfortable with Java and basic SQL syntax.

You can compare your code with the runnable analyzescript tutorial source. To run that source in the complete demo repository:

git clone https://github.com/sqlparser/gsp_demo_java.git
cd gsp_demo_java
mvn -q compile exec:java \
  -Dexec.mainClass=gudusoft.gsqlparser.demos.analyzescript.tutorial

Follow SQL through the parser¶

Understand the parse contract¶

Before we start parsing, let's understand the key concepts:

"Core Concepts"

**TGSqlParser** - The main parser class that processes SQL text

**EDbVendor** - Enum specifying the database vendor (Oracle, SQL Server, etc.)

**AST (Abstract Syntax Tree)** - The structured representation of parsed SQL

**SQL Statements** - Individual SQL commands (SELECT, INSERT, etc.)

**Parse Result** - Integer return code (0 = success, non-zero = error)

Parse a statement and check the result¶

Let's parse a simple SELECT statement:

package com.example.gsptutorial;

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;

public class FirstParse {
    public static void main(String[] args) {
        // Step 1: Create parser for Oracle SQL
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);

        // Step 2: Set the SQL text to parse
        String sql = "SELECT employee_id, first_name, last_name FROM employees";
        parser.sqltext = sql;

        // Step 3: Parse the SQL
        int result = parser.parse();

        // Step 4: Check the result
        if (result == 0) {
            System.out.println("✅ Parse successful!");
            System.out.println("Number of statements: " + parser.sqlstatements.size());
            System.out.println("Statement type: " + parser.sqlstatements.get(0).sqlstatementtype);
        } else {
            System.out.println("❌ Parse failed!");
            System.out.println("Error: " + parser.getErrormessage());
        }
    }
}

Expected Output:

✅ Parse successful!
Number of statements: 1
Statement type: sstselect

Inspect the AST¶

Now let's explore what we can extract from the parsed SQL:

package com.example.gsptutorial;

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;
import gudusoft.gsqlparser.nodes.TTable;

public class ExploreAST {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = "SELECT emp.employee_id, emp.first_name, dept.department_name " +
                        "FROM employees emp " +
                        "JOIN departments dept ON emp.department_id = dept.department_id " +
                        "WHERE emp.salary > 50000";

        if (parser.parse() == 0) {
            // Cast to SELECT statement
            TSelectSqlStatement select = (TSelectSqlStatement) parser.sqlstatements.get(0);

            // Extract tables
            System.out.println("📋 Tables used:");
            for (int i = 0; i < select.tables.size(); i++) {
                TTable table = select.tables.getTable(i);
                System.out.println("  - " + table.getTableName() + 
                    (table.getAliasClause() != null ? " (alias: " + table.getAliasClause() + ")" : ""));
            }

            // Extract columns
            System.out.println("\n📊 Columns selected:");
            for (int i = 0; i < select.getResultColumnList().size(); i++) {
                System.out.println("  - " + select.getResultColumnList().getResultColumn(i).toString());
            }

            // Check for WHERE clause
            if (select.getWhereClause() != null) {
                System.out.println("\n🔍 WHERE condition:");
                System.out.println("  " + select.getWhereClause().toString());
            }
        }
    }
}

Expected Output:

📋 Tables used:
  - employees (alias: emp)
  - departments (alias: dept)

📊 Columns selected:
  - emp.employee_id
  - emp.first_name
  - dept.department_name

🔍 WHERE condition:
  emp.salary > 50000

Match the parser to the source database¶

GSP supports 30+ database vendors. Let's see how to handle different SQL dialects:

package com.example.gsptutorial;

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;

public class MultiVendorExample {
    public static void main(String[] args) {
        // Test different database-specific SQL
        testOracleSQL();
        testSQLServerSQL();
        testPostgreSQLSQL();
        testMySQLSQL();
    }

    private static void testOracleSQL() {
        System.out.println("🔶 Testing Oracle SQL:");
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = "SELECT * FROM dual WHERE ROWNUM = 1";

        if (parser.parse() == 0) {
            System.out.println("  ✅ Oracle SQL parsed successfully");
        } else {
            System.out.println("  ❌ Error: " + parser.getErrormessage());
        }
    }

    private static void testSQLServerSQL() {
        System.out.println("\n🔷 Testing SQL Server SQL:");
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvmssql);
        parser.sqltext = "SELECT TOP 10 * FROM employees";

        if (parser.parse() == 0) {
            System.out.println("  ✅ SQL Server SQL parsed successfully");
        } else {
            System.out.println("  ❌ Error: " + parser.getErrormessage());
        }
    }

    private static void testPostgreSQLSQL() {
        System.out.println("\n🐘 Testing PostgreSQL SQL:");
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvpostgresql);
        parser.sqltext = "SELECT * FROM employees LIMIT 10 OFFSET 5";

        if (parser.parse() == 0) {
            System.out.println("  ✅ PostgreSQL SQL parsed successfully");
        } else {
            System.out.println("  ❌ Error: " + parser.getErrormessage());
        }
    }

    private static void testMySQLSQL() {
        System.out.println("\n🐬 Testing MySQL SQL:");
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvmysql);
        parser.sqltext = "SELECT * FROM employees LIMIT 5, 10";

        if (parser.parse() == 0) {
            System.out.println("  ✅ MySQL SQL parsed successfully");
        } else {
            System.out.println("  ❌ Error: " + parser.getErrormessage());
        }
    }
}

Treat parse errors as an application outcome¶

Let's learn how to handle parsing errors properly:

package com.example.gsptutorial;

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;

public class ErrorHandlingExample {
    public static void main(String[] args) {
        // Test with invalid SQL
        testInvalidSQL();

        // Test with wrong vendor
        testWrongVendor();
    }

    private static void testInvalidSQL() {
        System.out.println("🚫 Testing invalid SQL:");
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = "SELECT * FROM"; // Missing table name

        int result = parser.parse();
        if (result != 0) {
            System.out.println("  ❌ Parse failed as expected");
            System.out.println("  📍 Error message: " + parser.getErrormessage());
        }
    }

    private static void testWrongVendor() {
        System.out.println("\n🔄 Testing SQL Server syntax with Oracle parser:");
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = "SELECT TOP 10 * FROM employees"; // SQL Server syntax

        int result = parser.parse();
        if (result != 0) {
            System.out.println("  ❌ Parse failed - wrong vendor");
            System.out.println("  💡 Tip: Use EDbVendor.dbvmssql for SQL Server syntax");
        }
    }
}

Try the workflow with your SQL¶

Try these exercises to reinforce your learning:

"Exercise 1: Parse Different Statement Types"

Parse these SQL statements and identify their types:

```sql
INSERT INTO employees (id, name) VALUES (1, 'John');
UPDATE employees SET salary = 60000 WHERE id = 1;
DELETE FROM employees WHERE id = 1;
CREATE TABLE test (id INT, name VARCHAR(50));
```

"Exercise 2: Extract Information"

For this complex query, extract all table names, column names, and join conditions:

```sql
SELECT e.employee_id, e.first_name, d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE e.hire_date > '2020-01-01'
ORDER BY e.last_name;
```

"Exercise 3: Error Recovery"

Choose a query that contains syntax unique to one database. Parse it with
the matching dialect and with one incorrect dialect, then compare the
results. Repeat the experiment with a plain `SELECT` that several dialects
accept. This shows why a successful parse alone is not a reliable dialect
detector.

What you can do now¶

You can select a database dialect, parse SQL, check the return code, identify a statement type, inspect common AST structures, and report a failure without executing the statement.

Working rules to carry into your application¶

Always check the parse result. Zero means success; a non-zero result is an outcome your application must surface or handle.
Choose the source dialect deliberately. Successful parsing with one grammar does not prove compatibility with a different target database.
Use AST nodes for structural questions. Do not infer tables, columns, joins, or predicates from string matches when the parser already exposes those objects.
Keep parsing separate from execution. GSP analyzes SQL offline; your database permissions, parameter binding, limits, and execution controls remain separate responsibilities.

API documentation — look up the Java API and generated Javadoc.
SQL syntax support — choose a dialect and review measured grammar coverage.
Error handling — report errors across scripts and production workflows.

Continue learning¶

Continue with Basic SQL Parsing to inspect common statement types and the AST properties that answer application-level questions. Use Working with Database Dialects when your input spans multiple database systems.