文章目录
- 1、简要介绍
- 2、数据库设计
- 3、解决的问题
- 1、图片和音频的上传和存储
- 2、分页功能
- 4、数据返回
也算是进行了半个学期，跟着老师讲的进行
后端使用SpringBoot 前端 vue + layui
jdk 18
项目地址：gitee
1、简要介绍
只有管理端，但是对用户端的判断功能在数据库中已经体现，
实现了mp3音频文件和图片文件的上传，原理是存储在静态资源中，数据库中存的也是路径而已
以下为部分截图，整体风格亦是如此
2、数据库设计
CREATE TABLE `role` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '角色id',
`role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色名称',
`role_status` int NULL DEFAULT NULL COMMENT '角色状态 0 不可用 1 可用',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`dis` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`rule` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
CREATE TABLE `singer` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`sex` tinyint NULL DEFAULT NULL,
`pic` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`birth` datetime NULL DEFAULT NULL,
`location` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`introduction` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 44 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
CREATE TABLE `song` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`singer_id` int UNSIGNED NOT NULL,
`name` varchar(45) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`introduction` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '发行时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`pic` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
`lyric` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
`url` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 124 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
CREATE TABLE `sys_user` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
`nick_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '昵称',
`gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码 密文存储 ',
`phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号码',
`status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '1 可用 0 禁用',
`image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '服务器路径',
`created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建者',
`role_uid` int NULL DEFAULT NULL COMMENT '角色 严格遵循外键约束，但在表设计时候，不使用约束语句',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
3、解决的问题
1、图片和音频的上传和存储
首先肯定是要有一个对于存储文件的配置，然后，在前端发送的时候，注意使用
post + headers: { 'content-type': 'multipart/form-data' } 的格式，
package com.whd.system.controller;
import com.github.pagehelper.util.StringUtil;
import com.whd.system.common.AxiosResult;
import com.whd.system.mapper.SysUserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.Filter;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException;
import java.time.LocalDate;
import java.util.UUID;
@RestController
@RequestMapping("/fileup")
public class FileUpController {
public static final String UPLOAD_PATH = "F:\\javaweb\\study\\spingBootMaven\\src\\main\\resources\\upload\\";
@PostMapping("/image")
public AxiosResult<String> upload(@RequestParam(value = "name") String name, MultipartFile photo, HttpServletRequest request) throws IOException {
if(name==null || name.equals("")){
return AxiosResult.error("参数错误");
}
String extension = StringUtils.getFilenameExtension(photo.getOriginalFilename());
String fileName = UUID.randomUUID().toString().replaceAll("-", "") + "." + extension;
String curDateStr = LocalDate.now().toString();
File file = new File(UPLOAD_PATH+name+"\\"+ curDateStr);
if (!file.exists()) {
file.mkdirs();
}
photo.transferTo(new File(UPLOAD_PATH +name+ "\\" + curDateStr + "/" + fileName));
return AxiosResult.success(name+ "\\" + curDateStr + "/" + fileName);
}
@PostMapping("/video")
public AxiosResult<String> uploadViedo(@RequestParam(value = "name") String name, MultipartFile video, HttpServletRequest request) throws IOException {
name+="\\viedo";
if(!video.getContentType().equals("audio/mpeg")) {
return AxiosResult.error("只允许上传mp3格式的音频文件");
}
String extension = StringUtils.getFilenameExtension(video.getOriginalFilename());
String fileName = UUID.randomUUID().toString().replaceAll("-", "") + "." + extension;
String curDateStr = LocalDate.now().toString();
System.out.println(UPLOAD_PATH+name+"\\"+ curDateStr);
File file = new File(UPLOAD_PATH+name+"\\"+ curDateStr);
if (!file.exists()) {
file.mkdirs();
}
video.transferTo(new File(UPLOAD_PATH +name+ "\\" + curDateStr + "/" + fileName));
return AxiosResult.success(name+ "\\" + curDateStr + "/" + fileName);
}
}
2、分页功能
通过自定义的一个组件（当然不是我写的）,在后端进行判断处理，其实没有想到那么复杂
组件如下：
<script>
Vue.component('zpagenav', {
template: `<nav class="zpagenav">` +
`<ul class="page-ul">` +
`<li v-bind:key="index" v-for="(item,index) in pageList" v-bind:class ="item.class" @click.stop="setPage(item)" v-html="item.html">` +
`</li>` +
`</ul>` +
`<span class="total">共 {{total}} 条</span>` +
`</nav>`,
props: {
prevHtml: String,
nextHtml: String,
page: Number,
total: Number,
pageSize: Number,
maxPage: Number
},
computed: {
pageList: function() {
var _this = this,
pageList = [];
let pageCount = Math.ceil(_this.total / _this.pageSize);
let page = _this.page;
let prevHtml = _this.prevHtml ? _this.prevHtml : '<';
let nextHtml = _this.nextHtml ? _this.nextHtml : '>';
let maxPage = _this.maxPage ? _this.maxPage : 9;
let hasPrev = page > 1;
let hasNext = page < pageCount;
//上一页
pageList.push({
class: hasPrev ? '' : 'disabled',
page: hasPrev ? page - 1 : page,
html: prevHtml
});
//首页
pageList.push({
class: page == 1 ? 'active' : '',
page: 1,
html: 1
});
var p0 = Math.floor(maxPage / 2);
var p1 = 1 + 2 + p0; //首页+省略至少2个页码+中间页面数的一半
var start, end;
if(page >= p1) {
start = page - p0;
//前置省略号
pageList.push({
class: 'dot',
page: page,
html: '...'
});
} else {
start = 2;
}
var p2 = page + p0;
if(p2 < pageCount) {
end = p2;
} else {
end = pageCount - 1;
}
//页码列表
for(let i = start; i <= end; i++) {
pageList.push({
class: page == i ? 'active' : '',
page: i,
html: i
});
}
if(end < pageCount - 1) {
//后置省略号
pageList.push({
class: 'dot',
page: page,
html: '...'
});
}
//尾页
if(pageCount > 1) {
pageList.push({
class: page == pageCount ? 'active' : '',
page: pageCount,
html: pageCount
});
}
//下一页
pageList.push({
class: hasNext ? '' : 'disabled',
page: hasNext ? page + 1 : page,
html: nextHtml
});
return pageList;
}
},
methods: {
setPage: function(item) {
if(item.class == '') {
this.$emit('pagehandler', item.page);
}
}
}
});
</script>
<style>
.zpagenav {
text-align: center;
-webkit-user-select: none;
}
.zpagenav {
font-family: arial;
color: #48576a;
}
.zpagenav ul {
display: inline-block;
margin: 20px 20px;
padding: 0;
}
.zpagenav ul li {
display: inline-block;
margin: 0;
padding: 0 4px;
border: 1px solid #d1dbe5;
border-right: 0;
background: #fff;
font-size: 13px;
min-width: 28px;
height: 28px;
line-height: 28px;
cursor: pointer;
box-sizing: border-box;
text-align: center;
}
.zpagenav ul li:last-child {
border-right: 1px solid #d1dbe5;
}
.zpagenav ul li:hover {
color: #20a0ff;
}
.zpagenav ul li.active {
border-color: #20a0ff;
background-color: #20a0ff;
color: #fff;
cursor: default;
}
.zpagenav ul li.active:hover {
color: #fff;
}
.zpagenav ul li.disabled {
cursor: not-allowed;
color: #e4e4e4;
}
.zpagenav ul li.dot {
cursor: default;
}
</style>
使用的时候，注意引入该文件，和该文件的样式文件,然后，将这一坨复制到分页的地方
<div class="page">
<div class="wrap">
<zpagenav v-bind:page="page" v-bind:page-size="pageSize" v-bind:total="total" v-bind:max-page="maxPage"
v-on:pagehandler="pageHandler">
</zpagenav>
</div>
</div>
注意，在vue 的 data 中加上 page: 1, pageSize: 10, total: 0, maxPage: 5,这三个属性，
然后，就是后端处理返回数据了，正常查询结果，通过 PageHelper PageInfo进行分页处理
处理逻辑如下例子：
@PostMapping("/select/selectAllPage")
public AxiosResult<PageResult<SongVo>> getAllSongByPage(@RequestBody PageVO pageVO){
Integer page = pageVO.getPage();
Integer size = pageVO.getSize();
// 开始分页
PageHelper.startPage(page, size);
List<SongVo> songs=songMapper.getSongList();
songs.forEach(song -> {
song.setPic(PIC_URL + song.getPic());
song.setUrl(PIC_URL + song.getUrl());
});
PageInfo<SongVo> pageInfo = new PageInfo<>(songs);
//将数据封装在自己写的PageResult
PageResult<SongVo> pageResult = new PageResult<>();
pageResult.setPage(pageInfo.getPageNum());
pageResult.setTotalPage(pageInfo.getPages());
pageResult.setList(pageInfo.getList());
pageResult.setTotal(pageInfo.getTotal());
pageResult.setPageSize(pageInfo.getPageSize());
return AxiosResult.success(pageResult);
}
对于AxiosResult也是自己写的一个实体类， 用于返回信息和状态码， 具体代码可查看项目中的代码：
对于PageResult 类：
@Data
public class PageResult<T> {
private int page;
private int pageSize;
private long total;
private List<T> list;
private int totalPage;
}
PageVO 类：
@Data
public class PageVO {
private Integer page=1;//用户查询的第几页
private Integer size=10;//每页展示的数量
}
4、数据返回
通过SpringBoot返回的数据类型，会自动转为json格式，但是上面所提到的AxiosResult也不是必须的，但是这样子看起来更方便，该类可以作为模板重复使用
