Java SQLQuery类代码示例

import org.hibernate.SQLQuery; //导入依赖的package包/类
@Override
   @SuppressWarnings("unchecked")
   public List<VoteStatsDTO> getStatisticsBySession(Long toolContentId) {

SQLQuery query = getSession().createSQLQuery(GET_STATISTICS);
query.addScalar("sessionUid", LongType.INSTANCE).addScalar("sessionName", StringType.INSTANCE)
	.addScalar("countUsersComplete", IntegerType.INSTANCE).setLong("contentId", toolContentId)
	.setResultTransformer(Transformers.aliasToBean(VoteStatsDTO.class));

return query.list();
   }
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
protected void deleteHoldingExtras(Session session, long itemId)
{
	SQLQuery sqlQuery = session
		.createSQLQuery(sqlQuery("delete from %h_authors where %h_id in (select id from %h where item_id = ?)"));
	sqlQuery.setLong(0, itemId);
	sqlQuery.executeUpdate();
	sqlQuery = session
		.createSQLQuery(sqlQuery("delete from %h_ids where %h_id in (select id from %h where item_id = ?)"));
	sqlQuery.setLong(0, itemId);
	sqlQuery.executeUpdate();
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@Override
public String generateCardId(Long cardId) {
	Card card = Card.findCard(cardId);
	if(card.getDesfireIds().get(appName) == null || card.getDesfireIds().get(appName).isEmpty()) {
		Session session = (Session) entityManager.getDelegate();
		SQLQuery existSequenceQuery = session.createSQLQuery("SELECT 1 FROM pg_class where relname = '" + postgresqlSequence + "'");
		if(existSequenceQuery.list().isEmpty()) {
			SQLQuery createSequenceQuery = session.createSQLQuery("CREATE SEQUENCE " + postgresqlSequence);
			int createSequenceQueryResult = createSequenceQuery.executeUpdate();
			log.info("create sequence result : " + createSequenceQueryResult);
		}
		SQLQuery nextValQuery = session.createSQLQuery("SELECT nextval('" + postgresqlSequence + "')");
		BigInteger nextVal = (BigInteger)nextValQuery.list().get(0);
		card.getDesfireIds().put(appName, Long.toString(nextVal.longValue() + getIdCounterBegin(card)));
		card.merge();
	}
	return card.getDesfireIds().get(appName);
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
public static void optimize ()
{
   HibernateDaoLocalSupport support = ApplicationContextProvider.getBean (
         HibernateDaoLocalSupport.class);
   support.getHibernateTemplate ().flush ();
   support.getHibernateTemplate ().executeWithNativeSession (
      new HibernateCallback<Void> ()
      {
         @Override
         public Void doInHibernate (Session session) throws
               HibernateException, SQLException
         {
            SQLQuery query = session.createSQLQuery ("CHECKPOINT DEFRAG");
            query.executeUpdate ();
            return null;
         }
      });
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@SuppressWarnings ("rawtypes")
private int countOpenSessions ()
{
   return DataAccessUtils.intResult (getHibernateTemplate ().execute (
      new HibernateCallback<List>()
      {
         @Override
         public List doInHibernate(Session session) 
            throws HibernateException, SQLException
         {
            String sql = 
               "SELECT count (*) FROM INFORMATION_SCHEMA.SYSTEM_SESSIONS";
            SQLQuery query = session.createSQLQuery (sql);
            return query.list ();
         }
      }));
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@SuppressWarnings ({ "unchecked", "rawtypes" })
private String getSystemByName (final String name, final int index)
{
   return DataAccessUtils.uniqueResult (getHibernateTemplate ().execute (
      new HibernateCallback<List>()
      {
         @Override
         public List doInHibernate(Session session) 
            throws HibernateException, SQLException
         {
            String sql = 
               "SELECT " + name +
               " FROM INFORMATION_SCHEMA.SYSTEM_SESSIONS" +
               " LIMIT  1 OFFSET " + index;
            SQLQuery query = session.createSQLQuery (sql);
            return query.list ();
         }
      })).toString ();
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
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 ();
         }
      });
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@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);
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@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;
      }
   });
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@SuppressWarnings("nls")
@Override
public void updateLastRun(final HarvesterProfile profile, final Date date)
{
	getHibernateTemplate().execute(new HibernateCallback()
	{
		@Override
		public Object doInHibernate(Session session)
		{
			SQLQuery query = session.createSQLQuery("update harvester_profile set last_run = :date where id = :id");
			query.setDate("date", date);
			query.setLong("id", profile.getId());
			query.executeUpdate();
			return null;
		}
	});
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@SuppressWarnings("unchecked")
private Map<Long, Integer> getTotalChildCounts(Session session)
{
	// get total child counts in one hit
	// NOTE THAT THIS IS NOT HQL!!! IT IS PRETTY MUCH SQL!!!
	final StringBuilder sql = new StringBuilder(
		"SELECT all_parents_id, COUNT(*) AS CHILD_COUNT FROM taxonomy_node_all_parents GROUP BY all_parents_id");

	final Map<Long, Integer> totalChildCounts = new HashMap<Long, Integer>();
	final SQLQuery countQuery = session.createSQLQuery(sql.toString());
	final List<Object[]> countResults = countQuery.list();
	for( Object[] countResult : countResults )
	{
		totalChildCounts.put(((Number) countResult[0]).longValue(), ((Number) countResult[1]).intValue());
	}
	return totalChildCounts;
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@SuppressWarnings("unchecked")
public List<Map<String, Object>> querySqlObjects(String sql, Object params, Integer currentPage,Integer rowsInPage){
	Query qry = em.createNativeQuery(sql);
	SQLQuery s = qry.unwrap(SQLQuery.class);
	
	//设置参数
	if(params != null){
		if(params instanceof List){
			List<Object> paramList = (List<Object>) params;
			for(int i = 0, size = paramList.size(); i < size; i++){
				qry.setParameter(i+1, paramList.get(i));
			}
		}else if(params instanceof Map){
			Map<String, Object> paramMap = (Map<String, Object>) params;
			for(String key : paramMap.keySet()){
				qry.setParameter(key, paramMap.get(key));
			}
		}
	}
	
	if (currentPage != null && rowsInPage != null) {//判断是否有分页
		// 起始对象位置
		qry.setFirstResult(rowsInPage * (currentPage - 1));
		// 查询对象个数
		qry.setMaxResults(rowsInPage);
	}
	s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
	List<Map<String, Object>> resultList=new ArrayList<Map<String, Object>>();
	try {
		resultList=s.list();
	} catch (Exception e) {
	}finally{
		em.close();
	}
	return resultList;
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
private Query setParameter(SQLQuery query, Object... values) {
    if (Objects.isNull(values) || values.length == 0) {
        return query;
    }
    int i = 0;
    for (Object value : values) {
        query.setParameter(i, value);
        i++;
    }
    return query;
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
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();
             
        }
         
    });
     
}
 

import org.hibernate.SQLQuery; //导入依赖的package包/类
@Override
public <X> List<X> findByNative(String sql, List<?> params, Pageable pageable, Class<X> clazz) {
    Assert.hasText(sql, "native sql can not been null or blank");
    Query query = entityManager.createNativeQuery(sql);
    if (query != null) {
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(clazz));
        query.setFirstResult(pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        if (CollectionUtils.isNotEmpty(params)) {
            for (int i = 0; i < params.size(); i++) {
                query.setParameter(i + 1, params.get(i));
            }
        }
        return query.getResultList();
    }
    return Lists.newArrayList();
}