Practice project

20 CRUD & data-management patterns

Every pattern works on one table (article) in SQL Server. Use it in the app, open the SQL panel to see the statements it ran, then read the Java method that did it.

Java 17+ Spring Boot 3.5 Spring Data JPA · Hibernate 6 JdbcTemplate SQL Server 2022 Flyway Thymeleaf · Bootstrap 5

Open the articles Try the REST API

1 · Do it

Each card below says where the feature lives. Click Try it and use it on the demo articles.

2 · See the SQL

After every action, the SQL button at the top shows the exact statements sent to SQL Server.

3 · Read the code

Open the Java method named on the card (in src/main/java/com/crudlab) and change it. Activity log > Reset demo data gives you a fresh start.

CRUD basics

Insert / Create

CRUD basics

Saves a new row. The id comes from IDENTITY, the code from a SEQUENCE when you leave it empty.

Articles > New article > Save draft or Save & publish

Java ArticleService.create() -> repo.save(article)

SQL show the statement

INSERT INTO article (code, title, ...) VALUES (?, ?, ...);
-- SQL Server returns the new id (IDENTITY)

Try it

Update / Edit

CRUD basics

Changes the fields of one row. A hidden row_version stops you from overwriting someone else's save.

Open an article > Edit > Save changes

Java ArticleService.update() -> dirty checking, no save() call needed

SQL show the statement

UPDATE article SET title = ?, ..., row_version = ?
WHERE id = ? AND row_version = ?;

Try it

Delete (hard delete)

CRUD basics

Removes the row for good. It cannot be undone, so the page asks you to confirm.

Row menu > Delete permanently, or Trash > Delete permanently

Java ArticleService.hardDelete() -> repo.delete(article)

SQL show the statement

DELETE FROM article WHERE id = ? AND row_version = ?;

Try it

Safe delete

Soft delete

Safe delete

Hides the row by setting deleted = 1. The row stays in the table and shows up in Trash.

Row menu > Move to trash

Java ArticleService.softDelete()

SQL show the statement

UPDATE article SET deleted = 1, deleted_at = SYSDATETIME() WHERE id = ?;
-- every list adds: WHERE deleted = 0

Try it

Restore

Safe delete

Brings a soft-deleted row back by setting deleted = 0.

Trash > Restore

Java ArticleService.restore()

SQL show the statement

UPDATE article SET deleted = 0, deleted_at = NULL WHERE id = ?;

Try it

Copy & combine

Duplicate / Clone

Copy & combine

Copies a row into a new draft with a new code (CODE-COPY, CODE-COPY2 ...) and remembers the source.

Row menu > Duplicate

Java ArticleService.duplicate()

SQL show the statement

SELECT ... FROM article WHERE id = ?;
INSERT INTO article (..., source_id) VALUES (..., ?);

Try it

Merge

Copy & combine

Combines two rows into one: tags are joined, content appended, the second row goes to the trash with merged_into_id.

Tick two rows > Merge, or Tools > Merge

Java ArticleService.merge()

SQL show the statement

UPDATE article SET content = ?, tags = ?, ... WHERE id = @keep;
UPDATE article SET deleted = 1, merged_into_id = @keep WHERE id = @other;

Try it

Upsert

Copy & combine

One statement that inserts the row when the code is new and updates it when the code exists.

Tools > Upsert

Java UpsertService.upsert() -> JdbcTemplate + MERGE

SQL show the statement

MERGE article WITH (HOLDLOCK) AS t USING (SELECT ? AS code, ...) AS s ON t.code = s.code
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...
OUTPUT $action;

Try it

INSERT INTO article (..., status) VALUES (..., 'DRAFT');

Try it

Publish

Life cycle

Makes a row public: status = 'PUBLISHED' and the time it was published.

Row menu > Publish, or Save & publish

Java ArticleService.publish()

SQL show the statement

UPDATE article SET status = 'PUBLISHED', published_at = SYSDATETIME() WHERE id = ?;

Try it

Unpublish

Life cycle

Takes a published row offline again (status = 'UNPUBLISHED'); a draft simply stays a draft.

Row menu > Unpublish

Java ArticleService.unpublish()

SQL show the statement

UPDATE article SET status = 'UNPUBLISHED' WHERE id = ?;

Try it

Batch

Batch insert

Batch

Saves many rows in one transaction. Compare JDBC batching with JPA saveAll() and see the time.

Tools > Batch insert

Java BatchInsertService.insert() -> jdbc.batchUpdate(...) or repo.saveAll(...)

SQL show the statement

INSERT INTO article (code, title, ...) VALUES (?, ?, ...);  -- x N, sent in batches of 500

Try it

Batch update

Batch

Changes category, priority or status of all ticked rows with ONE UPDATE statement. Locked rows are skipped.

Tick rows > Edit selected

Java ArticleRepository.batchUpdate() -> JPQL bulk update

SQL show the statement

UPDATE article SET category = COALESCE(?, category), ...
WHERE id IN (?, ?, ?) AND locked = 0;

Try it

Batch delete

Batch

Moves all ticked rows to the trash, or deletes them for good, with one statement.

Tick rows > Move to trash / Delete permanently

Java ArticleRepository.batchSoftDelete() / batchHardDelete()

SQL show the statement

UPDATE article SET deleted = 1 WHERE id IN (...) AND locked = 0;
DELETE FROM article WHERE id IN (...) AND locked = 0;

Try it

Flags

Keeps important rows at the top of every list: ORDER BY pinned DESC comes first.

Click the pin icon on a row

Java ArticleService.setPinned()

SQL show the statement

UPDATE article SET pinned = 1, pinned_at = SYSDATETIME() WHERE id = ?;
SELECT ... ORDER BY pinned DESC, updated_at DESC;

Try it

Mark as favourite

Flags

A personal star. The Favourites tab lists them.

Click the star icon on a row

Java ArticleService.setFavourite()

SQL show the statement

UPDATE article SET favourite = 1 WHERE id = ?;

Try it

Mark as read

Flags

Unread rows are bold with a blue dot. Opening an article marks it as read; you can also mark it unread again.

Open an article, or Row menu > Mark as read / unread

Java ArticleService.setRead() / openAndMarkRead()

SQL show the statement

UPDATE article SET is_read = 1, read_at = SYSDATETIME() WHERE id = ?;

Try it

Lock record

Flags

A locked row cannot be edited, deleted, archived, published or merged until it is unlocked. Upsert and batch actions skip it.

Row menu > Lock

Java ArticleService.lock() + requireUnlocked() in every changing method

SQL show the statement

UPDATE article SET locked = 1, locked_at = SYSDATETIME(), locked_reason = ? WHERE id = ?;

Try it

How the project is organised

src/main/java/com/crudlab
├─ domain/     Article, ActivityLog (JPA entities), enums
├─ repo/       ArticleRepository (Spring Data + JPQL bulk updates)
│              ArticleSpecs (dynamic WHERE), ArticleCounts
├─ service/    ArticleService      features 1-9, 11-13, 15-20
│              UpsertService       feature 10 (MERGE)
│              BatchInsertService  feature 14 (batchUpdate)
├─ web/        ArticleController (pages), ArticleApiController (REST)
│              ToolsController, FeatureCatalog
├─ sql/        SqlTrace + Hibernate StatementInspector
└─ seed/       DemoDataService (demo articles, reset)
src/main/resources
├─ db/migration/V1__create_tables.sql   (Flyway)
├─ templates/  Thymeleaf pages
└─ static/     css + js

Rules worth remembering

One method = one transaction. @Transactional on the service commits all statements or none.
Lock checks live in the service (requireUnlocked), so the pages and the REST API follow the same rule.
Soft delete is a filter. Every list adds deleted = 0; Trash is just deleted = 1.
Bulk JPQL updates skip the entity: no @Version, no @PreUpdate, so the query sets row_version and updated_at itself.
Post / Redirect / Get. Every POST answers with a redirect, so F5 never repeats an action.
Parameters, never string concatenation. Every value is sent as ?, which stops SQL injection.

20 CRUD & data-management patterns

CRUD basics

Insert / Create

Update / Edit

Delete (hard delete)

Safe delete

Soft delete

Restore

Copy & combine

Duplicate / Clone

Merge

Upsert

Archive

Archive

Unarchive

Life cycle

Save draft

Publish

Unpublish

Batch

Batch insert

Batch update

Batch delete

Flags

Pin

Mark as favourite

Mark as read

Lock record

How the project is organised

SQL behind this page