JavaWeb项目实现SQL Server的增删改查登录（大作业）
简单的Javaweb实现对数据库的登录删除更改等功能，简单易懂，详细明了，请大家指点错误。
·
目录
目录
一，使用工具
1.eclipse
2.SQL Server 2008
3.apache-tomact-8.0.9
链接：https://pan.baidu.com/s/1H23n_urB1iF8rlqQKE5JdA
提取码：oiv9
4.sqljdbc4-2.0.
链接：https://pan.baidu.com/s/1ynSxjo_tJmSUoPjrFJmp9A
提取码：27k0
二，前期准备---数据库
1，搭建数据库
（1）新建一个数据库起名为School。创建一个新的表起名为Student,数据库结构基本为下图。
（2）将列名与数据类型修改如下图
注意：！！！
stuid需要设置自增（设置方法：将--表示规范--是标识 修改为是）
（3）修改数据库，用户名密码，修改登录属性
右键点击 sa(自己的用户名)选择属性，弹出来了登录属性，在次修改密码admin(都可以自己记住即可)
在登录属性页面，将登录修改为启用。确定。
（4）更改服务器身份验证
右击数据库---选择属性---在服务器属性中--点击安全性--选择SQL Server和Windows身份验证模式
（5）重启关闭sql或者重启sql服务
重启SQL Server(MSSQLSERVER)这个服务
（5）保存表格，测试数据库连通性
断开连接
重新连接数据库
选择SQL Server 身份验证
输入你自己的用户名与密码
登录成功，即可！！！
二，创建Eclipse项目
1.创建一个web项目
Ctrl+N快捷方式 ///或者选择左侧空白处右击选择New 再Other..
搜索web 选择Dynamic Web Project
在Project name中填项目名称，在Target runtime(目标运行时)选择Apache Tomcat v8.0服务
点击New Runtime...
在New Runtime...中选择Apache Tomcat v8.0(要与自己下载的Apache tomcat版本相适应)
Apache Tomcat v8.0下载链接（要解压到C盘的根目录）
链接：https://pan.baidu.com/s/1H23n_urB1iF8rlqQKE5JdA
提取码：oiv9
在此处导入apache-tomcat-8.0.9
前期环境搭建完成
在lib目录下放着sqljdbc4-2.0.jar
链接：https://pan.baidu.com/s/1ynSxjo_tJmSUoPjrFJmp9A
提取码：27k0
三，项目搭建
1.项目介绍
项目结构
项目名
src
dao 数据访问对象，对数据库进行增删改查操作
Common.java 对数据库执行增删改查的公共代码
filter 过滤器 ，设置请求和响应的字符编码格式
EncodingFilter.java 过滤字符编码格式的，[！！！@WebFilter("/")]
pojo 简单的Java对象，封装向jsp页面传递的数据
Teacher.java [提醒：
1，对应数据库表中的所有列，都要变成私有的属性，并且每个属性的第一一个单词首字母都是小写。
2，通过右键创建2个构造方法 （方法是 右键---source----generate construction...）
3，所有的属性，生成对应的get和set方法 （方法是 右键---source----generate getter and setter...）
servlet 服务连接器 接受并处理客户端浏览器提交的请求
SelectServlet.java 负责查询学生信息
lnsertServlet.java 负责新增学生信息
UpdateServlet.java 负责修改学生信息
LoginServlet.java 负责登录
ByidServlet.java 负责查询要编辑的学生信息
DeleteServlet.java 负责删除学生信息
WebContent
WEB-INF
lib
sqlijdbc4-2.0.jar 必须把jar文件放入lib目录
web.xml 创建web项目时通过选择generate web.xml
select.jsp 查询页面
edit.jsp 编辑
index.jsp
reg.jsp 注册
denglu.jsp 登录页面
项目结构如图所示
四，代码展示 -jsp部分
（1）制作登录页面denglu.jsp
<%@ 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>
<form action="LoginServlet" method="post">
用户名:<input type="text" name="userName" id="userName"/><br>
密码:<input type="password" name="userPwd" id="userPwd"/><br>
<input type="submit" value="登录">
</form>
</body>
</html> 效果图
（2）制作注册页面reg.jsp
<%@ 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>
<form action="RegServlet " method="post">
姓名：<input type="text" name="uname"><br>
密码：<input type="password" name="upwd"><br>
性别：<input type="radio" name="sex" checked="checked" value="男">男<input type="radio" name="sex" value="女">女<br>
爱好：<input type="checkbox" name="love" value="1">玩游戏
<input type="checkbox" name="love" value="2">看电视
<input type="checkbox" name="love" value="3">看电影<br>
年级：<select name="grade">
<option value="1">大一</option>
<option value="2">大二</option>
<option value="3">大三</option>
<option value="4">大四</option>
</select><br>
简介：<textarea rows="5" cols="10" name="info"></textarea><br>
<input type="submit" value="保存">
</form>
</body>
</html>效果图
3.制作查询页面select.jsp
<%@page import="pojo.Student"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
查询条件
<hr />
<form action="QueryServlet" method="post">
姓名：<input type="text" name="uname" />
年级：
<select name="grade">
<option value="0">全部</option>
<option value="1">大一</option>
<option value="2">大二</option>
<option value="3">大三</option>
<option value="4">大四</option>
</select>
<input type="submit" value="搜索" />
<a href="reg.jsp">新增</a>
</form>
<hr />
查询结果
<hr />
<table border="1" width="100%">
<tr>
<td>编号</td>
<td>姓名</td>
<td>性别</td>
<td>爱好</td>
<td>年级</td>
<td>简介</td>
<td>操作</td>
</tr>
<%
List<Student> list =(List<Student>)session.getAttribute("stuList");
if(list!=null){
for(Student stu : list){
String love=stu.getLove().replace("[","").replace("]","").replace("1","吃饭").replace("2","睡觉").replace("3","看电影");
String grade=(stu.getGrade()+"").replace("1","大一").replace("2","大二").replace("3","大三").replace("4","大四");
%>
<tr>
<td><%=stu.getStuid() %></td>
<td><%=stu.getStuname() %></td>
<td><%=stu.getSex() %></td>
<td><%=love %></td>
<td><%=grade %></td>
<td><%=stu.getInfo() %></td>
<td>
<a href="ByIDServlet?stuid=<%=stu.getStuid()%>">编辑</a>
<a href="DelServlet?stuid=<%=stu.getStuid()%>" onclick="return confirm('确定删除吗？')">删除</a>
</td>
</tr>
<%
}
}
%>
</table>
</body>
</html>效果图
4.制作表格 index.jsp
<%@ 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>
<%
for(int i=0;i<5;i++){
%>
<hr>
<%
}
%>
<table border="1">
<tr><td>编号</td><td>姓名</td><td>年龄</td></tr>
<%
for(int i=0;i<5;i++){
%>
<tr><td><%="0"+i %></td><td>李四</td><td>12</td></tr>
<%
}
%>
</table>
<hr>
<% String name="星期"; %>
<input type="text" name="uname" value="<%=name%>">
</body>
</html>效果图
5.制作编辑页面edit.jsp
<%@page import="pojo.Student"%>
<%@ 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>
<%
Student stu=(Student)session.getAttribute("editStu");
%>
<form action="EditServlet" method="post">
<input type="hidden" name="stuid" value="<%=stu.getStuid()%>"/>
姓名：<input type="text" name="uname" value="<%=stu.getStuname()%>"/><br/>
密码：<input type="password" name="upwd" value="<%=stu.getStupwd()%>"/><br/>
性别：
<input type="radio" name="sex" <%if(stu.getSex().equals("男")){%> checked="checked" <%}%> value="男"/>男
<input type="radio" name="sex" <%if(stu.getSex().equals("女")){%> checked="checked" <%}%> value="女"/>女<br/>
爱好：
<input type="checkbox" name="love" <%if(stu.getLove().indexOf("1")>-1){%> checked="checked" <%}%> value="1"/>吃饭
<input type="checkbox" name="love" <%if(stu.getLove().indexOf("2")>-1){%> checked="checked" <%}%> value="2"/>睡觉
<input type="checkbox" name="love" <%if(stu.getLove().indexOf("3")>-1){%> checked="checked" <%}%> value="3"/>看电影<br/>
年级：<select name="grade">
<option value="1" <%if(stu.getGrade()==1){%> selected="selected" <%}%> >大一</option>
<option value="2" <%if(stu.getGrade()==2){%> selected="selected" <%}%> >大二</option>
<option value="3" <%if(stu.getGrade()==3){%> selected="selected" <%}%> >大三</option>
<option value="4" <%if(stu.getGrade()==4){%> selected="selected" <%}%> >大四</option>
</select> <br/>
简介:
<textarea rows="5" cols="10" name="info"> <%=stu.getInfo()%></textarea><br/>
<input type="submit" value="保存"/>
</form>
</body>
</html>五，代码展示---src部分
1.设置dao文件夹
dao数据访问对象，对数据库进行增删改查操作
在次文件夹下，创建Java项目，用于数据库执行增删改查的公共代码
1.创建Common.java
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Common {
//
private Connection conn =null;
private Statement stmt =null;
private ResultSet rs=null;
private Connection getConnection() {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=school", "sa","admin");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public void close() {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
public ResultSet executeQuery(String sql){
try {
conn=this.getConnection();
stmt = conn.createStatement();//
rs=stmt.executeQuery(sql);
} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
public int executeUpdate(String sql) {
int row = 0;
try {
conn=this.getConnection();
stmt = conn.createStatement();//
row = stmt.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
this.close();
}
return row;
}
}
2.设置filter文件夹
1.编辑EncodingFilter.java文件夹
package filter;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;
@WebFilter("/*")
public class EncodingFilter implements Filter {
public EncodingFilter() {
// TODO Auto-generated constructor stub
}
public void destroy() {
// TODO Auto-generated method stub
}
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
chain.doFilter(request, response);
}
public void init(FilterConfig fConfig) throws ServletException {
// TODO Auto-generated method stub
}
}
3.设置pojo文件夹
1.设置Student.java
package pojo;
public class Student {
private int stuid;
private String stuname;
private String stupwd;
private String sex;
private String love;
private int grade;
private String info;
public Student() {
super();
// TODO Auto-generated constructor stub
}
public Student(int stuid, String stuname, String stupwd, String sex, String love, int grade, String info) {
super();
this.stuid = stuid;
this.stuname = stuname;
this.stupwd = stupwd;
this.sex = sex;
this.love = love;
this.grade = grade;
this.info = info;
}
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStuname() {
return stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
public String getStupwd() {
return stupwd;
}
public void setStupwd(String stupwd) {
this.stupwd = stupwd;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getLove() {
return love;
}
public void setLove(String love) {
this.love = love;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
}
4.设置servlet文件夹
1.编辑ByIDServlet.java
package servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import dao.Common;
import pojo.Student;
@WebServlet("/ByIDServlet")
public class ByIDServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public ByIDServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1、获取要修改的学生编号
String stuid=request.getParameter("stuid");
//2、拼接根据编号查询学生信息的sql语句
String sql="select * from student where stuid="+stuid;
//3、调用查询方法
Common com = new Common();
ResultSet rs=com.executeQuery(sql);
Student stu=null;
try {
while (rs.next()) {
stu = new Student(rs.getInt("stuid"), rs.getString("stuname"), rs.getString("stupwd"), rs.getString("sex"),
rs.getString("love"), rs.getInt("grade"), rs.getString("info"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
com.close();
}
//4、把根据编号查询到的学生信息保存到session中
request.getSession().setAttribute("editStu", stu);
//5、跳转到编辑页面
response.sendRedirect("edit.jsp");
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
2.编辑DelServlet.java
package servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.Common;
@WebServlet("/DelServlet")
public class DelServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public DelServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.获取要删除的学生编号
String stuid=request.getParameter("stuid");
//2.组合新的删除sql语句
String sql="delete from student where stuid="+stuid;
//3.调用删除方法
Common com = new Common();
int row=com.executeUpdate(sql);
//4.给出删除提示
if(row>0){
//删除成功给出提示，之后回到查询页面，最好刷新数据
response.getWriter().print("<script>alert('删除成功！');location.href='QueryServlet?uname=&grade=0'</script>");
}else{
//删除失败给出提示，之后回到查询页面
response.getWriter().print("<script>alert('删除失败！');location.href='select.jsp'</script>");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
3.编辑EditServlet.java
package servlet;
import java.io.IOException;
import java.util.Arrays;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.Common;
@WebServlet("/EditServlet")
public class EditServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public EditServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.
String stuid=request.getParameter("stuid");
String uname=request.getParameter("uname");
String upwd=request.getParameter("upwd");
String sex=request.getParameter("sex");
String[] loves=request.getParameterValues("love");
String grade=request.getParameter("grade");
String info=request.getParameter("info");
//2.
String str="update student set stuname='%s',stupwd='%s',sex='%s',love='%s',grade='%s',info='%s' where stuid=%s";
String sql=String.format(str,uname,upwd,sex,Arrays.toString(loves),grade,info,stuid);
//3.
Common com = new Common();
int row=com.executeUpdate(sql);
//4.
if(row>0){
//删除成功给出提示，之后回到查询页面，最好刷新数据
response.getWriter().print("<script>alert('保存成功！');location.href='QueryServlet?uname=&grade=0'</script>");
}else{
//删除失败给出提示，之后回到查询页面
response.getWriter().print("<script>alert('保存失败！');location.href='edit.jsp'</script>");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
4.编辑LoginServlet.java
package servlet;
import dao.Common;
import java.io.IOException;
import java.sql.ResultSet;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.Common;
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public LoginServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String name=request.getParameter("userName");
String pwd=request.getParameter("userPwd");
System.out.println(name+"......."+pwd);
String str="select * from student where stuname='%s' and stupwd='%s'";
String sql=String.format(str,name,pwd);
Common com = new Common();
ResultSet rs=com.executeQuery(sql);
try {
if (rs.next()) {
response.sendRedirect("select.jsp");
} else {
response.getWriter().println("<script>alert('用户名或密码错误！');location.href='denglu.jsp'</script>");
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
com.close();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}5.编辑QueryServlet.java
package servlet;
import java.io.IOException;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.Common;
import pojo.Student;
@WebServlet("/QueryServlet")
public class QueryServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public QueryServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String uname = request.getParameter("uname");
String grade = request.getParameter("grade");
String str = "select * from student where stuname like '%s'";
if (!grade.equals("0")) {
str += " and grade=%s";
}
String sql = String.format(str, "%" + uname + "%", grade);
System.out.println(sql);
Common com = new Common();
ResultSet rs = com.executeQuery(sql);
List<Student> list = new ArrayList<Student>();
try {
while (rs.next()) {
Student stu = new Student(rs.getInt("stuid"), rs.getString("stuname"), rs.getString("stupwd"), rs.getString("sex"),
rs.getString("love"), rs.getInt("grade"), rs.getString("info"));
list.add(stu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
com.close();
}
request.getSession().setAttribute("stuList", list);
response.sendRedirect("select.jsp");//閲嶅畾鍚�
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
6.编辑RegServlet.java
package servlet;
import java.io.IOException;
import java.util.Arrays;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import dao.Common;
@WebServlet("/RegServlet")
public class RegServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public RegServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String uname=request.getParameter("uname");
String upwd=request.getParameter("upwd");
String sex=request.getParameter("sex");
String[] loves=request.getParameterValues("love");
String grade=request.getParameter("grade");
String info=request.getParameter("info");
System.out.println(uname+"——"+upwd+"——"+"——"+Arrays.toString(loves)+sex+"——"+grade+"——"+info);
Common com = new Common();
String str="insert into student(stuname,stupwd,sex,love,grade,info)values('%s','%s','%s','%s',%s,'%s')";
String sql=String.format(str, uname,upwd,sex,Arrays.toString(loves),grade,info);
System.out.println(sql);
int row = com.executeUpdate(sql);
if(row > 0){
response.getWriter().print("<script>alert('保存成功！');location.href='QueryServlet?uname=&grade=0 '</script>");
}else{
response.getWriter().print("<script>alert('保存失败！');location.href='reg.jsp'</script> ");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
7.设置web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
<display-name>zbvc</display-name>
<welcome-file-list>
<welcome-file>login.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>
本社区面向用户介绍CSDN开发云部门内部产品使用和产品迭代功能，产品功能迭代和产品建议更透明和便捷
更多推荐
31
1- 0
已为社区贡献1条内容
所有评论(0)