This article is the author's work in the course design. It is mainly published to share the following learning results with you. If there is any omission, please spray lightly

Not much nonsense, first of all, source code

Student information management system implemented by Java + MySQL

This blog is just a brief introduction to the content of this project. For details, please check the source code to read by yourself (actually, I am too lazy to type too many words)

1. Project structure

View model Dao three-tier structure

2. Database table creation statement

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- users surface
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('root', '111');
INSERT INTO `users` VALUES ('root1', '1234');
INSERT INTO `users` VALUES ('root10', '123456');
INSERT INTO `users` VALUES ('root100', '123456');
INSERT INTO `users` VALUES ('root2', '1234');
INSERT INTO `users` VALUES ('root22', '222123');
INSERT INTO `users` VALUES ('root3', '1234');
INSERT INTO `users` VALUES ('root4', '1234');
INSERT INTO `users` VALUES ('root5', '1234');
INSERT INTO `users` VALUES ('root6', '6666');
INSERT INTO `users` VALUES ('root7', '1234');
INSERT INTO `users` VALUES ('root9', '123456');

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- students Student list
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `sno` varchar(255) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `birthDate` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `major` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('0987', '5555', 'female', '1994-09-04', 'School of management', 'accounting');
INSERT INTO `students` VALUES ('2099', 'ooll', 'female', '1998-01-01', 'School of information science and technology', 'Internet of things');
INSERT INTO `students` VALUES ('3131', 'ffawa', 'female', '1996-06-07', 'School of management', 'Business Administration');
INSERT INTO `students` VALUES ('4114', 'grsg', 'male', '1990-01-01', 'Institute of light industry and food', 'Food Safety');
INSERT INTO `students` VALUES ('4119', 'sgs', 'male', '1996-06-02', 'Economic and Trade College', 'Trade');
INSERT INTO `students` VALUES ('4144', 'faa', 'male', '1997-04-01', 'Agricultural College', 'plant protection');
INSERT INTO `students` VALUES ('4242', 'yyy', 'male', '1996-01-01', 'School of environmental science and Engineering ', 'environmental science');
INSERT INTO `students` VALUES ('4343', 'tea', 'female', '2000-08-06', 'School of information science and technology', 'Internet of things');
INSERT INTO `students` VALUES ('7654', 'ttt', 'male', '2000-05-06', 'College of Life Sciences', 'life sciences');
INSERT INTO `students` VALUES ('9090', 'viviv', 'male', '1999-02-28', 'Institute of light industry and food', 'Food Safety');
INSERT INTO `students` VALUES ('9922', 'erw', 'male', '1996-01-01', 'School of information science and technology', 'Internet of things');

3. entity class

package cn.stuManagementSys.entity;

//User user class
public class User {
	
	private String username;
	private String password;
	
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public User(String username, String password) {
		super();
		this.username = username;
		this.password = password;
	}
	public User() {
		super();
	}
}

package cn.stuManagementSys.entity;

//Student s
public class Student {
	
	private String sno;
	private String name;
	private String sex;
	private String birthDate;
	private String department;
	private String major;
	public String getSno() {
		return sno;
	}
	public void setSno(String sno) {
		this.sno = sno;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getBirthDate() {
		return birthDate;
	}
	public void setBirthDate(String birthDate) {
		this.birthDate = birthDate;
	}
	public String getDepartment() {
		return department;
	}
	public void setDepartment(String department) {
		this.department = department;
	}
	public String getMajor() {
		return major;
	}
	public void setMajor(String major) {
		this.major = major;
	}
	public Student(String sno, String name, String sex, String birthDate, String department, String major) {
		super();
		this.sno = sno;
		this.name = name;
		this.sex = sex;
		this.birthDate = birthDate;
		this.department = department;
		this.major = major;
	}
	public Student() {
		super();
	}
}

4. tool class

package cn.stuManagementSys.utils;

import java.sql.Connection;
import java.sql.DriverManager;

//Package the Jdbc
public class JdbcUtil {
		
		//Fill in the information of your own local database here
		private static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
		private static final String DBURL = "jdbc:mysql://localhost:3306/stumanagerdba?serverTimezone=UTC";
		private static final String DBUSER = "root";
		private static final String DBPASS = "123"; 
		
		public static Connection getConnection() {
			
			Connection conn = null;
			
			try { 
				Class.forName(DBDRIVER); 
				conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
				return conn;
			} catch (Exception e) { 
					e.printStackTrace();
					return null;
			} 
		}
}

5. Dao level

package cn.stuManagementSys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.stuManagementSys.entity.User;
import cn.stuManagementSys.utils.JdbcUtil;

public class UserDao {

	private Connection con = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;

	/**
	 * Find users by user name
	 * @return
	 * @throws SQLException
	 */
	public User searchUserByUsername(String username) {

		String sql = "SELECT * FROM users WHERE username=?";

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				return new User(rs.getString(1), rs.getString(2));
			}

		} catch (Exception e) {
			throw new RuntimeException();
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException();
			}
		}
		return null;
	}

	/**
	 * New users
	 * @param user
	 */
	public void createUser(User user) {

		String sql = "INSERT INTO users VALUES(?,?)";

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getPassword());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException();
			}
		}
	}

	/**
	 * Delete specified user
	 * @param username
	 */
	public void deleteUserByUsername(String username) {

		String sql = "DELETE FROM users WHERE username=?";

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException();
			}
		}
	}

	/**
	 * Change Password
	 * @param newPw
	 * @param username
	 */
	public void updatePwByUsername(String newPw, String username) {

		String sql = "UPDATE users SET password=? WHERE username=?";

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, newPw);
			pstmt.setString(2, username);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException();
			}
		}
	}
}

package cn.stuManagementSys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.stuManagementSys.entity.Student;
import cn.stuManagementSys.utils.JdbcUtil;

public class StudentDao {

	private Connection con = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;

	/**
	 * Return qualified student information according to conditions
	 * 
	 * @param student
	 * @return
	 */
	public List<Student> queryStuListByFilter(Student student) {

		List<Student> stuList = new ArrayList<Student>();

		String sql = "SELECT * FROM students WHERE 1<2";

		try {
			con = JdbcUtil.getConnection();

			if (student.getSno() != null && !student.getSno().equals(""))
				sql += " AND sno=?";
			if (student.getName() != null && !student.getName().equals(""))
				sql += " and name=?";
			if (student.getSex() != null && !student.getSex().equals(""))
				sql += " and sex=?";
			if (student.getBirthDate() != null && !student.getBirthDate().equals(""))
				sql += " and birthDate=?";
			if (student.getDepartment() != null && !student.getDepartment().equals(""))
				sql += " and department=?";
			if (student.getMajor() != null && !student.getMajor().equals(""))
				sql += " and major=?";
			pstmt = con.prepareStatement(sql);
			int index = 0;

			if (student.getSno() != null && !student.getSno().equals(""))
				pstmt.setString(++index, student.getSno());
			if (student.getName() != null && !student.getName().equals(""))
				pstmt.setString(++index, student.getName());
			if (student.getSex() != null && !student.getSex().equals(""))
				pstmt.setString(++index, student.getSex());
			if (student.getBirthDate() != null && !student.getBirthDate().equals(""))
				pstmt.setString(++index, student.getBirthDate());
			if (student.getDepartment() != null && !student.getDepartment().equals(""))
				pstmt.setString(++index, student.getDepartment());
			if (student.getMajor() != null && !student.getMajor().equals(""))
				pstmt.setString(++index, student.getMajor());

			rs = pstmt.executeQuery();
			while (rs.next()) {
				Student stu = new Student();
				stu.setSno(rs.getString(1));
				stu.setName(rs.getString(2));
				stu.setSex(rs.getString(3));
				stu.setBirthDate(rs.getString(4));
				stu.setDepartment(rs.getString(5));
				stu.setMajor(rs.getString(6));
				stuList.add(stu);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				con.close();
				rs.close();
				pstmt.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}

		}
		return stuList;
	}

	/**
	 * Query all student information
	 * 
	 * @return
	 */
	public List<Student> queryAllStudents() {

		List<Student> stuList = new ArrayList<Student>();

		String sql = "SELECT * FROM students";

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				stuList.add(new Student(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4),
						rs.getString(5), rs.getString(6)));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		return stuList;
	}

	/**
	 * Delete student information according to student number
	 * 
	 * @param sno
	 */
	public void deleteStudentBySno(String sno) {

		String sql = "DELETE FROM students WHERE sno=?";

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);

			pstmt.setString(1, sno);
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * Query student information according to student number
	 * 
	 * @param sno
	 * @return
	 */
	public Student queryStudentBySno(String sno) {

		String sql = "SELECT * FROM students WHERE sno=?";
		Student stu = null;

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);

			pstmt.setString(1, sno);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setSno(rs.getString(1));
				stu.setName(rs.getString(2));
				stu.setSex(rs.getString(3));
				stu.setBirthDate(rs.getString(4));
				stu.setDepartment(rs.getString(5));
				stu.setMajor(rs.getString(6));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		return stu;
	}

	/**
	 * Insert student information
	 * 
	 * @param student
	 */
	public void insertStudent(Student student) {

		String sql = "INSERT INTO students VALUES(?,?,?,?,?,?)";

		try {
			con = JdbcUtil.getConnection();

			String sno = student.getSno();
			String sname = student.getName();
			String ssex = student.getSex();
			String sbirthday = student.getBirthDate();
			String stie = student.getDepartment();
			String smajor = student.getMajor();

			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, sno);
			pstmt.setString(2, sname);
			pstmt.setString(3, ssex);
			pstmt.setString(4, sbirthday);
			pstmt.setString(5, stie);
			pstmt.setString(6, smajor);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * Delete all student information found
	 * 
	 * @param stuList
	 */
	public void deleteStudentsByList(List<Student> stuList) {

		String sql = "DELETE FROM students WHERE sno=?";

		try {
			con = JdbcUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < stuList.size(); i++) {
				pstmt.setString(1, stuList.get(i).getSno());
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	/**
	 * Modify student information
	 * 
	 * @param student
	 */
	public void updateStudent(Student student, String oldSno) {

		try {
			con = JdbcUtil.getConnection();

			String sql = "UPDATE students SET sno=?, name=?, sex=?, birthDate=?, department=?, major=? where sno=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, student.getSno());
			pstmt.setString(2, student.getName());
			pstmt.setString(3, student.getSex());
			pstmt.setString(4, student.getBirthDate());
			pstmt.setString(5, student.getDepartment());
			pstmt.setString(6, student.getMajor());
			pstmt.setString(7, oldSno);
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}
}

6. Model level

package cn.stuManagementSys.service;

import cn.stuManagementSys.dao.UserDao;
import cn.stuManagementSys.entity.User;
import cn.stuManagementSys.exception.UserException;

public class UserService {

	private UserDao userDao = new UserDao();

	/**
	 * Sign in
	 * @throws UserException 
	 */
	public void Login(String username, String password) throws UserException {

		User user = userDao.searchUserByUsername(username);
		
		if(user == null) {
			throw new UserException("user does not exist");
		} else if(!(user.getPassword().equals(password))) {
			throw new UserException("Password error");
		}
	}
	
	/**
	 * Change Password
	 * @param username
	 * @param oldPw
	 * @param newPw
	 * @throws UserException 
	 */
	public void UpdatePw(String username, String oldPw, String newPw) throws UserException {
		
		User user = userDao.searchUserByUsername(username);
		
		if(user == null) {
			throw new UserException("user does not exist");
		} else if(!(user.getPassword().equals(oldPw))) {
			throw new UserException("Password error");
		} else {
			userDao.updatePwByUsername(newPw, username);
		}
	}
	
	/**
	 * Query users by user name
	 * @param username
	 * @return
	 */
	public User searchUserByUsername(String username) {
		
		return userDao.searchUserByUsername(username);
	}
	
	/**
	 * New users
	 * @param user
	 */
	public void createUser(User user) {
		
		userDao.createUser(user);
	}
}

package cn.stuManagementSys.service;

import java.util.List;

import cn.stuManagementSys.dao.StudentDao;
import cn.stuManagementSys.entity.Student;

public class StudentService {

	private StudentDao studentDao = new StudentDao();
	
	/**
	 * Return the two-dimensional array of qualified students according to the condition
	 * @param student
	 * @return
	 */
	public List<Student> queryStuListByFilter(Student student) {

		return studentDao.queryStuListByFilter(student);
	}

	/**
	 * Query all students' information
	 * @param student
	 * @return
	 */
	public List<Student> queryAllStudent() {
		
		return studentDao.queryAllStudents();
	}
	
	/**
	 * Delete student information according to student number 
	 * @param sno
	 */
	public void deleteStudentBySno(String sno) {
		
		studentDao.deleteStudentBySno(sno);
	}
	
	/**
	 * Query corresponding student information according to student number
	 * @param sno
	 * @return
	 */
	public Student queryStudentBySno(String sno) {
		
		return studentDao.queryStudentBySno(sno);
	}
	
	/**
	 * Insert student information
	 * @param student
	 */
	public void insertStudent(Student student) {
		
		studentDao.insertStudent(student);
	}
	
	/**
	 * Delete all student information found
	 * @param stuList
	 */
	public void deleteStudentsByList(List<Student> stuList) {
		
		studentDao.deleteStudentsByList(stuList);
	}
	
	/**
	 * Modify student information
	 * @param student
	 */
	public void updateStudent(Student student, String oldSno) {
		
		studentDao.updateStudent(student, oldSno);
	}
}

7. View layer (only partial renderings are shown)

1. Login rendering
   
2. Home page drawing
   
3. Add student information
   
4. Query renderings
   
5. Registered accounts
   
6. Password modification
   
7. Modify student information