Database Expert Skill (MySQL + MyBatis)
This skill focuses on Data Integrity, Query Performance, and MyBatis Configuration quality.
💾 Core Principles
-
Schema First: Changes to Java Entities (AgCart ) must match Database Schema (ag_cart ) EXACTLY.
-
MyBatis Consistency:
-
resultMap is preferred over resultType to handle snake_case to camelCase mapping safely.
-
Always explicitely list columns in <select> ; avoid SELECT * .
-
Performance:
-
Use PageHelper.startPage() BEFORE the query execution for pagination.
-
Ensure Foreign Keys (e.g., product_id ) are indexed in MySQL.
🛠️ Common Workflows
- Adding New Fields
Context: You added receiver_address to the table. Checklist:
-
DB: Run ALTER TABLE script.
-
Entity: Add field private String receiverAddress;
- Getters/Setters.
-
Mapper XML:
-
Add to <resultMap> : <result property="receiverAddress" column="receiver_address"/>
-
Add to <insert> : #{receiverAddress}
-
Add to <update> : <if test="receiverAddress != null">receiver_address = #{receiverAddress},</if>
- Complex Relations (Joins)
Context: Querying Cart + Product details. Pattern:
-
Use LEFT JOIN in the XML.
-
Map the joined columns (e.g., p.product_name ) to transient fields in the main Entity or a dedicated VO (View Object).
-
Debug: If fields are null, check if the SQL alias (p.product_name ) matches the ResultMap column.
How to use
Invoke this skill when:
-
Modifying database tables.
-
Writing complex SQL queries.
-
Debugging "why is this field null" (it's often a mapping issue).