The jpa-patterns Skill to Add When Reducing JPA N+1 Problems
機械翻訳 / Machine-translated
機械翻訳 / Machine-translated
Original article: The jpa-patterns Skill to Add When Reducing JPA N+1 Problems (cccskills)
Skill page: jpa-patterns
Key takeaways from this article
With jpa-patterns installed, engineers developing in Spring Boot can work alongside an AI agent to tackle JPA/Hibernate entity design and N+1 problem mitigation.
This article walks through what comes back when you make a request, how to install it, and example prompts to use.
At the end, we cover what to verify before accepting any suggestions.
With jpa-patterns installed, the agent reviews your association definitions and query logic together, then proposes fetch strategies that prevent N+1 problems.
The N+1 problem refers to the situation where, after fetching a list in one query, additional SQL statements are issued for each row to retrieve related data.
For example, you might ask:
Review this entity's associations and suggest a fetch strategy that prevents N+1 problems.
The agent first checks how associations are being loaded.
The skill's approach is to default all associations to lazy loading (fetching only when accessed) and to avoid placing EAGER on collections.
For locations where parent and child data are used together, it returns a proposal to add a JOIN FETCH query to the repository.
@Query("select m from MarketEntity m left join fetch m.positions where m.id = :id")
Optional<MarketEntity> findWithPositions(@Param("id") Long id);
For read-only operations like list screens, it may also propose using DTO projections.
A DTO projection is a lightweight type that holds only the fields you need—such as an ID or name—rather than the full entity.
After making changes, the workflow involves enabling Hibernate's SQL logging and verifying the effect by checking how many SQL statements are issued.
jpa-patterns is a skill that consolidates JPA/Hibernate design and performance tuning in Spring Boot under a single consistent approach.
Its scope covers entities, repositories, transactions, indexes, and connection pools.
Let's break it down by task.
For new entities, the baseline is to explicitly declare table names, column lengths, and nullability via annotations.
Enum types are stored as strings, and creation/update timestamps are recorded automatically using auditing.
Associations are defined with @OneToMany, @ManyToOne, and similar annotations, with cascading deletes determined at this stage as well.
There are two approaches to reducing SQL: specifying fetch strategies and using projections.
The default for associations is lazy loading, with JOIN FETCH used only for queries that need to load data together.
For read-only screens, the principle is to return only the needed columns via projections rather than returning full entities.
Updates are grouped by applying @Transactional to service-layer methods.
Read-only methods receive @Transactional(readOnly = true), and the approach discourages long-running transactions.
Passing entities across transaction boundaries is also flagged for review.
For list searches, PageRequest is used to specify page size and sort order, with results returned as Page.
For large datasets, a cursor-style pagination approach—fetching rows with an ID greater than the last seen—is also an option.
Index candidates include columns used frequently for filtering, such as status fields and foreign keys.
Composite indexes matching the order of search conditions are also considered.
Bulk inserts use saveAll combined with Hibernate's batch settings.
Recommended values are provided for the HikariCP connection pool.
Production schema changes are managed with Flyway or Liquibase.
For data access testing, a setup using @DataJpaTest and Testcontainers to create a production-like environment is recommended.
There are two ways to install: via a command or by asking the agent.
The command is shown below, and you can choose the target agent—Claude Code, Codex, or others.
npx skills add https://github.com/affaan-m/ECC --skill jpa-patterns
If you prefer to delegate to the agent, send the following message as-is.
It asks the agent to review the contents before placing the files, so no verification steps are skipped.
次の Agent Skill をインストールしてください。
https://github.com/affaan-m/ECC/tree/main/skills/jpa-patterns
まず SKILL.md と付属ファイルの中身を確認し、不審な処理（外部への送信、認証情報の読み取りなど）がないことを確かめてから、Claude Code なら ~/.claude/skills/jpa-patterns/、Codex なら ~/.agents/skills/jpa-patterns/ に配置してください。
The installation location depends on whether you want to use the skill across all projects or only within a specific project.
| Agent | All Projects | Specific Project Only |
|---|---|---|
| Claude Code | ~/.claude/skills/ | .claude/skills/ in the project |
| Codex | ~/.agents/skills/ | .agents/skills/ in the project |
jpa-patterns is one of the skills included in the affaan-m/ECC repository.
Since it consists of only a single SKILL.md file, you can read it in its entirety before installing.
Here are three prompts you can use as-is in day-to-day development.
Simply send any of them in Japanese to an agent with the skill installed.
Add pagination to the list search and suggest the necessary indexes.
The response will include a proposal for a repository method that uses PageRequest to specify page size and sort order, returning a Page.
It will also suggest composite indexes suited to the filter columns and sort order (for example, status and creation date).
For lists with large amounts of data, a cursor-style approach that fetches subsequent rows based on an ID threshold will be presented as an option.
Check the transaction scope in the order service and tell me which methods can be made read-only.
For read-only methods, the response will suggest adding readOnly = true.
Any long-running transactions or patterns where entities are carried across transaction boundaries will be flagged for review.
You can also expect a note to choose propagation settings (how transactions are inherited) carefully.
Write repository tests so I can verify using SQL logs that N+1 isn't occurring.
This prompt returns a test proposal using @DataJpaTest and Testcontainers.
The following configuration for logging SQL and bind values is also included, letting you verify the number of issued SQL statements with your own eyes.
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE
jpa-patterns is a skill built around Spring Boot and JPA/Hibernate.
The code examples it provides may not apply directly to configurations that use a different ORM or that don't use Spring Data JPA.
The approach is to manage production schema changes with Flyway or Liquibase rather than relying on Hibernate's automatic DDL (the feature that auto-generates tables from entities).
Projects currently using automatic DDL should read suggestions with this difference in mind.
Also note that migrations are treated as additive by default, and dropping columns without a plan is discouraged.
Connection pool recommendations use fixed values, and the LOB configuration is PostgreSQL-specific.
Secondary caching is also treated as something to "consider carefully," so verify that it suits your database and workload before applying it.
It is safest to verify any proposed changes using tests and actual SQL logs before incorporating them.
The skill is published under the MIT license.
Before installing, read the contents of SKILL.md and confirm there is no suspicious behavior.
No, it won't fix them automatically.
The agent proposes fetch strategies using lazy loading, JOIN FETCH, and projections, but it's up to the developer to decide whether to adopt them.
After applying any changes, verify the result using SQL logs.
The target is JPA/Hibernate with Spring Boot.
The code examples assume Spring Data JPA repositories and @Transactional, so they won't work as-is in other configurations.
Yes, you can.
It covers not only new entity design but also reviewing existing associations and query/update logic.
Starting with areas where a large number of SQL statements appear on list screens makes it easy to compare the effect using SQL logs.
jpa-patterns is a skill for engineers using JPA/Hibernate with Spring Boot to work alongside an agent on entity design and N+1 mitigation.
It lets you review associations, pagination, indexes, and transactions all under the same approach.
Verify suggestions using tests and SQL logs before incorporating them.
More information is available on the cccskills page.
This article is a repost of The jpa-patterns Skill to Add When Reducing JPA N+1 Problems from cccskills.
Details about the skill and installation instructions can be found on the jpa-patterns page at cccskills.