jquery mysql jsp搜索功能_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 list = new ArrayList();

list.add(String.valueOf(id));

while(rs.next()) {

list.add(rs.getString(1));

list.add(String.valueOf(rs.getInt(2)));

}

String str = "

"; str = str + list.get(0) + "

" + list.get(1) + "

" + list.get(2) + "

";

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

pageEncoding="UTF-8"%>

Insert title here

图书ID
图书名字
图书数量

添加

返回到航页

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);

}

});

}

deleteOne.jsp

pageEncoding="UTF-8"%>

Insert title here

请填写要删除图书的ID
图书ID

返回到航页

<

function Delete(){

$.ajax({

type:"GET",

url:"DeleteServlet",

data:{"id":$("#id").val()},

success:function(data){

console.log(data);

$("#result").html(data);

}

});

}

FindAll.jsp

pageEncoding="UTF-8"%>

Insert title here

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 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 = "

ID

书名

数量

for(Book b : list){

str = str + "

" + b.getId() + "";

str = str + "

" + b.getName() + "";

str = str + "

" + b.getBookCount() + "";

}

str += "

";

out.println(str);

rs.close();

sta.close();

con.close();

}

catch(Exception ex) {

ex.printStackTrace();

}

%>

findOne.jsp

pageEncoding="UTF-8"%>

Insert title here

请填写要查找图书的ID
图书ID

返回到航页

<

function find(){

$.ajax({

type:"GET",

url:"FindOneServlet",

data:{"id":$("#id").val()},

success:function(data){

console.log(data);

$("#result").html(data);

}

});

}

index.jsp

pageEncoding="UTF-8"%>

Insert title here

请选择进行的操作

增添图书

删除图书

修改图书

查找某一图书

查看所有图书信息

function findAll(){

$.ajax({

type:"GET",

url:"FindAll.jsp",

success:function(data){

console.log(data);

$("#div1").html(data);

}

});

}

updateOne.jsp

pageEncoding="UTF-8"%>

Insert title here

请填写要删除图书的ID
图书ID	图书数量

返回到航页

<

function update(){

$.ajax({

type:"GET",

url:"UpdateServlet",

data:{"id":$("#id").val(), "bookCount":$("#bookCount").val()},

success:function(data){

console.log(data);

$("#result").html(data);

}

});

}

ps：

使用的技术较为简单，在 Eclipse 中只需按照指示创建并拷贝代码即可。