㈠ sql翻译器怎么实现
标准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));
}
}
㈡ SQL编写规范
书写格式 示例代码 存储过程SQL文书写格式例selectc dealerCode round(sum(c submitSubletAmountDLR + c submitPartsAmountDLR + c submitLaborAmountDLR) / count(*) ) as avg decode(null x xx CNY )from (selecta dealerCode a submitSubletAmountDLR a submitPartsAmountDLR a submitLaborAmountDLRfrom SRV_C_F awhere (to_char(a ORIGSUBMITTIME yyyy/mm/dd ) >= Date Range(start) and to_char(a ORIGSUBMITTIME yyyy/mm/dd ) <= Date Range(end) and nvl(a deleteflag ) <> )union allselectb dealerCode b submitSubletAmountDLR b submitPartsAmountDLR b submitLaborAmountDLRfrom SRV_CHistory_F bwhere (to_char(b ORIGSUBMITTIME yyyy/mm/dd ) >= Date Range(start) and to_char(b ORIGSUBMITTIME yyyy/mm/dd ) <= Date Range(end) and nvl(b deleteflag ) <> )) cgroup by c dealerCodeorder by avg desc;Java source里的SQL字符串书写格式例strSQL = insert into Snd_FinanceHistory_Tb + (DEALERCODE + REQUESTSEQUECE + HANDLETIME + JOBFLAG + FRAMENO + INMONEY + REMAINMONEY + DELETEFLAG + UPDATECOUNT + CREUSER + CREDATE + HONORCHECKNO + SEQ) + values ( + draftInputDetail dealerCode + + + draftInputDetail requestsequece + + sysdate + + + frameNO + + requestMoney + + remainMoney + + + + + draftStruct employeeCode + + sysdate + + draftInputDetail honorCheckNo + + index + ) ; ) 缩进对于存储过程文件 缩进为 个空格对于Java source里的SQL字符串 不可有缩进 即每一行字符串不可以空格开头 ) 换行 > Select/From/Where/Order by/Group by等子句必须另其一行写 > Select子句内容如果只有一项 与Select同行写 > Select子句内容如果多于一项 每一项单独占一行 在对应Select的基础上向右缩进 个空格（Java source无缩进） > From子句内容如果只有一项 与From同行写 > From子句内容如果多于一项 每一项单独占一行 在对应From的基础上向右缩进 个空格（Java source无缩进） > Where子句的条件如果有多项 每一个条件占一行 以AND开头 且无缩进 > (Update)Set子句内容每一项单独占一行 无缩进 > Insert子句内容每个表字段单独占一行 无缩进 values每一项单独占一行 无缩进 > SQL文中间不允许出现空行 > Java source里单引号必须跟所属的SQL子句处在同一行 连接符（ + ）必须在行首 ) 空格 > SQL内算数运算符 逻辑运算符连接的两个元素之间必须用空格分隔 > 逗号之后必须接一个空格 > 关键字 保留字和左括号之间必须有一个空格 不等于统一使用 <> Oracle认为 != 和 <> 是等价的 都代表不等于的意义 为了统一 不等于一律使用 <> 表示 使用表的别名 数据库查询 必须使用表的别名 SQL文对表字段扩展的兼容性 在Java source里使用Select *时 严禁通过getString( )的形式得到查询结果 必须使用getString( 字段名 )的形式使用Insert时 必须指定插入的字段名 严禁不指定字段名直接插入values 减少子查询的使用 子查询除了可读性差之外 还在一定程度上影响了SQL运行效率请尽量减少使用子查询的使用 用其他效率更高 可读性更好的方式替代 适当添加索引以提高查询效率 适当添加索引可以大幅度的提高检索速度请参看ORACLE SQL性能优化系列 对数据库表操作的特殊要求 本项目对数据库表的操作还有以下特殊要求 ) 以逻辑删除替代物理删除注意 现在数据库表中数据没有物理删除 只有逻辑删除以deleteflag字段作为删除标志 deleteflag= 代表此记录被逻辑删除 因此在查询数据时必须考虑deleteflag的因素deleteflag的标准查询条件 NVL(deleteflag ) <> ) 增加记录状态字段数据库中的每张表基本都有以下字段 DELETEFLAG UPDATECOUNT CREDATE CREUSER UPDATETIME UPDATEUSER要注意在对标进行操作时必须考虑以下字段插入一条记录时要置DELETEFLAG= UPDATECOUNT= CREDATE=sysdate CREUSER=登录User查询一条记录时要考虑DELETEFLAG 如果有可能对此记录作更新时还要取得UPDATECOUNT作同步检查修改一条记录时要置UPDATETIME=sysdate UPDATEUSER=登录User UPDATECOUNT=(UPDATECOUNT+ ) mod 删除一条记录时要置DELETEFLAG= ) 历史表数据库里部分表还存在相应的历史表 比如srv_c_f和srv_chistory_f在查询数据时除了检索所在表之外 还必须检索相应的历史表 对二者的结果做Union（或Union All） 用执行计划分析SQL性能 EXPLAIN PLAN是一个很好的分析SQL语句的工具 它可以在不执行SQL的情况下分析语句通过分析 我们就可以知道ORACLE是怎样连接表 使用什么方式扫描表（索引扫描或全表扫描） 以及使用到的索引名称按照从里到外 从上到下的次序解读分析的结果EXPLAIN PLAN的分析结果是用缩进的格式排列的 最内部的操作将最先被解读 如果两个操作处于同一层中 带有最小操作号的将首先被执行目前许多第三方的工具如PLSQL Developer和TOAD等都提供了极其方便的EXPLAIN PLAN工具PG需要将自己添加的查询SQL文记入log 然后在EXPLAIN PLAN中进行分析 尽量减少全表扫描 ORACLE SQL性能优化系列 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名 因此FROM子句中写在最后的表（基础表driving table）将被最先处理在FROM子句中包含多个表的情况下 必须选择记录条数最少的表作为基础表当ORACLE处理多个表时 会运用排序及合并的方式连接它们首先 扫描第一个表（FROM子句中最后的那个表)并对记录进行排序 然后扫描第二个表（FROM子句中最后第二个表) 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并例如:表 TAB 条记录表 TAB 条记录选择TAB 作为基础表 (最好的方法)select count(*) from tab tab 执行时间 秒选择TAB 作为基础表 (不佳的方法)select count(*) from tab tab 执行时间 秒如果有 个以上的表连接查询 那就需要选择交叉表（intersection table）作为基础表 交叉表是指那个被其他表所引用的表例如:EMP表描述了LOCATION表和CATEGORY表的交集SELECT *FROM LOCATION L CATEGORY C EMP EWHERE E EMP_NO BEEEN AND AND E CAT_NO = C CAT_NOAND E LOCN = L LOCN将比下列SQL更有效率SELECT *FROM EMP E LOCATION L CATEGORY CWHERE E CAT_NO = C CAT_NOAND E LOCN = L LOCNAND E EMP_NO BEEEN AND WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句根据这个原理 表之间的连接必须写在其他WHERE条件之前 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾例如 (低效 执行时间 秒)SELECT *FROM EMP EWHERE SAL > AND JOB = MANAGER AND < (SELECT COUNT(*) FROM EMP WHERE MGR=E EMPNO);(高效 执行时间 秒)SELECT *FROM EMP EWHERE < (SELECT COUNT(*) FROM EMP WHERE MGR=E EMPNO)AND SAL > AND JOB = MANAGER ; SELECT子句中避免使用 * 当你想在SELECT子句中列出所有的COLUMN时 使用动态SQL列引用 * 是一个方便的方法 不幸的是 这是一个非常低效的方法实际上 ORACLE在解析的过程中 会将 * 依次转换成所有的列名这个工作是通过查询数据字典完 lishixin/Article/program/Oracle/201311/18246
㈢ sql如何取交集
select * from mytable where pet in (select pet from mytable group by pet having count(pet)>1)
获取同一pet数量大于1的pet并select出相关信息
select pet,count(pet) as per_count from mytable group by pet order by count(pet) desc limit 10
获取pet拥有量前十的pet和数量
㈣ 浅谈hql和sql的区别，描述特别需要注意的地方
1.hql与sql的区别
sql 面向数据库表查询
hql 面向对象查询
hql ： from 后面跟的 类名＋类对象 where 后 用 对象的属性做条件
sql： from 后面跟的是表名 where 后 用表中字段做条件
查询
在Hibernate中使用查询时，一般使用Hql查询语句。
HQL（Hibernate Query Language），即Hibernate的查询语言跟SQL非常相像。不过HQL与SQL的最根本的区别，就是它是面向对象的。
使用HQL时需要注意以下几点：
l 大小写敏感
因为HQL是面向对象的，而对象类的名称和属性都是大小写敏感的，所以HQL是大小写敏感的。
Eg.
HQL语句：from Cat as cat where cat.id > 1;与from Cat as cat where cat.ID > 1;是不一样的，这点与SQL不同。
l from子句
Eg. from Cat，该句返回Cat对象实例，开发人员也可以给其加上别名，eg. from Cat as cat，对于多表查询的情况，可参考如下：
from Cat as cat, Dog as dog
其它方面都与SQL类似，在此不再赘述。
接下来讲一个在Hibernate中查询的例子。
1.1简单查询
List list = session.createQuery("from User as user order by user.loginName").list();
1.2带单个参数的查询
List list = session.find("from User as user where user.loginName=?",
loginName,
Hibernate.STRING);
1.3多个参数的查询
Eg1. 此例采用“?”占位符的方式
String hql = "from User as user where user.loginName=? and user.orgId=? ";
Query query = session.createQuery(hql);
query.setParameter(1, 'amigo');
query.setParameter(2, new Long(1)) ;
List list = query .list();
Eg2. 此例采用“:paramName”的方式
String hql = "from User as user where user.loginName=:loginName and user.orgId=:orgId ";
Query query = session.createQuery(hql);
query.setParameter('loginName', 'amigo');
query.setParameter('orgId', new Long(1)) ;
List list = query .list();
1.4查询数量
int count = (Integer) session.createQuery("select count(*) from User").uniqueResult().intValue();
1.5限制查询起始值和数量的查询
这种一般是在记录需要分页的时候需要用到，例如，在如下的代码中，限制查询的开始记录的位置为50，最大查询条数为50。
String hql = "from User as user order by user.loginName";
int firstResult= 50;
int maxResults = 50;
Query query = session.createQuery(hql);
query = query.setFirstResult(firstResult);
query.setMaxResults(maxResults);
1.6子查询
在某些情况下，也需要用到子查询，例如在下面的例子中，User为用户对象，UserRole为用户与角色关联对象。如下HQL语句将没有分配角色的用户对象查找出来。
String hql = "from User user where user.loginName"
+ " not in(select ur.user.loginName from UserRole ur) ";
List list = (session.createQuery(hql)).list();
1.7原生SQL查询
对于某些复杂的查询语句，需要调用某种特定的数据库的特定函数才能解决，Hibernate虽然不推荐使用原生SQL语句来查询，因为这将破坏数据库的易移植性，但是Hibernate中也提供了使用原生SQL进行查询的方法，只需要获得连接即可。
Eg. 在下面的例子中，用到了Sql Server数据库中的原生sql语句，如下所示：
String timeUnit = "13";
String sql = "select count(*) count, CONVERT(VARCHAR(" + timeUnit +"), log.gen_datetime,121) timeUnit " + "from Log log";
SQLQuery query = session.createSQLQuery(sql)
.addScalar("count", Hibernate.INTEGER)
.addScalar("timeUnit", Hibernate.STRING);
List list = query.list();
2 新增
在数据库中新增记录在Hibernate中不需要使用insert命令，只需要构造新增的对象后，调用Session对象的save(…)方法即可。
2.1新增单个对象
新增单个对象的实例如下，该实例将在用户表中新增一条记录。
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
User user = new User();
user.setLoginName("amigo");
user.setFullName("阿蜜果");
……
session.save(user) ;
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
2.2批量新增对象
对于批量新增对象的情况，需要在新增一部分对象后flush和clear一次，例如，没批量新增20个对象时手动的flush一次，假设在list为一个用户列表，里面包含很多User对象，那么要将实现这些对象的批量新增，可采用如下方法：
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
for (int i = 0; i < list.size(); i++) {
User user = (User) list.get(i);
session.save(user) ;
if (i % 20 == 0) {
session.flush();
session.clear();
}
}
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
3 更新
在hibernate中，更新对象前不需要使用查询语句：update…，一般需要在取得需要更新的持久化对象后，执行Session对象的update(…)方法。例如：
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
//取得持久化对象
User user = session.get(User.class, "amigo");
//对需要修改的属性进行修改
user.setFullName("阿蜜果");
……
session.update(user) ;
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
4 删除
4.1删除单个对象
一般在取得某对象后，开发人员可以调用Session对象的delete(…)方法删除该对象。
Eg. 下面的实例中取得loginName（主键）为“amigo”的User对象后，将它删除。
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
//取得持久化对象
User user = session.get(User.class, "amigo");
session.delete(user) ;
ts.commit();
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
4.2批量删除对象
对于批量删除对象的情况，开发人员可以在取得待删除的对象列表后，一个一个的将对象删除，对于每个对象的删除方法，见3.4.1小节。开发人员还可以hql语句来做批量删除。
Eg. 该实例通过delete语句来删除记录，除了loginName为“amigo”的对象为，其余都删除，代码如下所示：
Session session = HibernateSessionFactory.getSession();
Transaction ts = null;
try {
ts = session.beginTransaction();
String hql = "delete User as user where user.loginName != 'amigo'";
Query query = session.createQuery(hql);
int count = query.executeUpdate();
ts.commit();
System.out.println("delete count : " + count); //删除条数
} catch (Exception e) {
if (ts != null) {
ts.rollback();
}
} finally {
HibernateSessionFactory.closeSession();
}
㈤ SQL 每个分类显示一个记录
SELECT Pic.Cat_ID，Cat_Name，Pic_Url，Pic_Date
FROM [Pic]
INNER JOIN [Pic_Cat]
ON (Pic.Cat_ID = Pic_Cat.Cat_ID)
WHERE (Pic.Pic_ID IN (
SELECT MAX(Pic_ID)
FROM [Pic]
GROUP BY Pic_Cat
))
这只是一种用IN子查询的思路，也可以用相关子查询的方法。