importjava.sql.Connection;
importjava.sql.DatabaseMetaData;
importjava.sql.SQLException;
/**
*分页语句
*1oracle
*2sqlserver
*3mysql
*4db2
*思路:
*查询分页语句,先根据当前页数+每页记录数确定起始行+终止行
*根据起始行+终止行,确定
*@Title:SQLBuilder.java
*@Packagecom.jiuqi.external.stockintf.util
*@Description:TODO
*@Company:北京久其软件股份有限公司
*@authorshijianhang
*@date2010-12-24上午09:48:06
*@versionV1.0
*/
publicclassSQLBuilder{
privateStringselectParma;//select的字段
privateStringwhereParam;//where的条件
privateStringtable;//表名
privateStringkey;//主键名
privateStringorder;//排序
privatebooleanasc;//升序or降序
/**
*构造函数
*
*@paramselectParma查询的字段，以逗号分隔
*@paramwhereParam查询条件
*@paramtable表名
*@paramkey主键
*/
publicSQLBuilder(String_selectParma,String_whereParam,String_table,String_key,String_order,boolean_asc){
selectParma=_selectParma;
whereParam=_whereParam;
table=_table;
key=_key;
order=_order;
asc=_asc;
//调整空值
if(whereParam==null||"".equals(whereParam)){
whereParam="1=1";
}
if(order==null||"".equals(order)){
order=key;
}
}
/**
*构造函数--没有指定排序顺序，默认为升序
*
*@paramselectParma查询的字段，以逗号分隔
*@paramwhereParam查询条件
*@paramtable表名
*@paramkey主键
*@param_order顺序
*/
publicSQLBuilder(String_selectParma,String_whereParam,String_table,String_key,String_order){
selectParma=_selectParma;
whereParam=_whereParam;
table=_table;
key=_key;
order=_order;
asc=true;//默认升序
//调整空值
if(whereParam==null||"".equals(whereParam)){
whereParam="1=1";
}
if(order==null||"".equals(order)){
order=key;
}
}
/**
*构造简单的sql语句
*
*@return
*/
publicStringbuildSQL(){
StringBufferstr=newStringBuffer();
//select*fromtable1whereid=?
str.append("SELECT").append(selectParma).append("FROM").append(table).append("WHERE").append(whereParam).append("ORDERBY").append(order).append("").append(asc?"ASC":"DESC");
returnstr.toString();
}
/**
*构造指定行数内的sql语句
*
*@paramconn
*@paramcurrPage
*@paramnumPerPage
*@return
*/
publicStringbuildRowSQL(Connectionconn,intstartNum,intendNum){
//构造分页sql
StringpageSql=null;
try{
DatabaseMetaDatadbmd=conn.getMetaData();
Stringdbname=dbmd.getDatabaseProductName().toLowerCase();
if(dbname.indexOf("oracle")!=-1){//后台数据库为Oracle
pageSql=buildOracleRowSQL(startNum,endNum);
returnpageSql;
}elseif(dbname.indexOf("sqlserver")!=-1||dbname.indexOf("sqlserver")!=-1){//后台数据库为SqlServer
pageSql=buildSqlserverRowSQL(startNum,endNum);
returnpageSql;
}elseif(dbname.indexOf("mysql")!=-1){//后台数据库为MySql
pageSql=buildMysqlRowSQL(startNum,endNum);
returnpageSql;
}elseif(dbname.indexOf("db2")!=-1){//后台数据库为DB2
pageSql=buildDB2RowSQL(startNum,endNum);
returnpageSql;
}
}catch(SQLExceptione){
e.printStackTrace();
}
returnpageSql;
}
/**
*构造分页的sql语句
*
*@paramconn
*@paramcurrPage
*@paramnumPerPage
*@return
*/
publicStringbuildPageSQL(Connectionconn,intcurrPage,intnumPerPage){
//计算分页的起始行数
intstartNum=numPerPage*(currPage-1)+1;//起始行
intendNum=currPage*numPerPage;//终止行
//构造分页sql
StringpageSql=null;
try{
DatabaseMetaDatadbmd=conn.getMetaData();
Stringdbname=dbmd.getDatabaseProductName().toLowerCase();
if(dbname.indexOf("oracle")!=-1){//后台数据库为Oracle
pageSql=buildOracleRowSQL(startNum,endNum);
returnpageSql;
}elseif(dbname.indexOf("sqlserver")!=-1||dbname.indexOf("sqlserver")!=-1){//后台数据库为SqlServer
pageSql=buildSqlserverRowSQL(startNum,endNum);
returnpageSql;
}elseif(dbname.indexOf("mysql")!=-1){//后台数据库为MySql
pageSql=buildMysqlRowSQL(startNum,endNum);
returnpageSql;
}elseif(dbname.indexOf("db2")!=-1){//后台数据库为DB2
pageSql=buildDB2RowSQL(startNum,endNum);
returnpageSql;
}
}catch(SQLExceptione){
e.printStackTrace();
}
returnpageSql;
}
/**
*(1)oracle查询第startNum行到第endNum行的记录
*
*@paramstartNum
*@paramendNum
*@return
*/
privateStringbuildOracleRowSQL(intstartNum,intendNum){
//select*FROM(SELECTA.*,ROWNUMRNFROM(SELECT*FROM表whereid='root'orderbytorder)AWHEREROWNUM<=终止行号)whereRN>=起始行号
//sql（SELECT*FROM表whereid='root'orderbytorder）不用修改，直接在其上封装两个select并限定行数
Stringsql=buildSQL();
StringBufferstr=newStringBuffer();
str.append("SELECT*FROM(SELECTt.*,ROWNUMRNFROM(").append(sql).append(")tWHEREROWNUM<=").append(endNum).append(")whereRN>=").append(startNum);
//SELECT*FROM(SELECTt.*,ROWNUMRNFROM(select*fromt_filewhereid='AAA'orderbyf_order)tWHEREROWNUM<=6)whereRN>=4
returnstr.toString();
}
/**
*(2)sqlserver查询第startNum行到第endNum行的记录
*
*@paramstartNum
*@paramendNum
*@return
*/
privateStringbuildSqlserverRowSQL(intstartNum,intendNum){
//(1)ROW_NUMBER()OVER(ORDERBY表.排序)--适用于sqlserver2005以上
//SELECT*FROM(SELECTTOP30*,ROW_NUMBER()OVER(ORDERBY表.排序)ASposFROM表)tWHEREt.pos>20ANDt.pos<=30
StringBufferstr=newStringBuffer();
StringBuffersql=newStringBuffer();
sql.append("SELECT").append(selectParma).append(",ROW_NUMBER()OVER(ORDERBY").append(table).append(".").append(order).append("").append(asc?"ASC":"DESC").append(")ASposFROM").append(table).append("where").append(whereParam);
str.append("SELECT*FROM(").append(sql).append(")tWHEREt.pos>=").append(startNum).append("ANDt.pos<=").append(endNum);
//SELECT*FROM(SELECT*,ROW_NUMBER()OVER(ORDERBYt_file.f_order)ASposFROMt_filewhereid='AAA'orderbyf_order)tWHEREt.pos>=4ANDt.pos<=6
/*
//(2)排序的颠倒
//SELECT*FROM(SELECTTOP10*FROM(SELECTTOP20*FROM表ORDERBY排序字段ASC)tORDERBY排序字段DESC)ORDERBY排序字段ASC
StringBuffersql=newStringBuffer();
StringBufferstr=newStringBuffer();
intnumPerPage=endNum-startNum;
sql.append("SELECTTOP").append(endNum).append("").append(selectParma).append("FROM").append(table).append("WHERE").append(whereParam).append("ORDERBY").append(order).append("").append(asc?"ASC":"DESC");
str.append("SELECT*FROM(SELECTTOP").append(numPerPage).append("*FROM(").append(sql).append(")tORDERBY").append(order).append("").append(asc?"DESC":"ASC").append(")ORDERBY").append(order).append("").append(asc?"ASC":"DESC");
//SELECT*FROM(SELECTTOP3*FROM(SELECTTOP6*FROMt_fileWHEREid='AAA'ORDERBYf_orderASC)tORDERBYf_orderDESC)ORDERBYf_orderASC
*/
returnstr.toString();
}
/**
*(3)mySql查询第startNum行到第endNum行的记录
*
*@paramstartNum
*@paramendNum
*@return
*/
privateStringbuildMysqlRowSQL(intstartNum,intendNum){
//select*fromtableWHERE…LIMIT10,20;#返回第10-20行数据
Stringsql=buildSQL();
StringBufferstr=newStringBuffer();
str.append(sql).append("limit").append(startNum).append(",").append(endNum);
//select*fromt_filewhereid='AAA'orderbyf_orderlimit4,6
returnstr.toString();
}
/**
*(4)db2查询第startNum行到第endNum行的记录
*
*@paramstartNum
*@paramendNum
*@return
*/
privateStringbuildDB2RowSQL(intstartNum,intendNum){
//与sqlserver的第二种方式差不多
StringBufferstr=newStringBuffer();
StringBuffersql=newStringBuffer();
sql.append("SELECT").append(selectParma).append(",ROW_NUMBER()OVER()ASposFROM").append(table).append("where").append(whereParam).append("orderby").append(order).append("").append(asc?"ASC":"DESC");
str.append("SELECT*FROM(").append(sql).append(")AStWHEREt.pos>=").append(startNum).append("ANDt.pos<=").append(endNum);
//SELECT*FROM(SELECT*,ROW_NUMBER()OVER()ASposFROMt_filewhereid='AAA'orderbyf_order)AStWHEREt.pos>=4ANDt.pos<=6
returnstr.toString();
}
//测试
publicstaticvoidmain(String[]args){
//selecttop3*fromt_filewhereid='AAA'WHEREidNOTIN(SELECTTOP6idFROMt_filewhereid='AAA')
//selecttop*from(select*fromt_filewhereid='AAA')A
SQLBuildersb=newSQLBuilder("id,name,age","name='shijianhang'","student","id","id");
//System.out.println(sb.buildOracleRowSQL(2,3));
//System.out.println(sb.buildSqlserverRowSQL(2,3));
//System.out.println(sb.buildMysqlRowSQL(2,3));
System.out.println(sb.buildDB2RowSQL(1,7));
}
}
