java - 使用spring MVC对多列的动态更新查询
问题描述
我有一个 JSON 数据,如下所示:
{
"table" : "customer",
"uniqueColumn" : "customer",
"uniqueColVal" : "cust_786",
"columns" :
[{
"column_1" : "column_1 Val",
"column_2" : "column_2 Val",
"column_..." : "column_... Val",
"column_..." : "column_... Val",
"column_..." : "column_... Val",
"column_n" : "column_n Val"
}]
}
我需要执行一个查询,应该是下面的形式
UPDATE customer SET column_1 = 'column_1 Val', column_2 = 'column_2 Val', column_... = 'column_... Val', column_n = 'column_n Val' WHERE customer = 'cust_786';
我正在使用 Spring MVC 进行处理,我编写的代码如下。它不完整。
@Override
public Map<String, Object> updateTabColumnValues(Map<String, Object> data)
{
Map<String, Object> response = new HashMap();
try
{
String table= data.get("table").toString();
String uniqueid = data.get("uniqueid").toString();
if (table!=null && uniqueid !=null)
{
String column = null, columnVal = null, updateColumn = null, updateColumnVal = null;
JSONObject jsonObj = new JSONObject(data);
JSONArray columnsToUpdate = jsonObj.getJSONArray("columns");
for (int i = 0; i < columnsToUpdate.length(); i++)
{
if (i == columnsToUpdate.length() - 1)
{
JSONObject json_Obj = columnsToUpdate.getJSONObject(i);
column = json_Obj.keys().next().toString();
columnVal = json_Obj.getString(column).toString();
updateColumn = updateColumn + column.toString();
updateColumnVal = updateColumnVal + " = " + columnVal.toString() + "'";
}
}
System.out.println("UPDATE " + table+ " SET " + updateColumn +" = " + updateColumnVal + " WHERE " + data.get("uniqueColumn").toString() +" = '" + data.get("uniqueColVal").toString() +"';");
}
else
{
response.put("status", false);
LOGGER.info("Failed to get table>>> " + table+ " OR uniqueid >>> " + uniqueid);
}
}
catch (Exception e)
{
response.put("status", false);
LOGGER.error("Error @editLayerAttributeByUniqueID ", e);
System.err.println("Error @editLayerAttributeByUniqueID " + e);
}
return response;
}
如果有人可以在这里帮助我,那将非常有帮助。提前致谢。
解决方案
最后我能找到满意的答案。请按照以下说明进行操作。
您需要导入一些packages
,我在Maven repository
下面提到相同的。在pom.xml中添加依赖项
<!-- https://mvnrepository.com/artifact/org.json/json -->
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20180813</version>
</dependency>
<!-- For PostgreSQL Database connectivity -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.0.RELEASE</version>
</dependency>
现在导入Impl
文件中的包,如下所示:
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.jdbc.core.JdbcTemplate;
逻辑在下面的代码中解释
public JdbcTemplate getJdbcTemplate()
{
return jdbcTemplate;
}
@Override
//Defines a Map named as updateTabColumnValues to get data from client
public Map<String, Object> updateTabColumnValues(Map<String, Object> data)
{
//Defines a Map named as response to send data to client
Map<String, Object> response = new HashMap();
try
{
String table = data.get("table").toString();
String uniqueColumn = data.get("uniqueColumn").toString();
String uniqueValue = data.get("uniqueValue").toString();
if ((uniqueColumn != null && uniqueValue != null) && table != null)
{
String column;
String columnVal;
String keyValuePair = "";
String query = null;
JSONObject jsonObj = new JSONObject(data);
//Gets values in the key columns to columnsToUpdate
JSONArray columnsToUpdate = jsonObj.getJSONArray("columns");
//Loops each elements with in the array
if (columnsToUpdate.length() > 0)
{
for (int i = 0; i < columnsToUpdate.length(); i++)
{
if (i == columnsToUpdate.length() - 1)
{
//Create Key Value pair without adding comma at the end
JSONObject json_Obj = columnsToUpdate.getJSONObject(i);
column = json_Obj.keys().next();
columnVal = json_Obj.getString(column);
keyValuePair = keyValuePair + column + " = '" + columnVal + "'";
}
else
{
//Create Key Value pair with comma at the end
JSONObject json_Obj = columnsToUpdate.getJSONObject(i);
column = json_Obj.keys().next();
columnVal = json_Obj.getString(column);
keyValuePair = keyValuePair + column + " = '" + columnVal + "' , ";
}
}
int queryValidator = -1;
query = "UPDATE " + table +" SET "+ keyValuePair + " WHERE " + uniqueColumn + " = '" + uniqueValue +"';";
LOGGER.info("Query is >>> " + query);
//Uses getJdbcTemplate() to run query
queryValidator = getJdbcTemplate().update(query);
//Validating the query execution status with database
if (queryValidator >= 0)
{
response.put(stateOfstatus,true);
}
else
{
response.put(stateOfstatus,false);
}
}
else
{
response.put(stateOfstatus, false);
}
}
else
{
response.put(stateOfstatus, false);
LOGGER.info("Failed to get table >>> " + table + " OR uniqueColumn >>> " + uniqueColumn + " OR uniqueValue >>>" + uniqueValue);
}
}
catch (Exception e)
{
response.put(stateOfstatus, false);
LOGGER.error("Error in updateTabColumnValues ", e);
response.put("message", e);
}
return response;
}
这是在特殊用例下进行的与 RnD 相关的任务。上述逻辑完美有效地传递了输出。
推荐阅读
- c# - EF Core - 将实体映射到自定义类会引发循环引用的 stackoverflow
- python - 我在 python 中使用 beautifulSoup 将一页转到另一页时遇到问题?
- c# - 将消息从 Botframework 发送到 Azure Test WebChat
- typescript - 打字稿签名和范畴论
- java - 部署应用程序时出现 ClassNotFoundException 时如何处理 hazelcast
- python - 可选的非位置参数 Python 程序
- javascript - 如何在 jQuery / JS 的同一页面上的 4 个 div 中加载 4 个不同的页面(包含 d3js 图)?
- python - Pygame - 如何更快地blit图像
- jquery - jquery .html 值不返回代码隐藏 asp.net
- firebase - 我可以使用不同的身份验证提供商从 firebase 收集哪些数据?