import java.sql.*;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class ConnectionAufbau {

	public static void main(String[] args) {

		Connection con = getCon();

		try {
			// idFüllen();
			writeInDatabase("as", "d", 1);
			readFromDatabase();
			con.close();
			System.out.println("asd");
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	// Verbindung aufbauen

	private static Connection getCon() {

		Connection con = null;
		String url = "jdbc:mysql://127.0.0.1:3306/";
		String db = "planer";
		String driver = "com.mysql.jdbc.Driver";
		String user = "root";
		String pass = "";

		try {
			Class.forName(driver).newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		try {
			con = (Connection) DriverManager
					.getConnection(url + db, user, pass);
		} catch (SQLException e) {
			System.err.println("Mysql Connection Error: ");
			e.printStackTrace();
		}
		return con;
	}

	// In Datenbank schreiben

	public static void writeInDatabase(String termin, String beschreibung,
			int index) {
		System.out.println("Schreibe in Datenbank");
		String query = "UPDATE tron SET termin = '" + termin
				+ "', beschreibung= '" + beschreibung + "' WHERE id =" + index
				+ "";

		try {
			Connection con = getCon();
			Statement select = (Statement) con.createStatement();
			select.executeUpdate(query);
			select.close();
			con.close();
		} catch (SQLException e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

	}

	// Tabelle lesen

	public static String[] readFromDatabase() {
		String query = "SELECT termin , beschreibung FROM tron";
		String[] veranstaltungsArray = new String[105];

		try {
			Connection con = getCon();
			Statement select = (Statement) con.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			while (result.next()) {
				veranstaltungsArray[i] = result.getString(2);
				System.out.println(veranstaltungsArray[i]);
				i++;
			}
			// for(int j=0;j<106;j++){
			// System.out.println(veranstaltungsArray[j]);
			// }
			select.close();
			result.close();
			con.close();
		} catch (SQLException e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
		return veranstaltungsArray;

	}

	// Daten löschen
	public void deleteFromDatabase(int x, int y) {
		int id = x * 10 + y;
		System.out.println("Eintrag aus Datenbank geloescht");
		String query = "UPDATE tron SET termin = '', beschreibung= '' WHERE veranstaltungsID ="
				+ id + "";

		try {
			Connection con = getCon();
			Statement select = (Statement) con.createStatement();
			select.executeUpdate(query);
			select.close();
			con.close();
		} catch (SQLException e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

	}

	// Datenbank füllen mit ids
	public static void idFüllen() {
		int l = 0;
		for (int i = 1; i < 16; i++) {

			for (int j = 1; j < 8; j++) {

				String query = "INSERT INTO tron (id)  VALUES ( " + l + ")";
				l++;
				// String query = "SELECT * FROM veranstaltungen";

				try {
					Connection con = getCon();
					Statement select = (Statement) con.createStatement();
					select.executeUpdate(query);

					select.close();
					con.close();
				} catch (SQLException e) {
					System.err.println("Mysql Statement Error: " + query);
					e.printStackTrace();
				}

			}
		}

	}
}