小弟在写一个JSP调用低层方法的分页。
在SQL语句部分是这样的:
sql = "select * FROM ( SELECT A.*, ROWNUM RN FROM (select * from All_UsageReport_THISMONTH) A WHERE ROWNUM <= "+ currentPage* pageSize+") WHERE RN >= " + i;以下是JSP代码:
<%@ page contentType="text/html; charset=UTF-8"%><%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="com.trs.yellow.db.DBAccess"%>
<%@ page import="com.trs.yellow.dao.QueryLog"%>
<%@ page import="com.trs.yellow.dao.PageBean"%>
<%@ page import="java.lang.String"%>
<%@ page import="java.sql.CallableStatement"%>
<%@ page import="java.sql.DriverManager"%><%@ page import="com.trs.hxt.wcmsz.SplitString"%>
<%@ page import="com.trs.cdsx.common.*,com.trs.cdsx.toll.*"%>
<%@ page import="java.lang.String"%><script src="admin/toll/include/objs_related.js"></script><script LANGUAGE="javascript">
function submit10()
{
self.location.replace("fenye1.jsp")
}
</script><%
//String tablename = request.getParameter("tablename");
Connection conn; //数据库连接对象
PreparedStatement pstmt; //SQL语句对象
ResultSet rs; //结果集对象
String url2; //数据库连接字符串
String sql;
int totalRecords = QueryLog.getInstance().size("All_UsageReport_THISMONTH"); // 每页显示记录数量
int pageSize = 20;
// 当前页数
int currentPage = 0; if (request.getParameter("page") == null) { currentPage = 1; } else {
currentPage = Integer.parseInt(request.getParameter("page"));
} // 初始化PageBean对象
PageBean pageBean = new PageBean(totalRecords, pageSize, currentPage); // 计算当前显示页面的第一条起始记录数
int start = pageBean.getStart();
//装载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
//设置数据库连接字符串
url2 = "jdbc:oracle:thin:@203.194.142.214:1521:isinodb";
//连接数据库
conn = DriverManager.getConnection(url2,"isinolog_viewer","isino");
int i = (currentPage -1) * pageSize+1;
//sql = "select * from All_UsageReport_THISMONTH";
sql = "select * FROM ( SELECT A.*, ROWNUM RN FROM (select * from All_UsageReport_THISMONTH) A WHERE ROWNUM <= "+ currentPage* pageSize+") WHERE RN >= " + i;
//创建一个可以滚动的只读的SQL语句对象
pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//准备SQL语句
int count = 0;
//执行SQL语句并获取结果集
rs = pstmt.executeQuery();
//rs.absolute(start);
%> <form method="POST" action="querylog1.jsp">
第<%=currentPage%>页 共<%=totalRecords/pageSize%>页 <%if(currentPage<totalRecords/pageSize){%><a href="querylog1.jsp?page=<%=currentPage+1%>">下一页</a><%}%> <%if(currentPage>1){%><a href="querylog1.jsp?page=<%=currentPage-1%>">上一页</a><%}%>转到第:<input type="text" name="page" size="8"> 页
<span><input class="buttonface" type="submit" value=´GO´ name="cndok" ></span>
</form>
<%out.print(sql);%>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="top">
<table width="100%" bordercolordark="#ffffff" bordercolorlight="#4787b8" border="1" cellpadding="0" cellspacing="0">
<tr bgcolor="#4787b8" align="center">
<td width="3%" height="2"><font color="#FFFFFF"><b>序 号</b></font></td>
<td width="7%" height="2"><font color="#FFFFFF"><b>登录名</b></font></td>
<td width="8%"><font color="#FFFFFF"><b>登 录 IP</b></font></td>
<td width="27%"><font color="#FFFFFF"><b>点 击 链 接(URL)</b></font></td>
<td width="5%"><font color="#FFFFFF"><b>操 作</b></font></td>
<td width="6%"><font color="#FFFFFF"><b>用 时</b></font></td>
<td width="5%"><font color="#FFFFFF"><b>花 费</b></font></td>
<td width="15%" height="2"><font color="#FFFFFF"><b>创 建 时 间</b></font></td>
</tr><%
//while (count < pageSize){
while(rs.next()){
%>
<tr bgcolor="" align="left"> <td align="center">
<%=rs.getString("logid")%>
</td>
<td align="center"><%=rs.getString("username")%></td>
<td align="center">
<%=rs.getString("userip")%>
</td>
<%
String url = "http://www.isinolaw.com" + rs.getString("requesturi") + "?" + rs.getString("querystring");
%>
<td align="center">
<a href="<%=url%>" target=_blank> <%=SplitString.subString(url,40,"UTF-8")%></a>
</td>
<td align="center"><%=rs.getString("operation")%></td>
<td align="center"><%=rs.getString("costperiod")%></td>
<td align="center"><%=rs.getString("cost")%></td>
<td align="center"><%=rs.getString("crtime")%></td>
</tr>
<%
//rs.relative(1);
//count++;
//if (rs.isAfterLast())
// break;
}
rs.close();
pstmt.close();
conn.close();
%>
<%
out.println("totalRecords =" + totalRecords);
out.println("pageSize = " + pageSize);
out.println("currentPage = " + currentPage);
out.println("start = " + start);
out.println("count = " + count);
%>
</table>
</td>
</tr>
</table>
<table>
<tr>
<td>
<p>当前有[<%=totalRecords%>]条记录 共计<%=totalRecords/pageSize%>页,当前是<%=currentPage%>页
</td>
</tr>
<tr>
<td></td>
</tr>
</table>
</body>
</html>
上面这段代码是可以正常实现分页效果。
在SQL语句部分是这样的:
sql = "select * FROM ( SELECT A.*, ROWNUM RN FROM (select * from All_UsageReport_THISMONTH) A WHERE ROWNUM <= "+ currentPage* pageSize+") WHERE RN >= " + i;以下是JSP代码:
<%@ page contentType="text/html; charset=UTF-8"%><%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="com.trs.yellow.db.DBAccess"%>
<%@ page import="com.trs.yellow.dao.QueryLog"%>
<%@ page import="com.trs.yellow.dao.PageBean"%>
<%@ page import="java.lang.String"%>
<%@ page import="java.sql.CallableStatement"%>
<%@ page import="java.sql.DriverManager"%><%@ page import="com.trs.hxt.wcmsz.SplitString"%>
<%@ page import="com.trs.cdsx.common.*,com.trs.cdsx.toll.*"%>
<%@ page import="java.lang.String"%><script src="admin/toll/include/objs_related.js"></script><script LANGUAGE="javascript">
function submit10()
{
self.location.replace("fenye1.jsp")
}
</script><%
//String tablename = request.getParameter("tablename");
Connection conn; //数据库连接对象
PreparedStatement pstmt; //SQL语句对象
ResultSet rs; //结果集对象
String url2; //数据库连接字符串
String sql;
int totalRecords = QueryLog.getInstance().size("All_UsageReport_THISMONTH"); // 每页显示记录数量
int pageSize = 20;
// 当前页数
int currentPage = 0; if (request.getParameter("page") == null) { currentPage = 1; } else {
currentPage = Integer.parseInt(request.getParameter("page"));
} // 初始化PageBean对象
PageBean pageBean = new PageBean(totalRecords, pageSize, currentPage); // 计算当前显示页面的第一条起始记录数
int start = pageBean.getStart();
//装载JDBC驱动程序
Class.forName("oracle.jdbc.driver.OracleDriver");
//设置数据库连接字符串
url2 = "jdbc:oracle:thin:@203.194.142.214:1521:isinodb";
//连接数据库
conn = DriverManager.getConnection(url2,"isinolog_viewer","isino");
int i = (currentPage -1) * pageSize+1;
//sql = "select * from All_UsageReport_THISMONTH";
sql = "select * FROM ( SELECT A.*, ROWNUM RN FROM (select * from All_UsageReport_THISMONTH) A WHERE ROWNUM <= "+ currentPage* pageSize+") WHERE RN >= " + i;
//创建一个可以滚动的只读的SQL语句对象
pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);//准备SQL语句
int count = 0;
//执行SQL语句并获取结果集
rs = pstmt.executeQuery();
//rs.absolute(start);
%> <form method="POST" action="querylog1.jsp">
第<%=currentPage%>页 共<%=totalRecords/pageSize%>页 <%if(currentPage<totalRecords/pageSize){%><a href="querylog1.jsp?page=<%=currentPage+1%>">下一页</a><%}%> <%if(currentPage>1){%><a href="querylog1.jsp?page=<%=currentPage-1%>">上一页</a><%}%>转到第:<input type="text" name="page" size="8"> 页
<span><input class="buttonface" type="submit" value=´GO´ name="cndok" ></span>
</form>
<%out.print(sql);%>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="top">
<table width="100%" bordercolordark="#ffffff" bordercolorlight="#4787b8" border="1" cellpadding="0" cellspacing="0">
<tr bgcolor="#4787b8" align="center">
<td width="3%" height="2"><font color="#FFFFFF"><b>序 号</b></font></td>
<td width="7%" height="2"><font color="#FFFFFF"><b>登录名</b></font></td>
<td width="8%"><font color="#FFFFFF"><b>登 录 IP</b></font></td>
<td width="27%"><font color="#FFFFFF"><b>点 击 链 接(URL)</b></font></td>
<td width="5%"><font color="#FFFFFF"><b>操 作</b></font></td>
<td width="6%"><font color="#FFFFFF"><b>用 时</b></font></td>
<td width="5%"><font color="#FFFFFF"><b>花 费</b></font></td>
<td width="15%" height="2"><font color="#FFFFFF"><b>创 建 时 间</b></font></td>
</tr><%
//while (count < pageSize){
while(rs.next()){
%>
<tr bgcolor="" align="left"> <td align="center">
<%=rs.getString("logid")%>
</td>
<td align="center"><%=rs.getString("username")%></td>
<td align="center">
<%=rs.getString("userip")%>
</td>
<%
String url = "http://www.isinolaw.com" + rs.getString("requesturi") + "?" + rs.getString("querystring");
%>
<td align="center">
<a href="<%=url%>" target=_blank> <%=SplitString.subString(url,40,"UTF-8")%></a>
</td>
<td align="center"><%=rs.getString("operation")%></td>
<td align="center"><%=rs.getString("costperiod")%></td>
<td align="center"><%=rs.getString("cost")%></td>
<td align="center"><%=rs.getString("crtime")%></td>
</tr>
<%
//rs.relative(1);
//count++;
//if (rs.isAfterLast())
// break;
}
rs.close();
pstmt.close();
conn.close();
%>
<%
out.println("totalRecords =" + totalRecords);
out.println("pageSize = " + pageSize);
out.println("currentPage = " + currentPage);
out.println("start = " + start);
out.println("count = " + count);
%>
</table>
</td>
</tr>
</table>
<table>
<tr>
<td>
<p>当前有[<%=totalRecords%>]条记录 共计<%=totalRecords/pageSize%>页,当前是<%=currentPage%>页
</td>
</tr>
<tr>
<td></td>
</tr>
</table>
</body>
</html>
上面这段代码是可以正常实现分页效果。
解决方案 »
- PreparedStatement的Big问题!
- Jsp+utf-8+get+小方格+乱码+URIEncoding
- jsp+ajax,怎么我的返回值中有那么多空行?
- oracle分页的问题,请帮忙
- JSP打印报表的问题。
- 急求jspSmartUpload.zip
- 请教 如何在 Struts 中使用 下载 ?会做的高手指点一下.最好有代码说明一下..谢谢
- Injection of resource dependencies failed
- 文件操作,高手请进!在线!!!
- Spring4与Hibernate4整合时出现的问题
- installanyshere怎么打包WEB程序?
- 请教如何开发出一套类似ASP PHP JSP这样的系统来?
sql语句要传递参数,新的sql语句如下:
tablename = [当前需要查询的表];
begindate = [起始时间];
enddate = [结束时间];
str = " select * from " + tablename + " where username = '" + username + "' and (crtime between to_date('" + begindate + "','yyyy-mm-dd') and to_date('" + enddate + "','yyyy-mm-dd')) order by crtime desc ";
sql = "select * FROM ( SELECT A.*, ROWNUM RN FROM ("+ str +") A WHERE ROWNUM <= "+ currentPage* pageSize+") WHERE RN >= " + i;sql语句这样的改变后,分页效果不能实现。
据说是,sql语句这样的参数传递需要用js来实现,小弟疏于此道,还望各位老兄指教一二。
=================
要实现什么js调用压根就没说嘛!