SQL Server · CrudLabDB

All articles

Every article that is not archived and not in the trash. Tick rows to work on many at once.

Batch insert Upsert New article
0 selected
Article Category Priority Status Updated
ART-1005 Soft delete vs hard delete
Soft delete only sets deleted = 1, so the row can be restored; hard delete removes it.
soft deletetrashrestore
 Database Design High Published 09 Nov 2026 20:54
  • Open
  • Edit
  • Merge with…
ART-1002 Connecting Spring Boot to SQL Server
The JDBC URL, the login and why encrypt=true needs trustServerCertificate on a dev PC.
sql serverjdbchikari
 SQL Server High Published 26 Oct 2026 20:21
  • Open
  • Edit
  • Merge with…
ART-1001 What a Spring Boot starter gives you
Starters bundle the libraries and auto-configuration for one job.
spring bootstartersmaven
 Spring Boot High Published 26 Oct 2026 20:21
  • Open
  • Edit
  • Merge with…
ART-1018 Running the app with a Spring profile
--spring.profiles.active=local loads application-local.properties.
profilesconfiguration
 DevOps Normal Published 09 Nov 2026 20:55
  • Open
  • Merge with…
ART-1013 Indexes for list pages
An index on the filter and sort columns keeps the list fast as rows grow.
indexperformancesql server
 Database Design Normal Draft 09 Nov 2026 20:55
ART-1014 NVARCHAR and Thai text
Use NVARCHAR for text in any language. ภาษาไทย is stored correctly.
unicodenvarcharthai
 SQL Server Normal Published 09 Nov 2026 20:55
  • Open
  • Edit
  • Merge with…
ART-1011 Bean Validation on forms
@NotBlank, @Size and @Pattern on the form class, @Valid in the controller.
validationformsthymeleaf
 Spring Boot Normal Unpublished 09 Nov 2026 20:55
ART-1024 Lambda cheat sheet (second copy)
Collectors.groupingBy and joining.
javastreamscollectors
 Java Low Draft 09 Nov 2026 20:55
ART-1019 Backup and restore a SQL Server database
BACKUP DATABASE CrudLabDB TO DISK = 'C:\backup\CrudLabDB.bak'
backuprestoresqlcmd
 DevOps High Published 09 Nov 2026 20:55
  • Open
  • Merge with…
ART-1015 Writing integration tests against SQL Server
@SpringBootTest starts the whole app against a separate test database.
testingjunitspring boot test
 Testing Normal Draft 09 Nov 2026 20:55
ART-1017 Bootstrap without a CDN (WebJars)
WebJars put Bootstrap inside the jar, so the app works offline.
bootstrapwebjars
 Frontend Low Unpublished 09 Nov 2026 20:55
ART-1009 Spring Data Specifications
Build a WHERE clause from optional filters without string concatenation.
jpacriteriasearch
 Spring Boot Normal Published 09 Nov 2026 20:54
  • Open
  • Edit
  • Merge with…
ART-1007 MERGE: insert or update in one statement
SQL Server MERGE compares a source row with the table and inserts or updates.
mergeupsertsql server
 SQL Server High Published 09 Nov 2026 20:54
  • Open
  • Edit
  • Merge with…
ART-1008 JdbcTemplate batchUpdate
Send thousands of INSERTs in a few round trips.
jdbcbatchperformance
 Java Normal Draft 09 Nov 2026 20:54
ART-1004 Flyway migrations step by step
Every schema change is a numbered SQL file that runs exactly once.
flywaymigrationsschema
 Database Design Normal Published 09 Nov 2026 20:54
  • Open
  • Edit
  • Merge with…
Showing 1–15 of 22
SQL behind this page

Captured by a Hibernate StatementInspector and by the JdbcTemplate code. ? marks a parameter: values are sent separately, which keeps SQL injection out.

Loading this page
1
SELECT
  COUNT(*)                                                                AS total,
  SUM(CASE WHEN deleted = 0 AND archived = 0 THEN 1 ELSE 0 END)           AS active,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'DRAFT' THEN 1 ELSE 0 END)       AS drafts,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'PUBLISHED' THEN 1 ELSE 0 END)   AS published,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'UNPUBLISHED' THEN 1 ELSE 0 END) AS unpublished,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND pinned = 1 THEN 1 ELSE 0 END)    AS pinned,
  SUM(CASE WHEN deleted = 0 AND favourite = 1 THEN 1 ELSE 0 END)                  AS favourites,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND is_read = 0 THEN 1 ELSE 0 END)   AS unread,
  SUM(CASE WHEN deleted = 0 AND locked = 1 THEN 1 ELSE 0 END)                     AS locked,
  SUM(CASE WHEN deleted = 0 AND archived = 1 THEN 1 ELSE 0 END)                   AS archived,
  SUM(CASE WHEN deleted = 1 THEN 1 ELSE 0 END)                                    AS trash
FROM article
2
select a1_0.id,a1_0.archived,a1_0.archived_at,a1_0.category,a1_0.code,a1_0.content,a1_0.created_at,a1_0.deleted,a1_0.deleted_at,a1_0.favourite,a1_0.locked,a1_0.locked_at,a1_0.locked_reason,a1_0.merged_into_id,a1_0.pinned,a1_0.pinned_at,a1_0.priority,a1_0.published_at,a1_0.is_read,a1_0.read_at,a1_0.row_version,a1_0.source_id,a1_0.status,a1_0.summary,a1_0.tags,a1_0.title,a1_0.updated_at from article a1_0 where a1_0.deleted=0 and a1_0.archived=0 order by a1_0.pinned desc,a1_0.updated_at desc,a1_0.id desc offset ? rows fetch first ? rows only
3
select count_big(a1_0.id) from article a1_0 where a1_0.deleted=0 and a1_0.archived=0
4
select distinct a1_0.category from article a1_0 where a1_0.deleted=0 order by a1_0.category