JSQLParser is one of the most powerful open source parsers for parsing SQL statements using Java classes. The parser is capable of parsing DDL and DML statements and exposing the details of the query.
In this post, we will see how the JSQLParser library is used to parse a simple SELECT SQL statement and fetch the columns from that select statement.
For using the parser, first, we need to add the dependency of the JSQLParser library to our project.
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.2</version>
</dependency>
Parsing simple SELECT SQL with columns
In this example, let us take a simple select query that has columns selected from a single table under a database. Let’s try to parse this SQL and explore the objects inside the same.
SELECT ID, NAME, CITY, ADDRESS, COUNTRY, GENDER, CREATED_DT FROM CUSTOMERDB.CUSTOMERINFO
The above SQL is a simple one with a few columns selected from the CUSTOMERINFO table which is in the CUSTOMERDB database. Let us explore how to parse this.
After adding this dependency, you will be able to use the classes from the JSQLParser library.
Let us extract all the columns from the select query and print. The following JSQLParser example will show how to parse a simple select query in Java.
String selectSQL = "SELECT\n" +
"ID, NAME, CITY, ADDRESS, COUNTRY, GENDER, CREATED_DT\n" +
"FROM CUSTOMERDB.CUSTOMERINFO ";
Statement select = (Statement) CCJSqlParserUtil.parse(selectSQL);
List<SelectItem> selectlist = ((PlainSelect) ((Select) select).getSelectBody()).getSelectItems();
System.out.println("List of columns in select query");
System.out.println("--------------------------------");
List<SelectItem> selectCols = ((PlainSelect) ((Select) select).getSelectBody()).getSelectItems();
for (SelectItem selectItem : selectCols)
System.out.println(selectItem.toString());
Parsing simple SELECT SQL with Where clause
Let us try to parse another SQL statement with a where condition. Along with extracting the columns from the select statement, we can extract the where condition also.
String selectSQL = "SELECT\n" +
"ID, NAME, CITY, ADDRESS, COUNTRY, GENDER, CREATED_DT\n" +
"FROM CUSTOMERDB.CUSTOMERINFO WHERE CITY='CHENNAI'";
Statement select = (Statement) CCJSqlParserUtil.parse(selectSQL);
String whereCondition = ((PlainSelect) ((Select) select).getSelectBody()).getWhere().toString();
System.out.println("Where condition: " + whereCondition);
From the above code, you can see that we are first extracting the Select object from the SQL, and in that, we are able to extract the where condition.
Parsing SELECT SQL with Join conditions
In this example, let us try to parse a select statement that has multiple join statements. We will parse the join statements and extract the info like the number of joins used, what are the expressions used in each of the ON conditions of the statement.
String selectSQL = "SELECT CI.ID, CI.NAME, C.COUNTRY, S.SALESDATE \n" +
"FROM CUSTOMERDB.CUSTOMERINFO CI\n" +
"LEFT JOIN CUSTOMERDB.COUNTRY C ON CI.COUNTRYCODE = C.COUNTRYCODE " +
"LEFT JOIN CUSTOMERDB.SALES S ON CI.COUNTRYCODE = S.COUNTRYCODE;";
Statement select = (Statement) CCJSqlParserUtil.parse(selectSQL);
List<Join> joins = ((PlainSelect) ((Select) select).getSelectBody()).getJoins();
System.out.println("Number of joins: " + joins.size());
for(Join join : joins)
{
System.out.println("Join condition: " + join.toString());
System.out.println("ON elements of join");
for (Expression exp: join.getOnExpressions())
System.out.println(exp.toString());
}
As mentioned, there are two join conditions in the above SQL statement. The first for loop will process each join statement. The second for loop will go through each ON expression of that join condition.
Also check “Parsing INSERT sql using jsqlparser“,”Parsing CREATE sql using jsqlparser“