package SQL;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JTextField;

import javax.swing.WindowConstants;
import javax.swing.event.AncestorEvent;
import javax.swing.event.AncestorListener;
import javax.swing.SwingUtilities;

/**
* This code was edited or generated using CloudGarden's Jigloo
* SWT/Swing GUI Builder, which is free for non-commercial
* use. If Jigloo is being used commercially (ie, by a corporation,
* company or business for any purpose whatever) then you
* should purchase a license for each developer using Jigloo.
* Please visit www.cloudgarden.com for details.
* Use of Jigloo implies acceptance of these licensing terms.
* A COMMERCIAL LICENSE HAS NOT BEEN PURCHASED FOR
* THIS MACHINE, SO JIGLOO OR THIS CODE CANNOT BE USED
* LEGALLY FOR ANY CORPORATE OR COMMERCIAL PURPOSE.
*/
public class DatenbankVerknüpfung extends javax.swing.JFrame {
	private JLabel jLabel1;
	private JLabel jLabel2;
	private JLabel jLabel3;
	private JLabel jLabel4;
	private JButton jButtonnew;
	private JButton jButtonSave;
	private JTextField jTextFieldName;
	private JTextField jTextFieldEmail;
	private JButton jButton1Weiter;
	private JButton jButtonSuche;
	private JButton jButtonBeenden;
	private JButton jButtonZurück;
	private JTextField jTextFieldVorname;
	private JTextField jTextFieldID;

	private ResultSet ergebnisMenge;
	private Connection conn;
	/**
	* Auto-generated main method to display this JFrame
	*/
	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				DatenbankVerknüpfung inst = new DatenbankVerknüpfung();
				inst.setLocationRelativeTo(null);
				inst.setVisible(true);
			}
		});
	}

	public DatenbankVerknüpfung() {
		super();
		initGUI();
		holeDaten();

	}

	private void initGUI() {

		try {
			setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
			getContentPane().setLayout(null);
			this.setPreferredSize(new java.awt.Dimension(267, 260));
			{
				jLabel1 = new JLabel();
				getContentPane().add(jLabel1);
				jLabel1.setText("ID:");
				jLabel1.setBounds(12, 12, 70, 15);
			}
			{
				jLabel2 = new JLabel();
				getContentPane().add(jLabel2);
				jLabel2.setText("Name:");
				jLabel2.setBounds(12, 43, 70, 15);
			}
			{
				jLabel3 = new JLabel();
				getContentPane().add(jLabel3);
				jLabel3.setText("Vorname:");
				jLabel3.setBounds(12, 71, 70, 15);
			}
			{
				jLabel4 = new JLabel();
				getContentPane().add(jLabel4);
				jLabel4.setText("Email:");
				jLabel4.setBounds(12, 99, 70, 15);
			}
			{
				jTextFieldID = new JTextField();
				getContentPane().add(jTextFieldID);
				jTextFieldID.setBounds(100, 12, 141, 22);
			}
			{
				jTextFieldName = new JTextField();
				getContentPane().add(jTextFieldName);
				jTextFieldName.setBounds(100, 40, 141, 22);
			}
			{
				jTextFieldVorname = new JTextField();
				getContentPane().add(jTextFieldVorname);
				jTextFieldVorname.setBounds(100, 68, 141, 22);
			}
			{
				jTextFieldEmail = new JTextField();
				getContentPane().add(jTextFieldEmail);
				jTextFieldEmail.setBounds(100, 96, 141, 22);
			}
			{
				jButton1Weiter = new JButton();
				getContentPane().add(jButton1Weiter);
				jButton1Weiter.setText("Weiter");
				jButton1Weiter.setBounds(12, 149, 83, 22);
				jButton1Weiter.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						jButton1WeiterActionPerformed(evt);
					}
				});
			}
			{
				jButtonZurück = new JButton();
				getContentPane().add(jButtonZurück);
				jButtonZurück.setText("Zurück");
				jButtonZurück.setBounds(12, 189, 83, 22);
				jButtonZurück.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						jButtonZurückActionPerformed(evt);
					}
				});
			}
			{
				jButtonBeenden = new JButton();
				getContentPane().add(jButtonBeenden);
				jButtonBeenden.setText("Beenden");
				jButtonBeenden.setBounds(165, 189, 83, 22);
				jButtonBeenden.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						jButtonBeendenActionPerformed(evt);
					}
				});
			}
			{
				jButtonSuche = new JButton();
				getContentPane().add(jButtonSuche);
				jButtonSuche.setText("Suche");
				jButtonSuche.setBounds(165, 149, 83, 21);
				jButtonSuche.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						jButtonSucheActionPerformed(evt);
					}
				});
			}
			{
				jButtonSave = new JButton();
				getContentPane().add(jButtonSave);
				jButtonSave.setText("Save");
				jButtonSave.setBounds(112, 149, 38, 21);
				jButtonSave.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						jButtonSaveActionPerformed(evt);
					}
				});
			}
			{
				jButtonnew = new JButton();
				getContentPane().add(jButtonnew);
				jButtonnew.setText("New");
				jButtonnew.setBounds(112, 189, 35, 21);
				jButtonnew.addActionListener(new ActionListener() {
					public void actionPerformed(ActionEvent evt) {
						jButtonnewActionPerformed(evt);
					}
				});
			}
			pack();
			this.setSize(267, 260);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void jButton1WeiterActionPerformed(ActionEvent evt) {

		try {
			if (ergebnisMenge.isLast()){
				ergebnisMenge.first();

			}
			else{
				 ergebnisMenge.next();

			}
			ausgebenDaten();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}



	private void jButtonZurückActionPerformed(ActionEvent evt) {
		try {
			if (ergebnisMenge.isFirst()){
				ergebnisMenge.last();

			}
			else{
				 ergebnisMenge.previous();

			}
			ausgebenDaten();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}



	private void holeDaten()
	{
		String sql = " select * from email2011";
	    String server = "sys3";
	    String user = "MA1011";
	    String password = "MA1011";
	    String url = "jdbc:as400://" + server + "/";

	    try {
			//Verbindung zur Datenbank aufbauen.
			Class.forName("com.ibm.as400.access.AS400JDBCDriver");
			Connection conn = DriverManager.getConnection(url, user, password);

			// Anweisung erstellen (frei Navigation und Änderungen erlaubt).
			Statement anweisung = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

			//SQL-Abfrage ausführen.
			ergebnisMenge = anweisung.executeQuery(sql);

			//Datensatzzeiger auf den ersten Datensatz der Ergebnismenge stellen.
			ergebnisMenge.next();


			ausgebenDaten();


	    } catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}
	private void ausgebenDaten(){
		try {
		jTextFieldID.setText(ergebnisMenge.getString("id"));
		jTextFieldVorname.setText(ergebnisMenge.getString("vorname"));
		jTextFieldName.setText(ergebnisMenge.getString("name"));
	    jTextFieldEmail.setText(ergebnisMenge.getString("email"));
	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}



	private void jButtonBeendenActionPerformed(ActionEvent evt) {
		System.exit(0);
	}

	private void jButtonSucheActionPerformed(ActionEvent evt) {
		String nr = jTextFieldID.getText();
		boolean gefunden = false;
		try {
			ergebnisMenge.first();
			do {
				if(ergebnisMenge.getString("id").equals(nr)){
					ausgebenDaten();
					gefunden = true;
				}
				else {
					ergebnisMenge.next();
				}

			}while (!gefunden);
		}
		catch (Exception e) {
			e.printStackTrace();
		}



	}

	private void jButtonSaveActionPerformed(ActionEvent evt) {
		String id = jTextFieldID.getText();
		String vorname = jTextFieldVorname.getText();
		String nachname = jTextFieldName.getText();
		String email = jTextFieldEmail.getText();
		String sql = "INSERT INTO eamil2011 (id, vorname, name, email) Values ('" + id +"', '" + vorname + 	"','" + nachname + "', '" + email + "')";
		try {
			Statement anweisung = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			anweisung.executeUpdate(sql);
		} catch (SQLException e) {
		//	zeigeFehler(e);
		}
		holeDaten();
//		sucheDaten(id);
		}

	private void jButtonnewActionPerformed(ActionEvent evt) {
		loescheFelder();
	}
	private void loescheFelder() {
		jTextFieldID.setText("");
		jTextFieldVorname.setText("");
		jTextFieldName.setText("");
		jTextFieldEmail.setText("");
	}
}