import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Bank {
	public static void Überweisung(
		int srcAccount,
		int dstAccount,
		double amount) {
		Connection con = connectDB();
		try {
			con.setAutoCommit(false);
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			Statement stmt = con.createStatement();
			stmt.executeUpdate("UPDATE konto set stand=stand-"+amount+" where nummer='"+srcAccount+"';");
			stmt.executeUpdate("UPDATE konto set stand=stand+"+amount+" where nummer='"+dstAccount+"';");
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static String Vermögensübersicht(String kunde) {
		String result=null;
		try {
			result= "Vermögensübersicht für: "+kunde+"\n";
			result+="------------------------------------------\n";
			Statement stmt = connectDB().createStatement();
			stmt.executeUpdate("LOCK TABLES konto READ, inhaber READ;");
			ResultSet rs = stmt.executeQuery("SELECT k.nummer, stand FROM konto AS k, inhaber AS i WHERE i.name='"+kunde+"' AND i.nummer=k.nummer;");
			while(!rs.isLast()) {
				rs.next();
				result+=rs.getInt("nummer")+"\t"+rs.getInt("stand")+"\n";
			}
			result+="------------------------------------------\n";
			rs = stmt.executeQuery("SELECT SUM(stand) AS s from konto as k,inhaber as i where i.name='"+kunde+"' and i.nummer=k.nummer;");
			stmt.executeUpdate("UNLOCK TABLES;");
			rs.next();
			result+="Saldo aller Konten: "+rs.getInt("s")+"\n";
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	private static Connection connectDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.err.println("Driver class not found");
			e.printStackTrace();
		}
		Connection con = null;
		try {
			con =
				(Connection) DriverManager.getConnection(
					"jdbc:mysql://localhost/bank/",
					"root",
					"");
		} catch (SQLException e1) {
			System.err.println("Error establishing database connection");
			e1.printStackTrace();
		}
		return con;
	}

}