package postgres;

import java.sql.*;

public class Postgres
{
    public static void main(String[] args) throws SQLException
    {
        try
        {
            Class.forName("org.postgresql.Driver");
        }
        catch (ClassNotFoundException cnfe)
        {
            System.err.println("Couldn't find driver class.");
        }

        Connection connection = null;
        connection = DriverManager.getConnection(
           "jdbc:postgresql://localhost:5432/uni",
           "postgres",
           "password"
        );

        Statement stmt = connection.createStatement();

        Integer rest[][] = new Integer[2][11];

        rest[0][0]  = 7654; rest[1][0]  = 100;
        rest[0][1]  = 7654; rest[1][1]  = 105;
        rest[0][2]  = 7654; rest[1][2]  = 102;
        rest[0][3]  = 7654; rest[1][3]  = 104;
        rest[0][4]  = 7654; rest[1][4]  = 106;
        rest[0][5]  = 7499; rest[1][5]  = 101;
        rest[0][6]  = 7499; rest[1][6]  = 103;
        rest[0][7]  = 7499; rest[1][7]  = 105;
        rest[0][8]  = 7521; rest[1][8]  = 108;
        rest[0][9]  = 7521; rest[1][9]  = 107;
        rest[0][10] = 7521; rest[1][10] = 104;

        for(int i = 0; i < 100; i++)
        {
            for(int j = 0; j < 11; j++)
            {
                stmt.executeQuery(
                        "SELECT " +
                        "   Customer.name   AS customer, " +
                        "   Employee.name   AS employee, " +
                        "   Department.name AS department " +
                        "FROM " +
                        "   Customer " +
                        "JOIN " +
                        "   resp_for " +
                        "   ON " +
                        "       Customer.custno = resp_for.custno " +
                        "   AND " +
                        "       Customer.custno = '" + rest[1][j] + "' " +
                        "JOIN " +
                        "   Employee " +
                        "   ON " +
                        "       resp_for.empno = Employee.empno " +
                        "   AND " +
                        "       Employee.empno = '" + rest[0][j] +"' " +
                        "JOIN " +
                        "   Department " +
                        "   ON " +
                        "       Employee.deptno = Department.deptno"
                );
            }
        }

//      while (rs.next())
//      {
//          System.out.println("############################");
//
//          System.out.println("Kunde: ");
//          System.out.println(rs.getString("customer"));
//
//          System.out.println("Angesteller: ");
//          System.out.println(rs.getString("employee"));
//
//          System.out.println("Abeteilung: ");
//          System.out.println(rs.getString("department"));
//      }

/*
 * CREATE TABLE Employee (
 *  empno INTEGER NOT NULL,
 *  name VARCHAR(40) NOT NULL,
 *  position VARCHAR(40) NOT NULL,
 *  mngrno INTEGER NOT NULL,
 *  hire_date DATE NOT NULL,
 *  salary NUMERIC(10,2) NOT NULL,
 *  commission REAL,
 *  deptno INTEGER NOT NULL,
 *  PRIMARY KEY (empno),
 *  CONSTRAINT emp_dept_fk
 *  FOREIGN KEY (deptno) REFERENCES Department,
 *  CONSTRAINT emp_emp_fk FOREIGN KEY (mngrno) REFERENCES Employee
 * );
 *
 * CREATE TABLE Customer (
 *  custno INTEGER,
 *  name VARCHAR(50) NOT NULL,
 *  address VARCHAR(40),
 *  phone VARCHAR(12) NOT NULL,
 *  comments TEXT,
 *  cred_lim NUMERIC(10,2),
 *  CONSTRAINT cust_pk PRIMARY KEY (custno)
 * );
 *
 * CREATE TABLE resp_for (
 *  empno INTEGER NOT NULL,
 *  custno INTEGER NOT NULL,
 *  PRIMARY KEY (empno,custno),
 *  CONSTRAINT resp_emp_fk FOREIGN KEY (empno) REFERENCES Employee ON UPDATE CASCADE ON DELETE CASCADE,
 *  CONSTRAINT resp_cust_fk FOREIGN KEY (custno) REFERENCES Customer ON DELETE CASCADE ON UPDATE CASCADE
 * );
 *
 * CREATE TABLE Department (
 *  deptno INTEGER,
 *  name VARCHAR(20),
 *  location VARCHAR(40),
 *  PRIMARY KEY(deptno)
 * );
 *
 */

        connection.close();
    }
}