SHARE
TWEET

PubHub TagDAOImpl

NexGenration Jun 29th, 2020 1,436 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package examples.pubhub.dao;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.util.ArrayList;
  8. import java.util.List;
  9.  
  10. import examples.pubhub.model.Book;
  11. import examples.pubhub.model.BookTag;
  12. import examples.pubhub.utilities.DAOUtilities;
  13.  
  14. public class TagDAOImpl implements TagDAO {
  15.     Connection connection = null; // Our connection to the database
  16.     PreparedStatement stmt = null; // We use prepared statements to help protect against SQL injection
  17.  
  18.     @Override
  19.     public List<BookTag> getAlBookTags() {
  20.         List<BookTag> tagList = new ArrayList<>();
  21.  
  22.         try {
  23.             connection = DAOUtilities.getConnection(); // Get our database connection from the manager
  24.             String sql = "SELECT * FROM public.book_tags"; // Our SQL query
  25.             stmt = connection.prepareStatement(sql); // Creates the prepared statement from the query
  26.             ResultSet rs = stmt.executeQuery(); // Queries the database
  27.  
  28.             while (rs.next()) {
  29.                 BookTag tag = new BookTag();
  30.  
  31.                 tag.setIsbn13(rs.getString("isbn_13"));
  32.                 tag.setTagName(rs.getString("tag_name"));
  33.  
  34.                 tagList.add(tag);
  35.             }
  36.             rs.close();
  37.         } catch (SQLException e) {
  38.             e.printStackTrace();
  39.         } finally {
  40.             closeResources();
  41.         }
  42.         return tagList;
  43.     }
  44.  
  45.     @Override
  46.     public List<BookTag> getBookTagsByBook(Book bookObjArg) {
  47.         List<BookTag> tagList = new ArrayList<>();
  48.  
  49.         try {
  50.             connection = DAOUtilities.getConnection(); // Get our database connection from the manager
  51.             String sql = "SELECT * FROM public.book_tags " + "WHERE isbn_13 LIKE ?"; // Our SQL query
  52.             stmt = connection.prepareStatement(sql); // Creates the prepared statement from the query
  53.             stmt.setString(1, "%" + bookObjArg.getIsbn13() + "%");
  54.             ResultSet rs = stmt.executeQuery(); // Queries the database
  55.  
  56.             while (rs.next()) {
  57.                 BookTag tag = new BookTag();
  58.  
  59.                 tag.setIsbn13(rs.getString("isbn_13"));
  60.                 tag.setTagName(rs.getString("tag_name"));
  61.  
  62.                 tagList.add(tag);
  63.             }
  64.             rs.close();
  65.         } catch (SQLException e) {
  66.             e.printStackTrace();
  67.         } finally {
  68.             closeResources();
  69.         }
  70.         return tagList;
  71.     }
  72.  
  73.     @Override
  74.     public List<BookTag> getBookTagsByName(String nameArg) {
  75.         List<BookTag> tagList = new ArrayList<>();
  76.  
  77.         try {
  78.             connection = DAOUtilities.getConnection(); // Get our database connection from the manager
  79.             String sql = "SELECT * FROM public.book_tags " + "WHERE isbn_13 LIKE ?"; // Our SQL query
  80.             stmt = connection.prepareStatement(sql); // Creates the prepared statement from the query
  81.             stmt.setString(1, "%" + nameArg + "%");
  82.             ResultSet rs = stmt.executeQuery(); // Queries the database
  83.  
  84.             while (rs.next()) {
  85.                 BookTag tag = new BookTag();
  86.  
  87.                 tag.setIsbn13(rs.getString("isbn_13"));
  88.                 tag.setTagName(rs.getString("tag_name"));
  89.  
  90.                 tagList.add(tag);
  91.             }
  92.             rs.close();
  93.         } catch (SQLException e) {
  94.             e.printStackTrace();
  95.         } finally {
  96.             closeResources();
  97.         }
  98.         return tagList;
  99.     }
  100.  
  101.     @Override
  102.     public boolean addBookTag(BookTag bookTagObjArg) {
  103.         try {
  104.             connection = DAOUtilities.getConnection();
  105.             String sql = "INSERT INTO public.book_tags(isbn_13, tag_name) VALUES (?, ?);";
  106.             stmt = connection.prepareStatement(sql);
  107.  
  108.             stmt.setString(1, bookTagObjArg.getIsbn13());
  109.             stmt.setString(2, bookTagObjArg.getTagName());
  110.  
  111.             if (stmt.executeUpdate() != 0) {
  112.                 return true;
  113.             } else {
  114.                 return false;
  115.             }
  116.         } catch (SQLException e) {
  117.             e.printStackTrace();
  118.             return false;
  119.         } finally {
  120.             closeResources();
  121.         }
  122.     }
  123.  
  124.     @Override
  125.     public boolean deleteBookTag(BookTag bookTagObjArg) {
  126.         try {
  127.             connection = DAOUtilities.getConnection();
  128.             String sql = "DELETE FROM public.book_tags WHERE isbn_13=?, tag_name=?;";   // one book to many tags,
  129.             stmt = connection.prepareStatement(sql);                                    // so just the isbn_13 isnt enough
  130.  
  131.             stmt.setString(1, bookTagObjArg.getIsbn13());
  132.             stmt.setString(2, bookTagObjArg.getTagName());
  133.  
  134.             if (stmt.executeUpdate() != 0) {
  135.                 return true;
  136.             } else {
  137.                 return false;
  138.             }
  139.         } catch (SQLException e) {
  140.             e.printStackTrace();
  141.             return false;
  142.         }finally {
  143.             closeResources();
  144.         }
  145.     }
  146.  
  147.     private void closeResources() {
  148.         try {
  149.             if (stmt != null)
  150.                 stmt.close();
  151.         } catch (SQLException e) {
  152.             System.out.println("Could not close statement!");
  153.             e.printStackTrace();
  154.         }
  155.  
  156.         try {
  157.             if (connection != null)
  158.                 connection.close();
  159.         } catch (SQLException e) {
  160.             System.out.println("Could not close connection!");
  161.             e.printStackTrace();
  162.         }
  163.     }
  164.  
  165. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top