com.hangum.tadpole.sql.util.SQLUtil.java Source code

Java tutorial

Introduction

Here is the source code for com.hangum.tadpole.sql.util.SQLUtil.java

Source

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