PubHub TagDAOImpl

package examples.pubhub.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import examples.pubhub.model.Book;
import examples.pubhub.model.BookTag;
import examples.pubhub.utilities.DAOUtilities;

public class TagDAOImpl implements TagDAO {
    Connection connection = null; // Our connection to the database
    PreparedStatement stmt = null; // We use prepared statements to help protect against SQL injection

    @Override
    public List<BookTag> getAlBookTags() {
        List<BookTag> tagList = new ArrayList<>();

        try {
            connection = DAOUtilities.getConnection(); // Get our database connection from the manager
            String sql = "SELECT * FROM public.book_tags"; // Our SQL query
            stmt = connection.prepareStatement(sql); // Creates the prepared statement from the query
            ResultSet rs = stmt.executeQuery(); // Queries the database

            while (rs.next()) {
                BookTag tag = new BookTag();

                tag.setIsbn13(rs.getString("isbn_13"));
                tag.setTagName(rs.getString("tag_name"));

                tagList.add(tag);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources();
        }
        return tagList;
    }

    @Override
    public List<BookTag> getBookTagsByBook(Book bookObjArg) {
        List<BookTag> tagList = new ArrayList<>();

        try {
            connection = DAOUtilities.getConnection(); // Get our database connection from the manager
            String sql = "SELECT * FROM public.book_tags " + "WHERE isbn_13 LIKE ?"; // Our SQL query
            stmt = connection.prepareStatement(sql); // Creates the prepared statement from the query
            stmt.setString(1, "%" + bookObjArg.getIsbn13() + "%");
            ResultSet rs = stmt.executeQuery(); // Queries the database

            while (rs.next()) {
                BookTag tag = new BookTag();

                tag.setIsbn13(rs.getString("isbn_13"));
                tag.setTagName(rs.getString("tag_name"));

                tagList.add(tag);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources();
        }
        return tagList;
    }

    @Override
    public List<BookTag> getBookTagsByName(String nameArg) {
        List<BookTag> tagList = new ArrayList<>();

        try {
            connection = DAOUtilities.getConnection(); // Get our database connection from the manager
            String sql = "SELECT * FROM public.book_tags " + "WHERE isbn_13 LIKE ?"; // Our SQL query
            stmt = connection.prepareStatement(sql); // Creates the prepared statement from the query
            stmt.setString(1, "%" + nameArg + "%");
            ResultSet rs = stmt.executeQuery(); // Queries the database

            while (rs.next()) {
                BookTag tag = new BookTag();

                tag.setIsbn13(rs.getString("isbn_13"));
                tag.setTagName(rs.getString("tag_name"));

                tagList.add(tag);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources();
        }
        return tagList;
    }

    @Override
    public boolean addBookTag(BookTag bookTagObjArg) {
        try {
            connection = DAOUtilities.getConnection();
            String sql = "INSERT INTO public.book_tags(isbn_13, tag_name) VALUES (?, ?);";
            stmt = connection.prepareStatement(sql);

            stmt.setString(1, bookTagObjArg.getIsbn13());
            stmt.setString(2, bookTagObjArg.getTagName());

            if (stmt.executeUpdate() != 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources();
        }
    }

    @Override
    public boolean deleteBookTag(BookTag bookTagObjArg) {
        try {
            connection = DAOUtilities.getConnection();
            String sql = "DELETE FROM public.book_tags WHERE isbn_13=?, tag_name=?;";   // one book to many tags,
            stmt = connection.prepareStatement(sql);                                    // so just the isbn_13 isnt enough

            stmt.setString(1, bookTagObjArg.getIsbn13());
            stmt.setString(2, bookTagObjArg.getTagName());

            if (stmt.executeUpdate() != 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            closeResources();
        }
    }

    private void closeResources() {
        try {
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("Could not close statement!");
            e.printStackTrace();
        }

        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            System.out.println("Could not close connection!");
            e.printStackTrace();
        }
    }

}