=====================================================================
本次课程设计基于教材《Java基础案例教程(第二版)》中的第十三章,对课程源码的整理和分享。整个课程设计,我整理了mysql数据库的两个版本:5.7和8.0,方便各位码友们的学习!
开发环境:
-
jdk8
-
mysql数据库
-
intelliJ IDEA
=========================================================================
图书管理系统分为用户界面和管理员界面,结构下图所示:
=========================================================================
用户界面:
管理界面:
===========================================================================
E—R图设计:
1、用户实体的设计
2、图书实体的设计
3、图书类别实体
4、图书借阅详情实体
===========================================================================
用户实体表的设计:
#创建数据库
CREATE DATABASE bookmanager;
USE bookmanager;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
#创建用户表user
DROP TABLE IF EXISTS user
;
CREATE TABLE user
(
id
INT(11) NOT NULL AUTO_INCREMENT, #用户主键id
username
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #用户名
password
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #用户密码
role
INT(255) NULL DEFAULT NULL COMMENT ‘角色 1学生 2管理员’, #用户分类
sex
VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #用户性别
phone
CHAR(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #用户电话
PRIMARY KEY (id
) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
#插入数据
INSERT INTO user
VALUES (1, ‘xkj’, ‘xkj123’, 1, ‘男’, ‘13195648799’);
INSERT INTO user
VALUES (2, ‘admin’, ‘111111’, 2, ‘男’, ‘13198645975’);
INSERT INTO user
VALUES (3, ‘徐某人’, ‘xkj123’, 1, ‘女’, ‘13195648529’);
INSERT INTO user
VALUES (4, ‘肖淼’, ‘sdf78978’, 1, ‘女’, ‘13195698458’);
SET FOREIGN_KEY_CHECKS = 1;
书籍表的设计:
#创建书籍表book
DROP TABLE IF EXISTS book
;
CREATE TABLE book
(
id
INT(11) NOT NULL AUTO_INCREMENT, #图书表主键
book_name
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #图书名称
type_id
INT(11) NULL DEFAULT NULL, #图书类别
author
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #作者
publish
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #出版社
price
DOUBLE(10, 2) NULL DEFAULT NULL, #图书价格
number
INT(11) NULL DEFAULT NULL, #图书数量
status
INT(11) NULL DEFAULT 1 COMMENT ‘状态 1上架0下架’, #借阅状态
remark
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #图书描述
PRIMARY KEY (id
) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
#插入数据
INSERT INTO book
VALUES (1,‘Java核心技术’,1,‘霍斯特曼’,‘人民邮电出版社’,69.00,16,1,‘Java基础教学’);
INSERT INTO book
VALUES (2,‘Tomcat与Java web’,1,‘孙卫琴’,‘人民邮电出版社’,119.00,16,1,‘javaweb教学’);
INSERT INTO book
VALUES (3,‘MySQL基础教程’,1,‘西泽梦路’,‘人民邮电出版社’,129.00,16,1,‘MySQL基础教学’);
INSERT INTO book
VALUES (4, ‘西游记’, 3, ‘吴承恩’, ‘机械工业出版社’, 23.00, 213, 1, ‘四大名著之一’);
INSERT INTO book
VALUES (6, ‘SpringCloud微服务架构开发’, 1, ‘黑马程序员’, ‘人民邮电出版社’, 28.00, 20, 1, ‘微服务实战开发’);
INSERT INTO book
VALUES (7, ‘水浒传’, 3, '施耐庵 ', ‘人民文学出版社’, 29.00, 30, 1, ‘四大名著之一’);
图书类别表的设计:
#创建图书类别表
DROP TABLE IF EXISTS book_type
;
CREATE TABLE book_type
(
id
INT(11) NOT NULL AUTO_INCREMENT, #图书类别主键
type_name
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #类别名称
remark
VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, #类别描述
PRIMARY KEY (id
) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
#插入数据
INSERT INTO book_type
VALUES (1, ‘技术’, ‘技术类’);
最后
KEY (id
) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
#插入数据
INSERT INTO book_type
VALUES (1, ‘技术’, ‘技术类’);
最后
[外链图片转存中…(img-rWDT7Y5A-1718870028869)]