SQL String Builder DSL

What is this?

This is a simple DSL for building SQL queries in Java.

Why?

I needed a simple way to build SQL queries in Java, and I didn’t want to use additional libraries.

Features

• Column projections

  • Aliases

  • Aggregate functions

• Table aliases

  • Inter-schema joins support

• Table joins

  • Inner joins

  • Left joins

• Filters

  • Produces named parameters for later preparation of statement

• Aggregation

• Ordering (one or more columns)

• Pagination using standard limit-offset syntax

How do I use it?

For maven projects, add the dependency to your project:

<dependency>
  <groupId>com.lits.sql</groupId>
  <artifactId>sql-stringbuilder-dsl</artifactId>
  <version>0.0.12</version>
</dependency>

For gradle projects:

implementation 'io.github.psycotrompus:sql-stringbuilder-dsl:0.0.12'

or if you’re using Kotlin DSL in Gradle:

implementation("io.github.psycotrompus:sql-stringbuilder-dsl:0.0.12")

Create a SqlTable objects for each table in your query:

SqlTable tenant = SqlTable.of("tenant").on("schema1").as("t").build();
SqlTable profile = SqlTable.of("profile").on("schema2").as("p").build();
SqlTable user = SqlTable.of("user").on("schema3").as("u").build();

Then, create a SqlBuilder object and use it to build your query:

final var sql = SqlBuilder
    .select(user.column("tenant_id"), count(tenant.asterisk()))
    .from(tenant)
    .leftJoin(profile).on(tenant.column("id").eq(profile.column("tenant_id")))
    .innerJoin(user).on(profile.column("id").eq(user.column("profile_id")))
    .where(
        tenant.column("id").in("id").or(tenant.column("name").eq("name"))
    )
    .and(tenant.column("active").isFalse().or(tenant.column("active").isNull()))
    .and(profile.column("name").like("prefix"))
    .groupBy(user.column("tenant_id")).having(user.column("active").isTrue())
    .orderBy(profile.column("name").asc(), tenant.column("date_registered").desc())
    .limit(10, 5)
    .build();

The output of the above code will be:

SELECT u.tenant_id, COUNT(t.*)
FROM schema1.tenant AS t
  LEFT JOIN schema2.profile AS p ON t.id = p.tenant_id
  INNER JOIN schema3.user AS u ON p.id = u.profile_id
WHERE 1=1
  AND (t.id IN :id OR t.name = :name)
  AND (t.active IS false OR t.active IS null)
  AND p.name LIKE :prefix
GROUP BY u.tenant_id HAVING u.active IS true
ORDER BY p.name ASC, t.date_registered DESC
LIMIT 10 OFFSET 5;

Note	PREPARE YOUR STATEMENT After generating the SQL string, make sure to sanitize it before executing it.

How do I contribute to this project?

You can contribute developing this project by checking this out and make a pull request to merge your changes.

Make sure you have the minimum setup to contribute to this project:

• Java JDK 17

• Git CLI

Please make sure you follow the standard coding patterns being used throughout this project.

• Make sure to only expose classes that are necessary for the user to be used in their project. Mark your class as package private

• Do the same for the methods by exposing (marking as public) methods that are needed at the user level

• Use the Step-Builder pattern if necessary

• Follow standard Java naming conventions

License

This project is licensed under the MIT License - see the LICENSE.txt file for details.