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{
    int ticker, choice;

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

    Scanner input = new Scanner(System.in);

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

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

   default :
       return;
}
}

public void purchase_stock_with_ticker (int ticker,Connection conn) throws SQLException{
    //Connection conn = new getDBConnection();
    PreparedStatement stmt=conn.prepareStatement("");
    stmt.setInt(1,ticker);

    int i = stmt.executeUpdate();
}

public void avg_state_account_bal(Connection conn) throws SQLException{
String avg_acc_bal = "select avg(account.balance) " +
"from account, client " +
"group by client.state;";

    Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(avg_acc_bal);
        while(rs.next()){
            int bal = rs.getInt(1);
                    System.out.println(bal);
        }

}
}