package javacodebook.jdbc.transaction;

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

public class TransactionExample {

    public static void main(String[] args) {
        try {
            Class.forName("org.postgresql.Driver");
            Connection con =
                DriverManager.getConnection(
                    "jdbc:postgresql:test",
                    "postgres",
                    "postgres");

            con.createStatement().executeUpdate(
                "DELETE FROM employees WHERE id = 12");

            // Erst mal prüfen, was die Datenbank so in Sachen
            // Transaktionen bietet
            int transtype = con.getTransactionIsolation();
            switch (transtype) {
                case Connection.TRANSACTION_NONE :
                    System.out.println(
                        "This connection supports no transactions."
                            + " exiting...");
                    System.exit(0);
                case Connection.TRANSACTION_READ_COMMITTED :
                    System.out.println(
                        "Transactions of type 'read commited'");
                    break;
                case Connection.TRANSACTION_READ_UNCOMMITTED :
                    System.out.println(
                        "Transactions of type 'read uncommited'");
                    break;
                case Connection.TRANSACTION_REPEATABLE_READ :
                    System.out.println(
                        "Transactions of type 'repeatable read'");
                    break;
                case Connection.TRANSACTION_SERIALIZABLE :
                    System.out.println(
                        "Transactions of type 'serializable'");
                    break;
            }

            // Ab hier geht es mit Transaktionen los...
            con.setAutoCommit(false);

            Statement statement = con.createStatement();
            ResultSet result =
                statement.executeQuery(
                    "SELECT firstname FROM employees");
            System.out.println("before:");
            while (result.next()) {
                System.out.println(result.getString(1));
            }

            statement.executeUpdate("DELETE FROM employees");
            statement = con.createStatement();
            result =
                statement.executeQuery(
                    "SELECT firstname FROM employees");
            System.out.println("in between:");
            while (result.next()) {
                System.out.println(result.getString(1));
            }
            // Die bisherige Transaktion wird abgebrochen.
            // Alle Statements seit setAutoCommit(false) 
            // werden rückgängig gemacht
            con.rollback();

            statement = con.createStatement();
            result =
                statement.executeQuery(
                    "SELECT firstname FROM employees");
            System.out.println("after rollback:");
            while (result.next()) {
                System.out.println(result.getString(1));
            }
            statement.executeUpdate(
                "INSERT INTO employees(id,firstname,lastname)"
                    + " values (12,'Miriam','Bauman')");
            // Diese Transaktion wird hingegen ausgeführt
            con.commit();

            result =
                statement.executeQuery(
                    "SELECT firstname FROM employees");
            System.out.println("after commit:");
            while (result.next()) {
                System.out.println(result.getString(1));
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}