Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * 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 librarymanagementsys;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.util.ArrayList;
- import librarymanagementsys.Book;
- import java.sql.Date;
- public class DbOperations {
- /*String url = "jdbc:mysql://localhost:3306/library";
- String userName = "root";
- String password = "";
- Connection con = null;
- PreparedStatement pst = null;
- */
- private static String url = "jdbc:mysql://localhost:3306/library";
- private static String driverName = "org.sqlite.JDBC";
- private static String username = "root";
- private static String password = "";
- private static Connection con;
- PreparedStatement pst = null;
- ResultSet rs;
- public boolean addBooks(Book bk) {
- try {
- Class.forName(driverName);
- con = DriverManager.getConnection(url, username, password);
- String qurrey = "INSERT INTO books(BookName,AuthorName,ISBNnumber,Length,isCheckedOut,CheckedOutDate,DueDate,LoanedTo) VALUES(?,?,?,?,?,?,?,?)";
- pst = con.prepareStatement(qurrey);
- // pst.setInt(1, 0);
- pst.setString(1, bk.getItemName());
- pst.setString(2, bk.getAuthorName());
- pst.setString(3, bk.getISBNnumber());
- pst.setInt(4, bk.getLoanPeriodLength());
- pst.setBoolean(5, false);
- // java.sql.Date date = new java.sql.Date(0);
- pst.setNull(6, java.sql.Types.DATE);
- pst.setNull(7, java.sql.Types.DATE);
- pst.setString(8, "");
- // pst.setDate(7,date); // pst.setDate(7,new java.sql.Date(bk.getCheckOutDate().getTime()));
- // pst.setDate(8,date); // pst.setDate(8,new java.sql.Date(bk.getDueDate().getTime()));
- // pst.setString(9,"Kasun"); // pst.setString(9, bk.getLoanedTo());
- pst.executeUpdate();
- return true;
- } catch (Exception e) {
- System.out.print(e);
- return false;
- } finally {
- try {
- if (pst != null) {
- pst.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (Exception e) {
- System.out.print(e);
- }
- }
- }
- public ArrayList<Book> getBooks() {
- try {
- // System.out.println("getBooks 1");
- ArrayList<Book> bkList = new ArrayList<Book>();
- con = DriverManager.getConnection("jdbc:sqlite:library.sqlite");
- String qurrey = "SELECT * FROM books";
- pst = con.prepareStatement(qurrey);
- // System.out.println("getBooks 2");
- rs = pst.executeQuery();
- System.out.println("getBooks 3");
- while (rs.next()) {
- Book bk = new Book();
- bk.setLibID(rs.getInt(1));
- bk.setItemName(rs.getString(2));
- bk.setAuthorName(rs.getString(3));
- bk.setCheckedOut(rs.getBoolean(6));
- System.out.println(rs.getBoolean(6));
- bk.setDueDate(rs.getDate(8));
- bkList.add(bk);
- // System.out.println("getBooks loop");
- }
- System.out.println("getBooks 4");
- return bkList;
- } catch (Exception e) {
- System.out.print(e);
- // System.out.println("getBooks exception 1");
- return null;
- } finally {
- try {
- if (pst != null) {
- pst.close();
- }
- if (con != null) {
- con.close();
- }
- //return null;
- // System.out.println("getBooks final");
- } catch (Exception e) {
- System.out.print(e);
- // System.out.println("getBooks exception 2");
- // return null;
- }
- }
- }
- public void updateCheckOut(Book bk) {
- System.out.println("updateCheckOut 1");
- java.sql.Date cksqldae = new java.sql.Date(bk.getCheckOutDate().getTime());
- java.sql.Date Dusqldae = new java.sql.Date(bk.getDueDate().getTime());
- System.out.println("updateCheckOut 2");
- try {
- con = DriverManager.getConnection("jdbc:sqlite:library.sqlite");
- System.out.println("updateCheckOut befor Qurrey");
- int temp;
- if (bk.isCheckedOut()) {
- temp = 1;
- } else {
- temp = 0;
- }
- String qurrey = "UPDATE books SET BookName ='" + bk.getItemName() + "', AuthorName='" + bk.getAuthorName()
- + "', isCheckedOut='" + temp + "', CheckedOutDate= '" + cksqldae + "', DueDate= '" + Dusqldae + "', LoanedTo = '" + bk.getLoanedTo()
- + "' WHERE BookID='" + bk.getLibID() + "'";
- pst = con.prepareStatement(qurrey);
- pst.executeUpdate();
- System.out.println("updateCheckOut after execution");
- } catch (Exception e) {
- System.out.print(e);
- } finally {
- try {
- if (pst != null) {
- pst.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (Exception e) {
- System.out.print(e);
- }
- }
- }
- public int getSelectBook(int ID) {
- try {
- System.out.println("getSelectBook 1");
- ArrayList<Book> bkList = new ArrayList<Book>();
- con = DriverManager.getConnection("jdbc:sqlite:library.sqlite");
- String qurrey = "SELECT * FROM books WHERE BookID ='" + ID + "'";
- pst = con.prepareStatement(qurrey);
- System.out.println("getSelectBook 2");
- rs = pst.executeQuery();
- System.out.println("getBooks 3");
- while (rs.next()) {
- Book bk = new Book();
- /*bk.setLibID(rs.getInt(1));
- bk.setItemName(rs.getString(2));
- bk.setAuthorName(rs.getString(3));
- bk.setCheckedOut(rs.getBoolean(6));
- System.out.println(rs.getBoolean(6));
- bk.setDueDate(rs.getDate(8));
- */
- bk.setLoanPeriodLength(rs.getInt(5));
- bkList.add(bk);
- System.out.println("getSelectBook loop");
- }
- int temp = bkList.get(0).getLoanPeriodLength();
- return temp;
- } catch (Exception e) {
- System.out.print(e);
- System.out.println("getSelectBook exception 1");
- return 124563;
- } finally {
- try {
- if (pst != null) {
- pst.close();
- }
- if (con != null) {
- con.close();
- }
- //return null;
- System.out.println("getSelectBook final");
- } catch (Exception e) {
- System.out.print(e);
- System.out.println("getSelectBook exception 2");
- // return null;
- }
- }
- }
- public void returnBook(Book bk) {
- try {
- con = DriverManager.getConnection("jdbc:sqlite:library.sqlite");
- java.sql.Date sqldate = null;
- String qurrey = "UPDATE books SET BookName ='" + bk.getItemName() + "', AuthorName='" + bk.getAuthorName()
- + "', isCheckedOut='" + 0 + "', CheckedOutDate= ?, DueDate= ?, LoanedTo = '' WHERE BookID='" + bk.getLibID() + "'";
- pst = con.prepareStatement(qurrey);
- pst.setNull(1, java.sql.Types.DATE);
- pst.setNull(2, java.sql.Types.DATE);
- pst.executeUpdate();
- } catch (Exception e) {
- System.out.print(e);
- } finally {
- try {
- if (pst != null) {
- pst.close();
- }
- if (con != null) {
- con.close();
- }
- } catch (Exception e) {
- System.out.print(e);
- }
- }
- }
- public int checkRefarance(Book bkrefarance) {
- try {
- int ID = bkrefarance.getLibID();
- System.out.println("getSelectBook 1");
- ArrayList<Book> bkList = new ArrayList<Book>();
- con = DriverManager.getConnection("jdbc:sqlite:library.sqlite");
- String qurrey = "SELECT * FROM books WHERE BookID ='" + ID + "'";
- pst = con.prepareStatement(qurrey);
- System.out.println("getSelectBook 2");
- rs = pst.executeQuery();
- System.out.println("getBooks 3");
- while (rs.next()) {
- Book bk = new Book();
- /*bk.setLibID(rs.getInt(1));
- bk.setItemName(rs.getString(2));
- bk.setAuthorName(rs.getString(3));
- bk.setCheckedOut(rs.getBoolean(6));
- System.out.println(rs.getBoolean(6));
- bk.setDueDate(rs.getDate(8));
- */
- bk.setLoanPeriodLength(rs.getInt(5));
- bkList.add(bk);
- System.out.println("getSelectBook loop");
- }
- int temp = bkList.get(0).getLoanPeriodLength();
- return temp;
- } catch (Exception e) {
- System.out.print(e);
- System.out.println("getSelectBook exception 1");
- return 124563;
- } finally {
- try {
- if (pst != null) {
- pst.close();
- }
- if (con != null) {
- con.close();
- }
- //return null;
- System.out.println("getSelectBook final");
- } catch (Exception e) {
- System.out.print(e);
- System.out.println("getSelectBook exception 2");
- // return null;
- }
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement