org.hibernate.SQLQuery Java Examples

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

/**
 * 原生sql 多表关联分页查询 映射Map 或者 Class
 * @param countSql
 * @param sql
 * @param pageModel
 * @param result  映射的对象 （Map 或者 Class）
 * @return
 */
public Page createNativePageQuery(StringBuilder countSql , StringBuilder sql , PageModel pageModel,ResultTransformer result){
    Query query1 = entityManager.createNativeQuery(countSql.toString());
    long count =((BigInteger) query1.getSingleResult()).longValue() ;
    if(pageModel.getProperty()!=null && pageModel.getProperty().size()>0 && pageModel.getDirection().size() == pageModel.getProperty().size()){
        sql.append(" order by") ;
        for(int i = 0 ; i < pageModel.getProperty().size() ; i++){
            sql.append(" "+pageModel.getProperty().get(i)+" "+pageModel.getDirection().get(i)+" ");
            if(i < pageModel.getProperty().size()-1){
                sql.append(",");
            }
        }
    }
    sql.append(" limit "+pageModel.getPageSize()*(pageModel.getPageNo()-1)+" , "+pageModel.getPageSize());
    javax.persistence.Query query2 = entityManager.createNativeQuery(sql.toString());
    query2.unwrap(SQLQuery.class).setResultTransformer(result);
    List list = query2.getResultList() ;
    return new PageImpl<>(list,pageModel.getPageable(),count);
}
 

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

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param sqls
 * @param param
 * @return
 * @throws DaoException <br>
 */
@Override
public int[] batchExcuteSql(final String[] sqls, final DataParam param) throws DaoException {
    try {
        Session session = getSession();
        session.flush();

        int[] result = new int[sqls.length];
        SQLQuery query;
        for (int i = 0; i < sqls.length; i++) {
            query = session.createSQLQuery(sqls[i]);
            setParamMap(param.getParamMap(), query);
            result[i] = query.executeUpdate();
        }
        return result;
    }
    catch (Exception e) {
        logger.error(e.getMessage(), e);
        throw new DaoException(ErrorCodeDef.BATCH_EXECUTE_ERROR_10012, e);
    }
}
 

public List doLimitBackInfo(final String hql,final int pageCode,final int pageSize){
    //调用模板的execute方法，参数是实现了HibernateCallback接口的匿名类，
    return (List) this.getHibernateTemplate().execute(new HibernateCallback(){
        //重写其doInHibernate方法返回一个object对象，
        public Object doInHibernate(Session session)
                throws HibernateException, SQLException {
            //创建query对象
        	SQLQuery query=session.createSQLQuery(hql);
            //返回其执行了分布方法的list
            return query.setFirstResult((pageCode-1)*pageSize).setMaxResults(pageSize).list();
             
        }
         
    });
     
}
 

public int deleteCollectionReferences(final Collection collection)
{
   return getHibernateTemplate().execute  (
      new HibernateCallback<Integer>()
      {
         public Integer doInHibernate(Session session) 
            throws HibernateException, SQLException
         {
            String sql = "DELETE FROM FILESCANNER_COLLECTIONS s " +
                     " WHERE s.COLLECTIONS_UUID = :cid";
            SQLQuery query = session.createSQLQuery(sql);
            query.setString ("cid", collection.getUUID());
            return query.executeUpdate ();
         }
      });
}
 

@Override
public void delete (final FileScanner scanner)
{
   getHibernateTemplate ().execute (new HibernateCallback<Void>()
   {
      @Override
      public Void doInHibernate (Session session) throws HibernateException,
         SQLException
      {
         String sql = "DELETE FROM FILE_SCANNER_PREFERENCES " +
            "WHERE FILE_SCANNER_ID = ?";
         SQLQuery query = session.createSQLQuery (sql);
         query.setLong (0, scanner.getId ());
         query.executeUpdate ();
         return null;
      }
   });
   super.delete (scanner);
}
 

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

@SuppressWarnings("unchecked")
@Override
public Map<Integer, Integer> countTotalDownloadedItemsForCollectionIds(final List<Long> ids) {
    return (Map<Integer, Integer>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
        @Override
        public Object doInHibernate(Session session) throws HibernateException {
            String sql = " select c.collection_id as id, " +
                    " sum(c.count) as count " +
                    " from collection_log c " +
                    " where c.collection_id in :ids " +
                    " group by c.collection_id ";
            SQLQuery sqlQuery = session.createSQLQuery(sql);
            sqlQuery.addScalar("id", new IntegerType());
            sqlQuery.addScalar("count", new IntegerType());
            sqlQuery.setParameterList("ids", ids);

            List<Object[]> list = sqlQuery.list();
            Map<Integer, Integer> result = new HashMap<Integer, Integer>();
            for (Object[] row : list) {
                result.put((Integer) row[0], (Integer) row[1]);
            }

            return result;
        }
    });
}
 

public void migrate() throws Exception {
    if (nativeDbQueries == NativeDbQueries.NOT_SUPPORTED_DB) {
        logger.warn("Could not migrate database to set unlimited raw/json/human " +
                "message columns. Reason: migration supported for mysql and postgres only");
        return;
    }
    if (nativeDbQueries.isMigrationNeeded(session, databaseName)) {
        Transaction transaction = session.beginTransaction();
        try(AutoCloseable closeable = transaction::commit) {
            SQLQuery migrateSqlQuery = session.createSQLQuery(nativeDbQueries.getMigrationQuery());
            migrateSqlQuery.executeUpdate();
        } catch (Exception e) {
            transaction.rollback();
            throw e;
        }
    }
}
 

@Override
public void deleteAll ()
{
   getHibernateTemplate ().execute (new HibernateCallback<Void> ()
   {
      @Override
      public Void doInHibernate (Session session)
            throws HibernateException, SQLException
      {
         SQLQuery query =
               session.createSQLQuery ("DELETE FROM USER_RESTRICTIONS");
         query.executeUpdate ();
         query = session.createSQLQuery ("DELETE  FROM ACCESS_RESTRICTION");
         query.executeUpdate ();
         return null;
      }
   });
}
 

/**
 * @Title: executeSQLQuery
 * @Description: 执行SQL查询，多个参数调用
 * @param @param sql
 * @param @param params
 * @param @return
 * @return List<T>
 */
public <T> PageResult<T> executeSQLQuery(final String sql, final Class<T> clazz, final Limit limit, final Object... params) {
	String countSql = this.getCountSql(sql);
	List<T> list = (List<T>) getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
		public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
			SQLQuery query = session.createSQLQuery(sql);
			query.addEntity(clazz);
			query.setMaxResults(limit.getSize());
			query.setFirstResult(limit.getStart());
			buildParameters(query, params);
			return query.list();
		}
	});
	BigInteger bigTotalCount = this.getUniqueSQLResult(countSql, params);
	return new PageResult<T>(bigTotalCount.intValue(), limit, list);
}
 

@Override
public List<String> getEligibleFacebookCollectionsToReRun() {
	
	List<String> collectionCodes = new ArrayList<String>(); 
	@SuppressWarnings("unchecked")
	List<Object[]> collections = (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
		@Override
		public Object doInHibernate(Session session) throws HibernateException {
			String sql = " SELECT c.code FROM collection c " +
					" WHERE c.provider = 'Facebook' AND (c.status = 0 OR c.status = 2 OR c.status = 5 OR c.status = 8) "
					+ "AND date_add(c.last_execution_time, interval c.fetch_interval hour) <= now()";

			SQLQuery sqlQuery = session.createSQLQuery(sql);
			List<Object[]> codes = sqlQuery.list();

			return codes != null ? codes : Collections.emptyList();
		}
	});

	if(collections != null && collections.size() > 0) {
		for(Object col : collections) {
			collectionCodes.add((String) col);
		}
	}
	return collectionCodes;
}
 

/**
 * 原生sql 多表关联分页查询 映射Map 或者 Class
 * @param countSql
 * @param sql
 * @param pageModel
 * @param result  映射的对象 （Map 或者 Class）
 * @return
 */
public Page createNativePageQuery(StringBuilder countSql , StringBuilder sql , PageModel pageModel,ResultTransformer result){
    Query query1 = entityManager.createNativeQuery(countSql.toString());
    long count =((BigInteger) query1.getSingleResult()).longValue() ;
    if(pageModel.getProperty()!=null && pageModel.getProperty().size()>0 && pageModel.getDirection().size() == pageModel.getProperty().size()){
        sql.append(" order by") ;
        for(int i = 0 ; i < pageModel.getProperty().size() ; i++){
            sql.append(" "+pageModel.getProperty().get(i)+" "+pageModel.getDirection().get(i)+" ");
            if(i < pageModel.getProperty().size()-1){
                sql.append(",");
            }
        }
    }
    sql.append(" limit "+pageModel.getPageSize()*(pageModel.getPageNo()-1)+" , "+pageModel.getPageSize());
    javax.persistence.Query query2 = entityManager.createNativeQuery(sql.toString());
    query2.unwrap(SQLQuery.class).setResultTransformer(result);
    List list = query2.getResultList() ;
    return new PageImpl<>(list,pageModel.getPageable(),count);
}
 

private int countAdvanced (final String sid)
{
   return dao.getHibernateTemplate ().execute (
      new HibernateCallback<Integer> ()
      {
         @Override
         public Integer doInHibernate (Session session)
            throws HibernateException, SQLException
         {
            String hql =
               "SELECT count(*) FROM SEARCH_ADVANCED WHERE SEARCH_UUID = ?";
            SQLQuery query = session.createSQLQuery (hql);
            query.setString (0, sid);
            return ((BigInteger) query.uniqueResult ()).intValue ();
         }
      });
}
 

/**
 * 
 * @author Jon Chiang
 * @create_date 2014-5-9 下午2:41:58
 * @param hql
 * @param pageSize
 * @param startIndex
 * @return
 */
public <T> List<T> findBySQL(final String sql, final Limit limit, final Object... params) {
	return (List<T>) getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
		public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
			SQLQuery query = session.createSQLQuery(sql);
			buildParameters(query, params);
			if (null != limit) {
				query.setFirstResult(limit.getStart());
				query.setMaxResults(limit.getSize());
			}
			return query.list();
		}
	});
}
 

/**
 * TODO: manage access by page.
 * @param user
 * @return
 */
public List<Product> getNoCollectionProducts (User user)
{
   ArrayList<Product> products = new ArrayList<> ();
   StringBuilder sqlBuilder = new StringBuilder ();
   sqlBuilder.append ("SELECT p.ID ");
   sqlBuilder.append ("FROM PRODUCTS p ");
   sqlBuilder.append ("LEFT OUTER JOIN COLLECTION_PRODUCT cp ")
             .append ("ON p.ID = cp.PRODUCTS_ID ");
   sqlBuilder.append ("WHERE cp.COLLECTIONS_UUID IS NULL");
   final String sql = sqlBuilder.toString ();
   List<BigInteger> queryResult =
      getHibernateTemplate ().execute (
         new HibernateCallback<List<BigInteger>> ()
         {
            @Override
            @SuppressWarnings ("unchecked")
            public List<BigInteger> doInHibernate (Session session)
               throws HibernateException, SQLException
            {
               SQLQuery query = session.createSQLQuery (sql);
               return query.list ();
            }
         });

   for (BigInteger pid : queryResult)
   {
      Product p = read (pid.longValue ());
      if (p == null)
      {
         throw new IllegalStateException (
            "Existing product is null ! product id = " + pid.longValue ());
      }
      products.add (p);
   }

   return products;
}
 

/**
 * @Title: executeSQLQuery
 * @Description: 分页执行sql查询
 * @param @param sql
 * @param @param params
 * @param @param pageSize
 * @param @param startIndex
 * @param @return
 * @return List<T>
 */
public <T> List<T> executeSQLQuery(final String sql, final Object[] params, final int pageSize, final int startIndex) {
	return (List<T>) getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
		public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
			SQLQuery query = session.createSQLQuery(sql);
			buildParameters(query, params);
			query.setFirstResult(startIndex);
			query.setMaxResults(pageSize);
			return query.list();
		}
	});
}
 

/**
 * @Title: executeSQLQuery
 * @Description: 执行SQL查询，多个参数调用
 * @param @param sql
 * @param @param params
 * @param @return
 * @return List<T>
 */
public <T> List<T> executeSQLQuery(final String sql, final Object... params) {
	return (List<T>) getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
		public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
			SQLQuery query = session.createSQLQuery(sql);
			buildParameters(query, params);
			return query.list();
		}
	});
}
 

/**
 * @Title: executeSQLQuery
 * @Description: 执行SQL查询，多个参数调用
 * @param @param sql
 * @param @param params
 * @param @return
 * @return List<T>
 */
public <T> List<T> executeSQLQuery(final String sql, final Class<T> objectClass, final Object... params) {
	return (List<T>) getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
		public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
			SQLQuery query = session.createSQLQuery(sql).addEntity("bean", objectClass);
			buildParameters(query, params);
			return query.list();
		}
	});
}
 

/**
 * @Title: executeSQLQuery
 * @Description: sql分页查询，设置返回对象
 * @param @param sql
 * @param @param params
 * @param @param pageSize
 * @param @param startIndex
 * @param @param classObj
 * @param @return
 * @return List<T>
 */
public <T> List<T> executeSQLQuery(final String sql, final Object[] params, final int pageSize, final int startIndex, final Class<T> objectClass) {
	return (List<T>) getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
		public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
			SQLQuery query = session.createSQLQuery(sql).addEntity("bean", objectClass);
			buildParameters(query, params);
			query.setFirstResult(startIndex);
			query.setMaxResults(pageSize);
			return query.list();
		}
	});
}
 

public static int updateNaturalOrderForPipeline(Session session, Long pipelineId, double naturalOrder) {
    String sql = "UPDATE pipelines SET naturalOrder = :naturalOrder WHERE id = :pipelineId";
    SQLQuery query = session.createSQLQuery(sql);
    query.setLong("pipelineId", pipelineId);
    query.setDouble("naturalOrder", naturalOrder);
    return query.executeUpdate();
}
 

public SQLQuery setResultSetMapping(String name) {
	ResultSetMappingDefinition mapping = session.getFactory().getResultSetMapping( name );
	if ( mapping == null ) {
		throw new MappingException( "Unknown SqlResultSetMapping [" + name + "]" );
	}
	NativeSQLQueryReturn[] returns = mapping.getQueryReturns();
	int length = returns.length;
	for ( int index = 0 ; index < length ; index++ ) {
		queryReturns.add( returns[index] );
	}
	return this;
}
 

public int getCountBySql(String sql, Map<String, Object> map) {
	//sql = filter(sql, map);
	SQLQuery query = this.hibernateTemplate.getSessionFactory()
			.getCurrentSession().createSQLQuery(sql);
	setQuery(map, query);
	return Integer.parseInt(query.uniqueResult().toString());
}
 

/**
 * 
 * @author Jon Chiang
 * @create_date 2014-5-9 下午2:41:58
 * @param hql
 * @param pageSize
 * @param startIndex
 * @return
 */
public <T> List<T> findBySQL(final String sql, final Class<T> clazz, final Object... params) {
	return (List<T>) getHibernateTemplate().execute(new HibernateCallback<List<T>>() {
		public List<T> doInHibernate(Session session) throws HibernateException, SQLException {
			SQLQuery query = session.createSQLQuery(sql);
			query.addEntity("bean", clazz);
			buildParameters(query, params);
			return query.list();
		}
	});
}
 

@SuppressWarnings("unchecked")
@Override
public List<Collection> getPaginatedDataForPublic(final Integer start, final Integer limit, final Enum statusValue) {
	//        Workaround as criteria query gets result for different managers and in the end we get less then limit records.
	List<BigInteger> collectionIds = (List<BigInteger>) getHibernateTemplate().execute(new HibernateCallback<Object>() {
		@Override
		public Object doInHibernate(Session session) throws HibernateException {

			String sql = " SELECT DISTINCT c.id FROM collection c" +
					" WHERE (c.publicly_listed = 1 and c.status = :statusValue) " +
					" order by c.start_date DESC, c.created_at DESC LIMIT :start, :limit ";


			SQLQuery sqlQuery = session.createSQLQuery(sql);
			sqlQuery.setParameter("start", start);
			sqlQuery.setParameter("limit", limit);
			sqlQuery.setParameter("statusValue", statusValue.ordinal());
			List<BigInteger> ids = sqlQuery.list();

			return ids != null ? ids : Collections.emptyList();
		}
	});

	List<Collection> a = new ArrayList<Collection>();

	for(int i =0; i < collectionIds.size(); i++){
		Collection collection =	this.findById(collectionIds.get(i).longValue());
		a.add(collection) ;
	}
	return a;

}
 

public int getCountBySql(String sql, Object[] params) {
	//sql = filter(sql, map);
	SQLQuery query = this.hibernateTemplate.getSessionFactory()
			.getCurrentSession().createSQLQuery(sql);
	setQuery(params, query);
	return Integer.parseInt(query.uniqueResult().toString());
}
 

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;

}
 

public int executeBySql(String sql, Map<String, Object> map) {
	SQLQuery query = hibernateTemplate.getSessionFactory()
			.getCurrentSession().createSQLQuery(sql);
	if (map != null) {
		setQuery(map, query);
	}
	return query.executeUpdate();
}
 

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