2023/10
1.oracle使用序列主键自增
step:1 执行序列语句(sys_file_upload表名)
create sequence seq_sys_file_upload
increment by 1
start with 100
nomaxvalue
nominvalue
cache 20;
step:2 新增语句加如下sql
<selectKey keyProperty="fileId" order="BEFORE" resultType="long">
select seq_sys_file_upload.nextval as fileId from DUAL
</selectKey>
2.附件上传-ruoyi(批量上传)
step:实现步骤
- 获取请求路径
- 遍历上传文件
2-1 创建文件对象,判断文件是否存在,若不存在则创建
2-2 获取上传的文件名
2-3 获取原始文件的.索引
2-4 获取上传的文件后缀
2-5 创建新的文件名使用uuid随机数
2-6 创建本地File对象,访问路径
2-7 存储上传信息
service
public List<SysFileUpload> uploadFile(MultipartFile[] file, HttpServletRequest request);
impl
校验上传文件格式:工具类封装文件格式
public static final String[] FILE_UPLOAD_FORMAT = {
".jpg",".jpeg",".png",".gif",".bmp",".pdf",".doc",".docx",".xls",".xlsx",".txt",".ppt",".pptx"
};
public static String UPLOAD_PATH;
public static final String RESOURCE_PREFIX = "/profile";
@Override
public List<SysFileUpload> uploadFile(MultipartFile[] file, HttpServletRequest request) {
List<SysFileUpload> resultList = new ArrayList<>();
StringBuffer requestURL = request.getRequestURL();
String contextPath = request.getServletContext().getContextPath();
String domain = requestURL.delete(requestURL.length() - request.getRequestURI().length(), requestURL.length()).append(contextPath).toString();
String uploadPath = ProjectConfig.UPLOAD_PATH + "/" + DateUtils.getDate();
for(int i=0;i<file.length;i++) {
File temp = new File(uploadPath);
if (!temp.exists()){
temp.mkdirs();
}
String name = file[i].getOriginalFilename();
int endIndexOf = name.lastIndexOf(".");
String endFile = name.substring(endIndexOf,name.length());
boolean flag = Arrays.asList(MimeTypeUtils.FILE_UPLOAD_FORMAT).contains(endFile.toLowerCase(Locale.ROOT));
if(!flag){
throw new RuntimeException("文件格式不正确");
}
String newName = randomUUID().toString()+endFile;
String newPath = uploadPath+"/"+newName;
File localFile = new File(newPath);
String url = domain +"/"+ ProjectConstant.RESOURCE_PREFIX +"/"+ DateUtils.getDate()+'/'+newName;
try {
file[i].transferTo(localFile);
SysFileUpload sysFileUpload = new SysFileUpload();
sysFileUpload.setFileName(newName);
sysFileUpload.setFileStoreName(name);
sysFileUpload.setFileSurfix(endFile);
sysFileUpload.setFileContentType("1");
sysFileUpload.setFilePath(url);
sysFileUpload.setCreateBy(ShiroUtils.getUserIdByToken());
sysFileUploadMapper.insertSysFileUpload(sysFileUpload);
resultList.add(sysFileUpload);
}catch (IOException e){
e.printStackTrace();
}
}
return resultList;
}
controller
@PostMapping("/uploadFile")
@ApiOperation("通知公告附件上传")
public AjaxResult uploadFile(@RequestParam(value = "files") MultipartFile[] file, HttpServletRequest request) {
if(file == null){
return AjaxResult.error("附件不能为空");
}
try {
return AjaxResult.success(sysFileUploadService.uploadFile(file, request));
}catch (RuntimeException e){
e.printStackTrace();
return AjaxResult.error("文件格式不正确");
}
}
3.上移/下移
step:1 上移:取上一条记录排序号,将当前记录与上一条记录排序号调换位置
step:2 下移:取下一条记录排序号,将当前记录与下一条记录排序号调换位置
4.置顶
step:1 置顶:获取最大的记录排序号,修改当前记录的排序号为最大排序号+1
5.Oracle中的Nulls first和Nulls last用法
Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)
Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)
eg: order by is_top desc, top_time desc nulls last
6.查重
select 字段,count(*) from 表名 group by 字段 having count (*) >1
eg: select user_id, count(*) from sys_user group by user_id having count(*) > 1