解决hibernate+SqlServer分页问题
先来个sqlserver2000版.
在项目中选择了SqlServer,发现大数据量分页时,页值越大,分页越慢,大刀向Hibernate源码砍去。自己来实现Dialect
先来个2000版本的。
package com.cat.xtools.data;
import org.hibernate.dialect.SQLServerDialect;
/**
* 实现MsSql的limit
* @author FatCat
*
*/
public class MsSqlDialect extends SQLServerDialect{
public MsSqlDialect() {
super();
// TODO Auto-generated constructor stub
}
public String getLimitString(String sql,int offset,int limit)
{
if(offset==0)
{
return super.getLimitString(sql,0,limit);
}
String sqlTemp=super.getLimitString(sql,0,limit);
//DAOImpl.
String orderby=this.getOrderby(sql);
sqlTemp=super.getLimitString("select * from ("+sqlTemp+") cat_sql_temp_000 order by "+getOrderby(sql),0,limit-offset+1);
return sqlTemp;
}
// 得到反转的orderby
private static String getOrderby(String sql)
{
String orderby="";
int lastk=sql.lastIndexOf(")");
int lasto=sql.toLowerCase().lastIndexOf("order by");
if((lastk>lasto &&lastk>-1) || lastk==lasto)
{
return "";
}
orderby=sql.substring(lasto+8,sql.length());
String[] os=orderby.split(",");
String nob="";
for(int i=0;i<os.length;i++)
{
int dian=os[i].indexOf(".");
if(dian>-1)
{
os[i]=os[i].substring(dian+1,os[i].length());
}
String[] colums=os[i].trim().split(" ");
String cm=colums[0];
int as=0;
as=sql.indexOf(" "+colums[0]+" as ");
if(as<0){
as=sql.indexOf(","+colums[0]+" as ");
}
if(as<0){
as=sql.indexOf("."+colums[0]+" as ");
}
if(as>-1)//当内sql有as时
{
as=as+("."+colums[0]+" as ").length();
int end=sql.indexOf(" ",as);
if(end<0 || (end >(sql.indexOf(",",as)) && sql.indexOf(",",as)>-1))
{
end=sql.indexOf(",",as);
}
if(end>-1)
{
cm=sql.substring(as,end);
//System.out.println(as+":"+end);
}
}
if(colums.length<2)
{
nob+=","+cm+" desc";
}else if(colums[1].toLowerCase().equals("asc"))
{
nob+=","+cm+" desc";
}else
{
nob+=","+cm+" asc";
}
}
if(nob.length()>0)
{
nob=nob.substring(1,nob.length());
}
//System.out.println("=============="+sql+":"+nob+"=================");
return nob;
}
/**
* @param args
*/
public static void main(String[] args) {
System.out.println(getOrderby("select cc.id as idd,cc.label from cc order by id asc"));
System.out.println("select cc.id as idd,cc.label from cc order by id asc".indexOf(",",16));
// TODO Auto-generated method stub
//MsSqlDialect d=new MsSqlDialect();
//System.out.println(d.getLimitString("SELECT * FROM HtmlLabelIndex ORDER BY indexdesc",3,5));
}
}