新闻项目【分页模糊查询】

今天要讲的是新闻页面的分页模糊查询。由上一个博客可知，新闻页面的分页：是先给页面绑定数据，再利用sql语句进行分页。而分页模糊查询就是再次基础上根据关键字查询相关数据。

     String sql = "select * from (select a.*,rownum mid from news a where ntitle like '%"+str+"%')b where mid>=? and mid<=?";

重点代码展示：

<form action="admin.jsp" align="center">
		<input type="text" name="str">
		<input type="submit" value="查询">
	</form>
    <ul class="classlist">
     <%
     //获取模糊查询的关键字
     String str = request.getParameter("str");
     if(str==null){
    	 str = "";
     }
     //String sql = "select * from news where ntitle like '%"+str+"%' order by nid desc";
 	
 	 //注册驱动类
 	Class.forName("oracle.jdbc.driver.OracleDriver");
 	//连接数据库
 	String url = "jdbc:oracle:thin:@localhost:1521:orcl";
 	Connection con = DriverManager.getConnection(url, "scott", "tiger");
 	
 	//求出数据总条数
 	PreparedStatement ps = con.prepareStatement("select count(*) from news where ntitle like '%"+str+"%' ");
 	ResultSet rs = ps.executeQuery();
 	int count = 0;
 	if(rs.next()){
 		count = rs.getInt(1);
 	}
 	
 	int pageIndex=1;//页码
 	//接收页码
 	String index = request.getParameter("pageIndex");
 	if(index!=null){//如果接收到页码，就给页码pageIndex赋值
 		pageIndex = Integer.valueOf(index);
 	}
 	int pageSize=6;//每页的数据条数，每页6条数据
 	//计算最大页码
 	int maxPage = count/pageSize;
 	//判断能不能整除，不能整除上面还有不够一页的数据
 	if(count%pageSize!=0){
 		maxPage++;//此时为最大页码
 	}
 	
 	int start = (pageIndex-1)*pageSize+1;
 	int end = pageIndex*pageSize;
 	String sql = "select * from (select a.*,rownum mid from news a where ntitle like '%"+str+"%')b where mid>=? and mid<=?";
    
 	ps = con.prepareStatement(sql);
 	ps.setInt(1, start);
 	ps.setInt(2, end);
 	//执行sql语句
 	rs = ps.executeQuery();
 	while(rs.next()){
     %>
      <li> 
      	<a href="newspages/read_news.jsp?nid=<%=rs.getInt(1)%>">
      		<%=rs.getString("ntitle") %> 
      	</a>
      <span> 作者：<%=rs.getString("nzz") %>                                           
       &#160;&#160;&#160;&#160; <a href='newspages/update_news.jsp?nid=<%=rs.getInt(1)%>'>修改</a>
       &#160;&#160;&#160;&#160;<a href='dodelnews.jsp?nid=<%=rs.getInt(1)%>' onclick='return clickdel()'>删除</a> </span> </li>
      <li class='space'></li>
      <%} %>
      <p align="right"> 
      	<a href="admin.jsp">首页</a> 
      	<a href="admin.jsp?pageIndex=<%=pageIndex>1?pageIndex-1:1%><%
      		if(str!=null){
      			out.print("&str="+str);
      		}
      	%>">上一页</a> 
      	&nbsp;当前页数:[<%=pageIndex %>/<%=maxPage %>]&nbsp; 
      	<a href="admin.jsp?pageIndex=<%=pageIndex<maxPage?pageIndex+1:maxPage%><%
      		if(str!=null){
      			out.print("&str="+str);
      		}
      	%>">下一页</a> 
      	<a href="admin.jsp?pageIndex=<%=maxPage%>">末页</a> 
      	</p>
    </ul>

 第二个是主题分页：主题分页除了基本的分页，还需要根据新闻分类名称进行分页查询数据

重点代码展示：

 <div class="content">
      <ul class="class_date">
        <li id='class_month'> 
        <%
     	 //注册驱动类
   		Class.forName("oracle.jdbc.driver.OracleDriver");
   		//连接数据库
   		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
   		Connection con = DriverManager.getConnection(url, "scott", "tiger");
   		//查询主题
   		PreparedStatement ps = con.prepareStatement("select * from subject");
     	ResultSet rs = ps.executeQuery();
     	while(rs.next()){
        %>
        <a href='index.jsp?tid=<%=rs.getInt(1)%>'><b> <%=rs.getString(2) %> </b></a> 
        <%} %>
        </li>
      </ul>
      <ul class="classlist">
      	<%
      		//接收新闻主题
      		String id = request.getParameter("tid");
      		String countSql = "select count(*) from news";
      		String pageSql = "select * from (select a.*,rownum mid from news a)b where mid>=? and mid<=?";
      		int tid = 1;
      		if(id!=null){//要执行 根据主题查询的分页
      			tid = Integer.valueOf(id);
      			countSql = "select count(*) from news where tid="+tid;
      			pageSql = "select * from (select a.*,rownum mid from news a where tid="+tid+")b where mid>=? and mid<=?";
      		}
      		
      		//查询新闻总条数
      		ps = con.prepareStatement(countSql);
      		rs = ps.executeQuery();
      		int count = 0;
      		if(rs.next()){
      			count = rs.getInt(1);
      		}
      		
      		//获取页码
      		String index = request.getParameter("pageIndex");
      		int pageIndex = 1;//页码
      		if(index!=null){//如果接收到页码，就给页码pageIndex赋值
      			pageIndex = Integer.valueOf(index);
      		}
      		//
      		//查询新闻
      		int pageSize = 8;//每页的数据条数，每页6条数据
      		//求出最大的页码
      		int maxPage = count/pageSize;
      		if(count%pageSize!=0){
      			maxPage++;//此时为最大页码
      		}
      		int start = (pageIndex-1)*pageSize+1;
      	 	int end = pageIndex*pageSize;
      	 	
      		ps = con.prepareStatement(pageSql);
      		ps.setInt(1, start);
      		ps.setInt(2, end);
      		//执行sql语句
      		rs = ps.executeQuery();
      		while(rs.next()){
      	%>
        <li>
        <a href='newspages/news_add.html'><%=rs.getString("ntitle") %> </a>
        <span> <%=rs.getDate("ndate") %> </span>
        </li>
        <li class='space'></li>
        <%} %>
        <p align="right"> 
        	<a href="index.jsp<%
        		if(id!=null){
            		out.print("?tid="+tid);
            	}
        	%>">首页</a> 
        	<a href="index.jsp?pageIndex=<%=pageIndex>1?pageIndex-1:1%><%
        			if(id!=null){
            			out.print("&tid="+tid);
            		}
        	%>">上一页</a> 
        	&nbsp;当前页数:[<%=pageIndex %>/<%=maxPage %>]&nbsp; 
        	<a href="index.jsp?pageIndex=<%=pageIndex<maxPage?pageIndex+1:maxPage%><%
        		if(id!=null){
        			out.print("&tid="+tid);
        		}
        	%>">下一页</a> 
        	<a href="index.jsp?pageIndex=<%=maxPage%><%
        		if(id!=null){
            		out.print("&tid="+tid);
            	}
        	%>">末页</a> 
        </p>
      </ul>
    </div>

countSql = "select count(*) from news where tid="+tid;//根据tid查询新闻总条数
pageSql = "select * from (select a.*,rownum mid from news a where tid="+tid+")b where mid>=? and mid<=?";