Code example for Statement

Methods: close, execute, getResultSet, getUpdateCount

0
 
        statement = connection.createStatement();
        try { 
          executeStatement(query);
        } finally { 
          statement.close();
          statement = null;
        } 
      } finally { 
        db.close();
        db = null;
      } 
    } catch (OrmException err) {
      out.println("fatal: Cannot open connection: " + err.getMessage());
 
    } catch (SQLException err) {
      out.println("fatal: Cannot open connection: " + err.getMessage());
    } finally { 
      out.flush();
    } 
  } 
 
  private void readEvalPrintLoop() { 
    final StringBuilder buffer = new StringBuilder();
    boolean executed = false;
    for (;;) { 
      if (outputFormat == OutputFormat.PRETTY) {
        print(buffer.length() == 0 || executed ? "gerrit> " : "     -> ");
      } 
      String line = readLine();
      if (line == null) {
        return; 
      } 
 
      if (line.startsWith("\\")) {
        // Shell command, check the various cases we recognize 
        // 
        line = line.substring(1);
        if (line.equals("h") || line.equals("?")) {
          showHelp(); 
 
        } else if (line.equals("q")) {
          if (outputFormat == OutputFormat.PRETTY) {
            println("Bye"); 
          } 
          return; 
 
        } else if (line.equals("r")) {
          buffer.setLength(0);
          executed = false;
 
        } else if (line.equals("p")) {
          println(buffer.toString());
 
        } else if (line.equals("g")) {
          if (buffer.length() > 0) {
            executeStatement(buffer.toString());
            executed = true;
          } 
 
        } else if (line.equals("d")) {
          listTables(); 
 
        } else if (line.startsWith("d ")) {
          showTable(line.substring(2).trim());
 
        } else { 
          final String msg = "'\\" + line + "' not supported";
          switch (outputFormat) {
            case JSON: {
              final JsonObject err = new JsonObject();
              err.addProperty("type", "error");
              err.addProperty("message", msg);
              println(err.toString());
              break; 
            } 
            case PRETTY:
            default: 
              println("ERROR: " + msg);
              println(""); 
              showHelp(); 
              break; 
          } 
        } 
        continue; 
      } 
 
      if (executed) {
        buffer.setLength(0);
        executed = false;
      } 
      if (buffer.length() > 0) {
        buffer.append('\n');
      } 
      buffer.append(line);
 
      if (buffer.length() > 0 && buffer.charAt(buffer.length() - 1) == ';') {
        executeStatement(buffer.toString());
        executed = true;
      } 
    } 
  } 
 
  private void listTables() { 
    final DatabaseMetaData meta;
    try { 
      meta = connection.getMetaData();
    } catch (SQLException e) {
      error(e);
      return; 
    } 
 
    try { 
      final String[] types = {"TABLE", "VIEW"};
      ResultSet rs = meta.getTables(null, null, null, types);
      try { 
        if (outputFormat == OutputFormat.PRETTY) {
          println("                     List of relations"); 
        } 
        showResultSet(rs, false, //
            Identity.create(rs, "TABLE_SCHEM"), //
            Identity.create(rs, "TABLE_NAME"), //
            Identity.create(rs, "TABLE_TYPE"));
      } finally { 
        rs.close();
      } 
    } catch (SQLException e) {
      error(e);
    } 
 
    println(""); 
  } 
 
  private void showTable(String tableName) {
    final DatabaseMetaData meta;
    try { 
      meta = connection.getMetaData();
 
      if (meta.storesUpperCaseIdentifiers()) {
        tableName = tableName.toUpperCase();
      } else if (meta.storesLowerCaseIdentifiers()) {
        tableName = tableName.toLowerCase();
      } 
    } catch (SQLException e) {
      error(e);
      return; 
    } 
 
    try { 
      ResultSet rs = meta.getColumns(null, null, tableName, null);
      try { 
        if (!rs.next()) {
          throw new SQLException("Table " + tableName + " not found");
        } 
 
        if (outputFormat == OutputFormat.PRETTY) {
          println("                     Table " + tableName);
        } 
        showResultSet(rs, true, //
            Identity.create(rs, "COLUMN_NAME"), //
            new Function("TYPE") { 
              @Override 
              String apply(final ResultSet rs) throws SQLException {
                String type = rs.getString("TYPE_NAME");
                switch (rs.getInt("DATA_TYPE")) {
                  case java.sql.Types.CHAR:
                  case java.sql.Types.VARCHAR:
                    type += "(" + rs.getInt("COLUMN_SIZE") + ")";
                    break; 
                } 
 
                String def = rs.getString("COLUMN_DEF");
                if (def != null && !def.isEmpty()) {
                  type += " DEFAULT " + def;
                } 
 
                int nullable = rs.getInt("NULLABLE");
                if (nullable == DatabaseMetaData.columnNoNulls) {
                  type += " NOT NULL";
                } 
                return type;
              } 
            }); 
      } finally { 
        rs.close();
      } 
    } catch (SQLException e) {
      error(e);
      return; 
    } 
 
    try { 
      ResultSet rs = meta.getIndexInfo(null, null, tableName, false, true);
      try { 
        Map<String, IndexInfo> indexes = new TreeMap<String, IndexInfo>();
        while (rs.next()) {
          final String indexName = rs.getString("INDEX_NAME");
          IndexInfo def = indexes.get(indexName);
          if (def == null) {
            def = new IndexInfo();
            def.name = indexName;
            indexes.put(indexName, def);
          } 
 
          if (!rs.getBoolean("NON_UNIQUE")) {
            def.unique = true;
          } 
 
          final int pos = rs.getInt("ORDINAL_POSITION");
          final String col = rs.getString("COLUMN_NAME");
          String desc = rs.getString("ASC_OR_DESC");
          if ("D".equals(desc)) {
            desc = " DESC";
          } else { 
            desc = "";
          } 
          def.addColumn(pos, col + desc);
 
          String filter = rs.getString("FILTER_CONDITION");
          if (filter != null && !filter.isEmpty()) {
            def.filter.append(filter);
          } 
        } 
 
        if (outputFormat == OutputFormat.PRETTY) {
          println(""); 
          println("Indexes on " + tableName + ":");
          for (IndexInfo def : indexes.values()) {
            println("  " + def);
          } 
        } 
      } finally { 
        rs.close();
      } 
    } catch (SQLException e) {
      error(e);
      return; 
    } 
 
    println(""); 
  } 
 
  private void executeStatement(final String sql) {
    final long start = System.currentTimeMillis();
    final boolean hasResultSet;
    try { 
      hasResultSet = statement.execute(sql);
    } catch (SQLException e) {
      error(e);
      return; 
    } 
 
    try { 
      if (hasResultSet) {
        final ResultSet rs = statement.getResultSet();
        try { 
          final int rowCount = showResultSet(rs, false);
          final long ms = System.currentTimeMillis() - start;
          switch (outputFormat) {
            case JSON: {
              final JsonObject tail = new JsonObject();
              tail.addProperty("type", "query-stats");
              tail.addProperty("rowCount", rowCount);
              tail.addProperty("runTimeMilliseconds", ms);
              println(tail.toString());
              break; 
            } 
 
            case PRETTY:
            default: 
              println("(" + rowCount + (rowCount == 1 ? " row" : " rows") //
                  + "; " + ms + " ms)");
              break; 
          } 
        } finally { 
          rs.close();
        } 
 
      } else { 
        final int updateCount = statement.getUpdateCount();
        final long ms = System.currentTimeMillis() - start;
        switch (outputFormat) {
          case JSON: {
            final JsonObject tail = new JsonObject();
            tail.addProperty("type", "update-stats");