JSP+Servlet+jQuery实现简单的 MySQL 数据库的增、删、改、查

这个小项目通过使用 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 中只需按照指示创建并拷贝代码即可。

  • 4
    点赞
  • 29
    收藏
    觉得还不错? 一键收藏
  • 6
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值