It's coming, it's coming, the new MySQL + MyBatis version of the student management system is coming

This article has been synchronized to: Yuyao Village’s blog, welcome to follow the updates

preface

I wrote an article about the Java SE version of the student achievement management system and found it was well received. At the same time, many friends have asked whether there is an interactive version of the student score management system with the database through comments or private messages, and then put it into their own plans. Harm, all blame oneself lazy, so drag until now. But after all is forced to complete their own, but also to the private letter and comments of the partners an account.

Similarly, the student score management system with the interactive version of the data has the function of adding, deleting, changing and checking scores. However, compared to the previous SE version of the function is a little streamlined, missing two small feature points. Therefore, this version of the student score management system is mainly divided into the following functions:

Record student scores
Query individual student’s score
Query all student scores
Update student scores
Delete student scores
Exiting the management System

demo

The main menu

After we get our system up and running, there should be a corresponding system menu, and then we can do different things according to our input.

Record student scores

After entering the menu, we will be prompted to input the scores of the student number, grade, name, advanced mathematics, English, physical education, Java, C++, politics, algorithm and other subjects in turn. After the input is successful, the next function selection will be carried out.

Find individual student scores

This function is mainly through the student number we input, and then query the result of the corresponding student number, if found to print, if not found to prompt. However, whether the result is queried or not, it will enter the next function selection.

Query all student scores

This function can query all the results in the current database, no matter whether the results are queried or not, it will enter the next function selection.

Update student scores

This function by inputting the corresponding student number, and then if the student with the corresponding student number is found, then inputting the scores of each subject in turn, and then updating the scores in the database. If no student with the corresponding student number can be found, a prompt will be given.

Delete student scores

This function will delete the result of the student with the corresponding student number. If there is no student with the corresponding student number, it will prompt.

Function design and implementation

Different from SE project, this system needs to interact with the database, so MyBatis is chosen as the database interaction framework here. Next, I will make a record of the building process of this system for those who need it.

Environment set up

This project mainly uses Maven to construct, while IDE uses the latest version of IntelliJ IDEA. So the first thing to do is to build a Maven project. I’ve written about it before, so I won’t be verbose here. If you are not familiar with creating a Maven project, you can refer to 👉 how to create a Maven project using IDEA.

POM file

To implement this project, the following dependencies are required, so we need to introduce corresponding dependencies in the project’s POM.xml file.

MyBatis
Lombok
MySQL driver


      
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.cunyu</groupId>
    <artifactId>score-management-system</artifactId>
    <version>1.0 the SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
</project>
Copy the code

You may not be familiar with Lombok here, but it’s mostly used to simplify our code, so I recommend you check it out. If you are interested in the simple use of Lombok, I have written an article about it. Portal 👇 :

Lombok Installation and Use Guide

Database preparation

If you’re going to put your data in a database, you have to prepare a database. About the database installation and environment construction, you can go to the Internet to find a tutorial to see, I here only provide the data script to create tables.

/* Navicat Premium Data Transfer Source Server : mbp-mysql Source Server Type : MySQL Source Server Version : 80028 Source Host : localhost:3306 Source Schema : score_management_system Target Server Type : MySQL Target Server Version : 80028 File Encoding : 65001 Date: 05/03/2022 23:02:47 */

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'student id',
  `name` varchar(255) NOT NULL COMMENT 'name',
  `grade` varchar(50) NOT NULL COMMENT 'grade',
  `math` double NOT NULL COMMENT 'advanced mathematics',
  `sport` double NOT NULL COMMENT 'sports',
  `english` double NOT NULL COMMENT 'English',
  `java` double NOT NULL COMMENT 'Java',
  `algorithm` double NOT NULL COMMENT 'algorithms',
  `polity` double NOT NULL COMMENT 'political',
  `average` double NOT NULL COMMENT 'Average score',
  `score` double NOT NULL COMMENT 'total',
  `c_plus_plus` double NOT NULL COMMENT 'C++'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` (`id`, `name`, `grade`, `math`, `sport`, `english`, `java`, `algorithm`, `polity`, `average`, `score`, `c_plus_plus`) VALUES ('101'.'village in the rain'.'CS 1'.88.90.87.98.89.70.88.28571428571429.618.96);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

MyBatis configuration

If you want to use MyBatis, you must configure it. Here, the main need to configure MyBatis core configuration file and set up a tool class. Then add the database to add, delete, change and check the interface, convenient after use. If you are not very familiar with MyBatis, it is suggested to know more about the use of MyBatis, you can refer to some articles I wrote before: MyBatis column.

MyBatis configuration file

Create a file in your project’s main/ Resources folder called mybatis-config.xml, where you configure the database connection.


      
<! DOCTYPEconfiguration
        PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <! -- Hump naming -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <! -- Database configuration information -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/score_management_system? useUnicode=true&amp;characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <! Where to save XML -->
    <mappers>
        <mapper resource="mapper/StudentMapper.xml"/>
    </mappers>
</configuration>
Copy the code

MyBatis tools

In order to facilitate the subsequent call of each method, it is recommended that you configure a MyBatis utility class first to avoid some repeated connection establishment operations.

package com.cunyu.util;

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 java.io.IOException;
import java.io.InputStream;

/**
 * Created with IntelliJ IDEA.
 *
 * @Package : com.cunyu.util
 * @ClassName : MyBatisUtil.java
 * @CreateTime : 2022/3/5 17:11
 * @Version : 1.0
 * @Author* : the village of the rain@Email: [email protected] * @public account: Village Yuyao *@Website : https://cunyu1943.github.io
 * @Description: * /
public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch(IOException e) { e.printStackTrace(); }}/** * get the SqlSession connection **@param
     * @return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @Date"2022/3/5 * /
    public static SqlSession getSession(a) {
        returnsqlSessionFactory.openSession(); }}Copy the code

Design of the interface

package com.cunyu.mapper;

import com.cunyu.entity.Student;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Package : com.cunyu.mapper
 * @ClassName : StudentMapper.java
 * @CreateTime: 2022/3/5 o *@Version : 1.0
 * @Author* : the village of the rain@Email: [email protected] * @public account: Village Yuyao *@Website : https://cunyu1943.github.io
 * @Description: * /
@Mapper
public interface StudentMapper {
    /** * input student scores **@paramStudent Student object *@return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @DateThus 2022/3/5 * /
    int insertScore(Student student);

    /** * check all student scores **@param
     * @returnList<Student@AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @DateSeparate 2022/3/5 * /
    public List<Student> showStudentList(a);

    /** * select ** from student id **@paramId student id *@return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @DateThus 2022/3/5 were * /
    public Student queryById(String id);

    /** * Modify the corresponding student id **@paramStudent student *@return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @Date16:53 2022/3/5 * /
    public int updateById(Student student);


    /** * delete the corresponding student id **@paramId student id *@return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @Date17:02 2022/3/5 * /
    public int deleteById(String id);
}
Copy the code

Mapping SQL files

Remember the XML path in MyBatis configuration above, that is the file we used to map SQL. Typically, we create a new mapper directory under Resource, and then create SQL files in the mapper directory for mapping interfaces.


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.cunyu.mapper.StudentMapper">

    <insert id="insertScore" parameterType="com.cunyu.entity.Student">
        INSERT INTO student (id, name, grade, math, english, sport, polity, java, c_plus_plus, algorithm, score,
                             average)
        VALUES (#{id}, #{name}, #{grade}, #{math}, #{english}, #{sport}, #{polity}, #{java}, #{cPlusPlus}, #{algorithm},
                #{score}, #{average})
    </insert>
    
    <update id="updateById" parameterType="com.cunyu.entity.Student">
        UPDATE student
        SET math        = #{math},
            english     = #{english},
            sport       = #{sport},
            polity      = #{polity},
            java        = #{java},
            c_plus_plus = #{cPlusPlus},
            algorithm   = #{algorithm},
            score       = #{score},
            average     = #{average}
        WHERE id = #{id}
    </update>
    
    <delete id="deleteById">
        DELETE
        FROM student
        WHERE id = #{id}
    </delete>
    
    <select id="showStudentList" resultType="com.cunyu.entity.Student">
        SELECT *
        FROM student
    </select>

    <select id="queryById" resultType="com.cunyu.entity.Student">
        SELECT *
        FROM student
        WHERE id = #{id}
    </select>

</mapper>
Copy the code

The main class design

The system is mainly for student performance information management, so it mainly involves a student class.

package com.cunyu.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 *
 * @Package : com.cunyu.entity
 * @ClassName : Student.java
 * @CreateTime: 2022/3/5 out *@Version : 1.0
 * @Author* : the village of the rain@Email: [email protected] * @public account: Village Yuyao *@Website : https://cunyu1943.github.io
 * @Description: * /

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Student {
    /** ** student id */
    private String id;
    /** * name */
    private String name;
    /** * class */
    private String grade;
    /**
     * 高数
     */
    private Double math;
    /** ** ** /
    private Double sport;
    /** * English */
    private Double english;
    /** * Java */
    private Double java;
    /** * algorithm */
    private Double algorithm;
    /** * politics */
    private Double polity;
    /** * C ++ */
    private Double cPlusPlus;
    /** * total score */
    private Double score;
    /** ** average score */
    private Double average;
}
Copy the code

The main interface

The main interface is to print out the system function menu, and then enter different sub-function modules according to our instructions.

import com.cunyu.entity.Student;

import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 *
 * @Package : PACKAGE_NAME
 * @ClassName : Main.java
 * @CreateTime: 2022/3/5 then *@Version : 1.0
 * @Author* : the village of the rain@Email: [email protected] * @public account: Village Yuyao *@Website : https://cunyu1943.github.io
 * @Description: * /
public class Main {
    public static Scanner scanner = new Scanner(System.in);

    public static void main(String[] args) {
        while (true) {
            System.out.println("---------- Welcome to the student Achievement Management system -----------");
            System.out.println("------------- [1] Input student scores -------------");
            System.out.println("------------- [2] find student scores -------------");
            System.out.println("------------- [3] Modify student grades -------------");
            System.out.println("------------- [4] delete student scores -------------");
            System.out.println("------------- [5] display all grades -------------");
            System.out.println("------------- [0] Exit management system -------------");

            Student student = new Student();

            System.out.println("Enter your choice [0-5]");
            String choice = scanner.next();
            switch (choice) {
                case "1":
                    // Input student scores
                    student.insertScore();
                    break;
                case "2":
                    // Find the corresponding student's score
                    student.queryScoreById();
                    break;
                case "3":
                    // Modify student grades
                    student.update();
                    break;
                case "4":
                    // Delete student scores
                    student.delete();
                    break;
                case "5":
                    // query all student scores
                    student.queryScores();
                    break;
                case "0":
                    System.exit(0);
                    break;
                default:
                    break; }}}}Copy the code

Record student scores

First input student number, judge whether available, not available to re-input, available to continue to input follow-up other information.

It then assigns the information to the student object, and finally inserts the student object into the database.

Copy the code

/** * input student scores **@param
     * @return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @Date18:05 2022/3/5 * /
public void insertScore(a) {
    SqlSession sqlSession = MyBatisUtil.getSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Scanner scanner = new Scanner(System.in);
    System.out.println("---------- Enter student data ----------");
    System.out.println("Please enter the following data successively:");
    String id;
    while (true) {
        System.out.println("Student id");
        id = scanner.nextLine();
        if (isValid(id)) {
            System.out.println("Student number repeated, please re-enter.");
        } else {
            break;
        }
    }
    System.out.println("Grade");
    String grade = scanner.nextLine();

    System.out.println("Name");
    String name = scanner.nextLine();
    System.out.println("Mathematics");
    Double math = scanner.nextDouble();
    System.out.println("English");
    Double english = scanner.nextDouble();
    System.out.println("Sports");
    Double sport = scanner.nextDouble();
    System.out.println("Java");
    Double java = scanner.nextDouble();
    System.out.println("C++");
    Double cPlusPlus = scanner.nextDouble();
    System.out.println("Political");
    Double polity = scanner.nextDouble();
    System.out.println("Algorithm");
    Double algorithm = scanner.nextDouble();

    // Create an object, set its properties, and insert it into the database
    Student student = new Student();
    student.setId(id);
    student.setName(name);
    student.setGrade(grade);
    student.setSport(sport);
    student.setCPlusPlus(cPlusPlus);
    student.setJava(java);
    student.setMath(math);
    student.setPolity(polity);
    student.setEnglish(english);
    student.setAlgorithm(algorithm);
    student.setScore(math + polity + english + algorithm + cPlusPlus + java + sport);
    student.setAverage(student.getScore() * 1.0 / 7);
    int i = mapper.insertScore(student);
    if (i > 0) {
        sqlSession.commit();
        System.out.println("Record result successfully");
    } else {
        System.out.println("Failed to enter score, please re-enter");
    }

    sqlSession.close();

}
Copy the code

Query individual student’s score

Check whether there is data in the database. If there is data, perform subsequent operations. Through the input student number query, from the database to find the corresponding student number.

/** * select ** from student id@param
     * @return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @Date18:06 2022/3/5 * /
public void queryScoreById(a) {
    SqlSession sqlSession = MyBatisUtil.getSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Scanner scanner = new Scanner(System.in);
    System.out.println("---------- find student scores ----------");
    List<Student> students = mapper.showStudentList();
    Student result = new Student();
    Boolean flag = false;
    if (students.size() == 0) {
        System.out.println("There is no data at present. Please input your score and try again.");
    } else {
        System.out.println("Please enter the student id of the student whose grade you are looking for.");
        String id = scanner.nextLine();
        for (Student student : students) {
            if (id.equals(student.getId())) {
                flag = true; result = student; }}if (flag) {
            System.out.println("The result of the student corresponding to the student number is as follows:");
            System.out.println("Student id \ t \ \ t class t \ \ t t name mathematical \ t \ \ t \ \ t t t English sports \ tJava \ t \ \ t tC++ \ t political \ t \ \ t \ \ t t t algorithm average t \ \ t score");
            System.out.format("%s\t%s\t\t%s\t\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\n", result.getId(), result.getGrade(), result.getName(), result.getMath(), result.getEnglish(), result.getSport(), result.getJava(), result.getCPlusPlus(), result.getPolity(), result.getAlgorithm(), result.getAverage(), result.getScore());
            System.out.println("Search result successful!");
        } else {
            System.out.println("Did not find the corresponding student number, please confirm and re-enter!");
        }
    }
    sqlSession.close();
}
Copy the code

Query all student scores

Check whether there is data before performing subsequent operations. Query all the data from the database, and then print each student’s score information.

Copy the code

/** * query all student grades **@param
     * @return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @DateAnd 2022/3/5 * /
public void queryScores(a) {
    SqlSession sqlSession = MyBatisUtil.getSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> students = mapper.showStudentList();
    if (students.size() == 0) {
        System.out.println("Database currently has no data, please add data first");
    } else {
        System.out.println("---------- All students scored below ----------");
        System.out.println("Student id \ t \ \ t class t \ \ t t name mathematical \ t \ \ t \ \ t t t English sports \ tJava \ t \ \ t tC++ \ t political \ t \ \ t \ \ t t t algorithm average t \ \ t score");
        for (Student student : students) {
            System.out.format("%s\t%s\t%s\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\t%.2f\n", student.getId(), student.getGrade(), student.getName(), student.getMath(), student.getEnglish(), student.getSport(), student.getJava(), student.getCPlusPlus(), student.getPolity(), student.getAlgorithm(), student.getAverage(), student.getScore());
        }
    }
    sqlSession.close();
}
Copy the code

Revision of student grades

Check whether there is data before performing subsequent operations. By matching the student number, find the student corresponding to the student number, and then modify its performance information, but do not modify other personal information.

/** * Update the score of the student with the corresponding student id **@param
     * @return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @DateBacks 2022/3/5 * /
public void update(a) {
    SqlSession sqlSession = MyBatisUtil.getSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Scanner scanner = new Scanner(System.in);
    List<Student> students = mapper.showStudentList();
    System.out.println("---------- modify student grades ----------");
    if (students.isEmpty()) {
        System.out.println("There is no data in the current database. Please add and try again.");
    } else {
        System.out.println("Please enter the student ID you want to change.");
        String id = scanner.nextLine();
        Student student = mapper.queryById(id);
        if (student == null) {
            System.out.println("Did not find the corresponding student number information, please confirm and then delete!");
        } else {
            System.out.println("Please re-enter this student's score");
            System.out.println("Mathematics");
            Double math = scanner.nextDouble();
            System.out.println("English");
            Double english = scanner.nextDouble();
            System.out.println("Sports");
            Double sport = scanner.nextDouble();
            System.out.println("Java");
            Double java = scanner.nextDouble();
            System.out.println("C++");
            Double cPlusPlus = scanner.nextDouble();
            System.out.println("Political");
            Double polity = scanner.nextDouble();
            System.out.println("Algorithm");
            Double algorithm = scanner.nextDouble();

            student.setSport(sport);
            student.setEnglish(english);
            student.setPolity(polity);
            student.setMath(math);
            student.setJava(java);
            student.setCPlusPlus(cPlusPlus);
            student.setAlgorithm(algorithm);
            student.setScore(sport + math + english + polity + java + cPlusPlus + algorithm);
            student.setAverage(student.getScore() / 7);
            if (mapper.updateById(student) > 0) {
                sqlSession.commit();
                System.out.println("---------- Updated successfully ----------");
            } else {
                System.out.println("---------- update failed ----------");
            }
        }
    }
    sqlSession.close();
}
Copy the code

Delete student scores

Check whether there is data before performing subsequent operations. Then through the input student number to match, find the corresponding student number, and then delete it from the database.

/** * delete student id **@param
     * @return
     * @AuthorRemote * village in the rain@Version 1.0
     * @Description
     * @Date2022/3/5 out * /
public void delete(a) {
    SqlSession sqlSession = MyBatisUtil.getSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Scanner scanner = new Scanner(System.in);
    List<Student> students = mapper.showStudentList();
    System.out.println("---------- delete student scores ----------");
    if (students.size() == 0) {
        System.out.println("There is no data in the current database. Please add and try again.");
    } else {
        System.out.println("Enter the student ID to delete the student's score");
        String id = scanner.nextLine();
        if (mapper.queryById(id) == null) {
            System.out.println("Did not find the corresponding student number information, please confirm and then delete!");
        } else {
            if (mapper.deleteById(id) > 0) {
                sqlSession.commit();
                System.out.println("---------- deleted successfully ----------");
            } else {
                System.out.println("---------- failed to delete ----------");
            }
        }
    }
    sqlSession.close();
}
Copy the code

conclusion

The above is about the database version of the student score management system related to the design of all the content, I believe that read here partners should also be able to successfully complete the course design. If you still don’t understand some of the details, feel free to comment or follow me.

The code examples

Finally, I have uploaded the code of the sample to Github. If you need it, you can help yourself!

🏳️🌈 Portal: github.com/cunyu1943/j…

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

It’s coming, it’s coming, the new MySQL + MyBatis version of the student management system is coming

preface

demo

Function design and implementation

Environment set up

POM file

Database preparation

MyBatis configuration

Design of the interface

The main class design

The main interface

Record student scores

Query individual student’s score

Query all student scores

Revision of student grades

Delete student scores

conclusion

The code examples

It’s coming, it’s coming, the new MySQL + MyBatis version of the student management system is coming

preface

demo

Function design and implementation

Environment set up

POM file

Database preparation

MyBatis configuration

Design of the interface

The main class design

The main interface

Record student scores

Query individual student’s score

Query all student scores

Revision of student grades

Delete student scores

conclusion

The code examples

Related Posts

Out of the box micro-servitization rapid development platform

Learn SpringCloud Eureka to take you from zero to one

Two-dimensional code test point