sqlparser.java

数据仓库工具

/**
    Copyright (C) 2002-2003  Together

    This library is free software; you can redistribute it and/or
    modify it under the terms of the GNU Lesser General Public
    License as published by the Free Software Foundation; either
    version 2.1 of the License, or (at your option) any later version.

    This library is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
    Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public
    License along with this library; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

*/
package org.webdocwf.util.loader;

import java.io.*;
import java.util.*;
import java.sql.Statement;

/**
 * Class is used for parsing sql statements.
 *
 * @author     Zoran Milakovic
 */
public class SqlParser {

  public static final String INSERT = "insert";
  public static final String UPDATE = "update";
  public static final String SELECT = "select";

  private static final String QUOTE_ESCAPE = "''";
  private static final String COMMA_ESCAPE = "~#####1~";


  private ArrayList binaryStreamObjectList = new ArrayList();

  public String tableName;

  public String whereStatement;

  public String sqlType;

  public String[] columnNames;

  public String[] columnValues;

  public String[] columnWhereNames;

  public String[] columnWhereValues;

  /**
   *Gets the tableName attribute of the SqlParser object
   *
   * @return    The tableName value
   * @since
   */
  public String getTableName() {
    return tableName;
  }

  /**
   * Gets the columnNames attribute of the SqlParser object
   *
   * @return    The columnNames value
   * @since
   */
  public String[] getColumnNames() {
    return columnNames;
  }

  public String[] getWhereColumnNames() {
    return columnWhereNames;
  }

  public String[] getWhereColumnValues() {
    return columnWhereValues;
  }

  public String[] getColumnValues() {
    return columnValues;
  }

  /**
   * Parse sql statement.
   *
   * @param sql defines SQL statement
   * @exception  Exception  Description of Exception
   * @since
   */
  public void parse(String sql) throws Exception {
    sql = sql.trim();
    tableName = null;
    columnNames = new String[0];
    columnValues = new String[0];
    columnWhereNames = new String[0];
    columnWhereValues = new String[0];
    whereStatement = null;
    sqlType = null;
    sql = sql.trim();

//replace comma(,) in values between quotes(')
    StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
    StringBuffer sb = new StringBuffer();
    boolean openParent1 = false;
    while (tokQuote.hasMoreTokens()) {
      String next = tokQuote.nextToken();
      if (openParent1)
        next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
      sb.append(next);
      if (next.equalsIgnoreCase("'")) {
        if (openParent1 == true)
          openParent1 = false;
        else
          openParent1 = true;
      }
    }
//END replacement
    sql = sb.toString();
    String upperSql = sql.toUpperCase();


//handle unsupported statements
    if (upperSql.startsWith("ALTER "))
      throw new Exception("ALTER TABLE statements are not supported.");
    if (upperSql.startsWith("DROP "))
      throw new Exception("DROP statements are not supported.");

//SELECT
    if ( upperSql.startsWith("SELECT ") ) {
      if (upperSql.lastIndexOf(" FROM ") == -1) {
        throw new Exception("Malformed SQL. Missing FROM statement.");
      }

      sqlType = SELECT;
      int fromPos = upperSql.lastIndexOf(" FROM ");
      int wherePos = upperSql.lastIndexOf(" WHERE ");
      if (wherePos == -1)
        tableName = sql.substring(fromPos + 6).trim();
      else
        tableName = sql.substring(fromPos + 6, wherePos).trim();

      Vector cols = new Vector();
      StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(7,
          fromPos), ",");

      while (tokenizer.hasMoreTokens()) {
        cols.add(tokenizer.nextToken().trim());
      }

      columnNames = new String[cols.size()];
      cols.copyInto(columnNames);
      if (wherePos != -1) {
        String strWhere = sql.substring(wherePos + 7);
        Vector whereCols = new Vector();
        Vector whereValues = new Vector();
        StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");

        while (tokenizerWhere.hasMoreTokens()) {
          String strToken = tokenizerWhere.nextToken();
          if (strToken.toLowerCase().indexOf(" and ") != -1) {
            String temp = strToken;
            int andPos = 0;
            out:
            do {
              andPos = temp.toLowerCase().indexOf(" and ");
              String strTokenAdd;
              if (andPos != -1)
                strTokenAdd = temp.substring(0, andPos).trim();
              else
                strTokenAdd = temp.trim();
              int delimiter2 = strTokenAdd.indexOf("=");
              if (delimiter2 != -1) {
                String valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
                valueAdd = Utils.handleQuotedString(valueAdd);
                whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
                valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                whereValues.add(valueAdd);
              }
              else {
                int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                whereValues.add(null);
              }
              temp = temp.substring(andPos + 5);
              if (temp.toLowerCase().indexOf(" and ") == -1) {
                strTokenAdd = temp.trim();
                int delimiter4 = strTokenAdd.indexOf("=");
                if (delimiter4 != -1) {
                  String valueAdd = strTokenAdd.substring(delimiter4 + 1).trim();
                  valueAdd = Utils.handleQuotedString(valueAdd);
                  whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
                  valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                  valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                  whereValues.add(valueAdd);
                }
                else {
                  int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                  whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                  whereValues.add(null);
                }
                break out;
              }

            }
            while (true);

          }
          else {
            int delimiter = strToken.indexOf("=");
            if (delimiter != -1) {
              String value = strToken.substring(delimiter + 1).trim();
              value = Utils.handleQuotedString(value);
              whereCols.add(strToken.substring(0, delimiter).trim());
              value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
              value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
              whereValues.add(value);
            }
            else {
              int delimiter1 = strToken.toLowerCase().indexOf(" is ");
              whereCols.add(strToken.substring(0, delimiter1).trim());
              whereValues.add(null);
            }
          }
        }
        columnWhereNames = new String[whereCols.size()];
        columnWhereValues = new String[whereValues.size()];
        whereCols.copyInto(columnWhereNames);
        whereValues.copyInto(columnWhereValues);
      }
    }
//INSERT
    if ( upperSql.startsWith("INSERT ") ) {
      if (upperSql.lastIndexOf(" VALUES") == -1) {
          throw new Exception("Malformed SQL. Missing VALUES statement.");
        }
        sqlType = INSERT;
        int intoPos = 0;
        if (upperSql.indexOf(" INTO ") != -1)
          intoPos = upperSql.indexOf(" INTO ") + 6;
        else
          intoPos = upperSql.indexOf("INSERT ") + 7;
        int bracketPos = upperSql.indexOf("(");
        int lastBracketPos = upperSql.indexOf(")");
        tableName = sql.substring(intoPos, bracketPos).trim();

        Vector cols = new Vector();
        StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(
            bracketPos + 1, lastBracketPos), ",");
        while (tokenizer.hasMoreTokens()) {
          cols.add(tokenizer.nextToken().trim());
        }
        columnNames = new String[cols.size()];
        cols.copyInto(columnNames);

        int valuesPos = upperSql.indexOf("VALUES");
        String endStatement = sql.substring(valuesPos + 6).trim();
        bracketPos = endStatement.indexOf("(");
        lastBracketPos = endStatement.lastIndexOf(")");
        Vector values = new Vector();
        StringTokenizer tokenizer2 = new StringTokenizer(endStatement.substring(
            bracketPos + 1, lastBracketPos), ",");
        while (tokenizer2.hasMoreTokens()) {
          String value = tokenizer2.nextToken().trim();
          value = Utils.handleQuotedString(value);
          value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
          value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
          values.add(value);
        }
        columnValues = new String[values.size()];
        values.copyInto(columnValues);
    }


//UPDATE
    if ( upperSql.startsWith("UPDATE ") ) {
      if (upperSql.lastIndexOf(" SET ") == -1)
            throw new Exception("Malformed SQL. Missing SET statement.");
          sqlType = UPDATE;
          int updatePos = upperSql.indexOf("UPDATE");
          int setPos = upperSql.indexOf(" SET ");
          int equalPos = upperSql.indexOf("=");
          int wherePos = upperSql.indexOf(" WHERE ");
          tableName = sql.substring(updatePos + 6, setPos).trim();

          String setString = "";
          if (wherePos != -1)
            setString = sql.substring(setPos + 5, wherePos);
          else
            setString = sql.substring(setPos + 5, sql.length());
          StringTokenizer tokenizerSet = new StringTokenizer(setString, ",");
          Vector setNames = new Vector();
          Vector setValues = new Vector();

          while (tokenizerSet.hasMoreTokens()) {
            String strToken = tokenizerSet.nextToken();
            int delimiter = strToken.indexOf("=");
            setNames.add(strToken.substring(0, delimiter).trim());
            String value = strToken.substring(delimiter + 1).trim();
            value = Utils.handleQuotedString(value);
            value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
            value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
            setValues.add(value);
          }

          columnNames = new String[setNames.size()];
          columnValues = new String[setValues.size()];
          setNames.copyInto(columnNames);
          setValues.copyInto(columnValues);
          if (wherePos != -1) {
            String strWhere = sql.substring(wherePos + 6).trim();
            Vector whereCols = new Vector();
            Vector whereValues = new Vector();
            StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");

            while (tokenizerWhere.hasMoreTokens()) {
              String strToken = tokenizerWhere.nextToken();
              if (strToken.toLowerCase().indexOf(" and ") != -1) {
                String temp = strToken;
                int andPos = 0;
                out:
                do {
                  andPos = temp.toLowerCase().indexOf(" and ");
                  String strTokenAdd;
                  if (andPos != -1)
                    strTokenAdd = temp.substring(0, andPos).trim();
                  else
                    strTokenAdd = temp.trim();
                  int delimiter2 = strTokenAdd.indexOf("=");
                  if (delimiter2 != -1) {
                    String valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
                    valueAdd = Utils.handleQuotedString(valueAdd);
                    whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
                    valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                    valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                    whereValues.add(valueAdd);
                  }
                  else {
                    int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                    whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                    whereValues.add(null);
                  }
                  temp = temp.substring(andPos + 5);
                  if (temp.toLowerCase().indexOf(" and ") == -1) {
                    strTokenAdd = temp.trim();
                    int delimiter4 = strTokenAdd.indexOf("=");
                    if (delimiter4 != -1) {
                      String valueAdd = strTokenAdd.substring(delimiter4 + 1).
                          trim();
                      valueAdd = Utils.handleQuotedString(valueAdd);
                      whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
                      valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                      valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                      whereValues.add(valueAdd);
                    }
                    else {
                      int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                      whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                      whereValues.add(null);
                    }
                    break out;
                  }

                }
                while (true);

              }
              else {
                int delimiter = strToken.indexOf("=");
                if (delimiter != -1) {
                  String value = strToken.substring(delimiter + 1).trim();
                  value = Utils.handleQuotedString(value);
                  whereCols.add(strToken.substring(0, delimiter).trim());
                  value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
                  value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
                  whereValues.add(value);
                }
                else {
                  int delimiter1 = strToken.toLowerCase().indexOf(" is ");
                  whereCols.add(strToken.substring(0, delimiter1).trim());
                  whereValues.add(null);
                }
              }
            }
            columnWhereNames = new String[whereCols.size()];
            columnWhereValues = new String[whereValues.size()];
            whereCols.copyInto(columnWhereNames);
            whereValues.copyInto(columnWhereValues);
          }
    }

  }


}