【数据库】学生管理系统

红太狼 2022-11-25 11:56 228阅读 0赞

> ☞本项目主要练习MySQL数据库和java代码之间的连通☜  
> ☞后续会在本基础上，利用Spring实现WEB端☜  
> ☞梦想进大厂的一只程序猿☜  
> ☞期望毕业前力扣刷够400题☜  
> ☞正在复习数据结构和算法☜  
> ☞博客地址：[https://www.huangliangshuai.com/][https_www.huangliangshuai.com]☜

# 1. 项目架构图 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70]

# 2. 系统分析 #

## 2.1. 数据库 ##

### 2.1.1. 表student ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 1]

## 2.2 java端 ##

![在这里插入图片描述][20200808215646578.png]

### 2.2.1 JBDC ###

#### 2.2.1.1JDBC连接 ####

package com.situ.student.util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ResourceBundle;
    
    public class JDBCUtil {
        
    
    	private static ResourceBundle rb = ResourceBundle.getBundle("com.situ.student.util.JDBC");
    	private static String cLassName = rb.getString("driver");
    	private static String url = rb.getString("url");
    	private static String user = rb.getString("user");
    	private static String password = rb.getString("pass");
    
    	// 1- 加载驱动
    	static {
        
    		try {
        
    			Class.forName(cLassName);
    		} catch (Exception e) {
        
    			e.getCause();
    		}
    	}
    
    	// 2- 获得链接
    	public static Connection getConnection() throws SQLException {
        
    		return DriverManager.getConnection(url, user, password);
    	}
    
    	public static void close(Connection conn, Statement st, ResultSet rs) {
        
    		try {
        
    			if (rs != null) {
        
    				rs.close();
    				rs = null;
    			}
    			if (st != null) {
        
    				st.close();
    				st = null;
    			}
    			if (conn != null) {
        
    				conn.close();
    				conn = null;
    			}
    		} catch (SQLException e) {
        
    			e.printStackTrace();
    		}
    	}
    
    	public static void close(Connection conn, Statement st) {
        
    		close(conn, st, null);
    	}
    
    	public static void main(String[] args) throws SQLException {
        
    		// 加载驱动 + 获得链接
    		Connection con = JDBCUtil.getConnection();
    		System.out.println(con);
    		JDBCUtil.close(con, null); // 关闭的是插入的
    	}
    
    	private JDBCUtil() {
        
    
    	}
    }

#### 2.2.1.1JDBC配置文件 ####

pass=123456
    
    driver=com.mysql.cj.jdbc.Driver
    
    url=jdbc:mysql://127.0.0.1:3308/demo01?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Hongkong&allowPublicKeyRetrieval=true

### 2.2.2 student包 ###

#### 2.2.2.1 Model ####

package com.situ.student.student.model;
    
    public class StudentModel {
        
    	private String id;
    	private String name;
    	private String age;
    	private String profession;
    
    	public String getId() {
        
    		return id;
    	}
    
    	public void setId(String id) {
        
    		this.id = id;
    	}
    
    	public String getName() {
        
    		return name;
    	}
    
    	public void setName(String name) {
        
    		this.name = name;
    	}
    
    	public String getAge() {
        
    		return age;
    	}
    
    	public void setAge(String age) {
        
    		this.age = age;
    	}
    
    	public String getProfession() {
        
    		return profession;
    	}
    
    	public void setProfession(String profession) {
        
    		this.profession = profession;
    	}
    
    	@Override
    	public String toString() {
        
    		return "StudentModel [id=" + id + ", name=" + name + ", age=" + age + ", profession=" + profession + "]";
    	}
    
    }

#### 2.2.2.2 Dao ####

package com.situ.student.student.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 com.situ.student.student.model.StudentModel;
    import com.situ.student.util.JDBCUtil;
    
    public class StudentDao {
        
    	private Connection connection = null;
    	private PreparedStatement ps = null;
    
    	// 增加
    
    	public String insert(StudentModel model) {
        
    		String sql = "insert into student(s_id, name, age, profession) value(?,?,?,?)";
    		try {
        
    			connection = JDBCUtil.getConnection(); // 获得链接
    			ps = connection.prepareStatement(sql); // 执行SQL
    			ps.setString(1, model.getId()); // 填充问号
    			ps.setString(2, model.getName());
    			ps.setString(3, model.getAge());
    			ps.setString(4, model.getProfession());
    			int res = ps.executeUpdate(); // 返回改变的记录条数
    			return res + "";
    		} catch (SQLException e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	// 删除
    	public String delete(StudentModel model) {
        
    		String sql = "delete from student where  s_id = ?";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setString(1, model.getId());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	// 修改
    	public String update(StudentModel model) {
        
    		String sql = "update student set profession = ? where s_id = ?";
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql);
    			ps.setString(1, model.getProfession());
    			ps.setString(2, model.getId());
    			int res = ps.executeUpdate();
    			return res + "";
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return -1 + "";
    	}
    
    	// 查询
    	public List<StudentModel> select(StudentModel model) {
        
    		StringBuffer sql = new StringBuffer("select s_id, name, age, profession from student where 1 = 1");
    		List<Object> list = new ArrayList<Object>();
    		list = where(sql, model);
    		List<StudentModel> list2 = new ArrayList<StudentModel>();
    		ResultSet set = null;
    		try {
        
    			connection = JDBCUtil.getConnection();
    			ps = connection.prepareStatement(sql.toString());
    			for (int i = 0; i < list.size(); i++) {
        
    				ps.setObject(i + 1, list.get(i));
    			}
    			set = ps.executeQuery();
    			while (set.next()) {
        
    				StudentModel model1 = new StudentModel();
    				model1.setId(set.getString("s_id"));
    				model1.setName(set.getString("name"));
    				model1.setAge(set.getString("age"));
    				model1.setProfession(set.getString("profession"));
    				list2.add(model1);
    			}
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		} finally {
        
    			JDBCUtil.close(connection, ps);
    		}
    		return list2;
    	}
    
    	public List<Object> where(StringBuffer sql, StudentModel model) {
        
    		String s_id = model.getId();
    		List<Object> list = new ArrayList<Object>();
    		if (s_id != null && !s_id.trim().isEmpty()) {
        
    			sql.append(" and s_id=?");
    			list.add(s_id);
    		}
    		String name = model.getName();
    		if (name != null && !name.trim().isEmpty()) {
        
    			sql.append(" and name = ?");
    			list.add(name);
    		}
    		String age = model.getAge();
    		if (age != null && !age.trim().isEmpty()) {
        
    			sql.append(" and age = ?");
    			list.add(age);
    		}
    		String profession = model.getProfession();
    		if (profession != null && !profession.trim().isEmpty()) {
        
    			sql.append(" and profession = ?");
    			list.add(profession);
    		}
    		return list;
    	}
    }

#### 2.2.2.3 Service ####

package com.situ.student.student.service;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import com.situ.student.student.dao.StudentDao;
    import com.situ.student.student.model.StudentModel;
    
    public class StudentService {
        
    	private StudentDao dao = new StudentDao();
    
    	public String insert(StudentModel model) {
        
    		StudentModel model2 = new StudentModel();
    		model2.setId(model.getId());
    		List<StudentModel> list = new ArrayList<StudentModel>();
    		list = dao.select(model);
    		if (list == null || list.isEmpty()) {
        
    			dao.insert(model);
    			return "插入成功！";
    		}
    		return "已经存在！";
    	}
    
    	public String delete(StudentModel model) {
        
    		return dao.delete(model);
    	}
    
    	public String update(StudentModel model) {
        
    		return dao.update(model);
    
    	}
    
    	public List<StudentModel> selectList(StudentModel model) {
        
    		return dao.select(model);
    	}
    
    	public StudentModel selcetModel(StudentModel model) {
        
    		StudentModel model2 = new StudentModel();
    		model2.setId(model.getId());
    		List<StudentModel> list = new ArrayList<StudentModel>();
    		list = dao.select(model);
    		if (list == null || list.isEmpty()) {
        
    			return null;
    		}
    		return list.get(0);
    	}
    
    }

### 2.2.2 Test ###

package com.situ.student.student.studenttest;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Scanner;
    import com.situ.student.student.model.StudentModel;
    import com.situ.student.student.service.StudentService;
    
    public class StudentTset {
        
    	private Scanner scanner = new Scanner(System.in);
    	boolean p = true;
    	private StudentService service = new StudentService();
    
    	public static void main(String[] args) {
        
    		StudentTset test = new StudentTset();
    		test.start();
    	}
    
    	public void start() {
        
    		while (p) {
        
    			System.out.println("1. 添加学生信息");
    			System.out.println("2. 按学生姓名查询学生信息");
    			System.out.println("3. 修改学生的专业(转专业)");
    			System.out.println("4. 按专业查询学生信息");
    			System.out.println("5. 查询所有人的信息");
    			System.out.println("6. 退出");
    			System.out.println("请输入你要");
    			String string = scanner.nextLine();
    			sart_1(string);
    		}
    	}
    
    	public void sart_1(String string) {
        
    		if (string.equals("1")) {
        
    			addStudent();
    		} else if (string.equals("2")) {
        
    			queryNameStudent();
    		} else if (string.equals("3")) {
        
    			setStudentProfessional();
    		} else if (string.equals("4")) {
        
    			queryProfessionalStudent();
    		} else if (string.equals("5")) {
        
    			querySumInformation();
    		} else if (string.equals("6")) {
        
    			p = false;
    			System.out.println("已为你退出系统！");
    		} else {
        
    			System.out.println("你的输入有误！");
    		}
    	}
    
    	public void addStudent() {
        
    		StudentModel model = new StudentModel();
    		System.out.println("请输入学生的编号：");
    		String id = scanner.nextLine();
    		System.out.println("请输入学生的名字：");
    		String name = scanner.nextLine();
    		System.out.println("请输入学生的年龄：");
    		String age = scanner.nextLine();
    		System.out.println("请输入学生的专业：");
    		String profession = scanner.nextLine();
    		model.setId(id);
    		model.setName(name);
    		model.setAge(age);
    		model.setProfession(profession);
    		System.out.println(service.insert(model));
    	}
    
    	// 按名字进行查询
    	public void queryNameStudent() {
        
    		System.out.println("请输入你要查询的名字：");
    		String name = scanner.nextLine();
    		StudentModel model = new StudentModel();
    		model.setName(name);
    		List<StudentModel> list = new ArrayList<StudentModel>();
    		list = service.selectList(model);
    		for (int i = 0; i < list.size(); i++) {
        
    			System.out.println(list.get(i));
    		}
    	}
    
    	public void querySumInformation() {
        
    		StudentModel model = new StudentModel();
    		List<StudentModel> list = new ArrayList<StudentModel>();
    		list = service.selectList(model);
    		for (int i = 0; i < list.size(); i++) {
        
    			System.out.println(list.get(i));
    		}
    	}
    
    	public void setStudentProfessional() {
        
    		StudentModel model = new StudentModel();
    		System.out.println("请输入你要修改的学生编号：");
    		String id = scanner.nextLine();
    		model.setId(id);
    		System.out.println("请输入你要转的专业：");
    		String profession = scanner.nextLine();
    		model.setProfession(profession);
    		service.update(model);
    	}
    
    	public void queryProfessionalStudent() {
        
    		System.out.println("请输入你要查询的专业：");
    		StudentModel model = new StudentModel();
    		String profession = scanner.nextLine();
    		model.setProfession(profession);
    		List<StudentModel> list = new ArrayList<StudentModel>();
    		list = service.selectList(model);
    		for (int i = 0; i < list.size(); i++) {
        
    			System.out.println(list.get(i));
    		}
    	}
    }

[https_www.huangliangshuai.com]: https://www.huangliangshuai.com/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70]: /images/20221124/fcfbdbc4935241138c60fc5a76a388eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTE1NDM5_size_16_color_FFFFFF_t_70 1]: /images/20221124/c2e268d3ea824e5dbc2e7e43d436dac0.png
[20200808215646578.png]: /images/20221124/61d401a97cc34942b3cbb8c155771afc.png