类org.hibernate.SQLQuery源码实例Demo

下面列出了怎么用org.hibernate.SQLQuery的API类实例代码及写法,或者点击链接到github查看源代码。

源代码1 项目: ignite-book-code-samples   文件: EmpDaoImpl.java
@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();
}
 
源代码2 项目: ZTuoExchange_framework   文件: TopBaseService.java
/**
 * 原生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);
}
 
源代码3 项目: ZTuoExchange_framework   文件: TopBaseService.java
/**
 * 原生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);
}
 
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;
        }
    }
}
 
源代码5 项目: SpringCloud   文件: BaseDaoImpl.java
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();
}
 
源代码6 项目: lams   文件: PolicyDAO.java
@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;
   }
 
源代码7 项目: LibrarySystem   文件: BackDaoImpl.java
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();
             
        }
         
    });
     
}
 
源代码8 项目: AIDR   文件: CollectionRepositoryImpl.java
@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;
}
 
源代码9 项目: AIDR   文件: CollectionLogRepositoryImpl.java
@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;
        }
    });
}
 
源代码10 项目: framework   文件: BaseHibernateDao.java
/**
 * 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);
    }
}
 
源代码11 项目: DataHubSystem   文件: FileScannerDao.java
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 ();
         }
      });
}
 
源代码12 项目: DataHubSystem   文件: FileScannerDao.java
@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);
}
 
源代码13 项目: DataHubSystem   文件: AccessRestrictionDao.java
@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;
      }
   });
}
 
源代码14 项目: DataHubSystem   文件: TestSearchDao.java
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 ();
         }
      });
}
 
源代码15 项目: QiQuYingServer   文件: BaseDAO.java
/**
 * @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);
}
 
源代码16 项目: ZTuoExchange_framework   文件: OrderService.java
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;
}
 
源代码17 项目: ZTuoExchange_framework   文件: AdminService.java
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;
}
 
源代码18 项目: ZTuoExchange_framework   文件: OrderService.java
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;
}
 
源代码19 项目: ZTuoExchange_framework   文件: AdminService.java
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;
}
 
源代码20 项目: oim-fx   文件: BaseAbstractDAO.java
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());
				}
			}
		}
	}
}
 
源代码21 项目: QiQuYingServer   文件: BaseDAO.java
/**
 * @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();
		}
	});
}
 
源代码22 项目: sailfish-core   文件: NativeDbOperations.java
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();
}
 
源代码23 项目: sailfish-core   文件: NativeDbOperations.java
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;
}
 
@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));
}
 
源代码25 项目: QiQuYingServer   文件: BaseDAO.java
/**
 * 自定义的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();
		}
	});
}
 
源代码26 项目: spring-boot-seckill   文件: DynamicQueryImpl.java
@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();
}
 
源代码27 项目: spring-boot-seckill   文件: DynamicQueryImpl.java
@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();
}
 
/**
 * 自定义 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());

}
 
源代码30 项目: AIDR   文件: CollectionLogRepositoryImpl.java
@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;
        }
    });
}
 
 类所在包
 同包方法