package project1;

import java.awt.Dimension;

import java.awt.Rectangle;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
import oracle.jdbc.OracleDriver;

public class Frame1 extends JFrame {
    private JComboBox jComboBox1 = new JComboBox();
    private JScrollPane jScrollPane1 = new JScrollPane();
    DefaultTableModel model=new DefaultTableModel();
    private JTable jTable1 = new JTable(model);
    private JButton jButton1 = new JButton();

    public static Connection getConnection() throws SQLException {
        String username = "hr";
        String password = "hr";
        String thinConn = "jdbc:oracle:thin:@pitsa.pld.ttu.ee:1521:ORCL";
        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(thinConn,username,password);
        conn.setAutoCommit(false);
        return conn;
    }
    public static ResultSet kaivita(Connection conn, Statement stmt, String lause)throws SQLException {
    ResultSet rs=stmt.executeQuery(lause);
    return rs;
    }

    public Frame1() {
        try {
            jbInit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void jbInit() throws Exception {
        this.getContentPane().setLayout( null );
        this.setSize( new Dimension(400, 300) );
        this.setTitle( "welcome" );
        jComboBox1.setBounds(new Rectangle(15, 15, 190, 20));
        jScrollPane1.setBounds(new Rectangle(15, 65, 230, 180));
        jButton1.setText("jButton1");
        jButton1.setBounds(new Rectangle(265, 15, 110, 20));
        jButton1.addActionListener(new ActionListener() {
                    public void actionPerformed(ActionEvent e) {
                        try {
                            jButton1_actionPerformed(e);
                        } catch (SQLException f) {
                            // TODO
                        }
                    }
                });
        jScrollPane1.getViewport().add(jTable1, null);
        this.getContentPane().add(jButton1, null);
        this.getContentPane().add(jScrollPane1, null);
        this.getContentPane().add(jComboBox1, null);
        Connection conn=getConnection();
        Statement stmt=conn.createStatement();
        String lause="Select JOB_TITLE, MIN_SALARY from hr.jobs";
        ResultSet rs=kaivita(conn, stmt, lause);
        model.addColumn("JOB_TITLE");
        model.addColumn("Min_SALARY");
        
        while(rs.next()){
          model.addRow(new Object[]{rs.getString(1),rs.getString(2)});  
            jComboBox1.addItem(rs.getString(1)); 
        }
        conn.close();
        stmt.close();
    }

    private void jButton1_actionPerformed(ActionEvent e) throws SQLException {
    Object a=jComboBox1.getSelectedItem();
    int n=model.getRowCount();
    for(int i=0; i<n; i++){
    model.removeRow(0);
    }
    String d=a.toString();
    String x="Select JOB_TITLE, MIN_SALARY from hr.jobs where JOB_TITLE='"+d+"'";
    Connection conn=getConnection();
    Statement stmt=conn.createStatement();
    ResultSet rs=kaivita(conn, stmt, x);
        while(rs.next()){
            model.addRow(new Object[]{rs.getString(1),rs.getString(2)});  
        }
    conn.close();
    stmt.close();
    }
}