return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books獲取BookType方法/////////////////////////////////////////////////
    public ArrayList getBooksType() {
        ArrayList list = new ArrayList();
        try {
            String sql =
                    "select * from BookType";
            prepar = conn.prepareStatement(sql);
            res = prepar.executeQuery();
            while (res.next()) {
                BooksTypeBean btb = new BooksTypeBean();
                btb.setBookType_ID(res.getInt(1));
                btb.setBookType_Name(res.getString(2));
                list.add(btb);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books獲取列表方法/////////////////////////////////////////////////
    public ArrayList getBooksList(int count, int page, String sqlstr) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call BooksPage (?,?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.setString(3, sqlstr);
            proc.registerOutParameter(4, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                BooksBean bsb = new BooksBean();
                bsb.setBooks_Amount(res.getInt("Books_Amount"));
                bsb.setBooks_Company(res.getString("Books_Company"));
                bsb.setBooks_Count(res.getInt("Books_Count"));
                bsb.setBooks_ID(res.getInt("Books_ID"));
                bsb.setBooks_Name(res.getString("Books_Name"));
                bsb.setBooks_Time(res.getString("Books_Time"));
                bsb.setBookType_ID(res.getInt("BookType_ID"));
                list.add(bsb);
            }
            pagecount = proc.getInt(4);
        } catch (SQLException ex) {
            System.out.println("服務(wù)器異常發(fā)生在 getBooksList()");
            ex.printStackTrace();
        }
        return list;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books獲取Book持久化方法/////////////////////////////////////////////////
    public BooksBean getBooksBean(int books_ID) {
        BooksBean bsb = new BooksBean();
        try {
            String sql =
                    "select * from Books where Books_ID=?";
            prepar = conn.prepareStatement(sql);
            prepar.setInt(1, books_ID);
            res = prepar.executeQuery();
            if (res.next()) {
                bsb.setBooks_Amount(res.getInt("Books_Amount"));
                bsb.setBooks_Company(res.getString("Books_Company"));
                bsb.setBooks_Count(res.getInt("Books_Count"));
                bsb.setBooks_ID(res.getInt("Books_ID"));
                bsb.setBooks_Name(res.getString("Books_Name"));
                bsb.setBooks_Time(res.getString("Books_Time"));
                bsb.setBookType_ID(res.getInt("BookType_ID"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return bsb;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books執(zhí)行借書(shū)方法/////////////////////////////////////////////////
    public String lendBooks(LendBean lb) {
        try {
            proc = conn.prepareCall("{call LendBook (?,?,?,?,?)}");
            proc.setInt(1, lb.getUsers_ID());
            proc.setInt(2, lb.getBooks_ID());
            proc.setString(3, lb.getBookLend_StarTime());
            proc.setString(4, lb.getBookLend_EndTime());
            proc.registerOutParameter(5, Types.VARCHAR);
            proc.executeUpdate();
            return proc.getString(5);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return "借書(shū)失敗";
    }

    ////////////////獲取日期之差
    public int getDateCha(String stra, String end) {
        try {
            String sql =
                    "select datediff(day,?,?)";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, stra);
            prepar.setString(2, end);
            res = prepar.executeQuery();
            if (res.next()) {
                return res.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books錄入方法/////////////////////////////////////////////////
    public boolean insertBooks(BooksBean bsb) {
        try {
            prepar = conn.prepareStatement(
                    "insert into Books (Books_Name,Books_Company,Books_Time,BookType_ID) values(?,?,?,?)");
            prepar.setString(1, bsb.getBooks_Name());
            prepar.setString(2, bsb.getBooks_Company());
            prepar.setString(3, MyDate.getDate());
            prepar.setInt(4, bsb.getBookType_ID());
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books刪除方法/////////////////////////////////////////////////
    public boolean deleteBooks(int books_ID) {
        try {
            prepar = conn.prepareStatement(
                    "delete Books where Books_ID=?");
            prepar.setInt(1, books_ID);
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books修改方法/////////////////////////////////////////////////
    public boolean updateBooks(BooksBean bsb) {
        try {
            prepar = conn.prepareStatement(
                    "update Books set Books_Name=?,Books_Company=?,Books_Count=?,BookType_ID=? where Books_ID=?");
            prepar.setString(1, bsb.getBooks_Name());
            prepar.setString(2, bsb.getBooks_Company());
            prepar.setInt(3, bsb.getBooks_Count());
            prepar.setInt(4, bsb.getBookType_ID());
            prepar.setInt(5, bsb.getBooks_ID());
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Books歸還方法/////////////////////////////////////////////////
    public String giveBook(LendBean lb) {
        try {
            proc = conn.prepareCall("{call GiveBook (?,?,?,?,?,?)}");
            proc.setInt(1, lb.getUsers_ID());
            proc.setInt(2, lb.getBooks_ID());
            proc.setString(3, lb.getBookLend_StarTime());
            proc.setString(4, lb.getBookLend_EndTime());
            proc.setString(5, MyDate.getDate());
            proc.registerOutParameter(6, Types.VARCHAR);
            proc.executeUpdate();
            return proc.getString(6);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return "";
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Lend獲取借書(shū)信息方法/////////////////////////////////////////////////
    public LendBean getLendBean(int lend_ID) {
        LendBean lb = new LendBean();
        try {
            String sql =
                    "select * from BookLend where BookLend_ID=?";
            prepar = conn.prepareStatement(sql);
            prepar.setInt(1, lend_ID);
            res = prepar.executeQuery();
            if (res.next()) {
                lb.setBookLend_ID(res.getInt(1));
                lb.setUsers_ID(res.getInt(2));
                lb.setBooks_ID(res.getInt(3));
                lb.setBookLend_StarTime(res.getString(4));
                lb.setBookLend_EndTime(res.getString(5));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lb;
    }

    public LendBean getLendBean(int users_ID, int books_ID) {
        LendBean lb = new LendBean();
        try {
            String sql =
                    "select * from BookLend where Users_ID=? and Books_ID=?";
            prepar = conn.prepareStatement(sql);
            prepar.setInt(1, users_ID);
            prepar.setInt(2, books_ID);
            res = prepar.executeQuery();
            if (res.next()) {
                lb.setBookLend_ID(res.getInt(1));
                lb.setUsers_ID(res.getInt(2));
                lb.setBooks_ID(res.getInt(3));
                lb.setBookLend_StarTime(res.getString(4));
                lb.setBookLend_EndTime(res.getString(5));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lb;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Lend獲取借書(shū)列表方法/////////////////////////////////////////////////
    public ArrayList getLendList(int count, int page) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call LendPage (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                LendBean lb = new LendBean();
                lb.setBookLend_EndTime(res.getString("BookLend_EndTime"));
                lb.setBookLend_ID(res.getInt("BookLend_ID"));
                lb.setBookLend_StarTime(res.getString("BookLend_StarTime"));
                lb.setBooks_ID(res.getInt("Books_ID"));
                lb.setUsers_ID(res.getInt("Users_ID"));
                list.add(lb);
            }
            pagecount = proc.getInt(3);
        } catch (SQLException ex) {
            System.out.println("服務(wù)器異常發(fā)生在 getLendList()");
            ex.printStackTrace();
        }
        return list;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Punish獲取罰單列表方法/////////////////////////////////////////////////
    public ArrayList getPunishList(int count, int page) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call PunishPageAdmin (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                PunishBean pb = new PunishBean();
                pb.setBooks_ID(res.getInt("Books_ID"));
                pb.setPunish_Day(res.getInt("Punish_Day"));
                pb.setPunish_ID(res.getInt("Punish_ID"));
                pb.setPunish_Money(res.getInt("Punish_Money"));
                pb.setUsers_ID(res.getInt("Users_ID"));
                list.add(pb);
            }
            pagecount = proc.getInt(3);
        } catch (SQLException ex) {
            System.out.println("服務(wù)器異常發(fā)生在 getPunishList()");
            ex.printStackTrace();
        }
        return list;
    }

    public ArrayList getUserPunishList(int count, int page, int users_ID) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call PunishPage (?,?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.setInt(3, users_ID);
            proc.registerOutParameter(4, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                PunishBean pb = new PunishBean();
                pb.setBooks_ID(res.getInt("Books_ID"));
                pb.setPunish_Day(res.getInt("Punish_Day"));
                pb.setPunish_ID(res.getInt("Punish_ID"));
                pb.setPunish_Money(res.getInt("Punish_Money"));
                pb.setUsers_ID(res.getInt("Users_ID"));
                list.add(pb);
            }
            pagecount = proc.getInt(4);
        } catch (SQLException ex) {
            System.out.println("服務(wù)器異常發(fā)生在 getPunishList()");
            ex.printStackTrace();
        }
        return list;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Punish付款后刪除方法/////////////////////////////////////////////////
    public boolean deletePunish(int punish_ID) {
        try {
            prepar = conn.prepareStatement(
                    "delete Punish where Punish_ID=?");
            prepar.setInt(1, punish_ID);
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
}