import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Date;
 
public class SqlQueryExecutor {

    // Change the following constants to your settings.
    private static final String MYSQL_USER_NAME = "visit01";
    private static final String MYSQL_PASSWORD = "jxfap3pi";
    private static final String MYSQL_DATABASE = "visit01_testdb";
 
    public static void main(String[] argv) {

        String mysqlConnectionUrl = "jdbc:mysql://archerdb1.ph.ed.ac.uk:3306/" + MYSQL_DATABASE;
 
	try {
	    //Register the MySQL JDBC driver. 
	    Class.forName("com.mysql.jdbc.Driver");
	} catch (ClassNotFoundException e) {
            System.err.println("Unable to register the JDBC driver: " + e.getMessage());
	    e.printStackTrace();
	    return;
	}
	System.out.println("MySQL JDBC Driver is registered.");

        // Connect to your database with your username and password.
	Connection connection = null; 
	try {
	    connection = DriverManager.getConnection(mysqlConnectionUrl, MYSQL_USER_NAME, MYSQL_PASSWORD);
	} catch (SQLException e) {
            System.err.println("Unable to connect to the database: " + e.getMessage());
	    e.printStackTrace();
	    return;
	}
 
        //Prepare and execute an SQL statement to get a ResultSet.
        Statement sqlStatement = null;
        ResultSet resultSet = null;
        try {
	  if (connection != null) {
	      sqlStatement = connection.createStatement();
              String sql = "SELECT name, number, street, city FROM customers, addresses WHERE customers.address_id = addresses.address_id";
              resultSet = sqlStatement.executeQuery(sql);
	   } else {
	      System.out.println("No valid database connection detected.");
	   }
         } catch (SQLException e) {
             System.err.println("Unable to execute the SQL statement: " + e.getMessage());
             e.printStackTrace();
         }

        // Extract and print the data from the ResultSet.
        try {
          while (resultSet.next()) {
              String name = resultSet.getString("name");
              String number = resultSet.getString("number");
              String street = resultSet.getString("street");
              String city = resultSet.getString("city");
              System.out.println("name: " + name + " | number: " + number + " | street:" + street + " | city:" + city);
          }
        } catch (SQLException e) {
            System.err.print("Unable to extract data from the ResultSet: " + e.getMessage());
            e.printStackTrace();
        }
    }
}