# SqlToy keeps your SQL readable and moves the dialect work into the framework

> SqlToy is a Java ORM that combines JPA style object CRUD with SQL first querying, and its distinguishing features are concrete: dynamic conditions written as inline markers rather than if blocks, one query that runs across 25 databases with automatic function adaptation, a four layer pagination strategy, a cache translate step that removes dictionary joins, and an updateFetch operation that updates and returns a row under a lock. It is open source and has been in production since 2008.

**sagframe/sagacity-sqltoy** — Java ORM alternative to MyBatis & Hibernate: JPA-style CRUD + SQL-first queries. Dynamic conditions without <if> blocks; one SQL across 25 databases (auto dialect & function adaptation); self-optimizing pagination; cache translate (no dictionary JOINs); race-proof updateFetch ledgers; sharding, multi-tenancy, data masking. In production since 2008.

- Repository: https://github.com/sagframe/sagacity-sqltoy
- Website: https://sagframe.github.io/sqltoy-docs/#/en/
- Stars: 1,150 · Forks: 189
- Language: Java
- License: Apache-2.0
- Published: 2026-10-19 · Updated: 2026-10-19 · Language: en
- Canonical page: https://hysenlabs.com/projects/sagframe-sagacity-sqltoy

## Dynamic conditions without the if tag noise

The problem SqlToy is positioned against is the one every MyBatis user recognises: optional parameters produce a wall of if blocks, and the SQL stops being something you can paste into a database client and run.

The mechanism is a marker inside the SQL itself. A condition written as a parameter marker is joined into the statement only when its parameter is set, so the same text is both the query and the conditional:

```xml
<sql id="staff_search">
    select s.STAFF_ID, s.STAFF_NAME, s.ORGAN_ID
    from sqltoy_staff_info s
    where s.STATUS = 1
    #[and s.ORGAN_ID in (:organIds)]
    #[and s.STAFF_NAME like :staffName]
</sql>
```

The Java side passes a map of values, and the framework assembles the statement:

```java
// staffName = "Chen" -> sqltoy wraps it as '%Chen%' automatically
List<StaffVO> staff = lightDao.find("staff_search",
        MapKit.keys("staffName").values("Chen"), StaffVO.class);
```

Two details in that snippet matter more than they look. The like parameter is wrapped with the percent signs automatically, so the caller does not have to remember to add them, which removes a class of bug. And the query lives in an XML file identified by an id, so it is a named artefact that can be reviewed, diffed and reused, rather than a string concatenated in Java code.

The claim that the SQL stays copy-paste runnable in any database client is the practical payoff, because the whole point of a SQL first approach is that a person can read the statement and predict what it will do.

## Twenty five databases, and the adaptation layer that makes it work

The portability claim is the one that differentiates SqlToy from an ORM that only speaks to a handful of databases, and the mechanism behind it is described in the feature list as automatic function adaptation, per-dialect sqlId variants, and replay-against-other-databases verification in tests.

The function adaptation is the practical part. A query that uses a null-coalescing function is spelled differently across engines, and the README gives the example of nvl being rewritten to coalesce or ifnull depending on the target. So the same statement can be issued against a MySQL production database and an Oracle one, and the framework rewrites the parts that differ.

The per-dialect sqlId variants are the escape hatch for the cases that cannot be rewritten automatically, where a query has a different name or a different shape on each engine. And the replay verification is what makes the claim testable: the test suite runs the same query against other databases and checks the results agree, rather than trusting the rewrite to be right.

The supported list is long, and it is worth reading as a map of where the project expects to be deployed. The relational databases include mysql, oracle, postgresql, sqlserver, db2, sap hana, sqlite, h2, tidb, oceanbase, polardb, dm, kingbase, gaussdb, opengauss, mogdb, vastbase, oscar, highgo and stardb. The analytical list covers clickhouse, doris, starrocks, greenplum, impala with kudu, and tdengine. And the document side includes elasticsearch and mongodb, so the same query layer reaches beyond the relational case.

The concentration of domestic Chinese databases in that list, together with the Chinese database migration article the README points to, tells you what deployment scenario the project was built for.

## Pagination is four strategies, and the count is the expensive part

The pagination story is described as four-layer optimized pagination, and unpacking it explains most of the performance claims in the project.

The four layers are intelligent count rewriting, cached counts for identical filters, page-first joins marked with the @fast() annotation, and parallel count plus fetch.

Counting is where list pages usually die, because a count query over a joined and filtered dataset can cost more than the page of rows it is counting. Count rewriting addresses that by changing the shape of the count statement rather than counting the rows the page query would return. Cached counts handle the case where a filter combination is requested repeatedly, which is what a list view does when a user pages back and forth.

The @fast() annotation is the interesting one, because it changes the order of work rather than the cost of a query. A page-first join brings the rows of the current page together first and joins the rest afterwards, so the expensive part of the join is not paid for rows that the page throws away.

Parallel count and fetch is the last layer, running the two statements at the same time, which trades a little server load for a lower latency for the user.

For an adopter, the practical point is that this is not one strategy you toggle. It is a stack, and the layers apply to different query shapes, so the documentation is where you find out which of them apply to a given screen.

## Cache translate removes a join from every list page

Cache translate is described as resolving codes to names in memory after the query returns, so that you can skip the dictionary JOIN on every list page.

That pattern is everywhere in Chinese enterprise systems, where a status column stores a code and the display name lives in a dictionary table, and where the list query therefore joins the dictionary, and where the dictionary join is the reason the count query is slow and the page query is slow. Doing the resolution in the application after the fetch means the database does less work, and it means the count no longer has to consider the joined dictionary at all.

The feature list adds a detail that shows how seriously this is taken, with Cache Translate including an extra-large FIFO dynamic cache. So there is a cache in front of the dictionary lookup, with a bounded size, which is the arrangement you need when the dictionary is large enough to be worth caching and volatile enough that an unbounded cache would be a memory problem.

The trade is the usual one for an application level lookup. The resolution happens after the fetch, so a page of ten thousand rows resolves ten thousand codes in the application rather than in the database, and the mapping has to be loaded into memory first. For a small dictionary that is clearly the right trade, and the FIFO cache is what keeps it reasonable for a large one.

## updateFetch is the primitive for ledgers and counters

The race-proof ledgers feature is described as updateFetch locking the row, updating against its current values and returning the result in a single database interaction.

That single sentence describes a lot of application bugs avoided. The usual way to increment a counter or move a queue entry is to read the value, compute the new one in Java, and write it back, and between the read and the write another transaction can change the same row, so the write overwrites it. updateFetch moves the whole thing into the database, under a lock, and returns the updated row, so the application never works from a stale copy.

The same mechanism covers a broader set of cases than counters. The features table lists elastic update, updateFetch and updateSaveFetch among the object operations, so the read-modify-write primitive is available in several shapes, and cascades and tree-table routing sit alongside it.

For a team that has had a double-spend or a lost increment bug in a ledger, this is the part of the framework that justifies a look on its own, because it is the one thing an in-memory ORM cannot do for you and that hand written JPA code usually gets wrong.

## The enterprise surface is wide, and the packaging is layered

The remaining feature categories are what a Chinese enterprise system tends to need, and the feature table lists them without qualification: sharding, multi-tenancy, data masking and field encryption, quickvo code generation, and GraalVM AOT, alongside stored procedures, streaming query, pivot analysis with row to column and column to row transforms, grouped totals and period comparisons.

The distribution is layered by framework rather than by feature, and the README lists the artefacts explicitly. There is a Spring Boot starter, sagacity-sqltoy-spring-starter, which is the one to use in a Spring application, a traditional Spring adapter, sagacity-sqltoy-spring, a plain artifact, sagacity-sqltoy, for applications that do not want a framework, and a Solon adapter, sagacity-sqltoy-solon-plugin. The current version of the starter is 6.0.7.

The legacy line is also explicit. A JDK 8 line is maintained for legacy systems, at 5.6.96.jre8, which is described as final, and it works with the quickvo maven plugin. That final marker is a signal, since it says the 8 line is not getting further work.

The 6.0 upgrade is not free, and the README links a guide for it. The changes called out are the parallQuery to parallelQuery rename, a changed DBProfile signature of the connection callback, and package structure adjustments. Those are the two kinds of change that break a build silently, the second one especially, because a callback with the wrong shape fails at runtime rather than at compile time in some configurations.

The release cadence in the documentation snapshot is worth noting as well, with 6.0.5, 6.0.6 and 6.0.7 published on three consecutive days in October 2026, and the last push on 2026-10-15, so the project is shipping actively and recently.

## Conclusion

SqlToy suits a Java team on Spring Boot that wants to write SQL it can paste into a database client, that may face a migration between database vendors, and that has performance sensitive list pages. It does not suit a team that wants the JPA specification implemented, since the object side is a convenience layer rather than a portable persistence API, and the SQL first half means the query surface is the product. Verify first which dialect your current database is in the supported list, what the upgrade guide changes if you are on the 5.6 line, and whether the pagination and cache translate behaviour matches what your current list endpoints do by hand.

## FAQ

### What is SqlToy and what does it replace?

It is a Java ORM positioned as an alternative to MyBatis and Hibernate, combining JPA style object CRUD with SQL first querying for applications that need both convenient object access and the full power of SQL. It is open source and has been in production since 2008, running on JDK 17 or newer with Spring Boot 3 and 4, with a JDK 8 line maintained for legacy systems.

### How do dynamic conditions work in SqlToy?

You write the condition inline in the SQL, for example #[and s.ORGAN_ID in (:organIds)], and it is joined into the statement only when its parameter is set. There are no if or foreach blocks, and the resulting SQL stays copy-paste runnable in a database client.

### Which databases does SqlToy support?

The README lists relational databases including mysql, oracle, postgresql, sqlserver, db2, sap hana, sqlite, h2, tidb, oceanbase, polardb, dm, kingbase, gaussdb, opengauss, mogdb, vastbase, oscar, highgo and stardb, analytical databases including clickhouse, doris, starrocks, greenplum, impala with kudu and tdengine, and elasticsearch and mongodb for documents. One SQL is adapted automatically across them, with function rewriting such as nvl to coalesce or ifnull.

### What does updateFetch do in SqlToy?

It locks the row, updates it against its current values and returns the result in a single database interaction, which is the basis of the race-proof ledger support.

### Which SqlToy artifact should I depend on?

For a Spring Boot application the README gives com.sagframe:sagacity-sqltoy-spring-starter at version 6.0.7. There is also sagacity-sqltoy-spring for traditional Spring, sagacity-sqltoy for plain use without a framework, and sagacity-sqltoy-solon-plugin for Solon, plus a separate legacy JDK 8 line at 5.6.96.jre8 described as final.

## Sources

- [License: Apache-2.0](https://github.com/sagframe/sagacity-sqltoy/blob/6.0/LICENSE)
- [Project website](https://sagframe.github.io/sqltoy-docs/#/en/)
- [README](https://github.com/sagframe/sagacity-sqltoy/blob/6.0/README.md)
- [Releases](https://github.com/sagframe/sagacity-sqltoy/releases)
- [sagframe/sagacity-sqltoy on GitHub](https://github.com/sagframe/sagacity-sqltoy)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/sagframe-sagacity-sqltoy