sdsdf

StringBuffer hql = new StringBuffer("SELECT cl.uuid,c.* FROM t_creativelog cl LEFT JOIN t_creativelog cl3 ON cl.creativeId = cl3.creativeId AND cl.memberId = cl3.memberId AND cl3.state = "+BusinessConstants.CREATIVEADDUP_TYPE.SETTLE.getType()+" ");
      Map<String,Object> params =  new HashMap<String,Object>();
      hql.append(" LEFT JOIN tb_creative c ON cl.creativeId = c.uuid ");
 hql.append(" WHERE cl.memberId = '"+currentMember.getUuid()+"'");
 hql.append(" AND cl.state = "+BusinessConstants.CREATIVEADDUP_TYPE.DOWNLOAD.getType()+" ");
 hql.append(" AND cl.isdeleted = "+DEL_FLG.UNDELETED.getType()+" ");

 hql.append(" AND c.enddate >= '" + new SimpleDateFormat("yyyy-MM-dd").format(DateUtil.addDays(DateUtil.getNowDate(), 1)) + "'");

 hql.append(" AND c.STATUS = "+BusinessConstants.CREATIVESTATUSFLAG.END.getType()+" ");
//排序功能
 if(StringUtil.isNotBlank(orderType)){
if(StringUtil.equals(orderType, String.valueOf(BusinessConstants.MYCREATIVE_ORDDERBY.STATE.getType()))){
 hql.append(" order by statusorder desc" );
 }else if(StringUtil.equals(orderType, String.valueOf(BusinessConstants.MYCREATIVE_ORDDERBY.DOWNLOADTIME.getType()))){
 hql.append(" order by cl.createtime " );
 }
 }
 List<Map<String, Object>> reslut = this.getQueryHibernateTemplate().findMapParamBySQL(hql.toString(), params, -1, -1);

 List<Creative> list = new ArrayList<Creative>();
 System.out.println(reslut);
for (Map<String, Object> map : reslut){
 Creative creative = new Creative();
 creative.setUuid(map.get("uuid").toString());
 creative.setName(map.get("name").toString());
 creative.setPublishdate((Date) map.get("publishdate"));
 creative.setEnddate((Date) map.get("enddate"));
 creative.setPublishnum((Integer) map.get("publishnum"));
 creative.setLastnum((Integer) map.get("lastnum"));
 creative.setIntegral((Integer)map.get("Integral"));
 creative.setContent((String) map.get("content"));
 creative.setSmallattachment((Attachment) map.get("smallimageid"));
// creative.setSmallattachment((Attachment) map.get("smallimageid"));
 creative.setStatus((Integer)map.get("status"));
 }
return reslut;

package com.mb.helper;

import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.mb.exception.BusinessException;
import com.mb.util.StringUtil;
/**
 * hibernate模板类
 * @author fuyp
 *
 */
public class QueryHibernateTemplate extends HibernateTemplate implements IQuery{
public QueryHibernateTemplate() { 
 }

public QueryHibernateTemplate(SessionFactory sessionFactory) {
super(sessionFactory);
 }

/**
 * 原型sql检索一览（分页）
 * 
 * @author fuyp
 * @param queryModel
 * 检索条件
 * @param firstResult
 * 开始结果数
 * @param maxResults
 * 最大条数
 * @return 返回检索数据 
 */
 public List findBySQL(final QueryModel queryModel, final int firstResult, 
final int maxResults) {
if (queryModel==null) {
return null;
 }
return executeFind(new HibernateCallback<List>() { 
public List doInHibernate(Session session) throws HibernateException, SQLException {
// 创建query
 org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(queryModel.getNativeSQL());

for (EntityModel model : queryModel.getEntityList()) {
if(model.isReturn()) {
 query.addEntity(model.getAliasName(), model.getClazz());
 }
 }
 Object[] values = queryModel.getParams().toArray();
if (values != null) {
for (int i = 0; i < values.length; i++) {
 query.setParameter(i, values[i]);
 }
 }
 prepareQuery(query);
if(firstResult >=0) {
//定义从第几条开始查询
 query.setFirstResult(firstResult);
 }

if(maxResults > 0) {
//定义返回的记录数 
 query.setMaxResults(maxResults);
 }

 List list = query.list(); 
return list; 
 } 
 });
 }
/**
 * hql检索一览（分页）
 * 
 * @author fuyp
 * @param queryModel
 * 检索条件
 * @param firstResult
 * 开始结果数
 * @param maxResults
 * 最大条数
 * @return 返回检索数据 
 */
 public List findByHQL(final HQLQueryModel queryModel, final int firstResult, 
final int maxResults) {
if (queryModel==null) {
return null;
 }
return this.executeFind(new HibernateCallback<List>() { 
public List doInHibernate(Session session) 
throws HibernateException, SQLException { 
 StringBuilder tempNativeHQL = new StringBuilder();
for (EntityModel model : queryModel.getEntityList()) {
if(model.isReturn()) {
if(StringUtil.indexOf(tempNativeHQL.toString(), "select")==-1){
 tempNativeHQL.append(" select "+model.getAliasName() + " ");
 }else{
 tempNativeHQL.append(" , "+ model.getAliasName() + " ");
 }
 }
 }
 tempNativeHQL.append(queryModel.getNativeHQL());
 Query query = session.createQuery(tempNativeHQL.toString());
// 取出参数值 
 List<Object> values = queryModel.getParams();
if (values != null) {
for (int i = 0; i < values.size(); i++) {
 query.setParameter(i, values.get(i));
 }
 }
 prepareQuery(query);
if(firstResult >=0) {
//定义从第几条开始查询
 query.setFirstResult(firstResult);
 }

if(maxResults > 0) {
//定义返回的记录数 
 query.setMaxResults(maxResults);
 }

return query.list();
 } 
 });
 }
/**
 * 原型sql返回记录数(总数)
 * 
 * @param queryModel
 * @return 总数
 */
 public Object getRecordCount(final QueryModel queryModel) {
if (queryModel == null) {
return new Integer(0);
 }
 List list = getResultCountList(queryModel);
if(list.size() > 0 ) {
return new Integer(list.get(0).toString());
 } else{
return new Integer(0);
 }
 }

/**
 * hql返回记录数(总数)
 * 
 * @param queryModel
 * @return 总数
 */
 public Object getRecordCount(final HQLQueryModel queryModel) {
if (queryModel == null) {
return new Integer(0);
 }
return execute(new HibernateCallback<Object>() { 
public Object doInHibernate(Session session) 
throws HibernateException, SQLException { 
 String sql = queryModel.getCountHQL();
 Query query = session.createQuery(sql);
 List<Object> values = queryModel.getParams();
if (values != null) {
for (int i = 0; i < values.size(); i++) {
 query.setParameter(i, values.get(i));
 }
 }
 prepareQuery(query);
 Object obj = query.uniqueResult();
return obj; 
 } 
 }); 
 }
/**
 * 原型sql返回记录数(总数)
 * 
 * @param queryModel
 * @return 总数 */ private List getResultCountList(final QueryModel queryModel) { return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { String sql= queryModel.getCountSQL(); org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(sql); Object[] values = queryModel.getParams().toArray(); if (values != null) { for (int i = 0; i < values.length; i++) { query.setParameter(i, values[i]); } } prepareQuery(query); List list = query.list(); return list; } }); } /** * 原型sql检索一览（分页） * * @param querySql * sql文 * @param queryModel * 参数 * @param firstResult * 开始结果数 * @param maxResults * 最大条数 * @return 返回检索数据 */ public List findBySQL(final String querySql,final QueryModel queryModel, final int firstResult,final int maxResults) { if (StringUtil.isEmpty(querySql)) { return null; } return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); if(queryModel!=null){ for (EntityModel model : queryModel.getEntityList()) { if(model.isReturn()) { query.addEntity(model.getAliasName(), model.getClazz()); } } Object[] values = queryModel.getParams().toArray(); if (values != null) { for (int i = 0; i < values.length; i++) { query.setParameter(i, values[i]); } } } prepareQuery(query); if(firstResult >=0) { //定义从第几条开始查询 query.setFirstResult(firstResult); } if(maxResults > 0) { //定义返回的记录数 query.setMaxResults(maxResults); } List list = query.list(); return list; } }); } /** * 视图表检索一览的共通方法。 * @param querySql * @param firstResult * @param maxResults * @param clazz * 返回的class * @return List */ public List findBySQL(final String querySql, final int firstResult,final int maxResults, final Class clazz) { if (StringUtil.isEmpty(querySql)) { return null; } return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); prepareQuery(query); try { Method method= clazz.getMethod("setElementsType",org.hibernate.SQLQuery.class); method.invoke(clazz, query); } catch (Exception e) { e.printStackTrace(); return null; } if(firstResult >=0){ query.setFirstResult(firstResult);//定义从第几条开始查询 } if(maxResults > 0){ query.setMaxResults(maxResults);//定义返回的记录数 } query.setResultTransformer(Transformers.aliasToBean(clazz)); List list = query.list(); return list; } }); } /** * 视图表检索一览的共通方法。 * @param querySql * @param firstResult * @param maxResults * @param clazz * 返回的class * @return List */ public List findBySQL(final String querySql,final QueryModel queryModel, final int firstResult,final int maxResults,final Class clazz) { if (StringUtil.isEmpty(querySql)) { return null; } return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); prepareQuery(query); try { Method method= clazz.getMethod("setElementsType",org.hibernate.SQLQuery.class); method.invoke(clazz, query); } catch (Exception e) { new BusinessException(""); } if(queryModel!=null){ for (EntityModel model : queryModel.getEntityList()) { if(model.isReturn()) { query.addEntity(model.getAliasName(), model.getClazz()); } } Object[] values = queryModel.getParams().toArray(); if (values != null) { for (int i = 0; i < values.length; i++) { query.setParameter(i, values[i]); } } } if(firstResult >=0) { query.setFirstResult(firstResult);//定义从第几条开始查询 } if(maxResults > 0) { query.setMaxResults(maxResults);//定义返回的记录数 } query.setResultTransformer(Transformers.aliasToBean(clazz)); List list = query.list(); return list; } }); } /** * 视图表检索一览的共通方法。（根据视图中的方法不同得到值） * @param querySql * @param firstResult * @param maxResults * @param clazz * 返回的class * @return List */ public List findBySQL(final String querySql, final int firstResult,final int maxResults, final Class clazz, final String setMethodName) { if (StringUtil.isEmpty(querySql)) { return null; } return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); prepareQuery(query); try { if(StringUtil.isNullOrEmpty(setMethodName)) { Method method= clazz.getMethod("setElementsType", org.hibernate.SQLQuery.class); method.invoke(clazz, query); } else { Method method= clazz.getMethod(setMethodName, org.hibernate.SQLQuery.class); method.invoke(clazz, query); } } catch (Exception e) { e.printStackTrace(); return null; } if(firstResult >=0){ query.setFirstResult(firstResult);//定义从第几条开始查询 } if(maxResults > 0){ query.setMaxResults(maxResults);//定义返回的记录数 } query.setResultTransformer(Transformers.aliasToBean(clazz)); List list = query.list(); return list; } }); } /** * @author liuyt * 本地SQL查询结果转换成Map集合 * @param querySql sql语句 * @param queryModel 查询参数 * @param firstResult 开始记录数 * @param maxResults 最大记录数 * @return */ public List<Map<String, Object>> findMapBySQL(final String querySql,final QueryModel queryModel, final int firstResult,final int maxResults) { if (StringUtil.isEmpty(querySql)) { return null; } return null; } /** * 原型sql返回记录数(总数) * @param querySQL * @param queryModel * @return 总数 */ public Integer getRecordMapParamCount(final String querySQL , final Map<String,Object> params) { if (StringUtil.isEmpty(querySQL)) { return null; } // 检索总数 List iCount = this.executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { String countsql = "select count(*) "; countsql = countsql + querySQL; org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(countsql); // 添加参数 if (params != null && !params.isEmpty()) { for (String key : params.keySet()) { query.setParameter(key, params.get(key)); } } prepareQuery(query); List list = query.list(); return list; } }); if(iCount ==null || iCount.isEmpty()) { return new Integer(0); } else { return new Integer(iCount.get(0).toString()); } } /** * 通过检索条件查询数据一览 * @author fuyp * @param querySql 检索sql * @param params 参数 * @param firstResult * @param maxResults * @return */ public List<Map<String, Object>> findMapParamBySQL(final String querySql,final Map<String,Object> params, final int firstResult,final int maxResults) { if (StringUtil.isEmpty(querySql)) { return null; } return executeFind(new HibernateCallback<List<Map<String, Object>>>() { public List<Map<String, Object>> doInHibernate(Session session) throws HibernateException, SQLException { org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); // 添加参数 if (params != null && !params.isEmpty()) { for (String key : params.keySet()) { query.setParameter(key, params.get(key)); } } prepareQuery(query); if(firstResult >=0) { query.setFirstResult(firstResult);//定义从第几条开始查询 } if(maxResults > 0) { query.setMaxResults(maxResults);//定义返回的记录数 } // 设置返回结果集为MAP形式 query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> list = query.list(); return list; } }); } /** * 原型sql返回记录数(总数) * @param querySQL * @param queryModel * @return 总数 */ public Object getRecordCount(final String querySQL , final QueryModel queryModel) { if (StringUtil.isEmpty(querySQL)) { return new Integer(0); } List list = getRecordCountList(querySQL, queryModel); if(list.size() > 0 ) return new Integer(list.get(0).toString()); else return new Integer(0); } /** * 原型sql返回记录数(总数) * @param querySQL * @param queryModel * @return 总数 */ private List getRecordCountList(final String querySQL,final QueryModel queryModel) { if (StringUtil.isEmpty(querySQL)) { return null; } return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { String countsql = "select count(*) "; countsql = countsql + querySQL; org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(countsql); if(queryModel!=null){ Object[] values = queryModel.getParams().toArray(); if (values != null) { for (int i = 0; i < values.length; i++) { query.setParameter(i, values[i]); } } } prepareQuery(query); List list = query.list(); return list; } }); } /** * 2级缓存的取得对象的方法 * @param sql * @return List */ public List findCacheList(String sql,Object... params){ if (StringUtil.isEmpty(sql)) { return null; } this.setCacheQueries(true); List list = null; if(params==null || params.length < 1) { list = this.find(sql); } else { list = this.find(sql, params); } this.setCacheQueries(false); return list; } /** * 2级缓存的取得对象的方法 * @param sql * @return List */ public List findCacheListByNamedParam(String queryString, String[] paramNames, Object[] values) { if (StringUtil.isEmpty(queryString)) { return null; } this.setCacheQueries(true); List list = null; if(paramNames == null || paramNames.length < 1) { list = this.find(queryString); } else { list = this.findByNamedParam(queryString, paramNames, values); } this.setCacheQueries(false); return list; } /** * @see 根据纯hql实现翻页效果 * * @param iPaginatedList * 翻页实体 * @param fromHql * from带条件的hql * @param findObject * select的语句 * @param firstResult * 开始点 * @param maxResults * 最大的结果集 * @param params * hql的参数 * @return 返回翻页实体 */ public IPaginatedList getRecordsPageByHQL(IPaginatedList iPaginatedList, String fromHql, String findObject,Object... params) { if (StringUtil.isEmpty(fromHql) || StringUtil.isEmpty(findObject)) { return iPaginatedList; } String countHql = "select count(*) "; // 根据纯hql取得一览的总数 int count = this.getRecordCountByHQL(countHql + fromHql,params); List result = new ArrayList(); if (count > 0) { // 根据纯hql实现翻页效果 result = this.findObjectListByHQL(findObject + fromHql, iPaginatedList.getFirstRecordIndex(), iPaginatedList.getPageSize(), params); } iPaginatedList.setTotalNumberOfRows(count); iPaginatedList.setList(result); return iPaginatedList; } /** * @see 根据纯hql实现翻页效果 * @author fuyp * * @param hql * hql语句 * @param firstResult * 开始点 * @param maxResults * 最大的结果集 * @param params * hql的参数 * @return 返回翻页的结果集 */ public List findObjectListByHQL(final String hql, final int firstResult,final int maxResults ,final Object... params){ if (StringUtil.isEmpty(hql)) { return null; } List result = executeFind(new HibernateCallback<List>() { @Override public List doInHibernate(Session session) throws HibernateException, SQLException { Query query = session.createQuery(hql); if(params!=null){ for (int i = 0; i < params.length; i++) { query.setParameter(i, params[i]); } } prepareQuery(query); if (firstResult >= 0) { // 定义从第几条开始查询 query.setFirstResult(firstResult); } if (maxResults > 0) { // 定义返回的记录数 query.setMaxResults(maxResults); } List result = query.list(); return result; } }); return result; } /** * @see 根据纯hql取得一览的总数 * @author fuyp * * @param hql * hql语句 * @param params * hql的参数 * @return 返回翻页的结果集 */ protected int getRecordCountByHQL(String hql ,Object... params){ if (StringUtil.isEmpty(hql)) { return 0; } Object oCount= super.iterate(hql,params).next(); if (oCount == null) { return 0; } else { return ((Long) oCount).intValue(); } }}


/**
 * 
 * @param request
 * @return
 * 
 * 功 能 ：得到用户Ip地址
 */
 public static String getRemortIP(HttpServletRequest request) {
 if (request.getHeader("x-forwarded-for") == null) {
 return request.getRemoteAddr();
 }
 return request.getHeader("x-forwarded-for");
 }

转载于:https://www.cnblogs.com/yaoyao66123/p/4904284.html