A lightweight persistence framework based on Spring JdbcTemplate: preserves SQL expressiveness while reducing DAO boilerplate, helping Java projects write clean, maintainable data access logic faster.
GyJdbc is for projects that don't want a heavy ORM but are tired of writing DAO classes and SQL concatenation by hand. Built on top of JdbcTemplate, it provides JPA-like entity DAOs, a fluent SQL builder, Lambda field references, Criteria-based condition assembly, and multi-data-source binding with load balancing.
- Why GyJdbc
- When to Use GyJdbc
- Installation
- Quick Start
- EntityDao Common Capabilities
- Criteria: Build Dynamic Conditions More Comfortably
- SQL: Compose Complex Statements Like Writing SQL
- Multi-DataSource Support
- Testing
- Important Notes
- More Examples
- Project Philosophy
- License
- Lighter DAO layer: Generic CRUD, pagination, batch operations, and SQL queries are provided by
EntityDao— no more repetitive code in business DAOs. - SQL stays under control: SQL isn't hidden; it's written more safely and clearly via a fluent API.
- Near-native SQL expressiveness: Supports
select,insert,update,delete,join,union, subqueries, grouping, sorting, pagination, aggregate functions, and most other SQL scenarios. - Stabler field references: Use Lambda references like
TbUser::getNameto avoid typos in string field names. - Dynamic conditions made easy:
Criteriasupportswhere,and,or,in,like,between, nested conditions,xxxIfAbsent, and other common condition assembly patterns. - Built-in multi-data-source support: Bind data sources via annotations or DAO methods, and use load-balancing strategies within data-source groups.
- Low learning curve: The API follows SQL semantics, so developers familiar with SQL and Spring JdbcTemplate can pick it up quickly.
GyJdbc is a great fit for:
- Spring / Spring Boot projects that need a quick data access layer;
- SQL-centric business logic that shouldn't be constrained by complex ORM mapping rules;
- Scenarios requiring dynamic query conditions, pagination, or batch operations;
- Switching between primary/replica, read/write, or multi-tenant databases;
- Keeping JdbcTemplate's simplicity while cutting down repetitive DAO code.
If your project needs full object-relational management, complex entity state tracking, first-level caching, or automatic dirty checking, Hibernate / JPA may be a better choice. GyJdbc's philosophy is more direct: write less code, produce clearer SQL, and build a maintainable data access layer.
<dependency>
<groupId>io.github.springstudent</groupId>
<artifactId>GyJdbc</artifactId>
<version>13.0.0.RELEASE</version>
</dependency>Current version is based on Java 8+ and Spring JDBC 5.3.x.
Use @Table to declare the entity-to-table mapping, and pk to specify the primary key field.
import com.gysoft.jdbc.annotation.Table;
import java.util.Date;
@Table(name = "tb_user", pk = "id")
public class TbUser {
private String id;
private String name;
private String realName;
private String pwd;
private String email;
private String mobile;
private Date birth;
private Integer age;
private String career;
private Integer isActive = 0;
private Integer roleId;
// getter / setter
}Business DAOs extend EntityDao, and their implementations extend EntityDaoImpl.
import com.gysoft.jdbc.dao.EntityDao;
import com.gysoft.jdbc.dao.EntityDaoImpl;
import org.springframework.stereotype.Repository;
public interface TbUserDao extends EntityDao<TbUser, String> {
}
@Repository
public class TbUserDaoImpl extends EntityDaoImpl<TbUser, String> implements TbUserDao {
}import com.gysoft.jdbc.bean.Criteria;
import com.gysoft.jdbc.bean.Page;
import com.gysoft.jdbc.bean.PageResult;
import com.gysoft.jdbc.bean.SQL;
import java.util.Arrays;
import java.util.List;
public class UserService {
private TbUserDao tbUserDao;
public int createUser(TbUser user) throws Exception {
return tbUserDao.save(user);
}
public List<TbUser> queryActiveUsers() throws Exception {
return tbUserDao.queryWithCriteria(
new Criteria()
.where(TbUser::getIsActive, 1)
.in(TbUser::getName, Arrays.asList("zhouning", "yinhw"))
);
}
public PageResult<TbUser> pageUsers(int pageNo, int pageSize) throws Exception {
return tbUserDao.pageQueryWithCriteria(
new Page(pageNo, pageSize),
new Criteria().where(TbUser::getIsActive, 1)
);
}
public int updateEmail(String name, String email) throws Exception {
return tbUserDao.updateWithSql(
new SQL()
.update(TbUser.class)
.set(TbUser::getEmail, email)
.where(TbUser::getName, name)
);
}
}EntityDao<T, Id> covers most common data-access operations:
// Insert / Update / Delete
int save(T entity);
void batchSave(List<T> list);
void saveOrUpdate(T entity);
int saveAll(List<T> list);
int update(T entity);
void batchUpdate(List<T> list);
int updateWithSql(SQL sql);
int delete(Id id);
int batchDelete(List<Id> ids);
int deleteWithCriteria(Criteria criteria);
int deleteWithSql(SQL sql);
// Query by primary key
T queryOne(Id id);
Optional<T> queryOneOpt(Id id);
// Query with Criteria
List<T> queryAll();
List<T> queryWithCriteria(Criteria criteria);
T queryOne(Criteria criteria);
Optional<T> queryOneOpt(Criteria criteria);
PageResult<T> pageQuery(Page page);
PageResult<T> pageQueryWithCriteria(Page page, Criteria criteria);
long countWithCriteria(Criteria criteria);
boolean existsWithCriteria(Criteria criteria);
List<Id> queryIds(Criteria criteria);
// Query with SQL
<E> Result<E> queryWithSql(Class<E> type, SQL sql);
<E> List<E> queryListWithSql(Class<E> type, SQL sql);
<E> E queryOneWithSql(Class<E> type, SQL sql);
<E> Optional<E> queryOneWithSqlOpt(Class<E> type, SQL sql);
<E> PageResult<E> pageQueryWithSql(Page page, Class<E> type, SQL sql);
List<Map<String, Object>> queryMapsWithSql(SQL sql);
<K, V> Map<K, V> queryMapWithSql(SQL sql, ResultSetExtractor<Map<K, V>> extractor);
Integer queryIntegerWithSql(SQL sql);
long countWithSql(SQL sql);
boolean existsWithSql(SQL sql);
// DDL & special operations
int insertWithSql(SQL sql);
String createWithSql(SQL sql);
void drop();
void truncate();
void drunk(SQL sql);Criteria is ideal when query conditions come from page filters, API parameters, permission rules, or other dynamic sources.
// WHERE name = ?
new Criteria().where(TbUser::getName, "zhouning");
// WHERE name IN (?,?)
new Criteria().in(TbUser::getName, Arrays.asList("zhouning", "yinhw"));
// WHERE age < ? ORDER BY age DESC
new Criteria()
.lt(TbUser::getAge, 28)
.orderBy(new Sort(TbUser::getAge));
// WHERE age < ? AND (name LIKE ? OR realName LIKE ?)
new Criteria()
.lt(TbUser::getAge, 20)
.andCriteria(
new Criteria()
.like(TbUser::getName, "zhou")
.orLike(TbUser::getRealName, "周")
);
// Automatically skip the condition when the parameter is null — great for search forms
new Criteria()
.where(TbUser::getIsActive, 1)
.likeIfAbsent(TbUser::getName, keyword);When fields come from entity getters, use Lambda references directly to avoid string field name typos. andCriteria / orCriteria are for grouping a set of conditions inside parentheses.
// WHERE is_active = ? AND age >= ? AND (name LIKE ? OR real_name LIKE ?)
new Criteria()
.where(TbUser::getIsActive, 1)
.gte(TbUser::getAge, 18)
.andCriteria(c -> c
.like(TbUser::getName, "zhou")
.orLike(TbUser::getRealName, "周"));
// WHERE role_id IN(?,?,?) OR (email IS NULL AND mobile IS NOT NULL)
new Criteria()
.in(TbUser::getRoleId, Arrays.asList(1, 2, 3))
.orCriteria(c -> c
.isNull(TbUser::getEmail)
.isNotNull(TbUser::getMobile));Where lets you combine a set of local conditions in one chained expression. WhereParam is for passing arrays or lists of conditions to Opt.AND / Opt.OR for bulk assembly.
import com.gysoft.jdbc.bean.Opt;
import com.gysoft.jdbc.bean.Where;
import com.gysoft.jdbc.bean.WhereParam;
// WHERE name LIKE ? OR email LIKE ?
new Criteria()
.and(
Where.where(TbUser::getName).like("zhou")
.or(TbUser::getEmail).like("@example.com")
);
// WHERE is_active = ? AND (name LIKE ? OR email LIKE ?)
new Criteria()
.where(TbUser::getIsActive, 1)
.andWhere(
Where.where(TbUser::getName).like("zhou")
.or(TbUser::getEmail).like("@example.com")
);
// WHERE role_id IN(?,?,?) AND age >= ? AND mobile IS NOT NULL
new Criteria()
.and(
Opt.AND,
WhereParam.where(TbUser::getRoleId).in(Arrays.asList(1, 2, 3)),
WhereParam.where(TbUser::getAge).gte(18),
WhereParam.where(TbUser::getMobile).isNotNull()
);
// WHERE is_active = ? AND (role_id IN(?,?,?) OR age >= ? OR mobile IS NOT NULL)
new Criteria()
.where(TbUser::getIsActive, 1)
.andWhere(
Opt.OR,
WhereParam.where(TbUser::getRoleId).in(Arrays.asList(1, 2, 3)),
WhereParam.where(TbUser::getAge).gte(18),
WhereParam.where(TbUser::getMobile).isNotNull()
);
// WHERE is_active = ? AND (EXISTS(SELECT ...) AND age BETWEEN ? AND ?)
new Criteria()
.where(TbUser::getIsActive, 1)
.andWhere(
Where.where("ignored").exists(
new SQL().select("*").from("tb_role").where("tb_role.id", 1)
).and(TbUser::getAge).betweenAnd(18, 35)
);The SQL builder is for scenarios where you need explicit control over query fields, table joins, aggregations, subqueries, and update/insert/delete statements.
new SQL()
.select(TbUser::getName, TbUser::getEmail, TbUser::getMobile)
.from(TbUser.class)
.where(TbUser::getIsActive, 1);import static com.gysoft.jdbc.bean.FuncBuilder.countAs;
new SQL()
.select("age", countAs("age").as("num"))
.from(TbUser.class)
.groupBy(TbUser::getAge)
.orderBy(new Sort(TbUser::getAge));new SQL()
.update(TbUser.class)
.set(TbUser::getRealName, "Yuanlin")
.set(TbUser::getEmail, "13888888888@163.com")
.where(TbUser::getName, "Smith");new SQL()
.insertInto(TbAccount.class, "userName", "realName")
.values("test", "TestUser1")
.values("test2", "TestUser2");new SQL()
.delete()
.from(TbUser.class)
.gt(TbUser::getAge, 20);new SQL()
.select("u.name", "r.role_name")
.from("tb_user", "u")
.leftJoin("tb_role", "r")
.on("u.role_id", "r.id")
.where("u.is_active", 1);Join conditions can also be expressed via a callback, which supports Lambda field references and dynamic conditions.
// SELECT u.name, r.role_name, d.dept_name FROM tb_user u
// INNER JOIN tb_role r ON u.role_id = r.id AND r.status = ?
// LEFT JOIN tb_department d ON u.dept_id = d.id AND d.type = ?
// WHERE u.is_active = ? AND (u.name LIKE ? OR u.real_name like ?)
new SQL()
.select("u.name", "r.role_name", "d.dept_name")
.from("tb_user", "u")
.innerJoin("tb_role", "r", on -> on
.on("u.role_id", "r.id")
.and("r.status", "=", 1))
.leftJoin("tb_department", "d", on -> on
.on("u.dept_id", "d.id")
.andIfAbsent("d.type", "=", deptType))
.where("u.is_active", 1)
.andCriteria(c -> c
.like("u.name", keyword)
.orLike("u.real_name", keyword));
new SQL()
.select(Role::getName, Token::getTk)
.from(Role.class, "r")
.leftJoin(Token.class, "t", on -> on
.on(Role::getName, Token::getTk)
.and("t.status", "=", "active"));Complex filter conditions can be attached directly to the SQL builder — useful for report queries, list filtering, permission conditions, and similar scenarios.
new SQL()
.select("*")
.from("tb_user")
.and(
Where.where("is_active").equal(1)
.and("age").gte(18)
.or("name").like("zhou")
);
new SQL()
.select("*")
.from("tb_user")
.where("tenant_id", tenantId)
.andWhere(
Opt.OR,
WhereParam.where("role_id").in(Arrays.asList(1, 2, 3)),
WhereParam.where("email").like("@example.com"),
WhereParam.where("mobile").isNotNull()
);new SQL()
.select("*")
.from("tb_a")
.where("status", 1)
.unionAll()
.select("*")
.from("tb_b")
.where("status", 1);new SQL()
.select("*")
.from("BOOK")
.notIn(
"id",
new SQL()
.select("id")
.from("author")
.where("status", 1)
);// complex nest select sql is ok
new SQL().select("*").from(
new SQL().select("a.*").from(
new SQL().select("b.*").from(
new SQL().select("c.*").from(
new SQL().select("d.*").from(
new SQL().select("e.*").from("nestTable")
).where("key", "k1")
)
).like("keyLike","Lie").unionAll().select("f.*").from("f").isNotNull("notNull")
).where("condition", "1")
);import static com.gysoft.jdbc.bean.FuncBuilder.*;
new SQL()
.select(
countAs("id").as("total"),
maxAs("age").as("maxAge"),
jsonExtractAs("extra", "$.name").as("nameJson")
)
.from("tb_user")
.where("is_active", 1);SQL supports updating aliased tables, join updates, field-reference assignments, and subquery assignments. Use FieldReference when the right-hand side should be treated as a column or expression rather than a parameter value.
import com.gysoft.jdbc.bean.FieldReference;
// UPDATE tb_user u SET u.email = ?, u.real_name = ? WHERE u.name = ?
new SQL()
.update("tb_user", "u")
.set("u.email", "13888888888@163.com")
.set("u.real_name", "Yuanlin")
.where("u.name", "Smith");
// UPDATE tb_user u INNER JOIN tb_account a ON u.id = a.user_id
// SET u.email = a.email, u.mobile = a.mobile WHERE a.status = ?
new SQL()
.update("tb_user", "u")
.innerJoin("tb_account", "a")
.on("u.id", "a.user_id")
.set("u.email", new FieldReference("a.email"))
.set("u.mobile", new FieldReference("a.mobile"))
.where("a.status", 1);
// UPDATE tb_score SET (avg_score,max_score) = (SELECT ...)
new SQL()
.update("tb_score")
.set(
"(avg_score,max_score)",
new SQL()
.select("AVG(score)", "MAX(score)")
.from("tb_score_detail")
.where("student_id", studentId)
)
.where("student_id", studentId);Delete statements also support aliases, multi-table deletes, delete with joins, field-reference comparisons, and subquery conditions.
import com.gysoft.jdbc.bean.FieldReference;
// DELETE FROM tb_user WHERE age > ?
new SQL()
.delete()
.from("tb_user")
.gt("age", 60);
// DELETE u FROM tb_user u INNER JOIN tb_account a ON u.id = a.user_id
// WHERE a.status = ? AND u.is_active = ?
new SQL()
.delete("u")
.from("tb_user")
.as("u")
.innerJoin("tb_account", "a")
.on("u.id", "a.user_id")
.where("a.status", 0)
.and("u.is_active", 0);
// DELETE orders,items FROM orders,items
// WHERE orders.userid = items.userid AND orders.orderid = items.orderid AND orders.date <= ?
new SQL()
.delete("orders,items")
.from("orders,items")
.where("orders.userid", new FieldReference("items.userid"))
.and("orders.orderid", new FieldReference("items.orderid"))
.let("orders.date", "2000/03/01");
// DELETE FROM tb_user WHERE id NOT IN(SELECT ...)
new SQL()
.delete()
.from("tb_user")
.notIn(
"id",
new SQL()
.select("user_id")
.from("tb_order")
.where("status", "PAID")
);GyJdbc provides JdbcRoutingDataSource, which selects a data source by key or group. Groups support load-balancing strategies — useful for read/write splitting, multiple replicas, tenant databases, etc.
import com.gysoft.jdbc.multi.JdbcRoutingDataSource;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DatasourceConf {
@Bean(name = "primary")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.primary")
public HikariDataSource primary() {
return new HikariDataSource();
}
@Bean(name = "secondary")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public HikariDataSource secondary() {
return new HikariDataSource();
}
@Bean(name = "third")
@ConfigurationProperties(prefix = "spring.datasource.third")
public HikariDataSource third() {
return new HikariDataSource();
}
@Bean(name = "dataSource")
public DataSource dataSource(
@Qualifier("primary") DataSource primary,
@Qualifier("secondary") DataSource secondary,
@Qualifier("third") DataSource third) {
JdbcRoutingDataSource routingDataSource = new JdbcRoutingDataSource();
routingDataSource.setDefaultLookUpKey("primary");
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("primary", primary);
targetDataSources.put("secondary", secondary);
targetDataSources.put("third", third);
routingDataSource.setTargetDataSources(targetDataSources);
Map<String, String> dataSourceKeysGroup = new HashMap<>();
dataSourceKeysGroup.put("master", "primary");
dataSourceKeysGroup.put("slave", "secondary,third");
routingDataSource.setDataSourceKeysGroup(dataSourceKeysGroup);
return routingDataSource;
}
@Bean(name = "jdbcTemplate")
public JdbcTemplate jdbcTemplate(@Qualifier("dataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}import com.gysoft.jdbc.multi.BindPointAspectRegistar;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Import;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
@SpringBootApplication
@EnableAspectJAutoProxy(proxyTargetClass = true)
@Import(BindPointAspectRegistar.class)
public class SystemApp {
public static void main(String[] args) {
SpringApplication.run(SystemApp.class, args);
}
}import com.gysoft.jdbc.multi.BindPoint;
import com.gysoft.jdbc.multi.balance.RandomLoadBalance;
// Randomly select a data source from the slave group
@BindPoint(group = "slave", loadBalance = RandomLoadBalance.class)
public List<TbUser> queryFromSlave() throws Exception {
return tbUserDao.queryAll();
}
// Bind to a specific data source
@BindPoint(key = "secondary")
public int updateSecondary(TbUser user) throws Exception {
return tbUserDao.update(user);
}use the scoped API so the complete operation stays on one data source:
```java
import com.gysoft.jdbc.multi.DataSourceContext;
PageResult<TbUser> result = DataSourceContext.withDataSource(
"secondary",
() -> tbUserDao.pageQuery(page)
);
DataSourceContext.withDataSourceGroup(
"slave",
RoundRobinLoadBalance.class,
() -> tbUserDao.batchSave(users)
);Scopes may be nested. Leaving an inner scope restores the outer data source, and an exception still clears the binding.
When Spring transactions are used, the scope must wrap the transactional entry point so routing is established before the transaction obtains a connection:
DataSourceContext.withDataSource("secondary", transactionalService::execute);A transaction cannot change its physical data source after it has obtained a connection. @BindPoint runs before transaction advice, while atomic work across multiple data sources still requires a dedicated distributed transaction solution.
Note: bindings apply only to the current thread. Bindings from
@BindPoint/DataSourceContext.withDataSourceare stored in a thread-local variable and are not propagated to child threads. In asynchronous scenarios (@Async,CompletableFuture, thread-pool tasks), a child thread cannot see the outer binding, and its database operations fall back to the default data source (defaultLookUpKey). To target a data source inside an async task, bind explicitly within the child thread — pass the key/group as an argument and callDataSourceContext.withDataSource(...)inside the child thread.
Data source resolution priority:
EntityDao.bindXxx > @BindPoint on method > @BindPoint on class > JdbcRoutingDataSource.defaultLookUpKey
GyJdbc ships with two layers of tests. Both run with plain mvn test — no Docker or external database is required.
Verify that the builders produce the expected SQL string and parameters.
CSqlTest.java— SQL builder syntax: select/insert/update/delete, join, union, subqueries, aggregate functions, create/truncate/drop, and more.CriteriaTest.java— dynamicCriteriacondition assembly.
Run the DAO against a real in-memory H2 database in MySQL compatibility mode, covering behaviors that only surface against a real database.
AbstractJdbcIT.java— base class: H2 connection (MODE=MySQL), table DDL, reflectivejdbcTemplateinjection.EntityDaoImplIT.java— full DAO behavior: entity mapping, CRUD, batch operations, pagination, criteria queries, join/union/subquery,xxxIfAbsent, and DDL operations.JdbcRoutingDataSourceIT.java— programmatic multi-data-source routing and master/slave data isolation.
mvn test # run everything, including the *IT integration tests
mvn -Dtest=CSqlTest test # SQL-generation unit tests only
mvn -Dtest=EntityDaoImplIT test # H2 integration tests only@Column(name) takes effect on the save / update side (the SQL uses name as the column). On the query side, entity mapping is handled by Spring's BeanPropertyRowMapper, which does not read the @Column annotation.
If @Column(name) and the Java property name do not convert to the same column name via the camelCase/underscore rule (e.g. property email with @Column(name = "email_addr")), the field is persisted on save but read back as null on query — causing save/query inconsistency. Names that convert cleanly (e.g. emailAddr ↔ email_addr) are consistent.
Keep @Column(name) convertible to/from the property name, or provide a custom RowMapper when you need a non-convertible column name.
Bindings from @BindPoint / DataSourceContext.withDataSource are stored in a ThreadLocal and are not propagated to child threads. In async scenarios (@Async, thread pools), a child thread cannot see the outer binding and its DB operations fall back to the default data source. To target a data source inside an async task, bind explicitly within the child thread.
- SQL-generation tests: CSqlTest.java, CriteriaTest.java
- H2 integration tests: EntityDaoImplIT.java, JdbcRoutingDataSourceIT.java
- Sample projects:
GyJdbc isn't meant to replace all ORMs, nor is it about hiding SQL. It's a practical SQL assistant:
- Simple CRUD →
EntityDao; - Dynamic queries →
Criteria; - Complex SQL → the
SQLbuilder; - Multi-data-source routing →
JdbcRoutingDataSourceand@BindPoint.
You stay in control of your SQL — but you no longer waste time on repetitive DAO code, string concatenation, or scattered data-source-switching logic.
GyJdbc is open source under the Apache License 2.0.