将以sql server sql标准编写的SQl翻译成对应的数据库
解决方案 »
- 关于MySQL数据库中数据类型的转换问题
- 求助一个倒计时的问题
- hibernate映射问题请各位指教
- servlet乱码问题
- index.jsp页面是有多个.jsp文件组成的,那么执行index.jsp时如何执行其他的.jsp页面呢
- log4j.properties 找不到初始路径
- resin-2.1.12 出错:
- 紧急求救(我从数据库重取出一个char型的值,带回车。返回,如何在java重去掉这个回车啊)
- 摆托,别提MVC啦!
- easyStruts+Tomcat+Eclipse问题?
- 配置c3p0连接池报错 No set method found for property怎么回事啊?
- 一道可以锻炼你思维的笔试题,在线等!!!
可以参考hibernate的实现最好不是要用sqlserver作为通用的,最好是用自己定义的作为通用 的,然后再翻译成对应数据库
标准sql+一个翻页支持就可以了。提供一个mssql的
package com.cat.xtools.data;import java.io.PrintStream;
import org.hibernate.dialect.SQLServerDialect;public class MsSqlDialect extends SQLServerDialect
{ public MsSqlDialect()
{
} 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);
String orderby = getOrderby(sql);
if(orderby.equals(""))
{
sqlTemp = super.getLimitString((new StringBuilder("select * from (")).append(sqlTemp).append(") cat_sql_temp_000").toString(), 0, (limit - offset) + 1);
} else
{
sqlTemp = super.getLimitString((new StringBuilder("select * from (")).append(sqlTemp).append(") cat_sql_temp_000 order by ").append(orderby).toString(), 0, (limit - offset) + 1);
}
return sqlTemp;
} 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((new StringBuilder(" ")).append(colums[0]).append(" as ").toString());
if(as < 0)
{
as = sql.indexOf((new StringBuilder(",")).append(colums[0]).append(" as ").toString());
}
if(as < 0)
{
as = sql.indexOf((new StringBuilder(".")).append(colums[0]).append(" as ").toString());
}
if(as > -1)
{
as += (new StringBuilder(".")).append(colums[0]).append(" as ").toString().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);
}
}
if(colums.length < 2)
{
nob = (new StringBuilder(String.valueOf(nob))).append(",").append(cm).append(" desc").toString();
} else
if(colums[1].toLowerCase().equals("asc"))
{
nob = (new StringBuilder(String.valueOf(nob))).append(",").append(cm).append(" desc").toString();
} else
{
nob = (new StringBuilder(String.valueOf(nob))).append(",").append(cm).append(" asc").toString();
}
} if(nob.length() > 0)
{
nob = nob.substring(1, nob.length());
}
return nob;
} public static void main(String args[])
{
MsSqlDialect d = new MsSqlDialect();
System.out.println(d.getLimitString("SELECT * FROM HtmlLabelIndex", 1, 1));
}
}
有的是 [ ] 有的 [ )。