Operate the Course Table

Introduction

In this project, you will learn how to use MyBatis to perform various operations on a course table, including querying course information, updating course teacher, and batch querying course data.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to query course information whose course name starts with an uppercase "C" using dynamic SQL
• How to update the teaching teacher of a specific course while keeping others unchanged, using dynamic SQL
• How to batch query course information with a list of course numbers, using dynamic SQL

🏆 Achievements

After completing this project, you will be able to:

• Use MyBatis input and output parameters
• Implement dynamic SQL in MyBatis
• Use result mapping to specify the result set mapping
• Use the <foreach> tag for batch queries

Skills Graph

Query Course Information

In this step, you will learn how to query the course information whose course name starts with an uppercase "C" using dynamic SQL.

1. Open the CourseMapper.java file located in the /org/lanqiao/mapper/ directory.

2. Implement the selectCourses method in the CourseMapper interface. This method should accept a Map<String, Object> parameter to hold the input parameters.

List<Course> selectCourses(Map<String, Object> params);

3. Open the CourseMapper.xml file located in the /org/lanqiao/mapper/ directory.

4. Implement the selectCourses SQL statement in the CourseMapper namespace. Use dynamic SQL to check if the courseName parameter is empty. If it is not empty, add a condition to filter the course names starting with an uppercase "C".

<select id="selectCourses" resultMap="courseResultMap" parameterType="java.util.Map">
    SELECT * FROM course
    <where>
        <if test="courseName != null and courseName != ''">
            AND cName LIKE #{courseName}
        </if>
    </where>
</select>

5. Define the courseResultMap in the CourseMapper.xml file to specify the result set mapping.

<resultMap id="courseResultMap" type="org.lanqiao.pojo.Course">
    <id property="cNo" column="cNo"/>
    <result property="cName" column="cName"/>
    <result property="teacher" column="teacher"/>
</resultMap>

6. Open the MyBatisTest.java file located in the /org/lanqiao/test/ directory.

7. Implement the testSelectCourses test method to test the selectCourses functionality.

@Test
public void testSelectCourses() {
    CourseMapper mapper = session.getMapper(CourseMapper.class);
    Map<String, Object> params = new HashMap<>();
    params.put("courseName", "C%");
    List<Course> courses = mapper.selectCourses(params);
    System.out.println(courses);
}

Update Course Teacher

In this step, you will learn how to update the teaching teacher of the course with the course number "7" to "Blue" using dynamic SQL.

1. Open the CourseMapper.java file located in the /org/lanqiao/mapper/ directory.

2. Implement the updateTeacher method in the CourseMapper interface. This method should accept a Map<String, Object> parameter to hold the input parameters.

void updateTeacher(Map<String, Object> params);

3. Open the CourseMapper.xml file located in the /org/lanqiao/mapper/ directory.

4. Implement the updateTeacher SQL statement in the CourseMapper namespace. Use dynamic SQL to check if the teacherName parameter is empty. If it is not empty, update the teacher column for the course with the specified courseId.

<update id="updateTeacher" parameterType="java.util.Map">
    UPDATE course
    <set>
        <if test="teacherName != null and teacherName != ''">
            teacher = #{teacherName},
        </if>
    </set>
    WHERE cNo = #{courseId}
</update>

5. Open the MyBatisTest.java file located in the /org/lanqiao/test/ directory.

6. Implement the testUpdateTeacher test method to test the updateTeacher functionality.

@Test
public void testUpdateTeacher() {
    CourseMapper mapper = session.getMapper(CourseMapper.class);
    Map<String, Object> params = new HashMap<>();
    params.put("courseId", 7);
    params.put("teacherName", " Blue");
    mapper.updateTeacher(params);
    session.commit();
}

Batch Query Course Information

In this step, you will learn how to batch query the course information with the course numbers "1,2,3" using dynamic SQL.

1. Open the CourseMapper.java file located in the /org/lanqiao/mapper/ directory.

2. Implement the selectCoursesByIds method in the CourseMapper interface. This method should accept a Map<String, Object> parameter to hold the input parameters.

List<Map<String, Object>> selectCoursesByIds(Map<String, Object> params);

3. Open the CourseMapper.xml file located in the /org/lanqiao/mapper/ directory.

4. Implement the selectCoursesByIds SQL statement in the CourseMapper namespace. Use the <foreach> tag to iterate over the ids list and build the IN clause.

<select id="selectCoursesByIds" resultMap="courseResultMap" parameterType="java.util.Map">
    SELECT * FROM course WHERE cNo IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

5. Open the MyBatisTest.java file located in the /org/lanqiao/test/ directory.

6. Implement the testSelectCoursesByIds test method to test the selectCoursesByIds functionality.

@Test
public void testSelectCoursesByIds() {
    CourseMapper mapper = session.getMapper(CourseMapper.class);
    List<Integer> ids = Arrays.asList(1, 2, 3);
    Map<String, Object> params = new HashMap<>();
    params.put("ids", ids);
    List<Map<String, Object>> coursesByIds = mapper.selectCoursesByIds(params);
    System.out.println(coursesByIds);
}

Run

To make sure that the code works, take care to introduce relevant classes into the code.

CourseMapper.java:

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

MyBatisTest.java:

import java.util.Arrays;
import java.util.HashMap;

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 👀 Preview for a running rendering.

Summary

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