Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 DBUSER WHERE USER_ID = ?";
- 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 add_the_driver = "select driver.name " +
- "from driver " +
- "where driver.ssn_driver in(select insurance_policy.ssn_drive " +
- " from accident, insurance_policy" +
- " where insurance_policy.expiration_date < accident.accident_date)";
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(add_the_driver);
- while(rs.next()){
- String driver_name = rs.getString(1);
- System.out.println(driver_name);
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement