工程目录结构
数据库
工程文件下载
生成数据库和表的sql文件
代码
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.text.SimpleDateFormat"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>雇员操作</title>
</head>
<%!public static final String dbDriver = "com.mysql.cj.jdbc.Driver";
public static final String dbUrl = "jdbc:mysql://localhost:3306/demo0403?useSSL=false&serverTimezone=UTC";
public static final String dbUser = "root";
public static final String dbPass = "";%>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
%>
<%
Class.forName(dbDriver); // 加载驱动程序
conn = DriverManager.getConnection(dbUrl, dbUser, dbPass); //连接
%>
<body>
<form action="index.jsp" method="post">
<table style="margin: 0 auto; width: 48%;">
<tr>
<td><input name="eno" type="text" placeholder="输入雇员编号"></input></td>
<td><input name="ename" type="text" placeholder="输入雇员姓名"></input></td>
<td><input name="ejob" type="text" placeholder="输入雇员工作"></input></td>
<td><input name="hiredate" type="text" placeholder="输入雇佣日期"></input></td>
<td><input name="comm" type="text" placeholder="输入基本工资"></input></td>
<td><input type="submit" value="添加雇员"></input></td>
<%
/*插入数据*/
request.setCharacterEncoding("UTF-8");
if ((request.getParameter("eno") != null || !"".equals(request.getParameter("eno")))
&& request.getParameter("ename") != null && request.getParameter("ejob") != null
&& request.getParameter("hiredate") != null && request.getParameter("comm") != null) {
//先查询雇员编号是否重复
String sql = "select * from emp where empno=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(request.getParameter("eno")));
if (pstmt.executeQuery().next()) {
out.print("<td style=\"color: red;\">添加失败</td>");
} else {
sql = "insert into emp (empno,ename,job,hiredate,comm) value(?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(request.getParameter("eno")));
pstmt.setString(2, request.getParameter("ename"));
pstmt.setString(3, request.getParameter("ejob"));//request.getParameter("hiredate")
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse(request.getParameter("hiredate"));
long lg = date.getTime();
pstmt.setDate(4, new java.sql.Date(lg));
pstmt.setFloat(5, Float.parseFloat(request.getParameter("comm")));
if (pstmt.executeUpdate() > 0) {
out.print("<td style=\"color: red;\">添加成功</td>");
}
}
}
%>
</tr>
</table>
</form>
<form action="query.jsp" method="post">
<table style="margin: 0 auto; width: 40%;">
<tr>
<td><input name="keywords" type="text"
placeholder="根据雇员姓名进行模糊查询" style="width: 84%;"></input></td>
<td><input type="submit" value="查询" style="width: 60%;"></input></td>
</tr>
</table>
</form>
<table id="mytab" border="1" style="margin: 0 auto; width: 50%;">
<tr>
<td>雇员编号</td>
<td>雇员姓名</td>
<td>雇员工作</td>
<td>雇佣日期</td>
<td>基本工资</td>
</tr>
<%
/*显示数据*/
int total = 0; //数据总条数
int current = 1; //当前处于第几页
int pageSize = 5; //每页显示多少条
int begin = 0; //从哪一条开始查询
int count = pageSize; //查多少条
int totalPage = 0; //总页数
int prePage = 1; //上一页的值
int nextPage = 1; //下一页的值
//获取当前处于第几页
if (request.getParameter("current") != null) {
current = Integer.parseInt(request.getParameter("current"));
}
//获取每页显示多少条
if (request.getParameter("pageSize") != null) {
pageSize = Integer.parseInt(request.getParameter("pageSize"));
count = pageSize;
}
//计算数据总条数
String sql = "select count(*) from emp";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
total = rs.getInt(1);
}
if(total%pageSize==0){
totalPage = total / pageSize;
}else{
totalPage = total / pageSize + 1;
}
//计算上一页和下一页
prePage = (current > 1) ? (current - 1) : current;
nextPage = (current == totalPage) ? current : (current + 1);
//查询
begin = pageSize * (current - 1);
sql = "select * from emp limit ? , ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, begin);
pstmt.setInt(2, count);
rs = pstmt.executeQuery();
while (rs.next()) {
int empno = rs.getInt(2);
String ename = rs.getString(3);
String job = rs.getString(4);
Date hiredate = rs.getDate(5);
float comm = rs.getFloat(6);
%>
<tr>
<td><%=empno%></td>
<td><%=ename%></td>
<td><%=job%></td>
<td><%=hiredate%></td>
<td><%=comm%></td>
</tr>
<%
}
pstmt.close();
conn.close();
%>
</table>
<div style="margin: 0 auto; width: 25%;">
<span>每页<%=pageSize%>条
</span> <span>共<%=total%>条
</span> <span>当前第<%=current%>页
</span> <span>共<%=totalPage%>页
</span> <a href="index.jsp?current=<%=prePage%>&pageSize=<%=pageSize%>">上一页</a>
<a href="index.jsp?current=<%=nextPage%>&pageSize=<%=pageSize%>">下一页</a>
</div>
</body>
</html>
query.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.text.SimpleDateFormat"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查询</title>
</head>
<%!public static final String dbDriver = "com.mysql.cj.jdbc.Driver";
public static final String dbUrl = "jdbc:mysql://localhost:3306/demo0403?useSSL=false&serverTimezone=UTC";
public static final String dbUser = "root";
public static final String dbPass = "";%>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
%>
<%
Class.forName(dbDriver); // 加载驱动程序
conn = DriverManager.getConnection(dbUrl, dbUser, dbPass); //连接
//由于查询还需要分页显示 分页跳转使用的方式为带参数跳转 跳转后关键词失效 所以这里可以使用session来临时存储关键词
request.setCharacterEncoding("UTF-8");
String keywords = "";
if(request.getParameter("keywords")!=null||"".equals(request.getParameter("keywords"))){
keywords = request.getParameter("keywords");
session.setAttribute("saveKeywords", keywords);
}else{
if (session.getAttribute("saveKeywords") != null) {
keywords = (String)session.getAttribute("saveKeywords");
}
}
%>
<body>
<table id="mytab" border="1" style="margin: 0 auto; width: 50%;">
<tr>
<td>雇员编号</td>
<td>雇员姓名</td>
<td>雇员工作</td>
<td>雇佣日期</td>
<td>基本工资</td>
</tr>
<%
/*显示数据*/
int total = 0; //数据总条数
int current = 1; //当前处于第几页
int pageSize = 5; //每页显示多少条
int begin = 0; //从哪一条开始查询
int count = pageSize; //查多少条
int totalPage = 0; //总页数
int prePage = 1; //上一页的值
int nextPage = 1; //下一页的值
//获取当前处于第几页
if (request.getParameter("current") != null) {
current = Integer.parseInt(request.getParameter("current"));
}
//获取每页显示多少条
if (request.getParameter("pageSize") != null) {
pageSize = Integer.parseInt(request.getParameter("pageSize"));
count = pageSize;
}
//计算数据总条数
String sql = "select count(*) from emp where ename like ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "%"+keywords+"%");
rs = pstmt.executeQuery();
while (rs.next()) {
total = rs.getInt(1);
}
if(total%pageSize==0){
totalPage = total / pageSize;
}else{
totalPage = total / pageSize + 1;
}
//计算上一页和下一页
prePage = (current > 1) ? (current - 1) : current;
nextPage = (current == totalPage) ? current : (current + 1);
//查询
begin = pageSize * (current - 1);
sql = "select * from emp where ename like ? limit ? , ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "%"+keywords+"%");
pstmt.setInt(2, begin);
pstmt.setInt(3, count);
rs = pstmt.executeQuery();
while (rs.next()) {
int empno = rs.getInt(2);
String ename = rs.getString(3);
String job = rs.getString(4);
Date hiredate = rs.getDate(5);
float comm = rs.getFloat(6);
%>
<tr>
<td><%=empno%></td>
<td><%=ename%></td>
<td><%=job%></td>
<td><%=hiredate%></td>
<td><%=comm%></td>
</tr>
<%
}
pstmt.close();
conn.close();
%>
</table>
<div style="margin: 0 auto; width: 25%;">
<span>每页<%=pageSize%>条
</span> <span>共<%=total%>条
</span> <span>当前第<%=current%>页
</span> <span>共<%=totalPage%>页
</span> <a href="query.jsp?current=<%=prePage%>&pageSize=<%=pageSize%>">上一页</a>
<a href="query.jsp?current=<%=nextPage%>&pageSize=<%=pageSize%>">下一页</a>
<a href="index.jsp">返回首页</a>
</div>
</body>
</html>
原创文章，作者：witersen，如若转载，请注明出处：https://www.witersen.com
微信扫一扫
评论列表（5条）
哇塞
大聪明，是你嘛，找你好久了
@Hachi：
王大聪明！俺刚刚数据库报错了，俺贴一个answer，“生成转储文件的数据库版本为8.0,要导入sql文件的数据库版本为5.6,因为是高版本导入到低版本，引起1273错误”
解决方法：utf8mb4_0900_ai_ci替换为utf8_general_ci utf8mb4替换为utf8
@我不挂科。：yes，最常见的错误