JSQL – easy way to test & maintain your SQL in Java

JSQL Parser is open source library developed under dual license: LGPL V2.1 and Apache Software License. Main responsibility of the framework is parsing of SQL statements.

Introduction

There are millions of technologies working in Java on the persistence layer. The most popular ones like Hibernate or EclipseLink are working pretty stable and acceptable. If system requirements fit to them there is actually no need to look for something else. I had recently experience with something different than usual in the scope of persistence layer.

I was working on the application when we had to build over a dozen huge database queries. These queries were always built up in runtime with changing lists of arguments and conditions. There was actually no option to use any ORM for that purpose as we were client of a big platform and we didn’t have database schema available on our repository. That main platform system had hundreds of database tables so even if that could be possible, creating our own ORM mapping could took months. We decided to use pretty small and agile library called JSQL Parser instead of, for example, Hibernate Criteria API for that purpose and after some period of time I had realized that it was the perfect solution.

Ideas and examples

First, as usually, some quick description. JSQL Parser is open source library developed under dual license: LGPL V2.1 and Apache Software License. Main responsibility of the framework is parsing of SQL statements. JSQL can also easily prepare hierarchical structure of SQL classes and objects. It supports all of the most popular RDBMS solutions. We can also use Visitor pattern to lookup structure constructed by the framework. JSQL Parser is available here on github: https://github.com/JSQLParser/JSqlParser/

To use JSQL Parser all we need to do is include such maven dependency into our project in pom.xml:

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>0.9.6</version>
</dependency>

I am going to create simple and fast showcase for JSQL Parser, please take a look at the following code:

public final static String CORE_QUERY = "select report_id from report";

public static String addNavCondition(String coreQuery){
    Select select = createSelect(coreQuery);
    GreaterThan greaterThan = new GreaterThan();
    greaterThan.setLeftExpression(new Column("NAV"));
    UserVariable userVariable =  new UserVariable();
    userVariable.setName("NavGUI");
    greaterThan.setRightExpression(userVariable);
    addWhereCondition(select, greaterThan);
    return select.toString();
}

public static String addStatusCondition(String coreQuery, String status){
    Select select = createSelect(coreQuery);
    EqualsTo equalsTo = new EqualsTo();
    equalsTo.setLeftExpression(new Column("status"));
    equalsTo.setRightExpression(new StringValue(status));
    addWhereCondition(select, equalsTo);
    return select.toString();
}

public static String buildQuery(){
    return addStatusCondition(addNavCondition(CORE_QUERY), "'IN_PROGRESS'");
}

public static void main(String... args){
    System.out.prinln(buildQuery());
}

Console Output from above code: 

SELECT report_id FROM report WHERE NAV > @NavGUI AND status = 'IN_PROGRESS’

As the entry point to our query I wrote CORE_QUERY object, which will always return report_id.  I’ve also prepared some utility methods for JSQL to simplify its usage (full code would be available on my github) as methods such “addWhereCondition(…)” are not part of standard JSQL Parser library. Let’s talk now about two other presented methods, they both will extend CORE_QUERY and show pretty well how JSQL Parser works. As we can see all about SQL here is really objective. First method is adding “greater” than condition to my CORE_QUERY, where NAV value is supposed to be higher than some user variable called NavGUI. Such user variable can be overwritten later by some input from GUI for example. Another method works almost the same, the only difference is that it looks for “equals to” condition, where status must be equal to some String provided as method parameter.  These are just +/- 20 lines of code. But it shows full of JSQL Parser power. JSQL Parser supports all SQL syntax operation. Like we’ve already seen, some conditionals but also: functions, parenthesis, joins etc.

Someone may say: – Hey, you could write that simple query in one line as a String!

Of course I could, and I agree it would work fine if that’s only one query in my application. But what if I need to reuse parts of such query thousand times? And I have N possible scenarios as a final query prepared as an effect of building in from such small parts?

I am pretty sure that String concatenation would be nightmare for every developer who is going to do changes on such code and simply will not be elegant. What is the difference in such JSQLParser in comparison to ORM like Hibernate? Speed and simplicity – it doesn’t require any mapping, we are not using HQL or any other heavy criteria classes, we are just using pretty simple and light objects representing popular SQL operations.

In addition it is much easier to test such builders. In my trivial example such test may look just like below:

public class JsqParserIntroductionTest {

    private String query = "SELECT report_id FROM report WHERE NAV > @NavGUI AND status = 'IN_PROGRESS'";

    @Test
    public void verifySQLBuilder(){
        assertEquals(query,JsqlParserIntroduction.buildQuery());
    }
}

Code of JSQLParser Introduction is available on my github:https://github.com/michalmytnik/jsqlparserexample/