简单数据库管理系统
1) index.jsp文件代码
<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page errorPage="error.jsp"%>
<html>
<head>
<title>图书管理系统</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<h1>图书管理系统</h1>
<a href="add.jsp">添加图书信息</a>
<br />
<br />
<table style="width: 50%;">
<tr>
<th>书名</th>
<th>作者</th>
<th>价格</th>
<th>管理</th>
</tr>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/book?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true", "root", "a123456");
//使用Statement对象
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from bookinfo");
/*
PreparedStatement stmt = con.prepareStatement("select * from bookinfo");
ResultSet rs = stmt.executeQuery();
*/
while (rs.next()) {
int id = rs.getInt(1);
%>
<tr>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><a href="edit.jsp?id=<%=id%>">修改</a> <a href="del.jsp?id=<%=id%>">删除</a></td>
</tr>
<%
}
rs.close();
stmt.close();
con.close();
%>
</table>
<br />
<hr />
<div style="text-align: center; width: 100%; font-size: 12px; color: #333;">
©版权所有：石家庄铁道大学信息科学与技术学院 <a href="Lab03.png" target="_blank">网站地图</a>
</div>
</body>
</html>
2) add.jsp文件代码
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*" errorPage="error.jsp"%>
<html>
<head>
<title>添加图书信息</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<form action="addsave.jsp" method="post">
<h2>添加图书信息</h2>
<table style="width: 50%">
<tr>
<th width="30%">书名：</th>
<td width="70%"><input name="bookname" type="text"></td>
</tr>
<tr>
<th>作者：</th>
<td><input name="author" type="text"></td>
</tr>
<tr>
<th>价格：</th>
<td><input name="price" type="text">元</td>
</tr>
<tr>
<td colspan="2">
<input type="submit" name="submit" value="添加">
<input type="reset" value="重置">
</td>
</tr>
</table>
</form>
</body>
</html>
3) addsave.jsp文件代码
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*" errorPage="error.jsp"%>
<html>
<head>
<title>添加图书信息</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String bookname = request.getParameter("bookname");
String author = request.getParameter("author");
String price = request.getParameter("price");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true", "root", "stdusist");
//使用Statement对象
// Statement stmt = con.createStatement();
// String sql = "insert into bookinfo(bookname,author,price) values('" + bookname + "','" + author + "'," + price + ")";
// System.out.println(sql);
// int i = stmt.executeUpdate(sql);
PreparedStatement stmt = con.prepareStatement("insert into bookinfo(bookname,author,price) values(?, ?, ?)");
stmt.setString(1, bookname);
stmt.setString(2, author);
stmt.setString(3, price);
int i = stmt.executeUpdate();
if (i == 1) {
out.println("<h2>添加成功！</h2><br/>");
out.println("<a href='index.jsp'>返回首页</a>");
} else {
out.println("<h2>添加失败！</h2><br/>");
out.println("<a href='add.jsp'>重新添加</a>");
}
stmt.close();
con.close();
%>
</body>
</html>
4) edit.jsp文件代码
<%@ page import="java.sql.*" pageEncoding="utf-8" errorPage="error.jsp"%>
<html>
<head>
<title>修改图书信息</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String id = request.getParameter("id");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/book?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true", "root", "stdusist");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from bookinfo where id=" + id);
// PreparedStatement stmt = con.prepareStatement("select * from bookinfo where id=?");
// stmt.setString(1, id);
// ResultSet rs = stmt.executeQuery();
rs.next();
%>
<form action="editsave.jsp" method="post">
<h2>修改图书信息</h2>
<table style="width:50%">
<tr>
<th width="30%">书名：</th>
<td width="70%"><input name="bookname" type="text"
value="<%=rs.getString(2)%>"></td>
</tr>
<tr>
<th>作者：</th>
<td><input name="author" type="text"
value="<%=rs.getString(3)%>"></td>
</tr>
<tr>
<th>价格：</th>
<td><input name="price" type="text"
value="<%=rs.getString(4)%>">元</td>
</tr>
<tr>
<td colspan="2"><input type="hidden" name="id" value="<%=id%>">
<input type="submit" value="修改"> <input type="reset"
value="重置"></td>
</tr>
</table>
</form>
<%
rs.close();
stmt.close();
con.close();
%>
</body>
</html>
5) editsave.jsp文件代码
<%@ page import="java.sql.*" pageEncoding="utf-8" errorPage="error.jsp"%>
<html>
<head>
<title>修改完成</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String bookname = request.getParameter("bookname");
String author = request.getParameter("author");
String price = request.getParameter("price");
String id = request.getParameter("id");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/book?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true", "root", "stdusist");
Statement stmt = con.createStatement();
String sql = "update bookinfo set bookname='" + bookname + "',author='" + author + "',price=" + price
+ " where id=" + id;
System.out.println(sql);
int i = stmt.executeUpdate(sql);
if (i == 1) {
out.println("<h2>修改成功！</h2><br/>");
out.println("<a href='index.jsp'>返回首页</a>");
} else {
out.println("<h2>修改失败！</h2><br/>");
out.println("<a href='edit.jsp?id='" + id + ">重新添加</a>");
}
stmt.close();
con.close();
%>
</body>
</html>
6) del.jsp文件代码
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
<head>
<title>删除图书信息</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/book?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true", "root", "stdusist");
Statement stmt=con.createStatement();
String id=request.getParameter("id");
int i=stmt.executeUpdate("delete from bookinfo where id="+id);
if(i==1)
{
out.println("<h2>删除成功！</h2><br/>");
}
else
{
out.println("<h2>删除失败！</h2><br/>");
}
out.println("<a href='index.jsp'>返回首页</a>");
stmt.close();
con.close();
%>
</body>
</html>
7) error.jsp文件代码
<%@ page language="java" isErrorPage="true" pageEncoding="utf-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>error page</title>
</head>
<body>
<h1>程序出现错误</h1>
<div>错误信息为：<br/>
<%=exception.getMessage()%><br/>
<%=exception.toString()%>
</div>
</body>
</html>
