// JDBCSQL.java - exec arbitraty SQL statements (C) Georgiy Pruss 2017

// set P=org.postgresql.Driver jdbc:postgresql://10.10.1.1:5432/testdb u1 p1
// set J=java -cp .;postgresql-42.1.1.jar JDBCSQL %P%
// %J% 1 "select version()" or %J% @filewithstmts.jsql

import java.sql.*;      // DriverManager Connection SQLException Statement ResultSet
import java.nio.file.*; // Files Paths

public class JDBCSQL
{
  public static final String help = "java -cp .;<jdbcjar> JDBCSQL <driver> <jdbc> <user> <pwd> "+
    "{@<file> | <n-fields|0> <sql>} [-s sepL@sepC@sepR]\n"+
    "  where lines in file must start with <n-fields|0> and <sql> must be without quotes\n"+
    "  and <sql> in command line must be in quotes.\nExamples:\n"+
    "java -cp .;postgresql-42.1.1.jar JDBCSQL\n"+
    "   org.postgresql.Driver jdbc:postgresql://10.10.1.1:5432/testdb u1 p1\n"+
    "     1 \"select version()\"\n"+
    "     0 \"drop table if exists T; create table T (f1 numeric, f2 varchar, f3 char(4))\"\n"+
    "     0 \"insert into T values (1,'one','12'),(2,'two','123'),(3,'three','1234')\"\n"+
    "     3 \"select * from T\"\n"+
    "     2 \"select f1,'['||f2||':'||f3||']' from T where f1<3\"\n"+
    "     1 \"select sum(f1) from T\"\n"+
    "option '-s' can define up to three separators instead of default '|', e.g. -s \"(@, @)\"";
  public static void main( String[] argv )
  {
    if( argv.length<5 ) { System.out.println( help ); return; }
    try { Class.forName( argv[0] ); }
    catch( ClassNotFoundException e ) { System.out.println( "No JDBC Driver" ); return; }
    Connection cnxn = null; // may also close at the end (throwable op!)
    try { cnxn = DriverManager.getConnection( argv[1], argv[2], argv[3] ); }
    catch( SQLException e ) { System.out.println( "Connection Failed" ); return; }
    if( cnxn == null ) { System.out.println( "Failed to make connection" ); return; }
    String[] seps = {"|","|","|"};
    if( argv[argv.length-2].equals("-s") ) seps = argv[argv.length-1].split("@",3);
    try
    {
      if( !argv[4].startsWith("@") )
        ex_st( cnxn, new String[]{argv[4],argv[5]}, seps );
      else
        for( String line: Files.readAllLines( Paths.get(argv[4].substring(1)) ) )
          ex_st( cnxn, line.split(" +",2), seps );
    }
    catch( Exception e ) { System.out.println( "Oops! " + e.getMessage() ); }
  }
  public static void ex_st( Connection c, String[] ns, String[] seps ) throws SQLException
  {
    System.out.println( "Exec: "+ns[0]+"|"+ns[1] );
    int cols = Integer.parseInt(ns[0]);
    Statement stmt = c.createStatement();
    if( cols == 0 ) { stmt.execute( ns[1] ); return; }
    ResultSet rs = stmt.executeQuery( ns[1] ); int rows = 0;
    for( ; rs.next(); ++rows, System.out.println( seps[2] ) )
      for( int i=1; i<=cols; ++i ) System.out.print( seps[i==1?0:1] + rs.getString(i) );
    System.out.println( "Rows: "+rows );
  }
}