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:

Stringquery=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:

Stringquery=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:

Stringquery=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:

Stringquery=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:

Stringquery=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:

Stringquery=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

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

SELECT t1, t2
FROM test
WHERE t1 = t2

Not equality

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

SELECT t1, t2
FROM test
WHERE t1 != t2

Greater than

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

SELECT t1, t2
FROM test
WHERE t1 > t2

Less than

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

SELECT t1, t2
FROM test
WHERE t1 < t2

Less or equal than

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

SELECT t1, t2
FROM test
WHERE t1 <= t2

Is null

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

SELECT t1, t2
FROM test
WHERE t1 IS NULL

Is not null

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

SELECT t1, t2
FROM test
WHERE t1 IS NOT NULL

In

Stringquery=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

Stringquery=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:

Stringquery=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

Stringquery=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

Stringquery=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

Stringquery=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

Stringquery=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

Stringquery=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:

Stringquery=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
HAVINGCOUNT (t3) >=1000ANDCOUNT (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:

Stringquery=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:

Queryquery1=Sequel.from("test1")
.select("t1","t2");
Queryquery2=Sequel.from("test2")
.select("t3","t4");
Stringquery=query1.union(query2).getSql();

Generated query is:

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

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

Queryquery1=Sequel.from("test1")
.select("t1","t2");
Queryquery2=Sequel.from("test2")
.select("t3","t4");
Stringquery=query1.unionAll(query2).getSql();

Generated query is:

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

INTERSECT

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

Queryquery1=Sequel.from("test1")
.select("t1","t2");
Queryquery2=Sequel.from("test2")
.select("t3","t4");
Stringquery=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:

Queryquery1=Sequel.from("test1")
.select("t1","t2");
Queryquery2=Sequel.from("test2")
.select("t3","t4");
Stringquery=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:

Stringquery=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:

Stringquery=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:

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

INSERT INTO test1 VALUES (?, ?)

It is also possible to specify columns in generated statement:

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

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

Insert using query is suppoerted too:

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

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