数据库代码:
1)建立数据库:
DROP DATABASE if exists book;
CREATE DATABASE book default charset=utf8mb4;
2)建立数据表:
Use book;
CREATE TABLE books ( id int, name varchar(20), author varchar(20), price float,
info varchar(40)) default charset=utf8mb4;
index.jsp:
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>图书信息管理</title>
</head>
<body>
<div align="center">
<a href="addbook.jsp">搜索图书</a>  
<a href="deletebook.jsp">删除图书</a>  
<a href="search.jsp">添加图书</a>  
<a href="updatebook.jsp">修改图书</a>  
<a href="index.jsp">主页</a>  
</br>
<%
String driverName = "com.mysql.cj.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "12345678"; //密码
String dbName = "book"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=utf-8";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
String sql="select * from books ";
//PreparedStatement pstmt= conn.prepareStatement(sql);
PreparedStatement pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs=pstmt.executeQuery();
rs.last(); //移至最后一条记录
%>
现库中共有
<font size="5" > <%=rs.getRow()%></font>本图书
<table border="2" width="650">
<tr align="center">
<td>记录条数</td> <td>图书编号</td> <td>图书名</td><td>作者</td> <td>价格</td><td>备注</td>
</tr>
<% rs.beforeFirst(); //移至第一条记录之前
while(rs.next()){
%> <tr align="center">
<td><%= rs.getRow()%></td>
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("author") %></td>
<td><%= rs.getString("price") %></td>
<td><%= rs.getString("info") %></td>
</tr>
<% }%>
</table>
</center>
<%if(rs!=null){ rs.close(); }
if(pstmt!=null){ pstmt.close(); }
if(conn!=null){ conn.close(); }
%>
</div>
</body>
</html>
updatebook.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/9
Time: 2:15 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>修改信息页面</title>
</head>
<body>
<%
String driverName = "com.mysql.cj.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "12345678"; //密码
String dbName = "book"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=utf-8";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
%>
<div align="center">
<a href="addbook.jsp">搜索图书</a>  
<a href="deletebook.jsp">删除图书</a>  
<a href="search.jsp">添加图书</a>  
<a href="updatebook.jsp">修改图书</a>  
<a href="index.jsp">主页</a>  
<form action="updatebook.jsp">
图书id<input type="text" name="id"></br>
<input type="submit" value="提交" >
</form>
<%
String id1=request.getParameter("id");
if(id1!=null)
{
int id=Integer.parseInt(id1);
String sql1="select * from books where id=?";
PreparedStatement stmt=conn.prepareStatement(sql1,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1,id);
ResultSet rs=stmt.executeQuery();
if(!rs.next())
{
out.println("<script type='text/javascript'>alert('id不存在!');</script>");
}
%>
<table border="2" width="650">
<tr align="center">
<td>图书编号</td> <td>图书名</td><td>作者</td> <td>价格</td><td>备注</td>
</tr>
<% rs.beforeFirst(); //移至第一条记录之前
while(rs.next()){
%> <tr align="center">
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("author") %></td>
<td><%= rs.getString("price") %></td>
<td><%= rs.getString("info") %></td>
</tr>
<% }%>
</table>
<form action="updatebook.jsp">
<p>请输入修改后的信息</p>
图书名称*<input type="text" name="name"></br>
图书作者*<input type="text" name="author"></br>
图书价格*<input type="text" name="price"></br>
图书备注*<input type="text" name="info"></br>
<input type="submit" value="确定" >
</form>
<%
String name=request.getParameter("name");
if(name!=null)
{
String author=request.getParameter("author");
String price0=request.getParameter("price");
float price=0;
if(price0!=null)
{
price=Float.parseFloat(price0);
}
String info=request.getParameter("info");
String sql = "update books set name=?,author=?,price=?,info=? where id=?";
PreparedStatement pstmt=conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
pstmt.setString(1,name);
pstmt.setString(2,author);
pstmt.setFloat(3,price);
pstmt.setString(4,info);
pstmt.setInt(5,id);
pstmt.executeUpdate();
out.println("<script type='text/javascript'>alert('修改成功!');</script>");
}
}
%>
</div>
</body>
</html>
search.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/9
Time: 2:14 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>添加图书页面</title>
</head>
<body>
<%
String driverName = "com.mysql.cj.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "12345678"; //密码
String dbName = "book"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=utf-8";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
%>
<div align="center">
<a href="addbook.jsp">搜索图书</a>  
<a href="deletebook.jsp">删除图书</a>  
<a href="search.jsp">添加图书</a>  
<a href="updatebook.jsp">修改图书</a>  
<a href="index.jsp">主页</a>  
<form action="search.jsp">
图书id*<input type="text" name="id" ></br>
图书名称*<input type="text" name="name"></br>
图书作者*<input type="text" name="author"></br>
图书价格*<input type="text" name="price"></br>
图书备注*<input type="text" name="info"></br>
<input type="submit" value="添加" >
</form>
<%
String sql2="Insert into books(id,name,author,price,info) values(?,?,?,?,?)";
PreparedStatement pstmt2 = conn.prepareStatement(sql2);
String id_=request.getParameter("id");
int id=0;
if(id_!=null){
id=Integer.parseInt(id_);
String name=request.getParameter("name");
String author=request.getParameter("author");
String price_=request.getParameter("price");
float price=0;
if(price_!=null){
price=Float.parseFloat(price_);
}
String info=request.getParameter("info");
pstmt2.setInt(1,id);
pstmt2.setString(2,name);
pstmt2.setString(3,author);
pstmt2.setFloat(4,price);
pstmt2.setString(5,info);
try {
int n = pstmt2.executeUpdate();
if (n == 1) {
out.write("数据插入操作成功!<br>");
} else {
out.write("数据插入操作失败!<br>");
}
}
catch(Exception e){
out.write("操作过程出现异常!<br>" + e.getMessage());
}
}
%>
</div>
</body>
</html>
deletebook.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/9
Time: 2:14 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>删除图书页面</title>
</head>
<body>
<%
String driverName = "com.mysql.cj.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "12345678"; //密码
String dbName = "book"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=utf-8";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
%>
<div align="center">
<a href="addbook.jsp">搜索图书</a>  
<a href="deletebook.jsp">删除图书</a>  
<a href="search.jsp">添加图书</a>  
<a href="updatebook.jsp">修改图书</a>  
<a href="index.jsp">主页</a>  
<form action="deletebook.jsp">
图书id<input type="text" name="info"></br>
<input type="submit" value="删除" >
</form>
<%
String info1=request.getParameter("info");
int id=0;
if(info1!=null)
{ id=Integer.parseInt(info1);}
if(id!=0)
{
String sql="select * from books where id = ?";
PreparedStatement stmt=conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
stmt.setInt(1,id);
ResultSet rs=stmt.executeQuery();
if(rs.next()){
String sql1="delete from books where id = ?";
PreparedStatement stmt1=conn.prepareStatement(sql1,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
stmt1 = conn.prepareStatement(sql1);
//补全SQL语句
stmt1.setInt(1,id);
stmt1.executeUpdate();
out.println("<script type='text/javascript'>alert('删除成功!');</script>");
}
else{
out.println("<script type='text/javascript'>alert('id不存在');</script>");
}
}
%>
</div>
</body>
</html>
addbook.jsp:
<%--
Created by IntelliJ IDEA.
User: ssssssbbbbbb
Date: 2022/10/9
Time: 2:14 PM
To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>查询图书页面</title>
</head>
<body>
<%
String driverName = "com.mysql.cj.jdbc.Driver"; //驱动程序名
String userName = "root"; //数据库用户名
String userPwd = "12345678"; //密码
String dbName = "book"; //数据库名
String url1="jdbc:mysql://localhost:3306/"+dbName;
String url2="?user="+userName+"&password="+userPwd;
String url3="&useUnicode=true&characterEncoding=utf-8";
String url =url1+url2+url3; //形成带数据库读写编码的数据库连接字
Class.forName(driverName);
Connection conn=DriverManager.getConnection(url);
%>
<div align="center">
<a href="addbook.jsp">搜索图书</a>  
<a href="deletebook.jsp">删除图书</a>  
<a href="search.jsp">添加图书</a>  
<a href="updatebook.jsp">修改图书</a>  
<a href="index.jsp">主页</a>  
<form action="addbook.jsp">
图书名称(关键字即可)<input type="text" name="info"></br>
<input type="submit" value="提交" >
</form>
<%
String info1=request.getParameter("info");
String sql="select * from books where name like ?";
PreparedStatement stmt=conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
// stmt.setCharacterStream(1,new InputStreamReader(info1,info1.length()));
stmt.setString(1,"%" + info1 + "%");
ResultSet rs=stmt.executeQuery();%>
<table border="2" width="650">
<tr align="center">
<td>记录条数</td> <td>图书编号</td> <td>图书名</td><td>作者</td> <td>价格</td><td>备注</td>
</tr>
<% rs.beforeFirst(); //移至第一条记录之前
while(rs.next()){
%> <tr align="center">
<td><%= rs.getRow()%></td>
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("author") %></td>
<td><%= rs.getString("price") %></td>
<td><%= rs.getString("info") %></td>
</tr>
<% }%>
</div>
</body>
</html>
界面:
写的优点繁琐