1、在navicat中创建表单,有图书编号book_id,图书名称book_name,出版社book_press,价格book_price,出版日期publication_time,副本量quantity_of_copies,库存量inventory
2、创建index.html文件,编辑图书管理系统首页。
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>图书管理系统</title>
</head>
<body align="center">
<h1>图书管理系统</h1>
<form action="insert.jsp" method="post">
<table align="center">
<tr>
<td>图书编号:</td>
<td><input type="text" name="book_id"/></td>
</tr>
<tr>
<td>图书名称:</td>
<td><input type="text" name="book_name"/></td>
</tr>
<tr>
<td>出版社:</td>
<td><input type="text" name="book_press"/></td>
</tr>
<tr>
<td>价格:</td>
<td><input type="text" name="book_price"/></td>
</tr>
<tr>
<td>出版时间:</td>
<td><input type="date" name="publication_time"/></td>
</tr>
<tr>
<td>复本量:</td>
<td><input type="text" name="quantity_of_copies"/></td>
</tr>
<tr>
<td>库存量:</td>
<td><input type="text" name="inventory"/></td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="录入"/>
<input type="reset" value="重置"/>
</td>
</tr>
</table>
</form>
</body>
</html>
3、创建insert.jsp文件:主要用于与MySQL数据库进行交互,展示查询到的图书馆书籍信息,并提供了返回插入操作的链接。
3.1、指定页面使用的字符集为UTF-8,并导入了java.sql包和java.text.SimpleDateFormat类,后者用于日期格式化。
<%-- 这是一个注释部分,通常用于说明页面的创建信息,不会在浏览器中显示 --%>
<%@ page import="java.sql.*" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page contentType="text/html;charset=UTF-8" %>
3.2、定义数据库连接的基本信息,包括驱动类名、数据库URL、用户名和密码。
<%
Connection connection=null;
PreparedStatement preparedStatement=null;
int flag = 0;
try {
Class.forName(DBDriver);
connection = DriverManager.getConnection(DBUrl, DBUser, DBPassword);
String sql_demo0 = "INSERT INTO library (book_id, book_name, book_press, book_price, publication_time, quantity_of_copies, inventory) VALUES (?, ?, ?, ?, ?, ?, ?)";
//插入
preparedStatement = connection.prepareStatement(sql_demo0);
//设置编码
request.setCharacterEncoding("utf-8");
preparedStatement.setString(1,request.getParameter("book_id"));
preparedStatement.setString(2,request.getParameter("book_name"));
preparedStatement.setString(3,request.getParameter("book_press"));
preparedStatement.setFloat(4,Float.parseFloat(request.getParameter("book_price")));
//日期转换
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date=null;
date = simpleDateFormat.parse(request.getParameter("publication_time"));
java.sql.Date date1 = new java.sql.Date(date.getTime());
preparedStatement.setDate(5,date1);
preparedStatement.setString(6,request.getParameter("quantity_of_copies"));
preparedStatement.setString(7,request.getParameter("inventory"));
flag = preparedStatement.executeUpdate();
// 释放内存
preparedStatement.close();
connection.close();
} catch (Exception e) {
System.out.println("插入错误");
}
%>
3.3、尝试建立数据库连接,并执行插入语句。它从请求参数中获取图书信息,并将其插入到数据库中。
<jsp:forward page="select.jsp">
<jsp:param name="flag" value="<%=flag%>"/>
</jsp:forward>
3.4、完整代码:
<%--
Created by IntelliJ IDEA.
User: Administrator
Date: 2024/4/21
Time: 15:48
To change this template use File | Settings | File Templates.
--%>
<%@ page import="java.sql.*" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page contentType="text/html;charset=UTF-8" %>
<html>
<head>
<title>插入图书</title>
</head>
<body>
<%!
// 1.Driver 2.url 3.user 4.password
String DBDriver = "com.mysql.cj.jdbc.Driver";
String DBUrl = "jdbc:mysql://localhost:3306/mldn?serverTimezone=GMT%2B8";
String DBUser = "root";
String DBPassword = "123456";
%>
<%
Connection connection=null;
PreparedStatement preparedStatement=null;
int flag = 0;
try {
Class.forName(DBDriver);
connection = DriverManager.getConnection(DBUrl, DBUser, DBPassword);
String sql_demo0 = "INSERT INTO library (book_id, book_name, book_press, book_price, publication_time, quantity_of_copies, inventory) VALUES (?, ?, ?, ?, ?, ?, ?)";
//插入
preparedStatement = connection.prepareStatement(sql_demo0);
//设置编码
request.setCharacterEncoding("utf-8");
preparedStatement.setString(1,request.getParameter("book_id"));
preparedStatement.setString(2,request.getParameter("book_name"));
preparedStatement.setString(3,request.getParameter("book_press"));
preparedStatement.setFloat(4,Float.parseFloat(request.getParameter("book_price")));
//日期转换
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date=null;
date = simpleDateFormat.parse(request.getParameter("publication_time"));
java.sql.Date date1 = new java.sql.Date(date.getTime());
preparedStatement.setDate(5,date1);
preparedStatement.setString(6,request.getParameter("quantity_of_copies"));
preparedStatement.setString(7,request.getParameter("inventory"));
flag = preparedStatement.executeUpdate();
// 释放内存
preparedStatement.close();
connection.close();
} catch (Exception e) {
System.out.println("插入错误");
}
%>
<jsp:forward page="select.jsp">
<jsp:param name="flag" value="<%=flag%>"/>
</jsp:forward>
</body>
</html>
4、创建select.jsp文件
4.1、指定页面使用的字符集为UTF-8,并且导入了java.sql包,其中包含了数据库操作相关的类。
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.sql.*" %>
4.2、定义数据库连接的基本信息,包括驱动类名、数据库URL、用户名和密码。
<%!
String DBDriver = "com.mysql.cj.jdbc.Driver";
String DBUrl = "jdbc:mysql://localhost:3306/mldn?serverTimezone=GMT%2B8";
String DBUser = "root";
String DBPassword = "123456";
%>
4.3、尝试建立数据库连接,并执行查询语句。
<%
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
Class.forName(DBDriver);
connection = DriverManager.getConnection(DBUrl, DBUser, DBPassword);
String sql_demo = "select * from library";
//查询
preparedStatement = connection.prepareStatement(sql_demo);
resultSet = preparedStatement.executeQuery();
%>
4.4、从数据库中获取每一条记录,并将其显示在表格中。
<table>
<tr>
<td>book_id</td>
<td>book_name</td>
<td>book_press</td>
<td>book_price</td>
<td>publication_time</td>
<td>quantity_of_copies</td>
<td>inventory</td>
</tr>
<%
while (resultSet.next()){
String book_id = resultSet.getString("book_id");
String book_name = resultSet.getString("book_name");
String book_press = resultSet.getString("book_press");
float book_price = resultSet.getFloat("book_price");
Date publication_time = resultSet.getDate("publication_time");
int quantity_of_copies = resultSet.getInt("quantity_of_copies");
int inventory = resultSet.getInt("inventory");
%>
<tr>
<td><%=book_id%></td>
<td><%=book_name%></td>
<td><%=book_press%></td>
<td><%=book_price%></td>
<td><%=publication_time%></td>
<td><%=quantity_of_copies%></td>
<td><%=inventory%></td>
</tr>
<%
}
// 释放内存
resultSet.close();
preparedStatement.close();
connection.close();
} catch (Exception e) {
System.out.println("查询错误");
}
%>
</table>
4.5、完整代码:
<%--
Created by IntelliJ IDEA.
User: Administrator
Date: 2024/4/21
Time: 16:12
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.sql.*" %>
<html>
<head>
<title>查询页面</title>
</head>
<body>
<%!
String DBDriver = "com.mysql.cj.jdbc.Driver";
String DBUrl = "jdbc:mysql://localhost:3306/mldn?serverTimezone=GMT%2B8";
String DBUser = "root";
String DBPassword = "123456";
%>
<%
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
Class.forName(DBDriver);
connection = DriverManager.getConnection(DBUrl, DBUser, DBPassword);
String sql_demo = "select * from library";
//查询
preparedStatement = connection.prepareStatement(sql_demo);
resultSet = preparedStatement.executeQuery();
%>
<table>
<tr>
<td>book_id</td>
<td>book_name</td>
<td>book_press</td>
<td>book_price</td>
<td>publication_time</td>
<td>quantity_of_copies</td>
<td>inventory</td>
</tr>
<%
while (resultSet.next()){
String book_id = resultSet.getString("book_id");
String book_name = resultSet.getString("book_name");
String book_press = resultSet.getString("book_press");
float book_price = resultSet.getFloat("book_price");
Date publication_time = resultSet.getDate("publication_time");
int quantity_of_copies = resultSet.getInt("quantity_of_copies");
int inventory = resultSet.getInt("inventory");
%>
<tr>
<td><%=book_id%></td>
<td><%=book_name%></td>
<td><%=book_press%></td>
<td><%=book_price%></td>
<td><%=publication_time%></td>
<td><%=quantity_of_copies%></td>
<td><%=inventory%></td>
</tr>
<%
}
// 释放内存
resultSet.close();
preparedStatement.close();
connection.close();
} catch (Exception e) {
System.out.println("查询错误");
}
%>
</table>
<% if(Integer.parseInt(request.getParameter("flag"))==0){out.print("<p>插入失败</p>");}
else {out.print("<p>插入成功</p>");}
%>
<a href="index.html">返回插入</a>
</body>
</html>