Bulk Insert Data Into Course Schedule

Introduction

In this project, you will learn how to bulk insert data into a course schedule using MyBatis, a popular Java persistence framework. This project will guide you through the process of setting up the database, creating the necessary tables, and implementing the bulk insert functionality.

👀 Preview

🎯 Tasks

In this project, you will learn:

• How to start the database service and create the MyBatisDemo database and course table
• How to set up the MyBatisCourseDemo project
• How to implement the bulk insert of course data using the <foreach> tag in MyBatis

🏆 Achievements

After completing this project, you will be able to:

• Work with MyBatis to interact with a database
• Use the <foreach> tag in MyBatis to perform bulk data insertions
• Set up and configure a Java project with MyBatis

Skills Graph

Start the Database Service and Create the Course Table

In this step, you will learn how to start the database service, create the MyBatisDemo database, create the course table, and import the initial data.

1. Start the MySQL database service:

cd ~/project
sudo service mysql start

2. Create the MyBatisDemo database and the course table by running the SQL script:

mysql -u root < ~/project/course.sql && rm course.sql

This command will execute the SQL script located at ~/project/course.sql to create the necessary database and table. After the script is executed, the course.sql file will be deleted.

Set up the MyBatisCourseDemo Project

In this step, you will set up the MyBatisCourseDemo project, which is provided as a foundation to complete the challenge.

1. Navigate to the project directory:

cd ~/project/MyBatisCourseDemo/

Implement the Bulk Insert of Course Data

In this step, you will add the following three pieces of data to the course schedule at once (in bulk) using the <foreach> tag in MyBatis.

• Course ID: 8, Course Name: Golang Language, Instructor: Amy
• Course ID: 9, Course Name: Oracle, Instructor: Bob
• Course ID: 10, Course Name: Big Data, Instructor: Jone

1. Open the CourseMapper.java file in the /src/main/java/org/labex/mapper/ directory and add the following code:

package org.labex.mapper;

import java.util.List;

import org.labex.pojo.Course;

public interface CourseMapper {
    void insertCourses(List<Course> courses);
}

2. Open the CourseMapper.xml file and add the following code:

<?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">
    <insert id="insertCourses" parameterType="java.util.List">
        INSERT INTO course (cNo, cName, teacher)
        VALUES
        <foreach collection="list" item="course" separator=",">
            (#{course.cNo}, #{course.cName}, #{course.teacher})
        </foreach>
    </insert>
</mapper>

3. Open the MyBatisTest.java file in the /src/test/java/org/labex/test/ directory and add the following code:

package org.labex.test;

import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
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 SqlSessionFactory
        sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        // Create SqlSession object to execute SQL statements defined in the mapping files
        session = sessionFactory.openSession();
    }

    @Test
    public void testInsertCourses() {
        // Create a list of Course objects to be inserted
        List<Course> courses = new ArrayList<>();
        courses.add(new Course(8, "Golang Language", "Amy"));
        courses.add(new Course(9, "Oracle", "Bob"));
        courses.add(new Course(10, "Big Data", "Jone"));

        // Get the CourseMapper instance
        CourseMapper courseMapper = session.getMapper(CourseMapper.class);

        // Call the insertCourses method with the list of courses
        courseMapper.insertCourses(courses);

        // Commit the transaction
        session.commit();

        // Close the session
        session.close();
    }

}

4. Run the test case to verify the solution:

mvn test

The output results refer to the following:

Summary

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