Mybatis Many-to-One Association Mapping Techniques

Database Schema Setup

First, let's create the database tables for our example. We'll have two tables: one for nations and another for leaders.

CREATE DATABASE `demo_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `demo_db`.`nation` (
  `nation_id` INT(10) NOT NULL AUTO_INCREMENT,
  `nation_name` VARCHAR(20) NOT NULL,
  PRIMARY KEY(`nation_id`)
) ENGINE = InnoDB;

CREATE TABLE `demo_db`.`leader` (
  `leader_id` INT(10) NOT NULL AUTO_INCREMENT,
  `leader_name` VARCHAR(20) NOT NULL,
  `nation_id` INT(10) NOT NULL,
  PRIMARY KEY(`leader_id`)
) ENGINE = InnoDB;

INSERT INTO `nation` (`nation_id`, `nation_name`) VALUES ('1', 'United States');
INSERT INTO `nation` (`nation_id`, `nation_name`) VALUES ('2', 'United Kingdom');

INSERT INTO `leader` (`leader_id`, `leader_name`, `nation_id`) VALUES ('1', 'John Smith', '1');
INSERT INTO `leader` (`leader_id`, `leader_name`, `nation_id`) VALUES ('2', 'Mike Johnson', '1');
INSERT INTO `leader` (`leader_id`, `leader_name`, `nation_id`) VALUES ('3', 'Robert Brown', '2');
INSERT INTO `leader` (`leader_id`, `leader_name`, `nation_id`) VALUES ('4', 'David Wilson', '2');
INSERT INTO `leader` (`leader_id`, `leader_name`, `nation_id`) VALUES ('5', 'James Taylor', '2');

Entity Classes

Let's define the entity classes for our database tables.

public class Nation {
    private Integer nationId;
    private String nationName;

    public Integer getNationId() {
        return nationId;
    }

    public void setNationId(Integer nationId) {
        this.nationId = nationId;
    }

    public String getNationName() {
        return nationName;
    }

    public void setNationName(String nationName) {
        this.nationName = nationName;
    }

    @Override
    public String toString() {
        return "Nation{" +
                "nationId=" + nationId +
                ", nationName='" + nationName + '\'' +
                '}';
    }
}

public class Leader {
    private Integer leaderId;
    private String leaderName;
    private Nation nation;

    public Integer getLeaderId() {
        return leaderId;
    }

    public void setLeaderId(Integer leaderId) {
        this.leaderId = leaderId;
    }

    public String getLeaderName() {
        return leaderName;
    }

    public void setLeaderName(String leaderName) {
        this.leaderName = leaderName;
    }

    public Nation getNation() {
        return nation;
    }

    public void setNation(Nation nation) {
        this.nation = nation;
    }

    @Override
    public String toString() {
        return "Leader{" +
                "leaderId=" + leaderId +
                ", leaderName='" + leaderName + '\'' +
                ", nation=" + nation +
                '}';
    }
}

MyBatis Utility Class

Here's a utility class to create SqlSession instances:

public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    public static SqlSession getSqlSession() {
        try {
            if (sqlSessionFactory == null) {
                InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            }
            return sqlSessionFactory.openSession();
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
}

Mapper Interface

Define the interface for database operations:

public interface LeaderMapper {
    Leader selectLeaderById(int leaderId);
    Leader selectLeaderByIdWithJoin(int leaderId);
}

Mapper Configuration

Now, let's configure the mapper XML file with two approaches for many-to-one association mapping:

<mapper namespace="mapper.LeaderMapper">
    
    
    <select id="selectNationById" resultType="Nation">
        SELECT * FROM nation WHERE nation_id = #{nationId}
    </select>
    
    <resultMap id="leaderResultMap" type="Leader">
        <id column="leader_id" property="leaderId"/>
        <result column="leader_name" property="leaderName"/>
        <association property="nation" 
                     javaType="Nation"
                     select="selectNationById"
                     column="nation_id"/>
    </resultMap>
    
    <select id="selectLeaderById" resultMap="leaderResultMap">
        SELECT leader_id, leader_name, nation_id
        FROM leader
        WHERE leader_id = #{leaderId}
    </select>

    
    <select id="selectLeaderByIdWithJoin" resultMap="leaderResultMapWithJoin">
        SELECT l.leader_id, l.leader_name, l.nation_id, 
               n.nation_id, n.nation_name
        FROM leader l
        JOIN nation n ON l.nation_id = n.nation_id
        WHERE l.leader_id = #{leaderId}
    </select>
    
    <resultMap id="leaderResultMapWithJoin" type="Leader">
        <id column="leader_id" property="leaderId"/>
        <result column="leader_name" property="leaderName"/>
        <association property="nation" javaType="Nation">
            <id property="nationId" column="nation_id"/>
            <result property="nationName" column="nation_name"/>
        </association>
    </resultMap>
    
</mapper>

Unit Testing

Let's write unit tests to verify our implementation:

public class LeaderMapperTest {
    private LeaderMapper leaderMapper;
    private SqlSession sqlSession;
    
    @Before
    public void setUp() {
        sqlSession = MyBatisUtil.getSqlSession();
        leaderMapper = sqlSession.getMapper(LeaderMapper.class);
    }
    
    @Test
    public void testSelectLeaderById() {
        Leader leader = leaderMapper.selectLeaderById(1);
        System.out.println(leader);
    }
    
    @Test
    public void testSelectLeaderByIdWithJoin() {
        Leader leader = leaderMapper.selectLeaderByIdWithJoin(1);
        System.out.println(leader);
    }
    
    @After
    public void tearDown() {
        if (sqlSession != null) {
            sqlSession.close();
        }
    }
}

Sample Output

When running the tests, you should see output similar to this:

[main] DEBUG mapper.LeaderMapper.selectLeaderByIdWithJoin - ==>  Preparing: SELECT l.leader_id, l.leader_name, l.nation_id, n.nation_id, n.nation_name FROM leader l JOIN nation n ON l.nation_id = n.nation_id WHERE l.leader_id = ? 
[main] DEBUG mapper.LeaderMapper.selectLeaderByIdWithJoin - ==> Parameters: 1(Integer)
[main] DEBUG mapper.LeaderMapper.selectLeaderByIdWithJoin - <==      Total: 1
Leader{leaderId=1, leaderName='John Smith', nation=Nation{nationId=1, nationName='United States'}}