学生管理系统模块化编程

项目介绍：Java基础+mysql的一个简单练习

一.数据库

二.项目结构 

 

 lib下的jar包下载地址：Maven Repository: mysql » mysql-connector-java (mvnrepository.com)

1.db.properties 

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
user=root
password=123456

2.Student类

package pojo;public class Student {private int id;private String name;private int score;public Student(int id, String name, int score) {this.id = id;this.name = name;this.score = score;}public Student() {}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getScore() {return score;}public void setScore(int score) {this.score = score;}@Overridepublic String toString() {return "Student{" +"id=" + id +", name='" + name + '\'' +", score=" + score +'}';}
}

3.JDBCUtil工具类

package util;import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import pojo.Student;import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;public class JDBCUtil {private static String driver;private static String url;private static String user;private static String password;private static Connection connection = null;static {Properties properties = new Properties();InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("resources/db.properties");try {properties.load(inputStream);} catch (IOException e) {throw new RuntimeException(e);}driver = properties.getProperty("driver");url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");try {Class.forName(driver);connection = (Connection) DriverManager.getConnection(url,user,password);} catch (Exception e) {e.printStackTrace();}}// 编写公共查询类public static ArrayList<Student> executeQuery(String sql) {ResultSet resultSet = null;Statement statement = null;ArrayList<Student> arrayList = null;try {statement = (Statement) connection.createStatement();resultSet = statement.executeQuery(sql);arrayList = new ArrayList<>();while (resultSet.next()) {arrayList.add(new Student(resultSet.getInt(1),resultSet.getString(2),resultSet.getInt(3)));}return arrayList;} catch (SQLException e) {throw new RuntimeException(e);} finally {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {throw new RuntimeException(e);}}if (statement != null) {try {statement.close();} catch (SQLException e) {throw new RuntimeException(e);}}}}// 编写公共删除，增加，更改类public static int executeUpdate(String sql) {Statement statement = null;try {statement = (Statement) connection.createStatement();int update = statement.executeUpdate(sql);return update;} catch (SQLException e) {throw new RuntimeException(e);} finally {if (statement != null) {try {statement.close();} catch (SQLException e) {throw new RuntimeException(e);}}}}
}

 4.Dao层

 StudentDao

package dao;public interface StudentDao {// 查询全部public void searchAll();// 名字查找public void searchByName(String name);// 学号查找public void searchById(int id);// 根据学号删除public void deleteById(int Id);// 根据学号更新名字public void updateByName(int id,String name);// 根据学号更新成绩public void updateByScore(int id,int score);// 新增用户public void add(String name,int score);}

 StudentDaoImpl

package dao;import pojo.Student;
import util.JDBCUtil;import java.util.ArrayList;public class StudentDaoImpl implements StudentDao{@Overridepublic void searchAll() {String sql = "select * from student;";ArrayList<Student> arrayList = JDBCUtil.executeQuery(sql);for(Student student: arrayList) {System.out.println(student.toString());}}@Overridepublic void searchByName(String name) {try {StringBuilder sql = new StringBuilder();sql.append("select * from student where name = ");sql.append("'");sql.append(name);sql.append("';");System.out.println(JDBCUtil.executeQuery(sql.toString()).toString());} catch (NullPointerException e) {System.out.println("用户不存在");}}@Overridepublic void searchById(int id) {try {StringBuilder sql = new StringBuilder();sql.append("select * from student where id = ");sql.append(id);System.out.println(JDBCUtil.executeQuery(sql.toString()).toString());} catch (NullPointerException e) {System.out.println("用户不存在");}}@Overridepublic void deleteById(int id) {StringBuilder sql = new StringBuilder();sql.append("delete from student where id=");sql.append(id);if (JDBCUtil.executeUpdate(sql.toString()) > 0) {System.out.println("删除成功");}}@Overridepublic void updateByName(int id, String name) {StringBuilder sql = new StringBuilder();sql.append("update student set name=");sql.append("'");sql.append(name);sql.append("'");sql.append(" where id = ");sql.append(id);sql.append(";");if (JDBCUtil.executeUpdate(sql.toString()) > 0) {System.out.println("name修改成功");}}@Overridepublic void updateByScore(int id, int score) {StringBuilder sql = new StringBuilder();sql.append("update student set score=");sql.append(score);sql.append(" where id = ");sql.append(id);sql.append(";");if (JDBCUtil.executeUpdate(sql.toString()) > 0) {System.out.println("score修改成功");}}@Overridepublic void add(String name, int score) {StringBuilder sql = new StringBuilder();sql.append("insert student(name,score) values('");sql.append(name);sql.append("',");sql.append(score);sql.append(");");if (JDBCUtil.executeUpdate(sql.toString()) > 0) {System.out.println("添加成功,新用户如下");String sql2 = "select * from student order by id desc limit 0,1;";System.out.println(JDBCUtil.executeQuery(sql2).toString());}}
}