Java 类org.hibernate.SQLQuery 实例源码

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

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

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

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

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

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

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

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

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

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

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

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

        }

    });

}

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

/**
 * 返回指定对象数据集合。
 */
@SuppressWarnings({"unchecked"})
public Pagination pageBySql(final CharSequence queryString,
                            final Object[] values, int pageIndex, int pageSize) {
    SQLQuery sqlQuery = getSession().createSQLQuery(queryString.toString());
    if ((pageSize > 0) && (pageIndex > 0)) {
        sqlQuery.setFirstResult((pageIndex - 1) * pageSize);
        sqlQuery.setMaxResults(pageIndex * pageSize);
    }
    setParameter(sqlQuery, values);
    List items = sqlQuery.list();
    BigInteger rowsCount = (BigInteger) getSingleColumnBySql(getCountStr(queryString.toString()), values);

    Pagination pagination = new Pagination((long) pageIndex, (long) pageSize, rowsCount.longValue());
    pagination.setItems(items);
    return pagination;
}

public void updateBySql(String sql, Object[] vals) {
    Session session = hibernateTemplate.getSessionFactory().openSession();

    org.hibernate.Transaction tx = session.beginTransaction();

    SQLQuery query = session.createSQLQuery(sql);
    if(vals != null) {
        for(int i=0;i<vals.length;i++) {
            if(vals[i] instanceof Integer) {
                query.setInteger(i, (Integer)vals[i]);
            } else {
                query.setString(i, vals[i].toString());
            }
        }
    }
    query.executeUpdate();

    try {
        tx.commit();
    } catch (Exception e) {
        e.printStackTrace();
        tx.rollback();
    }
    session.close(); 
}

@Override
public List<demo> getList() {
    //create the session
    Session ses=null;
    //get the session
    ses=HibernateUtil.getSession();
        //create the list of class object
        List<demo> list=null;
            //create the SQLquery
    SQLQuery query=ses.createSQLQuery("select * from product");
    query.addEntity(demo.class);
        //execute the query
        list=query.list();

            //close the session
        HibernateUtil.closeSession(ses);
        //return the list
        return list;
}

@Override
public List<Object[]> getList() {
    //create the session
    Session ses=null;
    //get the session
    ses=HibernateUtil.getSession();
        //create the list of class object
        List<Object[]> list=null;
        //create the named parameterised the query
        SQLQuery query=ses.createSQLQuery("select PRODID,PRODNAME from PRODUCT where price>:price");
        //pass the parameter to query
        query.setString("price", "500");
    //add scalar to query
    query.addScalar("PRODID", StandardBasicTypes.INTEGER);
    query.addScalar("PRODNAME", StandardBasicTypes.STRING);
        //execute the query
        list=query.list();
            //close the session
        HibernateUtil.closeSession(ses);
        //return the list
        return list;
}

@SuppressWarnings({ "unchecked" })
   private SortedSet<Comment> getNextThreadByThreadIdNewestFirst(final Long rootTopicId,
    final Long previousThreadMessageId, Integer numberOfThreads, Integer sortBy, Integer userId) {

// the search to get to the top level is quite light, so get just the uids
// then build a complete set.
List<Number> threadUidList = null;
if (previousThreadMessageId == null || previousThreadMessageId == 0L) {
    threadUidList = getSession().createSQLQuery(SQL_QUERY_FIND_FIRST_THREAD_TOP_BY_UID)
        .setLong("rootUid", rootTopicId).setMaxResults(numberOfThreads).list();
} else {
    threadUidList = getSession().createSQLQuery(SQL_QUERY_FIND_NEXT_THREAD_TOP).setLong("rootUid", rootTopicId)
        .setLong("lastUid", previousThreadMessageId).setMaxResults(numberOfThreads).list();
}

if (threadUidList != null && threadUidList.size() > 0) {
    SQLQuery query = getSession().createSQLQuery(SQL_QUERY_FIND_NEXT_THREAD_MESSAGES);
    query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
        .addScalar("user_vote", IntegerType.INSTANCE).setLong("userId", userId != null ? userId : 0)
        .setParameterList("threadIds", threadUidList);
    List<Object[]> results = query.list();
    return upgradeComments(results, sortBy);
}
return new TreeSet<Comment>();
   }

@SuppressWarnings({ "unchecked" })
   private SortedSet<Comment> getStickyByThreadIdNewestFirst(final Long rootTopicId, Integer sortBy, Integer userId) {

// the search to get to the top level is quite light, so get just the uids
// then build a complete set.
List<Number> threadUidList = getSession().createSQLQuery(SQL_QUERY_FIND_STICKY_BY_UID)
    .setLong("rootUid", rootTopicId).list();

if (threadUidList != null && threadUidList.size() > 0) {
    SQLQuery query = getSession().createSQLQuery(SQL_QUERY_FIND_NEXT_THREAD_MESSAGES);
    query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
        .addScalar("user_vote", IntegerType.INSTANCE).setLong("userId", userId != null ? userId : 0)
        .setParameterList("threadIds", threadUidList);
    List<Object[]> results = query.list();
    return upgradeComments(results, sortBy);
}
return new TreeSet<Comment>();
   }

@SuppressWarnings({ "unchecked" })
   private SortedSet<Comment> getStickyByThreadIdLikes(final Long rootTopicId, Integer sortBy, String extraSortParam,
    Integer userId) {

List<Object[]> topThreadObjects = getSession().createSQLQuery(SQL_QUERY_FIND_STICKY_BY_LIKES)
    .addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
    .addScalar("user_vote", IntegerType.INSTANCE).setLong("rootUid", rootTopicId)
    .setLong("userId", userId != null ? userId : 0).list();

if (topThreadObjects != null && topThreadObjects.size() > 0) {
    // build the list of uids
    List<Number> threadUidList = new ArrayList<Number>();
    for (Object[] rawObject : topThreadObjects) {
    Comment comment = (Comment) rawObject[0];
    threadUidList.add(comment.getUid());
    }
    SQLQuery query = getSession().createSQLQuery(SQL_QUERY_FIND_NEXT_THREAD_MESSAGES_REPLIES_ONLY);
    query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
        .addScalar("user_vote", IntegerType.INSTANCE).setLong("userId", userId != null ? userId : 0)
        .setParameterList("threadIds", threadUidList);
    List<Object[]> results = query.list();
    topThreadObjects.addAll(results);
    return upgradeComments(topThreadObjects, sortBy);
}
return new TreeSet<Comment>();
   }

/**
 * 自定义 native query 查询 {@link ScheduleVO} ，略繁琐但是好像没有更好的办法.
 */
@Transactional
@Override
public List<ScheduleVO> findByOpenIdAndDate(String openId, Date date) {
    String sql = "select  t.schedule_id as scheduleId  ,t.date,t.meeting_room_id  as meetingRoomId,t.title,t.open_id as openId,m.room_no as roomNo,t.start_time as startTime,t.end_time as endTime, t.repeat_mode as repeatMode   from (select  p.schedule_id,p.date,s.meeting_room_id,s.title,p.open_id,s.start_time,s.end_time,s.repeat_mode from participant p left join  schedule  s on    p.schedule_id = s.id  ) as t left join meeting_room m on t.meeting_room_id  = m.id  where (t.open_id=? and t.date=?) or (t.open_id=? and repeat_mode='W')";
    Session session = entityManager.unwrap(org.hibernate.Session.class);
    SQLQuery query = session.createSQLQuery(sql);
    @SuppressWarnings("unchecked")
    List<ScheduleVO> scheduleVOs = query.setResultTransformer(Transformers.aliasToBean(ScheduleVO.class))
            .setParameter(0, openId).setParameter(1, date).setParameter(2, openId).list();
    return scheduleVOs.stream().filter(s -> s.isNeedInclude(date)).map(s -> {
        s.setDate(date);
        return s;
    }).sorted().collect(Collectors.toList());
}

@Transactional
@Override
public List<ScheduleVO> findByDate(Date date) {
    String sql = "select  t.schedule_id as scheduleId  ,t.date,t.meeting_room_id  as meetingRoomId,t.title,t.open_id as openId,m.room_no as roomNo,t.start_time as startTime,t.end_time as endTime, t.repeat_mode as repeatMode   from (select  p.schedule_id,p.date,s.meeting_room_id,s.title, s.creator_open_id open_id,s.start_time,s.end_time,s.repeat_mode from participant p left join  schedule  s on    p.schedule_id = s.id  ) as t left join meeting_room m on t.meeting_room_id  = m.id  where (t.date=?) or (repeat_mode='W')";
    Session session = entityManager.unwrap(org.hibernate.Session.class);
    SQLQuery query = session.createSQLQuery(sql);
    @SuppressWarnings("unchecked")
    List<ScheduleVO> scheduleVOs = query.setResultTransformer(Transformers.aliasToBean(ScheduleVO.class))
            .setParameter(0, date).list();
    return scheduleVOs.stream().filter(s -> s.isNeedInclude(date)).map(s -> {
        s.setDate(date);
        return s;
    }).sorted().collect(Collectors.toList());

}

/**
 * 更新orderbyId
 * 属性 belongId所有题目，只要大于等于orderById+1
 * @param belongId
 * @param orderById
 */
private void quOrderByIdAdd1(String belongId,Integer orderById){
    if(belongId!=null && !"".equals(belongId)){
        String sql="update t_question set order_by_id=order_by_id+1 where belong_id=? and order_by_id>=?";
        //更新排序号
        SQLQuery query=this.getSession().createSQLQuery(sql);
        query.setString(0, belongId);
        query.setInteger(1, orderById);
        query.executeUpdate();
    }
}

/**
 * 执行原生SQL查询
 * @param sql
 * @param params sql参数
 * @return 结果集并影射成Map
 */
public List<Map<String,Object>> queryByNativeSQL(String sql,Object... params){
    Query query=em.createNativeQuery(sql);
    if(params!=null&&params.length>0){
        for(int i=0;i< params.length;i++){
            query.setParameter(i,params[i]);
        }
    }
    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    return query.getResultList();
}

@SuppressWarnings("unchecked")
public List findBySql(String sql, Object[] params,
        PageBean pageBean, String order) {
    sql += (StringUtils.isEmpty(order)) ? "" : " order by " + order;
    System.out.println("sql->" + sql);
    SQLQuery query = hibernateTemplate.getSessionFactory()
            .getCurrentSession().createSQLQuery(sql);
    setPage(query, pageBean);
    setQuery(params, query);
    return query.list();
}

@SuppressWarnings("unchecked")
public List<Object[]> findObjectsBySql(String sql, Map<String, Object> map) {
    System.out.println("sql->" + sql);
    SQLQuery query = this.hibernateTemplate.getSessionFactory()
            .getCurrentSession().createSQLQuery(sql);
    setQuery(map, query);
    return query.list();
}

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

public List<OrderEntity> findByUserMatch(UserEntity user) {
    List<OrderEntity> res = new ArrayList<>();
    Session session = sessionFactory.getCurrentSession();
    int state = OrderEntity.OrderState.WAIT_ACCEPT.ordinal();
    SQLQuery query = session.createSQLQuery("select *  from orders where orders_state='" + state + "' and recipient_ID in(SELECT user_ID FROM users WHERE user_sex=(select user_sex from users where user_ID='" + user.getUid() + "'))");
    res.addAll(query.addEntity(OrderEntity.class).list());

    System.out.println(res.size());
    return res;
}

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 void quOrderByIdDel1(String belongId,Integer orderById){
    if(belongId!=null && !"".equals(belongId)){
        String sql="update t_question set order_by_id=order_by_id-1 where belong_id=? and order_by_id>=?";
        //更新排序号
        SQLQuery query=this.getSession().createSQLQuery(sql);
        query.setString(0, belongId);
        query.setInteger(1, orderById);
        query.executeUpdate();
    }
}

/**
 * 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;
}

public String newId() {
    Session session = sessionFactory.getCurrentSession();
    Calendar calendar = Calendar.getInstance();
    String year = calendar.get(Calendar.YEAR) + "";
    int month = calendar.get(Calendar.MONTH) + 1;
    int day = calendar.get(Calendar.DAY_OF_MONTH);

    String prefix = year + (month < 10 ? "0" + month : month) + (day < 10 ? "0" + day : day);


    StringBuilder builder = new StringBuilder();
    String sql = builder.append("SELECT MAX(review_ID) FROM review WHERE review_ID LIKE '")
            .append(prefix)
            .append("%' ")
            .toString();
    SQLQuery l = session.createSQLQuery(sql);
    List list = l.list();
    String id = (String) list.get(0);
    if (id == null || "null".equals(id)) {
        //当天没有，生成新的订单号
        id = prefix + "00000";
        return id;
    } else {
        long idd = Long.valueOf(id);
        String newid = String.valueOf(idd + 1);
        System.out.println(newid);
        return newid;
    }
}

@Override
@Transactional(propagation = Propagation.MANDATORY)
public void deleteAllForItem(final Item item)
{
    final long itemId = item.getId();
    final HibernateTemplate hibernateTemplate = getHibernateTemplate();

    hibernateTemplate.execute(new HibernateCallback()
    {
        @Override
        public Object doInHibernate(Session session)
        {
            SQLQuery sqlQuery = session.createSQLQuery(sqlQuery(
                "select count(h.item_id) as hcount, count(p.item_id) as pcount from item i left outer join %h h on h.item_id = i.id "
                    + "left outer join %p p on p.item_id = i.id where i.id = ? and (h.id is not null or p.id is not null)"));
            sqlQuery.setLong(0, itemId);
            Object[] hasSome = (Object[]) sqlQuery.uniqueResult();
            int numHoldings = ((Number) hasSome[0]).intValue();
            int numPortions = ((Number) hasSome[1]).intValue();
            if( numPortions > 0 )
            {
                deletePortionExtras(session, itemId);
                hibernateTemplate.bulkUpdate(query("delete from %s where portion in (from %p where item.id = ?)"),
                    itemId);
                hibernateTemplate.bulkUpdate(query("delete from %p where item.id = ?"), itemId);
            }
            if( numHoldings > 0 )
            {
                deleteHoldingExtras(session, itemId);
                hibernateTemplate.bulkUpdate(
                    query("update %p set holding = null where holding in (from %h where item.id = ?)"), itemId);
                hibernateTemplate.bulkUpdate(query("delete from %h where item.id = ?"), itemId);
            }
            return null;
        }
    });
}

@Override
@SuppressWarnings("unchecked")
@Transactional(propagation = Propagation.MANDATORY)
public List<Triple<Long, String, Boolean>> getMatchingExpressions(final List<String> values)
{
    return getHibernateTemplate().executeFind(new HibernateCallback()
    {
        @Override
        public Object doInHibernate(Session session)
        {
            // NOTE THAT THIS IS NOT HQL!!! IT IS PRETTY MUCH SQL!!!
            String sql = "SELECT id, expression, dynamic FROM access_expression WHERE id IN"
                + " (SELECT access_expression_id FROM access_expression_expression_p"
                + " WHERE element IN (:values))";

            SQLQuery query = session.createSQLQuery(sql);
            query.setParameterList("values", values);
            query.addScalar("id", StandardBasicTypes.LONG);
            query.addScalar("expression", StandardBasicTypes.STRING);
            query.addScalar("dynamic", StandardBasicTypes.BOOLEAN);
            query.setFetchSize(20);

            List<Pair<Long, String>> results = new ArrayList<Pair<Long, String>>();

            List<Object[]> queryResults = query.list();
            for( Object[] o : queryResults )
            {
                results.add(new Triple<Long, String, Boolean>((Long) o[0], (String) o[1], (Boolean) o[2]));
            }
            return results;
        }
    });
}

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

@Override
public Object queryForObject(String querySql, Object... params) {
    Query query = entityManager.createNativeQuery(querySql);
    SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
    if (params != null) {
        for (int i = 0; i < params.length; i++) {
            nativeQuery.setParameter(i, params[i]);
        }
    }
    return nativeQuery.uniqueResult();
}

@Override
   @SuppressWarnings("unchecked")
   /** Returns < [surveySession, numUsers] ... [surveySession, numUsers]> */
   public List<Object[]> getStatisticsBySession(final Long contentId) {

SQLQuery query = getSession().createSQLQuery(GET_STATISTICS);
query.addEntity(SurveySession.class).addScalar("numUsers", IntegerType.INSTANCE).setLong("contentId",
    contentId);
return query.list();
   }

@SuppressWarnings("unchecked")
   @Override
   public List<Number> getRawUserMarksBySession(Long sessionId) {

SQLQuery query = getSession().createSQLQuery(LOAD_MARKS_FOR_SESSION);
query.setLong("sessionId", sessionId);
List<Number> list = query.list();
return list;
   }

@SuppressWarnings("unchecked")
   @Override
   public List<Number> getRawLeaderMarksByToolContentId(Long toolContentId) {

SQLQuery query = getSession().createSQLQuery(LOAD_MARKS_FOR_LEADERS);
query.setLong("toolContentId", toolContentId);
List<Number> list = query.list();
return list;
   }