Solon Data SQL: Three Layers of Data Access Without the Bloat

Every Java framework eventually faces the data access question. Spring has JdbcTemplate, MyBatis-Plus, and JPA. Quarkus has Panache. But most of these come with heavy assumptions — you either go all-in on one ORM or spend days wiring multi-datasource configs.

Solon takes a different approach. Instead of bundling one "blessed" way to access data, it provides a three-layer scaffolding that lets you choose how much framework you want — from raw SQL to full ORM to distributed sharding — all within the same app.yml, without swapping dependencies or rewriting architecture.

Let me walk through each layer with real code.

---

The Foundation: solon-data

Before any SQL runs, you need the basics: transactions, caching, and datasource management. This is what solon-data provides. It's a 0-dependency base that the SQL plugins build on.

Transaction support — declarative and programmatic:

// Declarative
@Transaction
public void transfer(long fromId, long toId, double amount) {
    accountMapper.debit(fromId, amount);
    accountMapper.credit(toId, amount);
}

// Programmatic
TranUtils.execute(new TransactionAnno(), () -> {
    accountMapper.debit(fromId, amount);
    accountMapper.credit(toId, amount);
});

Cache annotations — drop-in for any service layer:

@Cache(key = "user_${id}", seconds = 300)
public User getUser(long id) { ... }

@CachePut(key = "user_${id}", seconds = 300)
public User updateUser(User user) { ... }

@CacheRemove(key = "user_${id}")
public void deleteUser(long id) { ... }

The caching is interface-based (CacheService), so swapping from local cache to Redis means changing one config line, not the code.

Datasource routing — AbstractRoutingDataSource gives you the building block for master-slave or multi-tenant scenarios without pulling in a heavy sharding middleware.

---

Layer 1: SqlUtils — Raw SQL, Polished

For projects that prefer direct SQL — or have complex queries that don't fit an ORM — solon-data-sqlutils is the answer. At 20KB, it's lighter than a single Spring Boot auto-config class.

Configure a datasource in app.yml:

solon.dataSources:
  db1!:
    class: "com.zaxxer.hikari.HikariDataSource"
    jdbcUrl: jdbc:mysql://localhost:3306/rock
    driverClassName: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456

The ! suffix means "also register as the default typed bean." Now inject SqlUtils directly:

@Component
public class UserService {
    @Inject
    SqlUtils sqlUtils;  // default datasource

    @Inject("db2")
    SqlUtils sqlUtils2; // named datasource
}

Query operations:

// Single value
Long count = sqlUtils.sql("select count(*) from users").queryValue();

// Row to POJO
User user = sqlUtils.sql("select * from users where id=?", 11)
                     .queryRow(User.class);

// List of rows
List<User> list = sqlUtils.sql("select * from users limit 10")
                          .queryRowList(User.class);

// Streaming (memory-safe for large datasets)
try (RowIterator<User> it = sqlUtils
        .sql("select * from users")
        .queryRowIterator(100, User.class)) {
    while (it.hasNext()) {
        process(it.next());
    }
}

Dynamic queries with SqlBuilder:

public List<User> search(String name, Integer age, String city) {
    SqlBuilder sb = new SqlBuilder();
    sb.append("select * from users where 1=1 ")
      .appendIf(name != null, "and name like ? ", name + "%")
      .appendIf(age != null, "and age = ? ", age)
      .appendIf(city != null, "and city = ? ", city);

    return sqlUtils.sql(sb).queryRowList(User.class);
}

Batch operations:

List<Object[]> batch = new ArrayList<>();
batch.add(new Object[]{1, "Alice", 100});
batch.add(new Object[]{2, "Bob", 200});

int[] rows = sqlUtils
    .sql("insert into accounts(id, name, balance) values(?,?,?)")
    .params(batch)
    .updateBatch();

SqlUtils is ideal for:

• SQL-centric projects where you want full control
• High-performance paths where ORM overhead matters
• Dynamic reporting queries with unpredictable WHERE clauses
• Use as an internal engine for custom data processing

---

Layer 2: MyBatis-Solon — Full ORM, Multi-Source First

When you need proper ORM with mapping, caching, and pagination, mybatis-solon-plugin integrates MyBatis into Solon's container — with first-class multi-datasource support built into the design philosophy.

The key insight: Solon treats multi-datasource as the default, not an edge case. Each datasource gets its own MyBatis configuration block.

solon.dataSources:
  db1!:
    class: "com.zaxxer.hikari.HikariDataSource"
    jdbcUrl: jdbc:mysql://localhost:3306/orders
    username: root
    password: 123456
  db2:
    class: "com.zaxxer.hikari.HikariDataSource"
    jdbcUrl: jdbc:mysql://localhost:3306/analytics
    username: root
    password: 123456

mybatis.db1:
  mappers:
    - "com.myapp.order.**.mapper"
    - "classpath:com/myapp/order/**/mapper.xml"
  configuration:
    mapUnderscoreToCamelCase: true

mybatis.db2:
  mappers:
    - "com.myapp.analytics.**.mapper"
  typeAliases:
    - "com.myapp.analytics.model"

Notice: each datasource's MyBatis config is namespaced under its own key (mybatis.db1, mybatis.db2). This is not extra complexity — it's explicit clarity about which mapper belongs to which database.

In code, the @Db annotation wires everything:

@Component
public class OrderService {
    @Db("db1")
    OrderMapper orderMapper;

    @Db("db2")
    AnalyticsMapper analyticsMapper;

    public void process(long orderId) {
        Order o = orderMapper.getById(orderId);
        analyticsMapper.recordView(o.getUserId(), orderId);
    }
}

Need pagination? Add mybatis-pagehelper-solon-plugin as a dependency — it integrates PageHelper under Solon's context without extra config.

---

Bonus: Dynamic Datasource for Read-Write Split

For master-slave or active-standby setups, solon-data-dynamicds gives you runtime-switchable datasources with zero code change to your mappers.

Configure a DynamicDataSource:

solon.dataSources:
  db_user:
    class: "org.noear.solon.data.dynamicds.DynamicDataSource"
    strict: true
    default: "db_user_1"
    db_user_1:
      dataSourceClassName: "com.zaxxer.hikari.HikariDataSource"
      jdbcUrl: jdbc:mysql://localhost:3306/users_master
      username: root
      password: 123456
    db_user_2:
      dataSourceClassName: "com.zaxxer.hikari.HikariDataSource"
      jdbcUrl: jdbc:mysql://localhost:3307/users_slave
      username: root
      password: 123456

Switch at runtime:

// Annotation-based
@DynamicDs("db_user_1")
public List<User> getActiveUsers() { ... }

// Programmatic
DynamicDsKey.with("db_user_2", () -> {
    return userMapper.reportQuery();
});

Your UserMapper code stays unchanged — only the datasource context changes. This is the power of Solon's layered design: the SQL and the connection strategy are decoupled.

---

The Ecosystem: 24+ ORM Plugins

Beyond SqlUtils and MyBatis, Solon's Data SQL ecosystem covers 24+ plugins adapted for different ORM preferences:

Category	Plugins
Raw SQL	SqlUtils (20KB), RxSqlUtils (R2DBC)
MyBatis family	MyBatis, MyBatis-Plus, MyBatis-Flex, Plus-Join, FastMyBatis, Xbatis, Mapper
Chinese ORMs	BeetlSQL, DbVisitor, Easy-Query, Sagacity-Sqltoy, AnyLine, Wood, ActiveRecord
JPA	Hibernate (javax), Hibernate (jakarta)
Others	Bean Searcher, Auto Table, Sorghum DDL
Advanced DS	DynamicDS, ShardingDS (ShardingSphere v5)

All of them share the same solon.dataSources configuration model and @Db injection pattern. Switching from MyBatis to BeetlSQL means changing the dependency and the mapper syntax — the datasource config, transaction management, and caching stay the same.

---

The Trade-offs (Honest)

No framework is perfect. Here's what Solon's data layer doesn't do:

• No built-in JPA-style auto-DDL — schema management is handled by your ORM plugin (Hibernate does it; SqlUtils users write migration scripts)
• No GraphQL — use graphql-solon-plugin for that
• No distributed transactions — seata-solon-plugin handles XA/AT for those who need it

But what it does well is give you one consistent configuration model across every data access style, so you're never locked into a single ORM's ecosystem. The same app.yml that configures a single HikariCP pool for SqlUtils can grow into a multi-source MyBatis + DynamicDS setup without restructuring your project.

---

Summary

Layer	Module	Best For
Foundation	solon-data	Transactions, caching, routing
Raw SQL	solon-data-sqlutils	Full control, dynamic queries, high performance
ORM	mybatis-solon-plugin (+ 23 others)	Mapping, pagination, team conventions
Advanced DS	solon-data-dynamicds / solon-data-shardingds	Read-write split, sharding

Solon's data layer philosophy is simple: you choose the abstraction; the configuration stays the same. Whether you're writing raw SQL for a reporting engine or wiring MyBatis mappers across three databases, the patterns are consistent, the annotations are familiar, and there's no single point of lock-in.

---

This article is based on Solon v4.0.3. All code examples are from the official documentation at solon.noear.org.

Comments

[Mads Hansen]

The consistent configuration model is attractive, but the multi-datasource examples need one boundary called out loudly: shared annotations do not imply shared atomicity. In process(), reading an order from db1 and writing analytics to db2 can leave partial state if the second operation fails; a local @Transaction can only guarantee the resource actually enlisted. If distributed transactions are intentionally excluded, I’d show the preferred consistency pattern beside the example—usually a transactional outbox in the source database, idempotent consumer keys, and reconciliation—so “multi-source first” is not mistaken for an implicit cross-database transaction.

Dynamic read/write routing also deserves executable semantics. After a write, what guarantees read-your-writes if the next annotated call selects a replica? How is routing context propagated across async tasks, reactive chains, and nested calls? Does strict: true fail closed when the key is missing, and what happens if a transaction tries to switch datasource midway? I’d add two-session tests for replica lag plus tests that assert datasource identity at every transaction boundary. The cache annotations create a similar contract: update the database and cache as two independent side effects, so eviction/update ordering and failure recovery need to be documented rather than hidden behind the common API. The layered design is strongest when it preserves these differences instead of making the APIs merely look interchangeable.

[Solon Framework]

Thanks for the thorough review, Mads — these are exactly the right questions for anyone considering multi-datasource setups.

On distributed transactions: You're right that the article's multi-datasource example showed cross-source writes without an explicit distributed TX boundary. Solon doesn't bundle a built-in distributed transaction; it delegates to Seata for XA/TCC via the solon-data-seata plugin. For most microservice boundaries we recommend the outbox + idempotent consumer pattern you mentioned — it's more practical at scale than 2PC. I should have called this out explicitly.

Read-your-writes & DynamicDS: This is a known gap. @dynamic("master") annotation on a service method pins it to a named cluster, but the per-request heuristic routing has no automatic read-after-write affinity. ThreadLocal-based routing context does not propagate across async boundaries or reactive streams automatically — users must set up their own propagation. When strict: true and the key is missing, it raises IllegalArgumentException; with strict: false (default) it falls back to the default datasource.

Cache interceptor ordering: @CachePut / @CacheRemove wrap around the @tran annotation in Solon's interceptor chain — so cache eviction runs after the transaction commits by default. For cache recovery on Redis/node failures, there's no built-in circuit breaker yet; that's on our roadmap.

Your closing point about preserving layer distinctions rather than making APIs look interchangeable is spot on — it's exactly why we kept SqlUtils, DynamicDS, and the ORM plugins as separate modules instead of hiding everything behind a single abstraction. Appreciate the feedback!