[JAVA] A tool for hitting arbitrary SQL using JDBC

• The revised version is described later in this article.

DB.java

import java.sql.*;

public class DB {
  public static void main(String[] args) throws Exception {
    if (args.length != 1 || "".equals(args[0].trim())) {
      System.err.println("Error: <SQL>");
      System.exit(1);
    }
    String sql = args[0];
    Connection conn = null;
    try {
      String url = "jdbc: ... ";
      conn = DriverManager.getConnection(url);
      Statement stat = conn.createStatement();
      ResultSet result = stat.executeQuery(sql);
      while (result.next()) {
        System.out.println(createRowString(result));
      }
    } finally {
      if (conn != null) conn.close();
    }
  }

  private static String createRowString(ResultSet result) {
    StringBuilder buf = new StringBuilder();
    for (int i=1 ;; i++) {
      try {
        String s = result.getString(i);
        buf.append(s);
        buf.append('\t');
      } catch(SQLException e) {
        break;
      }
    }
    return buf.toString();
  }
}

For example, if url is JavaDB (Apache Derby), it is the following character string.

jdbc:derby:/path/to/database/dir;create=true;user=<username>;password=<password>

You can use it to execute SQL as follows. The results are output tab-delimited.

$ javac -cp .:<Other required jar file paths> DB.java
$ java -cp .:<Other required jar file paths> DB "SELECT * FROM xxtable"
aaa  AAA  111
bbb  BBB  222
ccc  CCC  333

When outputting an arbitrary column using * in the SELECT statement, I did my best to create a method called createRowString () because I do not know how many columns will appear, but I wonder if there is a better way ...

2016/12/28 postscript saka1029 commented on how to get the number of columns, so I edited it a little.

2017/01/03 Edit The UPDATE and DELETE statements could not be executed, so I made it possible.

DB.java

import java.sql.*;

public class DB {
  private static final  String URL = ... ;
  public static void main(String[] args) throws Exception {
    if (args.length != 1 || "".equals(args[0].trim())) {
      System.err.println("Error: <SQL>");
      System.exit(1);
    }
    String sql = args[0].trim();
    Connection conn = null;
    try {
      conn = DriverManager.getConnection(URL);
      Statement stat = conn.createStatement();
      if (isSelectStatement(sql)) {
        doSelect(stat, sql);
      } else {
        doUpdate(stat, sql);
      }
    } finally {
      if (conn != null) conn.close();
    }
  }

  private static boolean isSelectStatement(String sql) {
    String type = sql.split(" ")[0].toLowerCase();
    return "select".equals(type);
  }

  private static void doSelect(Statement stat, String sql) throws Exception {
    ResultSet result = stat.executeQuery(sql);
    while (result.next()) {
      System.out.println(createRowString(result));
    }
  }

  private static void doUpdate(Statement stat, String sql) throws Exception {
    int rowCount = stat.executeUpdate(sql);
    System.out.println("rows = " + rowCount);
  }

  private static String createRowString(ResultSet result) throws SQLException {
    ResultSetMetaData metadata = result.getMetaData();
    int columnCount = metadata.getColumnCount();
    StringBuilder buf = new StringBuilder();
    for (int i=1 ; i<=columnCount ; i++) {
      String s = result.getString(i);
      buf.append(s);
      buf.append('\t');
    }
    return buf.toString().trim();
  }
}