一.问题概述
实现了数据库的增删改查和分页显示。
分页显示:mysql
String cmd = "select * from t_user limit "+firstResult+","+pageSize;
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
--为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
--如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行
--换句话说，LIMIT n 等价于 LIMIT 0,n。
分页显示:sql server
select top 10 * from table --这是选出前10条
select top 100 *from table where id not in(select Top 50 id from table)
--这是选出第51到100条,使用存储过程也好。
文档结构如下：
二.代码实现
<!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="insert.jsp" method="post">
<table>
<tr>
<td>用户名：</td>
<td><input type="text" name="username"></td>
</tr>
<tr>
<td>密码：</td>
<td><input type="text" name="password"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="提交"/> </td>
</tr>
</table>
</form>
</body>
</html>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String username = request.getParameter("username");
String password = request.getParameter("password");
String cmd = "insert into t_user (username,password) values('"+username+"','"+password+"')";
int count = 0;
try{
Class.forName("com.mysql.jdbc.Driver");
String url= "jdbc:mysql://127.0.0.1/db_user";
String uid = "root";
String pwd = "root";
Connection conn = DriverManager.getConnection(url,uid,pwd);
Statement st = conn.createStatement();
count = st.executeUpdate(cmd);
conn.close();
}catch(Exception e){
e.printStackTrace();
}
if(count>0) response.sendRedirect("list.jsp");
else response.sendRedirect("error.jsp");
%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ 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="fill.html">添加新人员</a>
<br />
<table border="1">
<tr>
<td>ID</td>
<td>username</td>
<td>password</td>
<td>操作</td>
</tr>
<%
String pageNum = request.getParameter("pageNum");
int rowCount = 0;//数据库表总纪录数
int pageSize = 3;//每页显示纪录数，假设为3
int pageCount =0;//共有多少页
//pn做pageNum的中间变量使用的
int pn = 1;
if(null != pageNum && pageNum.length()>0)
pn = Integer.parseInt(pageNum);
try{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1/db_user";
String uid = "root";
String pwd = "root";
Connection conn = DriverManager.getConnection(url,uid,pwd);
Statement st2 = conn.createStatement();
String cmd2 = "select count(*) from t_user ";
ResultSet rs2 = st2.executeQuery(cmd2);
if(rs2.next()){
rowCount = rs2.getInt(1);
}
rs2.close();
pageCount = rowCount%pageSize ==0 ? (rowCount/pageSize) : (rowCount/pageSize+1);
if(pn < 1 ) pn = 1;
if(pn >pageCount) pn = pageCount;
int firstResult = (pn-1)*pageSize;
Statement st = conn.createStatement();
/*MySql数据库有limit关键字，注意用法
第一个参数是从0开始的，不是一
SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
*/
String cmd = "select * from t_user limit "+firstResult+","+pageSize;
ResultSet rs = st.executeQuery(cmd);
while(rs.next()){
%>
<tr>
<td><%=rs.getInt(1) %></td>
<td><%=rs.getString(2) %></td>
<td><%=rs.getString(3) %></td>
<td>
<a href="edit.jsp?id=<%=rs.getInt(1) %>">编辑</a>
<a href="delete.jsp?id=<%=rs.getInt(1) %>"> 删除</a>
</td>
</tr>
<%
}
conn.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<tr>
<td colspan="4">共有<%=rowCount %>条记录，每页显示<%=pageSize %>条记录，共显示<%=pageCount %>页，当前是第<%=pn %>页。
</td>
</tr>
<tr>
<td colspan="4"><a href="list.jsp?pageNum=1">第一页</a>
<a href="list.jsp?pageNum=<%=pn-1 %>">上一页</a>
<a href="list.jsp?pageNum=<%=pn+1 %>">下一页</a>
<a href="list.jsp?pageNum=<%=pageCount %>">最后页</a>
</td>
</tr>
</table>
</body>
</html>
<%@page import="java.sql.ResultSet"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<!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>
<%
String id = request.getParameter("id");
String username = "";
String password = "";
try{
Class.forName("com.mysql.jdbc.Driver");
String url= "jdbc:mysql://127.0.0.1/db_user";
String uid = "root";
String pwd = "root";
Connection conn = DriverManager.getConnection(url,uid,pwd);
Statement st = conn.createStatement();
String cmd = "select * from t_user where id="+id;
ResultSet rs = st.executeQuery(cmd);
if(rs.next()){
username = rs.getString(2);
password = rs.getString(3);
}
conn.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<form action="update.jsp" method="post">
<input type="hidden" name="id" value="<%=id %>"/>
<table>
<tr>
<td>用户名：</td>
<td><input type="text" name="username" value="<%=username %>"></td>
</tr>
<tr>
<td>密码：</td>
<td><input type="text" name="password" value="<%=password %>"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="提交"/> </td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%
int count = 0;
String id = request.getParameter("id");
if(null != id && id.length()>0){
String cmd = "delete from t_user where id="+id;
try{
Class.forName("com.mysql.jdbc.Driver");
String url= "jdbc:mysql://127.0.0.1/db_user";
String uid = "root";
String pwd = "root";
Connection conn = DriverManager.getConnection(url,uid,pwd);
Statement st = conn.createStatement();
count = st.executeUpdate(cmd);
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
if(count>0) response.sendRedirect("list.jsp");
else response.sendRedirect("error.jsp");
%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%
int count = 0;
String id = request.getParameter("id");
String username = request.getParameter("username");
String password = request.getParameter("password");
if(null != id && id.length()>0){
String cmd = "update t_user set username='"+username+"',password='"+password+"' where id="+id;
try{
Class.forName("com.mysql.jdbc.Driver");
String url= "jdbc:mysql://127.0.0.1/db_user";
String uid = "root";
String pwd = "root";
Connection conn = DriverManager.getConnection(url,uid,pwd);
Statement st = conn.createStatement();
count = st.executeUpdate(cmd);
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
if(count>0) response.sendRedirect("list.jsp");
else response.sendRedirect("error.jsp");
%>
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>db</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>
</web-app>
