优化主键处理避免更新删除时条件不带主键

fs-danaus

2022-05-13 2e60c6034fdfbcfbc4d7a91f0a84926688a50009

 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;
    }