基于JSP和mysql的会员增删改查项目
我的数据库
注意事项
- 使用jdbc连接数据库需导入mysql的jar包;
- 注意字符编码防止中文乱码
源码
1.查看界面 :
1.1 Project_query.jsp
<%@ page import="java.sql.*" language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
<title>会员查看</title>
</head>
<body>
<a href="Project_insert.jsp"><span style="margin-left:10px">添加</span></a>
<hr color="green">
<table width="98%" align="center" border="0" cellpadding="4" cellspacing="1" bgcolor="#CBD8AC" style="margin-botton:8px">
<tr align="center" bgcolor="#FAFAFA">
<td>ID</td>
<td>姓名</td>
<td>性别</td>
<td>年龄</td>
<td>操作</td>
</tr>
<%
ResultSet rs=null;
Statement statement=null;
try{
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/JSP_visit_database?useUnicode=true&characterEncoding=UTF-8","root","123456");
String sql="select * from user order by ID asc";
statement=connection.createStatement();
rs=statement.executeQuery(sql);
int pagesize,count,pageCount,intpage; //一页显示的记录数,总记录,总页数,当前页
String strPage; //当前页的字符串形式
int i;
pagesize=3;
strPage=request.getParameter("page");
if(strPage==null){
intpage=1;
}else{
//将strPage转换成int类型,因为通过request获取到的都是string类型
intpage=Integer.parseInt(strPage);
}
if(intpage<1){
intpage=1;
}
rs.last(); //将光标移到ResultSet结果集最后一行
count=rs.getRow();
pageCount=(count+pagesize-1)/pagesize; //计算总页数
if(intpage>count){
//如果当前页大于总记录数,设置当前页的值等于总记录数
intpage=count;
}
if(count>0){
rs.absolute((intpage-1)*pagesize+1);
}
i=0;
while(i<pagesize&&!rs.isAfterLast()){
%>
<tr align="center" bgcolor="#FAFAFA">
<td><%=rs.getInt("ID") %></td>
<td><%=rs.getString("NAME") %></td>
<td><%=rs.getString("SEX") %></td>
<td><%=rs.getInt("AGE") %></td>
<td><a href="Project_editUser.jsp?id=<%=rs.getInt("ID") %>">修改</a>|
<a href="Project_deleteUser.jsp?id=<%=rs.getInt("ID") %>">删除</a>
</td>
</tr>
<%
rs.next();
i++;
}
rs.close();
statement.close();
connection.close();
%>
</table>
<div align="center">
共<%=count %>条记录,分<%=pageCount %>页显示,当前是第<%=intpage %>页
<%if(intpage>1){
%>
<a href="Project_query.jsp?page=<%=intpage-1 %>">上一页</a>
<%}%>
<%
if(intpage<pageCount){
%> <a href="Project_query.jsp?page=<%=intpage+1 %>">下一页</a>
<%}
%>
<%}catch(Exception e){
e.printStackTrace();
out.print("Something is wrong");
}
%> </div>
</body>
</html>
1.2 效果图
2.添加界面及其后台处理界面
2.1添加界面: Project_insert.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加用户</title>
</head>
<body>
<div align="center">
<center><h3 style="margin-top:5px">添加用户</h3><hr color="green">
<form action="Project_add.jsp" method="post">
姓名:<input type="text" name="name"><br><br>
性别:<input type="text" name="sex"><br><br>
年龄:<input type="text" name="age"><br><br>
<input type="submit" value="提交">
<input type="reset" value="重置">
</form>
</center>
</div>
</body>
</html>
2.2添加的后台处理界面:Project_add.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加用户</title>
</head>
<body>
<center><h3 style="margin-top:5px">添加用户</h3></center><hr color="green">
<% request.setCharacterEncoding("UTF-8"); //防止中文输入产生乱码
String name=request.getParameter("name");
String sex=request.getParameter("sex");
String age=request.getParameter("age");
try{
Class.forName("com.mysql.jdbc.Driver"); //加载驱动
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/JSP_visit_database?useUnicode=true&characterEncoding=UTF-8","root","123456"); //连接数据库
String sql="insert into user values(0,?,?,?)"; //0表示占位符
PreparedStatement ps=connection.prepareStatement(sql);
ps.setString(1,name);
ps.setString(2,sex);
ps.setString(3,age);
int result=ps.executeUpdate();
if(result==1){
//添加成功后跳回查看界面 response.sendRedirect("Project_query.jsp");
}
else{
out.print("添加失败");
}
}catch(Exception e){
out.print("there is something wrong");
}
%>
</body>
</html>
2.3 效果图
2.4添加成功后:
3.删除代码界面:
3.1 Project_deleteUser.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>删除用户</title>
</head>
<body>
<center><h3 style="margin-top:5px">删除用户</h3></center><hr color="green">
<%
String id=request.getParameter("id"); //获取Project_query页面的参数id
int userid=Integer.parseInt(id);
try{
Class.forName("com.mysql.jdbc.Driver"); //加载驱动
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/JSP_visit_database","root","123456"); //连接数据库
String sql="delete from user where id="+userid; //0表示占位符
PreparedStatement ps=connection.prepareStatement(sql);
int result=ps.executeUpdate();
if(result==1){
response.sendRedirect("Project_query.jsp");
}
else{
out.print("删除失败");
}
}catch(Exception e){
out.print("there is something wrong");
}
%>
</body>
</html>
3.2 点击删除上图ID为679的用户后用户人员如下:
4.修改代码及相关界面
4.1.1 Project_editUser.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" 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>
<body>
<%
request.setCharacterEncoding("UTF-8");
String id=request.getParameter("id");
int userid=Integer.parseInt(id);
try{
ResultSet rs=null;
Statement statement=null;
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/JSP_visit_database","root","123456");
String sql="select * from user where id="+userid;
statement=connection.createStatement();
rs=statement.executeQuery(sql);
String name,sex,age;
while(rs.next()){
name=rs.getString("NAME");
sex=rs.getString("SEX");
age=rs.getString("AGE");
request.setAttribute("name",name);
request.setAttribute("sex",sex);
request.setAttribute("age",age);
%>
<div>
<center><h3>修改用户信息</h3> <hr color="green">
<form action="Project_alter.jsp?id=<%=userid%>" method="post">
姓名:<input name="name" type="text" value="${name}"><br> <br>
性别:<input name="sex" type="text" value="${sex}"><br><br>
年龄:<input name="age" type="text" value="${age}"><br><br>
<input type="submit" value="提交">
</form></center></div>
<% //上面${}为EL表达式,可在jsp脚本编制元素范围外使用运行时表达式的功能
}
}catch(Exception e){
out.print("There is something wrong");
}
%>
</body>
</html>
4.1.2修改用户强哥
4.2.1 修改后的处理:Project_alter.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" 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>Insert title here</title>
</head>
<body>
<% request.setCharacterEncoding("UTF-8");
String name=request.getParameter("name");
String sex=request.getParameter("sex");
String age=request.getParameter("age");
int userid=Integer.parseInt(request.getParameter("id"));
try{
Class.forName("com.mysql.jdbc.Driver");
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/JSP_visit_database","root","123456");
String sql="update user set name='"+name+"',sex='"+sex+"',age='"+age+"'where id="+userid;
PreparedStatement ps=connection.prepareStatement(sql);
int result=ps.executeUpdate();
if(result==1){
response.sendRedirect("Project_query.jsp");
}else{
out.print("修改失败");
}
}catch(Exception e){
out.print("There is something wrong here");
}
%>
</body>
</html>
4.2.2 修改后的结果
目录
用 [TOC]
来生成目录: