Practical Use of resultMap

Introduction

In this project, you will learn how to use MyBatis to retrieve course information from a database and map the results to a custom Java object. You will also learn how to handle inconsistencies between the database table structure and the entity class properties.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to modify the properties and methods of an entity class to match the database table structure.
• How to configure the mapper interface to define the methods for querying course information.
• How to configure the mapper XML file to define the SQL queries and the result mapping.
• How to implement test cases to verify the functionality of the mapper.

🏆 Achievements

After completing this project, you will be able to:

• Use MyBatis to interact with a database and retrieve data.
• Use resultMap to handle inconsistencies between database table structure and entity class properties.
• Write test cases to verify the functionality of a MyBatis mapper.

Skills Graph

Modify the Entity Class

In this step, you will learn how to modify the properties and methods of the Course class to match the database table structure.

1. Open the Course.java file located in the /org/lanqiao/pojo directory.
2. Change the properties cNo, cName, and teacher to id, name, and lecturer respectively.
3. Modify the getter and setter methods accordingly to match the new property names.
4. Save the changes to the Course.java file.

package org.lanqiao.pojo;

/**
 * Define a Course class
 * @author lanqiao
 */
public class Course {
    // Change course number to id
    private int id;
    // Change course name to name
    private String name;
    // Change lecturer to lecturer
    private String lecturer;

    public Course() {

    }

    public Course(int id, String name, String lecturer) {
        this.id = id;
        this.name = name;
        this.lecturer = lecturer;
    }

    // Modify getter and setter method names
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getLecturer() {
        return lecturer;
    }

    public void setLecturer(String lecturer) {
        this.lecturer = lecturer;
    }

    @Override
    public String toString() {
        return "Course ID: " + this.id + "\tCourse Name: " + this.name + "\tLecturer: " + this.lecturer;
    }
}

Configure the Mapper Interface

In this step, you will learn how to configure the CourseMapper interface to define the methods for querying course information.

1. Open the CourseMapper.java file located in the /org/lanqiao/mapper directory.
2. Modify the interface to include the following methods:
   • selectAllCourses(): Retrieves all course information.
   • selectCourseById(int id): Retrieves the course information with the specified course ID.

package org.lanqiao.mapper;

import java.util.List;
import java.util.Map;
import org.lanqiao.pojo.Course;

public interface CourseMapper {
    List<Course> selectAllCourses();
    Map<String, Object> selectCourseById(int id);
}

Configure the Mapper XML File

In this step, you will learn how to configure the CourseMapper.xml file to define the SQL queries and the result mapping.

1. Open the CourseMapper.xml file located in the root directory of the project.
2. Define a resultMap to specify the mapping between the database table columns and the Course class properties.
3. Define the SQL queries for the methods in the CourseMapper interface.

<?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="org.lanqiao.mapper.CourseMapper">

    <!-- Definition of resultMap -->
    <resultMap id="courseResultMap" type="org.lanqiao.pojo.Course">
        <id property="id" column="cNo"/>
        <result property="name" column="cName"/>
        <result property="lecturer" column="teacher"/>
    </resultMap>

    <!-- Query all course information -->
    <select id="selectAllCourses" resultMap="courseResultMap">
        SELECT * FROM course;
    </select>

    <!-- Query course information with course number 2 -->
    <select id="selectCourseById" resultType="java.util.HashMap">
        SELECT * FROM course WHERE cNo = #{id};
    </select>

</mapper>

Implement the Test Cases

In this step, you will learn how to implement the test cases to verify the functionality of the CourseMapper.

1. Open the MyBatisTest.java file located in the /org/lanqiao/test directory.
2. Implement the testSelectAllCourses() method to retrieve and display all course information.
3. Implement the testSelectCourseById() method to retrieve the course information with course number 2 and store the result in a HashMap.

package org.lanqiao.test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import org.lanqiao.mapper.CourseMapper;
import org.lanqiao.pojo.Course;

public class MyBatisTest {

    SqlSessionFactory sessionFactory = null;
    SqlSession session = null;

    @Before
    public void before() throws IOException{
        String resource = "mybatis-config.xml";
        // Load the MyBatis configuration file
        Reader reader = Resources.getResourceAsReader(resource);
        // Create SqlSession factory
        sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        // Create SqlSession object capable of executing SQL statements in SQL mapping files
        session = sessionFactory.openSession();
    }

    @Test
    public void testSelectAllCourses() throws IOException{
        // Call the getMapper() method in session to return the interface object
        CourseMapper cMapper = session.getMapper(CourseMapper.class);
        // Perform query and return all Course objects
        List<Course> courses = cMapper.selectAllCourses();
        System.out.println(courses);
        session.close();
    }

    @Test
    public void testSelectCourseById() {
        // Obtain an instance of CourseMapper
        CourseMapper mapper = session.getMapper(CourseMapper.class);

        // Call the method to query the information of the course with course number 2
        Map<String, Object> course = mapper.selectCourseById(2);

        // Output the query result
        System.out.println(course);
    }
}

By following these steps, you have successfully modified the entity class, configured the mapper interface and XML file, and implemented the test cases to retrieve course information using MyBatis.

Run

Finally you can compile and run the code in the terminal and check the test files with Maven, using the following commands:

cd MyBatisCourseDemo03
mvn test

You can refer to the following running effects:

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.