Servlet+jdbc+jsp增删改查
目录
Servlet
1. 新建一个工程名为servletStudy
2. tomcat xml配置
3. servlet类
User类
Function类
//插入
//查找
//删除
//修改
WEB-INF下xml
Jsp文件
user_list.jsp
insert.jsp
Update.jsp
jdbc连接数据库
Mysql数据库
运行结果
总结
Servlet
1. 新建一个工程名为servletStudy
目录结构层次如下图:
并导入相应的包
2. tomcat xml配置
新建一个xml文件,内容为:
<?xml version="1.0" encoding="UTF-8"?>
<Context docBase="D:/eclipse/workspace/servletStudy/web" reloadable="true" />
3. servlet类
src下新建包com.whpu.test
包中添加class如下:
insertServlet
//添加记录
package com.whpu.test;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@SuppressWarnings("serial")
public class insertServlet extends HttpServlet{
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;chartset=utf-8");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String name = request.getParameter("name");
String password = request.getParameter("password");
//插入
try {
Test1.insertData(name, password);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//无参
response.sendRedirect("FindServlet");
//带参
// request.getRequestDispatcher("user_list.jsp").forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
doGet(request, response);
}
}
Findservlet
//执行查询数据
package com.whpu.test;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@SuppressWarnings("serial")
public class FindServlet extends HttpServlet {
/*private static String name;
private static String password;*/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
//避免乱码
response.setContentType("text/html;chartset=utf-8");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
//查找
try {
Test1.selectData();
request.setAttribute("list", Test1.list);
request.getRequestDispatcher("user_list.jsp").forward(request, response);
} catch (ClassNotFoundException |SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
/*request.setAttribute("name", Test1.name);
request.setAttribute("password", Test1.password);*/
// Test1.list.add(Test1.user);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
doGet(request, response);
}
}
deleteServlet
//删除数据
package com.whpu.test;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@SuppressWarnings("serial")
public class deleteServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
response.setContentType("text/html;chartset=utf-8");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
//获取参数id的值,执行删除
String id = request.getParameter("id");
try {
Test1.deleteData(id);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//重定向到查询
response.sendRedirect("FindServlet");
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
doGet(request, response);
}
}
updateServlet
//修改数据
package com.whpu.test;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@SuppressWarnings("serial")
public class updateServlet extends HttpServlet{
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
//防止出现乱码
response.setContentType("text/html;chartset=utf-8");
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
//从user_list.jsp获取参数id的值
String id = request.getParameter("id");
//从表单中获取name和password的值
String name = request.getParameter("name");
String password = request.getParameter("password");
try {
Test1.updateServlet(id,name,password);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
/*if(Test1.b >0)
{
//重定向到查询
response.sendRedirect("FindServlet");
}
else{
String message = "修改失败!";
request.getSession().setAttribute("message", message);
//重定向到查询
response.sendRedirect("FindServlet");
}*/
//重定向到查询
response.sendRedirect("FindServlet");
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
doGet(request, response);
}
}
User类
package com.whpu.test;
public class User {
private String id;
private String name;
private String password;
private int userCount;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getUserCount() {
return userCount;
}
public void setUserCount(int userCount) {
this.userCount = userCount;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
}
Function类
//相应增删改查的方法
package com.whpu.test;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.whpu.connection.DBconn;
public class Function{
static ResultSet ret = null;
static String sql = null;
static List<User> list;
static User user;
static int b;
/*static String name = null;
static String password = null;*/
//插入
public static void insertData(String username,String password) throws ClassNotFoundException, SQLException {
DBconn.getConnection();
String sql = "insert into user(name , password) values('"+username+"','"+password+"')";
PreparedStatement stmt = DBconn.conn.prepareStatement(sql);
stmt.execute();
//DBconn.conn.close();
}
//查找
public static void selectData() throws ClassNotFoundException, SQLException{
DBconn.getConnection();
String sql = "select * from user";
PreparedStatement stmt = DBconn.conn.prepareStatement(sql);
ret = stmt.executeQuery();
list = new ArrayList<User>();
while(ret.next())
{
user = new User();
String a = ret.getString("id");
user.setId(a);
user.setName(ret.getString("name"));
user.setPassword (ret.getString("password"));
list.add(user);
}
ret.close();
//DBconn.conn.close();
//return list;
}
//删除
public static void deleteData(String id) throws ClassNotFoundException, SQLException{
DBconn.getConnection();
String sql = "delete from user where id = '"+id+"'";
PreparedStatement stmt = DBconn.conn.prepareStatement(sql);
stmt.execute();
}
//修改
public static void updateServlet(String id, String name , String password) throws ClassNotFoundException, SQLException{
DBconn.getConnection();
String sql = "update user set name = '"+name+"' , password = '"+password+"' where id = '"+id+"'";
PreparedStatement stmt = DBconn.conn.prepareStatement(sql);
b = stmt.executeUpdate();
}
}
WEB-INF下xml
<?xml version="1.0" encoding="utf-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0" metadata-complete="true">
<!--查看 -->
<servlet>
<servlet-name>FindServlet</servlet-name>
<servlet-class>com.whpu.test.FindServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>FindServlet</servlet-name>
<url-pattern>/FindServlet</url-pattern>
</servlet-mapping>
<!-- 插入 -->
<servlet>
<servlet-name>insertServlet</servlet-name>
<servlet-class>com.whpu.test.insertServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>insertServlet</servlet-name>
<url-pattern>/insertServlet</url-pattern>
</servlet-mapping>
<!-- 删除 -->
<servlet>
<servlet-name>deleteServlet</servlet-name>
<servlet-class>com.whpu.test.deleteServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>deleteServlet</servlet-name>
<url-pattern>/deleteServlet</url-pattern>
</servlet-mapping>
<!-- 修改 -->
<servlet>
<servlet-name>updateServlet</servlet-name>
<servlet-class>com.whpu.test.updateServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>updateServlet</servlet-name>
<url-pattern>/updateServlet</url-pattern>
</servlet-mapping>
</web-app>
Jsp文件
user_list.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">
<%@page import="java.util.List"%>
<%@page import="com.whpu.test.User"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
<style type="text/css">
td {
font-size: 15px;
}
h2 {
margin: 0px
}
</style>
<script type="text/javascript">
function check(form) {
with (form) {
if (userCount.value == "") {
alert("请输入更新数量!");
return false;
}
if (isNaN(userCount.value)) {
alert("格式错误!");
return false;
}
return true;
}
}
</script>
</head>
<body>
<table align="center" width="450" border="1" height="250"
cellpadding="1" cellspacing="1">
<tr bgcolor="white">
<td align="center" colspan="4">
<h2>用户信息</h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1">
<th><b>id</b></th>
<th><b>name</b></th>
<th><b>password</b></th>
<th><b>操作</b></th>
</tr>
<c:forEach items="${list}" var="user" varStatus="vs">
<tr height="30px">
<td align = "center">${user.id}</td>
<td align = "center" >${user.name}</td>
<td align = "center" >${user.password}</td>
<td align = "center" ><a href = "deleteServlet?id=${user.id}">删除 </a>
<a href = "update.jsp?id=${user.id}">修改</a></td>
</tr>
</c:forEach>
</table>
<h2 align="center">
<a href="insert.jsp">添加用户信息</a>
</h2>
</body>
</html>
insert.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>Update</title>
<%
String info = (String)request.getAttribute("message"); // 获取错误属性
if(info != null) {
%>
<script type="text/javascript" language="javascript">
alert("<%=info%>");
window.location = 'user_list.jsp';
<%
}
%>
</head>
<body>
<%
String id1 = request.getParameter("id");
%>
<form action="updateServlet" name = "update" method = "get">
<h4>请输入要修改的姓名和密码:</h4>
<input type="hidden" name="id" value="<%=id1%>">
name:<input type = "text" name = "name">
password:<input type = "password" name = "password">
<input type = "submit" value = "提交">
</form>
</body>
</html>
Update.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>
<%
request.setCharacterEncoding("utf-8");
%>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert</title>
</head>
<body>
<form name = "input" method = "get" action = "insertServlet">
name:<input type = "text" name = "name">
password:<input type = "password" name = "password">
<input type = "submit" value = "提交">
</form>
</body>
</html>
jdbc连接数据库
com.whpu下新建包connection
包中新建类DBconn
package com.whpu.connection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DBconn {
public static final String url = "jdbc:mysql://127.0.0.1:3306/study?useUnicode=true&characterEncoding=utf-8";
public static final String driver = "com.mysql.jdbc.Driver";
public static final String username = "root";
public static final String password = "951217";
public static Connection conn = null;
public PreparedStatement pst = null;
public DBconn() {}
//获取连接
public static Connection getConnection() throws ClassNotFoundException, SQLException{
if(conn==null){
Class.forName(driver);//加载驱动
conn = DriverManager.getConnection(url,username,password);
}
return conn;
}
public void close() {
try {
DBconn.conn.close();
this.pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Mysql数据库
运行结果
启动tomcat
浏览器中输入url: http://127.0.0.1:8080/ss/FindServlet
总结