/*
 * 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 pkgfinal;

/**
 *
 * @author Jay
 */

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

/**
 *
 * @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);
        }
    // TODO code application logic here
    }
}