将json插入postgreSQL
将json插入postgreSQL
提问于 2019-07-10 21:00:00
我正在尝试使用Java8、Eclipse和SpringBoot将由ARC客户端(类似于postman)提供的json对象插入到我的webService控制器中，然后插入到Java11.4 dB中。我得到以下错误：
2019-07-10 13:02:42.356 ERROR 21516 --- [nio-8086-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO cwb_patent(patent_number,created_date,patent_data) VALUES (?,current_timestamp,cast(? AS JSON)) ON CONFLICT (patent_number) DO UPDATE SET patent_data = cast(? AS JSON), created_date = current_timestamp]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of com.clarivate.singularity.chemworkbench.webservices.common.PatentChemicalData. Use setObject() with an explicit Types value to specify the type to use.] with root cause
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of com.clarivate.singularity.chemworkbench.webservices.common.PatentChemicalData. Use setObject() with an explicit Types value to specify the type to use.
at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:967) ~[postgresql-42.2.2.jar:42.2.2]我的问题是：
public static final String mergeCwbPatent = "INSERT INTO cwb_patent" +
"(patent_number,created_date,patent_data)" +
" VALUES (:patent_number,current_timestamp,cast(:patent_data AS JSON))" +
" ON CONFLICT (patent_number)" +
" DO UPDATE SET patent_data = cast(:patent_data AS JSON), created_date = current_timestamp";我的控制器是：
@RequestMapping(value = "/merge-patent-data", method = RequestMethod.POST)
public Long mergePatentChemicalData(@RequestBody PatentChemicalData patentChemicalData) {
Long ret = db.mergeCwbPatent(patentChemicalData);
return ret;
}执行插入的方法是：
public Long mergeCwbPatent(PatentChemicalData pcd)
{
Map<String, Object> parameters = new HashMap<String, Object>();
Long ret = null;
String patentNumber;
if (pcd == null )
{
logger.error("DS4A Trying to Insert invalid PatentChemicalData into CWB_PATENT");
}else {
patentNumber=pcd.getPatentChemicalData().getPatentNumber();
logger.debug("DS4B Inserting data for patentNumber = " + patentNumber + " into cwb_patent");
parameters.put("patent_number", patentNumber); // THIS LINE THROWS THE ERROR
parameters.put("patent_data", pcd); // This is the JSON object
ret = jdbcTemplate.queryForObject(SqlQueries.mergeCwbPatent, parameters,Long.class);
}
return ret;
}堆栈跟踪显示“使用Use setObject()”，但这并不适用于我的参数HashMap。这个tenmilessquare链接表明我在正确的轨道上，但我似乎不能让它工作。感谢您的帮助。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-07-11 18:03:13
遵循@a_horse_with_no_name建议，我添加了一个函数来将PatentChemicalData对象转换为字符串：
public String patentChemicalDataToJson(PatentChemicalData pcd) throws JsonProcessingException {
ObjectMapper Obj = new ObjectMapper();
String jsonStr = Obj.writeValueAsString(pcd);
return jsonStr;
}然后在mergeCwbPatent()中，我按照建议使用了这个函数：
String jsonPcdStr= sf.patentChemicalDataToJson(pcd);
parameters.put("patent_data", jsonPcdStr);它运行得很好。我已经包含了杰克逊，我以为SpringBoot会自动完成杰克逊的转换，但它不是。SpringBoot的问题是它让你变得懒惰！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56971278
复制相关文章
相似问题
腾讯云开发者
