Java Code Examples for org.hibernate.SQLQuery

The following examples show how to use org.hibernate.SQLQuery. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
@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();
}
 
Example 2
Source Project: ZTuoExchange_framework   Source File: TopBaseService.java    License: MIT License 6 votes vote down vote up
/**
 * 原生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);
}
 
Example 3
Source Project: ZTuoExchange_framework   Source File: TopBaseService.java    License: MIT License 6 votes vote down vote up
/**
 * 原生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);
}
 
Example 4
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;
        }
    }
}
 
Example 5
Source Project: SpringCloud   Source File: BaseDaoImpl.java    License: Apache License 2.0 6 votes vote down vote up
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();
}
 
Example 6
Source Project: lams   Source File: PolicyDAO.java    License: GNU General Public License v2.0 6 votes vote down vote up
@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;
   }
 
Example 7
Source Project: LibrarySystem   Source File: BackDaoImpl.java    License: Apache License 2.0 6 votes vote down vote up
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();
             
        }
         
    });
     
}
 
Example 8
@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;
}
 
Example 9
@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;
        }
    });
}
 
Example 10
Source Project: framework   Source File: BaseHibernateDao.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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);
    }
}
 
Example 11
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 ();
         }
      });
}
 
Example 12
@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);
}
 
Example 13
@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;
      }
   });
}
 
Example 14
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 ();
         }
      });
}
 
Example 15
Source Project: QiQuYingServer   Source File: BaseDAO.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * @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);
}
 
Example 16
Source Project: ZTuoExchange_framework   Source File: OrderService.java    License: MIT License 5 votes vote down vote up
public Map getOrderBySn(Long memberId, String orderSn) {
    String sql = "select o.*,m.real_name from otc_order o  join member m on o.customer_id=m.id and o.member_id=:memberId and o.order_sn =:orderSn ";
    Query query = em.createNativeQuery(sql);
    //设置结果转成Map类型
    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    Object object = query.setParameter("memberId", memberId).setParameter("orderSn", orderSn).getSingleResult();
    Map map = (HashMap) object;
    return map;
}
 
Example 17
Source Project: ZTuoExchange_framework   Source File: AdminService.java    License: MIT License 5 votes vote down vote up
public Map findAdminDetail(Long id) {
    String sql = "select a.id,a.role_id roleId,a.department_id departmentId,a.real_name realName,a.avatar,a.email,a.enable,a.mobile_phone mobilePhone,a.qq,a.username, " +
            "d.name as 'departmentName',r.role from admin a LEFT join department d on a.department_id=d.id LEFT JOIN admin_role r on a.role_id=r.id WHERE a.id=:adminId ";
    Query query = em.createNativeQuery(sql);
    //设置结果转成Map类型
    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    Object object = query.setParameter("adminId", id).getSingleResult();
    Map map = (HashMap) object;
    return map;
}
 
Example 18
Source Project: ZTuoExchange_framework   Source File: OrderService.java    License: MIT License 5 votes vote down vote up
public Map getOrderBySn(Long memberId, String orderSn) {
    String sql = "select o.*,m.real_name from otc_order o  join member m on o.customer_id=m.id and o.member_id=:memberId and o.order_sn =:orderSn ";
    Query query = em.createNativeQuery(sql);
    //设置结果转成Map类型
    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    Object object = query.setParameter("memberId", memberId).setParameter("orderSn", orderSn).getSingleResult();
    Map map = (HashMap) object;
    return map;
}
 
Example 19
Source Project: ZTuoExchange_framework   Source File: AdminService.java    License: MIT License 5 votes vote down vote up
public Map findAdminDetail(Long id) {
    String sql = "select a.id,a.role_id roleId,a.department_id departmentId,a.real_name realName,a.avatar,a.email,a.enable,a.mobile_phone mobilePhone,a.qq,a.username, " +
            "d.name as 'departmentName',r.role from admin a LEFT join department d on a.department_id=d.id LEFT JOIN admin_role r on a.role_id=r.id WHERE a.id=:adminId ";
    Query query = em.createNativeQuery(sql);
    //设置结果转成Map类型
    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    Object object = query.setParameter("adminId", id).getSingleResult();
    Map map = (HashMap) object;
    return map;
}
 
Example 20
Source Project: oim-fx   Source File: BaseAbstractDAO.java    License: MIT License 5 votes vote down vote up
protected void setScalar(Query query, List<ResultType> returnTypeList) {
	if (returnTypeList != null && returnTypeList.size() > 0) {
		if (query instanceof SQLQuery) {
			SQLQuery tmpQuery = (SQLQuery) query;
			for (ResultType returnType : returnTypeList) {
				if (returnType.getReturnType() != null) {
					tmpQuery.addScalar(returnType.getColumnName(), returnType.getReturnType());
				} else {
					tmpQuery.addScalar(returnType.getColumnName());
				}
			}
		}
	}
}
 
Example 21
Source Project: QiQuYingServer   Source File: BaseDAO.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * @Title: findMapsBySqlQuery
 * @Description: 返回map
 * @param @param hql
 * @param @param page
 * @param @return
 * @return List<T>
 */
public <T> List<T> findMapsBySqlQuery(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);
			query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			return query.list();
		}
	});
}
 
Example 22
Source Project: sailfish-core   Source File: NativeDbOperations.java    License: Apache License 2.0 5 votes vote down vote up
public void createTagGroupTempTable(Session session, Collection<Long> tagIds) {
    String query = DROP_TAG_GROUP_TEMP_TABLE;
    SQLQuery sqlQuery = session.createSQLQuery(query);
    sqlQuery.executeUpdate();

    query = CREATE_TAG_GROUP_TEMP_TABLE.replace(":tagIds",
            tagIds.stream()
                    .map(String::valueOf)
                    .collect(Collectors.joining(",")));
    sqlQuery = session.createSQLQuery(query);
    sqlQuery.executeUpdate();
}
 
Example 23
Source Project: sailfish-core   Source File: NativeDbOperations.java    License: Apache License 2.0 5 votes vote down vote up
public JoinType recognizeJoinType(Session session) {
    SQLQuery sqlQuery = session.createSQLQuery(NativeQueryUtil.CHECK_TESTCASE_TAGS_QUERY);
    List list = sqlQuery.list();
    if (list == null || list.isEmpty()) {
        return JoinType.MATRIX_RUN_TAGS;
    }
    sqlQuery = session.createSQLQuery(NativeQueryUtil.CHECK_MATRIX_TAGS_QUERY);
    list = sqlQuery.list();
    if (list == null || list.isEmpty()) {
        return  JoinType.TEST_CASE_RUN_TAGS;
    }
    return JoinType.TEST_CASE_AND_MATRIX_RUN_TAGS;
}
 
Example 24
@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));
}
 
Example 25
Source Project: QiQuYingServer   Source File: BaseDAO.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 自定义的class
 * @author Jon Chiang
 * @create_date 2014-5-9 下午2:41:58
 * @param hql
 * @param pageSize
 * @param startIndex
 * @return
 */
public <T> List<T> findMyObjectBySQL(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.setResultTransformer(Transformers.aliasToBean(clazz));
			buildParameters(query, params);
			return query.list();
		}
	});
}
 
Example 26
@SuppressWarnings("unchecked")
@Override
public <T> List<T> nativeQueryListModel(Class<T> resultClass,
		String nativeSql, Object... params) {
	Query q = createNativeQuery(nativeSql, params);;
	q.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(resultClass));
	return q.getResultList();
}
 
Example 27
@SuppressWarnings("unchecked")
@Override
public <T> List<T> nativeQueryListMap(String nativeSql, Object... params) {
	Query q = createNativeQuery(nativeSql, params);
	q.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	return q.getResultList();
}
 
Example 28
Source Project: agile-wroking-backend   Source File: ScheduleRepositoryImpl.java    License: MIT License 5 votes vote down vote up
/**
 * 自定义 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());
}
 
Example 29
Source Project: agile-wroking-backend   Source File: ScheduleRepositoryImpl.java    License: MIT License 5 votes vote down vote up
@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());

}
 
Example 30
@Override
public Long countTotalTweets() {
    return (Long) getHibernateTemplate().execute(new HibernateCallback<Object>() {
        @Override
        public Object doInHibernate(Session session) throws HibernateException {
            String sql = " select sum(c.count) from collection_log c ";
            SQLQuery sqlQuery = session.createSQLQuery(sql);
            BigDecimal total = (BigDecimal) sqlQuery.uniqueResult();
            return total != null ? total.longValue() : 0;
        }
    });
}