one , System description
1, Introduce the functions of the developed system ： The student information management system can , Jointly delete student information , At the same time, it can also add students' personal information , modify , Delete and query functions
2, Problems solved ： Connect to the database through , Display student information on the running interface , And perform various functions
two , System analysis and design
1, Software requirements ;eclipse,MySQL,Navicat for MySQL
2, functional design ; Student information including student number , full name , password , type （ Judge whether it is an administrator or a student by type ）, Enter the corresponding interface
chart 1-1 Function structure diagram of student information management system
3, Database design （ Or data structure design ）; In the created database Studentgradessystem Student information table in （info) This table passes Navicat for
MySQL To build a very simple , Connected MySQL after , Create a new table
chart 1-2 Student information sheet
4, System detailed design document （ Classification and interrelation of classes , interface design , Algorithm design, etc ）;
1,com.tjl.dbc------------------ Database connection
JDBCUtils.java---------- encapsulation JDBC Tools , connect , Free resources
package com.tjl.dbc; import java.io.IOException; import java.io.InputStream;
import java.sql.Connection; import java.sql.DriverManager; import
java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;
import java.util.Properties; import javax.sql.ConnectionEvent; public class
JDBCUtils { private static String driver; private static String url; private
static String username; private static String sex; private static String
userpassword; // Static statement block static { //JDBCUtils.class Used to get objects //getClassLoader() Class loader
//getResourceAsStream("db.properties") Load resource file into output stream // Read this file as a stream InputStream
resourceAsStream =
JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
// Create a properties Object of Properties p=new Properties(); // Load stream file try {
p.load(resourceAsStream); driver = p.getProperty("driver");// Read this driver
And assign it to a variable driver Is the name of the driver I want url=p.getProperty("url");
username=p.getProperty("username"); userpassword=p.getProperty("userpassword");
Class.forName(driver);// load MySQL drive System.out.println(" Driver loaded successfully "); } catch
(Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }
// Method to get object public static Connection getConnection() { try {
System.out.println(" Database connection succeeded "); System.out.println(); //return
DriverManager.getConnection(url,username,userpassword); return
DriverManager.getConnection("jdbc:mysql://localhost:3306/StudentgradesSystem","root","msh8426pou");
}catch(SQLException e) { System.out.println(" Database connection failed "); e.printStackTrace(); }
return null; } // Methods of releasing resources public static void close(Connection
connection,Statement statement,ResultSet result) { try {
if(result!=null)// Judge first , Is this result set empty . If the result set is not empty , Turn off the results { result.close();
result=null;// Here java Results will be recycled , Assignment is null , Create before close } if(statement!=null) {
statement.close(); statement=null; } if(connection!=null) { connection.close();
connection=null; } } catch (SQLException e) { // TODO Auto-generated catch
block e.printStackTrace(); } } }
2,com.tjl.view----------------- View package
View.java----------------- Contains various view menu classes
package com.tji.view; import java.util.Scanner; import com.tji.bean.User;
public class View { private static Scanner input=new Scanner(System.in); // Home page view
public static User indexView() {
System.out.println("***********************************************************");
System.out.println("*************\t\t Student achievement management system \t\t***********");
System.out.println("*************\t\t Enter the following information according to \t\t***********");
System.out.println("*************\t\t Please enter the account number \t\t***************"); String
username = input.nextLine();// Return its name according to the account number entered by the user
System.out.println("**************\t\t Please input a password \t\t**************");
System.out.println("*************************************************************");
String userpassword = input.nextLine(); return new User(username,userpassword);
} // Administrator menu view public static int managerMeunView() {
System.out.println("***********************************************************");
System.out.println("***********************************************************");
System.out.println("*************\t\t Welcome back administrator \t\t***********");
System.out.println("*************\t\t Please follow the prompts \t\t***********");
System.out.println("*************\t0. sign out \t\t***********");
System.out.println("*************\t1. Add student information \t\t***********");
System.out.println("*************\t2. Delete student information \t\t***********");
System.out.println("*************\t3. Modify student information \t\t***********");
System.out.println("*************\t4. Query student information \t\t***********");
System.out.println("***********************************************************");
System.out.println("***********************************************************");
// Get user input information String type=input.nextLine(); // Convert the acquired data to int type int item =
Integer.parseInt(type);
// Judge whether the user input is in the 0~4 between , If out of range , Prompt user " Input error , Please re-enter ", And stay on the view page , If input is correct , Return user entered options
if(item<0||item>4) { System.out.println(" Input error , Please re-enter !"); return
managerMeunView();// Method callback } // If input is correct , The options entered by the user are returned
System.out.println("**************************************************************");
return item; } // Add student information view , Return to a new User object （username,userpassword） public static
User addMeunView() {
System.out.println("***************************************************************");
System.out.println("***************************************************************");
System.out.println("*************\t\t Please follow the prompts \t\t***********");
System.out.println("*************\t\t Please enter the account number \t\t***************"); String
username = input.nextLine();// Return its name according to the account number entered by the user
System.out.println("**************\t\t Please input a password \t\t**************");
System.out.println("*************************************************************");
System.out.println("*************************************************************");
String userpassword = input.nextLine(); return new User(username,userpassword);
} // Delete student information public static String deleteMeunView() {
System.out.println("***************************************************************");
System.out.println("*************\t\t Please follow the prompts \t\t***********");
System.out.println("*************\t\t Please enter the student account to delete \t\t***************"); String
username = input.nextLine();// Return its name according to the account number entered by the user
System.out.println("*************************************************************");
return username; } // Modify student information public static User updateMeunView() {
System.out.println("***************************************************************");
System.out.println("*************\t\t Please follow the prompts \t\t***********");
System.out.println("*************\t\t Please enter the account to be modified \t\t***************"); String
username = input.nextLine();// Return its name according to the account number entered by the user
System.out.println("**************\t\t Please enter a new password \t\t**************"); String
userpassword = input.nextLine();
System.out.println("*************************************************************");
return new User(username,userpassword); } // Query student information public static String
selectMeunView() {
System.out.println("***************************************************************");
System.out.println("*************\t\t Please follow the prompts \t\t***********");
System.out.println("*************\t\t Please enter the student account to be queried \t\t***************"); String
username = input.nextLine();// Return its name according to the account number entered by the user
System.out.println("*************************************************************");
return username; } // Methods of outputting student information public static void printUser(User user) {
System.out.println(" User's id："+user.getId());
System.out.println(" user name ："+user.getUsername());
System.out.println(" User password ："+user.getUserpassword()); if(user.getType()==1) {
System.out.println(" User rights ： administrators "); } else { System.out.println(" User rights : student "); } } }
3,com.tjl.test------------------ Test package
JDBCUtilsTest.java--------- test mysql Execute statement class
package com.tjl.test; import static org.junit.Assert.*; import
java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement;
import org.junit.Test; import com.tji.bean.User; import
com.tjl.dao.UserDao_Imp; import com.tjl.dbc.JDBCUtils; public class
JDBCUtilsTest { @Test public void jdbcConnectionTest() throws Exception {
Connection connection =
JDBCUtils.getConnection();// Call directly through the class name point getConnection This method . Will return a jdbcConnectionTest Object of
Statement statement =
connection.createStatement();// Create an execution environment , Get an execution object called statement ResultSet result =
statement.executeQuery("SELECT * FROM
user");// Take it statement implement sequel sentence , The result of the query is in the result set , Object of the result result Record it
while(result.next())// Query the following results , If there is any result, output it {
System.out.print(result.getString("id"));
System.out.print(result.getString("username"));
System.out.println(result.getString("userpassword")); } } // Test login system @Test public
void loginTest() throws Exception { UserDao_Imp userDao_Imp=new
UserDao_Imp();// Instantiate one first UserDao_Imp Object of , Use this object to call login method , There's one inside user, You need to instantiate a user Object of
User user=new User(" Wangmengmeng ","12345"); int type = userDao_Imp.login(user);
System.out.println(type); } // Test add student information @Test public void insertTest() throws
Exception { UserDao_Imp userDao_Imp = new UserDao_Imp();//new An object was , And return a value User
user = new User(" Li Kui ","12345"); boolean flag =
userDao_Imp.insert(user);// Take this object and call insert() This method System.out.println(flag); }
// Test deleting student information @Test public void deleteTest() throws Exception { UserDao_Imp
userDao_Imp = new UserDao_Imp(); User user = new User(" Li Kui ","12345"); boolean
flag = userDao_Imp.delete(user.getUsername()); System.out.println(flag); }
// Test and modify student information @Test public void updateTest() throws Exception { UserDao_Imp
userDao_Imp = new UserDao_Imp(); User user = new User(" Li Kui ","1234"); boolean
flag = userDao_Imp.update(user); System.out.println(flag); } // Test query student information @Test
public void selectTest() throws Exception { UserDao_Imp userDao_Imp = new
UserDao_Imp(); User user = new User(" Li Kui ","1234"); User username =
userDao_Imp.select(user.getUsername()); System.out.println(username); } }
ViewTest.java----------- Test classes for various views
package com.tjl.test; import static org.junit.Assert.*; import org.junit.Test;
import com.tji.bean.User; import com.tji.view.View; public class ViewTest {
@Test// Test login page public void indexViewTest() throws Exception { User user1 =
View.indexView(); System.out.println(user1); } // Test administrator menu view @Test public void
managerMeunViewTest() throws Exception { int item=
View.managerMeunView();// call View In class managerMeunViewTest() method , Return one int Value of type
System.out.println(item); } // Test add student information view @Test public void addMeunViewTest()
throws Exception { User user = View.addMeunView(); System.out.println(user); }
// Test delete student information view , @Test public void deleteMeunViewTest() throws Exception { String
user = View.deleteMeunView(); System.out.println(user);
System.out.println(" Deletion succeeded "); } // Test modify student information view @Test public void
updateMeunViewTest() throws Exception { User user = View.updateMeunView();
System.out.println(user); System.out.println(" Modified successfully "); } // Test query student information view @Test
public void selectMeunView() throws Exception { String user =
View.selectMeunView(); System.out.println(user); } }
4,com.tjl.bean---------------- User class
User.java----------------- Encapsulation class
package com.tji.bean; public class User { private int id; private String
username; private String userpassword; private int type; public User() {
super(); } public User(String username, String userpassword) { super();
this.username = username; this.userpassword = userpassword; } public
User(String username, String userpassword, int type) { super(); this.username =
username; this.userpassword = userpassword; this.type = type; } public User(int
id, String username, String userpassword, int type) { super(); this.id = id;
this.username = username; this.userpassword = userpassword; this.type = type; }
/** * @return the id */ public int getId() { return id; } /** * @param id the
id to set */ public void setId(int id) { this.id = id; } /** * @return the
username */ public String getUsername() { return username; } /** * @param
username the username to set */ public void setUsername(String username) {
this.username = username; } /** * @return the userpassword */ public String
getUserpassword() { return userpassword; } /** * @param userpassword the
userpassword to set */ public void setUserpassword(String userpassword) {
this.userpassword = userpassword; } /** * @return the type */ public int
getType() { return type; } /** * @param type the type to set */ public void
setType(int type) { this.type = type; } }
5,com.tjl.dao------------------ Implementation class for database access
UserDao.java------------ Interface （ Sign in , increase , Delete , change , check ）
package com.tjl.dao; import com.tji.bean.User; public interface UserDao {
/** How to verify login * @param user Is the user to log in ,username account ,userpassword password * @return int type -1 Login failed
1 Administrator login 2 Student login */ int login(User user);// Abstract method /** An abstract method for adding student information * @param
user Objects to add , Include account and password * @return If the addition is successful, put it back true, Add Failure Return false */ boolean insert(User
user); /** An abstract method for deleting student information * @param username Account and password in the student to be deleted * @return
If the deletion is successful , Then return true, If deletion fails , Then return false */ boolean delete (String username);
/** Methods for modifying student information * @param user Is the student information to be modified * @return Returned if the modification is successful true, If modification fails, return false */
boolean update(User user); /** Abstract methods for querying students * @param username Is the information of the student to be queried * @return
query was successful , Then return user, Return if query fails null */ User select(String username); }
UserDao_Imp.java----- Implementation class of interface （mysql Specific execution statement of ）
package com.tjl.dao; import java.sql.Connection; import
java.sql.PreparedStatement; import java.sql.ResultSet; import
java.sql.SQLException; import java.sql.Statement; import com.tji.bean.User;
import com.tjl.dbc.JDBCUtils; public class UserDao_Imp implements UserDao {
private static final String SQL_USER_LOGIN="SELECT type from user WHERE
username=? AND userpassword=?"; private static final String SQL_USER_INSERT =
"INSERT into `user` VALUES(id,??2)";// This constant is used to store this SQL sentence private static final
String SQL_USER_DELETE ="DELETE FROM `user`WHERE username=?"
;// This constant is used to put the name of the student I want to delete private static final String SQL_USER_UPDATE = "UPDATE
`user`SET userpassword= ?WHERE username=?"; private static final String
SQL_USER_SELECT = "SELECT *FROM `user` WHERE username=?";
// For dynamic parameters ? replace , The first question mark needs to be user Name of , the second user To get the password of the account @Override public int login(User
user) { // Connect to database , Create connection object connection Connection connection =
JDBCUtils.getConnection(); // Create execution environment statement // implement sql Statement Query method , Get result set object result
PreparedStatement prepareStatement =null; ResultSet result=null; try {
// Create precompiled environment prepareStatement =
connection.prepareStatement(SQL_USER_LOGIN);// Create precompiled environment ,SQL_USER_LOGIN Is a temporarily set constant
// set up SQL Parameters in statement prepareStatement.setString(1,user.getUsername());
prepareStatement.setString(2,user.getUserpassword()); // Execute statement result =
prepareStatement.executeQuery(); while(result.next()) { // Query current user permissions with result set int
type=result.getInt("type"); return type; } } catch (SQLException e) { // TODO
Auto-generated catch block e.printStackTrace(); } finally {
JDBCUtils.close(connection,prepareStatement,result);// close resource } return -1; }
@Override public boolean insert(User user) { //INSERT into `user`
VALUES(id," Highlight ","12345",2) // Connect to database , Create connection object connection Connection connection =
JDBCUtils.getConnection(); // Create execution environment PreparedStatement prepareStatement =null;
try { prepareStatement = connection.prepareStatement(SQL_USER_INSERT);
// set up SQL Parameters in statement
prepareStatement.setString(1,user.getUsername());// The first parameter should be replaced by the name of the newly added user
prepareStatement.setString(2,user.getUserpassword());// The second parameter is to be replaced by the user's password int line
=
prepareStatement.executeUpdate();// Returns the number of rows affected , If Line Value is greater than 0 Returns a true, If less than 0 Then return false
if(line>0) { return true; } } catch (SQLException e) { // TODO Auto-generated
catch block e.printStackTrace(); }finally {
JDBCUtils.close(connection,prepareStatement,null); } return false; } @Override
public boolean delete(String username) { // Connect to database , Create connection object connection Connection
connection = JDBCUtils.getConnection(); // Create execution environment PreparedStatement
prepareStatement =null; try { prepareStatement =
connection.prepareStatement(SQL_USER_DELETE); // set up SQL Parameters in statement
prepareStatement.setString(1,username);// The first parameter should be replaced by the name of the newly added user int line =
prepareStatement.executeUpdate();// Returns the number of rows affected , If Line Value is greater than 0 Returns a true, If less than 0 Then return false
if(line>0) { return true; } } catch (SQLException e) { // TODO Auto-generated
catch block e.printStackTrace(); }finally {
JDBCUtils.close(connection,prepareStatement,null); } return false; } @Override
public boolean update(User user) { Connection connection =
JDBCUtils.getConnection(); // Create execution environment PreparedStatement prepareStatement =null;
try { prepareStatement = connection.prepareStatement(SQL_USER_UPDATE);
// set up SQL Parameters in statement
prepareStatement.setString(1,user.getUserpassword());// The first parameter is to be replaced with the password of the modified user
prepareStatement.setString(2,user.getUsername());// The first parameter is to be replaced with the name of the modified user int line =
prepareStatement.executeUpdate();// Returns the number of rows affected , If Line Value is greater than 0 Returns a true, If less than 0 Then return false
if(line>0) { return true; } } catch (SQLException e) { // TODO Auto-generated
catch block e.printStackTrace(); }finally {
JDBCUtils.close(connection,prepareStatement,null); } return false; } @Override
public User select(String username) { Connection connection =
JDBCUtils.getConnection(); // Create execution environment PreparedStatement prepareStatement =null;
try { prepareStatement = connection.prepareStatement(SQL_USER_SELECT);
// set up SQL Parameters in statement prepareStatement.setString(1, username);;// The first parameter is to be replaced with the password of the user to be queried
ResultSet result =
prepareStatement.executeQuery();// Returns the number of rows affected , If Line Value is greater than 0 Returns a true, If less than 0 Then return false
while(result.next()) { int id = result.getInt("id"); username =
result.getString("username");// When transferring parameters, a username, No more duplicate definitions String userpassword
= result.getString("userpassword"); int type =
result.getInt("type");// Get type , If it is 1 Is an administrator , If it is 2 Students return new
User(id,username,userpassword,type); } } catch (SQLException e) { // TODO
Auto-generated catch block e.printStackTrace(); }finally {
JDBCUtils.close(connection,prepareStatement,null); } return null; } }
6,com.tjl.controller---------- Control layer
Control.java------------- Entrance to the system , Control business logic and call
package com.tjl.controller; import com.tji.bean.User; import
com.tji.view.View; import com.tjl.dao.UserDao_Imp; public class Control {
public static void main(String args[]) { while(true) { // Show the user the login interface User user =
View.indexView();// Get the information of the person to log in , Compare it with the database UserDao_Imp userDao_Imp = new
UserDao_Imp(); int type =
userDao_Imp.login(user);// This value returns type Value of , If it is 1 Is an administrator , Enter the add / delete / modify query interface , If it is 2 Students
switch(type) { case -1: System.out.println(" Input error , Please re-enter !"); break; case 1:
System.out.println(" Welcome back administrator !"); managerServer(); break; case 2:
System.out.println(" Congratulations on your successful login !"); studentServer(); default: break; } } } private
static void managerServer() { UserDao_Imp userDao_Imp = new UserDao_Imp();
while(true) { // Show administrator menu view int managerMeunView =
View.managerMeunView();// Returns an integer value , Depending on the number selected by the administrator boolean flag;
switch(managerMeunView) { case 0:// sign out System.exit(-1); break; case 1:// Add student information
User user = View.addMeunView();// Add returned student user flag =
userDao_Imp.insert(user);// Successfully added , Returns a Boolean value , Each one must return a value , Define a flag
System.out.println(flag?" Successfully added ":" Add failed "); break; case 2:// Delete student information String username
= View.deleteMeunView();// Returned name of the deleted account flag = userDao_Imp.delete(username);
System.out.println(flag?" Deletion succeeded ":" Delete failed "); break; case 3:// Modify student information User
updateusername = View.updateMeunView();// Return to the student information to be modified flag =
userDao_Imp.update(updateusername); System.out.println(flag?" Modified successfully ":" Modification failed ");
break; case 4:// Query student information String selectusername =
View.selectMeunView();// Return the student account to query User selectedUser=
userDao_Imp.select(selectusername);// Return the student information found
View.printUser(selectedUser);// Claim a way to output the student information break; default: break; } } }
private static void studentServer() { // TODO Auto-generated method stub } }
5, Implementation method description of each module ;
（1） load JDBC drive ——> Instantiate connection driven classes , And register it with DriverManger in ——> Connect to database , And obtain Connection object ——> Access database ——> Process result set ——> Close database connection
（2） Design of login page
（3） Realization and test of each function
（4） User information , Based on the name entered by the user , Password return database comparison , Find the permissions of the entered user , If it is 1, Is an administrator , Enter the administrator interface , If it is 2, Students , Then enter the student interface , If return -1, Input error , Prompt for re-entry
three , Design summary
In the student information management system , Focus on deepening learning types , variable , Interface , Input / output stream , Analysis exception , Throw exception , Interface design , For databases and Java We have a deeper understanding of the connection between , Write a system , Need clear thinking , There is no way to start from the beginning , You can know how many modules need to be written later . The most difficult part in the process of writing is to modify and delete this module . When the program is modified , Compilation always fails , And I don't know what went wrong , At this time, I need to find someone else to discuss and solve this problem with me . There are many practical problems in this design , Found in actual design , There are still some differences between theoretical things in books and those in practical use , So some problems should not only be deeply understood , But also in solving problems in programming , And programming is a very flexible thing , It reflects your logical thinking and innovative ability to solve problems . Through this course design, I also found my own shortcomings , Although in theory , But in the process of practice, there will be unexpected confusion , It took a lot of effort to solve the problem .
Technology