1, Project introduction

Title Requirements:

Functional requirements:

1. Add student functions: name, student number, gender, date of birth. (Note: the student number is automatically generated and must be unique)

2. Add student achievement function: suppose everyone has chosen Chinese, mathematics and English. However, when inputting grades, it is generally to input the grades of a certain course for all students.

3. Find the student's grade according to the student's student number, and display the name, student number and grade on the interface. If the student number does not exist, a prompt message will be given

4. Find the student's score according to the student's name (fuzzy matching is supported), and display the name, student number and score on the interface. If multiple students with the same name exist, they will be displayed together. If the name does not exist, a prompt will be given

5. Generate student learning report: the report includes student number, name, grade of each subject and corresponding class average value of the subject, total grade and average value of class total grade. Finally, the average value of the total score is output in descending order on the graphical interface, and the sorting results can be output to the "grade sheet. txt" file or excel file (the output to excel file is a special bonus item, and POI technology can be used).

6. Support the drawing of columnar distribution chart for the scores of all students in each subject (optional).

7. Support the modification and deletion of student information (to be reflected in the file or database), and the student number cannot be modified.

8. Test: support the random generation of 100000 students and their names, student numbers and grades into text files for testing. (the student numbers cannot be the same, and the score of each star is divided into normal parts based on 80 points) (important plus sub items)

9. Support user login and authentication

Interface: GUI or Web

The data is stored in the database or file. It is recommended to store it in the database.

 

2, Introduction to team members and responsible modules

group leader:

• Zhao Yida Jike 19-1

Team members:

• Wang Shuchang Jike 19-3

Team members are responsible for the following modules:

personnel	Responsible module
Zhao Yida	Select the function interface to add, modify, delete and query students' basic information and students' scores, and the program exits the interface
Shu Chang Wang	Database establishment, database connection, program login interface

 

3, Project git address

https://gitee.com/javay1/java

 

4, Screenshot of project git submission record.

 

5, Demand analysis

The system is for school educational administrators, who can manage students' basic information (student number, name, gender, age, major, home address) and achievement information, mainly adding, deleting, modifying and querying relevant information.

 

6, Project flow chart

 

7, Screenshot of project operation

Sign in:

Add basic student information:

Delete student basic information:

Modify basic student information:

Add student grade:

Delete student grade:

Modify student grade:

Query students' Basic & score information:

Exit:

8, Project key code

Database connection / shutdown:

public class DbUtil {
	private String dbUrl="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=db "; / / database connection address
	private String dbUserName="zyy"; // Database user name
	private String dbPassword="123456"; // password
	private String jdbcName="com.microsoft.sqlserver.jdbc.SQLServerDriver"; // Drive name
	/**
	 * Get database connection
	 */
	public Connection getCon()throws Exception{
		Class.forName(jdbcName);                  //Load driver class
		Connection con=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);      //Get data connection
		return con;
	}
	/**
	 * Close database connection
	 */
	public void closeCon(Connection con)throws Exception{
		if(con!=null){
			con.close();
		}
	}
	
	public static void main(String[] args) {
		DbUtil dbUtil=new DbUtil();
		try {
			dbUtil.getCon();
			System.out.println("Database connection succeeded!");
		} catch (Exception e) {
			e.printStackTrace();         //Error location and cause of printing exception information on the command line in the program
			System.out.println("Database connection failed");
		}
	}
}

Student information operation method:

public class StudentDao {

	/**
	 * student
	 */
	public int add(Connection con,Student student)throws Exception{
		String sql="insert into Student(Sno,Sname,Sex,Sage,Sdept,Saddr) values(?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, student.getSn());
		pstmt.setString(2, student.getName());
		pstmt.setString(3, student.getSex());
		pstmt.setString(4, student.getage());
		pstmt.setString(5, student.getDept());
		pstmt.setString(6, student.getAddress());
		return pstmt.executeUpdate();
	}

	
	/**
	 * Student information deletion
	 */
	public int delete(Connection con,String id)throws Exception{
		String sql="delete from Student where Sno=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, id);
		return pstmt.executeUpdate();
	}
	
	/**
	 * Student information modification
	 */
	public int update(Connection con,Student student)throws Exception{
		String sql="update Student set Sname=?,Sex=?,Sage=?,Sdept=?,Saddr=? where Sno=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, student.getName());
		pstmt.setString(2, student.getSex());
		pstmt.setString(3, student.getage());
		pstmt.setString(4, student.getDept());
		pstmt.setString(5, student.getAddress());
		pstmt.setString(6, student.getSn());
		return pstmt.executeUpdate();
	}

}

Operation method of score information:

public class ClassDao {

	/**
	 add to
	 */
	public int add(Connection con,Classs Classs)throws Exception{
		String sql="insert into SC(Sno,Cno,Grade) values(?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, Classs.getsno());
		pstmt.setString(2,Classs.getCno());
		pstmt.setString(3, Classs.getgrade());
		return pstmt.executeUpdate();//Returns the number of updated rows after the INSERT, UPDATE or DELETE statement is executed; A return of 0 indicates that the SQL statement was not executed successfully.
	}
	
	
	/**
	 * delete

	 */
	public int delete(Connection con,Classs classs)throws Exception{
		String sql="delete from SC where Sno=? and Cno=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, classs.getsno());
		pstmt.setString(2, classs.getCno());
		return pstmt.executeUpdate();
	}
	
	/**
	 * to update
	 */
	public int update(Connection con,Classs sClass)throws Exception{
		String sql="update SC set Grade=? where Sno=? and Cno=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, sClass.getgrade());
		pstmt.setString(2, sClass.getsno());
		pstmt.setString(3, sClass.getCno());
		return pstmt.executeUpdate();
	}
}

Joint query method of student basic information and score information:

	
	/**
	 * Student information query
	 */
	public ResultSet list(Connection con,Student student)throws Exception{
		StringBuffer sb=new StringBuffer("select * from Student b,SC bt where b.Sno=bt.Sno");
		if(StringUtil.isNotEmpty(student.getName())){
			sb.append(" and b.Sname like '%"+student.getName()+"%'");
		}
		if(StringUtil.isNotEmpty(student.getSn())){
			sb.append(" and b.Sno like '%"+student.getSn()+"%'");
		}
		if(student.getage()!=null){
			sb.append(" and b.Sage="+student.getage());
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}