import java.awt.Color;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;

public class BLZSuche extends JFrame implements ActionListener {

    final String hostname = "b203-lehrer";
    final String port = "3306";
    final String dbname = "blz";
    final String user = "schueler";
    final String password = "schueler";
    JPanel North = new JPanel();
    JPanel Center = new JPanel();
    JPanel South = new JPanel();
    JLabel lbBLZ = new JLabel("BLZ");
    JLabel lbPLZ = new JLabel("PLZ");
    JLabel lbORT = new JLabel("ORT");
    JTextField tfBLZ = new JTextField(20);
    JTextField tfPLZ = new JTextField(10);
    JTextField tfORT = new JTextField(50);
    JTextArea taOUTPUT = new JTextArea();
    JButton btSUCHE = new JButton("Search and Destroy");
    JButton btCLEAR = new JButton("Clear");

    public BLZSuche(String title) {
        super(title);
        createGUI();
    }
    private void createGUI() {
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setSize(800,500);
        this.setBackground(Color.GRAY);
        btSUCHE.addActionListener(this);
        btCLEAR.addActionListener(this);
        North.setLayout(new GridLayout(3,3));
        add("North",North);
        North.add(lbBLZ);
        North.add(tfBLZ);
        North.add(lbPLZ);
        North.add(tfPLZ);
        North.add(lbORT);
        North.add(tfORT);
        Center.setLayout(new GridLayout(1,0));
        add("Center",Center);
        taOUTPUT.setEditable(false);
        Center.add(taOUTPUT);
        add("South",South);
        South.add(btSUCHE);
        South.add(btCLEAR);
        add(new JScrollPane(taOUTPUT));
        taOUTPUT.append("Bankleitzahl\tMerkmal\tBezeichnung\t\tPLZ\tORT\n");
        this.setLocation(150,200);
        this.setVisible(true);
    }
    public void actionPerformed(ActionEvent e) {
        if(e.getSource() == btSUCHE)
            datenbank();
        else if(e.getSource() == btCLEAR) {
            taOUTPUT.setText("");
            taOUTPUT.append("Bankleitzahl\tMerkmal\tBezeichnung\t\tPLZ\tORT\n");
        }
    }
    private void datenbank() {
        Connection conn = null;
        try {
            System.out.println("[+] (MySQL) Treiber laden\n");
            Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        }
        catch (Exception e) {
            System.out.println("[+] (MySQL) Unable to load driver.\n");
            e.printStackTrace();
        }
        try {
            System.out.println("[+] (MySQL) Verbindung aufbauen\n");
            String url = "jdbc:mysql://"+hostname+":"+port+"/"+dbname;
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("[+] (MySQL) Statement beginnen\n");
            Statement stmt = conn.createStatement();
            System.out.println("[+] (MySQL) Abfrage beginnen\n");
            String blz = tfBLZ.getText();
            String plz = tfPLZ.getText();
            String ort = tfORT.getText();
            ResultSet rsBLZ, rsPLZ, rsORT;
            if(!blz.equals("")) {
                String sqlCommandBLZ = "SELECT * FROM blz WHERE Bankleitzahl LIKE \""+blz+"\";";
                rsBLZ = stmt.executeQuery(sqlCommandBLZ);
                while(rsBLZ.next()) {
                    taOUTPUT.append(rsBLZ.getString(1)+"\t"+rsBLZ.getDouble(2)+"\t"+rsBLZ.getString(3)+"\t"+rsBLZ.getString(4)+"\t"+rsBLZ.getString(5)+"\n");
                }
            }
            if(!plz.equals("")) {
                String sqlCommandPLZ = "SELECT * FROM blz WHERE PLZ LIKE \""+plz+"\";";
                rsPLZ = stmt.executeQuery(sqlCommandPLZ);
                while(rsPLZ.next()) {
                    taOUTPUT.append(rsPLZ.getString(1)+"\t"+rsPLZ.getDouble(2)+"\t"+rsPLZ.getString(3)+"\t"+rsPLZ.getString(4)+"\t"+rsPLZ.getString(5)+"\n");
                }
            }
            if(!ort.equals("")) {
                String sqlCommandORT = "SELECT * FROM blz WHERE Ort LIKE \""+ort+"\";";
                rsORT = stmt.executeQuery(sqlCommandORT);
                while(rsORT.next()) {
                    String bezeichnung = rsORT.getString(3);
                    if(bezeichnung.length()<13)
                        taOUTPUT.append(rsORT.getString(1)+"\t"+rsORT.getDouble(2)+"\t"+bezeichnung+"\t\t\t"+rsORT.getString(4)+"\t"+rsORT.getString(5)+"\n");
                    else taOUTPUT.append(rsORT.getString(1)+"\t"+rsORT.getDouble(2)+"\t"+bezeichnung+"\t"+rsORT.getString(4)+"\t"+rsORT.getString(5)+"\n");

                }
            }
            System.out.println("[+] (MySQL) Ergebnisse anzeigen\n");
            System.out.println("[+] (MySQL) Statement beenden\n");
            stmt.close();
            System.out.println("[+] (MySQL) Datenbank-Verbindung beenden\n");
            conn.close();
        }
        catch (SQLException sqle) {
            System.out.println("[+] (MySQL) SQLException: " + sqle.getMessage()+"\n");
            System.out.println("[+] (MySQL) SQLState: " + sqle.getSQLState()+"\n");
            System.out.println("[+] (MySQL) VendorError: " + sqle.getErrorCode()+"\n");
            sqle.printStackTrace();
        }
    }
    public static void main(String args[]) {
        new BLZSuche("BLZSuchmaschine");
    }
}