尚硅谷谷粒学院项目第六天内容之对象存储oss服务、使用EasyExcel添加课程分类功能、课程列表分类、ningx的使用
对象存储oss
打开阿里云官网——对象存储——oss管理控制台
创建bucket：选择默认地域、低频访问、公共读
创建oss许可证
对象存储oss——概览——常用入口——Access key
点击继续使用Accesskey
创建AccessKey——复制——确定
在service下创建SpringBoot子模块service_oss
引入依赖
<!-- 阿里云oss依赖 --><dependency><groupId>com.aliyun.oss</groupId><artifactId>aliyun-sdk-oss</artifactId></dependency><!--日期工具栏依赖--><dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId></dependency>
配置application.properties
注意：地域节点、key、密钥要换成你自己的
#服务端口server.port=8002#服务名spring.application.name=service-oss#环境设置：dev、test、prodspring.profiles.active=dev#阿里云 OSSaliyun.oss.file.endpoint=oss-cn- aliyun.oss.file.keyid=LTAI5t6R5cWusp7MeWpKgT1W aliyun.oss.file.keysecret=Dd6Ku0j5dQja9jh4d7cl8ULepWcNE7 #bucketaliyun.oss.file.bucketname=bucket--a
创建启动类，启动模块
报错
在启动类上添加：
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
创建一个常量类，用来读取配置文件中的属性值
创建utils.ConstantRead类
@Componentpublic class ConstantRead implements InitializingBean {@Value("${aliyun.oss.file.endpoint}")private String endpoint;@Value("${aliyun.oss.file.keyid}")private String keyId;@Value("${aliyun.oss.file.keysecret}")private String keySecret;@Value("${aliyun.oss.file.bucketname}")private String bucketName;public static String ENDPOINT;public static String KEY_ID;public static String KEY_SECRET;public static String BUCKET_NAME;@Overridepublic void afterPropertiesSet() throws Exception {ENDPOINT=endpoint;KEY_ID=keyId;KEY_SECRET=keySecret;BUCKET_NAME=bucketName;}}
创建OssController
@RestController@RequestMapping("/ossservice")public class OssController {@Autowiredprivate OssService ossService;//上传头像@PostMapping("uploadAvatar")public R uploadFile(MultipartFile file){String url = ossService.uploadAvatar(file);return R.ok().data("url",url);}}
创建OssService
@Servicepublic interface OssService {//上传头像String uploadAvatar(MultipartFile file);}
创建OssServiceImpl
@Componentpublic class OssServiceImpl implements OssService {@Overridepublic String uploadAvatar(MultipartFile file) {String endpoint = ConstantRead.ENDPOINT;String accessKeyId = ConstantRead.KEY_ID;String accessKeySecret = ConstantRead.KEY_SECRET;String bucketName = ConstantRead.BUCKET_NAME;String filename = file.getOriginalFilename();OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);try {InputStream inputStream = file.getInputStream();// 创建PutObject请求。ossClient.putObject(bucketName, filename, inputStream);} catch (Exception e) {e.printStackTrace();} finally {if (ossClient != null) {ossClient.shutdown();}}return "http://"+bucketName+"."+endpoint+"/"+filename;}}
swagger测试
如果多个用户上传的文件名字相同，后上传的文件会把先上传的文件覆盖掉
我们在文件名前加上uuid值，让每个文件名都不同
在OssServiceImpl里添加
String filename = file.getOriginalFilename();String uuid = UUID.randomUUID().toString().replaceAll("-", "");filename=uuid+filename;
此时上传的文件都是在bucket的根目录下
把文件按照日期进行分类
在OssServiceImpl里添加
String date = new DateTime().toString("yyyy/MM/dd");filename=date+"/"+uuid+filename;
测试
nginx：反向代理服务器
功能：请求转发、负载均衡、动静分离
下载windows版的nginx，解压
在cmd中通过nginx.exe命令启动
关闭cmd窗口，nginx不会关闭
关闭nginx：nginx.exe -s stop
配置nginx实现请求转发功能
编辑nginx安装目录/conf/nginx.conf
将默认端口从80改为81，不改会端口冲突
添加端口映射规则
server {listen 9001;server_name localhost;location ~ /eduservice/ {proxy_pass http://localhost:8001;}location ~ /ossservice/ {proxy_pass http://localhost:8002;}}
将前端模板中config/dev.env.js中的BASE_API端口号改为9001
重启ningx、重启前后端，测试
讲师上传头像功能
将组件 ImageCropper和PanThumb 复制到项目中的 src/components 目录下
在save.vue中添加上传头像的组件
<!-- 讲师头像 --><el-form-item label="讲师头像"><!-- 头衔缩略图 --><pan-thumb :image="teacher.avatar"/><!-- 文件上传按钮 --><el-button type="primary" icon="el-icon-upload" @click="imagecropperShow=true">更换头像</el-button><!--v-show：这个组件是否显示:key：类似于id，如果一个页面多个图片上传控件，可以做区分@crop-upload-success：上传成功后的回调 field：这个组件的name@close：关闭组件时会调用close()方法--><image-cropperv-show="imagecropperShow" :width="300":height="300":key="imagecropperKey":url="BASE_API+'/eduoss/fileoss'"field="file"@close="close"@crop-upload-success="cropSuccess"/></el-form-item>
引入组件
import ImageCropper from '@/components/ImageCropper'import PanThumb from '@/components/PanThumb'
声明组件
components: { ImageCropper, PanThumb },
定义变量
imagecropperShow:false,imagecropperKey:0,BASE_API:process.env.BASE_API,
修改上传url
写两个方法
close(){this.imagecropperShow=false},cropSuccess(data){ //data是后端返回的数据，相当于response.data，这里做了封装this.imagecropperShow=falsethis.teacher.avatar=data.url},
上传了头像后，再次点击更换头像，出现上传成功字样，而不是“将图片拖拽至此处”
解决：
改变imagecropperKey的值相当于让上传组件初始化
课程分类管理模块
课程分类使用二级分类：通过parentId实现
创建edu_subject表
CREATE TABLE `edu_subject` (`id` char(19) NOT NULL COMMENT '课程类别ID',`title` varchar(10) NOT NULL COMMENT '类别名称',`parent_id` char(19) NOT NULL DEFAULT '0' COMMENT '父ID',`sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序字段',`gmt_create` datetime NOT NULL COMMENT '创建时间',`gmt_modified` datetime NOT NULL COMMENT '更新时间',PRIMARY KEY (`id`),KEY `idx_parent_id` (`parent_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='课程科目';
向edu_subject表中添加数据
INSERT INTO `edu_subject` VALUES ('1178214681118568449','后端开发','0',1,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681139539969','Java','1178214681118568449',1,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681181483010','前端开发','0',3,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681210843137','JavaScript','1178214681181483010',4,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681231814658','云计算','0',5,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681252786178','Docker','1178214681231814658',5,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681294729217','Linux','1178214681231814658',6,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681324089345','系统/运维','0',7,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681353449473','Linux','1178214681324089345',7,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681382809602','Windows','1178214681324089345',8,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681399586817','数据库','0',9,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681428946945','MySQL','1178214681399586817',9,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681454112770','MongoDB','1178214681399586817',10,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681483472898','大数据','0',11,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681504444418','Hadoop','1178214681483472898',11,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681529610242','Spark','1178214681483472898',12,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681554776066','人工智能','0',13,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681584136193','Python','1178214681554776066',13,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681613496321','编程语言','0',14,'-09-29 15:47:25','-09-29 15:47:25'),('1178214681626079234','Java','1178214681613496321',14,'-09-29 15:47:25','-09-29 15:47:25'),('1178585108407984130','Python','1178214681118568449',2,'-09-30 16:19:22','-09-30 16:19:22'),('1178585108454121473','HTML/CSS','1178214681181483010',3,'-09-30 16:19:22','-09-30 16:19:22');
EasyExcel
EasyExcel是处理excel表格的工具，将excel里的数据一行一行读进内存，高效简单
新建一个SpringBoot模块用来测试easyexcel
创建excel表格，用于测试
写操作
首先，引入依赖，easyecxel（2.1.1）+poi（3.17），版本之间有对应关系
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency>
创建与excel表对应的实体类
@Datapublic class Entity {@ExcelProperty("学生编号")private Integer sno;@ExcelProperty("学生姓名")private String name;}
使用easyexcel向excel表格中写入数据
public class WriteTest {public static void main(String[] args) {String filename="F:\\temp\\test.xlsx";EasyExcel.write(filename,Entity.class).sheet("学生列表").doWrite(getData());//会自动关流}public static List<Entity> getData(){ArrayList<Entity> list = new ArrayList<>();for (int i=0;i<10;i++){Entity entity = new Entity();entity.setSno(i);entity.setName("lucy"+i);list.add(entity);}return list;}}
执行结果
读操作
创建与excel表对应的实体类
@Datapublic class ReadEntity {@ExcelProperty(value = "学生编号",index = 0)private Integer sno;@ExcelProperty(value = "学生姓名",index = 1)private String sname;}
创建监听器
public class EasyExcelListener extends AnalysisEventListener<ReadEntity> {//一行一行读取数据（不读表头），数据封装在 readEntity@Overridepublic void invoke(ReadEntity readEntity, AnalysisContext analysisContext) {System.out.println(readEntity);}//读取表头@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {System.out.println(headMap);}//读取完成后的处理@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}
使用easyexcel读excel表格的数据
public class ReadTest {public static void main(String[] args) {String filename="F:\\temp\\test.xlsx";EasyExcel.read(filename,ReadEntity.class,new EasyExcelListener()).sheet().doRead();}}
执行结果
课程分类管理功能实现
生成代码框架：在service_edu模块，打开之前写的代码编辑器：src\test\java\com\jiabei\demo\CodeGenerator.java
修改数据库表名
执行后生成代码框架。
添加依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.1</version></dependency>
创建与xlsx对应的实体类com\jiabei\eduservice\entity\xlsx\SubjectSort.java
@Datapublic class SubjectSort {@ExcelProperty(index=0)private String oneSubjectName;@ExcelProperty(index=1)private String twoSubjectName;}
在实体类EduSubject中添加属性自动填充注解
@TableId(value = "id", type = IdType.ID_WORKER_STR)private String id; @TableField(fill=FieldFill.INSERT)private Date gmtCreate;@TableField(fill=FieldFill.INSERT_UPDATE)private Date gmtModified;
controller
@RestController@RequestMapping("/eduservice/subject")@CrossOriginpublic class EduSubjectController {@Autowiredprivate EduSubjectService subjectService;//添加学科类别：将上传的xlsx文件添加到数据库中@PostMapping("addSubjectSort")public R addSubjectSort(MultipartFile file){subjectService.addSubjectSort(file);return R.ok();}}
service
public interface EduSubjectService extends IService<EduSubject> {void addSubjectSort(MultipartFile file);}
serviceImpl
@Servicepublic class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {@Autowiredprivate SubjectSortListener listener;@Overridepublic void addSubjectSort(MultipartFile file) {try {InputStream in = file.getInputStream();EasyExcel.read(in, SubjectSort.class,listener).sheet().doRead();//读取学科分类数据，listener.invoke()监听器负责将它们读到数据库}catch (Exception e){e.printStackTrace();}}}
listener
@Componentpublic class SubjectSortListener extends AnalysisEventListener<SubjectSort> {@Autowiredprivate EduSubjectService subjectService;@Overridepublic void invoke(SubjectSort subjectSort, AnalysisContext analysisContext) {if (subjectSort==null){throw new GuliException(20001,"excel表中没有数据，读取失败");}String pid = addOne(subjectSort.getOneSubjectName());addTwo(subjectSort.getTwoSubjectName(),pid);}//添加一级分类private String addOne(String name){QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();queryWrapper.eq("title",name);queryWrapper.eq("parent_id","0");EduSubject subject = subjectService.getOne(queryWrapper);//该分类是否已存在if (subject==null){//不存在时添加EduSubject s = new EduSubject();s.setParentId("0");s.setTitle(name);subjectService.save(s);}return subjectService.getOne(queryWrapper).getId();}//添加二级分类private void addTwo(String name,String pid){QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();queryWrapper.eq("title",name);queryWrapper.eq("parent_id",pid);EduSubject subject = subjectService.getOne(queryWrapper);if(subject==null){EduSubject s = new EduSubject();s.setTitle(name);s.setParentId(pid);subjectService.save(s);}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) { }}
将edu_subject表中数据清空，然后swagger测试，成功把excel表中的数据读到了数据库
