Hibernate中使用SQL脚本，count()函数,oracle分页，左处连接

声明:我写的博客就是我的学习笔记,让我在忘记的时候可以在任何时候,任何地方,打开连接就可以看到,让我想起忘记的东西.
我的博客通常都有资料来源,只用于学习,没有任何其他目的,如果有版权问题,请跟我联系,如果对资料有什么疑问，可以留言！
我的email:[e-mail]

代码来源:SZGHRS项目，PersonDaoImpl.java
平台：struts,hibernate,spring,oracle

/**
* 条件查询单位中的人员
*/
public List<PersonDTO> findUnitPersonByCondition(TableTagBean ttb) throws DataAccessException {


// 查询字段
StringBuffer sbD = new StringBuffer("select " +
"p.PERSON_OID, " +
"p.NAME, " +
"p.ADMINISTRATIVE_DUTY, " +
"p.ADMINISTRATIVE_DUTY_LEVEL, " +
"p.DEGREE_CODE, " +
"p.DEPT_OID, " +
"p.EDUCATION_LEVEL_CODE, " +
"p.SEX_CODE, " +
"p.AUDITING_FLAG, " +
"p.IS_SPECIAL, " +
"p.BIRTHDAY, "+
"p.POLITIC_STATUS_CODE, " +
"o.ORG_NAME");
// 统计总记录数
StringBuffer sbTo = new StringBuffer("select count(p.PERSON_OID)");
String unitOid=ttb.getCondition().get("UNIT_OID");
if(unitOid==null) return null;
StringBuffer sb = new StringBuffer();
sb.append(" from PERSON p,ORG o ");
sb.append(" where o.end_date is null  and o.ORG_INT_OID = p.DEPT_OID(+) and p.UNIT_OID='" + unitOid + "' and (p.IS_ACTIVE is null or  p.IS_ACTIVE<>'N')");
// 姓名查询
String queryname = ttb.getCondition().get("NAME");
if (queryname != null) {
sb.append(" and p.NAME like '%" + queryname + "%'");
}
//内设机构
String DEPT_OID=ttb.getCondition().get("DEPT_OID");
if (DEPT_OID != null && DEPT_OID.length()>0) {
sb.append(" and p.DEPT_OID =" + DEPT_OID );
}
// 职务层次查询
String administrative_Duty_Level = ttb.getCondition().get("ADMINISTRATIVE_DUTY_LEVEL");
if (administrative_Duty_Level != null) {
sb.append(" and p.ADMINISTRATIVE_DUTY_LEVEL = '" + administrative_Duty_Level + "'");
}
// 年龄查询,参数格式yyyy/MM/dd
String BIRTHDAY_S = ttb.getCondition().get("BIRTHDAY_S");
if (BIRTHDAY_S != null && BIRTHDAY_S.length()>0) {
sb.append(" and p.BIRTHDAY > to_date('" + BIRTHDAY_S + "','yyyy-MM-dd')");
}
String BIRTHDAY_E = ttb.getCondition().get("BIRTHDAY_E");
if (BIRTHDAY_E != null && BIRTHDAY_E.length()>0) {
sb.append(" and p.BIRTHDAY < to_date('" + BIRTHDAY_E + "','yyyy-MM-dd')");
}
// 学位
String DEGREE_CODE = ttb.getCondition().get("DEGREE_CODE");
if (DEGREE_CODE != null && DEGREE_CODE.length()>0) {
sb.append(" and p.DEGREE_CODE='" + DEGREE_CODE + "'");
}
// 学历查询条件

String EDUCATION_LEVEL_CODE = ttb.getCondition().get("EDUCATION_LEVEL_CODE");
if (DEGREE_CODE != null && DEGREE_CODE.length()>0) {
sb.append(" and p.EDUCATION_LEVEL_CODE='" + EDUCATION_LEVEL_CODE + "'");
}

// 政治面貌
String POLITIC_STATUS_CODE = ttb.getCondition().get("POLITIC_STATUS_CODE");
if (DEGREE_CODE != null && DEGREE_CODE.length()>0) {
sb.append(" and p.POLITIC_STATUS_CODE='" + POLITIC_STATUS_CODE + "'");
}

// 证件号码
String ID_NO = ttb.getCondition().get("ID_NO");
if (ID_NO != null && ID_NO.length()>0) {
sb.append(" and p.ID_NO like'%" + ID_NO + "%'");
}

// 排序
String sort = ttb.getOrderBy();
boolean ASC = ttb.getAsc();
if ("ORG_NAME".equals(sort)) {
if (ASC) {
sb.append(" order by o.ORG_NAME ASC");
} else {
sb.append(" order by o.ORG_NAME DESC");
}
} else {
if (ASC) {
sb.append(" order by p." + sort + " ASC");
} else {
sb.append(" order by p." + sort + " DESC");
}
}

StringBuffer header = new StringBuffer("select * from (select rowst.*,rownum rownum_ from ( ");
// 分布
int start = ttb.getPage();
int limit = ttb.getPageSize();

StringBuffer tile = new StringBuffer("  ) rowst where rownum<" + limit + ") where rownum_>" + start);
header.append(sbD).append(sb).append(tile);
sbTo.append(sb);
log.info(sbTo.toString());
// 统计总记录数
Integer total=new Integer(this.getSession().createSQLQuery(sbTo.toString()).list().get(0).toString());
ttb.setTotal(total);
//查询记录
log.info(header);
List list = this.getSession().createSQLQuery(header.toString()).list();

if (list == null || list.size() == 0)
return null;
List<PersonDTO> personDtoList=new ArrayList<PersonDTO>();
for (int i = 0; i < list.size(); i++) {
Object[] queryResult = (Object[]) list.get(i);
String personOidResult = queryResult[0]==null?null:queryResult[0].toString();
String nameResult = queryResult[1]==null?null:queryResult[1].toString();
String administrativeDutyResult = queryResult[2]==null?null:queryResult[2].toString();
String administrativeDutyLevelResult = queryResult[3]==null?null:queryResult[3].toString();
String degreeCodeResult = queryResult[4]==null?null:queryResult[4].toString();
String deptOidResult = queryResult[5]==null?null:queryResult[5].toString();
String educationLevelCodeResult = queryResult[6]==null?null:queryResult[6].toString();
String sexCodeResult = queryResult[7]==null?null:queryResult[7].toString();
String auditingFlagResult = queryResult[8]==null?null:queryResult[8].toString();
String isSpecialResult = queryResult[9]==null?null:queryResult[9].toString();
Date birthdayResult = queryResult[10]!=null?(Date)queryResult[10]:null;
String politicStatusCodeResult = queryResult[11]==null?null:queryResult[11].toString();
String orgNameResult = queryResult[12]==null?null:queryResult[12].toString();

PersonDTO personDto=new PersonDTO();
personDto.setPersonOid(new Long(personOidResult));
personDto.setName(nameResult);
personDto.setAdministrativeDuty(administrativeDutyResult);
personDto.setAdministrativeDutyLevel(administrativeDutyLevelResult);
personDto.setDegree(degreeCodeResult);
personDto.setDeptName(orgNameResult);
personDto.setEducationLevelCode(educationLevelCodeResult);
personDto.setSexCode(sexCodeResult);
personDto.setAuditingFlag(auditingFlagResult);
personDto.setIsSpecial(isSpecialResult);
personDto.setBirthday(birthdayResult);
personDto.setPoliticStatusCode(politicStatusCodeResult);
personDtoList.add(personDto);
}
return personDtoList;
}

完整的SQL语句:
Hibernate: select count(p.PERSON_OID) from PERSON p,ORG o  where o.end_date is null  and p.DEPT_OID = o.ORG_INT_OID(+) and p.UNIT_OID='70' and (p.IS_ACTIVE is null or  p.IS_ACTIVE<>'N') order by p.person_Oid ASC


Hibernate: select * from (select rowst.*,rownum rownum_ from ( select p.PERSON_OID, p.NAME, p.ADMINISTRATIVE_DUTY, p.ADMINISTRATIVE_DUTY_LEVEL, p.DEGREE_CODE, p.DEPT_OID, p.EDUCATION_LEVEL_CODE, p.SEX_CODE, p.AUDITING_FLAG, p.IS_SPECIAL, p.BIRTHDAY, p.POLITIC_STATUS_CODE, o.ORG_NAME from PERSON p,ORG o  where o.end_date is null  and p.DEPT_OID = o.ORG_INT_OID(+) and p.UNIT_OID='70' and (p.IS_ACTIVE is null or  p.IS_ACTIVE<>'N') order by p.person_Oid ASC  ) rowst where rownum<30) where rownum_>0