From 2e60c6034fdfbcfbc4d7a91f0a84926688a50009 Mon Sep 17 00:00:00 2001
From: fs-danaus <danaus314@qq.com>
Date: 星期五, 13 五月 2022 09:29:29 +0800
Subject: [PATCH] 优化主键处理避免更新删除时条件不带主键
---
src/com/yc/action/panval/PanelCommitAction.java | 12 ++
src/com/yc/ionic/build/BuildListPage.java | 16 ----
src/com/yc/action/grid/GTGrid.java | 61 ++++++++++----
src/com/yc/action/grid/TreeGrid.java | 29 +------
src/com/yc/service/grid/GridServiceImpl.java | 41 +++++----
src/com/yc/app/v2/service/impl/BaseFormServiceImpl.java | 19 ++--
src/com/yc/api/controller/ApiController.java | 44 ++--------
7 files changed, 97 insertions(+), 125 deletions(-)
diff --git a/src/com/yc/action/grid/GTGrid.java b/src/com/yc/action/grid/GTGrid.java
index 6db12fd..cfc89f6 100644
--- a/src/com/yc/action/grid/GTGrid.java
+++ b/src/com/yc/action/grid/GTGrid.java
@@ -1981,7 +1981,7 @@
//m = md2;
}
if (list != null && list.size() > 0) {
- this.checkPrimKey(g, name);
+ this.checkPrimKey(g, name, list);
String[] keys = (this.keyid != null && this.keyid.length() > 0) ? this.keyid.split(",") : null;
for (HashMap map : list) {
StringBuilder key = new StringBuilder();
@@ -2034,14 +2034,14 @@
}
getPicInfo(map);
//澧炲姞鍒犻櫎鍙仮澶嶅鐞嗭紝2琛�
- if (key1.toString().length() == 0)
+ if (key1.toString().length() == 0) {
sqlDel.add(proccSql(name) + ",' 1=1 " + key.toString().replaceAll("&", "&").replaceAll("'", "''") + "'");
- else
+ }else {
sqlDel.add(proccSql(name) + ",' 1=1 " + key1.toString().replaceAll("&", "&").replaceAll("'", "''") + "'");
-
- sqlDel.add(" delete " + name + " where 1=1 " + key1.toString().replaceAll("&", "&"));
- //澧炲姞鍒犻櫎
- //this.getProcValues(map,str);
+ }
+ if(org.apache.commons.lang3.StringUtils.isNotBlank(key1.toString())) {
+ sqlDel.add(" delete " + name + " where 1=1 " + key1.toString().replaceAll("&", "&"));
+ }
}
keys = null;
}
@@ -3017,7 +3017,7 @@
colns = g.getColns();
}
if (update1 != null && update1.size() > 0) {
- checkPrimKey(g, tableName);
+ checkPrimKey(g, tableName,vss);
for (int i = 0; i < update1.size(); i++) {
StringBuilder key = new StringBuilder();
StringBuilder value = new StringBuilder();
@@ -3151,9 +3151,19 @@
vss = null;
}
- private void checkPrimKey(com.yc.utils.Grid g, String name) {//妫�鏌ユ槸鍚﹁〃鏄惁璁剧疆浜嗕富閿紝鏇存柊锛屽垹闄ゆ搷浣滈兘闇�瑕佽繖涓潵浣滀负鍞竴鏉′欢
+ private void checkPrimKey(com.yc.utils.Grid g, String name, List<HashMap<String, String>> update1) {//妫�鏌ユ槸鍚﹁〃鏄惁璁剧疆浜嗕富閿紝鏇存柊锛屽垹闄ゆ搷浣滈兘闇�瑕佽繖涓潵浣滀负鍞竴鏉′欢
if (g.getPrimeKey() == null || "".equalsIgnoreCase(g.getPrimeKey()))
throw new ApplicationException(name + "琛ㄦ病鏈夎缃富閿紒");
+ //---瀛樺湪涓婚敭璁剧疆锛屼絾鍓嶇娌′紶涓婚敭瀵瑰簲鐨勫�硷紝涔熼渶瑕佹彁绀�
+ final String[] keys = g.getPrimeKey().split(";");
+ if(update1!=null&&update1.size()>0){
+ Arrays.stream(keys).forEach(key->{
+ final String object = update1.get(0).get(key.toLowerCase());
+ if(org.apache.commons.lang3.StringUtils.isBlank(object)){
+ throw new ApplicationException(name + "涓婚敭瀛楁"+key+"娌℃湁浼犲�硷紝涓嶈兘鎵ц鎿嶄綔锛岃В鍐虫柟娉曪細璇峰湪9802鍕鹃�変笂姝ゅ瓧娈电殑鎰熷簲灞炴��");
+ }
+ });
+ }
}
private boolean checkDocType() {
@@ -3530,7 +3540,7 @@
* @param isPramyPan TODO
*/
private void convertPanelSQL(Map<String, TableMetaData> tableMetaDatas, List<HashMap<String, String>> update1, String tableName, String isPramyPan, String dbid) {
- if (update1 != null) {
+ if (update1 != null&&update1.size()>0) {
List<String> primaryKey = null;
//496瀛愰潰鏉垮湪鏂板崟浣嗗張鍦ㄨ繖閲屾墽琛屾椂璺宠繃涓婚敭妫�鏌�
boolean sub496=false;
@@ -3545,13 +3555,6 @@
(org.apache.commons.lang3.StringUtils.isNotBlank(update1.get(0).get("doccode"))?true:false)
) {
primaryKey = gridService.getPrimaryKey(tableName);
- //澧炲姞闈㈡澘璇诲彇琛ㄤ负瑙嗗浘锛屼粠鎸囧畾琛ㄥ叧閿瓧璁剧疆閲岃鍙栦俊鎭�
- if (primaryKey == null || primaryKey.size() == 0) {
- String keyfields = gridService.getTableKeyFields(tableName);
- if (org.apache.commons.lang3.StringUtils.isNotBlank(keyfields)) {
- primaryKey = Arrays.asList(keyfields.split(";"));
- }
- }
}else{
sub496=true;
}
@@ -3560,6 +3563,20 @@
}finally {
SpObserver.setDBtoInstance();
}
+ //鍙栧緱涓婚敭锛屽垽鏂富閿病鍊兼椂瑙﹀彂寮傚父
+ if (primaryKey == null || primaryKey.size() ==0) {
+ throw new ApplicationException(tableName + "娌℃湁瀹氫箟涓婚敭锛屼笉鑳芥墽琛屾洿鏂�(update)鎿嶄綔");
+ }
+ //----娴嬭瘯bug鎵�鍔犱唬鐮侊紝娴嬭瘯瀹岃娉ㄩ噴鎺�
+// if(tableName.equals("v110520")){
+// ((Map) update1.get(0)).remove("doccode");
+// }
+ primaryKey.forEach(key->{
+ final Object object = ((Map) update1.get(0)).get(key.toLowerCase());
+ if(object==null){
+ throw new ApplicationException(tableName + "涓婚敭瀛楁"+key+"娌℃湁浼犲�硷紝涓嶈兘鎵ц鏇存柊(update)鎿嶄綔锛岃В鍐虫柟娉曪細璇峰湪9802鍕鹃�変笂姝ゅ瓧娈电殑鎰熷簲灞炴��");
+ }
+ });
for (int i = 0; i < update1.size(); i++) {
StringBuilder key = new StringBuilder();
@@ -3568,6 +3585,7 @@
key.append(" set ");
value.append(" where 1=1");
HashMap<String, String> up = update1.get(i);
+
if (this.isTaobao) {// 鍘绘帀鐗瑰畾ERP鐨勫瓧娈靛唴瀹�
up.remove("doccode");
up.remove("enterdate");
@@ -3714,11 +3732,16 @@
" end \n";
}
if (!this.type496.equals("update")) {
- if (isPramyPan != null && "1".equalsIgnoreCase(isPramyPan))
+ if (isPramyPan != null && "1".equalsIgnoreCase(isPramyPan)) {
this.updatesql.add(0, updateSql);
- else
+ }else {
this.updatesql.add(updateSql);
+ }
} else {// 496绫诲瀷鐨剈pdate璋冪敤
+ //---澧炲姞澶勭悊value鐨勫�兼病鏈墂here鏉′欢锛屽彧鏄痸alue=where 1=1鐨勬儏鍐碉紝浼氬鑷存洿鏂版墍鏈夋暟鎹紝濡傛灉鍑虹幇杩欑鎯呭喌锛岄渶瑕佹姏鍑洪敊璇�
+ if(value.toString().trim().equals("where 1=1")){
+ throw new ApplicationException(tableName + "娌℃湁瀹氫箟涓婚敭锛屼笉鑳芥墽琛屾洿鏂�(update)鎿嶄綔");
+ }
this.wherepan496 = "if exists(select top 1 1 from " + tableName + value.toString() + ")\n begin\n";
this.wherepan496 += updateSql;
this.wherepan496 += " end \n";
diff --git a/src/com/yc/action/grid/TreeGrid.java b/src/com/yc/action/grid/TreeGrid.java
index cb03e13..37ba84e 100644
--- a/src/com/yc/action/grid/TreeGrid.java
+++ b/src/com/yc/action/grid/TreeGrid.java
@@ -314,43 +314,22 @@
return null;
}
- private void getFirstField(TreeGridDTO dto) throws DataAccessException, SQLException {
- //1鍏堣琛ㄥ叧閿姛鑳斤紝濡傛灉娌℃湁鐩稿叧鐨勫啀璇昏〃缁撴瀯
+ public void getFirstField(TreeGridDTO dto) throws DataAccessException, SQLException {
- String keyfields = null;
+ List<String> keys = null;
try {
SpObserver.setDBtoInstance("_" + dto.dbid);
- keyfields = gridService.getTableKeyFields(dto.table);
+ keys = gridService.getPrimaryKey(dto.table);
} finally {
SpObserver.setDBtoInstance();
}
-
- if (keyfields == null || "".equals(keyfields)) {
- //2鍙彇涓昏〃,涓讳粠琛ㄦ儏鍐� 鍙�18,19绫诲瀷锛屼笁琛ㄦ殏鏃朵笉鑰冭檻
- List<String> keys = null;
- try {
- SpObserver.setDBtoInstance("_" + dto.dbid);
- keys = gridService.getPrimaryKey(dto.table);
- } finally {
- SpObserver.setDBtoInstance();
- }
+ if(keys!=null) {
for (String str : keys) {
if ("".equals(dto.field))
dto.field += str;
else
dto.field += ";" + str;
}
- keys = null;
- } else {
- String[] arry = keyfields.split(";");
- for (int i = 0; i < arry.length; i++) {
- if ("".equals(dto.field))
- dto.field += arry[i].toLowerCase();
- else
- dto.field += ";" + arry[i].toLowerCase();
-
- }
- arry = null;
}
dto.primeKey = dto.field;
}
diff --git a/src/com/yc/action/panval/PanelCommitAction.java b/src/com/yc/action/panval/PanelCommitAction.java
index d03f930..b5b7b77 100644
--- a/src/com/yc/action/panval/PanelCommitAction.java
+++ b/src/com/yc/action/panval/PanelCommitAction.java
@@ -12,6 +12,7 @@
import com.yc.service.panel.SqlDBHelperIfc;
import com.yc.service.panel.SystemSettingsDao;
import com.yc.utils.SessionKey;
+import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
@@ -172,11 +173,18 @@
public String PostDeleteDocSql(String formid, String doccode,String docstatus,String dbid,String formtype,
String userName,String userCode,String no,Map<String, String> env) {
Map<String, Object> map =null;
+ CallBackMessage callBackMessage=new CallBackMessage();
try {
SpObserver.setDBtoInstance("_"+dbid);
map = sDao.getFormIdInTable(Integer.parseInt(formid));
- }finally {
+ }catch (Exception e){
+ return callBackMessage.sendErrorMessage(e.getCause() != null ? e.getCause().getMessage():e.getMessage());
+ }
+ finally {
SpObserver.setDBtoInstance();
+ }
+ if(StringUtils.isBlank(doccode)){
+ return callBackMessage.sendErrorMessage("涓嶈兘鍒犻櫎鍗曞彿涓虹┖鐨勫崟鎹�!");
}
env.put("doccode",doccode);
List<String> listSql=new ArrayList<String>();
@@ -192,7 +200,7 @@
int formType_int = (strTi != null) ? Integer.parseInt(strTi) : 0;//
strTi = no;
int no496 = (strTi != null) ? Integer.parseInt(strTi) : -1;// 閽堝499鐩稿悓绫诲瀷
- CallBackMessage callBackMessage=new CallBackMessage();
+
try {
SpObserver.setDBtoInstance("_"+dbid);
List<Map<String, Object>> selshandan = sDbHelperIfc.getselectshandan("select 1 from " + HDTable
diff --git a/src/com/yc/api/controller/ApiController.java b/src/com/yc/api/controller/ApiController.java
index 1c7b14a..dd00ec1 100644
--- a/src/com/yc/api/controller/ApiController.java
+++ b/src/com/yc/api/controller/ApiController.java
@@ -1145,53 +1145,29 @@
private void getFirstField(TreeGridDTO dto) {
//1鍏堣琛ㄥ叧閿姛鑳斤紝濡傛灉娌℃湁鐩稿叧鐨勫啀璇昏〃缁撴瀯
-
- String keyfields = null;
+ List<String> keys = null;
try {
SpObserver.setDBtoInstance("_" + dto.dbid);
String taleName=dto.table;
if(dto.winType==18||dto.winType==19){
taleName=dto.table.split("\\|")[0];
}
- keyfields = gridService.getTableKeyFields(taleName);
- } finally {
- SpObserver.setDBtoInstance();
- }
- if (keyfields == null || "".equalsIgnoreCase(keyfields)) {
- //2鍙彇涓昏〃,涓讳粠琛ㄦ儏鍐�
- List<String> keys = null;
- try {
- SpObserver.setDBtoInstance("_" + dto.dbid);
- try {
- keys = gridService.getPrimaryKey(dto.table);
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- } finally {
- SpObserver.setDBtoInstance();
- }
- if (keys != null) {
+ keys = gridService.getPrimaryKey(taleName);
+ if(keys!=null) {
for (String str : keys) {
- if (dto.field == "")
+ if ("".equals(dto.field))
dto.field += str;
else
dto.field += ";" + str;
}
- keys = null;
}
- } else {
- String[] arry = keyfields.split(";");
- for (int i = 0; i < arry.length; i++) {
- if ("".equals(dto.field))
- dto.field += arry[i].toLowerCase();
- else
- dto.field += ";" + arry[i].toLowerCase();
-
- }
- arry = null;
+ dto.primeKey = dto.field;
+ }catch (SQLException e){
+ throw new ApplicationException(e.getMessage());
+ }finally {
+ SpObserver.setDBtoInstance();
}
- dto.primeKey = dto.field;
+
}
public void getTableName(int formid, String winType, TreeGridDTO dto) {
diff --git a/src/com/yc/app/v2/service/impl/BaseFormServiceImpl.java b/src/com/yc/app/v2/service/impl/BaseFormServiceImpl.java
index 9bc847c..b534663 100644
--- a/src/com/yc/app/v2/service/impl/BaseFormServiceImpl.java
+++ b/src/com/yc/app/v2/service/impl/BaseFormServiceImpl.java
@@ -1332,31 +1332,28 @@
public String orderBySql(GformEntity gformEntity, FormVOEntity formVOEntity, String orderbyStr) {
try {
- String keys = "";
+ String primaryStr = "";
String tableName = this.setTableName(gformEntity);
//----鍙栬〃鍏抽敭瀛楄缃�
- keys = gridService.getTableKeyFields(tableName);
+ //keys = gridService.getTableKeyFields(tableName);
//-----鍙栬〃涓婚敭
final List<String> primaryKey = gridService.getPrimaryKey(tableName);
- String primaryStr = "";
if (primaryKey != null && primaryKey.size() > 0) {
primaryStr = String.join(";", primaryKey);
}
//---鎶婅〃鍚嶏紝涓婚敭淇濆瓨杈撳嚭
formVOEntity.setPrimaryKey(primaryStr);
formVOEntity.setTableName(tableName);
- if (StringUtils.isBlank(keys)) {
+ if (StringUtils.isBlank(primaryStr)) {
//-----鍙�9801璁剧疆
String indexField = gformEntity.isGetHeadTabe() ? gformEntity.getIndex1() : gformEntity.getIndex2();
if (StringUtils.isNotBlank(indexField)) {//鍙�9801鐨勬帓搴忚缃�
- keys = indexField;
+ primaryStr = indexField;
- } else if (StringUtils.isBlank(keys)) {
- keys = primaryStr;
}
}
- if (StringUtils.isNotBlank(keys)) {
- String orderFields = keys.replaceAll(";", ",");
+ if (StringUtils.isNotBlank(primaryStr)) {
+ String orderFields = primaryStr.replaceAll(";", ",");
String newOrderFiled = "";
String[] sorts = orderFields.split(",");
int index = 0;
@@ -1376,9 +1373,9 @@
}
index++;
}
- keys = newOrderFiled;// keys.replace(";"," desc, ")+" desc";
+ primaryStr = newOrderFiled;// keys.replace(";"," desc, ")+" desc";
}
- return keys;
+ return primaryStr;
} catch (SQLException e) {
return "";
}
diff --git a/src/com/yc/ionic/build/BuildListPage.java b/src/com/yc/ionic/build/BuildListPage.java
index ba67ecc..87d0b75 100644
--- a/src/com/yc/ionic/build/BuildListPage.java
+++ b/src/com/yc/ionic/build/BuildListPage.java
@@ -3,7 +3,6 @@
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.yc.action.grid.GridUtils;
-import com.yc.exception.ApplicationException;
import com.yc.ionic.control.*;
import com.yc.service.grid.GridServiceIfc;
import com.yc.service.panel.GetDataTypeIfc;
@@ -102,9 +101,6 @@
}
private String getFirstField(String table,PageInfo page,String firstID) {
String field="";
- //1,鍏堣琛ㄥ叧閿姛鑳斤紝濡傛灉娌℃湁鐩稿叧鐨勫啀璇昏〃缁撴瀯
- String keyfields=gridService.getTableKeyFields(table);
- if(keyfields==null||"".equals(keyfields)){
//2,鍙彇涓昏〃,涓讳粠琛ㄦ儏鍐� 鍙�18,19绫诲瀷锛屼笁琛ㄦ殏鏃朵笉鑰冭檻
List<String> keys;
try {
@@ -118,18 +114,6 @@
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
- }
-
- keys=null;
- }else{
- String[] arry=keyfields.split(";");
- for(int i=0;i<arry.length;i++){
- if("".equals(field))
- field+=arry[i].toLowerCase();
- else
- field+=";"+arry[i].toLowerCase();
- }
- arry=null;
}
if(field==null||"".equalsIgnoreCase(field)){
field=firstID;
diff --git a/src/com/yc/service/grid/GridServiceImpl.java b/src/com/yc/service/grid/GridServiceImpl.java
index cee5806..0a6e70a 100644
--- a/src/com/yc/service/grid/GridServiceImpl.java
+++ b/src/com/yc/service/grid/GridServiceImpl.java
@@ -34,7 +34,9 @@
import javax.servlet.http.HttpSession;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
-import java.sql.*;
+import java.sql.CallableStatement;
+import java.sql.SQLException;
+import java.sql.Types;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Matcher;
@@ -2033,29 +2035,32 @@
}
/**
- * 鏍规嵁琛ㄥ悕鍙栧緱琛ㄤ富閿悕
+ * 鏍规嵁琛ㄥ悕鍙栧緱琛ㄤ富閿悕,鏌ユ壘琛ㄧ粨鏋勫拰9807璁剧疆
*
* @return int
* @throws SQLException
*/
+ @Override
public List<String> getPrimaryKey(String tableName) throws DataAccessException, SQLException {
-
- Connection con = null;
- ResultSet rs = null;
List<String> key = new ArrayList<String>();
- try {
- con = this.jdbcTemplate.getDataSource().getConnection();
- rs = con.getMetaData().getPrimaryKeys(null, null, tableName);
- while (rs.next()) {
- key.add(rs.getString(4));
- }
- } catch (Exception e) {
- throw new ApplicationException(e.getMessage());
- } finally {
- if (rs != null)
- rs.close();
- if (con != null)
- con.close();
+ String sql="declare @key varchar(1000),@tableName varchar(100)=?\n" +
+ " SELECT @key=stuff((SELECT';' + CONVERT(VARCHAR, column_name)\n" +
+ " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC\n" +
+ " INNER JOIN\n" +
+ " INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU\n" +
+ " ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND\n" +
+ " TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND \n" +
+ " KU.table_name=@tableName\n" +
+ " ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION\n" +
+ " FOR XML PATH ('')),1,1,'')\n" +
+ " if isnull(@key,'')=''\n" +
+ " begin\n" +
+ " select @key=keyfields from _systablekey where tableid=@tableName\n" +
+ " end\n" +
+ " select lower(@key)";
+ final String result = this.getSimpleJdbcTemplate().queryForObject(sql, String.class, tableName);
+ if(result!=null){
+ key=Arrays.asList(result.split(";"));
}
return key;
}
--
Gitblit v1.8.0