前面都是细说某个功能,比如添加功能和按姓名查询功能,所以我把不相关的代码去掉了。
在这里贴完整的。
AddMessageDao.java
内容是学生信息的录入,以及按照各种要求查询的函数
package dao;
import java.sql.Connection;
import java.io.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.annotation.WebServlet;
import bean.AddMessageBean;
public class AddMessageDao {
static {
// 加载数据库驱动 ,也就是jdbc
try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public void addClassInfo(AddMessageBean Bean) {
Connection conn = null;
Statement stmt = null;
try {
// 获取数据库连接,三个参数为("数据库名字","用户名","密码")
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 写一条SQL的插入语句,按照表的顺序,依次调用对应的get方法,得到值
String sql = "insert into stumessage() values ('"+Bean.getNum()+"','"+ Bean.getName() +"','"+ Bean.getSex() +"','"
+ Bean.getCollege() +"','"+ Bean.getYear()+"','"+ Bean.getMajor() +"','"+ Bean.getPoor()+"')";
// 创建SQL执行对象
stmt = conn.createStatement();
// 执行sql语句
stmt.executeUpdate(sql);
} catch (SQLException e) { //抓取异常
e.printStackTrace();
} finally { //最后要关掉数据库和statement对象
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public List<AddMessageBean> findAll() {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
public List<AddMessageBean> SearchID(String ID) {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage where num ='";
sql += ID + "'";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
public List<AddMessageBean> SearchIns(String Ins) {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage where college ='";
sql += Ins + "'";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
public List<AddMessageBean> SearchLevel(String Level) {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage where poor ='";
sql += Level + "'";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
public List<AddMessageBean> SearchName(String Name) {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage where name ='";
sql += Name + "'";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
public List<AddMessageBean> SearchPro(String Pro) {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage where major ='";
sql += Pro + "'";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
public List<AddMessageBean> SearchSex(String Sex) {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage where sex ='";
sql += Sex + "'";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
public List<AddMessageBean> SearchYear(String Year) {
Connection conn = null;
Statement stmt = null;
List<AddMessageBean> classList= new ArrayList<AddMessageBean>();
try {
// 获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/project","root","root");
// 整理一条SQL语句
String sql = "select * from stumessage where year ='";
sql += Year + "'";
// 创建执行sql的对象
stmt = conn.createStatement();
//执行sql语句
ResultSet rs =stmt.executeQuery(sql);
//遍历结果集
while(rs.next()){
String num =rs.getString("num");
String name=rs.getString("name");
String sex =rs.getString("sex");
String college =rs.getString("college");
String year =rs.getString("year");
String major =rs.getString("major");
String poor =rs.getString("poor");
AddMessageBean bean = new AddMessageBean();
bean.setNum(num);
bean.setName(name);
bean.setSex(sex);
bean.setCollege(college);
bean.setYear(year);
bean.setMajor(major);
bean.setPoor(poor);
classList.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
return classList;
}
}
web.xml
包括所有的配置(信息添加,按各种要求查询,删除,查看详细信息)
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
<display-name>test</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>AddMessage</servlet-name>
<servlet-class>servlet.AddMessageServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>AddMessage</servlet-name>
<url-pattern>/AddMessage</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>AddWorkTime</servlet-name>
<servlet-class>servlet.AddWorkTimeServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>AddWorkTime</servlet-name>
<url-pattern>/AddWorkTime</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>TimeManage</servlet-name>
<servlet-class>servlet.TimeManageServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>TimeManage</servlet-name>
<url-pattern>/TimeManage</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>SearchID</servlet-name>
<servlet-class>servlet.SearchID</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SearchID</servlet-name>
<url-pattern>/SearchID</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>SearchIns</servlet-name>
<servlet-class>servlet.SearchIns</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SearchIns</servlet-name>
<url-pattern>/SearchIns</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>SearchLevel</servlet-name>
<servlet-class>servlet.SearchLevel</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SearchLevel</servlet-name>
<url-pattern>/SearchLevel</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>SearchName</servlet-name>
<servlet-class>servlet.SearchName</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SearchName</servlet-name>
<url-pattern>/SearchName</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>SearchPro</servlet-name>
<servlet-class>servlet.SearchPro</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SearchPro</servlet-name>
<url-pattern>/SearchPro</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>SearchSex</servlet-name>
<servlet-class>servlet.SearchSex</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SearchSex</servlet-name>
<url-pattern>/SearchSex</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>SearchYear</servlet-name>
<servlet-class>servlet.SearchYear</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>SearchYear</servlet-name>
<url-pattern>/SearchYear</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>Delete</servlet-name>
<servlet-class>servlet.Delete</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Delete</servlet-name>
<url-pattern>/Delete</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>Detail</servlet-name>
<servlet-class>servlet.Detail</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Detail</servlet-name>
<url-pattern>/Detail</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>DeleteWork</servlet-name>
<servlet-class>servlet.DeleteWork</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DeleteWork</servlet-name>
<url-pattern>/DeleteWork</url-pattern>
</servlet-mapping>
</web-app>