Le attività di Antonio Daniele

English

Snippet JDBC MySQL

In this document I present some Java snippets I used during my practical portion of the Databases exam.
It consisted of solving some requests with SQL on the MySQL DBMS, by using JDBC.

I’ve used this method to print to screen the contents of a ResultSet’s instance, in a semi-tabular form:

public static String resultSet2String(ResultSet s) throws SQLException {
  ResultSetMetaData s = m.getMetaData();
  int nCols = m.getColumnCount();
  List<String> currRow = new ArrayList<>();

  for (int i = 0; i < nCols; i++) {
      currRow.add(m.getColumnLabel(i + 1)); // Yep, the first column is 1, not 0
  }

  /*
   * This part only makes the header
   */
  StringBuilder bld = new StringBuilder("=".repeat(30) + "\n");
  bld.append("| " + String.join(" | ", currRow) + " |\n");
  bld.append("=".repeat(30) + "\n");

  while (s.next()) {
      currRow = new ArrayList<>();
      for (int i = 0; i < nCols; i++) {
           // With 'getObject', you may ignore the particular type of data, simplifying things
           // Yep, again, the first column is at position 1, not 0
           Object o = s.getObject(i + 1);
           String oStr = o == null ? "NULL" : o.toString();
           currRow.add(oStr);
      }
      // Construct the row
      bld.append("| " + String.join(" | ", currRow) + " |\n");
  }

  // Despite the table not being perfectly aligned with characters,
  // it is still readable, which is good, if you need to be quick
  // about writing this method.

  return bld.toString();
}

To verify the correctness of the method, I recommend to print the results of a query like:

SELECT 10, 100, 1000 UNION ALL 1000, 100, 10

I’ve used this method to execute external SQL scripts, this is useful even to perform stdout/stderr redirection on external files, for futher analysises:

public static void runMySQLScript(String username, String userpassw, String stdinPath,
  String stdoutPath, String stderrPath) throws IOException, InterruptedException {

  // Build the process to invoke, the first parameter is a binary path.
  // If the binary is in the PATH environment variable, its name is sufficient,
  // otherwise, the full path is expected
  ProcessBuilder pb = new ProcessBuilder("mysql", "-v", "-v", "-v", "-u", username, "-p" + userpassw);
  pb.redirectInput(new File(stdinPath));

  // This is equivalent to >/dev/null (or >NUL in Windows) if stdoutPath == null
  pb.redirectOutput(stdoutPath == null ? ProcessBuilder.Redirect.DISCARD : new File(stdoutPath));

  // This is equivalent to 2>/dev/null (or 2>NUL in Windows) if stderrPath == null
  pb.redirectError(stderrPath == null ? ProcessBuilder.Redirect.DISCARD : new File(stderrPath));

  // Invoke the process and wait for it to complete
  Process p = pb.start();
  int exitCode = p.waitFor();

  // Throw exception if exit code is not 0
  // MySQL/MariaDB returns non-zero in case of errors
  if (exitCode != 0) {
      throw new IllegalStateException("Error during the execution of script \"" + stdinPath +
          "\", error code " + exitCode);
  }
}

This snippet is the equivalent of executing, on the command line:

mysql -v -v -v -u USER_NAME -pUSER_PASSW < STDIN_FILE > STDOUT_FILE 2> STDERR_FILE

Please note that the three -v options enable the so-called “table format”, equivalent to the interactive mode, in terms of verbosity.

Italiano

Snippet JDBC MySQL

In questo documento presento alcuni snippet Java che ho utilizzato nell’esame pratico di Basi di Dati.
Esso consisteva nella risoluzione di una serie di quesiti con SQL sul DBMS MySQL, sfruttando JDBC.

Ho usato questo metodo per stampare a schermo i contenuti di un’istanza di ResultSet, in formato semi-tabellare:

public static String resultSet2String(ResultSet s) throws SQLException {
  ResultSetMetaData s = m.getMetaData();
  int nCols = m.getColumnCount();
  List<String> currRow = new ArrayList<>();

  for (int i = 0; i < nCols; i++) {
      currRow.add(m.getColumnLabel(i + 1)); // Yep, the first column is 1, not 0
  }

  /*
   * This part only makes the header
   */
  StringBuilder bld = new StringBuilder("=".repeat(30) + "\n");
  bld.append("| " + String.join(" | ", currRow) + " |\n");
  bld.append("=".repeat(30) + "\n");

  while (s.next()) {
      currRow = new ArrayList<>();
      for (int i = 0; i < nCols; i++) {
           // With 'getObject', you may ignore the particular type of data, simplifying things
           // Yep, again, the first column is at position 1, not 0
           Object o = s.getObject(i + 1);
           String oStr = o == null ? "NULL" : o.toString();
           currRow.add(oStr);
      }
      // Construct the row
      bld.append("| " + String.join(" | ", currRow) + " |\n");
  }

  // Despite the table not being perfectly aligned with characters,
  // it is still readable, which is good, if you need to be quick
  // about writing this method.

  return bld.toString();
}

Per verificare la corretta scrittura del metodo, raccomando di stampare i risultati di una query come:

SELECT 10, 100, 1000 UNION ALL 1000, 100, 10

Ho usato questo metodo per eseguire script SQL esterni, questo è utile anche per fare redirezione di stdout/stderr su file esterni, per ulteriori analisi:

public static void runMySQLScript(String username, String userpassw, String stdinPath,
  String stdoutPath, String stderrPath) throws IOException, InterruptedException {

  // Build the process to invoke, the first parameter is a binary path.
  // If the binary is in the PATH environment variable, its name is sufficient,
  // otherwise, the full path is expected
  ProcessBuilder pb = new ProcessBuilder("mysql", "-v", "-v", "-v", "-u", username, "-p" + userpassw);
  pb.redirectInput(new File(stdinPath));

  // This is equivalent to >/dev/null (or >NUL in Windows) if stdoutPath == null
  pb.redirectOutput(stdoutPath == null ? ProcessBuilder.Redirect.DISCARD : new File(stdoutPath));

  // This is equivalent to 2>/dev/null (or 2>NUL in Windows) if stderrPath == null
  pb.redirectError(stderrPath == null ? ProcessBuilder.Redirect.DISCARD : new File(stderrPath));

  // Invoke the process and wait for it to complete
  Process p = pb.start();
  int exitCode = p.waitFor();

  // Throw exception if exit code is not 0
  // MySQL/MariaDB returns non-zero in case of errors
  if (exitCode != 0) {
      throw new IllegalStateException("Error during the execution of script \"" + stdinPath +
          "\", error code " + exitCode);
  }
}

Questo snippet è equivalente ad eseguire, da riga di comando:

mysql -v -v -v -u USER_NAME -pUSER_PASSW < STDIN_FILE > STDOUT_FILE 2> STDERR_FILE

Si noti che le tre opzioni -v abilitano il cosiddetto “table format”, equivalente alla modalità interattiva, in termini di prolissità.