Java Web实现简易的图书管理系统
前言
首先实现的是用户的登录注册,注册成功后自动跳转到图书列表页面,之后实现图书的增删改查功能。(菜鸡学习中,大佬勿喷)
一、运行环境
1.数据库:MySQL:5.7
2.Tomcat Apache 8.5
3.编译器:Eclipse 2020版
二、使用步骤
1.MySQL文件
User.sql:
*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50723
Source Host : localhost:3306
Source Schema : library
Target Server Type : MySQL
Target Server Version : 50723
File Encoding : 65001
Date: 10/06/2021 17:59:30
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'admin', 'admin');
INSERT INTO `user` VALUES (2, 'root', 'root');
INSERT INTO `user` VALUES (5, '123', '4596');
SET FOREIGN_KEY_CHECKS = 1;
booklist.sql:
/*
Navicat MySQL Data Transfer
Source Server : 121.36.6.154_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : library
Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001
Date: 2021-06-22 16:05:51
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for booklist
-- ----------------------------
DROP TABLE IF EXISTS `booklist`;
CREATE TABLE `booklist` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`bookname` varchar(255) NOT NULL,
`author` varchar(255) NOT NULL,
`status` tinyint(255) NOT NULL,
`price` double(10,0) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
2.项目的目录层次结构,导包
3.DBUtil类的创建
package com.qfnu.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtilTmp {
public static void main(String[] args) {
// 0.准备连接数据库的资源
String username = "root";
String password = "root";
// String url = "jdbc:mysql://127.0.0.1:3306/hellojdbc";
String url = "jdbc:mysql://localhost:3306/library";
String driver = "com.mysql.cj.jdbc.Driver";
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
// 1.加载驱动
Class.forName(driver);
// 2.获取连接
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 7.释放资源,关闭连接 - 先申请的后释放
try {
if (rs != null) {
rs.close();
}
if (pst != null) {
pst.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
4.Dao层的方法
userdao.java
package com.qfnu.Dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.qfnu.entity.User;
import com.qfnu.util.DBUtil;
public class UserDao {
public List<User> getAllUsers() throws SQLException {
List<User> users = new ArrayList<User>();
// 获取连接
Connection conn = DBUtil.getConnection();
// 创建 sql 语句
String sql = "select * from user";
// 创建 PreparedStatement 对象
PreparedStatement pst = conn.prepareStatement(sql);
// 执行 sql 语句,保存结果集
ResultSet rs = pst.executeQuery();
/**
* 遍历结果集,将结果集中的每一条记录的每个字段值取出,
* 封装为一个 user 对象,并把该 user 对象加入到 users 集合中。
*/
while (rs.next()) {
// 2.直接调用带参构造器
User user = new User(rs.getInt("id"),
rs.getString("username"),
rs.getString("password")
);
users.add(user);
}
return users