<%@ page language="java" %>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="oracle.sql.CLOB"%>
<%@page import="oracle.jdbc.OracleStatement"%>
<%@page import="oracle.jdbc.OracleResultSet"%>
<%@page import="java.io.BufferedReader"%>
<%@page import="oracle.jdbc.OraclePreparedStatement"%><%@ page contentType="text/html; charset=GB2312" %>
<%
//connect to datebase
Connection connect = null;
OracleResultSet oRs = null;
OracleStatement oState = null;
CLOB oclob = null; try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connect = DriverManager.getConnection(
"jdbc:oracle:thin:@133.166.200.235:1521:orajp","asset","asset");
}
catch (Exception ee) {
}
%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@page import="oracle.sql.CLOB"%>
<%@page import="oracle.jdbc.OracleStatement"%>
<%@page import="oracle.jdbc.OracleResultSet"%>
<%@page import="java.io.BufferedReader"%>
<%@page import="oracle.jdbc.OraclePreparedStatement"%><%@ page contentType="text/html; charset=GB2312" %>
<%
//connect to datebase
Connection connect = null;
OracleResultSet oRs = null;
OracleStatement oState = null;
CLOB oclob = null; try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
connect = DriverManager.getConnection(
"jdbc:oracle:thin:@133.166.200.235:1521:orajp","asset","asset");
}
catch (Exception ee) {
}
%>
解决方案 »
- 如何将字符串“12/Dec/2009:20:06:04 +0800”转化成Date类型?急!
- 如何自动生成报表?谢谢,本人对这个不了解
- 大家都来看一下这样的下载能实现吗
- 怎么处理 JSP 留言页中的 javascript 代码?
- jsp页面为循环表单,数据为同一对象的多条记录,如何存入数组,返回到后台呢?/
- 急急~~~~~~~~~~~页面刷新问题~~~~~~~~
- 2006年换工作(深圳)jsp、php、delphi
- HSSFWorkbook生成excel出现的问题
- 正则表达式 配置问题 (急!!!)
- 简述如何通过串口采集数据
- 如何实时的判断用户是在线还是离线?
- resin-2.1.5 和 iis结合的问题
* 检索结果
*
* @param subDataList 全部详细DATA LIST
* @param currPageNo
* @param recordsPerPage
* @return rltInfo 结果
**/
private static SearchResult getRslt(ArrayList subDataList,
int currPageNo,
int recordsPerPage){
/* 返回的结果 */
SearchResult rltInfo = new SearchResult();
/* 该页表示用的DATA */
ArrayList dataList = new ArrayList();
/* 该页开始记录号 */
int startNoOnCurrPage = 0;
/* 结束记录号*/
int endNoOnCurrPage = 0;
if (subDataList.size() > 0) { startNoOnCurrPage = (currPageNo - 1) * recordsPerPage + 1;
endNoOnCurrPage = (currPageNo * recordsPerPage
< subDataList.size())
? currPageNo * recordsPerPage
: subDataList.size();
for (int i = (startNoOnCurrPage - 1); i < endNoOnCurrPage;
i ++) {
dataList.add(subDataList.get(i));
}
}
rltInfo.setSrchRsltList(dataList);
rltInfo.setRecordsTotal(subDataList.size());
rltInfo.setStartNoOnCurrPage(startNoOnCurrPage);
rltInfo.setEndNoOnCurrPage(endNoOnCurrPage);
return rltInfo;
}
package mshtang.html;
/**
* <p>Title: DataBaseQuery</p>
* <p>Description: 用于数据库翻页查询操作</p>
* <p>Copyright: 厦门一方软件公司版权所有Copyright (c) 2002</p>
* <p>Company: 厦门一方软件公司</p>
* @author 小唐蔡
* @version 1.0
*/
import java.sql.*;
import javax.servlet.http.*;
import java.util.*;
import mshtang.StringAction;
public class DataBaseQuery
{ private HttpServletRequest request;
private StringAction S;
private String sql;
private String userPara;
private String[][] resultArray;
private String[] columnNameArray;
private String[] columnTypeArray;
private int pageSize;
private int columnCount;
private int currentPageNum;
private int currentPageRecordNum;
private int totalPages;
private int pageStartRecord;
private int totalRecord;
private static boolean initSuccessful;
private String currentJSPPageName;
private String displayMessage; public DataBaseQuery()
{
S = new StringAction();
sql = "";
pageSize = 10;
totalRecord = 0;
initSuccessful = false;
currentJSPPageName = "";
displayMessage = "";
columnNameArray = null;
columnTypeArray = null;
currentPageRecordNum = 0;
columnCount = 0;
} /**功能:数据库初始化操作,其它操作的前提。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
* @param pageSize:每页显示记录数;
* @param startPageNum:开始显示页码
*/
public void init(Connection conn, HttpServletRequest request, String querySQL, int pageSize, int startPageNum)
{
if(conn != null)
{
this.request = request;
this.sql = request.getParameter("querySQL");
this.userPara = request.getParameter("userPara");
if(sql == null || sql.equals(""))
{
sql = querySQL;
}
if(this.userPara == null)
{
this.userPara = "";
}
if(S.isContains(sql, "select;from", ";", true))
{
try
{
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
columnNameArray = new String[columnCount];
columnTypeArray = new String[columnCount];
String columnName;
String value;
while(rs != null && rs.next())
{
totalRecord++;
if(totalRecord == 1)
{
for(int i = 0; i < columnCount; i++)
{
columnNameArray[i] = rsmd.getColumnName(i + 1);
columnTypeArray[i] = rsmd.getColumnTypeName(i + 1);
}
}
}
if(rs != null)
{
rs.close();
}
//在总记录数大于0的情况下进行下列操作 //获取链接图象
if(totalRecord > 0 && pageSize > 0 && columnCount > 0 && startPageNum > 0)
{
//获取总页数
totalPages = totalRecord / pageSize;
int tempNum = totalRecord % pageSize;
if(tempNum != 0)
{
totalPages++;
} //获得当前页页码
String currentPage = request.getParameter("currentPageNum");
currentPageNum = (currentPage == null || currentPage.equals(""))? startPageNum:Integer.parseInt(currentPage);
currentPageNum = (currentPageNum > totalPages)?totalPages:currentPageNum;
currentPageNum = (currentPageNum <= 0)?1:currentPageNum; //获得当前页起始显示记录数
pageStartRecord = (currentPageNum - 1) * pageSize + 1;
pageStartRecord = (pageStartRecord <= 0) ? 1 : pageStartRecord;
pageStartRecord = (pageStartRecord > totalRecord) ? totalRecord : pageStartRecord; //获得当前页显示记录数
if(currentPageNum * pageSize > totalRecord)
{
currentPageRecordNum = totalRecord - (currentPageNum - 1) * pageSize;
}
else
{
currentPageRecordNum = pageSize;
}
resultArray = new String[currentPageRecordNum][columnCount]; //用于跳过前面不需显示的记录
int continueRowNum = 0; //用于跳过后面不再显示的记录
int breakRowNum = 0; ResultSet rs2 = st.executeQuery(sql);
while(rs2 != null && rs2.next())
{
//跳过前面不需显示的记录
continueRowNum++;
if(continueRowNum < pageStartRecord)
{
continue;
}
//存取当前页需显示的记录到二维数组
for(int i = 0; i < columnCount; i++)
{
value = rs2.getString(columnNameArray[i]);
value = (value == null)?"":value.trim();
resultArray[breakRowNum][i] = value;
}
//跳过后面不再显示的记录
breakRowNum++;
if(breakRowNum >= currentPageRecordNum)
{
break;
}
}
rs2.close();
}
st.close();
}
catch(SQLException e)
{
if(e != null)
{
e.printStackTrace();
}
}
}
transferSQL(sql);
initSuccessful = true;
}
} /**功能:数据库初始化操作,其它操作的前提,默认每页显示10条记录。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
* @param startPageNum:开始显示页码
*/
public void init(Connection conn, HttpServletRequest request, String querySQL, int startPageNum)
{
init(conn, request, querySQL, 10, startPageNum);
} /**功能:数据库初始化操作,其它操作的前提,默认从第一页开始显示。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
* @param pageSize:每页显示记录数;
*/
public void init(Connection conn, HttpServletRequest request, int pageSize, String querySQL)
{
init(conn, request, querySQL, pageSize, 1);
} /**功能:数据库初始化操作,其它操作的前提,默认从第一页开始显示,每页显示10条记录。
*
* @param conn:数据库连接;
* @param request:jsp页面request对象;
* @param querySQL:查询语句;
*/
public void init(Connection conn, HttpServletRequest request, String querySQL)
{
init(conn, request, querySQL, 10, 1);
} /**功能:给出没有初始化的提醒信息,内部调用。
*
*/
private static void getMessage()
{
if(!initSuccessful)
{
System.out.println("没有完成初始化");
}
} /**功能:得到查询结果的总记录数。
*
* @return
*/
public int getTotalRecord()
{
getMessage();
return totalRecord;
} /**功能:得到当前页的页码
*
* @return
*/
public int getCurrentPageNum()
{
getMessage();
return currentPageNum;
} /**功能:获得当前页记录数
*
* @return
*/
public int getCurrentPageRecord()
{
getMessage();
return currentPageRecordNum;
} /**功能:获得总页数
*
* @return
*/
public int getTotalPages()
{
getMessage();
return totalPages;
}
/**获得调用该javaBean的jsp页面文件名,用于翻页操作,可以免去外界输入页面参数的错误,用于内部调用。
*
* @return:调用该javaBean的jsp页面文件名
*/
private String getCurrentJSPPageName()
{
getMessage();
if(request != null)
{
String tempPage = request.getRequestURI();
String[] tempArray = S.stringSplit(tempPage, "/");
if(tempArray != null && tempArray.length > 0)
{
currentJSPPageName = tempArray[tempArray.length - 1];
}
}
return currentJSPPageName;
} /**功能:用于显示图片链接或字符串(上一页、下一页等链接)。用于翻页操作,内部调用
*
* @param imageSource:图片来源;
* @param i:翻页信息,1表示第一页,2表示上一页,3表示下一页,4表示尾页,
* @return:显示的链接图片或链接文字
*/
private void displayMessage(String imageSource, int i)
{
getMessage();
if(imageSource != null && !imageSource.equals(""))
{
displayMessage = "<img src=\"" + imageSource + "\" border=\"0\">";
}
else
{
switch(i)
{
case 1:
displayMessage = "<font size=\"2\">[首页]</font>";
break;
case 2:
displayMessage = "<font size=\"2\">[上一页]</font>";
break;
case 3:
displayMessage = "<font size=\"2\">[下一页]</font>";
break;
case 4:
displayMessage = "<font size=\"2\">[尾页]</font>";
}
}
} /**功能:链接到相应页面,内部调用。
*
* @param imageSource:图片来源;
* @param i:翻页信息,1表示第一页,2表示上一页,3表示下一页,4表示尾页,
* @return:相应页面的链接
*/
private String getNavigation(String imageSource, int i)
{
displayMessage(imageSource, i);
int pageNum = 0;
switch(i)
{
case 1:
pageNum = 1;
break;
case 2:
pageNum = currentPageNum - 1;
break;
case 3:
pageNum = currentPageNum + 1;
break;
case 4:
pageNum = totalPages;
}
currentJSPPageName = "<a href=\"" + currentJSPPageName + "?currentPageNum=" + pageNum + "&querySQL=" + sql + userPara + "\">" + displayMessage + "</a>";
if(((currentPageNum == 1 || totalPages == 0) && (i == 1 || i == 2)) || (currentPageNum == totalPages && (i == 3 || i == 4)))
{
currentJSPPageName = displayMessage;
}
return currentJSPPageName;
} /**功能:翻到第一页。以图片显示。
*
* @param imageSource
* @return
*/
public String firstPage(String imageSource)
{
return makePages(imageSource, 1);
} /**功能:方法重载。翻到第一页,以文字显示。
*
* @return
*/
public String firstPage()
{
return firstPage("");
} /**功能:翻到上一页,以图片显示链接。
*
* @return
*/
public String nextPage(String imageSource)
{
return makePages(imageSource, 3);
} /**功能:方法重载。翻到下一页,以文字显示。
*
* @return
*/
public String nextPage()
{
return nextPage("");
} /**功能:翻到上一页,以图片显示链接。
*
* @return
*/
public String previousPage(String imageSource)
{
return makePages(imageSource, 2);
} /**功能:方法重载。翻到上一页,以文字显示。
*
* @return
*/
public String previousPage()
{
return previousPage("");
} /**功能:方法重载。翻到尾页,以图片显示链接。
*
* @return
*/
public String lastPage(String imageSource)
{
return makePages(imageSource, 4);
} /**功能:方法重载。翻到尾页,以文字显示链接。
*
* @return
*/
public String lastPage()
{
return lastPage("");
} /**功能:得到查询记录中某字段的值。
*
* @param recordIndex:记录索引号,从0开始。
* @param columnName:字段名;
* @return:有结果返回该字段的值,否则为"";
*/
public String getColumnValue(int recordIndex, String columnName)
{
getMessage();
String columnValue = "";
if(recordIndex >=0 && recordIndex < currentPageRecordNum)
{
int columnIndex = S.findElementIndexOfArray(columnNameArray, columnName, true);
if(resultArray != null && columnIndex != -1)
{
columnValue = resultArray[recordIndex][columnIndex];
}
}
return columnValue;
} /**功能:方法重载。返回特定行特定列的值。
*
* @param recordIndex:行索引,从0开始;
* @param columnIndex:列索引,从1开始;
* @return
*/
public String getColumnValue(int recordIndex, int columnIndex)
{
getMessage();
String columnValue = "";
if(resultArray != null && recordIndex >= 0 && recordIndex < currentPageRecordNum && columnIndex > 0 && columnIndex <= columnNameArray.length)
{
columnValue = resultArray[recordIndex][columnIndex];
}
return columnValue;
}
/**功能:返回数据库字段名构成的数组
*
* @return
*/
public String[] getColumnName()
{
getMessage();
return columnNameArray;
} /**返回某一字段的类型名,不合法字段则返回空字符串""。
*
* @param columnName
* @return
*/
public String getColumnType(String columnName)
{
getMessage();
String columnType = "";
int index = S.findElementIndexOfArray(columnNameArray, columnName, true);
if(index >= 0 && index <= columnCount)
{
columnType = columnTypeArray[index];
}
return columnType;
}
/**功能:显示下面一排的翻页链接导航条,以图片显示上一页和下一页链接,中间显示数字
*
* @param previousIMage:上一页链接图片;
* @param nextImage:下一页链接图片;
* @param displayPages:中间显示的链接数;
* @return
*/
public String pagesArrange(String previousIMage, String nextImage, int displayPages)
{
getMessage();
getCurrentJSPPageName();
String pagesArrange = previousPage(previousIMage);
int startPage = 0;
int endPage = 0;
if(displayPages > 0 && displayPages <= totalPages)
{
startPage = (totalPages - displayPages > currentPageNum) ? currentPageNum : totalPages - displayPages;
if(startPage == 0)
{
startPage = 1;
}
endPage = (currentPageNum + displayPages > totalPages) ? totalPages : currentPageNum + displayPages;
}
else if(displayPages > totalPages)
{
startPage = 1;
endPage = totalPages;
}
for(int i = startPage; i <= endPage; i++)
{
getCurrentJSPPageName();
if(i == currentPageNum)
{
pagesArrange += "<font size=\"2\">" + i + "</font> ";
}
else
{
pagesArrange += "<a href=\"" + currentJSPPageName + "?currentPageNum=" + i + "&querySQL=" + sql + userPara + "\"><font size=\"2\">" + i + "</font></a> ";
}
}
pagesArrange += " " + nextPage(nextImage);
return pagesArrange;
}
/**功能:显示下面一排的翻页链接导航条,以文字显示上一页和下一页链接,中间显示数字
*
* @param displayPages:中间显示的链接数;
* @return
*/
public String pagesArrange(int displayPages)
{
return pagesArrange("", "", displayPages);
} /**功能:给出查询结果信息。模式为:共几条记录,第几页/共几页。
*
* @return
*/
public String getQueryInfo()
{
getMessage();
currentPageNum = (totalPages == 0)? 0 : currentPageNum;
String queryInfo = "<font size=\"2\">共" + totalRecord + "条记录,第" + currentPageNum + "页/共" + totalPages +"页</font>";
return queryInfo;
} /**功能:以二维数组返回所有结果。
*
* @return
*/
public String[][] getAllResult()
{
getMessage();
return resultArray;
} /**功能:得到所有记录特定列的值。
*
* @param columnIndex:列索引号,从1开始。
* @return
*/
public String[] getSpecialColumn(int columnIndex)
{
getMessage();
String[] columnValue = null;
if(columnIndex >= 1 && columnIndex <= columnNameArray.length && resultArray != null)
{
columnValue = new String[resultArray.length];
for(int i = 0; i < resultArray.length; i++)
{
columnValue[i] = resultArray[i][columnIndex - 1];
}
}
return columnValue;
} /**得到所有记录第一列值。
*
* @return
*/
public String[] getFirstColumn()
{
return getSpecialColumn(1);
} /**功能:得到查询结果中特定行的值。
*
* @param recordIndex
* @return
*/
public String[] getSpecialRow(int recordIndex)
{
getMessage();
String[] returnArray = null;
if(recordIndex < currentPageRecordNum && recordIndex >= 0 && resultArray != null)
{
returnArray = new String[columnNameArray.length];
returnArray = resultArray[recordIndex];
}
return returnArray;
} /**得到查询结果第一行的值。
*
* @return
*/
public String[] getFirstRow()
{
return getSpecialRow(0);
} /**显示图片导航条,并传递用户参数。
*
* @param firstImage:第一页链接图片;
* @param previousImage:第一页链接图片;
* @param nextImage:第一页链接图片;
* @param lastImage:第一页链接图片;
* @param userPara:用户参数。传递格式为:¶=para
* @return
*/
public String getNavigator(String firstImage, String previousImage, String nextImage, String lastImage, String userPara)
{
getMessage();
this.userPara = userPara;
String navigator = firstPage(firstImage) + " " + previousPage(previousImage) + " " + nextPage(nextImage) + " " + lastPage(lastImage);
return navigator;
} /**功能:方法重载。显示图片导航条,不传递用户参数。
*
* @param firstImage:第一页链接图片;
* @param previousImage:第一页链接图片;
* @param nextImage:第一页链接图片;
* @param lastImage:第一页链接图片;
* @return
*/
public String getNavigator(String firstImage, String previousImage, String nextImage, String lastImage)
{
return getNavigator(firstImage, previousImage, nextImage, lastImage, "");
} /**功能:方法重载。显示文字链接导航条,不传递用户参数。
*
* @return
*/
public String getNavigator()
{
return getNavigator("", "", "", "", "");
} /**功能:返回查询结果列数。
*
* @return
*/
public int getColumnCount()
{
getMessage();
return columnCount;
} private String makePages(String imageSource, int i)
{
getMessage();
getCurrentJSPPageName();
displayMessage(imageSource, i);
return getNavigation(imageSource, i);
} /**对sql查询语句进行编码转换
*
* @param SQL
*/
private void transferSQL(String SQL)
{
String[] sourceStr = {"%", "+", "/", "\\", "<", "=", ">", "(", ")", "|", "&", "?", "#", "$", "^", "{", "}", "[", "]", "~", "`", "!", ":", ";", "\"", ","};
String[] toStr = {"%25", "%2B", "%2F", "%5C", "%3C", "%3D", "%3E", "%28", "%29", "%7C", "%26", "%3F", "%23", "%24", "%5E", "%7B", "%7D", "%5B", "%5D", "%7E", "%60", "%21", "%3A", "%3B", "%22", "%2C"};
for(int i = 0; i < sourceStr.length; i++)
{
SQL = S.stringReplace(SQL, sourceStr[i], toStr[i]);
}
sql = SQL;
} /**功能:显示调试信息
*
* @return
*/
public String getDebugInfo()
{
String returnString = "";
if(initSuccessful)
{
returnString += "查询语句:" + sql;
}
else
{
returnString = "没有初始化,请用init()方法完成初始化";
}
return returnString;
} public String getStaticInfo(String firstImage, String previousImage, String nextImage, String lastImage, String userPara)
{
String returnString = "";
returnString += "<font size=\"2\">共" + totalRecord + "条记录," + getNavigator(firstImage, previousImage, nextImage, lastImage, userPara) + "第" + currentPageNum + "页/共" + totalPages + "页</font>";
return returnString;
} public String getStaticInfo(String firstImage, String previousImage, String nextImage, String lastImage)
{
return getStaticInfo(firstImage, previousImage, nextImage, lastImage, "");
} public String getStaticInfo(String userPara)
{
return getStaticInfo("", "", "", "", userPara);
} public String getStaticInfo()
{
return getStaticInfo("", "", "", "");
}
public void freeGlobalValue()
{
if(initSuccessful)
{
resultArray = null;
columnNameArray = null;
columnTypeArray = null;
pageSize = 0;
columnCount = 0;
currentPageNum = 0;
currentPageRecordNum = 0;
totalPages = 0;
pageStartRecord = 1;
totalRecord = 0;
initSuccessful = false;
currentJSPPageName = "";
displayMessage = "";
}
}
}