Skip to content

Latest commit

 

History

History
621 lines (476 loc) · 10.3 KB

README.md

File metadata and controls

621 lines (476 loc) · 10.3 KB
Raw

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