Parse database query with JSQL Parser

Table of contents

Reading Time: 3 minutes

Hi guys, as we discussed in the previous blog that is about parsing the database query, it is also an alternative for parsing the SQL queries. So write the query of database and that looks good to you but you don’t know the query you wrote was syntactically correct or not. In this blog, we parse the database query and test it using a test case with the help of JSQL. Now let’s know it con and prons and how to use it for parsing the database query.

Introduction

JSqlParser parses an SQL statement and translates it into a hierarchy of Java classes. The generated hierarchy can be navigated using the Visitor Pattern. JSqlParser is not limited to one database but provides support for a lot of specials of Oracle, SqlServer, MySQL, PostgreSQL … To name some, it has support for Oracles join syntax using (+), PostgreSQLs cast syntax using ::, relational operators like != and so on.

What is JSQL?

SQL parser
SQL validation
Query optimizer
SQL generator

How it works and what it does?

The parser is built using JavaCC. The core JavaCC grammar for SQL has been taken from Guido Draheim’s site and has been changed in order to produce a hierarchy of Java classes. The classes called deparsers are used to build again the SQL text of the class hierarchy.

Over the time the grammar was extended and now is a combination of specialities of grammars of various database systems. It is grown by need. So some (not all) Oracle, MySql, SQLServer, PostgreSQL specific aspects can be parsed.

SQL parser
SQL validation
Query optimizer

Clean up messy SQL codes

Easily integrate SQL formatter into your application for a color-coded layout that is easy to navigate, giving your product a professional feeling.

Detect SQL syntax errors

Your application will be able to validate SQL syntax before a database executes the query. This is very useful especially if your SQL was dynamically built based on user input.

Rid SQL injections

Avoid being vulnerable to SQL injection in your ASP.NET or Java application, by automatically detecting malicious SQL segment with our ready-to-use library.

Retrieve/Refactor table & column name

Precisely determining and renaming every table and column in stored SQL statements is very complex with a lot of nesting and sub-queries, but we can make it hassle-free for you

Query parse tree in XML

Parsing SQL is a notoriously difficult task, but we are here to help. It will provide an SQL query parse tree in XML output that will allow you to further process SQL scripts.

Discover the relationship in DDL

Quickly locate the primary key and/or foreign key in DDL scripts to figure out the complex relationship among the various tables. Extracted table, attribute names, data types can be used to generate the CRUD-Statements.

Advantage of using JSQL parser:

The following features are complete.

Query parser, validator and optimizer.
Many standard functions and aggregate functions.
SQL features: SELECT, FROM (including JOIN syntax), WHERE, GROUP BY (including GROUPING SETS), aggregate functions (including COUNT(DISTINCT …) and FILTER), HAVING, ORDER BY (including NULLS FIRST/LAST), set operations (UNION, INTERSECT, MINUS), sub-queries (including correlated sub-queries), windowed aggregates, LIMIT (syntax as Postgres); more details in the SQL reference

Why do people choose to use our powerful SQL Parser?

Due to the complexity of the SQL grammar, many people have attempted but failed to generate a successful parser.
JSQL parser team constantly releasing new versions of databases, so maintaining an original home-grown SQL parser is time-consuming, error-prone, and costly. We are dedicated to helping your product adapt to the latest versions of database programs.
With the appropriate library, like the one we provide, you can save hundreds of hours, if not more, on developing time. As a result, the efficiency of your business will increase dramatically.

Components of Calcite

SQL validator – Validates abstract syntax trees against metadata provided by the catalog

SQL parser – Parses valid SQL queries into an abstract syntax tree (AST).

Query optimiser – Converts AST into logical plans, optimizes logical plans, and converts logical expressions into physical plans

SQL generator – Converts physical plans to SQL

Query Optimization

Now lets coming how to parse the SQL query using Apache Calcite.

1: Add this plugin into your Dependencies or module where you have the database queries

“libraryDependencies += “com.github.jsqlparser” % “jsqlparser” % “0.9””

2: create the method that takes query as input returns the SqlNode.

In this method you have to give the config like below:

val sqlParserConfig = CalciteParser

.configBuilder()

.setParserFactory(SqlBabelParserImpl.FACTORY)

.setConformance(SqlConformanceEnum.BABEL)

.build()

3: Passes the above config (I am taking the example of BABEL you can choose your own Parser Factory)

CalciteParser.create(sql, sqlParserConfig).parseQuery()

4: Now, in test cases simply assert your database query or method that returns the query

assert(parseSql(resultedSql).isInstanceOf[SqlNode])

In which parseSql is a method that actually parse the Query.

Overview

A framework for building SQL databases. JQLParser is developed over more than ten years. Written in Java and previously known as Optimiser. Also known as Farrago. It became an Apache project in 2013. Led by Julian Hyde at Hortonworks.