import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import oracle.jdbc.pool.OracleDataSource;

public class JDBCExample {
    String jdbcUrl  = "jdbc:oracle:thin:@akka.d.umn.edu:1521:xe";
    Connection conn;

public Connection getDBConnection() throws SQLException{
    OracleDataSource ds = new OracleDataSource();


        ds.setURL(jdbcUrl);if(conn == null) {
            // Display a message to get the password from the user
            JLabel label = new JLabel("Oracle Username: ");
            JTextField jtf = new JTextField();
            JLabel label2 = new JLabel("Oracle Password:");
            JPasswordField jpf = new JPasswordField();
            JOptionPane.showConfirmDialog(null,new Object[]{label, jtf, label2, jpf}, "Password:",
            JOptionPane.OK_CANCEL_OPTION);
            String password = String.valueOf(jpf.getPassword());
    conn = ds.getConnection(jtf.getText(), password );
        }

    conn.setAutoCommit(true);
    return conn;
}

public static void main(String[] args) throws Exception{
    String ticker;
    int choice;
    //boolean answer;

    JDBCExample a = new JDBCExample();
    Connection conn = a.getDBConnection();

    Scanner input = new Scanner(System.in);

    System.out.println("Either 'ticker_symbol' (1), 'avg_bal' (2), or quit(0).\n");
        choice = input.nextInt();
    switch(choice)
{
   case 1 :
       System.out.println("Enter the ticker symbol\n");
        ticker = input.next();
        a.ticker_symbol(ticker,conn);
      break;

   case 2 :
       a.avg_bal(conn);
      break;

   default :
       return;
}
}

public void ticker_symbol (String ticker, Connection conn) throws SQLException{

   String selectSQL = "select client_ssn, name, email from s_client where s_client.client_ssn = (select client_ssn from transaction where transaction.cusip = (select cusip from stock where ticker = (?)))";

    PreparedStatement preparedStatement = conn.prepareStatement(selectSQL);
    preparedStatement.setString(1, ticker);
    ResultSet rs = preparedStatement.executeQuery(selectSQL);
    while (rs.next()) {
            int ssn = rs.getInt("client_ssn");
                System.out.println(ssn);
            String name = rs.getString("name");
                System.out.println(name);
            String email = rs.getString("email");
                System.out.println(email);

    }

}

public void avg_bal(Connection conn) throws SQLException{
String selectSQL = "select s_client.state, avg(account.balance) from account, s_client group by s_client.state;";
    Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(selectSQL);
        while(rs.next()){
            String state = rs.getString(1);
                    System.out.println(state);
            int bal = rs.getInt("avg(account.balance)");
                    System.out.println(bal);
        }
}
}