// QueryDB.java
// Andrew Davison, ad@coe.psu.ac.th, Nov. 2022

/*  Query the "cities" table of worldcities.db, 
    retrieving all the records which match the 
    supplied city name.

    The result set is printed in three ways:
      * using a while loop
      * using DBUtils.writeToCSV() to write the data in CSV
        form to "resultsQuery.csv"
      * using DBTablePrinter.printResultSet() to print the
        results in a nice textual table

    Since SQLite only supports TYPE_FORWARD_ONLY cursors for
    ResultSet, it's not possible to rewind the cursor after each
    printing. Instead we have to recalculate the result (twice).

    Usage:
      > compile QueryDB.java
      > run QueryDB <city name>
    e.g.
      > run QueryDB London
      > run QueryDB "Hat Yai"

*/

import java.sql.*;


public class QueryDB
{
  public static void main(String[] args)
  {
    if (args.length != 1) {
      System.out.println("Usage: java QueryDB <city name>");
      System.exit(1);
    }
    String cityName = args[0];

    try {
      System.out.println("Connecting to worldcities.db");
      Connection conn = DriverManager.getConnection(
                                   "jdbc:sqlite:worldcities.db");
      Statement stmt = conn.createStatement();

      System.out.println("Extracting details for \"" + cityName + "\"");
      ResultSet rs = stmt.executeQuery(
        "SELECT * FROM cities WHERE city = \"" + cityName + "\"");

      int count = 0;
      while(rs.next()) {
        System.out.println("  Located in " +  rs.getString("country") +
                       " at (" + rs.getDouble("lat") + ", " +
                                 rs.getDouble("lng") + ")");
        count++;
      }
      System.out.println("No. of matches: " + count);

      // SQLite only supports TYPE_FORWARD_ONLY cursors, so
      // recalculate the result twice!
      rs = stmt.executeQuery(
            "SELECT * FROM cities WHERE city = \"" + cityName + "\"");
      DBUtils.writeToCSV(rs, "resultsQuery.csv");

      rs = stmt.executeQuery(
            "SELECT * FROM cities WHERE city = \"" + cityName + "\"");
      DBTablePrinter.printResultSet(rs);

      conn.close();
    } 
    catch (SQLException e) 
    {  System.out.println(e.getMessage());  }
  } /* main */


}  // end of QueryDB class