database hw

import java.sql.*;

public class FTM
{
    public static void main(String[] args) throws SQLException, ClassNotFoundException
    {
        System.out.println(">>>>Begin program...");
        Class.forName("org.postgresql.Driver");
        String connectionString = "jdbc:postgresql://localhost:5432/PA2Bank";
        String username = args[0];
        String password = args[1];
        Connection conn = DriverManager.getConnection(connectionString, username, password);
        DatabaseMetaData md = conn.getMetaData();

        Statement stmt1 = conn.createStatement();
        Statement stmt2 = conn.createStatement();

        boolean empty = true;
        ResultSet transferEmpty = stmt1.executeQuery("SELECT * FROM transfer");
        while (transferEmpty.next())
        {
            empty = false;
        }
        if (empty)
        {
            System.out.println("exiting...");
            return;
        }

        boolean deltaEmpty;
        String dropInfluence = "DROP TABLE influence";
        String dropFunds = "DROP TABLE funds";
        String dropDelta = "DROP TABLE delta";
        String dropFundsOld = "DROP TABLE funds_old";
        String createInfluence = "CREATE TABLE influence (\"from\" varchar(32), \"to\" varchar(32));";
        String createFunds = "CREATE TABLE funds (src varchar(32), dest varchar(32))";
        String createDelta = "CREATE TABLE delta (src varchar(32), dest varchar(32));";
        String createFundsOld = "CREATE TABLE funds_old (src varchar(32), dest varchar(32))";
        ResultSet influenceExists = md.getTables(null, null, "influence", null);
        ResultSet deltaExists = md.getTables(null, null, "delta", null);
        ResultSet fundsOldExists = md.getTables(null,null,"funds_old",null);
        ResultSet fundsExists = md.getTables(null,null,"funds",null);

        // drop tables if they exist and create new ones
        while (influenceExists.next())
        {
            stmt1.executeUpdate(dropInfluence);
        }
        while (fundsOldExists.next())
        {
            stmt1.executeUpdate(dropFundsOld);
        }
        while (deltaExists.next())
        {
            stmt1.executeUpdate(dropDelta);
        }
        while (fundsExists.next())
        {
            stmt1.executeUpdate(dropFunds);
        }
        stmt1.executeUpdate(createFunds);
        stmt1.executeUpdate("INSERT INTO funds (src, dest) SELECT src, tgt FROM transfer");
        stmt1.executeUpdate(createFundsOld);
        stmt1.executeUpdate("INSERT INTO funds_old (src, dest) SELECT src, tgt FROM transfer");
        stmt1.executeUpdate(createDelta);
        stmt1.executeUpdate("INSERT INTO delta (src, dest) SELECT src, dest FROM funds");
        stmt1.executeUpdate(createInfluence);
        deltaEmpty = false;

        ResultSet delta;
        String update;

        while (!deltaEmpty)
        {
            // funds = funds + new recursive step
            update = "INSERT INTO funds SELECT DISTINCT x.src, y.dest FROM delta x, funds y WHERE x.dest=y.src "
                   + "AND (x.src, y.dest) NOT IN (SELECT * FROM funds) UNION "
                   + "SELECT DISTINCT w.src, z.dest FROM funds w, delta z WHERE w.dest=z.src "
                   + "AND (w.src, z.dest) NOT IN (SELECT * FROM funds)";
            stmt1.executeUpdate(update);
            stmt1.executeUpdate("DELETE FROM delta");
            deltaEmpty = true;
            stmt1.executeUpdate("INSERT INTO delta SELECT src, dest FROM funds EXCEPT SELECT src, dest FROM funds_old");
            delta = stmt2.executeQuery("SELECT * FROM delta");

            while (delta.next())
            {
                deltaEmpty = false;
                break;
            }
            stmt1.executeUpdate("INSERT INTO funds_old SELECT * FROM delta");
            System.out.println("==========================");
        }

        // insert names corresponding to acct numbers into influence table
        update = "INSERT INTO influence SELECT d1.cname c1, d2.cname c2 FROM depositor d1, depositor d2, funds " +
                 "WHERE d1.ano=funds.src AND d2.ano=funds.dest";
        stmt1.executeUpdate(update);

        // cleanup
        fundsOldExists = md.getTables(null,null,"funds_old",null);
        fundsExists = md.getTables(null,null,"funds",null);
        deltaExists = md.getTables(null, null, "delta", null);

        while (fundsOldExists.next())
        {
            stmt1.executeUpdate(dropFundsOld);
        }
        while (deltaExists.next())
        {
            stmt1.executeUpdate(dropDelta);
        }
        while (fundsExists.next())
        {
            stmt1.executeUpdate(dropFunds);
        }

        stmt1.close();
        stmt2.close();
        conn.close();

        System.out.println("<<<<...Finished successfully");
    }
}