先来个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));
}}
在项目中选择了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));
}}
解决方案 »
- Struts2的问题 高手求救
- 打开j2ee里面的jar包
- java 压缩文件
- jsf+spring+hibernate增删改简明示例
- 关于voicexml工作难点求帮忙。完成后可以有RMB报酬。请各位大神帮忙。
- 信息提醒在JSP中如何实现呢??虚心请教
- 实在不知道为什么,前来请教有关jdom初级问题!
- 有大牛对JMAIL熟悉的么-这个软件用jmail从我的QQ邮箱中下载邮件~但是一直都连不上服务器,都说是connection timeout
- 我自己整合的开发平台,代码生成,大家评价一下 ssmy_m
- springmvc+dubbo+zookeeper调用服务失败帮忙看看啥问题
- SSH整合出现java.lang.NullPointerException,在线等!
- 服务器为我们做了什么?
这样的缺点就是
1、排序字段必须是显示字段
2、最后一页结果可能不正确,因为如果查找第21-25的数据时,可能数据库中只有23条数据,取了前25,实际是23,再反向查5条。也就是说,最后一页与上一页可能产生重复数据。也就是说,如果每页显示5 条,最后一页肯定是5条,如果总记录数不能被5整除,就查询出重复数据了。
/*
* Hibernate, Relational Persistence for Idiomatic Java
*
* Copyright (c) 2008, Red Hat Middleware LLC or third-party contributors as
* indicated by the @author tags or express copyright attribution
* statements applied by the authors. All third-party contributions are
* distributed under license by Red Hat Middleware LLC.
*
* This copyrighted material is made available to anyone wishing to use, modify,
* copy, or redistribute it subject to the terms and conditions of the GNU
* Lesser General Public License, as published by the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
* or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
* for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with this distribution; if not, write to:
* Free Software Foundation, Inc.
* 51 Franklin Street, Fifth Floor
* Boston, MA 02110-1301 USA
*
*/
package org.hibernate.dialect;import java.sql.Types;import org.hibernate.Hibernate;
import org.hibernate.LockMode;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.AnsiTrimEmulationFunction;/**
* A dialect for Microsoft SQL Server 2000 and 2005
*
* @author Gavin King
*/
public class SQLServerDialect extends AbstractTransactSQLDialect { public SQLServerDialect() {
registerColumnType( Types.VARBINARY, "image" );
registerColumnType( Types.VARBINARY, 8000, "varbinary($l)" );
registerColumnType( Types.LONGVARBINARY, "image" );
registerColumnType( Types.LONGVARCHAR, "text" ); registerFunction( "second", new SQLFunctionTemplate( Hibernate.INTEGER, "datepart(second, ?1)" ) );
registerFunction( "minute", new SQLFunctionTemplate( Hibernate.INTEGER, "datepart(minute, ?1)" ) );
registerFunction( "hour", new SQLFunctionTemplate( Hibernate.INTEGER, "datepart(hour, ?1)" ) );
registerFunction( "locate", new StandardSQLFunction( "charindex", Hibernate.INTEGER ) ); registerFunction( "extract", new SQLFunctionTemplate( Hibernate.INTEGER, "datepart(?1, ?3)" ) );
registerFunction( "mod", new SQLFunctionTemplate( Hibernate.INTEGER, "?1 % ?2" ) );
registerFunction( "bit_length", new SQLFunctionTemplate( Hibernate.INTEGER, "datalength(?1) * 8" ) ); registerFunction( "trim", new AnsiTrimEmulationFunction() ); registerKeyword( "top" );
} public String getNoColumnsInsertString() {
return "default values";
} static int getAfterSelectInsertPoint(String sql) {
int selectIndex = sql.toLowerCase().indexOf( "select" );
final int selectDistinctIndex = sql.toLowerCase().indexOf( "select distinct" );
return selectIndex + ( selectDistinctIndex == selectIndex ? 15 : 6 );
} public String getLimitString(String querySelect, int offset, int limit) {
if ( offset > 0 ) {
throw new UnsupportedOperationException( "query result offset is not supported" );
}
return new StringBuffer( querySelect.length() + 8 )
.append( querySelect )
.insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )
.toString();
} /**
* Use <tt>insert table(...) values(...) select SCOPE_IDENTITY()</tt>
*/
public String appendIdentitySelectToInsert(String insertSQL) {
return insertSQL + " select scope_identity()";
} public boolean supportsLimit() {
return true;
} public boolean useMaxForLimit() {
return true;
} public boolean supportsLimitOffset() {
return false;
} public boolean supportsVariableLimit() {
return false;
} public char closeQuote() {
return ']';
} public char openQuote() {
return '[';
} public String appendLockHint(LockMode mode, String tableName) {
if ( ( mode == LockMode.UPGRADE ) ||
( mode == LockMode.UPGRADE_NOWAIT ) ||
( mode == LockMode.PESSIMISTIC_WRITE ) ||
( mode == LockMode.WRITE ) ) {
return tableName + " with (updlock, rowlock)";
}
else if ( mode == LockMode.PESSIMISTIC_READ ) {
return tableName + " with (holdlock, rowlock)";
}
else {
return tableName;
}
} public String getSelectGUIDString() {
return "select newid()";
} // The current_timestamp is more accurate, but only known to be supported
// in SQL Server 7.0 and later (i.e., Sybase not known to support it at all)
public String getCurrentTimestampSelectString() {
return "select current_timestamp";
} // Overridden informational metadata ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ public boolean areStringComparisonsCaseInsensitive() {
return true;
} public boolean supportsResultSetPositionQueryMethodsOnForwardOnlyCursor() {
return false;
} public boolean supportsCircularCascadeDeleteConstraints() {
// SQL Server (at least up through 2005) does not support defining
// cascade delete constraints which can circel back to the mutating
// table
return false;
} public boolean supportsLobValueChangePropogation() {
// note: at least my local SQL Server 2005 Express shows this not working...
return false;
} public boolean doesReadCommittedCauseWritersToBlockReaders() {
return false; // here assume SQLServer2005 using snapshot isolation, which does not have this problem
} public boolean doesRepeatableReadCauseReadersToBlockWriters() {
return false; // here assume SQLServer2005 using snapshot isolation, which does not have this problem
} public boolean supportsTupleDistinctCounts() {
return false;
}
}
Hibernate本身不支持Sql2005的row_number.需要你自己去实现。