第18周---基于JSP的数据查询Web系统

实验目的

首先在Mysql中创建相应的学生成绩表，然后基于 JSP+Mysql 做一个简单的学生成绩查询web系统。

下面两个博客作为参考，作者写得很清晰，也有源代码，只需按照说明搭建软件环境和数据库，把代码编译调试通过。

参考网址：

1、学生成绩管理系统3.0(JSP+Servlet+MySQL)

https://www.cnblogs.com/wenruo/p/6351787.html

代码： https://github.com/G-lory/StudentAchievementManagementSystem

2、基于JSP实现的学生成绩管理系统

https://www.write-bug.com/article/1433.html

数据库：

数据库代码如下：

-- 学生表: 学号 密码 姓名
drop table if exists student;
create table student (
	sid int(9) primary key auto_increment,
	spwd varchar(20),
	sname varchar(20)
) AUTO_INCREMENT = 100000000 ;

-- 课程表: 课程号 课程名 学分
drop table if exists course;
create table course (
	cid int(9) primary key auto_increment,
	cname varchar(20),
	credit int
) AUTO_INCREMENT = 100000000 ;

-- 选课表: 学号 课程号 成绩
drop table if exists optcou;
create table optcou (
	sid int(9) not null,
	cid int(9) not null,
	score decimal(5,2) default -1,
	foreign key(sid) references student(sid) on delete cascade on update cascade,
	foreign key(cid) references course(cid) on delete cascade on update cascade,
	primary key(sid, cid)
);


insert into student(spwd,sname) values('1','小一'),('2','小二');
insert into course(cname, credit) values('高等数学',5),('大学英语',2),('线性代数',2),('大学计算机基础',1);
insert into optcou(sid,cid,score) values(100000000,100000000,80), (100000000,100000001,75), (100000000,100000002,80), (100000001,100000000,95);
insert into optcou(sid,cid) values(100000000,100000003);

相关代码：

DBUtil：

package com.wenr.util;

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

public class DBUtil {
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost:3306/studentmanagement?useUnicode=true&characterEncoding=UTF-8";
	private static final String username = "root";
	private static final String password = "123456";

	private static Connection conn;

	static {
		try {
			Class.forName(driver);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException {
		if (conn == null) {
			conn = DriverManager.getConnection(url, username, password);
			return conn;
		}
		return conn;
	}
}

StudentDao：
package com.wenr.dao;

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

import com.wenr.model.Course;
import com.wenr.model.Student;
import com.wenr.util.DBUtil;

public class StudentDao {
	
	public boolean isValid(Student student) {
		Connection conn = null;
		PreparedStatement pstmt = null; 
		ResultSet rs = null;
		
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from student where sid=? and spwd=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, student.getSid());
			pstmt.setString(2, student.getSpwd());
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				student.setSname(rs.getString("sname"));
				return true;
			} else {
				return false;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return false;
	}
	
	public ArrayList<Course> getSelectedCourse(Student student) {
		
		ArrayList<Course> list = new ArrayList<Course>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.getConnection();
			String sql = "select course.cid, cname, credit, score from student, course, optcou where student.sid = optcou.sid and course.cid = optcou.cid and student.sid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, student.getSid());
			rs = pstmt.executeQuery();
			while (rs != null && rs.next()) {
				Course course = new Course();
				course.setCid(rs.getInt(1));
				course.setCname(rs.getString("cname"));
				course.setCredit(rs.getInt("credit"));
				course.setScore(rs.getDouble("score"));
				list.add(course);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return list;
	}
	
	public ArrayList<Student> getAllStudent() {
		
		ArrayList<Student> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from student";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs != null && rs.next()) {
				Student student = new Student();
				student.setSid(rs.getInt(1));
				student.setSpwd(rs.getString(2));
				student.setSname(rs.getString(3));
				list.add(student);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return list;
	}
	

	public boolean deleteCourse(Student student, int cid) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.getConnection();
			String sql = "select score from optcou where sid=? and cid=? and score > 0";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, student.getSid());
			pstmt.setInt(2, cid);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				// 存在成绩 不可以删除
				return false;
			} else {
				// 成绩不存在，可以删除
				if (pstmt != null) pstmt.close();
				sql = "delete from optcou where sid=? and cid=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, student.getSid());
				pstmt.setInt(2, cid);
				pstmt.executeUpdate();
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return false;
		
	}
	
	public boolean addCourse(Student student, int cid) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtil.getConnection();
			String sql = "select * from optcou where sid=? and cid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, student.getSid());
			pstmt.setInt(2, cid);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				// 该同学已经选过该门课 不能重复选择
				return false;
			} else {
				// 没选过 可以选择
				sql = "insert into optcou(sid, cid) values(?, ?)";
				if (pstmt != null) pstmt.close();
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, student.getSid());
				pstmt.setInt(2, cid);
				pstmt.executeUpdate();
				return true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return false;
	}
	
	public void addStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBUtil.getConnection();
			String sql = "insert into student(sname, spwd) values(?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, student.getSname());
			pstmt.setString(2, student.getSpwd());
			pstmt.executeUpdate();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void main(String[] args) {
		StudentDao dao = new StudentDao();
		Student student = new Student();
		student.setSid(100000000);
		student.setSpwd("1");
		dao.isValid(student);
		System.out.println(student.getSname());
		
		ArrayList<Course> list = dao.getSelectedCourse(student);
		for (Course course: list) {
			System.out.println(course.getCname() + "," + course.getCredit() + "," + course.getScore());
		}
		
		dao.deleteCourse(student, 100000003);
		
	}
}

结果展示