MyBatis XML 매퍼 · Oracle 방언 · PageHelper · Spring Boot

src/main/resources/mapper/
├── oracle/AT/AuditMapper.xml        ← 같은 namespace, 같은 id, SQL 만 다르다
├── maria/AT/AuditMapper.xml
└── tibero/AT/AuditMapper.xml

# application-oracle.yml
mybatis.mapper-locations: classpath:mapper/oracle/**/*.xml
# application-maria.yml
mybatis.mapper-locations: classpath:mapper/maria/**/*.xml

java -jar app.jar --spring.profiles.active=prd,oracle

<properties resource="jdbc.properties"/>                   <!-- db.vendor=oracle -->
<mappers>
    <mapper resource="mapper/${db.vendor}/MemberMapper.xml"/>
    <mapper resource="mapper/${db.vendor}/OrderMapper.xml"/>
</mappers>

// pom.xml: com.github.pagehelper:pagehelper-spring-boot-starter:1.4.7
// application.yml: pagehelper.helper-dialect=oracle, pagehelper.reasonable=true, pagehelper.support-methods-arguments=true

// ① 기본형
PageHelper.startPage(pageNo, pageSize);                      // ThreadLocal 지시
List<Member> list = mapper.search(cond);                     // 바로 다음 조회에만 적용
PageInfo<Member> info = new PageInfo<>(list);                // total, pages, hasNextPage, navigatepageNums …

// ② 안전형: 조회 전에 예외가 나도 지시가 남지 않게
try {
    PageHelper.startPage(pageNo, pageSize);
    return new PageInfo<>(mapper.search(cond));
} finally {
    PageHelper.clearPage();
}

// ③ count 쿼리 직접 제공: 자동 생성된 SELECT COUNT(*) FROM (복잡한 SQL) 이 느릴 때
//    XML 에 id="search_COUNT" 를 만들면 PageHelper 가 자동 count 대신 이것을 쓴다

<select id="search_COUNT" resultType="_long">
    SELECT COUNT(*) FROM member <include refid="searchWhere"/>
</select>

<!-- MariaDB / H2 기본: VALUES (...), (...), (...) -->
<insert id="insertAll" databaseId="maria">
    INSERT INTO member (id, name, email, grade, joined_at, balance, active_yn) VALUES
    <foreach collection="list" item="m" separator=",">
        (#{m.id}, #{m.name}, #{m.email}, #{m.grade}, #{m.joinedAt}, #{m.balance}, #{m.active})
    </foreach>
</insert>

<!-- Oracle: 다중 VALUES 문법이 없다. INSERT ALL INTO … SELECT 1 FROM DUAL -->
<insert id="insertAll" databaseId="oracle">
    INSERT ALL
    <foreach collection="list" item="m">
        INTO member (id, name, email, grade, joined_at, balance, active_yn)
        VALUES (member_seq.NEXTVAL, #{m.name}, #{m.email}, #{m.grade}, #{m.joinedAt}, #{m.balance}, #{m.active})
    </foreach>
    SELECT 1 FROM DUAL
</insert>

@Intercepts({
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
public class SlowQueryInterceptor implements Interceptor {
    private final long thresholdMs;
    public SlowQueryInterceptor(long thresholdMs) { this.thresholdMs = thresholdMs; }

    @Override public Object intercept(Invocation inv) throws Throwable {
        long t0 = System.nanoTime();
        try {
            return inv.proceed();
        } finally {
            long ms = (System.nanoTime() - t0) / 1_000_000;
            if (ms >= thresholdMs) {
                MappedStatement ms0 = (MappedStatement) inv.getArgs()[0];
                String sql = ms0.getBoundSql(inv.getArgs()[1]).getSql().replaceAll("\\s+", " ");
                System.err.printf("[SLOW %d ms] %s : %s%n", ms, ms0.getId(), sql);
            }
        }
    }
    @Override public Object plugin(Object target) { return Plugin.wrap(target, this); }
    @Override public void setProperties(Properties p) { }             // <plugin> 의 <property> 로 threshold 를 받을 수도 있다
}

<plugins>
    <plugin interceptor="SlowQueryInterceptor"><property name="thresholdMs" value="500"/></plugin>
    <plugin interceptor="PageInterceptor"/>
</plugins>