AJAX实验（添加+模糊查询图书）

本文介绍: 实验要求：在数据库中建立表T_BOOK,其包含图书ID，图书名称，图书价格等1. 制作“添加图书“界面，在界面有一表单，输入对应的信息，提交后能插入数据表，但是页面不刷新。2. 制作一个图书模糊查询界面，输入图书名称的模糊信息，能够显示系统中所有符合条件图书的名称和价格等信息，但是页面不刷新前置知识：HTML、Jav a、JavaSc rip t、servlet 编程、session、MySQL基础AJAX基础API编程，sql 模糊查询…

实验要求

在数据库中建立表T_BOOK,其包含图书ID，图书名称，图书价格等

制作“添加图书”界面，在界面有一表单，输入对应的信息，提交后能插入数据表，但是页面不刷新。
制作一个图书模糊查询界面，输入图书名称的模糊信息，能够显示系统中所有符合条件图书的名称和价格等信息，但是页面不刷新

前置知识

HTML、Java、JavaSc rip t、servlet 编程、session、MySQL基础
AJAX基础API编程，sql模糊查询

实验 过程

项目 文件 结构

web.xml 配置：略

建立数据库

用mysql建立数据库 ts

create database ts;

IDEA连接数据库
在这里插入图片描述

ts 数据库建立t_book表以及添加图书
IDEA中建立sql文件，并运行以下sql代码

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `t_book`
-- ----------------------------
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
  `bookno` varchar(40) DEFAULT NULL,
  `bookname` varchar(40) DEFAULT NULL,
  `author` varchar(40) DEFAULT NULL,
  `booktype` varchar(40) DEFAULT NULL,
  `bookprice` float DEFAULT NULL,
  `bookstate` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_book
-- ----------------------------
INSERT INTO `t_book` VALUES ('001','Java Web程序设计','郭克华','教育',49.8,1);
INSERT INTO `t_book` VALUES ('002', '软件工程导论','张海藩 牟永敏','教育',39.5,1);
INSERT INTO `t_book` VALUES ('003', '通信原理','樊昌信 曹丽娜','教育',58.00,1);
INSERT INTO `t_book` VALUES ('004', '绝代双骄','古龙','小说',407.86,1);
INSERT INTO `t_book` VALUES ('005', '球状闪电','刘慈欣','小说',25.00,1);

编写 java的图书类，放在vo包中

package vo;

public class Book {
    private String bookno;
    private String bookname;
    private String author;
    private String booktype;
    private float bookprice;
    private int bookstate=1;
    public String getBookno(){
        return bookno;
    }
    public void setBookno(String bookno) {
        this.bookno = bookno;
    }
    public String getBookname() {
        return bookname;
    }
    public void setBookname(String bookname) {
        this.bookname = bookname;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public String getBooktype() {
        return booktype;
    }
    public void setBooktype(String booktype) {
        this.booktype = booktype;
    }
    public float getBookprice() {
        return bookprice;
    }
    public void setBookprice(float bookprice) {
        this.bookprice = bookprice;
    }
    public int getBookstate() {
        return bookstate;
    }
    public void setBookstate(int bookstate) {
        this.bookstate = bookstate;
    }
}

制作“添加图书”界面，

（1）代码编写（部分，完整见项目文件）
添加图书页面前端代码

<%@ page import="java.util.HashMap" %>
<%@ page import="vo.Book" %>
<%@ page import="java.util.Iterator" %>
<%@ page import="java.util.Set" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加图书</title>
</head>
<body>
<font size="5" color="blue">欢迎来到三味书屋</font><hr>
    <SCRIPT language="JavaScript">
        function addbook(){
            var xmlHttp = false;
            if(window.XMLHttpRequest){
                xmlHttp = new XMLHttpRequest();
            }
            else if(window.ActiveXObject){
                try{
                    xmlHttp = new ActiveXObject("Msxml2.XMLHTTP");
                }catch (e) {
                    try{
                        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
                    }catch(e){
                        window.alert("该浏览器不支持AJAX");
                    }
                }
            }

            var bookno = document.addbookform.bookno.value;
            var bookname = document.addbookform.bookname.value;
            var author = document.addbookform.author.value;
            var booktype = document.addbookform.booktype.value;
            var bookprice = document.addbookform.bookprice.value;
            var url = "servlets/AddBook?bookno="+bookno+"&amp;bookname="+bookname+
                "&amp;author="+author+ "&amp;booktype="+booktype+"&amp;bookprice="+bookprice;
            xmlHttp.open("POST",url,true);
            // xmlHttp.open("GET","showbooks.jsp",true);
            xmlHttp.onreadystatechange=function(){
                if(xmlHttp.readyState==4){
                    booklist.innerHTML=xmlHttp.responseText;
                }
                else{
                    booklist.innerHTML+="正在添加，请稍后......"
                }
            }
            xmlHttp.send();
        }
    </SCRIPT>
    添加图书信息：<br>
    <form name="addbookform">
        书ID：<input type="text" name="bookno"><br>
        书名：<input type="text" name="bookname"><br>
        作者：<input type="text" name="author"><br>
        类型：
        <select name="booktype">
            <option value="1">教育</option>
            <option value="2">小说</option>
            <option value="3">青春文学/动漫</option>
            <option value="4">文学</option>
            <option value="5">理财</option>
            <option value="6">成功/励志</option>
        </select><br>
        价格：<input type="text" name="bookprice"><br><br>
        <input type="reset" value="清空选择">
        <input type="button" value="添加" onclick="addbook()" width="10"><br>
    </form>
    <a href="findbook.jsp">查询图书</a><br>
    <div id="booklist"></div>
</body>
</html>

展示查询的书的页面 showbooks.jsp

<%@ page import="java.util.HashMap" %>
<%@ page import="java.util.Set" %>
<%@ page import="java.util.Iterator" %>
<%@ page import="vo.Book" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<table border="1" align="center">
    <tr align="center" bgcolor="pink">
        <td>书本id</td>
        <td>书本名称</td>
        <td>作者</td>
        <td>书本类型</td>
        <td>书本价格/￥</td>
    </tr>
    <%
        HashMap allbook = (HashMap) session.getAttribute("allbook");
        Set set = allbook.keySet();
        Iterator ite = set.iterator();
        while(ite.hasNext()) {
            String bookno = (String) ite.next();
            Book book = (Book) allbook.get(bookno);
            if(book.getBookstate()==1){
    %>
    <tr align="center" bgcolor="yellow">
        <td><%= book.getBookno() %></td>
        <td><%= book.getBookname() %></td>
        <td><%= book.getAuthor() %></td>
        <td><%= book.getBooktype() %></td>
        <td><%= book.getBookprice() %></td>
    </tr>
    <%
            }
        }
    %>
</table>
</body>
</html>

编写添加图书代码AddBook.java，加入 servlets包中

package servlets;

import dao.BookDao;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.HashMap;

public class AddBook extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        BookDao bdao = new BookDao();
        // 获取提交的内容
        String bookno = req.getParameter("bookno");
        String bookname = req.getParameter("bookname");
        String author = req.getParameter("author");
        String booktype = req.getParameter("booktype");
        String bookprice = req.getParameter("bookprice");
        try { //在数据库中添加图书
            bdao.addBook(bookno,bookname,author,booktype,bookprice);
        } catch (Exception e) {
            e.printStackTrace();
        }
        HashMap allbook = null;
        try { // 更新allbook
            allbook=bdao.getAllBook();
        } catch (Exception e){
            e.printStackTrace();
        }
        HttpSession session = req.getSession();
        session.setAttribute("allbook",allbook);
        String targetUrl="/showbooks.jsp";
        ServletContext application = this.getServletContext();
        RequestDispatcher rd = application.getRequestDispatcher(targetUrl);
        rd.forward(req,resp);
    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        doPost(req,resp);
    }
}

在BookDao.java中添加addBook()函数

public boolean addBook(String bookno,String bookname,String author,String booktype,String bookprice)
        throws Exception {
    this.initConnection();
    Statement stat = conn.createStatement();
    booktype = numtotype(booktype);
    String sql = "INSERT INTO `t_book` VALUES ('"+bookno+"','"+bookname+"','"
            +author+"','"+booktype+"',"+bookprice+",1)";
    stat.execute(sql);
    this.closeConnection();
    return true;
}

（2）网页效果展示

添加后，表中插入记录，但是页面没有刷新

数据库中也添加了该图书

图书模糊查询界面

书模糊查询界面
（1）代码编写
查询界面前端代码和添加界面类似
后端 servlet 代码

package servlets;

import dao.BookDao;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.util.HashMap;

public class FindBook extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        BookDao bdao = new BookDao();
        HashMap allbook = null;
        // 获取提交的内容
        String bookname = req.getParameter("bookname");
        try { //模糊查询
            allbook = bdao.findBook(bookname);
        } catch (Exception e) {
            e.printStackTrace();
        }
        HttpSession session = req.getSession();
        session.setAttribute("allbook",allbook);
        String targetUrl="/showbooks.jsp";
        ServletContext application = this.getServletContext();
        RequestDispatcher rd = application.getRequestDispatcher(targetUrl);
        rd.forward(req,resp);
    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req,resp);
    }
}

在 BookDao.java 中添加 findBook()函数

public HashMap findBook(String bookname) throws Exception {
    HashMap hm=new HashMap();
    this.initConnection();
    Statement stat = conn.createStatement();
    String sql = "SELECT * from t_book WHERE bookname LIKE '%"+bookname+"%'";
    ResultSet rs = stat.executeQuery(sql);
    while (rs.next()){
        Book book=new Book();
        book.setBookno(rs.getString("bookno"));
        book.setBookname(rs.getString("bookname"));
        book.setAuthor(rs.getString("author"));
        book.setBooktype(rs.getString("booktype"));
        book.setBookprice(rs.getFloat("bookprice"));
        book.setBookstate(rs.getInt("bookstate"));
        hm.put(book.getBookno(),book);
    }
    this.closeConnection();
    return hm;
}

（2）网页效果展示

总结

1、AJAX框架

<html>
<body>
<SCRIPT language="JavaScript">
    function findbook(){
        // 1、AJAX 实例化浏览器内置对象xmlHttp
        var xmlHttp = false;
        if(window.XMLHttpRequest){ // Mozilla等浏览器
            xmlHttp = new XMLHttpRequest();
        }
        else if(window.ActiveXObject){
            try{
                xmlHttp = new ActiveXObject("Msxml2.XMLHTTP"); // IE浏览器
            }catch (e) {
                try{
                    xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
                }catch(e){
                    window.alert("该浏览器不支持AJAX");
                }
            }
        }
        
        var bookname = document.addbookform.bookname.value;
        var url = "servlets/FindBook?bookname="+bookname;
        
        // 2、指定异步提交的目标和提交方式
        xmlHttp.open("POST",url,true);
        // xmlHttp.open("GET","showbooks.jsp",true);
        
        // 3、指定当xmlHttp状态改变时需要进行的处理
        xmlHttp.onreadystatechange=function(){
            if(xmlHttp.readyState==4){
                booklist.innerHTML=xmlHttp.responseText;
            }
            else{
                booklist.innerHTML+="正在搜索，请稍后......"
            }
        }
        
        // 4、发出请求
        xmlHttp.send();
    }
</SCRIPT>
<form name="addbookform">
    书名：<input type="text" name="bookname"><br>
    <input type="button" value="查询" onclick="findbook()"><br>
</form>
<div id="booklist"></div>
</body>
</html>

2、SQL模糊查询实例

// 本语句将会查询出u_name中所有包含“我”的记录。
SELECT * FROM table WHERE u_name LIKE '%我%'

//本语句将会查询出 u_name 中长度为三且第二个字是而的记录。
SELECT * FROM table WHERE u_name LIKE '_而_'

// 找到的结果将可能是‘你们’‘我们’‘他们’
SELECT * FROM table WHERE u_name LIKE '[你我他]们'

// 最后查询的结果中将不能包括’你们‘’我们‘’他们‘
SELECT * FROM [user] WHERE u_name LIKE '[^你我他]们'