hql 在oracle数据库下的简单应用

String sql = "select distinct a";
String fromSql = " from Aqyjfb a";
String whereSql = " where 1=1 and a.delete=false";
String orderSql = " order by fbsj desc";

String id = pb.decode(pb.getParamete("id"));
String zch = pb.decode(pb.getParamete("zch"));
String xxmc = pb.decode(pb.getParamete("xxmc"));
String orgId =pb.decode( pb.getParamete("orgId"));
String ssj = pb.decode(pb.getParamete("ssj"));
String esj = pb.decode(pb.getParamete("esj"));

if(id!=null && !id.equalsIgnoreCase("")){
whereSql+=" and a.id = '"+id+"'";
}

if(zch!=null && !zch.equalsIgnoreCase("")){
whereSql+=" and a.qyhznr.id like '%"+zch+"%'";
}

if(xxmc!=null && !xxmc.equalsIgnoreCase("")){
whereSql+=" and a.xxmc like '%"+xxmc+"%'";
}

if(orgId!=null && !orgId.equalsIgnoreCase("")){
whereSql+=" and (a.organization.id = '"+orgId+"')";
}

if(ssj!=null && !esj.equalsIgnoreCase("")){
whereSql+=" and a.fbsj between (to_date('"+ssj+"','yyyy-mm-dd')) and (to_date('"+esj+"','yyyy-mm-dd'))";

}

String exeSql = sql + fromSql + whereSql + orderSql;
String countSql = "select COUNT(distinct a) " + fromSql + whereSql;

return this.getPageBody(pb, countSql, exeSql);

如果是mysql或者mssql数据库，between and 是可以直接中使用的，但是oracle有自己的规范，故需要做下转换

whereSql+=" and a.fbsj between (to_date('"+ssj+"','yyyy-mm-dd')) and (to_date('"+esj+"','yyyy-mm-dd'))";