/**
* SQL 分页查询
*
* @param page
* @param sqlString
* @param resultClass
* @param parameter
* @return
*/
@SuppressWarnings("unchecked")
public <E> Page<E> findBySql(
Page<E> page, String sqlString, Parameter parameter, Class<?> resultClass) {
// get count
if (!page.isDisabled() && !page.isNotCount()) {
String countSqlString = "select count(*) " + removeSelect(removeOrders(sqlString));
// page.setCount(Long.valueOf(createSqlQuery(countSqlString,
// parameter).uniqueResult().toString()));
Query query = createSqlQuery(countSqlString, parameter);
List<Object> list = query.list();
if (list.size() > 0) {
page.setCount(Long.valueOf(list.get(0).toString()));
} else {
page.setCount(list.size());
}
if (page.getCount() < 1) {
return page;
}
}
// order by
String sql = sqlString;
if (StringUtils.isNotBlank(page.getOrderBy())) {
sql += " order by " + page.getOrderBy();
}
SQLQuery query = createSqlQuery(sql, parameter);
// set page
if (!page.isDisabled()) {
query.setFirstResult(page.getFirstResult());
query.setMaxResults(page.getMaxResults());
}
setResultTransformer(query, resultClass);
page.setList(query.list());
return page;
}
@SuppressWarnings("unchecked")
public static <E> List<E> find(
Session session, String sql, Class<E> clazz, PageInfo pageInfo, Object... params) {
List<E> list = new ArrayList<E>();
SQLQuery sqlQuery = session.createSQLQuery(sql);
setParameter(sqlQuery, params);
if (pageInfo != null) {
Integer first = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize();
// if(ValidateUtil.isEmpty(first)){
// first = 1;
// }
sqlQuery.setFirstResult(first);
@SuppressWarnings("unused")
Integer pageSize = pageInfo.getPageSize();
// if(Validator.isEmpty(pageSize)){
// pageSize = PageInfoUtil.getDefaultPagesize();
// }
sqlQuery.setMaxResults(pageInfo.getPageSize());
Integer total = getCount(session, sql, params);
pageInfo.setPageTotal(total);
}
sqlQuery = sqlQuery.addEntity("model", clazz);
list = sqlQuery.list();
return list;
}
/**
* 使用原生sql根据应用专题分页查询应用信息列表
*
* @param page
* @param sql
* @return [参数说明]
* @return List<DpAppInfo> [返回类型说明]
* @exception throws [违例类型] [违例说明]
*/
@SuppressWarnings("unchecked")
@Override
public List<DpAppInfo> queryForPageBySql(Page<AppSubjectType> page, String sql) {
Session session = getSession();
SQLQuery sq = session.createSQLQuery(sql);
sq.addEntity(DpAppInfo.class);
sq.setFirstResult(page.getBeginCount());
sq.setMaxResults(page.getPageSize());
return sq.list();
}
/**
* Obtiene la lista del Log
*
* @param id_usuario Id. del usuario
* @param id_tipo Id. del tipo (APPBS_USUARIOS_LOG_LEGEND)
* @param nivel Nivel del log
* @param fec_from Fecha de inicio
* @param fec_to Fecha final
* @param start Elemento en el que se comienza
* @param limit Cantidad de elementos
* @return Lista de Log
*/
public static List<APPBS_USUARIOS_LOG> listLog(
Long id_usuario,
Long id_tipo,
Long nivel,
Date fec_from,
Date fec_to,
int start,
int limit,
String sort,
String dir) {
Session session = HibernateUtil.currentSession();
SQLQuery query =
(SQLQuery)
session
.createSQLQuery(
getListQuery(id_usuario, id_tipo, nivel, fec_from, fec_to, false, sort, dir))
.setCacheable(false);
if (start != -1 && limit != -1) {
query.setFirstResult(start);
query.setMaxResults(limit);
}
List qRes = query.list();
List<APPBS_USUARIOS_LOG> result = new ArrayList<APPBS_USUARIOS_LOG>();
for (int i = 0; i < qRes.size(); i++) {
Object[] item = (Object[]) qRes.get(i);
APPBS_USUARIOS_LOG uLog = new APPBS_USUARIOS_LOG();
if (Constants.DB_TYPE == Constants.DB_TYPE_ORACLE) {
uLog.setId_usuario(((BigDecimal) item[0]).longValue());
uLog.setNivel(((BigDecimal) item[2]).longValue());
uLog.setId_tipo(((BigDecimal) item[3]).longValue());
} else {
uLog.setId_usuario(((Integer) item[0]).longValue());
uLog.setNivel(((Integer) item[2]).longValue());
uLog.setId_tipo(((Integer) item[3]).longValue());
}
uLog.setFecha((Timestamp) item[1]);
uLog.setUsuario((String) item[4]);
uLog.setDescripcion((String) item[5]);
uLog.setTexto1((String) item[6]);
uLog.setTexto2((String) item[7]);
uLog.setTexto3((String) item[8]);
uLog.setTexto4((String) item[9]);
uLog.setTexto5((String) item[10]);
uLog.setSesion_id((String) item[11]);
result.add(uLog);
}
return result;
}
@Override
public Page getUserPage(int pageNow) {
// TODO Auto-generated method stub
Page page = new Page();
String queryString =
"SELECT users.id,users.account,users.`name`,users.`password`,users.`level`,users.registertime,users.logintime,users.active,"
+ "s_users.openid,s_users.type,s_users.age,s_users.sex,s_users.mail,s_users.phone,"
+ "s_users.address,s_users.education,s_users.school,s_users.id AS susersid "
+ "FROM users LEFT JOIN s_users ON users.id=s_users.p_users_id";
SQLQuery query = this.getCurrentSession().createSQLQuery(queryString);
page.setTotalCount(query.list().size());
page.setPageNow(pageNow);
int pageSize = page.getPageSize();
page.setPageCount((page.getTotalCount() + pageSize - 1) / pageSize);
query.setFirstResult((pageNow - 1) * pageSize);
query.setMaxResults(pageSize);
page.setDataList(query.list());
return page;
}
public List<BlogEntry> findBlogEntriesInDirectory(
WikiDirectory startDir,
WikiDocument ignoreDoc,
Pager pager,
Integer year,
Integer month,
Integer day,
String tag,
boolean countComments) {
final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>();
StringBuilder queryString = new StringBuilder();
queryString.append("select").append(" ");
for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) {
queryString.append(getWikiDocumentSQLColumnNames()[i]);
if (i != getWikiDocumentSQLColumnNames().length - 1) queryString.append(", ");
}
queryString.append(", '0' as COMMENT_COUNT").append(" ");
queryString.append(getblogEntryFromClause(tag));
queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag));
queryString.append(" ");
queryString.append("order by doc2.CREATED_ON desc");
SQLQuery query = getSession().createSQLQuery(queryString.toString());
bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag);
query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName());
query.addEntity(WikiDocument.class);
query.addScalar("COMMENT_COUNT", Hibernate.LONG);
query.setFirstResult(pager.getQueryFirstResult());
query.setMaxResults(pager.getQueryMaxResults());
query.setResultTransformer(
new ResultTransformer() {
public Object transformTuple(Object[] result, String[] aliases) {
BlogEntry be = new BlogEntry();
be.setEntryDocument((WikiDocument) result[0]);
blogEntryMap.put(
be.getEntryDocument().getId(),
be); // Put in map so we can attach comment count later
return be;
}
public List transformList(List list) {
return list;
}
});
List<BlogEntry> result = (List<BlogEntry>) query.list();
if (countComments && result.size() > 0) {
// The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some
// DBs...
StringBuilder commentQueryString = new StringBuilder();
commentQueryString
.append("select doc.NODE_ID as DOC_ID, count(c1.NODE_ID) as COMMENT_COUNT")
.append(" ");
commentQueryString.append("from WIKI_DOCUMENT doc").append(" ");
commentQueryString
.append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID")
.append(" ");
commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" ");
commentQueryString.append("group by doc.NODE_ID");
SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString());
commentQuery.setComment("Finding comment count for blog entries");
commentQuery.addScalar("DOC_ID");
commentQuery.addScalar("COMMENT_COUNT");
commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet());
commentQuery.setResultTransformer(
new ResultTransformer() {
public Object transformTuple(Object[] result, String[] aliases) {
BlogEntry be = blogEntryMap.get(((BigInteger) result[0]).longValue());
be.setCommentCount(((BigInteger) result[1]).longValue());
return null;
}
public List transformList(List list) {
return list;
}
});
commentQuery.list();
}
return result;
}
