Mysql+servlet简单学生管理系统

主要功能：添加班级，学生信息的增删改查

主要用到的知识：MySQL，servlet和MVC设计模式

Util部分：

配置文件：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hkjbase?useUnicode=true&characterEncoding=utf-8
jdbc.user=root
jdbc.password=

package com.neusoft.util;

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;

/**
 * 数据库连接工具类
 * 打开链接
 * 关闭连接
 * */
public class DBUtil {
	private static String driver;
	private static String url;	
	private static String user;
	private static String password;
	static{
		InputStream is=DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
		Properties props=new Properties();
		try {
			props.load(is);
			driver=props.getProperty("jdbc.driver");
			url=props.getProperty("jdbc.url");
			user=props.getProperty("jdbc.user");
			password=props.getProperty("jdbc.password");
			Class.forName(driver);//class类型
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * 获取连接  Connection
	 * **/
	public static Connection getConn(){
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 
	 * 关闭连接 Statement ResultSet
	 * **/
	public static void closeAll(ResultSet rs, Statement st,Connection conn){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(st!=null){
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public static void main(String args[]){
		Connection conn=DBUtil.getConn();
		System.out.println(conn);
	}
}

Dao层：

package com.neusoft.dao;

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

import com.neusoft.pojo.Clazz;
import com.neusoft.util.DBUtil;

public class ClassDao {
	public int insertClass(String classname){
		int result=0;
		String sql="insert into t_class(classname) values(?)";
		Connection conn=DBUtil.getConn();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1,classname);
			result=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(null, pst, conn);
		}
		return result;
	}
	public List<Clazz> getClazzes(){
		//创建一个集合，将查询到的对象封装成对象，放到集合中
		List<Clazz> clazzes=new ArrayList<>();
		Connection conn=DBUtil.getConn();
		Statement st=null;
		ResultSet rs=null;
		String sql="select * from t_class order by id";
		try {
			st=conn.createStatement();
			rs=st.executeQuery(sql);//执行sql
			while(rs.next()){
				Clazz clazz=new Clazz();
				clazz.setId(rs.getInt("id"));
				clazz.setClassname(rs.getString("classname"));
				clazzes.add(clazz);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(rs, st, conn);
		}
		return clazzes;
	}
}

package com.neusoft.dao;

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

import com.neusoft.pojo.Clazz;
import com.neusoft.pojo.student;
import com.neusoft.util.DBUtil;

import javafx.scene.Parent;

public class StudentDao {
	public int insertStudent(String stuno,String stuname,int classid){
		int result =0;
		String sql="insert into t_student(stuno,stuname,classid) values(?,?,?)";
		Connection conn=DBUtil.getConn();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, stuno);
			pst.setString(2, stuname);
			pst.setInt(3, classid);
			result=pst.executeUpdate();//执行
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(null, pst, conn);
		}
		return result;
	}
	public List<student> getStus(){
		List<student> stus=new ArrayList<>();
		Connection conn=DBUtil.getConn();
		Statement st=null;
		ResultSet rs=null;
		String sql="select s.id,stuno,stuname,classname from t_student s join t_class c on s.classid=c.id";
		try {
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next()){
				student stu=new student();
				stu.setId(rs.getInt("id"));
				stu.setStuname(rs.getString("stuname"));
				stu.setStuno(rs.getString("stuno"));
				Clazz clazz=new Clazz();
				clazz.setClassname(rs.getString("classname"));
				stu.setClazz(clazz);
				stus.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(rs, st, conn);
		}
		return stus;
	}
	public int delStudentById(int id){
		int result=0;
		Connection conn=DBUtil.getConn();
		String sql="delete from t_student where id=?";
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, id);
			result=pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(null, pst, conn);
		}
		return result;
		
	}
	public student getStuByID(int id){
		Connection conn=DBUtil.getConn();
		Statement st=null;
		ResultSet rs=null;
		String sql="select s.id,stuno,stuname,classname,classid from t_student s join t_class c on s.classid=c.id where s.id="+id;
		student stu=null;
		try {
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			if(rs.next()){
				stu=new student();
				stu.setId(rs.getInt("id"));
				stu.setStuname(rs.getString("stuname"));
				stu.setStuno(rs.getString("stuno"));
				Clazz clazz=new Clazz();
				clazz.setId(rs.getInt("classid"));
				clazz.setClassname(rs.getString("classname"));
				stu.setClazz(clazz);	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(rs, st, conn);
		}
		return stu;
	}
	public int UpdateStudent(int id,String stuname,int classid){
		int result=0;
		Connection conn=DBUtil.getConn();
		String sql="update t_student set stuname=?,classid=? where id=?";
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, stuname);
			pst.setInt(2,classid);
			pst.setInt(3,id);
			result=pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(null, pst, conn);
		}
		return result;
		
	}
}

实体类：

package com.neusoft.pojo;
/**
 * 实体类，对应数据库中的表
 */
 
public class Clazz {
	private int id;
	private String classname;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getClassname() {
		return classname;
	}
	public void setClassname(String classname) {
		this.classname = classname;
	}
	
}

package com.neusoft.pojo;

public class student {
	private int id;
	private String stuno;
	private String stuname;
	//多对一建模
	private Clazz clazz;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getStuno() {
		return stuno;
	}
	public void setStuno(String stuno) {
		this.stuno = stuno;
	}
	public String getStuname() {
		return stuname;
	}
	public void setStuname(String stuname) {
		this.stuname = stuname;
	}
	public Clazz getClazz() {
		return clazz;
	}
	public void setClazz(Clazz clazz) {
		this.clazz = clazz;
	}
}

servlet层：

package com.neusoft.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.ClassDao;
@WebServlet("/AddClassServlet")
public class AddClassServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String classname=request.getParameter("classname");
		ClassDao classDao=new ClassDao();
		int result=classDao.insertClass(classname);
	}

}

package com.neusoft.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.StudentDao;

/**
 * Servlet implementation class AddStudentServlet
 */
@WebServlet("/AddStudentServlet")
public class AddStudentServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//		request.setCharacterEncoding("utf-8");
		String stuno=request.getParameter("stuno");
		String stuname=request.getParameter("stuname");
		Integer classid=Integer.valueOf(request.getParameter("classid"));
		StudentDao studentDao=new StudentDao();
		System.out.println(stuname);
		int result=studentDao.insertStudent(stuno, stuname, classid);
		
	}

}

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.StudentDao;

@WebServlet("/DelStudentServlet")
public class DelStudentServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		int  id = Integer.valueOf(request.getParameter("id"));
		StudentDao studentDao=new StudentDao();
		int result=studentDao.delStudentById(id);
//		response.sendRedirect("ListStudentServlet");
		if(result>0){
			//成功
			out.println("<script>alert('删除成功');location.href='ListStudentServlet';</script>");
		}else{
			//失败
			out.println("<script>alert('删除失败');location.href='ListStudentServlet';</script>");
		}
	}
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

package com.neusoft.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.ClassDao;
import com.neusoft.dao.StudentDao;
import com.neusoft.pojo.Clazz;
import com.neusoft.pojo.student;

/**
 * Servlet implementation class EditStudentServlet
 */
@WebServlet("/EditStudentServlet")
public class EditStudentServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		int id=Integer.valueOf(request.getParameter("id"));
		StudentDao studentDao=new StudentDao();
		student stu=studentDao.getStuByID(id);
		request.setAttribute("stu", stu);
		ClassDao classDao=new ClassDao();
		List<Clazz> clazzes=classDao.getClazzes();
		request.setAttribute("clazzes", clazzes);
		request.getRequestDispatcher("edit.jsp").forward(request, response);
		
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

package com.neusoft.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.ClassDao;
import com.neusoft.pojo.Clazz;
@WebServlet("/ListClassServlet")
public class ListClassServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		ClassDao clazzDao=new ClassDao();
		List<Clazz> clazzes=clazzDao.getClazzes();
		request.setAttribute("clazzes", clazzes);
		request.getRequestDispatcher("add.jsp").forward(request, response);
	}

}

package com.neusoft.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.StudentDao;
import com.neusoft.pojo.student;

@WebServlet("/ListStudentServlet")
public class ListStudentServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		StudentDao studentDao=new StudentDao();
		List <student> stus=studentDao.getStus();
		request.setAttribute("stus", stus);
		request.getRequestDispatcher("List.jsp").forward(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

package com.neusoft.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.neusoft.dao.StudentDao;

/**
 * Servlet implementation class UpdateStudentServlet
 */
@WebServlet("/UpdateStudentServlet")
public class UpdateStudentServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		int id=Integer.valueOf(request.getParameter("id"));
		String stuname=request.getParameter("stuname");
		int classid=Integer.valueOf(request.getParameter("classid"));
		
		StudentDao studentDao=new StudentDao();
		//提示信息写法如删除
		int result=studentDao.UpdateStudent(id, stuname, classid);
		if(result>0){
			//成功
			out.println("<script>alert('修改成功');location.href='ListStudentServlet';</script>");
		}else{
			//失败
			out.println("<script>alert('修改失败');location.href='ListStudentServlet';</script>");
		}
		response.sendRedirect("ListStudentServlet");
	}

}

前端代码：

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<form action="AddStudentServlet" method="post">
	学号：<input type="text" name="stuno"></br>
	姓名：<input type="text" name="stuname"></br>	
	班级：<select name="classid" >
		<c:forEach items="${clazzes}" var="cl">
		<!--items放的是作用域里的Key -->
		<option value="${cl.id}">${cl.classname}</option>
		</c:forEach>
		</select><br>
	<input type="submit" value="添加学生">
	</form>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<form action="AddClassServlet" method="post">
	班级：<input type="text" name="classname"></br>
	<input type="submit" value="添加班级">
	</form>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<form action="UpdateStudentServlet" method="post">
	<input type="hidden" name="id" value="${stu.id }" readonly="readonly">
	学号：<input type="text" name="stuno" value="${stu.stuno}"></br>
	姓名：<input type="text" name="stuname" value="${stu.stuname}"></br>	
	班级：<select name="classid" >
		<c:forEach items="${clazzes}" var="cl">
		<!--items放的是作用域里的Key -->
		<option value="${cl.id}" <c:if test="${stu.clazz.id==cl.id }">selected="selected"</c:if>>${cl.classname }</option>
		</c:forEach>
		</select><br>
	<input type="submit" value="修改学生">
	</form>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<a href="ListStudentServlet">学员列表</a>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
     <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
<script type="text/javascript">
	function del(id){
		var flag=confirm("是否确认删除ID="+id+"的记录");
		if(flag){
			location.href="DelStudentServlet?id="+id;
		}
		
	}
</script>
</head>
<body>
	<table algin="center" border="1">
		<tr>
			<td><input type="checkbox" name="checkall"/>全选</td>
			<td>学号</td>
			<td>姓名</td>
			<td>班级</td>
			<td>操作</td>
		</tr>
		<c:forEach items="${stus }" var="stu">
		<tr>
			<td><input type="checkbox" name="id" value="${stu.id}"/></td>
			<td>${stu.stuno }</td>
			<td>${stu.stuname }</td>
			<td>${stu.clazz.classname }</td>
			<td><a href="EditStudentServlet?id=${stu.id}">编辑</a>
			<a href="javascript:void(0);" onclick="del(${stu.id})">删除</a></td>
		</tr>
		</c:forEach>
	</table>
</body>
</html>

注：这个小项目就是练练手，熟悉一下servlet，没有具体的前段页面设计