前提是MySQL数据库有一个emp雇员表,数据最好在10条以上,以便看到分页结果
运行效果图如下:
代码还是有点小瑕疵,关于增加雇员,返回false结果时,跳转不到失败页面
- page.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!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">
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<meta http-equiv="Content-Type" content="text/html; charset='UTF-8'" />
<base href="<%=basePath%>">
<title>雇员查询</title>
</head>
<%
request.setCharacterEncoding("UTF-8");
%>
<body>
<%!//设置连接数据库的参数
private static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
private static final String DBURL = "jdbc:mysql://localhost:3306/emp?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
private static final String DBUSER = "root";
private static final String DBPASSWORD = "123456";
%>
<%
String url = "page.jsp";
int currentPage = 1;
String keyWord = "";//默认的关键字
int lineSize = 5;//每页显示的数据数
int allRecorders = 0;//保存总记录数
String column = "empno";//定义默认的查询列
String columnData = "雇员编号:empno|雇员姓名:ename|雇员工作:job";//可操作的查询列
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
%>
<%
try{//如果没有输入参数那么就会是null,null无法变为数字
currentPage = Integer.parseInt(request.getParameter("cp"));
}catch(Exception e){}
try{//如果没有输入参数那么就会是null,null无法变为数字
lineSize = Integer.parseInt(request.getParameter("ls"));
}catch(Exception e){}
if(request.getParameter("kw")!=null){//表示有查询的关键字
keyWord = new String(request.getParameter("kw").getBytes("UTF-8"), "UTF-8");//将取得的关键字进行转换
}
if(request.getParameter("col")!=null){//表示有查询的检索列
column = request.getParameter("col");
}
%>
<%//查询总记录数
String sql = " SELECT COUNT(*) FROM emp WHERE "+column+" LIKE ? ";
Class.forName(DBDRIVER);//加载数据库驱动
conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);//设置数据库连接
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"%"+keyWord+"%");
rs = pstmt.executeQuery();//将查询的结果返回
if (rs.next()){
allRecorders = rs.getInt(1);//返回总记录数
}
%>
<%//模糊查找数据
sql = " SELECT empno,ename,job,hiredate,sal,comm " +
" FROM emp " +
" WHERE "+ column +" LIKE ? LIMIT ?,? ";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"%"+keyWord+"%");// 模糊查询 %关键词%
pstmt.setInt(2,(currentPage-1)*lineSize);//(当前所在的页 - 1) * 每页显示数据行数 0 5
pstmt.setInt(3,lineSize);//每页最多显示数据行数 5 5
rs = pstmt.executeQuery();
%>
<%--搜索栏--%>
<div id="spiltSearchDiv">
<jsp:include page="split_page_search.jsp">
<jsp:param name="columnData" value="<%=columnData%>"></jsp:param>
<jsp:param name="keyWord" value="<%=keyWord%>"></jsp:param>
<jsp:param name="allRecorders" value="<%=allRecorders%>"></jsp:param>
<jsp:param name="column" value="<%=column%>"></jsp:param>
</jsp:include>
</div>
<%--数据显示栏--%>
<div id="dataDiv">
<table border="1" width="70%" bgcolor="#F2F2F2">
<tr>
<td>雇员编号</td>
<td>雇员姓名</td>
<td>雇员工作</td>
<td>雇佣日期</td>
<td>雇员工资</td>
<td>雇员奖金</td>
</tr>
<%
while (rs.next()){
int empno = rs.getInt(1);
String ename = rs.getString(2);
String job = rs.getString(3);
Date hiredate = rs.getDate(4);
double sal = rs.getDouble(5);
double comm = rs.getDouble(6);
%>
<tr>
<td><%=empno%></td>
<td><%=ename%></td>
<td><%=job%></td>
<td><%=hiredate%></td>
<td><%=sal%></td>
<td><%=comm%></td>
</tr>
<%
}
conn.close();
%>
</table>
</div>
<%--分页栏--%>
<div id="splitBarDiv">
<jsp:include page="split_page_bar.jsp">
<jsp:param name="currentPage" value="<%=currentPage%>"></jsp:param>
<jsp:param name="lineSize" value="<%=lineSize%>"></jsp:param>
<jsp:param name="column" value="<%=column%>"></jsp:param>
<jsp:param name="keyWord" value="<%=keyWord%>"></jsp:param>
<jsp:param name="allRecorders" value="<%=allRecorders%>"></jsp:param>
<jsp:param name="url" value="<%=url%>"></jsp:param>
</jsp:include>
</div>
</body>
</html>
- split_page_bar.jsp
<%@ page pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
%>
<%//设置由外部接收的数据
String url = null;
int currentPage = 1;//当前页
int lineSize = 5;//每页数据数
String column = null;//检索列
String keyWord = null;//关键词
int allRecorders = 0;//总记录数
int pageSize = 0 ;//总页数
int lsData [] = new int [] {1,5,10,15,20,30,50,100} ;//每页显示多少条数据
%>
<%//接收外部传递的参数
try {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
} catch (Exception e) {}
try {
allRecorders = Integer.parseInt(request.getParameter("allRecorders"));
} catch (Exception e) {}
try {
lineSize = Integer.parseInt(request.getParameter("lineSize"));
} catch (Exception e) {}
column = request.getParameter("column") ;
keyWord = request.getParameter("keyWord") ;
url = request.getParameter("url") ;
%>
<%//计算总页数
if (allRecorders > 0) {
pageSize = (allRecorders + lineSize - 1) / lineSize ;
} else { // 没有记录
pageSize = 1 ;
}
%>
<script type="text/javascript">
function goSplit(vcp) { // 根据外部传递的cp内容进行操作
var eleLs = document.getElementById("lsSel").value ;
try {
var eleKw = document.getElementById("kw").value ;
var eleCol = document.getElementById("colSel").value ;
window.location = "<%=url%>?cp=" + vcp + "&ls=" + eleLs + "&kw=" + encodeURIComponent(eleKw) + "&col=" + eleCol ;
} catch (Exception) {//如果出现异常,说明没有关键字和检索列
window.location = "<%=url%>?cp=" + vcp + "&ls=" + eleLs ;
}
}
</script>
<input type="button" class="btn btn-default" value="首页" onclick="goSplit(1)" <%=currentPage == 1 ? "disabled" : ""%>>
<input type="button" class="btn btn-default" value="上一页" onclick="goSplit(<%=currentPage-1%>)" <%=currentPage == 1 ? "disabled" : ""%>>
<input type="button" class="btn btn-default" value="下一页" onclick="goSplit(<%=currentPage+1%>)" <%=currentPage == pageSize ? "disabled" : ""%>>
<input type="button" class="btn btn-default" value="尾页" onclick="goSplit(<%=pageSize%>)" <%=currentPage == pageSize ? "disabled" : ""%>>
<%--//添加一个下拉列表框--%>
跳转到:<select id="cpSel" onchange="goSplit(this.value)">
<%--动态控制的option--%>
<%
for (int x = 1;x <= pageSize;x++){
%>
<option value="<%=x%>" <%=currentPage == x ? "selected" : ""%>><%=x%></option>
<%
}
%>
</select>页 每页显示:
<select id="lsSel" onchange="goSplit(1)">
<%
for (int x = 0 ; x<lsData.length ;x++){
%>
<option value="<%=lsData[x]%>" <%=lineSize == lsData[x] ? "selected" : ""%>><%=lsData[x]%></option>
<%
}
%>
</select>
行记录
- split_page_search.jsp
<%@ page pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
%>
<%
String columnData = null;//查询的数据列
String keyWord = null;//查询的关键字
String column = null;//查询的
int allRecorders = 0;//查询的总数据量
%>
<%//接收页面的接收列
try{
allRecorders = Integer.parseInt(request.getParameter("allRecorders"));
}catch (Exception e){}
columnData = request.getParameter("columnData");
keyWord = request.getParameter("keyWord");
column = request.getParameter("column");
%>
请输入查询关键字:
<%
if (columnData!=null){
%>
<select id="colSel">
<%
String result[] = columnData.split("\\|");
for (int x = 0 ; x < result.length ; x ++){
String temp[] = result[x].split(":");
%>
<option value="<%=temp[1]%>" <%=column.equals(temp[1])?"selected":""%>><%=temp[0]%></option>
<%
}
%>
</select>
<%
}
%>
<input type="text" name="kw" id="kw" value="<%=keyWord%>">
<input type="button" value="查询" onclick="goSplit(1)">
<input type="button" value="增加雇员" onclick="javascript:window.location.href='add.html'">
<br>
<span>一共查询到<%=allRecorders%>条记录</span><br>
<%--,一共有<%=pageSize%>页。--%>
- add.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>雇员注册表单</title>
<script type="text/javascript">
function Emp(){
return Check();
}
function Check(){
if(document.getElementById('empno').value.length==0)
{
alert("编号不能为空!");
document.getElementById('wrong1').style.display = "block";
document.getElementById('empno').focus();
return false;
}
var value = document.getElementById("empno").value;
var reg=/^[1-9]\d*$|^0$/;
if(reg.test(value)!=true)
{
alert("编号必须为数字!");
document.getElementById('right1').style.display = "none";
document.getElementById('wrong1').style.display = "block";
document.getElementById('empno').focus();
return false;
}else{
document.getElementById('wrong1').style.display = "none";
document.getElementById('right1').style.display = "block";
}
if(document.getElementById('empname').value.length==0)
{
alert("姓名不能为空!");
document.getElementById('wrong2').style.display = "block";
document.getElementById('empname').focus();
return false;
}else{
document.getElementById('wrong2').style.display = "none";
document.getElementById('right2').style.display = "block";
}
if(document.getElementById('empjob').value.length==0)
{
alert("工作不能为空!");
document.getElementById('wrong3').style.display = "block";
document.getElementById('empjob').focus();
return false;
}else{
document.getElementById('wrong3').style.display = "none";
document.getElementById('right3').style.display = "block";
}
if(!document.getElementById("empdate").value.match("^[0-9]{4,4}-[0-9]{2,2}-[0-9]{2,2}$"))
{
alert( "日期格式错误 xxxx-xx-xx");
document.getElementById('wrong4').style.display = "block";
document.getElementById('empdate').focus();
return false;
}else{
document.getElementById('wrong4').style.display = "none";
document.getElementById('right4').style.display = "block";
}
if(!document.getElementById("empsal").value.match("^[0-9]{1,9}.[0-9]{2,2}$"))
{
alert( "工资格式错误x...x.xx ");
document.getElementById('wrong5').style.display = "block";
document.getElementById('empsal').focus();
return false;
}else{
document.getElementById('wrong5').style.display = "none";
document.getElementById('right5').style.display = "block";
}
if(!document.getElementById("empbond").value.match("^[0-9]{1,9}.[0-9]{2,2}$"))
{
alert( "奖金格式错误x...x.xx ");
document.getElementById('wrong6').style.display = "block";
document.getElementById('empbond').focus();
return false;
}else{
document.getElementById('wrong6').style.display = "none";
document.getElementById('right6').style.display = "block";
}
}
</script>
</head>
<body>
<form action="addafter.jsp" method="post" name="emp" onSubmit="return Emp()">
<h2>增加雇员</h2>
<table>
<tr>
<td>雇员编号:<input type="text" id="empno" name="empno"></td>
<td>
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\错.png' id="wrong1" style='display:none' width="30px" height="30px">
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\对.png' id="right1" style='display:none' width="30px" height="30px">
</td>
</tr>
<tr>
<td>雇员姓名:<input type="text" id="empname" name="empname"></td>
<td>
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\错.png' id="wrong2" style='display:none' width="30px" height="30px">
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\对.png' id="right2" style='display:none' width="30px" height="30px">
</td>
</tr>
<tr>
<td>雇员工作:<input type="text" id="empjob" name="empjob"></td>
<td>
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\错.png' id="wrong3" style='display:none' width="30px" height="30px">
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\对.png' id="right3" style='display:none' width="30px" height="30px">
</td>
</tr>
<tr>
<td>雇佣日期:<input type="text" id="empdate" name="empdate"></td>
<td>
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\错.png' id="wrong4" style='display:none' width="30px" height="30px">
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\对.png' id="right4" style='display:none' width="30px" height="30px">
</td>
</tr>
<tr>
<td>基本工资:<input type="text" id="empsal" name="empsal"></td>
<td>
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\错.png' id="wrong5" style='display:none' width="30px" height="30px">
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\对.png' id="right5" style='display:none' width="30px" height="30px">
</td>
</tr>
<tr>
<td>雇员奖金:<input type="text" id="empbond" name="empbond"></td>
<td>
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\错.png' id="wrong6" style='display:none' width="30px" height="30px">
<img src='C:\Users\Administrator\Desktop\Mine\Javaweb\实验三\third\对.png' id="right6" style='display:none' width="30px" height="30px">
</td>
</tr>
</table>
<input type="submit" value="提交">
<input type="reset" value="重置">
</form>
</body>
</html>
- addafter.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!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>增加结果</title>
</head>
<%
request.setCharacterEncoding("UTF-8");
%>
<body>
<%!
private static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
private static final String DBURL = "jdbc:mysql://localhost:3306/emp?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
private static final String DBUSER = "root";
private static final String DBPASSWORD = "123456";
%>
<%
String empno = request.getParameter("empno");//内置对象应该会吧
String emname = request.getParameter("empname");
String emjob = request.getParameter("empjob");
String emhiredate = request.getParameter("empdate");
String emsal = request.getParameter("empsal");
String emcomm = request.getParameter("empbond");
try{
Class.forName(DBDRIVER);
Connection con = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);//设置数据库连接
String sql="insert into emp (empno,ename,job,hiredate,sal,comm) values ('"+ empno +"','"+ emname +"','"+ emjob + "','"+ emhiredate +"','"+ emsal +"','"+ emcomm +"')";
Statement stmt = con.createStatement();
//此处最好写一个查询雇员编号语句,判断一下数据库中是否存在,若存在,增加失败,给出提示
if(stmt.executeUpdate(sql)==1){
%>
<h3>雇员信息添加成功!</h3>
<%
response.setHeader("refresh", "3;url=page.jsp"); //客户端跳转,跳转之后地址栏会变为跳转页面的地址
%>
<h3>3秒后跳转到主界面,若没有跳转请按<a href="page.jsp">这里</a>!</h3>
<%
}else{
%>
<h3>雇员信息添加失败</h3>
<%
}
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
%>
</body>
</html>
以上便是完整代码,不过不建议这样做,因为这样jsp代码量会非常大,建议用MVC模式,jsp页面的代码量越少越好
参考博客:https://blog.csdn.net/jluzh04140717/article/details/70200863