制作基于springboot的简易学生管理系统(详细)
基于书本与百度创作,内容简易,请多多指教( ̄▽ ̄)/
设计一个简易学生管理系统
所需环境
Java 1.8(JDK8)
MySQL 8
Gradle 6.3
创建一个springboot项目
可视化速度创建(https://start.spring.io/)
一览无遗、一看就懂的界面,注意左边的选项即可,右边的可以在日后自行在build.gradle文件里添加:
点击“GENERATE”生成并下载到本地:
解压并在项目根目录下执行“gradle build”命令来编译:
现在,在项目根目录的下的“build”文件夹下的“libs”文件夹内执行“java -jar demo-0.0.1-SNAPSHOT.jar”命令:
可见使用端口为8080,打开浏览器输入“localhost:8080”查看,由于使用了Spring Security,所以一开始就有登录界面,用户名固定为“user”、密码初次使用随机生成,在上面的命令行内找,这里就不演示登录了:
到此,一个简单的springboot项目就可以上线了,虽然还没有任何内容。
设计数据库
一览无遗,这里简单设计,不复杂。
CREATE TABLE `newtest`.`authority` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
AVG_ROW_LENGTH = 0
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
KEY_BLOCK_SIZE = 0
MAX_ROWS = 0
MIN_ROWS = 0
ROW_FORMAT = Compact;
CREATE TABLE `newtest`.`course` (
`courseId` int NOT NULL COMMENT '课程ID',
`courseName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程名',
`time` date NOT NULL COMMENT '开课时期',
PRIMARY KEY (`courseId`)
)
ENGINE = InnoDB
AVG_ROW_LENGTH = 0
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci
KEY_BLOCK_SIZE = 0
MAX_ROWS = 0
MIN_ROWS = 0
ROW_FORMAT = Dynamic;
CREATE TABLE `newtest`.`score` (
`userId` int NOT NULL,
`courseId` int NOT NULL,
`score` int NOT NULL,
PRIMARY KEY (`userId`, `courseId`) ,
CONSTRAINT `score-courseId` FOREIGN KEY (`courseId`) REFERENCES `newtest`.`course` (`courseId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `score-userId` FOREIGN KEY (`userId`) REFERENCES `newtest`.`user` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `score-courseId` (`courseId` ASC) USING BTREE
)
ENGINE = InnoDB
AVG_ROW_LENGTH = 0
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
KEY_BLOCK_SIZE = 0
MAX_ROWS = 0
MIN_ROWS = 0
ROW_FORMAT = Dynamic;
CREATE TABLE `newtest`.`user` (
`userId` int 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,
`imgURL` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`userId`)
)
ENGINE = InnoDB
AVG_ROW_LENGTH = 0
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
KEY_BLOCK_SIZE = 0
MAX_ROWS = 0
MIN_ROWS = 0
ROW_FORMAT = Dynamic;
CREATE TABLE `newtest`.`userauthority` (
`id` int NOT NULL AUTO_INCREMENT,
`userId` int NOT NULL,
`authorityId` int NOT NULL,
PRIMARY KEY (`id`) ,
CONSTRAINT `userAuthority-authorityId` FOREIGN KEY (`authorityId`) REFERENCES `newtest`.`authority` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `userAuthority-userId` FOREIGN KEY (`userId`) REFERENCES `newtest`.`user` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `userAuthority-userId` (`userId` ASC) USING BTREE,
INDEX `userAuthority-authorityId` (`authorityId` ASC) USING BTREE
)
ENGINE = InnoDB
AVG_ROW_LENGTH = 0
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci
KEY_BLOCK_SIZE = 0
MAX_ROWS = 0
MIN_ROWS = 0
ROW_FORMAT = Dynamic;
配置Gradle
都是基本需求,无特殊设置,顾名思义。对于该配置可以更进一步,详情很多,自行百度。
build.gradle
plugins {
id 'org.springframework.boot' version '2.2.6.RELEASE'
id 'io.spring.dependency-management' version '1.0.9.RELEASE'
id 'java'
id 'idea'
}
group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'
configurations {
developmentOnly
runtimeClasspath {
extendsFrom developmentOnly
}
compileOnly {
extendsFrom annotationProcessor
}
}
repositories {
mavenLocal()
maven {
url 'http://maven.aliyun.com/nexus/content/repositories/central/' }
mavenCentral()
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-security'
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.2'
compile group: 'org.thymeleaf.extras', name: 'thymeleaf-extras-springsecurity5', version: '3.0.4.RELEASE'
compile group: 'com.alibaba', name: 'fastjson', version: '1.2.68'
compile group: 'org.apache.poi', name: 'poi', version: '4.1.2'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'
developmentOnly 'org.springframework.boot:spring-boot-devtools'
runtimeOnly 'mysql:mysql-connector-java'
annotationProcessor 'org.springframework.boot:spring-boot-configuration-processor'
testImplementation('org.springframework.boot:spring-boot-starter-test') {
exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
}
testImplementation 'org.springframework.security:spring-security-test'
}
test {
useJUnitPlatform()
}
配置SpringBoot
都是基本需求,无特殊设置,顾名思义。对于该配置可以更进一步,详情很多,自行百度。
application.properties
#Thymeleaf编码
spring.thymeleaf.encoding=UTF-8
#热部署静态文件
spring.thymeleaf.cache=false
#使用HTML5标准
spring.thymeleaf.mode=HTML5
#数据库驱动设置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接设置
spring.datasource.url=jdbc:mysql://localhost:3306/newTest?characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
项目结构
创建实体类
- User.java
- Authority.java
- UserAuthorities.java
- ResultMsg.java
- Page.java
- ExcelUtils.java
User
实现UserDetails类使其可以用于SpringSecurity的认证返回对象。
package com.example.demo.entity;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
* 用户实体类
*
* @author Administrator
*/
public class User implements UserDetails {
private long userId;
private String userName;
private String password;
private String imgURL;
private List<Authority> authorities = new ArrayList<Authority>();
private boolean isAccountNonExpired = true;
private boolean isAccountNonLocked = true;
private boolean isCredentialsNonExpired = true;
private boolean isEnabled = true;
public User() {
}
public User(String userName, String password) {
BCryptPasswordEncoder bCryptPasswordEncoder = new BCryptPasswordEncoder();
this.password = bCryptPasswordEncoder.encode(password);
this.userName = userName;
}
@Override
public Collection<? extends GrantedAuthority> getAuthorities() {
return this.authorities;
}
public void setAuthorities(List<Authority> authorities) {
this.authorities = authorities;
}
@Override
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String getUsername() {
return this.userName;
}
@Override
public boolean isAccountNonExpired() {
return this.isAccountNonExpired;
}
public void setAccountNonExpired(boolean accountNonExpired) {
this.isAccountNonExpired = accountNonExpired;
}
@Override
public boolean isAccountNonLocked() {
return this.isAccountNonLocked;
}
public void setAccountNonLocked(boolean accountNonLocked) {
this.isAccountNonLocked = accountNonLocked;
}
@Override
public boolean isCredentialsNonExpired() {
return this.isCredentialsNonExpired;
}
public void setCredentialsNonExpired(boolean credentialsNonExpired) {
this.isCredentialsNonExpired = credentialsNonExpired;
}
@Override
public boolean isEnabled() {
return this.isEnabled;
}
public void setEnabled(boolean enabled) {
this.isEnabled = enabled;
}
public long getUserId() {
return this.userId;
}
public void setUserId(long userId) {
this.userId = userId;
}
public List<Authority> getAuthorities2() {
return this.authorities;
}
public String getUserName() {
return this.userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getImgURL() {
return imgURL;
}
public void setImgURL(String imgURL) {
this.imgURL = imgURL;
}
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", imgURL='" + imgURL + '\'' +
", authorities=" + authorities +
", isAccountNonExpired=" + isAccountNonExpired +
", isAccountNonLocked=" + isAccountNonLocked +
", isCredentialsNonExpired=" + isCredentialsNonExpired +
", isEnabled=" + isEnabled +
'}';
}
}
Authority
权限(角色)类
package com.example.demo.entity;
import org.springframework.security.core.GrantedAuthority;
/**
* @author Administrator
*/
public class Authority implements GrantedAuthority {
private long id;
private String name;
public Authority() {
}
public Authority(long id, String name) {
this.id = id;
this.name = name;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return this.name;
}
@Override
public String getAuthority() {
return this.name;
}
}
UserAuthorities
与用户关联的用户权限类。
package com.example.demo.entity;
/**
* @author Administrator
*/
public class UserAuthorities {
private long id;
private long userId;
private long authorityId;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public long getUserId() {
return userId;
}
public void setUserId(long userId) {
this.userId = userId;
}
public long getAuthorityId() {
return authorityId;
}
public void setAuthorityId(long authorityId) {
this.authorityId = authorityId;
}
}
ResultMsg
统一封装的后端返回消息类,里面可以弄staic字符串,更方便,这里没弄。
package com.example.demo.entity;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import java.io.Serializable;
/**
* code: 0 成功, 1 失败,
*
* @author KING
*/
public class ResultMsg implements Serializable {
private int code;
private String msg;
private JSONObject jsonObject;
private JSON json;
public ResultMsg() {
}
public ResultMsg(int code, String msg) {
this.code = code;
this.msg = msg;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public JSONObject getJsonObject() {
return jsonObject;
}
public void setJsonObject(JSONObject jsonObject) {
this.jsonObject = jsonObject;
}
public JSON getJson() {
return json;
}
public void setJson(JSON json) {
this.json = json;
}
@Override
public String toString() {
return "ResultMsg{" +
"code=" + code +
", msg='" + msg + '\'' +
", jsonObject=" + jsonObject +
", json=" + json +
'}';
}
}
Page
分页类
package com.example.demo.entity;
import java.io.Serializable;
import java.util.List;
/**
* @author KING
*/
public class Page implements Serializable {
private int pageIndex;
private int userNumber;
private int totalPage;
private List<User> userList;
public Page() {
}
public Page(int pageIndex, int userNumber, List<User> userList) {
this.pageIndex = pageIndex;
this.userNumber = userNumber;
this.totalPage = (userNumber - 1) / 10 + 1;
this.userList = userList;
}
public int getPageIndex() {
return pageIndex;
}
public void setPageIndex(int pageIndex) {
this.pageIndex = pageIndex;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List<User> getUserList() {
return userList;
}
public void setUserList(List<User> userList) {
this.userList = userList;
}
public int getUserNumber() {
return userNumber;
}
public void setUserNumber(int userNumber) {
this.userNumber = userNumber;
}
@Override
public String toString() {
return "Page{" +
"pageIndex=" + pageIndex +
", userNumber=" + userNumber +
", totalPage=" + totalPage +
", userList=" + userList +
'}';
}
}
ExcelUtils
Excel类,用于解析Excel文件,获取文件内容,需有表头格式,否则无法拿到信息,这里表头为 score、courseId、userId。
package com.example.demo.entity;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author Administrator
*/
public class ExcelUtils {
/**
* 总行数
*/
private int totalRows = 0;
/**
* 总条数
*/
private int totalCells = 0;
/**
* 错误信息接收器
*/
private String errorMsg;
/**
* 最多行数
*/
private int maxRows;
public ExcelUtils() {
}
/**
* 是否是2003的excel,返回true是2003
*
* @param filePath
* @return
*/
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* 是否是2007的excel,返回true是2007
*
* @param filePath
* @return
*/
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
public int getTotalRows() {
return totalRows;
}
public int getTotalCells() {
return totalCells;
}
public String getErrorInfo() {
return errorMsg;
}
public int getMaxRows() {
return maxRows;
}
public void setMaxRows(int maxRows) {
this.maxRows = maxRows;
}
/**
* 验证EXCEL文件
*
* @param filePath
* @return
*/
public boolean validateExcel(String filePath) {
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
errorMsg = "文件名不是excel格式";
return false;
}
return true;
}
/**
* 读EXCEL文件,获取信息集合
*
* @param
* @return
*/
public List<Map<String, Object>> getExcelInfo(String fileName, MultipartFile multipartFile) {
// 初始化信息的集合
List<Map<String, Object>> mapArrayList = new ArrayList<>();
// 初始化输入流
InputStream inputStream = null;
try {
// 验证文件名是否合格
if (!validateExcel(fileName)) {
return null;
}
// 根据文件名判断文件是2003版本还是2007版本
boolean isExcel2003 = true;
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
// 根据新建的文件实例化输入流
inputStream = multipartFile.getInputStream();
// 根据excel里面的内容读取信息
mapArrayList = getExcelInfo(inputStream, isExcel2003);
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
inputStream = null;
e.printStackTrace();
}
}
}
return mapArrayList;
}
/**
* 根据excel里面的内容读取信息
*
* @param inputStream 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
public List<Map<String, Object>> getExcelInfo(InputStream inputStream, boolean isExcel2003) {
List<Map<String, Object>> mapList = null;
try {
/**
* 根据版本选择创建Workbook的方式
*
*/
Workbook workbook = null;
// 当excel是2003时
if (isExcel2003) {
workbook = new HSSFWorkbook(inputStream);
} else {
// 当excel是2007时
workbook = new XSSFWorkbook(inputStream);
}
// 读取Excel里面的信息
mapList = readExcelValue(workbook);
} catch (IOException e) {
e.printStackTrace();
}
return mapList;
}
/**
* 读取Excel里面的信息
*
* @param workbook
* @return
*/
public List<Map<String, Object>> readExcelValue(Workbook workbook) {
List<Map<String, Object>> mapArrayList = new ArrayList<>();
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
// 得到一个shell
Sheet sheet = workbook.getSheetAt(i);
// 得到Excel的行数
this.totalRows = sheet.getPhysicalNumberOfRows();
// 得到Excel的列数(前提是有行数)
if (totalRows >= 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
Map<String, Object> mapTemp;
// 循环Excel行数,从第二行开始。标题不入库
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
mapTemp = new HashMap<>();
try {
Thread.sleep(1);
} catch (InterruptedException e) {
e.printStackTrace();
}
// 循环Excel的列
for (int c = 0; c < this.totalCells; c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (c == 0) {
mapTemp.put("userId", cell.getNumericCellValue());
} else if (c == 1) {
mapTemp.put("courseId", cell.getNumericCellValue());
} else if (c == 2) {
mapTemp.put("score", cell.getNumericCellValue());
}
}
}
// 添加记录
mapArrayList.add(mapTemp);
}
}
return mapArrayList;
}
}
前端页面
- 前端页面使用thymeleaf模板引擎。
- 使用bootstrap,也有可视化布局工具。
- 使用Font Awesome图标。
页面包括
- login.html
- index.html
- register.html
- userList.html
- modifyPassword.html
- modifyPicture.html
- modifyUserRole.html
- uploadExcel.html
- userList.html
- showScore.html
模板包括
- footer.html
- head.html
- nav.html
- pageList.html
- resultMsg.html
模板页面,用于引用,减少重复代码
关于thymeleaf的简单使用,例子:
- Spring官方推荐,与Spring有很好的契合度。
xmlns:th="http://www.thymeleaf.org"
命名空间引入先。th:href="@{/{role}/login/{userName}(userName=${session.user},role=${session.role})}"
URLth:id="container"
标签属性,几乎都有thymeleaf的替代标签属性,加前缀th:
或data-th-
。th:if="${user.imgURL != null}"
就是个if函数,注意,直接在${}
内进行判断,其中thymeleaf方式的引用无需再加${}
。th:class="${page.pageIndex == 2}? 'active':''"
同上,一个if、else判断,多举个例子,可以用在单数行或复数行的显示上,odd?..th:onclick="'javascript:doPage(\'/pageIndex/'+${page.totalPage}+'\',\'get\',null,\'userList\',null,null)'"
引用JS函数,把双引号中的整条表达式都当做字符串打,其中需用转义符\'
,除了引用时无需如此+${page.totalPage}+
。th:text="|共 ${page.totalPage} 页,共 ${page.userNumber} 人|"
也可以使用这种写法,用||包裹着一切,文本、变量等,但出错似乎更难查一点?也许是我的错觉。th:replace="~{fragments/head :: head}"
引用fragments文件夹内的head文件内的名为head的模板。th:fragment="resultMsg"
注册为模板,名为resultMsg。th:each="user : ${page.userList}"
就是一个循环,变量user被userList中的user一个个赋值,在下文中使用th:text="${user.authorities}"
,可以有其它参数使用,其它参数一般都是用来获取${page.userList}的状态
。<script th:inline="javascript">
如此,可在本script块中使用thymeleaf语法,对于外部JS文件,可以先在页面中引入外部JS,然后在这外部JS的引入标签中使用th:inline="javascript"
,此时外部JS应当能够使用thymeleaf语法(估计,未测试)。th:text="${#session.id}"
也可以直接拿到一些数据,如session、csrf等等。- 详情很多,自行百度。
关于thymeleaf-extras-springsecurity5的简单使用,例子:
- SpringSecurity与thymeleaf的配合使用,可以直接拿到SpringSecurity中的认证信息。后端对数据进行修改操作后需重新更正认证信息,不然前端显示的是老信息,甚至会引发错误。
xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity5"
命名空间引入先。sec:authorize="isAuthenticated()"
一个判断函数,是否已认证。sec:authentication="details.sessionId"
与后端交互,可以拿到SpringSecurity中的认证信息。sec:authentication="principal.password"
同上。sec:authorize="hasRole('ROLE_ADMIN')"
顾名思义,只有管理员才能看到的世界。- 详情很多,自行百度。
footer.html
引脚模板。
<html lang="zh"
xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
<footer th:class="text-center" th:fragment="footer">
<span>版权公告 © 1999-2020 XXX有限公司及/或其关联公司及特许人。版权所有。</span>
<script th:src="@{/js/jQuery.js}"></script>
<script th:src="@{/js/bootstrap.min.js}"></script>
</footer>
</html>
head.html
头部模板。
<html lang="zh"
xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">
<head th:fragment="head">
<title th:id="title" th:text="${title}">title