Envoking Database Stored Procedures via XML Mappers

MyBatis provides native support for executing database routines through XML mapping files. Proper configuration requires explicitly defining parameter directions, statement types, and result handling strategies.

Retrieving Scalar Output Values

When a routine calculates a single aggregate metric, it typicallly utilizes IN and OUT parameters. Consider a routine that counts records matching a specific age threshold:

CREATE PROCEDURE `count_records_by_age`(
    IN target_age INT,
    OUT total_matches INT
)
BEGIN
    SELECT COUNT(*) INTO total_matches FROM user_profile WHERE user_age = target_age;
END;

Validation outside the framework confirms expected behavior:

SET @match_count = 0;
CALL count_records_by_age(28, @match_count);
SELECT @match_count;

In the mapper XML, a parameterMap defines the JDBC mode for each placeholder. The statementType must be set to CALLABLE to trigger the procedure driver path:

<select id="fetchAgeAggregate" parameterMap="ageCountMap" statementType="CALLABLE">
    {call count_records_by_age(?, ?)}
</select>

<parameterMap type="java.util.Map" id="ageCountMap">
    <parameter property="searchAge" mode="IN" jdbcType="INTEGER"/>
    <parameter property="resultCount" mode="OUT" jdbcType="INTEGER"/>
</parameterMap>

The calling layer supplies a mutable map. Upon execution, MyBatis populates the outbound key automatically:

String statementRef = "com.example.dao.UserAnalytics.fetchAgeAggregate";
Map<String, Integer> executionContext = new HashMap<>();
executionContext.put("searchAge", 28);

sqlSession.selectOne(statementRef, executionContext);
Integer finalTotal = executionContext.get("resultCount");
System.out.println("Total matching records: " + finalTotal);

Fetching Tabular Result Sets

Procedures that return full datasets require standard result mapping. The critical constraint is ensuring the column aliases in the SELECT statement align exactly with the configured resultMap definitions.

DELIMITER //
CREATE PROCEDURE `list_profiles_by_age`(IN target_age INT)
BEGIN
    SELECT profile_id, display_name, user_age, contact_email 
    FROM user_profile 
    WHERE user_age = target_age;
END //
DELIMITER ;

The mapper combines parameter binding with entity hydration:

<select id="fetchAgeProfiles" parameterMap="profileParams" statementType="CALLABLE" resultMap="profileEntityMap">
    {call list_profiles_by_age(?)}
</select>

<parameterMap type="java.util.Map" id="profileParams">
    <parameter property="searchAge" mode="IN" jdbcType="INTEGER"/>
</parameterMap>

<resultMap id="profileEntityMap" type="com.example.model.UserProfile">
    <id property="userId" column="profile_id"/>
    <result property="loginName" column="display_name"/>
    <result property="age" column="user_age"/>
    <result property="email" column="contact_email"/>
</resultMap>

Invocation returns a fully populated collection:

String statementRef = "com.example.dao.UserAnalytics.fetchAgeProfiles";
Map<String, Integer> queryParams = Collections.singletonMap("searchAge", 28);

List<UserProfile> matchedUsers = sqlSession.selectList(statementRef, queryParams);
for (UserProfile account : matchedUsers) {
    System.out.println(account.getLoginName() + " | " + account.getEmail());
}

Enforcing Manual Transaction Boundaries

By default, SqlSessionFactory.openSession() enables auto-commit, causing each statement to persist immediately. Passing false disables this behvaior, allowing developers to wrap multiple operations within a unified transactional scope and enforce explicit commit or rollback logic.

String configLocation = "mybatis-config.xml";
try (InputStream configStream = Resources.getResourceAsStream(configLocation)) {
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(configStream);
    SqlSession session = factory.openSession(false); // Disable automatic commits

    String insertRef = "com.example.dao.UserManagement.createAccount";
    try {
        UserProfile firstUser = new UserProfile("alice_dev", 28, AccountStatus.ACTIVE);
        session.insert(insertRef, firstUser);

        // Force an arithmetic exception to simulate failure
        UserProfile secondUser = new UserProfile("bob_test", 1 / 0, AccountStatus.ACTIVE);
        session.insert(insertRef, secondUser);

        session.commit();
        System.out.println("Batch committed successfully.");
    } catch (ArithmeticException | RuntimeException ex) {
        System.err.println("Execution interrupted, reverting database state.");
        ex.printStackTrace();
        session.rollback();
    } finally {
        session.close();
    }
}

With auto-commit disabled, the framework treats all statements as an atomic unit. If an exception interrupts execution before commit() is invoked, calling rollback() guarantees that no partial data persists. In the example above, the division-by-zero error triggers the catch block, rolling back the entire transaction so that neither record remains in the database. Enabling auto-commit would have permanently stored the first insertion before the exception halted the process.