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

Introduction

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

Source

/*******************************************************************************
 * Copyright (c) 2012 Cho Hyun Jong.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 * 
 * Contributors:
 *     Cho Hyun Jong - initial API and implementation
 ******************************************************************************/
package com.hangum.tadpole.commons.sql.util;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

/**
 * <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 
     */
    private static final Pattern PATTERN_STATEMENT_QUERY = Pattern.compile("^SELECT.*|^SHOW.*|^DESCRIBE.*|^DESC.*",
            Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL);

    /**
     *   prestatment   ?,  execute executebatch  ? .
     * 
     * @param strSQL
     * @return
     */
    public static boolean isStatment(String strSQL) {
        if ((PATTERN_STATEMENT_QUERY.matcher(strSQL)).matches()) {
            return true;
        }

        return false;
    }

    /**
     * metadata     .
     * 
     * @param rs
     * @return index? 
     */
    public static HashMap<Integer, String> mataDataToMap(ResultSet rs) throws Exception {
        HashMap<Integer, String> map = new HashMap<Integer, String>();

        ResultSetMetaData rsm = rs.getMetaData();
        int columnCount = rsm.getColumnCount();
        for (int i = 0; i < columnCount; i++) {
            map.put(i, rsm.getColumnLabel(i + 1));
        }

        return map;
    }

    /**
     * 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 + "(");

        HashMap<Integer, String> mapTable = mataDataToMap(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();
    }

    /**
     *  ??  ??  ?  ?.
     * 
     * @param exeSQL
     * @return
     */
    public static String executeQuery(String exeSQL) {
        try {
            //         
            //         https://github.com/hangum/TadpoleForDBTools/issues/140  .
            //         TO DO  ? ??  ??..DB?    ?   . 

            //         // ? -- ? ? ?? ? .
            //         exeSQL = delComment(exeSQL, "--");

            //  ? // ? ? ?? ? .
            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 = StringUtils.trimToEmpty(exeSQL);

        } catch (Exception e) {
            logger.error("query execute", e);
        }

        return exeSQL;
    }

    /**
     * ? ?? .
     * 
     * @param sql
     * @param comment
     * @return
     */
    private static String delComment(String sql, String comment) {
        try {
            String[] linesSQL = sql.split("\n");
            if (linesSQL.length > 0) {
                StringBuffer tmpSQL = new StringBuffer();
                for (String string : linesSQL) {
                    int idx = string.indexOf(comment);//"--");
                    if (idx == 0) {
                    } else if (idx > 0) {
                        tmpSQL.append(string.substring(0, idx - 1)).append("\n");
                    } else {
                        tmpSQL.append(string).append("\n");
                    }
                }

                return tmpSQL.toString();
            }

        } catch (Exception e) {
            logger.error("execute sql", e);
        }

        return sql;
    }

    /**
     * db resource data  2000byte  ?? .
     * 
     * @param resource data
     * @return
     */
    public static String[] makeResourceDataArays(String resourceContent) {
        int cutsize = 1998;
        String[] tmpRetArryStr = new String[2000];
        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;
    }
}