连接数据库
记得在页面中引包 import java.sql.*;
到如需要的jar包,到lib下
LoginCL.jsp
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="GBK"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'LoginCL.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<%
//接收用户名和密码,完成用户的验证
String username=request.getParameter("username");
String password=request.getParameter("password");
// 到数据库验证用户
Class.forName("org.gjt.mm.mysql.Driver");
//创建连接 记得引包
Connection ct=DriverManager.getConnection("jdbc:mysql://localhost:3306/dwt1220","root","123456");
//创建Statment
Statement sm=ct.createStatement();
//查询
ResultSet rs=sm.executeQuery("select password from users where username='"+ username+"' limit 1");
//根据结果做判断
if(rs.next()){
//用户名存在
if(rs.getString(1).equals(password)){
//如果密码正确
response.sendRedirect("Welcome.jsp");
}else{
response.sendRedirect("Login.jsp?err=pwd");
}
}else{
response.sendRedirect("Login.jsp?err=use");
}
%>
</body>
</html>
分页
算法
需要4个变量
int pageSize;每页显示多少条记录(是指定的)
int pageNew;希望显示第几页(是用户选择的)
int pageCount;一共有多少页 (是从表中查询得到的) //注意,读取数据库的时候一定要rs.next()
int rowCount;一共有多少条记录(是计算出来的 该计算式为)
if(rowCount%pageSize==0){
pageCount=rowCountp/pageSize;
}else{
pageCount=rowCount/pageSize+1;
}
Mysql中
分页使用 limit接受一个或两个数字参数
Limit有2个参数
第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目
初 始记录行的偏移量是 0(而不是 1)
limit 5,从第1条到5条
limit 5, 10,从第6开始显示10条
select * from users limit 0,3;
"select * from users limit "+(pageNow-1)*pageSize+","+pageSize
分页SQL,SERVLET语句
Select top pageSize 字段名 from 表明 where id not in (select top pageSize*(pageNow-1) id from 表名)
例如现实第三页,每页3条
Select top 3 * from users where userid not in (select top 6 userid from user);
数据库自我复制
Insert into users (username,password,email,grade) select username,password,emial,grade form users;
Welcome.jsp
<%@ page language="java" import="java.util.*,java.sql.*;" pageEncoding="GBK"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'Welcome.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
欢迎登录. <br>
<%=request.getParameter("username") %><br/>
<a href="Login.jsp">返回登录页面</a><br/>
<h1>用户列表</h1>
<%
//定义四个分页的变量
int pageSize=3;
int pageNow=1;
int rowCount=0;
int pageCount=0;
String pageNow_str=request.getParameter("pageNow");
if(pageNow_str!=null){
pageNow=Integer.parseInt(pageNow_str);
}
// 到数据库验证用户
Class.forName("org.gjt.mm.mysql.Driver");
//创建连接 记得引包
Connection ct=DriverManager.getConnection("jdbc:mysql://localhost:3306/dwt1220","root","123456");
//创建Statment
Statement sm=ct.createStatement();
ResultSet rs=null;
rs=sm.executeQuery("select count(*) from users");
//一定要rs.next().
if(rs.next()){
rowCount=rs.getInt(1);
}
//计算pageCount
if(rowCount%pageSize==0){
pageCount=rowCount/pageSize;
}else{
pageCount=rowCount/pageSize+1;
}
//查询出需要显示的记录
rs=sm.executeQuery("select * from users limit "+(pageNow-1)*3+","+pageSize );
%>
<table border="1">
<tr><td>ID</td><td>用户名</td><td>密码</td><td>邮编</td><td>权限</td></tr>
<%
while(rs.next()){
%>
<tr>
<td><%=rs.getInt(1)%></td>
<td><%=rs.getString(2) %></td>
<td><%=rs.getString(3) %></td>
<td><%=rs.getString(4) %></td>
<td><%=rs.getInt(5) %></td>
</tr>
<%
}
%>
</table>
<%if(pageNow!=1){ %>
<a href=Welcome.jsp?pageNow=<%=pageNow-1 %>>上一页</a>
<%
}
for(int i=1;i<=pageCount;i++){
%>
<a href=Welcome.jsp?pageNow=<%=i %>>[<%=i %>]</a>
<%
}
%>
<%if(pageNow!=pageCount){ %>
<a href=Welcome.jsp?pageNow=<%=pageNow+1 %>>下一页</a>
<%} %>
</body>
</html>