x-ream/sqli for Java SQL Query Construction and Repository Access

Why x-ream/sqli Splits Query Building From Persistence

x-ream/sqli addresses SQL composition inside Java applications, especially services that need object-shaped results, Map-shaped results, joins, updates, and pagination without placing every SQL fragment directly in repository code. It is intended for developers integrating SQL construction into an existing framework or project. The repository description calls it an ORM and SQL query builder, but the README qualifies that description: sqli is a SQL programming interface, not a self-contained application framework.

The project separates this role across four modules visible in the repository: `sqli-builder`, `sqli-core`, `sqli-dialect`, and `sqli-repo`. Its primary APIs are `QB`, `QB.X`, and `QrB`. `QB` builds queries intended to return mapped objects, while `QB.X` builds queries that return `Map` records and supports joins, aggregation, and dotted result keys. `QrB` builds update fields and conditions.

This design fits codebases that already provide configuration, connection management, transaction handling, and repository proxies. The README shows repository interfaces extending `BaseRepository<Foo>` or `RepositoryX`, along with Spring annotations such as `@Repository` and `@X.Mapping`. It does not describe sqli as a runnable database tool, command-line utility, migration system, or schema management service. A team seeking those capabilities would need another layer.

How QB and QB.X Construct Filtered Queries

The core query path begins with an entity or a Map-oriented query object, adds predicates through chained methods, and returns a built query object for a repository. The README gives this object-query example:

QB qb = QB.of(Order.class);
qb.eq("userId",obj.getUserId()).eq("status","PAID");
Q q = qb.build();
orderRepository.find(q);

Here, `QB.of(Order.class)` associates the query with `Order`. The two equality calls append the supplied conditions, and `build()` produces the `Q` value passed to `find`. Conditions with null values are not appended to SQL, according to the API notes. Predicate methods include `eq`, `ne`, `gt`, `gte`, `lt`, `lte`, `in`, `nin`, `isNull`, and `nonNull`.

Pattern matching is represented by several deliberately different methods. `like` adds both wildcard sides, `likeLeft` adds only the left wildcard, and `notLike` creates a negated pattern containing the value. The `x` method accepts a handwritten SQL fragment, while `and` and `or` accept nested builders.

`QB` also supports pagination and sorting through methods such as `sort` and `paged`. One paging example can ignore the total row count, select page 1 and 10 rows, or stop after row 10000 through `last`. Setting `last` takes precedence over `page`, which is important when reviewing generated pagination behavior.

Map Queries, Joins, and SQL Optimizations in QB.X

`QB.X` covers cases where object mapping is inconvenient or unavailable, especially joins that return columns from several sources. The documented example selects an identifier, filters a status, creates nested time and `OR` predicates, supplies a handwritten join clause, and applies paging:

QB.X qbx = QB.x();
qbx.resultKey("o.id");
qbx.eq("o.status","PAID");
qbx.and(sub -> sub.gt("o.createAt",obj.getStartTime()).lt("o.createAt",obj.getEndTime()));
qbx.or(sub -> sub.eq("o.test",obj.getTest()).or().eq("i.test",obj.getTest()));
qbx.from("FROM order o INNER JOIN orderItem i ON i.orderId = o.id");
qbx.paged(obj);
Q.X xq = qbx.build();
orderRepository.find(xq);

This path returns Map-form records. The `resultWithDottedKey` option can preserve dotted names such as `o.id` in the resulting keys instead of converting nested join output to JSON-like structure. Projection methods include `select`, `selectWithFunc`, `distinct`, `groupBy`, and `reduce`. Reduction supports operations represented by `ReduceType`, including `SUM`, and the README notes limited `Having` support after reduction.

Join construction has two levels. The simple `from(joinSql)` path accepts join SQL, but the README explicitly says it does not support `LEFT JOIN` with multiple `ON` conditions. The `fromBuilder` API provides structured joins, including `JOIN(LEFT)`, `.of`, and an `.on` callback for additional predicates.

The repository also describes automatic join optimization. Based on `froms` and `fromBuilder`, the framework removes a connection when neither conditions nor returned fields use that source. Intermediate joins can remain when a target join table is still required. Developers can disable this behavior with `withoutOptimization()`. Large `IN` lists are split into chunks of 500 values. `UNION` is explicitly unsupported.

Installing x-ream/sqli Through the x7 Spring Integration

The README does not provide a complete standalone installation procedure. It says that sqli must be integrated into an existing framework and points to the `x7/x7-spring-boot-starter` project for integration with `Spring-Boot` and `Spring-JdbcTemplate`. The only dependency fragment shown contains an ellipsis, so the README does not provide a complete version or the full Maven coordinates that can be copied into a production build.

<dependency>
     <groupId>io.xream.x7</groupId>
     <artifactId>x7-spring-boot-starter</artifactId>
     ....
</dependency>

The ellipsis means this is illustrative rather than an executable dependency declaration. A developer would need to consult the x7 project for the missing version and then confirm how the sqli modules are pulled into that integration. No command for building, testing, or publishing the repository is documented in the README.

For a first real use in an application already using x7, the README shows enabling the repository integration and defining an entity mapping. Both snippets are incomplete: the application example omits its body, and the Maven dependency omits its version.

@EnableX7Repostory  // code at x7/x7-spring-boot-starter
public class App{
main() 
....
}

The entity example is more concrete. `@X.Mapping("t_foo")` selects `t_foo` instead of the default table name, while `@X.Key` marks the identifier. Field names can also map through `@X.Mapping("full_name")`, with `fullName` documented as the default mapping for that field.

Repository Operations and QrB Update Conditions

The repository API separates read and write behavior. `BaseRepository` offers object-oriented operations including `in`, `find`, `list`, `get`, `getOne`, `create`, `createOrReplace`, `createBatch`, `refresh`, `refreshUnSafe`, and `remove`. `RepositoryX` provides Map-oriented reads such as `find`, `list`, `listPlainValue`, and `findToHandle` with a `RowHandler<Map<String,Object>>`.

There are constraints around identity and return values. The entity documentation says that without `@X.Key`, operations such as `get(id)` and `remove(id)` are unsupported. The single-record `create(Object)` operation does not return an auto-increment key because the framework includes its own ID generator. This matters for schemas and services that depend on database-generated identifiers.

`QrB` adds update predicates to the fields being changed. The README example uses an ID and the previous status as conditions while setting a new status:

orderRepository.refresh(
QrB.of(Order.class).refresh("status","PAYING").eq("id",1).eq("status","UN_PAID").build()
);

This is closer to an atomic conditional update than a simple field assignment, although the README does not state transaction isolation or describe race behavior. `refreshUnSafe` exists for updates that do not use the primary key. Its name clearly marks the trade-off, and callers must ensure the constructed predicate is sufficient to identify the intended records. The documentation does not add guards against accidentally broad updates.

Redis Cache Boundaries and Maintenance Costs

Secondary caching is implemented in the x7 integration rather than presented as a standalone sqli cache API. The README describes `@EnableX7L2Caching` and says the implementation uses `redis.multiGet`. Cache support covers selected `BaseRepository` reads: `in`, `find`, `list`, `get`, and `getOne`. Map-oriented operations listed as unsupported include `findX`, `listX`, and `listPlainValue`.

The documented limit is strict: cached calls should return no more than 20 records. If a cached query returns more than 20, caching should be disabled. Every project component that writes to the database must also enable secondary caching. That means cache consistency depends on deployment-wide adoption of the x7 cache integration, not merely adding one annotation to an isolated service. Redis availability, serialization rules, and eviction behavior are not documented in the README, so those areas require verification before adoption.

The project is not archived. Its last push was on 2026-07-24. The listed releases are more conservative: version `2.1.10` was released on 2024-05-29, following `2.1.9` on 2024-04-23 and `2.1.8` on 2024-03-23. The repository has received a later push than those releases, but the README does not explain whether unreleased changes are compatible with `2.1.10`. Upgrading therefore requires checking dependency versions in the x7 integration and reviewing changes between the adopted release and the current repository state.

The repository is licensed under `Apache-2.0`. That identifies the stated license, but this review does not interpret its legal obligations or provide legal advice. Teams still need to check the actual `LICENSE` file and their own compliance processes when redistributing the library.

Editorial conclusion

Adopt x-ream/sqli when a Java service already has a framework integration and needs typed query construction beside Map-based joins and repository operations. Do not choose it as a standalone ORM or for applications that require UNION or complete left-join condition coverage. Verify the Spring integration dependency, generated SQL, dialect support, and release compatibility before introducing it into production.

Frequently asked questions

Official sources

1. License: Apache-2.0
2. Project website
3. README
4. Releases
5. x-ream/sqli on GitHub