DEV Community

Z. Gao
Z. Gao

Posted on • Edited on

1 . SimpleDAO From Zero To Production: Single-table CRUD, Audit Fields & Soft Delete

#java #spring #springboot #simpledao

A practical walkthrough of SQL-First persistence: no XML, no Mapper interfaces, no generated queries.

I maintain a Java backend that handles ~1M requests/day. For persistence, we used to run MyBatis. The XML was manageable at first, then it wasn't. Dynamic conditions became <if> tag soup. A simple join query needed three files and two languages.

We switched to a simpler approach. Here's how it works for the most common case: single-table CRUD.

What You Need

  • Java 21+
  • Spring Boot (any 3.x)
  • A database (H2 for the demo, MySQL/PostgreSQL for production)

That's it. No XML parser, no code generator, no annotation processor.

The Project 

pom.xml
src/main/java/example/
  DemoApplication.java
  user/
    User.java        -- entity
    UserDao.java     -- data access
    UserCond.java    -- query conditions
src/main/resources/
  application.yml
  schema.sql
Enter fullscreen mode Exit fullscreen mode

Three Java files for a complete CRUD API.

The Entity 

@Data @Builder
@Table("sys_user")
public class User {
    @Id private Long id;
    private String name;
    private Integer age;
    private String email;
    // ... other fields

    // These four are auto-managed:
    private LocalDateTime createTime;
    private Long createBy;
    private LocalDateTime updateTime;
    private Long updateBy;
    private Byte dr;  // 0 = active, 1 = soft-deleted
}
Enter fullscreen mode Exit fullscreen mode

@Table maps to the database table. @Id marks the primary key (Snowflake ID by default). The audit fields and soft-delete marker are handled automatically—you don't set them in business code.

The DAO 

@Repository
public class UserDao extends BaseDao<User> {
    // Empty. All CRUD methods inherited.
}
Enter fullscreen mode Exit fullscreen mode

BaseDao provides save, saveBatch, update, delete, findById, list, page, count, exists. For single-table operations, this is all you need.

The Conditions 

@Getter @Setter @Builder
public class UserCond extends BaseCondition {
    private String name;
    private Integer ageMin;
    private Integer ageMax;
    private Byte dr;
    private Object[] ids;

    @Override
    protected void addCondition() {
        and("name LIKE", name, 3);   // 3 = %value%
        and("age >=", ageMin);
        and("age <=", ageMax);
        and("dr =", dr);
        in("id", ids);
    }
}
Enter fullscreen mode Exit fullscreen mode

Each and() line is one condition. If the parameter is null, the condition is skipped. No <if> tags, no OGNL, no XML.

The 3 in and("name LIKE", name, 3) means "wrap with % on both sides". 1 = suffix, 2 = prefix.

Running It 

@Autowired UserDao userDao;

public void demo() {
    // Insert
    User user = User.builder().name("John").age(25).email("john@example.com").build();
    userDao.save(user);  // id, createTime, createBy, dr auto-filled

    // Query by ID
    User found = userDao.findById(user.getId());

    // Paginated search
    Page<User> page = userDao.page(
        UserCond.builder().name("John").ageMin(20).ageMax(30).build()
    );

    // Update
    found.setAge(26);
    userDao.update(found);  // updateTime, updateBy auto-filled

    // Soft delete (dr=1, not DELETE)
    userDao.delete(found.getId());
}
Enter fullscreen mode Exit fullscreen mode

The SQL (from logs) 

-- save()
INSERT INTO sys_user (id,name,age,email,create_time,create_by,dr) 
VALUES (3679201737291333632,'John',25,'john@example.com','2026-04-10 13:14:10',1000,0)

-- page()
SELECT COUNT(1) FROM sys_user t 
WHERE t.name LIKE '%John%' AND t.age >= 20 AND t.age <= 30

SELECT t.id,t.name,t.age,t.email FROM sys_user t 
WHERE t.name LIKE '%John%' AND t.age >= 20 AND t.age <= 30 
LIMIT 0,10

-- delete() with dr field present
UPDATE sys_user t SET dr=1 WHERE id IN (3679201737291333632)
Enter fullscreen mode Exit fullscreen mode

The SQL in the logs is the SQL that runs. No hidden transformations, no proxy-generated queries.

What This Isn't

  • Not an ORM: We don't map object graphs or handle relationships automatically. For joins, you write the SQL.
  • Not type-safe SQL: If you misspell a column name, you find out at runtime. The tradeoff is transparency.
  • Not a replacement for everything: If you need complex caching, second-level cache, or distributed transactions, Spring has other tools.

What This Is

A thin bridge between Java and SQL. The framework handles:

  • Parameter collection and null-checking
  • Audit field auto-fill
  • Soft-delete logic
  • Pagination (count + limit)

You handle:

  • The SQL
  • The business logic
  • The optimization

Next

Episode 02: Multi-table joins with the same API → [link]

Full source: github.com/gzz2017gzz/simple-dao-demo

Top comments (2)

Collapse
 
buildbasekit profile image
buildbasekit •

The journey of a backend developer:

Write SQL ➜ Use ORM ➜ Fight ORM ➜ Write SQL again 😅

Collapse
 
sqlfirst_dev profile image
Z. Gao •

That’s the exact pain point SimpleDAO solves. Instead of being trapped between verbose raw JDBC and bloated ORM tag soup, it keeps full SQL control while automating repetitive trivial work like audit fields, soft delete and null condition filtering.
Also, multi-table join queries are where SimpleDAO really shines, you’ll see it clearly in my next article.