Querier: Type Safe Java SQL Query Builder

Querier, a type-safe Java SQL query builder using method references for compile-time safety. Querier helps teams build readable SQL queries with support for analytics and reporting-heavy use cases.

Installation

Maven

<dependency>
    <groupId>com.aytmatech</groupId>
    <artifactId>querier</artifactId>
    <version>0.1.0</version>
</dependency>

Gradle

implementation("com.aytmatech:querier:0.1.0")

Build a query

Select select = Select.builder()
    .select(Order::getId)
    .select(Order::getTotal)
    .from(Order.class)
    .where(Condition.eq(Order::getStatus, OrderStatus.PAID))
    .build();

Select.SqlAndParams sp = select.toSqlAndParams();
// SQL: SELECT orders.id, orders.total FROM orders WHERE orders.status = :param0

Execute the query

Using EntityManager

EntityManager em = ...;
Select.SqlAndParams sp = select.toSqlAndParams();
Query nativeQuery = em.createNativeQuery(sp.sql(), Tuple.class);
sp.params().forEach(nativeQuery::setParameter);
List<?> rows = nativeQuery.getResultList();

Using Spring NamedParameterJdbcTemplate

Select.SqlAndParams sp = select.toSqlAndParams();
List<Map<String,Object>> rows = namedJdbc.queryForList(sp.sql(), sp.params());

Using Spring JdbcTemplate

Select.PositionalSqlAndParams sp = select.toPositionalSql();
List<Map<String,Object>> rows = jdbc.queryForList(sp.sql(), sp.params().toArray());

Plain JDBC PreparedStatement

Select.PositionalSqlAndParams sp = select.toPositionalSql();
PreparedStatement ps = connection.prepareStatement(sp.sql());
for (int i = 0; i < sp.params().size(); i++) {
    ps.setObject(i + 1, sp.params().get(i));
}
ResultSet rs = ps.executeQuery();

Using jOOQ

Select.PositionalSqlAndParams sp = select.toPositionalSql();
Result<Record> result = dsl.resultQuery(sp.sql(), sp.params().toArray()).fetch();

Using Hibernate Native Query

Select.SqlAndParams sp = select.toSqlAndParams();
Query query = session.createNativeQuery(sp.sql(), Tuple.class);
sp.params().forEach(query::setParameter);
List<?> results = query.getResultList();

Using R2DBC DatabaseClient

Select.SqlAndParams sp = select.toSqlAndParams();
DatabaseClient.GenericExecuteSpec spec = client.sql(sp.sql());
for (var entry : sp.params().entrySet()) {
    spec = spec.bind(entry.getKey(), entry.getValue());
}
Flux<Map<String,Object>> rows = spec.fetch().all();

Using Vert.x SQL Client

Select.IndexedSqlAndParams sp = select.toIndexedSql();
Tuple tuple = Tuple.tuple();
sp.params().forEach(tuple::addValue);
client.preparedQuery(sp.sql()).execute(tuple);

Logging / Debugging

String plainSql = select.toPlainSql();
log.debug("Executing query: {}", plainSql);
// WARNING: Not safe for execution - use for logging only

Complex query examples

Window functions

Select select = Select.builder()
    .select(Order::getId)
    .select(Order::getTotal)
    .select(WindowFunction.of(Aggregate.sum(Order::getTotal))
        .partitionBy(Order::getCustomerId)
        .orderBy(OrderBy.asc(Order::getCreatedAt))
        .as("running_total"))
    .from(Order.class)
    .build();
// SQL: SELECT orders.id, orders.total, 
//      SUM(orders.total) OVER (PARTITION BY orders.customer_id ORDER BY orders.created_at ASC) AS running_total
//      FROM orders

CTEs (Common Table Expressions)

Select cte = Select.builder()
    .select(Order::getCustomerId)
    .select(Aggregate.sum(Order::getTotal).as("total_spent"))
    .from(Order.class)
    .groupBy(Order::getCustomerId)
    .build();

Select main = Select.builder()
    .with("customer_totals", cte)
    .select(Expression.raw("customer_totals.customer_id"))
    .select(Expression.raw("customer_totals.total_spent"))
    .from(Expression.tableRef("customer_totals"))
    .where(Condition.gt(Expression.raw("customer_totals.total_spent"), 5000))
    .build();
// SQL: WITH customer_totals AS (
//        SELECT orders.customer_id, SUM(orders.total) AS total_spent 
//        FROM orders GROUP BY orders.customer_id
//      )
//      SELECT customer_totals.customer_id, customer_totals.total_spent 
//      FROM customer_totals WHERE customer_totals.total_spent > :param0