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

@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 boolean isMigrationNeeded(Session session, String dbName) {
    SQLQuery conditionalSqlQuery = session.createSQLQuery(MYSQL_CHECK_MIGRATION_QUERY);
    conditionalSqlQuery.setParameter(0, dbName);
    List<?> conditionalSqlQueryResult = conditionalSqlQuery.list();
    if (CollectionUtils.isEmpty(conditionalSqlQueryResult)) {
        return false;
    }
    return !StringUtils.equalsIgnoreCase("longblob", (String)conditionalSqlQueryResult.get(0));
}
 

@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());
}
 

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param procedureSql
 * @param params
 * @return
 * @throws DaoException <br>
 */

@Override
public <T> List<T> executeProcedure(final String procedureSql, final Object... params) throws DaoException {
    Session session = getSession();
    session.flush();
    SQLQuery sqlQuery = session.createSQLQuery(procedureSql);

    for (int i = 0; i < params.length; i++) {
        sqlQuery.setParameter(i, params[i]);
    }
    return sqlQuery.list();
}
 

@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 Object queryCount(String sql, Object... args) {
    SQLQuery countQuery = getSession().createSQLQuery(sql);
    if(args.length > 0) {
        for (int i = 0; i < args.length; i++) {
            countQuery.setParameter(i, args[i]);
        }
    }
    return countQuery.uniqueResult();
}
 

protected SQLQuery getQuery(String queryString, Map<String, Object> where, int start, int limit, boolean returnMap) {
    Session session = this.getSession();
    SQLQuery query = null;
    if (start > -1 && limit > 0) {
        query = session.createSQLQuery(queryString);
        query.setFirstResult(start);
        query.setMaxResults(limit);
    } else {
        query = session.createSQLQuery(queryString);
    }
    if (where != null) {
        for (Entry<String, Object> entry : where.entrySet()) {
            Object value = entry.getValue();
            String key = entry.getKey();
            if (value instanceof Collection) {
                query.setParameterList(key, (Collection) value);
            } else if (value instanceof Object[]) {
                query.setParameterList(key, (Object[]) value);
            } else {
                query.setParameter(key, value);
            }
        }
    }
    if (returnMap) {
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    }
    return query;
}
 

/**
 * 调用存储过程
 */
@SuppressWarnings({ "unchecked",})
public <T> List<T> executeProcedure(String executeSql,Object... params) {
	SQLQuery sqlQuery = getSession().createSQLQuery(executeSql);
	
	for(int i=0;i<params.length;i++){
		sqlQuery.setParameter(i, params[i]);
	}
	
	return sqlQuery.list();
}
 

public Integer getLatestProcessVersion(String name) {
	SQLQuery query = getSession().createSQLQuery(QUERY_VERSION + " where name = ?");
	query.setParameter(0, name);
	Object result = query.uniqueResult();
	return new Long(ClassHelper.castLong(result)).intValue();
}