/*
 * This sample shows how to list all the names from the Ledger table
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import com.mysql.jdbc.Driver;
import java.util.Properties;

class Moma
{
  public static void main (String args [])
       throws SQLException
  {
    // You can put a database name after the @ sign in the connection URL.
      Properties prop = new Properties();
      //prop.setProperty("serverTimezone","US/Eastern");
      //prop.setProperty("useLegacyDatetimeCode","false");

    Connection conn =
	DriverManager.getConnection ("jdbc:mysql://sql.cs.oberlin.edu/moma?useSSL=false&user=alice&password=tiger",prop);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select data from the MOMA database
    ResultSet rset = stmt.executeQuery ("select displayname,count(*) from artists natural join produced where displayname='John Lennon'");

    // Iterate through the result and print the artwork counts
    while (rset.next ()){
      System.out.println (rset.getString(1) + "  " + rset.getInt(2));
    }

    // Close the ResultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Close the connection
    conn.close();   
  }
}