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