MyBatis: Finally Talking to a Database

The ArrayList Problem

My CRUD API worked beautifully. Create users. Update them. Delete them. Perfect REST endpoints.

Then I restarted the server.

GET /api/users
[]

Gone. Everything gone. All my test users, vanished into the void.

Storing data in an ArrayList is like writing notes on a whiteboard that gets erased every night. Fun for learning, useless for production.

I needed a database.

Why MyBatis Instead of JPA/Hibernate?

If you Google “Spring Boot database,” you’ll see JPA and Hibernate everywhere. They’re the “standard” choice. So why did I go with MyBatis?

JPA/Hibernate:

• Object-Relational Mapping (ORM)
• Write Java, it generates SQL
• Lots of “magic” happening behind the scenes
• Complex queries require learning JPQL or Criteria API

MyBatis:

• SQL Mapper
• Write actual SQL, map it to Java objects
• You see exactly what queries run
• If you know SQL, you know MyBatis

I already knew SQL from my frontend days (yes, I’d poked at databases before). I wanted to see my queries, not have them generated. MyBatis let me do that.

Note: JPA is great for simple CRUD. MyBatis shines when you need complex queries or want full control.

Setting Up MySQL

First, I needed a database. I installed MySQL locally (Docker would’ve been smarter, but I didn’t know Docker yet).

CREATE DATABASE myapi_db;
USE myapi_db;

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Simple table. Four columns. Ready to store users.

Adding MyBatis to Spring Boot

In pom.xml, I added the MyBatis starter and MySQL connector:

<dependencies>
    <!-- Existing dependencies -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <!-- MyBatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>

    <!-- MySQL Connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

Then in application.properties:

# Database connection
spring.datasource.url=jdbc:mysql://localhost:3306/myapi_db
spring.datasource.username=root
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# MyBatis configuration
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

That last line is crucial: map-underscore-to-camel-case=true. It automatically converts created_at in the database to createdAt in Java. No manual mapping needed.

The Mapper Interface

In MyBatis, a Mapper is an interface that defines your database operations:

package com.mycompany.myapi.mapper;

import com.mycompany.myapi.model.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
import java.util.Optional;

@Mapper
public interface UserMapper {

    List<User> findAll();

    Optional<User> findById(Long id);

    void insert(User user);

    void update(User user);

    void delete(Long id);
}

No implementation. Just method signatures. The actual SQL goes in XML files.

Yes, you can use annotations instead of XML. But XML lets you write complex multi-line SQL without cluttering your Java code.

The XML Mapper

Create src/main/resources/mapper/UserMapper.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.mycompany.myapi.mapper.UserMapper">

    <!-- Result map (optional but useful) -->
    <resultMap id="UserResultMap" type="com.mycompany.myapi.model.User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="email" column="email"/>
        <result property="createdAt" column="created_at"/>
    </resultMap>

    <!-- Find all users -->
    <select id="findAll" resultMap="UserResultMap">
        SELECT id, name, email, created_at
        FROM users
        ORDER BY created_at DESC
    </select>

    <!-- Find by ID -->
    <select id="findById" resultMap="UserResultMap">
        SELECT id, name, email, created_at
        FROM users
        WHERE id = #{id}
    </select>

    <!-- Insert new user -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO users (name, email)
        VALUES (#{name}, #{email})
    </insert>

    <!-- Update user -->
    <update id="update">
        UPDATE users
        SET name = #{name}, email = #{email}
        WHERE id = #{id}
    </update>

    <!-- Delete user -->
    <delete id="delete">
        DELETE FROM users
        WHERE id = #{id}
    </delete>

</mapper>

Look at that. Real SQL. SELECT, INSERT, UPDATE, DELETE. No magic query language. No annotations everywhere. Just SQL in a dedicated file.

The #{name} syntax is a placeholder. MyBatis safely inserts the value from the Java object—no SQL injection possible.

Updating the Service

Now the service uses the mapper instead of an ArrayList:

@Service
public class UserService {

    private final UserMapper userMapper;

    public UserService(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    public List<User> findAll() {
        return userMapper.findAll();
    }

    public Optional<User> findById(Long id) {
        return userMapper.findById(id);
    }

    public User create(User user) {
        userMapper.insert(user);
        // MyBatis sets the generated ID on the user object
        return user;
    }

    public Optional<User> update(Long id, User updatedUser) {
        Optional<User> existing = userMapper.findById(id);
        if (existing.isPresent()) {
            updatedUser.setId(id);
            userMapper.update(updatedUser);
            return Optional.of(updatedUser);
        }
        return Optional.empty();
    }

    public boolean delete(Long id) {
        Optional<User> existing = userMapper.findById(id);
        if (existing.isPresent()) {
            userMapper.delete(id);
            return true;
        }
        return false;
    }
}

The controller stays exactly the same. That’s the beauty of the service layer—I changed the persistence mechanism, and the HTTP layer didn’t care.

The Moment of Truth

Start the server. Create a user:

fetch('http://localhost:8080/api/users', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ name: 'Saurav', email: 'saurav@example.com' })
});

Response:

{
  "id": 1,
  "name": "Saurav",
  "email": "saurav@example.com",
  "createdAt": "2020-05-10T14:30:00"
}

Stop the server. Start it again. Fetch users:

[
  {
    "id": 1,
    "name": "Saurav",
    "email": "saurav@example.com",
    "createdAt": "2020-05-10T14:30:00"
  }
]

IT’S STILL THERE.

Data persists across restarts. I nearly cried.

Dynamic SQL: MyBatis Superpowers

Here’s where MyBatis really shines. Need to build a query based on which parameters are provided?

<select id="search" resultMap="UserResultMap">
    SELECT id, name, email, created_at
    FROM users
    <where>
        <if test="name != null">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="email != null">
            AND email = #{email}
        </if>
    </where>
    ORDER BY created_at DESC
</select>

The <where> tag intelligently handles the first AND. The <if> tags conditionally include clauses. This generates different SQL based on what parameters you pass:

// Search by name only
userMapper.search("Saurav", null);
// -> SELECT ... WHERE name LIKE '%Saurav%'

// Search by email only
userMapper.search(null, "saurav@example.com");
// -> SELECT ... WHERE email = 'saurav@example.com'

// Search by both
userMapper.search("Saurav", "saurav@example.com");
// -> SELECT ... WHERE name LIKE '%Saurav%' AND email = 'saurav@example.com'

// No filters
userMapper.search(null, null);
// -> SELECT ... (no WHERE clause)

Try doing that elegantly with JPA. I dare you.

Handling Relationships

When I added a posts table with a foreign key to users, MyBatis handled it beautifully:

<resultMap id="UserWithPostsMap" type="com.mycompany.myapi.model.User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    <collection property="posts" ofType="com.mycompany.myapi.model.Post">
        <id property="id" column="post_id"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
    </collection>
</resultMap>

<select id="findByIdWithPosts" resultMap="UserWithPostsMap">
    SELECT 
        u.id, u.name, u.email,
        p.id AS post_id, p.title, p.content
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.id = #{id}
</select>

One query, one JOIN, and MyBatis maps everything into nested objects. The User object has a List<Post> automatically populated.

Transactions

What if creating a user also needs to create their default settings, and both must succeed or fail together?

@Service
public class UserService {

    private final UserMapper userMapper;
    private final SettingsMapper settingsMapper;

    @Transactional
    public User createWithDefaults(User user) {
        userMapper.insert(user);

        Settings defaults = new Settings();
        defaults.setUserId(user.getId());
        defaults.setTheme("light");
        defaults.setNotifications(true);
        settingsMapper.insert(defaults);

        return user;
    }
}

The @Transactional annotation ensures both operations succeed or both roll back. If settingsMapper.insert() fails, the user isn’t created either.

Common Gotchas I Hit

1. Mapper XML Not Found

Invalid bound statement (not found): com.mycompany.myapi.mapper.UserMapper.findAll

Check that:

• XML file is in src/main/resources/mapper/
• mybatis.mapper-locations in application.properties is correct
• The namespace in XML matches the interface’s fully qualified name

2. Column Name Mismatch

createdAt is always null

Either use map-underscore-to-camel-case=true or explicitly map it in the resultMap:

<result property="createdAt" column="created_at"/>

3. Insert Not Returning ID

userMapper.insert(user);
System.out.println(user.getId());  // null!

Add useGeneratedKeys="true" keyProperty="id" to the insert statement:

<insert id="insert" useGeneratedKeys="true" keyProperty="id">

4. Connection Pool Exhaustion

For production, configure HikariCP (included by default):

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000

What I Wish I’d Known Earlier

1. MyBatis is not “old” or “legacy.” It’s actively maintained and widely used, especially for complex query requirements.

2. XML isn’t evil. It separates SQL from Java code, making both cleaner.

3. Dynamic SQL is MyBatis’s killer feature. Building queries programmatically is elegant and safe.

4. Start with map-underscore-to-camel-case=true. It saves hours of manual mapping.

5. Use Optional return types. findById returning Optional<User> is cleaner than returning null.

The Journey Continues

I had a real API now. Real database. Data that survives restarts. My React app could store and retrieve data permanently.

But there was a problem: anyone could access any endpoint. No authentication. No authorization. Any user could delete any other user.

Time to add security.

---

P.S. — If you’re coming from an ORM background and MyBatis feels “primitive,” give it a fair chance. When your JPA query breaks and you spend 3 hours debugging generated SQL, you’ll wish you’d written it yourself.