[JSP]JDBC 用户登陆，查看修改添加用户
[java]
package com.yyqf.toolbean;
/*
* 数据库操作类
* 查询数据库，获取所有投票选项
* 获取指定IP上一次进行投票的时间
* 更新数据表，实现票数累加
* */
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import com.yyqf.valuebean.User;
public class DB
{
private String DRIVER;
private String URL;
private String USERNAME;
private String PASSWORD;
private Connection conn;
private Statement stmt;
private PreparedStatement prstmt;
private ResultSet rs;
public DB() throws IOException
{
Properties p = new Properties();
InputStream in = getClass().getResourceAsStream("/Config.properties");
p.load(in);
DRIVER = p.getProperty("DRIVER");
URL = p.getProperty("URL");
USERNAME = p.getProperty("USERNAME");
PASSWORD = p.getProperty("PASSWORD");
}
public void getConnection()
{
try {
Class.forName(DRIVER);
} catch (Exception e) {
System.out.println("驱动加载失败");
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (Exception e) {
System.out.println("连接数据库失败");
e.printStackTrace();
}
}
public void getstmt()
{
getConnection();
try {
stmt = conn.createStatement();
stmt.execute("USE User_pwd");
} catch (Exception e) {
System.out.println("获取statment对象失败");
e.printStackTrace();
}
}
public ResultSet getRs(String sql)
{
ResultSet resultSet = null;
getstmt();
try {
rs = stmt.executeQuery(sql);
resultSet = rs;
} catch (Exception e) {
System.out.println("获取resultset失败");
e.printStackTrace();
}
return resultSet;
}
public ResultSet getPrRs(String sql)
{
ResultSet resultSet = null;
try {
getstmt();
prstmt = conn.prepareStatement(sql);
} catch (Exception e) {
System.out.println("获取Prepare statment失败");
e.printStackTrace();
}
try {
rs = prstmt.executeQuery();
resultSet = rs;
} catch (Exception e) {
System.out.println("获取Prepare resultset失败");
e.printStackTrace();
}
return resultSet;
}
public void closed()
{
try {
if (rs!=null) rs.close();
if (stmt!=null) stmt.close();
if (prstmt!=null) prstmt.close();
if (conn!=null) conn.close();
} catch (Exception e) {
System.out.println("关闭数据库失败");
e.printStackTrace();
}
}
public ArrayList<User> SelectAllUser(String sql)
{
ArrayList<User> userlist = null;
if (sql!=null){
getRs(sql);
if (rs!=null){
userlist = new ArrayList<User>();
try {
while (rs.next()){
User temp = new User();
temp.setUsername(MyTools.toChinese(MyTools.change(rs.getString("username"))));
temp.setPassword(MyTools.toChinese(MyTools.change(rs.getString("password"))));
temp.setPower(MyTools.toChinese(MyTools.change(rs.getString("power"))));
userlist.add(temp);
}
} catch (Exception e) {
System.out.println("封装应用信息失败 Accout_user表");
e.printStackTrace();
}finally{
closed();
}
}
}
return userlist;
}
public int update(String sql) //更新数据表
{
int i = -1;
if (sql!=null && !sql.equals(""))
{
getstmt();
try {
i = stmt.executeUpdate(sql);
} catch (Exception e) {
System.out.println("更新数据库失败");
e.printStackTrace();
}finally{
closed();
}
}
return i;
}
<span style="font-size:32px">}
</span>
JSP常用工具封装
[java]
package com.yyqf.toolbean;
/*
* JSP操作工具类*/
import java.sql.Date;
import java.text.SimpleDateFormat;
public class MyTools {
public static String intToStr(int num) //把int转换为字符
{
return String.valueOf(num);
}
public static int strToint(String str) //把字符转换为int
{
if (str==null || str.equals("")) str="0";
int i=0;
try {
i=Integer.parseInt(str);
} catch (Exception e) {
i=0;
e.printStackTrace();
}
return i;
}
public static boolean compareTime(long today,long temp) //比较上次和当前投票时间，参数都是毫秒级时间
{
int limitTime=60; //设置限制时间60分钟
long count=today-temp;
if (count<=limitTime*60*1000) return false;
else return true;
}
public static String formatDate(long ms) //转换时间为指定格式
{
Date date = new Date(ms);
SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd HH:mm:ss");
String strDate = format.format(date);
return strDate;
}
public static String toChinese(String str) //解决乱码问题，转换为中文
{
if (str==null) str="";
try {
str=new String(str.getBytes("ISO-8859-1"),"gb2312");
} catch (Exception e) {
str="";
e.printStackTrace();
}
return str;
}
public static String change(String str) //转换网页表单提交数据
{
str=str.replace("<","<");
str=str.replace(">",">");
return str;
}
}
用户信息ValueBean
[java]
package com.yyqf.valuebean;
public class User {
private String username;
private String password;
private String power;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPower() {
return power;
}
public void setPower(String power) {
this.power = power;
}
}
ajax操作的JS封装
[javascript]
var net=new Object(); //定义一个全局变量net
net.AjaxRequest=function(url,onload,onerror,method,params){//创建一个构造函数
this.req=null;
this.onload=onload;
this.onerror=(onerror) ? onerror : this.defaultError;
this.loadDate(url,method,params);
}
net.AjaxRequest.prototype.loadDate=function(url,method,params){
if (!method){
method="GET";
}
if (window.XMLHttpRequest){
this.req=new XMLHttpRequest();
} else if (window.ActiveXObject){
this.req=new ActiveXObject("Microsoft.XMLHTTP");
}
if (this.req){
try{
var loader=this;
this.req.onreadystatechange=function(){
net.AjaxRequest.onReadyState.call(loader);
}
this.req.open(method,url,true);
//this.req.send(params);
this.req.send(null);
}catch (err){
this.onerror.call(this);
}
}
}
net.AjaxRequest.onReadyState=function(){ //重构onReadyState函数
var req=this.req;
var ready=req.readyState;
if (ready==4){
if (req.status==200 ){
this.onload.call(this);
}else{
this.onerror.call(this);
}
}
}
net.AjaxRequest.prototype.defaultError=function(){ //默认的错误处理函数
alert("error fetching data!"
+"\n\nreadyState:"+this.req.readyState
+"\nstatus: "+this.req.status
+"\nheaders: "+this.req.getAllResponseHeaders());
}
登陆页面
[html]
<%@ page language="java" contentType="text/html; charset=gb2312"
pageEncoding="gb2312"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>登陆</title>
</head>
<body onload="setFocus()">
<b><font size="10">逸雨清风系统登陆</font></b><hr/>
<form id="form1" name="form1" method="post" action="login_deal2.jsp">
<p>用户名: <input name="username" type="text" id="username"/></p>
<script>
function setFocus(){
document.getElementById('username').focus();
}
</script>
<p>密 码: <input name="password" type="password" id="password"/></p>
<input type="submit" name="Submit" value="登陆">
<input type="reset" name="Reset" value="清空">
</form>
</body>
</html>
登录处理页面
[html]
<%@page import="com.yyqf.toolbean.MyTools"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="com.yyqf.toolbean.DB"%>
<%@page import="java.awt.Button"%>
<%@ page language="java" contentType="text/html; charset=gb2312"
pageEncoding="gb2312"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>欢迎登陆</title>
</head>
<body>
<script language="javascript" src="JS/AjaxRequest.js"></script>
<script language="javascript">
//此处需要加&nocache="+new Date().getTime()，否则将出不更新的情况
function showUser(){
var loader=new net.AjaxRequest("showUser.jsp?nocache="+new Date().getTime(),deal_User,onerror,"GET");
}
function onerror(){
alert("很抱歉，服务器出现错误，当前窗口将关闭！");
window.location("login.jsp");
}
function deal_User(){
User.innerHTML=this.req.responseText;
}
</script>
<jsp:useBean id="myDb" class="com.yyqf.toolbean.DB"/>
<%!
boolean login = false;
%>
<%
String username = request.getParameter("username");
String password = request.getParameter("password");
//这里数据库返回的密码末尾的空格无法常规去除，特殊方法去除
String sql="select replace(password,char(32),'') from Account_info where username='"+username+"'";
ResultSet rstemp = myDb.getRs(sql);
if ((rstemp.next() && rstemp.getString(1).equals(password)) || login == true) {
if (login == true) username = (String)session.getAttribute("username");
sql="select replace(power,char(32),'') from Account_info where username='"+username+"'";
rstemp = myDb.getRs(sql);rstemp.next();
out.println("<b><font size='10'>逸雨清风系统登陆</font></b><hr/> ");
out.println("<b><font size='4'> 欢迎用户：</font></b>"+ username);
if (rstemp.getString(1).equals("admin")) out.println("<br><b><font size='4'> 所在用户组： 管理员</font></b><br>");
else out.println("<br><b><font size='4'> 所在用户组： 普通用户</font></b><br>");
session.setAttribute("username", username);
login = true;
}else out.println("<script>alert('用户名或密码错误！');window.location('login.jsp');</script>");
%>
<br/> <a href="#" onClick="showUser();window.setInterval('showUser();',1000);"><font size="4">查看所有用户</font></a>
<a href="do_User.jsp?action=add"><font size="4">添加用户</font></a><br/>
<br><p id="User"></p>
</body>
</html>
用户信息操作处理跳转页面
[html]
<%@page import="com.yyqf.valuebean.User"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://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>
<jsp:useBean id="myDB" class="com.yyqf.toolbean.DB"/>
<%
String action = request.getParameter("action");
String sql = null;
if (action.equals("edit")){
%>
<form name="add" action="edit.jsp">
用户名： <input type="text" name="username">
密码： <input type="password" name="password">
<input type="submit" name="Submit" value="确定">
<input type="reset" name="Reset" value="清空">
</form>
<%
response.sendRedirect("login_deal2.jsp");
}else if (action.equals("delete")){
sql = "delete from Account_info where username='"+request.getParameter("username")+"'";
if (myDB.update(sql)<0) out.println("<script>alert('删除错误！')</script>");
response.sendRedirect("login_deal2.jsp?");
}else if (action.equals("add")){
%>
<form name="add" action="add.jsp">
用户名： <input type="text" name="username">
密码： <input type="password" name="password">
<input type="submit" name="Submit" value="确定">
<input type="reset" name="Reset" value="清空">
</form>
<%
}else response.sendRedirect("login.jsp");
%>
</body>
</html>
显示用户信息
[html]
<%@page import="java.sql.ResultSet"%>
<%@page import="com.yyqf.valuebean.User"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://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>
<table border="1" width="450" rules="none" cellspacing="0" cellpadding="0">
<tr align="center" height="30" bgcolor="lightgrey">
<td>用户名</td>
<td>权限</td>
<td>修改</td>
<td>删除</td>
</tr>
<jsp:useBean id="myDb" class="com.yyqf.toolbean.DB"/>
<%
String sql = "select * from Account_info";
ArrayList<User> Userlist = myDb.SelectAllUser(sql);
for (int i=0;i<Userlist.size();i++){
String power = null;
sql="select replace(power,char(32),'') from Account_info where username='"+Userlist.get(i).getUsername()+"'";
ResultSet temp = myDb.getRs(sql);temp.next();
if (temp.getString(1).equals("admin")) power = "管理员";
else power = "普通用户";
%>
<tr height="35" align="center">
<td><%=Userlist.get(i).getUsername()%></td>
<td><%=power%></td>
<td><a href="do_User.jsp?action=edit&username=<%=Userlist.get(i).getUsername()%>">修改</a></td>
<td><a href="do_User.jsp?action=delete&username=<%=Userlist.get(i).getUsername()%>">删除</a></td>
</tr>
<%} %>
</table>
</body>
</html>
添加用户
[html]
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://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>
<jsp:useBean id="myDB" class="com.yyqf.toolbean.DB"></jsp:useBean>
<%
String username = request.getParameter("username");
String password = request.getParameter("password");
String sql = "insert into Account_info (username,password,power) values('"+username+"','"+password+"','common')";
System.out.println(sql);
if (myDB.update(sql)>0) response.sendRedirect("login_deal2.jsp");
else out.println("<script>alert('添加错误');window.location('login_deal2.jsp');</script>");
%>
</body>
</html>