Course Schedule CRUD with MyBatis

Introduction

In this project, you will learn how to perform CRUD (Create, Read, Update, Delete) operations on a course schedule table using MyBatis, a popular Java persistence framework.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to configure the project and set up the necessary dependencies
• How to improve the Course entity class by adding properties, constructors, setter/getter methods, and other content
• How to improve the MyBatis configuration file mybatis-config.xml
• How to implement the CourseMapper interface and its corresponding methods
• How to implement the SQL mapping in the CourseMapper.xml file
• How to implement the test cases in the MyBatisTest.java file

🏆 Achievements

After completing this project, you will be able to:

• Use MyBatis to interact with a MySQL database
• Define entity classes and configure the MyBatis mapping files
• Implement CRUD operations using MyBatis
• Write unit tests to verify the functionality of the application

Configure the Project

In this step, you will learn how to configure the project and set up the necessary dependencies.

1. Open the pom.xml file in the MyBatisCourseDemo02 project.
2. Add the following dependencies to the <dependencies> section:

<!-- MyBatis jar -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.6</version>
    </dependency>

    <!-- MySQL database driver jar -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>

3. Add the following properties to the <properties> section to configure the character encoding and JDK version:

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

4. Add the following <build> section to include the XML mapping files in the classpath:

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

Improve the Entity Class

In this step, you will improve the Course entity class by adding properties, constructors, setter/getter methods, and other content.

1. Open the Course.java file in the /src/main/java/org/labex/pojo directory.
2. Add the following properties to the Course class:

    private int cNo;
    private String cName;
    private String teacher;

3. Add the following constructor:

    public Course() {

    }

    public Course(int cNo, String cName, String teacher) {
        this.cNo = cNo;
        this.cName = cName;
        this.teacher = teacher;
    }

4. Add the following getter and setter methods:

    public int getCNo() {
        return cNo;
    }

    public void setCNo(int cNo) {
        this.cNo = cNo;
    }

    public String getCName() {
        return cName;
    }

    public void setCName(String cName) {
        this.cName = cName;
    }

    public String getTeacher() {
        return teacher;
    }

    public void setTeacher(String teacher) {
        this.teacher = teacher;
    }

5. Add the following toString() method:

    @Override
    public String toString() {
        return "Course number: " + this.cNo + "\tCourse name: " + this.cName + "\tTeacher: " + this.teacher;
    }

Improve the MyBatis Configuration

In this step, you will improve the MyBatis configuration file mybatis-config.xml.

1. Open the mybatis-config.xml file in the src/main/resources directory.
2. Add the following <typeAliases> section to define aliases for the entity classes:

    <typeAliases>
        <package name="org.labex.pojo"/>
    </typeAliases>

3. Configure the data source environment in the <environments> section:

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}" />
                <property name="url" value="${mysql.url}" />
                <property name="username" value="${mysql.username}" />
                <property name="password" value="${mysql.password}" />
            </dataSource>
        </environment>
    </environments>

4. Register the SQL mapping file CourseMapper.xml in the <mappers> section:

    <mappers>
        <package name="org.labex.mapper" />
    </mappers>

Implement the CourseMapper Interface

In this step, you will implement the CourseMapper interface and its corresponding methods.

1. Open the CourseMapper.java file in the org.labex.mapper package.
2. Add the following methods to the CourseMapper interface:

import java.util.List;

import org.labex.pojo.Course;

public interface CourseMapper {

    /**
     * Query all courses information
     */
    public List<Course> queryAllCourse();

    /**
     * Insert a new course
     * @param course
     */
    public int insertCourse(Course course);

    /**
     * Update a course information
     * @param course
     */
    public int updateCourse(Course course);

    /**
     * Delete a course information by course number
     * @param course
     */
    public int deleteCourse(int cNo);
}

Implement the SQL Mapping

In this step, you will implement the SQL mapping in the CourseMapper.xml file.

1. Open the CourseMapper.xml file in the src/main/java/org/labex/mapper directory.
2. Add the following SQL mapping statements:

    <!-- Retrieve all course information -->
    <select id="queryAllCourse" resultType="org.labex.pojo.Course">
        select * from course
    </select>

    <!-- Insert a new course -->
    <insert id="insertCourse" parameterType="org.labex.pojo.Course">
        insert into course(cNo,cName,teacher)
        values(#{cNo},#{cName},#{teacher})
    </insert>

    <!-- Update course information based on course number -->
    <update id="updateCourse" parameterType="org.labex.pojo.Course">
        update course set cName=#{cName},teacher=#{teacher}
        where cNo=#{cNo}
    </update>

    <!-- Delete course information based on course number -->
    <delete id="deleteCourse" parameterType="int">
        delete from course where cNo=#{cNo}
    </delete>

Implement the Test Cases

In this step, you will implement the test cases in the MyBatisTest.java file.

1. Open the MyBatisTest.java file in the /src/test/java/org/labex/test directory.
2. Add the following test methods:

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

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.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 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 testSel() 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.queryAllCourse();
        System.out.println(courses);
        session.close();
    }

    @Test
    public void testIns() throws IOException{
        // Call the getMapper() method in session to return the interface object
        CourseMapper cMapper = session.getMapper(CourseMapper.class);

        Course course = new Course(8,"Artificial Intelligence","Tom");

        int i = cMapper.insertCourse(course);

        session.commit();

        System.out.println((i!=0 ? "Data added successfully":"Failed to add data"));

        session.close();
    }

    @Test
    public void testUpd() throws IOException{
        // Call the getMapper() method in session to return the interface object
        CourseMapper cMapper = session.getMapper(CourseMapper.class);

        Course course = new Course(1,"Software Testing","Jack");

        int i = cMapper.updateCourse(course);

        session.commit();

        System.out.println((i!=0 ? "Data updated successfully":"Failed to update data"));

        session.close();
    }

    @Test
    public void testDEl() throws IOException{
        // Call the getMapper() method in session to return the interface object
        CourseMapper cMapper = session.getMapper(CourseMapper.class);

        int i = cMapper.deleteCourse(7);

        session.commit();

        System.out.println((i!=0 ? "Data deleted successfully":"Failed to delete data"));

        session.close();
    }
}

After completing these steps, you have successfully configured the project, improved the entity class, configured the MyBatis settings, implemented the CourseMapper interface, and implemented the test cases. You can now run the tests to verify the functionality of the application.

Run

Next, compile and run in the terminal, and use Maven to check the test files:

cd MyBatisCourseDemo02
mvn test

You can refer to the steps below to verify the challenge results.

Summary

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