MyBatis Database Integration: Mastering SQL Queries and Data Retrieval

Introduction

In this project, you will learn how to use MyBatis, a popular Java persistence framework, to interact with a MySQL database. Specifically, you will learn how to use input parameters in SQL queries and retrieve course information based on the teacher's name.

👀 Preview

[labex] DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@642a7222]
[labex] DEBUG [main] - ==>  Preparing: SELECT * FROM course WHERE teacher LIKE CONCAT('%', ?, '%') ORDER BY cNo DESC
[labex] DEBUG [main] - ==> Parameters: Am(String)
[labex] DEBUG [main] - <==      Total: 2
Course Number: 3        Course Name: Python     Teacher: Liam
Course Number: 1        Course Name: HTML       Teacher: Amy

🎯 Tasks

In this project, you will learn:

Implement the CourseMapper interface to define a method for retrieving courses based on the teacher's name
Write the SQL query in the CourseMapper.xml file to perform a fuzzy search on the teacher column and sort the results in descending order by course number
Test the CourseMapper implementation in the MyBatisTest class to ensure the desired functionality

🏆 Achievements

After completing this project, you will be able to:

Use MyBatis to interact with a MySQL database
Pass input parameters to SQL queries using a Map
Perform a fuzzy search on a database column and sort the results
Test the functionality of a MyBatis mapper using a JUnit test case

Implement the CourseMapper Interface

In this step, you will implement the CourseMapper interface to retrieve course information based on the teacher's name.

Open the CourseMapper.java file located in the org.labex.mapper package.

package org.labex.mapper;

import java.util.List;
import java.util.Map;

import org.labex.pojo.Course;

public interface CourseMapper {
    List<Course> getCoursesByTeacherName(Map<String, Object> params);
}

The CourseMapper interface defines a method getCoursesByTeacherName that takes a Map as an input parameter. This method will be used to retrieve courses where the teacher's name contains the specified value.

Implement the SQL Query in the Mapper XML File

In this step, you will implement the SQL query in the CourseMapper.xml file to retrieve the courses based on the teacher's name.

Open the CourseMapper.xml file located in the src/main/java/org/labex/mapper directory.

<?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.labex.mapper.CourseMapper">
    <select id="getCoursesByTeacherName" parameterType="java.util.HashMap" resultType="org.labex.pojo.Course">
        SELECT * FROM course
        WHERE teacher LIKE CONCAT('%', #{teacherName}, '%')
        ORDER BY cNo DESC
    </select>
</mapper>

The <select> element defines the SQL query that will be executed when the getCoursesByTeacherName method is called. The parameterType attribute specifies that the input parameter is a HashMap, and the resultType attribute specifies that the result will be mapped to the Course class.

The SQL query uses the LIKE operator with the CONCAT function to perform a fuzzy search on the teacher column, where the teacher's name contains the specified teacherName value. The results are then sorted in descending order by the cNo (course number) column.

Test the CourseMapper in the MyBatisTest Class

In this step, you will test the CourseMapper implementation by creating a test case in the MyBatisTest.java file.

Open the MyBatisTest.java file located in the org.labex.test package.

package org.labex.test;

import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
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.After;
import org.junit.Before;
import org.junit.Test;
import org.labex.mapper.CourseMapper;
import org.labex.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 the SqlSession factory
        sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //Create a SqlSession object that can execute SQL statements in the SQL mapping file
        session = sessionFactory.openSession();
    }

    @Test
    public void testCourseMapper() {
        CourseMapper courseMapper = session.getMapper(CourseMapper.class);

        // Create a parameter map
        Map<String, Object> param = new HashMap<>();
        param.put("teacherName", "Am");

        // Call the method defined in CourseMapper
        List<Course> courses = courseMapper.getCoursesByTeacherName(param);
        for (Course course : courses) {
            System.out.println(course);
        }
    }

    @After
    public void after() {
        session.close();
    }

}

In the testCourseMapper method, we first get an instance of the CourseMapper interface using session.getMapper(CourseMapper.class). Then, we create a HashMap as the input parameter and put the "teacherName" key with the value "Am".

Finally, we call the getCoursesByTeacherName method of the CourseMapper and print the retrieved Course objects.

Run the Test Case

To run the test case, follow these steps:

In your terminal, use the following command to start the MySQL database and import the course.sql file:

sudo service mysql start
mysql -u root < course.sql

Navigate to the MyBatisCourseDemo project directory using the following command:

cd ~/project/MyBatisCourseDemo/

Run the following command to execute the test case:

mvn test

The output should be similar to the following:

[labex] DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@642a7222]
[labex] DEBUG [main] - ==>  Preparing: SELECT * FROM course WHERE teacher LIKE CONCAT('%', ?, '%') ORDER BY cNo DESC
[labex] DEBUG [main] - ==> Parameters: Am(String)
[labex] DEBUG [main] - <==      Total: 2
Course Number: 3        Course Name: Python     Teacher: Liam
Course Number: 1        Course Name: HTML       Teacher: Amy

This shows that the getCoursesByTeacherName method in the CourseMapper is working as expected, retrieving the courses where the teacher's name contains "Am" and sorting them in descending order by course number.

Summary

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

MyBatis Database Integration Project