dao

/*
 * 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 onlinelibrary.model;

import onlinelibrary.model.bean.Book;
import onlinelibrary.model.bean.LentBook;
import onlinelibrary.model.bean.User;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Horvath
 */
public class Dao {
    /**
     * Sqlite Database file path
     */



    private static final String DB_FILE_ABSOLUTE_PATH = System.getenv("OnlineLibraryDB");
    private static final String DB_CONNECTION_STRING = "jdbc:sqlite:" + DB_FILE_ABSOLUTE_PATH;

    // SQLs
    private static final String SELECT_ADMIN_USER_SQL = "SELECT * FROM users WHERE email = ? AND password = ? AND is_admin = 1";
    private static final String SELECT_ALL_USER_SQL = "SELECT * FROM users WHERE is_admin = 0";
    private static final String SELECT_ALL_BOOK_SQL = "SELECT * FROM books";
    private static final String SELECT_ALL_LENTBOOK_SQL = "SELECT * FROM lentbooks";
    private static final String SELECT_USER_BY_ID = "SELECT * FROM users WHERE id = ?";
    private static final String SELECT_BOOK_BY_ID = "SELECT * FROM books WHERE id = ?";
    private static final String SELECT_BOOK_BY_NAME = "SELECT * FROM books WHERE name like '%?%' ";
    private static final String SELECT_BOOK_BY_AUTHOR = "SELECT * FROM books WHERE author like '%?%' ";
    private static final String SELECT_BOOK_BY_PUBLISHDATE = "SELECT * FROM books WHERE publishdate like '%?%' ";
    private static final String SELECT_BOOK_BY_IMEI = "SELECT * FROM books WHERE imei = ?";
    private static final String SELECT_FREE_BOOKS = "SELECT * FROM books WHERE piece > outpiece";
    private static final String SELECT_LENTBOOKS_BY_ID = "SELECT * FROM lentbooks WHERE id = ?";
    private static final String SELECT_USERNAME_AND_PASSWORD = "SELECT * FROM users WHERE email=? AND password=?";
    private static final String SELECT_USERNAME_AND_ADMIN = "SELECT * FROM users WHERE email=? AND is_admin=1";
    private static final String SELECT_EMAIL = "SELECT * FROM users WHERE email=?";




    // Insert SQLs
    private static final String INSERT_READER_SQL =
        "INSERT INTO users " +
        "(name, password, email, address, is_admin) " +
        "VALUES (?, ?, ?, ?, 0)"
    ;

    private static final String INSERT_BOOK_SQL =
        "INSERT INTO books " +
        "(name, author, imei, publishercity, publishername, imageurl, piece, outpiece, publishdate, tag) " +
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    ;

    private static final String INSERT_LENTBOOK_SQL =
        "INSERT INTO lentbooks " +
        "(userid, bookid, startdate, deadlinedate) " +
        "VALUES (?, ?, ?, ?)"
    ;

    // Update SQLs
    private static final String UPDATE_USER_SQL =
        "UPDATE users " +
        "SET name = ?, password = ?, email = ?, address = ? " +
        "WHERE id = ?"
    ;

    private static final String UPDATE_BOOK_SQL =
        "UPDATE books " +
        "SET name = ?, author = ?, imei = ?, publishercity = ?, publishername = ?, piece = ?, outpiece = ?, publishdate = ? " +
        "WHERE id = ?"
    ;

    // Delete Sql command
    private static final String DELETE_LENTBOOK = "DELETE FROM lentbooks WHERE id = ?";

    List<User> users = new ArrayList<User>();
    List<Book> books = new ArrayList<Book>();
    List<LentBook> lentbooks = new ArrayList<LentBook>();
    List<Book> searchbook = new ArrayList<Book>();


    public Dao() {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

     public List<Book> getSearchResult(int keres, String keyword){

        List<Book> books = new ArrayList<Book>();
        switch(keres){
            case 1:  books = getBookByName(keyword);
                     break;
            case 2:  books = getBookByAuthor(keyword);;
                     break;
            case 3:  books = getBookByPublishdate(keyword);
                     break;
        }
      return books;
    }

    public List<User> getUsers() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        users.clear();

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            st = conn.createStatement();
            rs = st.executeQuery(SELECT_ALL_USER_SQL);

            users = getUsersFromResultSet(rs);
        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return users;
    }

    public User getUserById(int id) {
        Connection conn = null;
        PreparedStatement pSt = null;
        ResultSet rs = null;
        User user = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pSt = conn.prepareStatement(SELECT_USER_BY_ID);
            pSt.setInt(1, id);
            rs = pSt.executeQuery();

            user = new User();

            while (rs.next()) {
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                user.setPassword(rs.getString("password"));
                user.setAddress(rs.getString("address"));
                user.setAdmin(rs.getInt("is_admin") == 1);
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (pSt != null) {
                    pSt.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return user;
    }


    private List<User> getUsersFromResultSet(ResultSet rs) throws SQLException {
        List<User> rvUserList = new ArrayList<>();

        while (rs.next()) {

            User user = new User();

            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setEmail(rs.getString("email"));
            user.setPassword(rs.getString("password"));
            user.setAddress(rs.getString("address"));
            user.setAdmin(rs.getInt("is_admin") == 1);

            rvUserList.add(user);
        }

        return rvUserList;
    }

    public List<Book> getBooks() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        books.clear();

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            st = conn.createStatement();
            rs = st.executeQuery(SELECT_ALL_BOOK_SQL);

            books = getBooksFromResultSet(rs);
        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return books;
    }

    public Book getBookById(int id) {
        Connection conn = null;
        PreparedStatement pSt = null;
        ResultSet rs = null;
        Book book = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pSt = conn.prepareStatement(SELECT_BOOK_BY_ID);
            pSt.setInt(1, id);
            rs = pSt.executeQuery();

            book = new Book();

            while (rs.next()) {

                book.setId(rs.getInt("id"));
                book.setName(rs.getString("name"));
                book.setAuthor(rs.getString("author"));
                book.setImei(rs.getString("imei"));
                book.setPublishercity(rs.getString("publishercity"));
                book.setPublishername(rs.getString("publishername"));
                book.setImageurl(rs.getString("imageurl"));
                book.setPiece(rs.getInt("piece"));
                book.setOutpiece(rs.getInt("outpiece"));
                book.setPublishdate(rs.getInt("publishdate"));
                book.setTag(rs.getInt("tag"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (pSt != null) {
                    pSt.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return book;
    }

    public List<Book> getBookByName(String keyword) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        searchbook.clear();

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            st = conn.prepareStatement(SELECT_BOOK_BY_NAME);
            st.setString(1,keyword);
            rs = st.executeQuery();


            searchbook = getBooksFromResultSet(rs);

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return searchbook;
    }

    public List<Book> getBookByAuthor(String keyword) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        searchbook.clear();

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            st = conn.prepareStatement(SELECT_BOOK_BY_AUTHOR);
            st.setString(1,keyword);
            rs = st.executeQuery();

            searchbook = getBooksFromResultSet(rs);

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return searchbook;
    }

    public List<Book> getBookByPublishdate(String keyword) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        searchbook.clear();

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            st = conn.prepareStatement(SELECT_BOOK_BY_PUBLISHDATE);
            st.setString(1,keyword);
            rs = st.executeQuery();

            searchbook = getBooksFromResultSet(rs);

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return searchbook;
    }

    private List<Book> getBooksFromResultSet(ResultSet rs) throws SQLException {
        List<Book> rvBookList = new ArrayList<>();

        while (rs.next()) {

            Book book = new Book();

            book.setId(rs.getInt("id"));
            book.setName(rs.getString("name"));
            book.setAuthor(rs.getString("author"));
            book.setImei(rs.getString("imei"));
            book.setPublishercity(rs.getString("publishercity"));
            book.setPublishername(rs.getString("publishername"));
            book.setImageurl(rs.getString("imageurl"));
            book.setPiece(rs.getInt("piece"));
            book.setOutpiece(rs.getInt("outpiece"));
            book.setPublishdate(rs.getInt("publishdate"));
            book.setTag(rs.getInt("tag"));

            rvBookList.add(book);
        }

        return rvBookList;
    }

    public List<LentBook> getLentBooks() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        lentbooks.clear();

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            st = conn.createStatement();
            rs = st.executeQuery(SELECT_ALL_LENTBOOK_SQL);

            lentbooks = getLentbooksFromResultSet(rs);
        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return lentbooks;
    }

    private List<LentBook> getLentbooksFromResultSet(ResultSet rs) throws SQLException {
        List<LentBook> rvLentBookList = new ArrayList<>();

        while (rs.next()) {

            LentBook lentbook = new LentBook();

            lentbook.setId(rs.getInt("id"));
            lentbook.setUser(this.getUserById(rs.getInt("userid")));
            lentbook.setBook(this.getBookById(rs.getInt("bookid")));
            lentbook.setStartdate(rs.getString("startdate"));
            lentbook.setDeadlinedate(rs.getString("deadlinedate"));

            rvLentBookList.add(lentbook);
        }

        return rvLentBookList;
    }

    public boolean addNewUser(User user) {
        boolean rvSucceeded = false;
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pst = conn.prepareStatement(INSERT_READER_SQL);
            int index = 1;
            pst.setString(index++, user.getName());
            pst.setString(index++, user.getPassword());
            pst.setString(index++, user.getEmail());
            pst.setString(index++, user.getAddress());

            int rowsAffected = pst.executeUpdate();

            if (rowsAffected == 1) {
                rvSucceeded = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return rvSucceeded;
    }

    public boolean updateUser(User user) {
        boolean rvSucceeded = false;
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pst = conn.prepareStatement(UPDATE_USER_SQL);
            int index = 1;
            pst.setString(index++, user.getName());
            pst.setString(index++, user.getPassword());
            pst.setString(index++, user.getEmail());
            pst.setString(index++, user.getAddress());
            pst.setInt(index++, user.getId());

            int rowsAffected = pst.executeUpdate();

            // csak akkor sikeres, ha valóban volt érintett sor
            if (rowsAffected == 1) {
                rvSucceeded = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return rvSucceeded;
    }

    public boolean updateBook(Book book) {
        boolean rvSucceeded = false;
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pst = conn.prepareStatement(UPDATE_BOOK_SQL);
            int index = 1;
            pst.setString(index++, book.getName());
            pst.setString(index++, book.getAuthor());
            pst.setString(index++, book.getImei());
            pst.setString(index++, book.getPublishercity());
            pst.setString(index++, book.getPublishername());
            pst.setInt(index++, book.getPiece());
            pst.setInt(index++, book.getOutpiece());
            pst.setInt(index++, book.getPublishdate());
            pst.setInt(index++, book.getId());

            int rowsAffected = pst.executeUpdate();

            // csak akkor sikeres, ha valóban volt érintett sor
            if (rowsAffected == 1) {
                rvSucceeded = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return rvSucceeded;
    }

    public boolean isBookUpdated(String imei) {
        boolean rvUploaded = false;
        Connection conn = null;
        PreparedStatement pSt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pSt = conn.prepareStatement(SELECT_BOOK_BY_IMEI);
            pSt.setString(1, imei);
            rs = pSt.executeQuery();

            if (rs.next()) {
                rvUploaded = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (pSt != null) {
                    pSt.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return rvUploaded;
    }

    public boolean addNewBook(Book book) {
        boolean rvSucceeded = false;
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pst = conn.prepareStatement(INSERT_BOOK_SQL);
            int index = 1;
            pst.setString(index++, book.getName());
            pst.setString(index++, book.getAuthor());
            pst.setString(index++, book.getImei());
            pst.setString(index++, book.getPublishercity());
            pst.setString(index++, book.getPublishername());
            pst.setString(index++, book.getImageurl());
            pst.setInt(index++, book.getPiece());
            pst.setInt(index++, book.getOutpiece());
            pst.setInt(index++, book.getPublishdate());
            pst.setInt(index++, book.getTag());

            int rowsAffected = pst.executeUpdate();

            // csak akkor sikeres, ha valóban volt érintett sor
            if (rowsAffected == 1) {
                rvSucceeded = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return rvSucceeded;
    }

    public List<Book> getFreeBooks() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        books.clear();

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            st = conn.createStatement();
            rs = st.executeQuery(SELECT_FREE_BOOKS);

            books = getBooksFromResultSet(rs);
        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return books;
    }

    public boolean addLentBook(LentBook lentbook) {
        boolean rvSucceeded = false;
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pst = conn.prepareStatement(INSERT_LENTBOOK_SQL);
            int index = 1;
            pst.setInt(index++, lentbook.getUser().getId());
            pst.setInt(index++, lentbook.getBook().getId());
            pst.setString(index++, lentbook.getStartdate());
            pst.setString(index++, lentbook.getDeadlinedate());

            int rowsAffected = pst.executeUpdate();

            Book book = lentbook.getBook();
            book.incOutPiece();

            if (rowsAffected == 1 && this.updateBook(book)) {
                rvSucceeded = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return rvSucceeded;
    }

    public boolean deleteLentBook (LentBook lentbook) {
        boolean rvSucceeded = false;
        Connection conn = null;
        PreparedStatement pst = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pst = conn.prepareStatement(DELETE_LENTBOOK);
            int index = 1;
            pst.setInt(index++, lentbook.getId());

            int rowsAffected = pst.executeUpdate();

            Book book = lentbook.getBook();
            book.decOutPiece();

            if (rowsAffected == 1 && this.updateBook(book)) {
                rvSucceeded = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return rvSucceeded;
    }

    public LentBook getLentBookById(int lentbookId) {
        Connection conn = null;
        PreparedStatement pSt = null;
        ResultSet rs = null;
        LentBook lentbook = null;

        try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);

            pSt = conn.prepareStatement(SELECT_LENTBOOKS_BY_ID);
            int index = 1;
            pSt.setInt(index++, lentbookId);
            rs = pSt.executeQuery();

            lentbook = new LentBook();

            while (rs.next()) {
                lentbook.setId(rs.getInt("id"));
                lentbook.setUser(this.getUserById(rs.getInt("userid")));
                lentbook.setBook(this.getBookById(rs.getInt("bookid")));
                lentbook.setStartdate(rs.getString("startdate"));
                lentbook.setDeadlinedate(rs.getString("deadlinedate"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (pSt != null) {
                    pSt.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return lentbook;
    }

    public boolean checkLogin(String email, String password){
	Connection conn = null;
        PreparedStatement pS = null;
        ResultSet rs = null;
	boolean value = false;

	try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);
            pS = conn.prepareStatement(SELECT_USERNAME_AND_PASSWORD);

            pS.setString(1, email);
            pS.setString(2, password);

            rs = pS.executeQuery();

            if (rs.next()) {
                value = true;
            }

	} catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (pS != null) {
                    pS.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return value;

    }

    public boolean checkEmail(String email){
	Connection conn = null;
        PreparedStatement pS = null;
        ResultSet rs = null;
	boolean value = false;

	try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);
            pS = conn.prepareStatement(SELECT_EMAIL);

            pS.setString(1, email);

            rs = pS.executeQuery();

            if (rs.next()) {
                value = true;
            }

	} catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (pS != null) {
                    pS.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return value;

    }

    public boolean isAdmin(String email){
	Connection conn = null;
        PreparedStatement pS = null;
        ResultSet rs = null;
	boolean value = false;

	try {
            conn = DriverManager.getConnection(DB_CONNECTION_STRING);
            pS = conn.prepareStatement(SELECT_USERNAME_AND_ADMIN);

            pS.setString(1, email);

            rs = pS.executeQuery();

            if (rs.next()) {
                value = true;
            }

	} catch (SQLException ex) {
            Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null ) {
                    rs.close ();
                }

            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (pS != null) {
                    pS.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }

            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(Dao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

	return value;

    }
}