/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package feedingzoo;

import static feedingzoo.FeedingZooAnimals.rhino;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Amrit
 */
public class Database1 {

    public static void dbConnection() throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        System.out.println("Successful!");

        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "elephant");
        System.out.println("2....connection established");

        Statement stmt = conn.createStatement();

        String createTableSQLs = "Drop table FeedingData";
        stmt.executeUpdate(createTableSQLs);

        String createTableSQL = "CREATE TABLE FeedingData(animalName VARCHAR2(30), amountFed number)";
        stmt.executeUpdate(createTableSQL);

        PreparedStatement preparedUpdateStmt = conn.prepareStatement("INSERT INTO FeedingData(animalName, amountFed) VALUES (?, ?)");

        int rhinoSum = 0;
        int cowSum = 0;
        int horseSum = 0;
        int zebraSum = 0;
        int deerSum = 0;

        for (int i = 0; i < rhino.size(); i++) {
            rhinoSum += rhino.get(i);
        }

        for (int i = 0; i < FeedingZooAnimals.cow.size(); i++) {
            cowSum += FeedingZooAnimals.cow.get(i);
        }

        for (int i = 0; i < FeedingZooAnimals.horse.size(); i++) {
            horseSum += FeedingZooAnimals.horse.get(i);
        }

        for (int i = 0; i < FeedingZooAnimals.zebra.size(); i++) {
            zebraSum += FeedingZooAnimals.zebra.get(i);
        }

        for (int i = 0; i < FeedingZooAnimals.deer.size(); i++) {
            deerSum += FeedingZooAnimals.deer.get(i);
        }

        preparedUpdateStmt.setString(1, "Rhino");
        preparedUpdateStmt.setInt(2, rhinoSum);
        preparedUpdateStmt.executeUpdate();

        preparedUpdateStmt.setString(1, "Cow");
        preparedUpdateStmt.setInt(2, cowSum);
        preparedUpdateStmt.executeUpdate();

        preparedUpdateStmt.setString(1, "Horse");
        preparedUpdateStmt.setInt(2, horseSum);
        preparedUpdateStmt.executeUpdate();

        preparedUpdateStmt.setString(1, "Zebra");
        preparedUpdateStmt.setInt(2, zebraSum);
        preparedUpdateStmt.executeUpdate();

        preparedUpdateStmt.setString(1, "Deer");
        preparedUpdateStmt.setInt(2, deerSum);
        preparedUpdateStmt.executeUpdate();

        System.out.println("\nAnimalName    AmountFed");
        ResultSet rs = stmt.executeQuery("SELECT * FROM FeedingData");
        while (rs.next()) {
            String animalName = rs.getString("animalName");
            int amountFed = rs.getInt("amountFed");
            System.out.println(animalName + "\t\t  " + amountFed);
        }

        rs = stmt.executeQuery("select * from feedingData where amountfed = (select max(amountfed) from feedingData)");
        System.out.println("\nThe Animal with the most eaten....");
        while (rs.next()) {
            String animalName = rs.getString("animalName");
            int amountFed = rs.getInt("amountFed");
            System.out.println(animalName + "\t\t  " + amountFed);
        }

        rs = stmt.executeQuery("select sum(amountFed) from feedingData");
        System.out.println("\nTotal Amount Eaten By All");
        while (rs.next()) {
            int amountFed = rs.getInt("sum(amountFed)");
            System.out.println(amountFed);
        }
        // TODO code application logic here
    }
}