spring内jdbcTemplate查询列表Timestamp类型返回前端为时间戳、及org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: No serializer found for class java.io.FileDescriptor and no properties discovered to create BeanSerializer.
一、jdbcTemplate.queryForList返回包含Timestamp类型数据返回处理
在系统中有需要对内部人员开出一个数据查询口子，前端vue，后端java（使用spring内部jdbcTemplate执行sql）。
页面代码如下：
<template>
<div>
<el-container>
<el-header style="height: auto;background-color: #f5f5f5;display: inline-flex;">
<el-input
type="textarea"
contentType="text/html"
:autosize="{ minRows: 6, maxRows: 6}"
style="width: 85%;margin-top: 10px;margin-bottom: 10px;"
placeholder="请输入内容"
v-model="sql">
</el-input>
<div class="button-group">
<el-button type="primary" icon="el-icon-search" :loading="queryLoading" @click="queryData">查询
</el-button>
<el-button :loading="exportLoading" @click="exportExcel">
<svg-icon icon-class="download"></svg-icon>
导出
</el-button>
<el-button icon="el-icon-plus" :loading="updateLoading" @click="updateData">编辑</el-button>
</div>
</el-header>
<el-main style="height: auto">
<el-table
v-if="tableData && tableData.length > 0"
:data="tableData"
:height="tableHeight"
border
style="width: 100%">
<template v-for="item in columnData">
<el-table-column
:prop="item"
:label="item"
width="180px"
show-overflow-tooltip
>
</el-table-column>
</template>
</el-table>
</el-main>
</el-container>
</div>
</template>
<script>
export default {
data() {
return {
sql: '',
tableHeight: window.innerHeight - 284 + 'px',
queryLoading: false,
updateLoading: false,
exportLoading: false,
tableData: [],
columnData: []
}
},
mounted() {
let _this = this;
window.onresize = function () {
_this.tableHeight = window.innerHeight - 284 + 'px';
};
},
methods: {
alertMessage(message, type) {
this.$message({
message: message,
type: type
});
},
queryData() {
this.queryLoading = true;
this.tableData = [];
this.columnData = [];
let data = {
sql: this.sql
};
this.$nextTick(async () => {
let params = {
className: "DataQuery",
method: "queryData",
param: data
};
try{
let res = await this.$store.dispatch("http/post", params);
if (res.code == 200) {
this.alertMessage("查询成功", "success");
if (res.data && res.data.length > 0) {
for (let key in res.data[0]) {
this.columnData.push(key);
}
}
this.tableData = [...res.data];
this.queryLoading = false;
} else {
this.alertMessage(res.msg, "warning");
this.queryLoading = false;
}
}catch (e) {
this.alertMessage("查询失败", "error");
this.queryLoading = false;
}
});
},
exportExcel() {
if (this.sql && this.sql.trim() != '') {
this.$router.push({
path: '/dataExport',
query: {
name: '数据查询',
sql: this.sql,
random:Math.random()
}
})
} else {
this.alertMessage("sql不能为空", "warning");
}
/*this.exportLoading = true;
this.tableData = [];
this.columnData = [];
let data = {
sql: this.sql
};
this.$nextTick(async () => {
let params = {
className: "DataQuery",
method: "queryData",
param: data
};
let res = await this.$store.dispatch("http/post", params);
if (res.code == 200) {
if (res.data && res.data.length > 0) {
for (let key in res.data[0]) {
this.columnData.push(key);
}
}
this.tableData = [...res.data];
require.ensure([], () => {
const {
export_json_to_excel
} = require('vendor/Export2Excel');
const tHeader = [...this.columnData];
const filterVal = [...this.columnData];
const list = [...this.tableData];
const data = this.formatJson(filterVal, list);
let name = this.formatDate();
export_json_to_excel(tHeader, data, name);
})
this.alertMessage("导出成功", "success");
this.exportLoading = false;
} else {
this.alertMessage(res.msg, "warning");
this.exportLoading = false;
}
});*/
},
formatDate() {
let currDate = new Date();
let year = currDate.getFullYear();
let month = currDate.getMonth() + 1 < 10 ? "0" + (currDate.getMonth() + 1) : currDate.getMonth() + 1;
let date = currDate.getDate() < 10 ? "0" + currDate.getDate() : currDate.getDate();
let hh = currDate.getHours() < 10 ? "0" + currDate.getHours() : currDate.getHours();
let mm = currDate.getMinutes() < 10 ? "0" + currDate.getMinutes() : currDate.getMinutes();
let ss = currDate.getSeconds() < 10 ? "0" + currDate.getSeconds() : currDate.getSeconds();
return year + "年" + month + "月" + date + "日" + " " + hh + "时" + mm + '分' + ss + '秒';
},
// 参数过滤
formatJson(filterVal, jsonData) {
return jsonData.map(v => filterVal.map(j => v[j]))
},
updateData() {
this.updateLoading = true;
this.tableData = [];
this.columnData = [];
let data = {
sql: this.sql
};
this.$nextTick(async () => {
let params = {
className: "DataQuery",
method: "updateData",
param: data
};
let res = await this.$store.dispatch("http/post", params);
if (res.code == 200) {
this.alertMessage("编辑成功", "success");
this.updateLoading = false;
} else {
this.alertMessage(res.msg, "warning");
this.updateLoading = false;
}
});
}
},
filters: {
formatDate(item) {
console.log(item);
}
}
}
</script>
<style rel="stylesheet/scss" lang="scss" scoped>
.el-button {
width: 100px;
height: 32px;
margin-top: 10px;
margin-left: 0px;
padding-left: 12px;
padding-top: 8px;
}
.button-group {
width: 15%;
text-align: center;
flex-direction: column;
display: inline-flex;
align-items: center;
justify-content: center;
}
</style>
在后端使用spring内部jdbcTemplate进行查询时，oracle数据库中包含Timestamp类型数据，则在前端不好进行处理（查询sql不同，返回数据不同，不好针对特定数据进行处理，显示很简单）。
后台代码先贴出来：
引入：
@Autowired
JdbcTemplate jdbcTemplate;
private Map<String,Object> queryData(String param){
Map<String,Object> result = null;
Map<String,Object> resultMap = new HashMap<>();
String sql = "";
try {
result = new HashMap<String,Object>();
JSONObject data = JSONObject.parseObject(param, JSONObject.class);
sql = data.getString("sql");
//判断查询sql是否符合规范
resultMap.put("code",201);
//判断sql是为为空
if(null==sql || "".equals(sql.trim())){
resultMap.put("msg","查询sql不能为空，请检查");
Constants.getSuccMsg(result,resultMap);
return result;
}
//判断sql是否为select开头
sql = sql.trim();//去除首尾空格
if(!sql.substring(0, 6).toUpperCase().equals("SELECT")){
resultMap.put("msg","查询sql应以SELECT...语句开始，请检查");
Constants.getSuccMsg(result,resultMap);
return result;
}
//判断是否为一条sql语句
int specIndex = sql.indexOf(";");
if(sql.endsWith(";") || sql.endsWith("；")){
sql = sql.substring(0,sql.length()-1);
}
if(specIndex >= 0 && specIndex != sql.length() && (
sql.substring(specIndex + 1).trim().toUpperCase().startsWith("ALTER")
|| sql.substring(specIndex + 1).trim().toUpperCase().startsWith("BEGIN")
|| sql.substring(specIndex + 1).trim().toUpperCase().startsWith("SELECT")
|| sql.substring(specIndex + 1).trim().toUpperCase().startsWith("INSERT")
|| sql.substring(specIndex + 1).trim().toUpperCase().startsWith("UPDATE")
|| sql.substring(specIndex + 1).trim().toUpperCase().startsWith("DELETE")
|| sql.substring(specIndex + 1).trim().toUpperCase().startsWith("CREATE")
)){
resultMap.put("msg","查询sql应为一条，请检查");
Constants.getSuccMsg(result,resultMap);
return result;
}
//判断是否有where条件
String value="[ \n]((?i)where)[ \n]";
Pattern r = Pattern.compile(value);
Matcher m = r.matcher(sql);
if(!m.find()) {
value ="((?i)count)";
r = Pattern.compile(value);
m = r.matcher(sql);
if(!m.find()){
resultMap.put("msg","查询sql未添加where条件，请检查");
Constants.getSuccMsg(result,resultMap);
return result;
}
}
List<Map<String,String>> list = (List) jdbcTemplate.queryForList(sql);
resultMap.put("code",200);
resultMap.put("data",list);
} catch (Exception e) {
resultMap.put("code",201);
resultMap.put("msg","查询sql不正确，请检查");
resultMap.put("data",e.toString());
logger.error("数据查询--查询sql不正确，sql为：" + sql);
logger.error("数据查询--查询sql不正确，错误信息为：" + e.toString());
}
Constants.getSuccMsg(result,resultMap);
return result;
}
查询数据库代码：
List<Map<String,String>> list = (List) jdbcTemplate.queryForList(sql);
sql为要查询的sql，若返回的数据中包含Timestamp类型数据，则在前台页面上就会显示为时间戳，为进行转换为固定格式字符串显示。
此时就需要在后端进行处理：配置文件中（application.properties）添加如下配置。
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8
二、HttpMessageNotWritableException异常处理
我在查询特定的sql时，发现其中某些类型数据（如下图），就会导致在返回到前端时报此异常。
后端断点是可以看到数据正常查询出来，只是无法正常返回到前端页面上，上述页面无发正常显示查询出的结果。
处理方法：
在后端配置文件中添加配置即可解决：
#对无法转换的对象进行忽略
spring.jackson.serialization.fail-on-empty-beans=false
问题记录不易，有帮助的话就给小弟点个赞吧。
1549
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
