分页和模糊查询
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加主题--管理后台</title>
<link href="CSS/admin.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header">
<div id="welcome">欢迎使用新闻管理系统!</div>
<div id="nav">
<div id="logo">
<img src="images/logo.jpg" alt="新闻中国" />
</div>
<div id="a_b01">
<img src="images/a_b01.gif" alt="" />
</div>
</div>
</div>
<div id="admin_bar">
<div id="status">
管理员:
<%=session.getAttribute("uname")%>
     <a href="index.jsp">新闻中国</a>
</div>
<div id="channel"></div>
</div>
<div id="main">
<div id="opt_list">
<ul>
<li><a href="newspages/add_news.jsp">添加新闻</a></li>
<li><a href="#">编辑新闻</a></li>
<li><a href="#">查找新闻</a></li>
<li><a href="#">添加主题</a></li>
<li><a href="修改主题.jsp">编辑主题</a></li>
</ul>
</div>
<div id="opt_area">
<script type="text/javascript">
function clickdel() {
return confirm("删除请点击确认");
}
</script>
<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 = "";
}
//连接数据库,查询新闻新
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
Connection con = DriverManager.getConnection(url, "scott", "wearegood000");
//求出数据总条数
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 = 7;//每页的数据条数,每页5条数据
//计算最大页码
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);
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") %>
     <a href='newspages/up_news.jsp?nid=<%=rs.getInt(1)%>'>修改</a>     
<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>
当前页数:[<%=pageIndex %>/<%=maxPage %>]
<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>
</div>
<div id="site_link">
<a href="#">关于我们</a><span>|</span> <a href="#">Aboue Us</a><span>|</span>
<a href="#">联系我们</a><span>|</span> <a href="#">广告服务</a><span>|</span>
<a href="#">供稿服务</a><span>|</span> <a href="#">法律声明</a><span>|</span>
<a href="#">招聘信息</a><span>|</span> <a href="#">网站地图</a><span>|</span>
<a href="#">留言反馈</a>
</div>
<div id="footer">
<p class="">
24小时客户服务热线:010-68988888      <a href="#">常见问题解答</a>
     新闻热线:010-627488888<br />
文明办网文明上网举报电话:010-627488888      举报邮箱:<a href="#">jubao@jb-aptech.com.cn</a>
</p>
<p class="copyright">
Copyright © 1999-2009 News China gov, All Right Reserver<br />
新闻中国 版权所有
</p>
</div>
</body>
</html>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link href="CSS/main.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header">
<div id="top_login">
<label> 登录名 </label>
<input type="text" id="uname" value="" class="login_input" />
<label> 密  码 </label>
<input type="password" id="upwd" value="" class="login_input" />
<input type="button" class="login_sub" value="登录" onclick="login()"/>
<label id="error"> </label>
<img src="Images/friend_logo.gif" alt="Google" id="friend_logo" /> </div>
<div id="nav">
<div id="logo"> <img src="Images/logo.jpg" alt="新闻中国" /> </div>
<div id="a_b01"> <img src="Images/a_b01.gif" alt="" /> </div>
<!--mainnav end-->
</div>
</div>
<div id="container">
<div class="sidebar">
<h1> <img src="Images/title_1.gif" alt="国内新闻" /> </h1>
<div class="side_list">
</div>
</div>
<div class="main">
<div class="class_type"> <img src="Images/class_type.gif" alt="新闻中心" /> </div>
<div class="content">
<ul class="class_date">
<%
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
Connection con = DriverManager.getConnection(url, "scott", "wearegood000");
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>
<%} %>
</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 = Integer.valueOf(index);
}
//查询新闻
int pageSize = 5;
//求出最大页码
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);
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>
当前页数:[<%=pageIndex %>/<%=maxPage %>]
<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>
<div class="picnews">
<ul>
<li> <a href="#"><img src="images/Picture1.jpg" width="249" alt="" /> </a><a href="#">幻想中穿越时空</a> </li>
<li> <a href="#"><img src="images/Picture2.jpg" width="249" alt="" /> </a><a href="#">国庆多变的发型</a> </li>
<li> <a href="#"><img src="images/Picture3.jpg" width="249" alt="" /> </a><a href="#">新技术照亮都市</a> </li>
<li> <a href="#"><img src="images/Picture4.jpg" width="249" alt="" /> </a><a href="#">群星闪耀红地毯</a> </li>
</ul>
</div>
</div>
</div>
<div id="friend">
<h1 class="friend_t"> <img src="Images/friend_ico.gif" alt="合作伙伴" /> </h1>
<div class="friend_list">
</div>
</div>
<div id="footer">
<p class=""> 24小时客户服务热线:010-68988888      <a href="#">常见问题解答</a>      新闻热线:010-627488888 <br />
文明办网文明上网举报电话:010-627488888      举报邮箱: <a href="#">jubao@jb-aptech.com.cn</a> </p>
<p class="copyright"> Copyright © 1999-2009 News China gov, All Right Reserver <br />
新闻中国 版权所有 </p>
</div>
</body>
</html>