op_book.java

这个是我自己写的网上书店的管理系统

	        if (!classid.equals("")) 
	        {//查询某一类
	           sqlStr = sqlStr + "and a.Bookclass='" +
	           classid + "' and (upper(a.bookname) like '%" +
	           keyword+ "%' or upper(a.content) like '%" +
	           keyword + "%')  order by a.Id desc";
	        } 
	        else 
	        {		//查询所有类
	           sqlStr = sqlStr + " and (upper(a.bookname) like '%" +
	           keyword+ "%' or upper(a.content) like '%" +
	           keyword + "%') order by a.Id desc";
	        }
	     } 
	    else 
	    {
	      if (!classid.equals(""))
	      {
	    	  if((recordCount-pageSize * (page-1))>=pageSize)     //若不是最后一页，每页显示10条记录
	    	  {
	    		  sqlStr = sqlStr + " and a.Bookclass='" +
		           classid + "' and (upper(a.bookname) like '%" +
		           keyword+ "%' or upper(a.content) like '%" +
		           keyword + "%') limit "+pageSize * (page-1)+","+pageSize;
	    		  
	    	  }
	    	  else
	    	  {
	    		  sqlStr = sqlStr + " and a.Bookclass='" +
		           classid + "' and (upper(a.bookname) like '%" +
		           keyword+ "%' or upper(a.content) like '%" +
		           keyword + "%') limit "+pageSize * (page-1)+","+
		           (recordCount-pageSize * (page-1));
	    		  
	    	  }
	           
	      } 
	      else 
	      {
	    	  if((recordCount-pageSize * (page-1))>=pageSize)     //若不是最后一页，每页显示10条记录
	    	  {
	    		  sqlStr = sqlStr + " and (upper(a.bookname) like '%" +
		            keyword+ "%' or upper(a.content) like '%" +
		            keyword + "%') limit "+pageSize * (page-1)+","+pageSize;
	    	  }
	    	  else
	    	  {
	    		  sqlStr = sqlStr + " and (upper(a.bookname) like '%" +
		            keyword+ "%' or upper(a.content) like '%" +
		            keyword + "%') limit "+pageSize * (page-1)+","+
		            (recordCount-pageSize * (page-1));
	    	  }
	           
	       }
	     }
	  } 
	  else 
	  {//非查询，也非分类浏览
	         if (page == 1)
	         {
	              sqlStr = sqlStr + "  order by a.Id desc limit 0,"+pageSize;
	          } 
	         else 
	         {
	        	 if((recordCount-pageSize * (page-1))>=pageSize)     //若不是最后一页，每页显示10条记录
	        	 {
	        		 sqlStr = sqlStr + "limit "+pageSize*(page-1)+","+pageSize;
	        	 }
	        	 else                                         //若是最后一页且不足10条记录，则只显示剩余的几条记录
	        	 {
	        		 sqlStr = sqlStr + "limit "+pageSize*(page-1)+","+(recordCount-pageSize*(page-1));
	        	 }
	             
	          }
	   }
	   try  
	   {
	              rs = sm.executeQuery(sqlStr);
	              booklist = new Vector(rscount);    //设定booklist的初始容量为rscount;
	              while (rs.next()){
	                                books book = new books();
	                                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.setPrince(rs.getFloat("prince"));
	                                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();
	                        return true;
	    }
	    catch (Exception e)
	    {
	           System.out.println(e.getMessage());
	           return false;
	    }
	 }
	        /**
	         * 完成图书添加
	         * @return
	         * @throws java.lang.Exception
	         */
	 public boolean insert() throws Exception 
	 {
	  sqlStr = "insert into book (Bookname,Bookclass,Author,Publish,Bookno,"+
	           "Content,Prince,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.getPrince() + "','";
	           sqlStr = sqlStr + abooks.getAmount() + "','";
	           sqlStr = sqlStr + abooks.getAmount() + "',";
	           sqlStr = sqlStr + "now()"+ ",'";
	           sqlStr = sqlStr + abooks.getPicture()+"')";
	                try
	                {
	                        System.out.print(sqlStr);
	                        Database db = new Database();
	                        db.connect();
	                        sm =db.conn.createStatement ();
	                        sm.execute(sqlStr);
	                        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 + "prince = '" + abooks.getPrince() + "',";
	  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();
	                        sm =db.conn.createStatement ();
	                        sm.execute(sqlStr);
	                        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();
	                          sm =db.conn.createStatement ();
	                          sm.execute(sqlStr);
	                         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();
	     sm =db.conn.createStatement ();
	     try 
	     {
	        sqlStr="select  a.id,a.bookname,a.bookclass,b.classname,a.author,"+
	        "a.publish,a.bookno,a.content,a.prince,a.amount,a.Leav_number,"+
	        "a.regtime,a.picture from book a,bookclass b where a.Bookclass="+
	        "b.Id and a.Id = " + newid ;
	                        rs = sm.executeQuery(sqlStr);
	                        if (rs.next())
	                        {	booklist = new Vector(1);   //由于单本图书，所以空间长度为1
	                                books book = new books();
	                                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.setPrince(rs.getFloat("prince"));
	                                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();
	                        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()   //构造函数
	        {
	       }
	}