使用element-ui的时间控件时,前端控件填充时间一直报错
TypeError: dateStr.search is not a function
错误原因:后台时间数据类型和空间所需数据类型不匹配
尝试使用:
@DateTimeFormat(pattern="yyyy-MM-dd")
无法解决,使用下列注解即可解决
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
private Date start;
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
private Date end;
// controller层加上
@InitBinder
public void initBinder(WebDataBinder binder) {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");//自定义格式
dateFormat.setLenient(false);
binder.registerCustomEditor(Date.class, new CustomDateEditor( dateFormat, true));
}
//否则会由于时间类型无法解析的问题,报400
1、数据库动态创建表
数据库中<= 小于等于,>= 大于等于
insert标签中加入 useGeneratedKeys="true" keyProperty="id" keyColumn="id" 插入记录返回主键id(通过插入对象的getter获取)
//xml
<update id="createCollectDataTbNotExists" parameterType="String">
CREATE TABLE IF NOT EXISTS `${name}` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL COMMENT '日期',
`value` double DEFAULT '0' COMMENT '值',
`values` varchar(255) DEFAULT '{}',
`collect_data` varchar(255) DEFAULT '',
`type` int(11) DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
</update>
//dao
void createCollectDataTbNotExists(@Param("name")String name);
2、id串的处理办法,把id串当做字符串处理
<if test="ids != null">
c.id in (#{ids})
</if>
3、后台返回json对象
创建json对象,将json字符串塞到json对象中,返回json的toString方法
4、读取excel,每行包装成一个list。
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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;
public class ExcelImportUtil {
public static List<List<String>> readExcel(MultipartFile file) {
String fileName = file.getOriginalFilename();
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
// return a list contains many list
List<List<String>> lists = new ArrayList<List<String>>();
//读取excel文件
InputStream is = null;
try {
is = file.getInputStream();
//获取工作薄
Workbook wb = null;
if (fileType.equals("xls")) {
wb = new HSSFWorkbook(is);
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook(is);
} else {
return null;
}
//读取第一个工作页sheet
Sheet sheet = wb.getSheetAt(0);
//第一行为标题
for (Row row : sheet) {
ArrayList<String> list = new ArrayList<String>();
for (Cell cell : row) {
//根据不同类型转化成字符串
cell.setCellType(Cell.CELL_TYPE_STRING);
list.add(cell.getStringCellValue());
}
lists.add(list);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (is != null) is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return lists;
}
}
5、两个日期间的日期列表
/* 两个日期间的日期列表 */
public Map<String, CollectDataItem> betweenTwoDate(String beginDateString,
String endDateString) {
Map<String, CollectDataItem> dateMap = new HashMap<>();
Calendar startCalendar = Calendar.getInstance();
Calendar endCalendar = Calendar.getInstance();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Date startDate;
try {
startDate = df.parse(beginDateString);
startCalendar.setTime(startDate);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Date endDate;
try {
endDate = df.parse(endDateString);
endCalendar.setTime(endDate);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
while(true){
if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
dateMap.put(df.format(startCalendar.getTime()), null);
}else{
break;
}
startCalendar.add(Calendar.DAY_OF_MONTH, 1);
}
Map<String, CollectDataItem> result = new LinkedHashMap<>();
dateMap.entrySet().stream()
.sorted(Map.Entry.comparingByKey())
.forEachOrdered(x -> result.put(x.getKey(), x.getValue()));
return result;
}
6、获取任意日期的任意时分秒
private static Date nextDay(Date start) {
Calendar c = Calendar.getInstance();
c.setTime(start);
c.set(Calendar.HOUR_OF_DAY, 23);
c.set(Calendar.MINUTE, 59);
c.set(Calendar.SECOND, 59);
c.set(Calendar.MILLISECOND, 999);
return c.getTime();
}
private static Date dayStartDate(Date date) {
Calendar c = Calendar.getInstance();
c.setTime(date);
c.set(Calendar.HOUR_OF_DAY, 0);
c.set(Calendar.MINUTE, 0);
c.set(Calendar.SECOND, 0);
c.set(Calendar.MILLISECOND, 0);
return c.getTime();
}
7、模板思想,解决freemarker导出的非标准excel格式文件(实际是xml文件)
@RequestMapping("/test/{pid}")
@ResponseBody
public Object dataImport(HttpServletResponse response, HttpServletRequest request,@PathVariable Integer pid) {
//模板文档可放到服务器上
String path = "D:/textual-model.xlsx";
String fileType = path.substring(path.lastIndexOf(".") + 1);
// return a list contains many list
List<List<String>> lists = new ArrayList<List<String>>();
//读取excel文件
InputStream is = null;
Date now = new Date();
String filename = now.getTime() + ".xlsx";
OutputStream outputStream = null;
try {
is = new FileInputStream(path);
//获取工作薄
Workbook wb = null;
if (fileType.equals("xls")) {
wb = new HSSFWorkbook(is);
} else if (fileType.equals("xlsx")) {
wb = new XSSFWorkbook(is);
} else {
return null;
}
Map params = new HashMap<>();
params.put("pid", pid);
TexturalTable textTural = texturalTableService.query(params);
if (textTural == null) {
return "[]";
}
String table = textTural.getTableStr();
JSONObject tableJson = JSONObject.parseObject(table);
//读取第一个工作页sheet
Sheet sheet = wb.getSheetAt(0);
sheet.getRow(1).getCell(1).setCellValue(tableJson.getString("gcbw"));
sheet.getRow(1).getCell(5).setCellValue(tableJson.getString("cgbh"));
sheet.getRow(2).getCell(1).setCellValue(tableJson.getString("zh"));
sheet.getRow(2).getCell(3).setCellValue(tableJson.getString("bzj"));
sheet.getRow(2).getCell(5).setCellValue(tableJson.getString("msqy"));
sheet.getRow(3).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("zkzj"));
sheet.getRow(3).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("cygcz"));
sheet.getRow(4).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("zksd"));
sheet.getRow(4).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("gnj"));
sheet.getRow(5).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("kkgc"));
sheet.getRow(5).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("gwj"));
sheet.getRow(6).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("kdgc"));
sheet.getRow(6).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("gcd"));
sheet.getRow(7).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("zrjyhjcsd"));
sheet.getRow(7).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("jsdcd"));
sheet.getRow(8).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("httscl"));
sheet.getRow(8).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("msff"));
sheet.getRow(9).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("tsclddgc"));
sheet.getRow(9).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("gkgc"));
sheet.getRow(10).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("htfkcl"));
sheet.getRow(10).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("msqsw"));
sheet.getRow(11).getCell(2).setCellValue(tableJson.getJSONObject("zkParam").getString("fkclddgc"));
sheet.getRow(11).getCell(5).setCellValue(tableJson.getJSONObject("cygParam").getString("mshsw"));
sheet.getRow(12).getCell(1).setCellValue(tableJson.getString("sysw"));
sheet.getRow(12).getCell(3).setCellValue(tableJson.getString("xysw"));
sheet.getRow(12).getCell(5).setCellValue(tableJson.getString("tq"));
sheet.getRow(13).getCell(1).setCellValue(tableJson.getString("mssytjsm"));
sheet.getRow(14).getCell(1).setCellValue(tableJson.getString("mssd"));
sheet.getRow(15).getCell(2).setCellValue(tableJson.getJSONObject("aboutPerson").getString("zg"));
sheet.getRow(15).getCell(4).setCellValue(tableJson.getJSONObject("aboutPerson").getString("msz"));
sheet.getRow(15).getCell(6).setCellValue(tableJson.getJSONObject("aboutPerson").getString("tbz"));
sheet.getRow(16).getCell(2).setCellValue(tableJson.getJSONObject("aboutPerson").getString("jhz"));
sheet.getRow(16).getCell(4).setCellValue(tableJson.getJSONObject("aboutPerson").getString("jcz"));
sheet.getRow(16).getCell(6).setCellValue(tableJson.getJSONObject("aboutPerson").getString("tbrq"));
//第一行为标题
response.setContentType("application/octet-stream;charset=UTF-8");
String userAgent = request.getHeader("user-agent").toLowerCase();
if (userAgent != null
&& ((userAgent.indexOf("msie") != -1)
|| (userAgent.indexOf("rv") != -1 && userAgent.indexOf("firefox") == -1))
&& ((userAgent.indexOf("msie") != -1
|| (userAgent.indexOf("rv") != -1 && userAgent.indexOf("chrom") == -1)))) {
// 识别IE浏览器
filename = URLEncoder.encode(filename, "UTF-8");
} else {
// 非IE浏览器
filename = new String(filename.getBytes("UTF-8"), "iso-8859-1");
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
outputStream = new BufferedOutputStream(response.getOutputStream());
wb.write(outputStream);
outputStream.flush();
//response.reset();
return null;
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (is != null) is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return lists;
}
poi读写excel参考 https://www.cnblogs.com/SimonHu1993/p/8202391.html
8、清除数据库表的自增id,让其从1开始计数
TRUNCATE TABLE [TableName];
9、批量清除满足要求的数据表--sql存储过程函数
DROP PROCEDURE IF EXISTS drop_collect_datas;
CREATE PROCEDURE drop_collect_datas( //创建函数
IN projectId INT, //定义变量
IN monitorContentId INT //定义变量
)
BEGIN
DECLARE _done INT;
DECLARE _id INT;
DECLARE _cursor CURSOR FOR
SELECT id FROM device_points a
WHERE a.project_id = projectId and a.monitor_content_id = monitorContentId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = -1;
OPEN _cursor;
REPEAT
FETCH _cursor INTO _id;
SET @prepareStatement = CONCAT("TRUNCATE TABLE collect_data_", _id);
SELECT @prepareStatement;
PREPARE stmt FROM @prepareStatement;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
SET @prepareStatement1 = CONCAT("ALTER TABLE collect_data_", _id, " AUTO_INCREMENT = 1");
SELECT @prepareStatement1;
PREPARE stmt1 FROM @prepareStatement1;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
UNTIL _done = -1 end REPEAT;
CLOSE _cursor;
END;
call drop_collect_datas(9,8); //调用函数
10、integer 比较采坑
包装类型存在一个叫常量池的概念,integer在-127~128范围中默认比较的数值,但超过这个范围则比较的是引用地址,即使两者的数值大小相等,两者也是不相等的。所以可以转为int在进行比较.intValue 或者equal(integer会重载,底层转化为int比较的)
JVM会自动维护八种基本类型的常量池,int常量池中初始化-128~127的范围,所以当为Integer i=127时,在自动装箱过程中是取自常量池中的数值,而当Integer i=128时,128不在常量池范围内,所以在自动装箱过程中需new 128,所以地址不一样。对于Integer来说,你用==比较的是对象引用地址,而不是Integer的值。Integer你要把当当成一个对象来看待
11、阿里云OSS对象存储妙用 需要导入OSSClient包
返回上传文件的访问路径
package cn.com.bucc.monitor.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import org.springframework.web.multipart.MultipartFile;
import com.aliyun.oss.OSSClient;
/**
* file:上传的文件
* dir:上传文件新建文件夹,格式:pic/a/b...
* 返回上传文件路径
* @author Perrin
*
*/
public class OSSClientUtil {
public static String Upload(MultipartFile file ,String dir) throws IOException {
// Endpoint以杭州为例,其它Region请按实际情况填写。
String endpoint = "http://oss-cn-beijing.aliyuncs.com";
// 云账号AccessKey有所有API访问权限,建议遵循阿里云安全最佳实践,创建并使用RAM子账号进行API访问或日常运维,请登录 https://ram.console.aliyun.com 创建。
String accessKeyId = "LTAIuouhGJNOSDGS";
String accessKeySecret = "ytIFTaxeQIPdz9jE4kRuk7rEMVHIfl";
String BucketName = "zhous-zz";
// 创建OSSClient实例。
OSSClient ossClient = new OSSClient(endpoint, accessKeyId, accessKeySecret);
String originName = file.getOriginalFilename();
String suffix = originName.substring(originName.lastIndexOf("."));
Date date = new Date();
String fileName = date.getTime()+suffix;
InputStream inputStream = file.getInputStream();
// 上传文件流。
//dir格式:pic/a/b
ossClient.putObject(BucketName , dir+"/"+fileName , inputStream);
// 关闭OSSClient。
ossClient.shutdown();
return "http://"+BucketName+".oss-cn-beijing.aliyuncs.com/"+dir+"/"+fileName;
}
}
12、获取两个日期之间的所有日期
public ResponseResult betweenTwoDate(String beginDateString,
String endDateString) {
Map<String, CollectDataItem> dateMap = new HashMap<>();
Calendar startCalendar = Calendar.getInstance();
Calendar endCalendar = Calendar.getInstance();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Date startDate;
try {
startDate = df.parse(beginDateString);
startCalendar.setTime(startDate);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Date endDate;
try {
endDate = df.parse(endDateString);
endCalendar.setTime(endDate);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
while(true){
if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
dateMap.put(df.format(startCalendar.getTime()), null);
}else{
break;
}
startCalendar.add(Calendar.DAY_OF_MONTH, 1);
}
Map<String, CollectDataItem> result = new LinkedHashMap<>();
List dateList = new ArrayList<>();
dateMap.entrySet().stream()
.sorted(Map.Entry.comparingByKey())
.forEachOrdered(x -> result.put(x.getKey(), x.getValue()));
result.forEach((key,value) -> {
dateList.add(key);
});
return ResponseResult.ok(dateList);
}