代码框架介绍：
后端部分：
beans：实体类，存放各个数据库表单的实体类；
dao:接口部分：创建对实体类对象的增删改查的方法接口。
impl部分：具体实现接口的方法，方便调用。
servlet: 接受处理前端页面传来的数据，并在处理结束后响应前端页面。
test:负责后端代码测试。
utis工具模块:由于JDBC连接及数据处理存在大量重复，故提取其中重复部分代码封装作为工具类被impl中的类调用以简化代码。
前端部分：
lib:存放前端及jsp、servlet所需jar包；
web.xml：用来指定默认首页及建立后端servlet与前端代码之间的映射连接；
index.jsp:默认首页（内含登录（还未实现），注册，数据展示功能）;
register.jsp：注册页面，填写相关信息点击注册， 跳转到数据展示页面，完成注册。
StudentList.jsp：数据展示，将表中所有数据读取并展示到页面。（内含数据修改和删除功能）
updatestu.jsp:数据修改更新，在数据展示页面点击修改自动跳转到此页面，根据学号修改相关信息，修改后提交继续跳转到数据展示页面展示更新后的数据并同步到数据库。
代码展示：（以Student为例）
后端：
实体类：
package com.openlab.beans;
//也可使用limbok插件简化代码，通过注解省略get set方法的书写
public class Student {
private String StudentNo ;
private String LoginPwd ;
private String StudentName;
private String Sex ;
private Integer GradeId ;
private String Phone ;
private String Address ;
private String BornDate ;
private String Email ;
public Student() {
}
public Student(String studentNo, String loginPwd, String studentName, String sex, Integer gradeId, String phone, String address, String bornDate, String email) {
StudentNo = studentNo;
LoginPwd = loginPwd;
StudentName = studentName;
Sex = sex;
GradeId = gradeId;
Phone = phone;
Address = address;
BornDate = bornDate;
Email = email;
}
public String getStudentNo() {
return StudentNo;
}
public void setStudentNo(String studentNo) {
StudentNo = studentNo;
}
public String getLoginPwd() {
return LoginPwd;
}
public void setLoginPwd(String loginPwd) {
LoginPwd = loginPwd;
}
public String getStudentName() {
return StudentName;
}
public void setStudentName(String studentName) {
StudentName = studentName;
}
public String getSex() {
return Sex;
}
public void setSex(String sex) {
Sex = sex;
}
public Integer getGradeId() {
return GradeId;
}
public void setGradeId(Integer gradeId) {
GradeId = gradeId;
}
public String getPhone() {
return Phone;
}
public void setPhone(String phone) {
Phone = phone;
}
public String getAddress() {
return Address;
}
public void setAddress(String address) {
Address = address;
}
public String getBornDate() {
return BornDate;
}
public void setBornDate(String bornDate) {
BornDate = bornDate;
}
public String getEmail() {
return Email;
}
public void setEmail(String email) {
Email = email;
}
@Override
public String toString() {
return "Student{" +
"StudentNo='" + StudentNo + '\'' +
", LoginPwd='" + LoginPwd + '\'' +
", StudentName='" + StudentName + '\'' +
", Sex='" + Sex + '\'' +
", GradeId=" + GradeId +
", Phone='" + Phone + '\'' +
", Address='" + Address + '\'' +
", BornDate='" + BornDate + '\'' +
", Email='" + Email + '\'' +
'}';
}
}
接口类：
package com.openlab.dao;
import com.openlab.beans.Student;
import java.util.List;
public interface StudentDao {
public int save(Student student);//插入学生信息
public int update(Student student);//更新学生信息（通过学生学号）
public int delete(String stuid);//通过学号删除学生信息
public List<Student> getAll();//获取所有学生信息
public Student findById(String stuid);//通过id查找指定学生信息
}
工具类:
package com.openlab.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class JDBCUtils {
static Properties properties = new Properties();
static DataSource dataSource = null;
static {
InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
try {
properties.load(inputStream);
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection() throws Exception {
try {
Connection connection = dataSource.getConnection();
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//新增成功后返回新增的主键
public int save(String sql, Object... params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
int id = -1;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
setParameter(preparedStatement, params);
preparedStatement.executeUpdate();
rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
Object obj = rs.getObject(1);
id = Integer.parseInt(obj.toString());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(connection, preparedStatement, rs);
}
return id;
}
public int executeUpdate(String sql, Object... params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
setParameter(preparedStatement, params);
// System.out.println(sql);
int rows = preparedStatement.executeUpdate();
return rows;
} catch (Exception e) {
e.printStackTrace();
} finally {
close(connection, preparedStatement, null);
}
return -1;
}
public <T> T findOneById(Class<T> tClass, String sql, Object... params) {
List<T> list = executeQuery(tClass, sql, params);
if (list != null && list.size() > 0) {
return list.get(0);
} else {
return null;
}
}
public <T> List<T> executeQuery(Class<T> tClass, String sql, Object... params) {
List<T> list = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
setParameter(preparedStatement, params);
rs = preparedStatement.executeQuery();
while (rs.next()) {
T t = tClass.newInstance();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
for (int i = 1; i <= count; i++) {
String label = rsmd.getColumnLabel(i);
Object v = rs.getObject(label);
//
// String fieldname = change(label);
String fieldname = label;
Field field = tClass.getDeclaredField(fieldname);
field.setAccessible(true);
field.set(t, v);
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(connection, preparedStatement, rs);
}
return list;
}
/**
* 如果表中的列是全部小写的 empid --->empid
* _ emp_id--->empId
* EMPID--->empid
*
* @param label
* @return
*/
private static String change(String label) {
int index = label.indexOf("_");
String fieldname = "";
if (index != -1) {
fieldname = label.substring(0, index) + label.substring(index + 1, index + 2).toUpperCase() + label.substring(index + 2);
} else {//没找到
fieldname = label.toLowerCase();
}
return fieldname;
}
private void setParameter(PreparedStatement preparedStatement, Object... params) {
try {
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
// System.out.println(params[i]);
preparedStatement.setObject(i + 1, params[i]);
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public void close(Connection connection, Statement statement, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
实现类：
package com.openlab.dao.impl;
import com.openlab.beans.Student;
import com.openlab.utils.JDBCUtils;
import java.util.List;
public class StudentDao implements com.openlab.dao.StudentDao {
@Override
public int save(Student student) {
return new JDBCUtils().save("insert into student values(?,?,?,?,?,?,?,?,?)",student.getStudentNo(),student.getLoginPwd(),student.getStudentName(),student.getSex(),student.getGradeId(),student.getPhone(),student.getAddress(),student.getBornDate(),student.getEmail());
}
@Override
public int update(Student student) {
// System.out.println(student.getStudentName()+student.getLoginPwd()+student.getSex()+student.getGradeId()+student.getPhone()+student.getAddress()+student.getBornDate()+student.getEmail()+student.getStudentNo());
return new JDBCUtils().executeUpdate("update student set StudentName = ?,LoginPwd = ? ,Sex = ?, GradeId = ?, Phone = ?, Address = ?, BornDate = ?, Email = ? where StudentNo = ?" ,student.getStudentName(),student.getLoginPwd(),student.getSex(),student.getGradeId(),student.getPhone(),student.getAddress(),student.getBornDate(),student.getEmail(),student.getStudentNo());
}
@Override
public int delete(String stuid) {
return new JDBCUtils().executeUpdate("delete from student where StudentNo = ?",stuid);
}
@Override
public List<Student> getAll() {
return new JDBCUtils().executeQuery(Student.class,"select * from student");
}
@Override
public Student findById(String stuid) {
return new JDBCUtils().findOneById(Student.class,"select * from student where StudentNo = ? ",stuid);
}
}
servlet类：
增加：
package com.openlab.sevlet;
import com.openlab.beans.Student;
import com.openlab.dao.impl.StudentDao;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
@WebServlet(name = "Servlet", value = "/Servlet")
public class addStudentServlet extends HttpServlet {
StudentDao studentDao = new StudentDao();
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
//获取参数
// System.out.println(request.getParameter("StudentNo"));
String studentNo = request.getParameter("StudentNo");
String loginPwd = request.getParameter("LoginPwd");
String studentName = request.getParameter("StudentName");
String sex = request.getParameter("sex");
if(sex.equals("f")){
sex = "女";
}else {
sex = "男";
}
String gradeId = request.getParameter("GradeId");
String phone = request.getParameter("Phone");
String address = request.getParameter("Address");
String bornDate = request.getParameter("BornDate");
String email = request.getParameter("Email");
Student student = new Student(studentNo,loginPwd,studentName,sex,Integer.valueOf(gradeId),phone,address,bornDate,email);
//插入数据
studentDao.save(student);
// System.out.println(update);
//响应页面
response.sendRedirect("Studentlist.jsp");
}
}
删除：
package com.openlab.sevlet;
import com.openlab.dao.impl.StudentDao;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
@WebServlet(name = "Servlet2", value = "/Servlet2")
public class deleteStudentServlet extends HttpServlet {
StudentDao studentDao = new StudentDao();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
//获取参数
String studentNo = request.getParameter("stuid");
//删除数据
studentDao.delete(studentNo);
//响应页面
response.sendRedirect("Studentlist.jsp");
}
}
修改：
package com.openlab.sevlet;
import com.openlab.beans.Student;
import com.openlab.dao.impl.StudentDao;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
@WebServlet(name = "updateStudentServlet", value = "/updateStudentServlet")
public class updateStudentServlet extends HttpServlet {
StudentDao studentDao = new StudentDao();
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
//获取参数
String studentNo = request.getParameter("StudentNo");
String selecttext = request.getParameter("selecttext");
Student student = studentDao.findById(studentNo);
String selectid = request.getParameter("selectid");
switch (selectid){
case "1": student.setLoginPwd(selecttext);break;
case "2":student.setStudentName(selecttext);break;
case "3":student.setSex(selecttext);break;
case "4":student.setGradeId(Integer.parseInt(selecttext));break;
case "5":student.setPhone(selecttext);break;
case "6":student.setAddress(selecttext);break;
case "7":student.setBornDate(selecttext);break;
case "8":student.setEmail(selecttext);break;
default:
System.out.println("输入id有误！！！");
}
//插入数据
studentDao.update(student);
//响应页面
response.sendRedirect("Studentlist.jsp");
}
}
前端：
web.xml：
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<!--默认首页-->
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<!--建立映射-->
<servlet>
<servlet-name>addStudentServlet</servlet-name>
<servlet-class>com.openlab.sevlet.addStudentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>addStudentServlet</servlet-name>
<url-pattern>/addstu.do</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>deleteStudentServlet</servlet-name>
<servlet-class>com.openlab.sevlet.deleteStudentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>deleteStudentServlet</servlet-name>
<url-pattern>/delestu.do</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>updateStudentServlet</servlet-name>
<servlet-class>com.openlab.sevlet.updateStudentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>updateStudentServlet</servlet-name>
<url-pattern>/updatestu.do</url-pattern>
</servlet-mapping>
</web-app>首页：
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>首页</title>
</head>
<body>
<p>登录</p>
<p><a href="register.jsp">注册</a></p>
<p><a href="Studentlist.jsp">数据展示</a></p>
</body>
</html>
注册页面：
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>注册</title>
</head>
<body>
<form action="addstu.do" method="post">
<p>学号：<input type="text" name="StudentNo"></p>
<p>密码：<input type="text" name="LoginPwd"></p>
<p>姓名：<input type="text" name="StudentName"></p>
<p>性别：<input type="radio" name="sex" value="m">男
<input type="radio" name="sex" value="f">女
</p>
<p>年级：<input type="text" name="GradeId"></p>
<p>电话：<input type="text" name="Phone"></p>
<p>住址：<textarea rows="5" cols="50" name="Address"></textarea></p>
<p>生日：<input type="text" name="BornDate"></p>
<p>邮箱：<input type="text" name="Email"></p>
<p><input type="submit" value="注册"/> </p>
</form>
</body>
</html>
数据展示：
<%@ page import="java.util.ArrayList" %>
<%@ page import="com.openlab.beans.Student" %>
<%@ page import="java.util.List" %>
<%@ page import="com.openlab.dao.impl.StudentDao" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>展示</title>
</head>
<body>
<%
List<Student> list = new ArrayList<>();
StudentDao studentDao = new StudentDao();
List<Student> all = studentDao.getAll();
for (Student student : all) {
list.add(student);
}
%>
<table border="1" width="600" cellspacing="0" style="color: red">
<tr align="center">
<td>学号</td>
<td>密码</td>
<td>姓名</td>
<td>性别</td>
<td>年级</td>
<td>电话</td>
<td>住址</td>
<td>生日</td>
<td>邮件</td>
<td colspan="2">操作</td>
</tr>
<%
for (Student student : list){
%>
<tr align="center">
<td><%= student.getStudentNo()%></td>
<td><%= student.getLoginPwd()%></td>
<td><%= student.getStudentName()%></td>
<td><%= student.getSex()%></td>
<td><%= student.getGradeId()%></td>
<td><%= student.getPhone()%></td>
<td><%= student.getAddress()%></td>
<td><%= student.getBornDate()%></td>
<td><%= student.getEmail()%></td>
<td>
<a href="updatestu.jsp?stuid=<%=student.getStudentNo()%>">修改</a>
</td>
<td>
<a href="delestu.do?stuid=<%=student.getStudentNo()%>">删除</a>
</td>
</tr>
<%
}
%>
</table>
</body>
</html>
数据修改：
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>更新</title>
</head>
<body>
<form action="updatestu.do?StudentNo=<%=request.getParameter("stuid")%>" method="post">
<p>修改的学生学号：<%=request.getParameter("stuid")%></p>
<p>请输入你要修改的字段<input type="text" name="selectid"></p>
<p>请输入修改后的字段内容<input type="text" name="selecttext"></p>
<p>密码：1</p>
<p>姓名：2</p>
<p>性别：3</p>
<p>年级：4</p>
<p>电话：5</p>
<p>住址：6</p>
<p>生日：7</p>
<p>邮箱：8</p>
<p><input type="submit" value="确认修改"/> </p>
</form>
</body>
</html>
效果展示：
首页（稍显简陋，可后期修饰）：（登录还未实现）
注册：
填写信息点击注册跳转到数据展示页面（孙悟空已添加）：
点击修改：（修改zhaosi名字为赵四）
可以看到赵四修改成功；
点击删除：（点击删除”九点“，删除成功）
文章出处登录后可见！
已经登录？立即刷新
