Java Code Examples for org.hibernate.SQLQuery#addEntity()

@Override
@CacheEvict(value = "exchangeRate", key = "#e.region")
public void updateExchange(ExchangeRate e) {
    Session session = sessionFactory.openSession();
    session.getTransaction().begin();
    SQLQuery query =  session.createSQLQuery("update exchangerate \n" +
            " set usdollar = :usdollar" +
            " where region = :region and ratedate = TO_DATE('2015-05-02','YYYY-MM-DD')") ;

    query.setParameter("region", e.getRegion());
    query.setParameter("usdollar", e.getUsdollar());
    query.addEntity(ExchangeRate.class);
    query.executeUpdate();
    session.getTransaction().commit();
    session.close();
}
 

@Override
   public List<Policy> getAllPoliciesWithUserConsentsCount() {
final String LOAD_POLICIES_WITH_CONSENTS_COUNT = "SELECT policy.*, COUNT(policyConsent.uid) AS userConsentsCount "
	+ "FROM lams_policy AS policy "
	+ "LEFT JOIN lams_policy_consent AS policyConsent ON policyConsent.policy_uid = policy.uid "
	+ "GROUP BY policy.uid ORDER BY policy.last_modified ASC";

SQLQuery query = getSession().createSQLQuery(LOAD_POLICIES_WITH_CONSENTS_COUNT);
query.addEntity(Policy.class);
query.addScalar("userConsentsCount");
List<Object[]> resultQuery = query.list();

// this map keeps the insertion order
LinkedList<Policy> policies = new LinkedList<Policy>();
// make the result easier to process
for (Object[] entry : resultQuery) {
    Policy policy = (Policy) entry[0];
    int userConsentsCount = ((Number) entry[1]).intValue();
    policy.setUserConsentsCount(userConsentsCount);

    policies.add(policy);
}
return policies;
   }
 

public <N extends Object>Pager<N> findBySql(String sql, Object[] args,
		Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
	sql = initSort(sql);
	String cq = getCountHql(sql,false);
	SQLQuery sq = getSession().createSQLQuery(sql);
	SQLQuery cquery = getSession().createSQLQuery(cq);
	setAliasParameter(sq, alias);
	setAliasParameter(cquery, alias);
	setParameter(sq, args);
	setParameter(cquery, args);
	Pager<N> pages = new Pager<N>();
	setPagers(sq, pages);
	if(hasEntity) {
		sq.addEntity(clz);
	} else {
		sq.setResultTransformer(Transformers.aliasToBean(clz));
	}
	List<N> datas = sq.list();
	pages.setDatas(datas);
	long total = ((BigInteger)cquery.uniqueResult()).longValue();
	pages.setTotal(total);
	return pages;
}
 

public <N extends Object> List<N> listBySql(String sql, Object[] args, Map<String, Object> alias, Class<?> clz,
        boolean hasEntiry) {
    sql = initSort(sql);
    SQLQuery sq = getSession().createSQLQuery(sql);
    setAliasParameter(sq, alias);
    setParameter(sq, args);
    if (hasEntiry) {

        sq.addEntity(clz);

    } else {

        sq.setResultTransformer(Transformers.aliasToBean(clz));
    }

    return sq.list();
}
 

/**
 * 根据查询SQL与参数列表创建SQLQuery对象
 * 
 * @param queryOrNamedSQLQuery query 或者 NamedSQLQuery
 * @param values 数量可变的参数,按顺序绑定.
 *            
 * @return {@link SQLQuery}
 */
protected SQLQuery createSQLQuery( String queryOrNamedSQLQuery,  Object... values) {
	Assert.hasText(queryOrNamedSQLQuery, "queryOrNamedSQLQuery不能为空");
	SessionFactoryImpl factory = (SessionFactoryImpl) sessionFactory;
	NamedSQLQueryDefinition nsqlqd = factory.getNamedSQLQuery( queryOrNamedSQLQuery );
	Query query = null;
	
	if (nsqlqd != null) {
		query = getSession().getNamedQuery(queryOrNamedSQLQuery);
	} else {
		query = getSession().createSQLQuery(queryOrNamedSQLQuery);
	}
	
	setQueryValues(query, values);
	SQLQuery sqlQuery = (SQLQuery)query;
	
	return sqlQuery.addEntity(entityClass);
}
 

@Override
   public List<PolicyDTO> getPolicyDtosByUser(Integer userId) {
final String LOAD_POLICIES_WITH_USER_CONSENTS = "SELECT policy.*, policyConsent.uid IS NOT NULL as isConsentedByUser, policyConsent.date_agreed_on as dateAgreedOn "
	+ "FROM lams_policy AS policy "
	+ "LEFT JOIN lams_policy_consent AS policyConsent ON policyConsent.policy_uid = policy.uid AND policyConsent.user_id = :userId "
	+ "WHERE policy.policy_state_id=1";

SQLQuery query = getSession().createSQLQuery(LOAD_POLICIES_WITH_USER_CONSENTS);
query.addEntity(Policy.class);
query.addScalar("isConsentedByUser");
query.addScalar("dateAgreedOn");
query.setInteger("userId", userId);
List<Object[]> resultQuery = query.list();

// this map keeps the insertion order
LinkedList<PolicyDTO> policyDtos = new LinkedList<PolicyDTO>();
// make the result easier to process
for (Object[] entry : resultQuery) {
    Policy policy = (Policy) entry[0];

    PolicyDTO policyDto = new PolicyDTO(policy);

    boolean isConsentedByUser = ((Number) entry[1]).intValue() == 1;
    policyDto.setConsentedByUser(isConsentedByUser);

    Date dateAgreedOn = (Date) entry[2];
    policyDto.setDateAgreedOn(dateAgreedOn);

    policyDtos.add(policyDto);
}
return policyDtos;
   }
 

@Override
@Cacheable(value = "exchangeRate")
public String getExchangeRateByRegion(String region) {
    Session session = sessionFactory.openSession();
    // in real life, it should be current date time
    SQLQuery query = session.createSQLQuery("select * from exchangerate e where e.ratedate = TO_DATE('2015-05-02','YYYY-MM-DD') and e.region=:region");
    query.setParameter("region", region);
    query.addEntity(ExchangeRate.class);
    ExchangeRate res =  (ExchangeRate)query.uniqueResult();
    session.close();
    return String.valueOf(res.getUsdollar());
}
 

public <N extends Object>List<N> listBySql(String sql, Object[] args,
		Map<String, Object> alias, Class<?> clz, boolean hasEntity) {
	sql = initSort(sql);
	SQLQuery sq = getSession().createSQLQuery(sql);
	setAliasParameter(sq, alias);
	setParameter(sq, args);
	if(hasEntity) {
		sq.addEntity(clz);
	} else 
		sq.setResultTransformer(Transformers.aliasToBean(clz));
	return sq.list();
}
 

public <N extends Object> Pager<N> findBySql(String sql, Object[] args, Map<String, Object> alias, Class<?> clz,
        boolean hasEntiry) {

    sql = initSort(sql);
    String cq = getCountHql(sql, false);
    // cq=initSort(cq);不需要再加上就多了一个Order

    SQLQuery sq = getSession().createSQLQuery(sql);
    SQLQuery cquery = getSession().createSQLQuery(cq);
    setAliasParameter(sq, alias);
    setAliasParameter(cquery, alias);
    setParameter(sq, args);
    setParameter(cquery, args);
    Pager<N> pages = new Pager<N>();
    setPagers(sq, pages);
    if (hasEntiry) {

        sq.addEntity(clz);
    } else {
        sq.setResultTransformer(Transformers.aliasToBean(clz));
    }
    List<N> datas = sq.list();
    pages.setDatas(datas);
    long total = ((BigInteger) cquery.uniqueResult()).longValue();
    pages.setTotal(total);

    return pages;

}
 

@SuppressWarnings("unchecked")
public <T> List<T> queryList(Class<T> clazz, String sql, Object... args) {
	SQLQuery query = getSession().createSQLQuery(sql);
	query.addEntity(clazz);
	for(int i = 0; i < args.length; i++) {
		query.setParameter(i, args[i]);
	}
	return (List<T>)query.list();
}
 

public static Query createSearchQuery(CriteriaInstantVO criteriaInstantVO, DBModule module, PSFVO psf, SessionFactory sessionFactory,
		CriterionTieDao criterionTieDao, CriterionPropertyDao criterionPropertyDao, CriterionRestrictionDao criterionRestrictionDao) throws Exception {
	ArrayList<QueryParameterValue> queryValues = new ArrayList<QueryParameterValue>();
	HashMap<NamedParameterValues, Object> namedParameterValuesCache = new HashMap<NamedParameterValues, Object>();
	StringBuilder sqlQuery = createSQLSetStatement(criteriaInstantVO, module, psf, sessionFactory, queryValues,
			createCriterionTieMap(criterionTieDao),
			createCriterionTieNameMap(criterionTieDao),
			createCriterionPropertyMap(module, criterionPropertyDao),
			createCriterionRestrictionMap(criterionRestrictionDao),
			null);
	Class rootEntityClass = getRootEntityClass(module);
	SQLQuery query;
	if (psf != null) {
		if (psf.getUpdateRowCount()) {
			StringBuilder countStatement = new StringBuilder("select count(*) from (");
			countStatement.append(sqlQuery);
			countStatement.append(") as resultset");
			Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(countStatement.toString());
			setQueryValues(countQuery, queryValues, namedParameterValuesCache);
			psf.setRowCount(new Long(countQuery.uniqueResult().toString()));
		}
		String sortField = psf.getSortField();
		if (sortField != null && sortField.length() > 0) {
			// http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx
			sqlQuery.append(" order by ");
			sqlQuery.append(getPropertyColumnName(rootEntityClass, sortField, sessionFactory));
			if (psf.getSortOrder()) {
				sqlQuery.append(" asc");
			} else {
				sqlQuery.append(" desc");
			}
		}
		query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery.toString());
		if (psf.getFirst() != null) {
			query.setFirstResult(psf.getFirst());
		}
		if (psf.getPageSize() != null) {
			query.setMaxResults(psf.getPageSize());
		}
	} else {
		query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery.toString());
	}
	setQueryValues(query, queryValues, namedParameterValuesCache);
	query.addEntity(rootEntityClass);
	return query;
}
 

/**
 * 根据查询HQL与参数列表创建Query对象
 * 
 * @param queryOrSqlQuery query 或者 NamedSQLQuery
 * @param values
 *            命名参数,按名称绑定.
 *            
 * @return {@link Query}           
 * 
 */
protected SQLQuery createSQLQuery( String queryOrSqlQuery, Map<String, ?> values) {
	SQLQuery query = createSQLQuery(queryOrSqlQuery);
	if (values != null) {
		query.setProperties(values);
	}
	return query.addEntity(entityClass);
}