实验要求
在数据库中建立表T_BOOK,其包含图书ID，图书名称，图书价格等
- 制作"添加图书"界面，在界面有一表单，输入对应的信息，提交后能插入数据表，但是页面不刷新。
- 制作一个图书模糊查询界面，输入图书名称的模糊信息，能够显示系统中所有符合条件图书的名称和价格等信息，但是页面不刷新
前置知识
HTML、Java、JavaScript、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+"&bookname="+bookname+
"&author="+author+ "&booktype="+booktype+"&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 '[^你我他]们'
690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
