daily pastebin goal
43%
SHARE
TWEET

Untitled

a guest Oct 23rd, 2017 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package swen304;
  2. import java.sql.*;
  3. /*
  4.  * LibraryModel.java
  5.  * Author:
  6.  * Created on:
  7.  */
  8. import java.util.ArrayList;
  9. import java.util.List;
  10.  
  11. import javax.swing.*;
  12.  
  13. public class LibraryModel {
  14.  
  15.     private Connection con = null;
  16.  
  17.     // For use in creating dialogs and making them modal
  18.     private JFrame dialogParent;
  19.  
  20.     public LibraryModel(JFrame parent, String userid, String password) {
  21.         dialogParent = parent;
  22.         userid = "vrecicstef";
  23.         try {
  24.             Class.forName("org.postgresql.Driver");
  25.         } catch(ClassNotFoundException cnfe) {
  26.             System.out.println("Can not find the driver class: " +
  27.                     "\nEither I have not installed it properly or \n postgresql.jar file is not in my CLASSPATH)");
  28.         }
  29.  
  30.         String url = "jdbc:postgresql:"+ "//db.ecs.vuw.ac.nz/" + userid + "_jdbc";
  31.  
  32.         try{
  33.             con = DriverManager.getConnection(url,
  34.                     userid, password);
  35.             System.out.println("SUCCESSFULLY CONNECTED");
  36.         }
  37.         catch (SQLException sqlex){
  38.             System.out.println("Can not connect");
  39.             System.out.println(sqlex.getMessage() +"...");
  40.         }
  41.  
  42.     }
  43.  
  44.     public String bookLookup(int isbn) {
  45.         String QUERY = "Select * from book WHERE isbn =" + isbn + ";" ;
  46.         String BOOK = "";
  47.         int i = 0;
  48.         try {
  49.             Statement s = con.createStatement();
  50.             ResultSet rs = s.executeQuery(QUERY);
  51.  
  52.             while (rs.next()){
  53.                 i++;
  54.                 BOOK = rs.getString(2);
  55.             }
  56.         } catch (SQLException sqlex)    {
  57.             System.out.println(sqlex.getMessage());
  58.         }
  59.         if (i==0)
  60.             System.out.println("No results");
  61.  
  62.         return BOOK;
  63.     }
  64.  
  65.     public String showCatalogue() {
  66.  
  67.         String QUERY = "select isbn,title,COUNT(isbn) AS dupe  from book_author NATURAL JOIN book group by isbn,title;";
  68.         String RES = "";
  69.         // rs.getString(2);
  70.         int i = 0;
  71.  
  72.         try {
  73.             Statement s = con.createStatement();
  74.             ResultSet rs = s.executeQuery(QUERY);
  75.             while (rs.next()){
  76.                 i++;
  77.                 RES = RES + rs.getString(2) + "(" + rs.getInt(3) + ")" + "\n ISBN: " + rs.getString(1) + "\n";
  78. //              System.out.println(rs.getString(2));
  79. //              System.out.println(rs.getString(0));
  80. //              rs.getInt(3);
  81.             }
  82.         } catch (SQLException sqlex)    {
  83.             System.out.println(sqlex.getMessage());
  84.         }
  85.  
  86.             if (i==0)
  87.                 System.out.println("OUT OF BOOKS");
  88.         return RES;
  89.     }
  90.  
  91.     public String showLoanedBooks() {
  92. //      String QUERY = "Select * from cust_book;";
  93.         String QUERY =  "select isbn,duedate,customerid,title from cust_book NATURAL JOIN book;";
  94.         String loanedBook = "";
  95.         int i = 0;
  96.         try {
  97.             Statement s = con.createStatement();
  98.             ResultSet rs = s.executeQuery(QUERY);
  99.             while (rs.next()){
  100.                 i++;
  101.                  loanedBook = loanedBook + "Title:" + rs.getString(4) + "\n ISBN:" + rs.getInt(1) +
  102.                          " Customer:" + rs.getInt(3) + " DUE:" + rs.getDate(2) + "\n";
  103.  
  104.                 System.out.println(loanedBook);
  105.             }
  106.         } catch (SQLException sqlex)    {
  107.             System.out.println(sqlex.getMessage());
  108.         }
  109.         if (i==0)
  110.                 System.out.println("no books issued");
  111.  
  112.         return loanedBook;
  113.     }
  114.  
  115.     public String showAuthor(int authorID) {
  116.         String QUERY = "select * from AUTHOR where authorid = " + authorID + ";";
  117.         String auth = "";
  118.  
  119.         int i = 0;
  120.         try {
  121.             Statement s = con.createStatement();
  122.             ResultSet rs = s.executeQuery(QUERY);
  123.             while (rs.next()){
  124.                 i++;
  125.                  auth = auth + rs.getString(2) + " " + rs.getString(3);
  126.  
  127.             }
  128.         } catch (SQLException sqlex)    {
  129.             System.out.println(sqlex.getMessage());
  130.         }
  131.         if (i==0)
  132.                 System.out.println("no books issued");
  133.  
  134.  
  135.         return auth;
  136.     }
  137.  
  138.     public String showAllAuthors() {
  139.         String QR = "select * from AUTHOR;";
  140.         List<Integer> authorList = new ArrayList<Integer>();
  141.         String auth = "";
  142.         int i = 0;
  143.         try {
  144.             Statement s = con.createStatement();
  145.             ResultSet rs = s.executeQuery(QR);
  146.             while (rs.next()){
  147.                 i++;
  148.                 auth = auth + rs.getString(2) + " " + rs.getString(3) + "\n";
  149.             }
  150.         } catch (SQLException sqlex)    {
  151.             System.out.println(sqlex.getMessage());
  152.         }
  153.         if (i==0) return "NO AUTHORS!";
  154.  
  155.  
  156.         return auth;
  157.     }
  158.  
  159.     public String showCustomer(int customerID) {
  160.         String QR = "select * from customer where customerid = " + customerID + ";";
  161.         int i = 0;
  162.         String CUST = "";
  163.         try {
  164.             Statement s = con.createStatement();
  165.             ResultSet rs = s.executeQuery(QR);
  166.             while (rs.next()){
  167.                 i++;
  168.                 CUST = rs.getString(3) + " " + rs.getString(2) + "(" + rs.getInt(1) + ")" + "\n" + rs.getString(4) ;
  169.             }
  170.         } catch (SQLException sqlex)    {
  171.             System.out.println(sqlex.getMessage());
  172.         }
  173.         if (i==0) return "No customer with such ID" ;
  174.         return CUST;
  175.     }
  176.  
  177.     public String showAllCustomers() {
  178.         String QR = "select * from customer;";
  179.         String CUST = "";
  180.         int i = 0;
  181.         try {
  182.             Statement s = con.createStatement();
  183.             ResultSet rs = s.executeQuery(QR);
  184.             while (rs.next()){
  185.                 i++;
  186.                 CUST = CUST + rs.getString(3) + " " + rs.getString(2) + "(" + rs.getInt(1) + ")" + "\n" + rs.getString(4) + "\n" ;
  187.             }
  188.         } catch (SQLException sqlex)    {
  189.             System.out.println(sqlex.getMessage());
  190.         }
  191.         return CUST;
  192.     }
  193.  
  194.     public String borrowBook(int isbn, int customerID,
  195.             int day, int month, int year) {
  196.  
  197.         String QR_CheckCustomer = "Select * from customer where customerid = " + customerID + ";";
  198.         String QR_CheckBook = "Select * from book where isbn = " + isbn + ";";
  199.         String QR_Insert = "Insert into cust_book values(" +isbn + ",'" + year + "-" + month + "-" + day + "'," + customerID + ");" ;
  200.         String QR_BookCt = "Select numLeft from book where isbn = " + isbn + ";";
  201.         System.out.println(QR_Insert);
  202.         int i = 0; int j =0; int k = 0;
  203.         int current_numLeft = -1;
  204.  
  205.         // check to see if the customer already has this book issued. This library won't allow customers to issue more than one copy of the same book
  206.         String QR_CheckDup = "Select * from cust_book where customerid = " + customerID + " AND isbn = " + isbn + ";";
  207.         try {
  208.             Statement s0 = con.createStatement();
  209.             ResultSet rs0 = s0.executeQuery(QR_CheckDup);
  210.             while (rs0.next()){
  211.                 i++;
  212.             }
  213.         } catch (SQLException sqlex)    {
  214.             System.out.println("????????????");
  215.             System.out.println(sqlex.getMessage());
  216.         }
  217.         if (i > 0) return "Customer is greedy, stop trying to issue multiple copies of same book";
  218.  
  219.         try {
  220.             Statement s = con.createStatement();
  221.             ResultSet rs = s.executeQuery(QR_CheckCustomer);
  222.             while (rs.next()){
  223.                 i++;
  224.             }
  225.         } catch (SQLException sqlex)    {
  226.             System.out.println("????????????");
  227.             System.out.println(sqlex.getMessage());
  228.         }
  229.         if (i==0) return "No such customer";
  230.  
  231.         try {
  232.             Statement s2 = con.createStatement();
  233.             ResultSet rs2 = s2.executeQuery(QR_CheckBook);
  234.             while (rs2.next()){
  235.                 if (rs2.getInt(5) > 0) {
  236.                     j++;
  237.                     current_numLeft = rs2.getInt(5);
  238.                 }
  239.             }
  240.         } catch (SQLException sqlex)    {
  241.             System.out.println("????????????");
  242.             System.out.println(sqlex.getMessage());
  243.         }
  244.         if (j==0) return "No such book";
  245.         // lock customer
  246.         try {
  247.             Statement s3 = con.createStatement();
  248.             s3.executeUpdate(QR_Insert);
  249.  
  250.         } catch (SQLException sqlex)    {
  251.             System.out.println("????????????");
  252.             System.out.println(sqlex.getMessage());
  253.         }
  254.             // check that it succesfully added
  255.             // reduce a copy
  256.  
  257.         System.out.println(current_numLeft + "");
  258.         String QR_Update = "Update book set numleft = " + (current_numLeft - 1) + "where isbn = " + isbn + ";";
  259.         try {
  260.             Statement s4 = con.createStatement();
  261.             s4.executeUpdate(QR_Update);
  262.         } catch (SQLException sqlex)    {
  263.             System.out.println("????????????");
  264.             System.out.println(sqlex.getMessage());
  265.         }
  266.  
  267.         return "Succesfully borrowed book";
  268.     }
  269.  
  270.     public String returnBook(int isbn, int customerid) {
  271. //      String QR_booksLeft = "select numleft from book where isbn = " + isbn + ";";
  272.         String QR_CheckBook = "Select * from book where isbn = " + isbn + ";";
  273.         int current_numLeft = 0; int j =0;
  274. //      String QR
  275.         // update book set numleft = 5 where isbn = 9009;
  276.  
  277.         String QR_RETURN = "Delete from cust_book where customerid = " + customerid + " AND isbn = " + isbn + ";";
  278.  
  279.         try {
  280.             Statement s2 = con.createStatement();
  281.             ResultSet rs2 = s2.executeQuery(QR_CheckBook);
  282.             while (rs2.next()){
  283.                 if (rs2.getInt(5) > 0) {
  284.                     j++;
  285.                     current_numLeft = rs2.getInt(5);
  286.                 }
  287.             }
  288.         } catch (SQLException sqlex)    {
  289.             System.out.println("????????????");
  290.             System.out.println(sqlex.getMessage());
  291.         } if (j==0) return "No such book";
  292.  
  293.         current_numLeft++;
  294.             String QR_updateBooks = "update book set numleft = " + current_numLeft + " where isbn = " + isbn + ";";
  295.             try {
  296.                 Statement s4 = con.createStatement();
  297.                 s4.executeUpdate(QR_updateBooks);
  298.             } catch (SQLException sqlex)    {
  299.                 System.out.println("????????????");
  300.                 System.out.println(sqlex.getMessage());
  301.             }
  302.  
  303.             try {
  304.                 Statement s5 = con.createStatement();
  305.                 s5.executeUpdate(QR_RETURN);
  306.             } catch (SQLException sqlex)    {
  307.                 System.out.println("????????????");
  308.                 System.out.println(sqlex.getMessage());
  309.             }
  310.  
  311.         return "Succesfully returned";
  312.     }
  313.  
  314.     public void closeDBConnection() {
  315.     }
  316.  
  317.     public String deleteCus(int customerID) {
  318.         return "Delete Customer";
  319.     }
  320.  
  321.     public String deleteAuthor(int authorID) {
  322.         return "Delete Author";
  323.     }
  324.  
  325.     public String deleteBook(int isbn) {
  326.         return "Delete Book";
  327.     }
  328. }
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