jsp分页中SQL语句传参数的写法
小弟在写一个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>
[解决办法]
看看http://www.chinaunix.net/jh/19/261521.html这个帖子吧，写的非常详细
[解决办法]
现在我的这个分页要实现查询功能。
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来实现，小弟疏于此道，还望各位老兄指教一二。
------解决方案--------------------
你们的东西对我真的蛮有帮助的哦
我一定好好的参研
[解决办法]
上一页和下一页参数的传递可以通过建立一个serverlet来实现
