op_book.java~99~

基于JSP的网上书店.功能较为全面 开发环境:sql server2000+tomcat+jdk

        rscount = (int) recordCount % pageSize;	 // 最后一页记录数

        // sql为倒序取值
        sqlStr = "SELECT TOP " + (recordCount-pageSize * (page-1)) ;
        sqlStr = sqlStr + " a.id,a.bookname,a.bookclass,b.classname,"+
            "a.author,a.publish,a.bookno,a.content,a.price,a.amount,"+
            "a.leav_number,a.regtime,a.picture from book a,bookclass b " +
            //" (a.price *" +  fRate + ") AS memberPrice " +
            " where a.bookclass = b.id ";
        if( !classid.equals("") )  //如果类别不为空
        {
            sqlStr = sqlStr + " AND a.bookclass='" + classid + "' ";
        }
        if( !keyword.equals("" ) )  //如果关键字不为空
        {
            sqlStr = sqlStr + " AND  (UPPER(bookname) LIKE '%" +keyword+ "%' OR "+
                "UPPER(content) LIKE '%" + keyword + "%') ";
        }
        sqlStr = sqlStr + " ORDER BY a.Id ASC ";
        sqlStr = " SELECT TOP " + pageSize +  " * FROM ( " +  sqlStr + " ) myTable ORDER BY myTable.Id DESC " ;

        try
        {
            System.out.println(sqlStr);
            rs = db.stmt.executeQuery(sqlStr);
            booklist = new Vector(rscount);
            while (rs.next())
            {
                float fPrice = 0;
                book book = new book();
                book.setId(rs.getLong("id"));
                book.setBookName(rs.getString("bookname"));
                book.setBookClass(rs.getInt("bookclass"));
                book.setClassname(rs.getString("classname"));
                book.setAuthor(rs.getString("author"));
                book.setPublish(rs.getString("publish"));
                book.setBookNo(rs.getString("bookno"));
                book.setContent(rs.getString("content"));
                fPrice = rs.getFloat("price");
                book.setPrice(fPrice);
                book.setMemberPrice(fPrice * fRate);
                book.setAmount(rs.getInt("amount"));
                book.setLeav_number(rs.getInt("leav_number"));
                book.setRegTime(rs.getString("regtime"));
                book.setPicture(rs.getString("picture"));
                booklist.addElement(book);
            }
            rs.close();
            db.finalize();
            return true;
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            sqlStr = e.getMessage();
            db.finalize();
            return false;
        }
    }

    /**
    * 完成图书添加
    * @return
    * @throws java.lang.Exception
    */
    public boolean insert() throws Exception
    {
        sqlStr = "insert into book (Bookname,Bookclass,Author,Publish,Bookno,"+
            "Content,Price,Amount,Leav_number,Regtime,picture) values ('";
        sqlStr = sqlStr + dataFormat.toSql(abooks.getBookName()) + "','";
        sqlStr = sqlStr + abooks.getBookClass() + "','";
        sqlStr = sqlStr + dataFormat.toSql(abooks.getAuthor()) + "','";
        sqlStr = sqlStr + dataFormat.toSql(abooks.getPublish()) + "','";
        sqlStr = sqlStr + dataFormat.toSql(abooks.getBookNo()) + "','";
        sqlStr = sqlStr + dataFormat.toSql(abooks.getContent()) + "','";
        sqlStr = sqlStr + abooks.getPrice() + "','";
        sqlStr = sqlStr + abooks.getAmount() + "','";
        sqlStr = sqlStr + abooks.getAmount() + "',";
        sqlStr = sqlStr + "GETDATE()"+ ",'";
        sqlStr = sqlStr + abooks.getPicture()+"')";
        try
        {
            System.out.print(sqlStr);
            DataBase db = new DataBase();
            //db.connect();
            //stmt =db.conn.createStatement ();
            db.stmt.execute(sqlStr);
            db.finalize();
            return true;
        }
        catch (SQLException sqle)
        {
            System.out.print(sqle.getMessage());
            return false;
        }
    }
    /**
    * 完成图书修改
    * @return
    * @throws java.lang.Exception
    */
    public boolean update() throws Exception
    {
        sqlStr = "UPDATE book set ";
        sqlStr = sqlStr + "bookname = '" + dataFormat.toSql(abooks.getBookName()) + "',";
        sqlStr = sqlStr + "bookclass = '" + abooks.getBookClass() + "',";
        sqlStr = sqlStr + "Author = '" + dataFormat.toSql(abooks.getAuthor()) + "',";
        sqlStr = sqlStr + "publish = '" + dataFormat.toSql(abooks.getPublish()) + "',";
        sqlStr = sqlStr + "bookno = '" + dataFormat.toSql(abooks.getBookNo()) + "',";
        sqlStr = sqlStr + "content = '" + dataFormat.toSql(abooks.getContent()) + "',";
        sqlStr = sqlStr + "price = '" + abooks.getPrice() + "',";
        sqlStr = sqlStr + "Amount = '" + abooks.getAmount() + "',";
        sqlStr = sqlStr + "leav_number = '" + abooks.getLeav_number()+ "' ,";
        sqlStr = sqlStr + "picture = '" + abooks.getPicture() + "' ";
        sqlStr = sqlStr + "where id = " + abooks.getId();
        try
        {
            DataBase db = new DataBase();
            //db.connect();
            //stmt =db.conn.createStatement ();
            db.stmt.execute(sqlStr);
            db.finalize();
            return true;
        }
        catch (SQLException e)
        {
            System.out.print(e.getMessage());
            return false;
        }

    }
    /**
    * 完成图书删除
    * @param aid
    * @return
    * @throws java.lang.Exception
    */
    public boolean delete( int aid ) throws Exception
    {
        sqlStr = "DELETE from book where id = "  + aid ;
        try
        {
            DataBase db = new DataBase();
            //db.connect();
            //stmt =db.conn.createStatement ();
            db.stmt.execute(sqlStr);
            db.finalize();
            return true;
        }
        catch (SQLException e)
        {
            System.out.println(e);
            return false;
        }
    }
    /**
    * 完成图书单本查询，用于支持页面的查看图书详细资料
    * @param newid
    * @return
    * @throws java.lang.Exception
    */
    public boolean getOnebook(int newid ) throws Exception
    {
        DataBase db = new DataBase();
        //db.connect();
        //stmt =db.conn.createStatement ();
        try
        {
            sqlStr="select  a.id,a.bookname,a.bookclass,b.classname,a.author,"+
                "a.publish,a.bookno,a.content,a.price,a.amount,a.leav_number,"+
                "a.regtime,a.picture from book a,bookclass b where a.bookclass="+
                "b.id and a.id = " + newid ;
            rs = db.stmt.executeQuery(sqlStr);
            if (rs.next())
            {
                booklist = new Vector(1);
                book book = new book();
                book.setId(rs.getLong("id"));
                book.setBookName(rs.getString("bookname"));
                book.setBookClass(rs.getInt("bookclass"));
                book.setClassname(rs.getString("classname"));
                book.setAuthor(rs.getString("author"));
                book.setPublish(rs.getString("publish"));
                book.setBookNo(rs.getString("bookno"));
                book.setContent(rs.getString("content"));
                book.setPrice(rs.getFloat("price"));
                book.setAmount(rs.getInt("amount"));
                book.setLeav_number(rs.getInt("leav_number"));
                book.setRegTime(rs.getString("regtime"));
                book.setPicture(rs.getString("picture"));
                booklist.addElement(book);
            }
            else
            {
                rs.close();
                return false;
            }
            rs.close();
            db.finalize();
            return true;
        }
        catch (SQLException e)
        {
            return false;
        }
    }
    public int getPage() 				//显示的页码
    {
        return page;
    }
    public void setPage(int newpage)
    {
        page = newpage;
    }
    public int getPageSize()			//每页显示的图书数
    {
        return pageSize;
    }
    public void setPageSize(int newpsize)
    {
        pageSize = newpsize;
    }
    public int getPageCount() 			//页面总数
    {
        return pageCount;
    }
    public void setPageCount(int newpcount)
    {
        pageCount = newpcount;
    }
    public long getRecordCount()
    {
        return recordCount;
    }
    public void setRecordCount(long newrcount)
    {
        recordCount= newrcount;
    }
    public op_book()
    {
    }
}