JSQLParser/JSqlParser

JSqlParser parses an SQL statement and translate it into a hierarchy of Java classes. The generated hierarchy can be navigated using the Visitor Pattern https://github.com/JSQLParser/JSqlParser/wiki

Download

Step 1. Add the JitPack repository to your build file

Add it in your root settings.gradle at the end of repositories:

	dependencyResolutionManagement {
		repositoriesMode.set(RepositoriesMode.FAIL_ON_PROJECT_REPOS)
		repositories {
			mavenCentral()
			maven { url 'https://jitpack.io' }
		}
	}

Add it in your settings.gradle.kts at the end of repositories:

	dependencyResolutionManagement {
		repositoriesMode.set(RepositoriesMode.FAIL_ON_PROJECT_REPOS)
		repositories {
			mavenCentral()
			maven { url = uri("https://jitpack.io") }
		}
	}

Add to pom.xml

	<repositories>
		<repository>
		    <id>jitpack.io</id>
		    <url>https://jitpack.io</url>
		</repository>
	</repositories>

Add it in your build.sbt at the end of resolvers:

 
    resolvers += "jitpack" at "https://jitpack.io"

Add it in your project.clj at the end of repositories:

 
    :repositories [["jitpack" "https://jitpack.io"]]

Step 2. Add the dependency

	dependencies {
		implementation 'com.github.jsqlparser:jsqlparser:'
	}
	dependencies {
		implementation("com.github.jsqlparser:jsqlparser:")
	}
	<dependency>
	    <groupId>com.github.jsqlparser</groupId>
	    <artifactId>jsqlparser</artifactId>
	    <version></version>
	</dependency>

                            
    libraryDependencies += "com.github.jsqlparser" % "jsqlparser" % ""
        
        

                            
    :dependencies [[com.github.jsqlparser/jsqlparser ""]]

Readme

JSqlParser 5.3 Website <img src="src/site/sphinx/_images/logo-no-background.svg" alt="drawing" width="200" align="right"/>

CI Coverage Status Codacy Badge Maven Central Javadocs Gitter

A huge thank you to our sponsor, Starlake.ai who simplifies data ingestion, transformation, and orchestration, enabling faster delivery of high-quality data. Starlake has been instrumental in providing Piped SQL and numerous test cases for BigQuery, Redshift, DataBricks, and DuckDB. Show your support for ongoing development by visiting Starlake.ai and giving us a star!

Summary

Please visit our WebSite for detailed information. JSqlParser is a RDBMS agnostic SQL statement parser. It translates SQL statements into a traversable hierarchy of Java classes (see Samples):

SELECT 1 FROM dual WHERE a = b

/* produces the following AST

SQL Text
 └─Statements: statement.select.PlainSelect
    ├─selectItems: statement.select.SelectItem
    │  └─LongValue: 1
    ├─Table: dual
    └─where: expression.operators.relational.EqualsTo
       ├─Column: a
       └─Column: b
*/

String sqlStr = "select 1 from dual where a=b";

PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(sqlStr);

SelectItem selectItem =
        select.getSelectItems().get(0);
Assertions.assertEquals(
        new LongValue(1)
        , selectItem.getExpression());

Table table = (Table) select.getFromItem();
Assertions.assertEquals("dual", table.getName());

EqualsTo equalsTo = (EqualsTo) select.getWhere();
Column a = (Column) equalsTo.getLeftExpression();
Column b = (Column) equalsTo.getRightExpression();
Assertions.assertEquals("a", a.getColumnName());
Assertions.assertEquals("b", b.getColumnName());

Support for Piped SQL

Work is progressing for parsing Piped SQL, a much saner and more logical way to write queries in its semantic order.

FROM Produce
|> WHERE
    item != 'bananas'
    AND category IN ('fruit', 'nut')
|> AGGREGATE COUNT(*) AS num_items, SUM(sales) AS total_sales
   GROUP BY item
|> ORDER BY item DESC;

For details, please see https://storage.googleapis.com/gweb-research2023-media/pubtools/1004848.pdf, https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax and https://duckdb.org/docs/sql/query_syntax/from.html#from-first-syntax

Java Version

JSQLParser-4.9 was the last JDK8 compatible version. JSQLParser-5.0 and later depend on JDK11 and introduce API breaking changes to the AST Visitors. Please see the Migration Guide for the details.

Building JSQLParser-5.1 and newer with Gradle will depend on a JDK17 toolchain due to the used plugins.

JSQLParser-5.4 Snapshot and later use JavaCC-8 Snapshots for generating the parser.

Performance

Unfortunately the released JSQLParser-5.2 shows a performance deterioration caused by commit 30cf5d7 related to FunctionAllColumns(). This has been resolved in JSQLParser 5.3-SNAPSHOT and JMH benchmarks have been added to avoid such regressions in the future. Further all LOOKAHEAD have been revised one by one, and we have gained back a very good performance of the Parser.

Benchmark                               (version)  Mode  Cnt   Score   Error  Units
JSQLParserBenchmark.parseSQLStatements     latest  avgt   15  82.695 ± 2.841  ms/op
JSQLParserBenchmark.parseSQLStatements        5.3  avgt   15  84.687 ± 3.321  ms/op
JSQLParserBenchmark.parseSQLStatements        5.1  avgt   15  86.592 ± 5.781  ms/op

Supported Grammar and Syntax

JSqlParser aims to support the SQL standard as well as all major RDBMS. Any missing syntax or features can be added on demand.

| RDBMS | Statements | |-----------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------| | Oracle<br>MS SQL Server and Sybase<br>Postgres<br>MySQL and MariaDB<br>DB2<br>H2 and HSQLDB and Derby<br>SQLite | SELECT<br>INSERT, UPDATE, UPSERT, MERGE<br>DELETE, TRUNCATE TABLE<br>CREATE ..., ALTER ...., DROP ...<br>WITH ... | | Salesforce SOQL | INCLUDES, EXCLUDES | | Piped SQL (also known as FROM SQL) | |

JSqlParser can also be used to create SQL Statements from Java Code with a fluent API (see Samples).

Sister Projects

If you like JSqlParser then please check out its related projects:

Alternatives to JSqlParser?

General SQL Parser looks pretty good, with extended SQL syntax (like PL/SQL and T-SQL) and java + .NET APIs. The tool is commercial (license available online), with a free download option.

Alternatively the dual-licensed JOOQ provides a handwritten Parser supporting a lot of RDBMS, translation between dialects, SQL transformation, can be used as a JDBC proxy for translation and transformation purposes.

Documentation

  1. Samples
  2. Build Instructions and Maven Artifact
  3. Contribution
  4. Change Log
  5. Issues

License

JSqlParser is dual licensed under LGPL V2.1 or Apache Software License, Version 2.0.