Sequel

Sequel query builder is a Java query builder inspired from PyPika. It supports:

Simple query filters like equal, not equal
Range filters like BETWEEN, IN, NOT IN
Criteria filter's method like AND, OR
GROUP BY using standard functions for aggregation like COUNT(), SUM() and so on

Intention of the library is to generate a valid SQL Sequel. Is not an ORM and is not intended to use it for validating query input

Compatibility

This library is written for Java 8 or greater

Compilation

With maven, you should compile with:

mvn clean install

Add dependency

It is possible to add the library as dependency bay adding this in your pom.xml:

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

<dependencies>
<dependency>
    <groupId>com.github.henryx</groupId>
    <artifactId>sequel</artifactId>
    <version>0.20.0</version>
</dependency>
</dependencies>

Queries

Queries are generated by Query class. Usage is simple:

String query=Sequel.from("test")
        .select("t1","t2")
        .getSql();

That generates the following SQL:

SELECT t1, t2
FROM test

Query can be ordered using Sequel.orderBy method:

String query=Sequel.from("test")
        .select("t1","t2")
        .orderBy("t1")
        .getSql();

SELECT t1, t2
FROM test
ORDER BY t1

Filters

Data can be filtered via Sequel.where method:

String query=Sequel.from("test")
        .where(Criterion.eq("t1","t2"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 = t2ø

Sequel.where method can be repeated:

String query=Sequel.from("test")
        .where(Criterion.eq("t1","t2"))
        .where(Criterion.eq("t1","?"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 = t2
  AND t1 = ?

Criteria

All criteria filters are implemented via Criterion class and applied at Sequel.where method:

String query=Sequel.from("test")
        .where(Criterion.eq("t1","t2"))
        .select("t1","t2")
        .getSql();

Generated SQL is:

SELECT t1, t2
FROM test
WHERE t1 = t2

Criterion class implements also a criterion method used to apply filter. Criterion method can be AND or OR:

String query=Sequel.from("test")
        .where(Criterion.eq("t1","?"))
        .where(Criterion.eq("t1","?").method(Criterion.OR))
        .where(Criterion.neq("t2","?").method(Criterion.AND))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 = ?
   OR t1 = ?
  AND t2 != ?

Criteria filters implemented are:

Equality

String query=Sequel.from("test")
        .where(Criterion.eq("t1","t2"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 = t2

Not equality

String query=Sequel.from("test")
        .where(Criterion.neq("t1","t2"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 != t2

Greater than

String query=Sequel.from("test")
        .where(Criterion.gt("t1","t2"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 > t2

Less than

String query=Sequel.from("test")
        .where(Criterion.lt("t1","t2"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 < t2

Less or equal than

String query=Sequel.from("test")
        .where(Criterion.lte("t1","t2"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 <= t2

Is null

String query=Sequel.from("test")
        .where(Criterion.isNull("t1"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 IS NULL

Is not null

String query=Sequel.from("test")
        .where(Criterion.isNotNull("t1"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 IS NOT NULL

In

String query=Sequel.from("test")
        .where(Criterion.in("t1","1","2","3"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 IN (1, 2, 3)

Not in

String query=Sequel.from("test")
        .where(Criterion.nin("t1","1","2","3"))
        .select("t1","t2")
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 NOT IN (1, 2, 3)

Subqueries

Is it possible to define a subquery as filter in all criteria methods:

String query=Sequel.from("test")
        .select("t1","t2")
        .where(Criterion.gte("t1",Sequel.from("test").select("a2").where(Criterion.neq("a1","3"))))
        .getSql();

SELECT t1, t2
FROM test
WHERE t1 >= (SELECT a2 FROM test WHERE a1 != 3)

Aggregations

Sequel.groupBy mehtod implements aggregation methods described in Functions class:

Count

String query=Sequel.from("test")
        .where(Criterion.neq("t1","t2"))
        .select("t1","t2",Functions.count("t3").getSql())
        .groupBy("t1","t2")
        .getSql();

SELECT t1, t2, COUNT(t3)
FROM test
WHERE t1 != t2
GROUP BY t1, t2

Sum

String query=Sequel.from("test")
        .where(Criterion.neq("t1","t2"))
        .select("t1","t2",Functions.sum("t3").getSql())
        .groupBy("t1","t2")
        .getSql();

SELECT t1, t2, SUM(t3)
FROM test
WHERE t1 != t2
GROUP BY t1, t2

Average

String query=Sequel.from("test")
        .where(Criterion.neq("t1","t2"))
        .select("t1","t2",Functions.avg("t3").getSql())
        .groupBy("t1","t2")
        .getSql();

SELECT t1, t2, AVG(t3)
FROM test
WHERE t1 != t2
GROUP BY t1, t2

Minimum

String query=Sequel.from("test")
        .where(Criterion.neq("t1","t2"))
        .select("t1","t2",Functions.min("t3").getSql())
        .groupBy("t1","t2")
        .getSql();

SELECT t1, t2, MIN(t3)
FROM test
WHERE t1 != t2
GROUP BY t1, t2

Maximum

String query=Sequel.from("test")
        .where(Criterion.neq("t1","t2"))
        .select("t1","t2",Functions.max("t3").getSql())
        .groupBy("t1","t2")
        .getSql();

SELECT t1, t2, MAX(t3)
FROM test
WHERE t1 != t2
GROUP BY t1, t2

Having

With Sequel.having method is possible to implement a HAVING filter:

String query=Sequel.from("test")
        .where(Criterion.neq("t1","t2"))
        .select("t1","t2",Functions.count("t3").getSql())
        .groupBy("t1","t2")
        .having(Criterion.gte(Functions.count("t3").getSql(),"1000"))
        .getSql();

SELECT t1, t2, COUNT(t3)
FROM test
WHERE t1 != t2
GROUP BY t1, t2
HAVING COUNT (t3) >= 1000 AND COUNT (t1) < 10

As viewed in WHERE clause, Sequel.having can be repeated multiple times using Criterion class logic

Limit and offset

Query support pagination with limit() and offset() methods:

String query=Sequel.from("test")
        .select("t1","t2")
        .limit(10)
        .offset(3)
        .getSql();

Generated query performs the SQL:2008 standard:

SELECT t1, t2
FROM test OFFSET 3 ROWS FETCH FIRST 10 ROWS ONLY;

Sets

UNIONs

Query support UNION clause in queries using union() method:

Query query1=Sequel.from("test1")
        .select("t1","t2");

        Query query2=Sequel.from("test2")
        .select("t3","t4");

        String query=query1.union(query2).getSql();

Generated query is:

SELECT t1, t2
FROM test1
UNION
SELECT t3, t4
FROM test2

In the same way, support of UNION ALL is made by unionAll() method:

Query query1=Sequel.from("test1")
        .select("t1","t2");

        Query query2=Sequel.from("test2")
        .select("t3","t4");

        String query=query1.unionAll(query2).getSql();

Generated query is:

SELECT t1, t2
FROM test1
UNION ALL
SELECT t3, t4
FROM test2

INTERSECT

Query support INTERSECT clause in queries using intersect() method:

Query query1=Sequel.from("test1")
        .select("t1","t2");

        Query query2=Sequel.from("test2")
        .select("t3","t4");

        String query=query1.intersect(query2).getSql();

Generated query is:

SELECT t1, t2
FROM test1
INTERSECT
SELECT t3, t4
FROM test2

EXCEPT

Query support EXCEPT clause in queries using except() method:

Query query1=Sequel.from("test1")
        .select("t1","t2");

        Query query2=Sequel.from("test2")
        .select("t3","t4");

        String query=query1.except(query2).getSql();

Generated query is:

SELECT t1, t2
FROM test1
EXCEPT
SELECT t3, t4
FROM test2

Joins

Joins can be created via Join builder class:

String query=Sequel.from("test1")
        .select("t1","t2")
        .join(Join.join("test2")
        .on(Criterion.eq("t3","t2"))
        .getSql();

Generated query is:

SELECT t1, t2
FROM test1
         JOIN test2 ON t3 = t2

It is possible to specify the JOIN type:

String query=Sequel.from("test1")
        .select("t1","t2")
        .join(Join.join("test2",JoinType.INNER)
        .on(Criterion.eq("t3","t2")))
        .on(Criterion.eq("t3","1")))
        .getSql();

That generate this query:

SELECT t1, t2
FROM test1
         INNER JOIN test2 ON t3 = t2 AND t3 = 1

Insert

It is possible to generate insert statements:

String q=Sequel.into("test1")
        .insert("?","?")
        .getSql();

INSERT INTO test1 VALUES (?, ?)

It is also possible to specify columns in generated statement:

String q=Sequel.into("test1")
        .columns("t1","t2")
        .insert("?","?")
        .getSql();

INSERT INTO test1 (t1, t2) VALUES (?, ?)

Insert using query is suppoerted too:

Sequel.Select query=Sequel.from("test2").select("t3","t4");
String sql=Sequel.into("test1")
        .columns("t1","t2")
        .select(query)
        .insert("?","?")
        .getSql();

INSERT INTO test1 (t1, t2) SELECT t3, t4 FROM test2

ronmarasigan/sequel

Sequel

Compatibility

Compilation

Add dependency

Queries

Filters

Criteria

Equality

Not equality

Greater than

Less than

Less or equal than

Is null

Is not null

In

Not in

Subqueries

Aggregations

Count

Sum

Average

Minimum

Maximum

Having

Limit and offset

Sets

UNIONs

INTERSECT

EXCEPT

Joins

Insert