package cart.dbUtils;

import cart.ItemCategory;
import java.util.ArrayList;
import java.util.ListIterator;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

//ItemCategoryDBUtil
//author - Lawrence Truett - FluffyCat.com
//date - May 23, 2003 - San Diego, CA
public class ItemCategoryDBUtil {
    private long itemKey;
    private long categoryKey;
    
    ItemCategoryDBUtil() {}
    
    public static ArrayList getItemsForCategory(long category, 
                                                DBConnection dbc) {
        ArrayList itemCatToReturn = null;
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "Select * from itemCategory where category = ?");
            preparedStatement.setLong(1, category);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                itemCatToReturn.add(
                    new ItemCategory(resultSet.getLong(1), //item
                resultSet.getLong(2)));//category
            }
            resultSet.close();
            preparedStatement.close();
            
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemDBUtil.getItemsForCategory: " + 
                sqlException);
        }
        return itemCatToReturn;
    }
    
    public static ArrayList getCategoriesForItem(long item, 
                                                 DBConnection dbc) {
        ArrayList itemCatToReturn = null;
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "Select * from itemCategory where item = ?");
            preparedStatement.setLong(1, item);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                itemCatToReturn.add(
                    new ItemCategory(resultSet.getLong(1), //item
                resultSet.getLong(2)));//category
            }
            resultSet.close();
            preparedStatement.close();
            
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemCategoryDBUtil.getCategoriesForItem: " + 
                sqlException);
        }
        return itemCatToReturn;
    }
    
    public static ArrayList 
        getAllItemCategoriesByCategory(DBConnection dbc) {
        ArrayList itemCatToReturn = new ArrayList();
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "Select * from itemCategory order by category");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                itemCatToReturn.add(
                    new ItemCategory(resultSet.getLong(1), //item
                resultSet.getLong(2)));//category
            }
            resultSet.close();
            preparedStatement.close();
            
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemCategoryDBUtil.getAllItemCategoriesByCategory: " + 
                sqlException);
        }
        return itemCatToReturn;
    }
    
    public static void insert(long item, 
                              long category, 
                              Connection connection) {
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "INSERT INTO itemCategory values(?,?);");
            preparedStatement.setLong(1, item);
            preparedStatement.setLong(2, category);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "CategoryDBUtil.insert: " + 
                sqlException);
        }
    }
    
}