这个小项目通过使用 jsp、servlet、jQuery 在网页中对 MySQL 数据库中的数据进行增删改查。
软件:Eclipse
数据库:MySQL
请注意:关于数据库的一些的地方需要进行适当修改
jQuery中使用 Ajax 的语法格式:
$.ajax({
type:" ",//提交的格式:GET 或 POST
url:" ",//要提交的“地方”
data:{key:value, key:value, `````},//待提交的数据
success:function(data){
//处理数据的操作
},//处理成功返回的数据
});
项目的文件
代码
Book.java
package com.lyq.bean;
public class Book {
private int id;
private String name;
private int bookCount;
public int getId() {return id;}
public String getName() {return name;}
public int getBookCount() {return bookCount;}
public void setId(int id) {this.id = id;}
public void setName(String name) {this.name = name;}
public void setBookCount(int bookCount) {this.bookCount = bookCount;}
}
AddServlet.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.*;
/**
* Servlet implementation class AddServlet
*/
@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
response.setCharacterEncoding("utf-8");
int id = Integer.valueOf(request.getParameter("id"));
String name = request.getParameter("bookName");
int bookCount = Integer.valueOf(request.getParameter("bookCount"));
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/tuShu","root","111111");
String sql = "insert into xinXi(id, name, bookCount) values(?, ?, ?)";
PreparedStatement pr = con.prepareStatement(sql);
pr.setInt(1, id);
pr.setString(2, name);
pr.setInt(3, bookCount);
int row = pr.executeUpdate();
String str = "成功添加" + row + "条数据!";
if(row > 0){
response.getWriter().print(str);
}
else {
response.getWriter().print("添加失败!");
}
pr.close();
con.close();
}
catch(Exception ex){
response.getWriter().print("失败!");
ex.printStackTrace();
}
}
}
DeleteServlet.java
package Servlet;
import java.sql.*;
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;
/**
* Servlet implementation class DeleteServlet
*/
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
response.setCharacterEncoding("UTF-8");
int id = Integer.valueOf(request.getParameter("id"));
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/tuShu","root","111111");
String sql = "delete from xinXi where id=?";
PreparedStatement pr = con.prepareStatement(sql);
pr.setInt(1,id);
int row = pr.executeUpdate();
String str = "成功删除" + row + "条数据!";
if(row > 0){
response.getWriter().print(str);
}
else {
response.getWriter().print("删除失败!");
}
pr.close();
con.close();
}
catch(Exception ex){
response.getWriter().print("失败!");
ex.printStackTrace();
}
}
}
FindOneServlet.java
package Servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
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;
/**
* Servlet implementation class FindOneServlet
*/
@WebServlet("/FindOneServlet")
public class FindOneServlet extends HttpServlet {
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
response.setCharacterEncoding("UTF-8");
int id = Integer.valueOf(request.getParameter("id"));
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/tuShu","root","111111");
Statement sta = con.createStatement();
String sql = "select name, bookCount from xinXi where id=" + id;
ResultSet rs = sta.executeQuery(sql);
List<String> list = new ArrayList<String>();
list.add(String.valueOf(id));
while(rs.next()) {
list.add(rs.getString(1));
list.add(String.valueOf(rs.getInt(2)));
}
String str = "<table align=\"center\" border=\"1\"><tr><td>";
str = str + list.get(0) + "</td><td>" + list.get(1) + "</td><td>" + list.get(2) + "</td></table>";
response.getWriter().print(str);
rs.close();
sta.close();
con.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
}
}
UpdateServlet.java
package Servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class UpdateServlet
*/
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setCharacterEncoding("utf-8");
int id = Integer.valueOf(request.getParameter("id"));
int bookCount = Integer.valueOf(request.getParameter("bookCount"));
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/tuShu","root","111111");
String sql = "update xinXi set bookCount=? where id=?";
PreparedStatement pr = con.prepareStatement(sql);
pr.setInt(1,bookCount);
pr.setInt(2, id);
int row = pr.executeUpdate();
String str = "成功修改" + row + "条数据!";
if(row > 0){
response.getWriter().print(str);
}
else {
response.getWriter().print("修改失败!");
}
pr.close();
con.close();
pr.close();
con.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
}
}
addBook.jsp
<%@ 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>
<table align="center" border="1">
<tr>
<td>图书ID</td>
<td><input type="text" id="id"></td>
</tr>
<tr>
<td>图书名字</td>
<td><input type="text" id="bookName"></td>
</tr>
<tr>
<td>图书数量</td>
<td><input type="text" id="bookCount"></td>
</tr>
</table>
<button type="button" onclick="add()">添加</button>
<div id="result" align="center"></div>
<a href="index.jsp">返回到航页</a>
<script type="text/javascript" src="jQuery/jquery-3.4.1.js"></script>
<script type="text/javascript">
function add(){
$.ajax({
type:"GET",
url:"AddServlet",
data:{"id":$("#id").val(), "bookName":$("#bookName").val(), "bookCount":$("#bookCount").val()},
success:function(data){
console.log(data);
$("#result").html(data);
}
});
}
</script>
</body>
</html>
deleteOne.jsp
<%@ 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>
<table align="center" border="1">
<tr colspan="2"><td>请填写要删除图书的ID</td></tr>
<tr><td>图书ID</td><td><input type="text" id="id"></td></tr>
<tr colspan="2"><td><input type="button" value="删除" onclick="Delete()"></td></tr>
</table>
<div id="result" align="center"></div>
<a href="index.jsp">返回到航页</a>
<script type="text/javascript" src="jQuery/jquery-3.4.1.js"></script><
<script type="text/javascript">
function Delete(){
$.ajax({
type:"GET",
url:"DeleteServlet",
data:{"id":$("#id").val()},
success:function(data){
console.log(data);
$("#result").html(data);
}
});
}
</script>
</body>
</html>
FindAll.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*" import="com.lyq.bean.*" import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/tuShu","root","111111");
String sql = "select * from xinXi";
Statement sta = con.createStatement();
ResultSet rs = sta.executeQuery(sql);
ArrayList<Book> list = new ArrayList<>();
while(rs.next()) {
Book book = new Book();
book.setId(rs.getInt(1));
book.setName(rs.getString(2));
book.setBookCount(rs.getInt(3));
list.add(book);
}
String str = "<table align='center' width='450' border='1'><tr><td>ID</td><td>书名</td><td>数量</td></tr>";
for(Book b : list){
str = str + "<tr><td>" + b.getId() + "</td>";
str = str + "<td>" + b.getName() + "</td>";
str = str + "<td>" + b.getBookCount() + "</td></tr>";
}
str += "</table>";
out.println(str);
rs.close();
sta.close();
con.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
%>
</body>
</html>
findOne.jsp
<%@ 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>
<table align="center" border="1">
<tr colspan="2"><td>请填写要查找图书的ID</td></tr>
<tr><td>图书ID</td><td><input type="text" id="id"></td></tr>
<tr colspan="2"><td><input type="button" value="查找" onclick="find()"></td></tr>
</table>
<div id="result" align="center"></div>
<a href="index.jsp">返回到航页</a>
<script type="text/javascript" src="jQuery/jquery-3.4.1.js"></script><
<script type="text/javascript">
function find(){
$.ajax({
type:"GET",
url:"FindOneServlet",
data:{"id":$("#id").val()},
success:function(data){
console.log(data);
$("#result").html(data);
}
});
}
</script>
</body>
</html>
index.jsp
<%@ 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>
<table align="center" border="1">
<tr>
<td>请选择进行的操作</td>
</tr>
<tr align="center">
<td><a href="addBook.jsp">增添图书</a></td>
</tr>
<tr align="center">
<td><a href="deleteOne.jsp">删除图书</a></td>
</tr>
<tr align="center">
<td><a href="updateOne.jsp">修改图书</a></td>
</tr>
<tr align="center">
<td><a href="findOne.jsp">查找某一图书</a></td>
</tr>
<tr>
<td><button type="button" onclick="findAll()">查看所有图书信息</button></td>
</tr>
</table>
<div id="div1" align="center"></div>
<script type="text/javascript" src="jQuery/jquery-3.4.1.js"></script>
<script type="text/javascript">
function findAll(){
$.ajax({
type:"GET",
url:"FindAll.jsp",
success:function(data){
console.log(data);
$("#div1").html(data);
}
});
}
</script>
</body>
</html>
updateOne.jsp
<%@ 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>
<table align="center" border="1">
<tr colspan="2"><td>请填写要删除图书的ID</td></tr>
<tr><td>图书ID</td><td>图书数量</td></tr>
<tr><td><input type="text" id="id"></td><td><input type="text" id="bookCount"></td></tr>
<tr colspan="2"><td><input type="button" value="修改" onclick="update()"></td></tr>
</table>
<div id="result"></div>
<a href="index.jsp">返回到航页</a>
<script type="text/javascript" src="jQuery/jquery-3.4.1.js"></script><
<script type="text/javascript">
function update(){
$.ajax({
type:"GET",
url:"UpdateServlet",
data:{"id":$("#id").val(), "bookCount":$("#bookCount").val()},
success:function(data){
console.log(data);
$("#result").html(data);
}
});
}
</script>
</body>
</html>
ps:
使用的技术较为简单,在 Eclipse 中只需按照指示创建并拷贝代码即可。