Database Expert Skill (MySQL + MyBatis)

This skill focuses on Data Integrity, Query Performance, and MyBatis Configuration quality.

💾 Core Principles

1. Schema First: Changes to Java Entities (AgCart) must match Database Schema (ag_cart) EXACTLY.
2. MyBatis Consistency:
   • resultMap is preferred over resultType to handle snake_case to camelCase mapping safely.
   • Always explicitely list columns in <select>; avoid SELECT *.
3. Performance:
   • Use PageHelper.startPage() BEFORE the query execution for pagination.
   • Ensure Foreign Keys (e.g., product_id) are indexed in MySQL.

🛠️ Common Workflows

1. Adding New Fields

Context: You added receiver_address to the table. Checklist:

1. DB: Run ALTER TABLE script.
2. Entity: Add field private String receiverAddress; + Getters/Setters.
3. 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>

2. 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).