예제 #1
0
  private String createStatements(boolean preview) {
    boolean insOnly = true;
    StringBuilder sbTotal = new StringBuilder();
    StringBuffer sbStatement = new StringBuffer();

    String rawText = new String(paneSyntax.getRawText());
    Pattern bracketPattern = Pattern.compile("P\\{.*?\\}|I\\{.*?\\}");

    Matcher matcher = bracketPattern.matcher(rawText);

    while (matcher.find()) {
      String s = matcher.group(0);
      if (matcher.group(0).charAt(0) == 'P') {
        insOnly = false;
      }
      s = s.substring(1, s.length() - 1);
    }

    int iRows = sheet.getRowCount() - 1;
    int iCol = sheet.getColCount();

    StringBuilder sbStmt = new StringBuilder();
    StringBuilder sbWhere = new StringBuilder();
    StringBuilder sbSet = new StringBuilder();
    List<String> listWheres = new ArrayList<String>();
    List<String> listSets = new ArrayList<String>();

    Row currRow;
    if (!insOnly) {
      for (int i = 0; i < (preview ? 1 : iRows); i++) {
        matcher.reset();
        sbStatement.setLength(0);
        while (matcher.find()) {
          String s = matcher.group(0);
          s = s.substring(2, s.length() - 1);
          Column c = sheet.getColumnByName(s);
          if (c != null) {
            matcher.appendReplacement(sbStatement, "");
            sbStatement.append(c.getValueAsString(i));
          }
        }
        matcher.appendTail(sbStatement);
        System.out.println(sbStatement.toString());
        sbTotal.append(sbStatement.toString() + "\n");
      }
      return sbTotal.toString();
    } else {
      sbTotal.setLength(0);

      matcher.reset();
      sbStatement.setLength(0);
      while (matcher.find()) {
        String s = matcher.group(0);
        matcher.appendReplacement(sbStatement, "");
        if (s.charAt(0) == 'I') {
          s = s.substring(2, s.length() - 1);
          Column c = sheet.getColumnByName(s);
          if (c != null) {
            sbStatement.append("(");
            for (int i = 0; i < sheet.getRowCount() - 1; i++) {

              sbStatement.append(c.getValueAsString(i) + ", ");
            }

            sbStatement.delete(sbStatement.length() - 2, sbStatement.length());
            sbStatement.append(")");
          }
        }
      }
      matcher.appendTail(sbStatement);

      return sbStatement.toString();
    }
  }
예제 #2
0
  private String createUpdateFromColumns(boolean preview) {
    String table = textField.getText().trim();
    while (table == null || table.equals("")) {
      table = JOptionPane.showInputDialog("Bitte erst einen Tabellennamen eingeben");
      if (table != null) {
        table.trim();
      }
    }
    textField.setText(table);
    int iRows = sheet.getRowCount() - 1;
    int iCol = sheet.getColCount();
    StringBuilder sbTotal = new StringBuilder();
    StringBuilder sbStmt = new StringBuilder();
    StringBuilder sbWhere = new StringBuilder();
    StringBuilder sbSet = new StringBuilder();
    List<String> listWheres = new ArrayList<String>();
    List<String> listSets = new ArrayList<String>();

    Row currRow;
    for (int i = 0; i < (preview ? 1 : iRows); i++) {
      listWheres.clear();
      listSets.clear();
      sbStmt.setLength(0);
      sbWhere.setLength(0);
      sbSet.setLength(0);
      sbStmt.append("UPDATE " + table + " ");

      currRow = xssfSheet.getRow(i);
      for (int j = 0; j < iCol; j++) {
        if (!sheet.getColumn(j).isIgnore()) {
          if (sheet.getColumn(j).isWhere()) {
            sbWhere.setLength(0);
            sbWhere.append(sheet.getColumn(j).getName() + " = ");
            Cell cell = xssfSheet.getRow(i).getCell(j);
            if (sheet.getColumn(j).getType() == Column.COL_TYPE_NUMBER) {
              sbWhere.append("" + sheet.getColumn(j).getNumber(i));
            } else if (sheet.getColumn(j).getType() == Column.COL_TYPE_STRING) {
              String strCleaned = cleanString(sheet.getColumn(j).getString(i));
              sbWhere.append("'" + strCleaned + "'");
            }
            listWheres.add(new String(sbWhere.toString()));
          } else {
            sbSet.setLength(0);
            sbSet.append(sheet.getColumn(j).getName() + " = ");
            Cell cell = xssfSheet.getRow(i).getCell(j);
            if (sheet.getColumn(j).getType() == Column.COL_TYPE_NUMBER) {
              sbSet.append("" + sheet.getColumn(j).getNumber(i));
            } else if (sheet.getColumn(j).getType() == Column.COL_TYPE_STRING) {
              String strCleaned = cleanString(sheet.getColumn(j).getString(i));
              sbSet.append("'" + strCleaned + "'");
            }
            listSets.add(new String(sbSet.toString()));
          }
        }
      }
      Iterator<String> iterator = listSets.iterator();
      if (iterator.hasNext()) {
        sbStmt.append("SET " + iterator.next());

        while (iterator.hasNext()) {
          sbStmt.append(", " + iterator.next());
        }
        sbStmt.append(" ");
      }
      iterator = listWheres.iterator();
      if (iterator.hasNext()) {
        sbStmt.append(" WHERE " + iterator.next());

        while (iterator.hasNext()) {
          sbStmt.append(" AND " + iterator.next());
        }
      }
      sbTotal.append(sbStmt.toString() + ";\n");
      System.out.println(sbStmt.toString());
    }
    return new String(sbTotal.toString());
  }