Java tutorial
/******************************************************************************* * Copyright (c) 2013 hangum. * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Lesser Public License v2.1 * which accompanies this distribution, and is available at * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html * * Contributors: * hangum - initial API and implementation ******************************************************************************/ package com.hangum.tadpole.sql.util; import java.io.StringReader; import java.sql.ResultSet; import java.util.Map; import java.util.regex.Pattern; import net.sf.jsqlparser.parser.CCJSqlParserManager; import net.sf.jsqlparser.statement.Statement; import net.sf.jsqlparser.statement.select.Select; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import com.hangum.tadpold.commons.libs.core.define.PublicTadpoleDefine; import com.hangum.tadpold.commons.libs.core.define.PublicTadpoleDefine.DB_ACTION; import com.hangum.tadpole.db.metadata.TadpoleMetaData; import com.hangum.tadpole.engine.manager.TadpoleSQLManager; import com.hangum.tadpole.sql.dao.system.UserDBDAO; import com.hangum.tadpole.sql.util.resultset.ResultSetUtils; /** * <pre> * java.sql.ResultSet ResultSetMeta TableViewer Util * * resource?? data . * </pre> * * @author hangum * */ public class SQLUtil { /** * Logger for this class */ private static final Logger logger = Logger.getLogger(SQLUtil.class); /** * pattern statement * * <PRE> * CHECK MYSQL? CHECK TABLE VIEW_TABLE_NAME; VIEW? . * PRAGMA sqlite? . * </PRE> */ private static final String PATTERN_STATEMENT = "^SELECT.*|^EXPLAIN.*|^SHOW.*|^DESCRIBE.*|^DESC.*|^CHECK.*|^PRAGMA.*|^WITH.*"; private static final Pattern PATTERN_STATEMENT_QUERY = Pattern.compile(PATTERN_STATEMENT, Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL); private static final String PATTERN_EXECUTE = "^GRANT.*|^REVOKE.*|^ALTER.*|^DROP.*|^RENAME.*|^TRUNCATE.*|^COMMENT.*"; private static final String PATTERN_EXECUTE_UPDATE = "^INSERT.*|^UPDATE.*|^DELETET.*|^MERGE.*|^COMMIT.*|^ROLLBACK.*|^SAVEPOINT.*"; private static final String PATTERN_EXECUTE_CREATE = "^CREATE.*|^DECLARE.*"; private static final Pattern PATTERN_EXECUTE_QUERY = Pattern.compile( PATTERN_EXECUTE /*+ PATTERN_EXECUTE_UPDATE*/ + "|" + PATTERN_EXECUTE_CREATE, Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL); private static final String PATTERN_COMMENT = "/\\*([^*]|[\r\n]|(\\*+([^*/]|[\r\n])))*\\*+/"; private static final String PATTERN_COMMENT2 = "(--.*)|(//.*)"; /** ? sql ? */ private static final String[] NOT_ALLOWED_SQL = { /* MSSQL- USE DATABASE */ "USE" }; /** * remove comment * * @param strSQL * @return */ public static String removeComment(String strSQL) { if (null == strSQL) return ""; String retStr = strSQL.replaceAll(PATTERN_COMMENT, ""); retStr = retStr.replaceAll(PATTERN_COMMENT2, ""); return retStr; } /** * ? ?. * ? ?? ... -- / ** * / / * * / * * @param strSQL * @return */ public static boolean isNotAllowed(String strSQL) { boolean isRet = false; strSQL = removeComment(strSQL); String cmpSql = StringUtils.trim(strSQL); for (String strNAllSQL : NOT_ALLOWED_SQL) { if (StringUtils.startsWith(cmpSql.toLowerCase(), strNAllSQL.toLowerCase())) { return true; } } return isRet; } /** * execute query * * @param strSQL * @return */ public static boolean isExecute(String strSQL) { strSQL = removeComment(strSQL); if ((PATTERN_EXECUTE_QUERY.matcher(strSQL)).matches()) { return true; } return false; } /** * ? ? <code>PATTERN_STATEMENT</code>?? * * @param strSQL * @return */ public static boolean isStatement(String strSQL) { strSQL = removeComment(strSQL); if ((PATTERN_STATEMENT_QUERY.matcher(strSQL)).matches()) { return true; } else { // add issue https://github.com/JSQLParser/JSqlParser/issues/31 try { // ?? ?? . ?? ? false . // ? ? ? ? ? ?? . // if(!isEnglish(strSQL)) return false; // // CCJSqlParserManager parserManager = new CCJSqlParserManager(); // Statement statement = parserManager.parse(new StringReader(strSQL)); // if(statement instanceof Select) return true; } catch (Exception e) { logger.error("SQL Parser Exception.\n sql is [" + strSQL + "]"); } return false; } // return false; } // /** // * ?? . // * @param strValue // * @return // */ // public static boolean isEnglish(String strValue) { // if(strValue == null || strValue.length() == 0) return false; // // char charVal = strValue.charAt(0); // if(charVal >= 65 && charVal <= 90) return true; // ? // if(charVal >= 97 && charVal <= 122) return true; // ? // // return false; // } /** * INSERT ? ?. * * @param tableName * @param rs * @return * @throws Exception */ public static String makeInsertStatment(String tableName, ResultSet rs) throws Exception { StringBuffer result = new StringBuffer("INSERT INTO " + tableName + "("); Map<Integer, String> mapTable = ResultSetUtils.getColumnName(rs); for (int i = 0; i < mapTable.size(); i++) { if (i != (mapTable.size() - 1)) result.append(mapTable.get(i) + ","); else result.append(mapTable.get(i)); } result.append(") VALUES("); for (int i = 0; i < mapTable.size(); i++) { if (i != (mapTable.size() - 1)) result.append("?,"); else result.append('?'); } result.append(')'); if (logger.isDebugEnabled()) logger.debug("[make insert statment is " + result.toString()); return result.toString(); } /** * jdbc? . * * @param exeSQL * @return */ public static String sqlExecutable(String exeSQL) { // tmpStrSelText = UnicodeUtils.getUnicode(tmpStrSelText); try { // // https://github.com/hangum/TadpoleForDBTools/issues/140 . // TO DO ? ?? ??..DB? ? . // ? // ? ? ?? ? . /* * mysql? ?? , -- ? ? -- ? ?? ?. --comment ? ? ?? .( (mssql, oralce, pgsql)? ? ??) * , ?? ?? ?? ?? . - 2013.11.11- (hangum) */ // exeSQL = delComment(exeSQL, "--"); // // exeSQL = StringUtils.replace(exeSQL, "\r", " "); // exeSQL = StringUtils.replace(exeSQL, "\n", " "); // exeSQL = StringUtils.replace(exeSQL, Define.LINE_SEPARATOR, " "); // exeSQL = exeSQL.replaceAll("(\r\n|\n|\r)", " "); // ? ? exeSQL = removeComment(exeSQL); exeSQL = StringUtils.trimToEmpty(exeSQL); exeSQL = StringUtils.removeEnd(exeSQL, PublicTadpoleDefine.SQL_DELIMITER); } catch (Exception e) { logger.error("query execute", e); } return exeSQL.trim(); } /** * ? Table name? . * * @param userDB * @param tableName * @return */ public static String makeIdentifierName(UserDBDAO userDB, String tableName) { boolean isChanged = false; String retStr = tableName; TadpoleMetaData tmd = TadpoleSQLManager.getDbMetadata(userDB); switch (tmd.getSTORE_TYPE()) { // case NONE: // retStr = tableName; // break; case BLANK: if (tableName.matches(".*\\s.*")) { isChanged = true; retStr = makeFullyTableName(tableName, tmd.getIdentifierQuoteString()); } break; case LOWCASE_BLANK: if (tableName.matches(".*[a-z\\s].*")) { isChanged = true; retStr = makeFullyTableName(tableName, tmd.getIdentifierQuoteString()); } break; case UPPERCASE_BLANK: if (tableName.matches(".*[A-Z\\s].*")) { isChanged = true; retStr = makeFullyTableName(tableName, tmd.getIdentifierQuoteString()); } break; } // Is keywords? // schema.tableName if (!isChanged) { String[] arryRetStr = StringUtils.split(retStr, "."); if (arryRetStr.length == 1) { if (StringUtils.containsIgnoreCase("," + tmd.getKeywords() + ",", "," + arryRetStr[0] + ",")) { retStr = tmd.getIdentifierQuoteString() + retStr + tmd.getIdentifierQuoteString(); } } else if (arryRetStr.length > 1) { if (StringUtils.containsIgnoreCase("," + tmd.getKeywords() + ",", "," + arryRetStr[1] + ",")) { retStr = tmd.getIdentifierQuoteString() + retStr + tmd.getIdentifierQuoteString(); } } } // if(logger.isDebugEnabled()) logger.debug("[tmd.getSTORE_TYPE()]" + tmd.getSTORE_TYPE() + "[original]" + tableName + "[retStr = ]" + retStr); return retStr; } private static String makeFullyTableName(String tableName, String strIdentifier) { String retStr = ""; for (String chunk : StringUtils.split(tableName, '.')) { retStr += strIdentifier + chunk + strIdentifier + "."; } retStr = StringUtils.removeEnd(retStr, "."); return retStr; } /** * db resource data 2000byte ?? . * * @param resource data * @return */ public static String[] makeResourceDataArays(String resourceContent) { int cutsize = 1998; String[] tmpRetArryStr = new String[2000]; resourceContent = resourceContent == null ? "" : resourceContent; byte[] byteSqlText = resourceContent.getBytes(); int isEndTextHangul = 0; int workCnt = 0; while (byteSqlText.length > cutsize) { isEndTextHangul = 0; for (int i = 0; i < cutsize; i++) { if (byteSqlText[i] < 0) isEndTextHangul++; } if (isEndTextHangul % 2 != 0) { tmpRetArryStr[workCnt] = new String(byteSqlText, 0, cutsize + 1); byteSqlText = new String(byteSqlText, cutsize + 1, byteSqlText.length - (cutsize + 1)).getBytes(); } else { tmpRetArryStr[workCnt] = new String(byteSqlText, 0, cutsize); byteSqlText = new String(byteSqlText, cutsize, byteSqlText.length - cutsize).getBytes(); } workCnt++; } tmpRetArryStr[workCnt] = new String(byteSqlText); // ? String[] returnDataArry = new String[workCnt + 1]; for (int i = 0; i <= workCnt; i++) { returnDataArry[i] = tmpRetArryStr[i]; } return returnDataArry; } /** * ?? . * * https://github.com/hangum/TadpoleForDBTools/issues/466 * * @param dbAction * @return */ public static boolean isSELECTEditor(DB_ACTION dbAction) { if (dbAction == DB_ACTION.TABLES || dbAction == DB_ACTION.VIEWS || dbAction == DB_ACTION.SYNONYM || dbAction == DB_ACTION.INDEXES) { return true; } return false; } }