Java Code Examples for org.hibernate.SQLQuery

The following are top voted examples for showing how to use org.hibernate.SQLQuery. These examples are extracted from open source projects. You can vote up the examples you like and your votes will be used in our system to generate more good examples.
Example 1
Project: lams   File: VoteUsrAttemptDAO.java   View source code 8 votes vote down vote up
@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();
   }
 
Example 2
Project: Equella   File: AbstractCopyrightDao.java   View source code 7 votes vote down vote up
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();
}
 
Example 3
Project: esup-sgc   File: GenericCardIdService.java   View source code 7 votes vote down vote up
@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);
}
 
Example 4
Project: dhus-core   File: DaoUtils.java   View source code 6 votes vote down vote up
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;
         }
      });
}
 
Example 5
Project: dhus-core   File: HibernateDao.java   View source code 6 votes vote down vote up
@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 ();
         }
      }));
}
 
Example 6
Project: dhus-core   File: HibernateDao.java   View source code 6 votes vote down vote up
@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 ();
}
 
Example 7
Project: dhus-core   File: FileScannerDao.java   View source code 6 votes vote down vote up
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 8
Project: dhus-core   File: FileScannerDao.java   View source code 6 votes vote down vote up
@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 9
Project: dhus-core   File: AccessRestrictionDao.java   View source code 6 votes vote down vote up
@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 10
Project: Equella   File: HarvesterProfileDaoImpl.java   View source code 6 votes vote down vote up
@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;
		}
	});
}
 
Example 11
Project: Equella   File: MigrateOldTaxonomyToNew.java   View source code 6 votes vote down vote up
@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;
}
 
Example 12
Project: tianti   File: CustomBaseSqlDaoImpl.java   View source code 6 votes vote down vote up
@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;
}
 
Example 13
Project: admin-shiro   File: DaoSupport.java   View source code 6 votes vote down vote up
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;
}
 
Example 14
Project: LibrarySystem   File: BackDaoImpl.java   View source code 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 15
Project: spring-cloud-samples   File: BaseRepositoryImpl.java   View source code 6 votes vote down vote up
@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();
}
 
Example 16
Project: admin-shiro   File: DaoSupport.java   View source code 6 votes vote down vote up
/**
 * 返回指定对象数据集合。
 */
@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;
}
 
Example 17
Project: djpt   File: DaoImpl.java   View source code 6 votes vote down vote up
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(); 
}
 
Example 18
Project: Hibernate_Native_SQL_Using_DAO_Using_MAVEN   File: DAO_IMPL.java   View source code 6 votes vote down vote up
@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;
}
 
Example 19
Project: Hibernate_Native_SQL_Scalar_Using_DAO_Using_MAVEN   File: DAO_IMPL.java   View source code 6 votes vote down vote up
@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;
}
 
Example 20
Project: lams   File: CommentDAO.java   View source code 6 votes vote down vote up
@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>();
   }
 
Example 21
Project: lams   File: CommentDAO.java   View source code 6 votes vote down vote up
@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>();
   }
 
Example 22
Project: lams   File: CommentDAO.java   View source code 6 votes vote down vote up
@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>();
   }
 
Example 23
Project: agile-wroking-backend   File: ScheduleRepositoryImpl.java   View source code 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 24
Project: agile-wroking-backend   File: ScheduleRepositoryImpl.java   View source code 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 25
Project: DWSurvey   File: QuestionDaoImpl.java   View source code 5 votes vote down vote up
/**
 * 更新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();
	}
}
 
Example 26
Project: sucok-framework   File: BaseDao.java   View source code 5 votes vote down vote up
/**
 * 执行原生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();
}
 
Example 27
Project: WeiXing_xmu-2016-MrCode   File: BaseDaoImpl.java   View source code 5 votes vote down vote up
@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();
}
 
Example 28
Project: WeiXing_xmu-2016-MrCode   File: BaseDaoImpl.java   View source code 5 votes vote down vote up
@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();
}
 
Example 29
Project: WeiXing_xmu-2016-MrCode   File: BaseDaoImpl.java   View source code 5 votes vote down vote up
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());
}
 
Example 30
Project: school-express-delivery   File: OrderDao.java   View source code 5 votes vote down vote up
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;
}
 
Example 31
Project: WeiXing_xmu-2016-MrCode   File: BaseDaoImpl.java   View source code 5 votes vote down vote up
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();
}
 
Example 32
Project: DWSurvey   File: QuestionDaoImpl.java   View source code 5 votes vote down vote up
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();
	}
}
 
Example 33
Project: dhus-core   File: ProductDao.java   View source code 5 votes vote down vote up
/**
 * 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;
}
 
Example 34
Project: school-express-delivery   File: ReviewDao.java   View source code 5 votes vote down vote up
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;
    }
}
 
Example 35
Project: Equella   File: AbstractCopyrightDao.java   View source code 5 votes vote down vote up
@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;
		}
	});
}
 
Example 36
Project: Equella   File: AccessExpressionDaoImpl.java   View source code 5 votes vote down vote up
@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;
		}
	});
}
 
Example 37
Project: xq_seckill_microservice   File: BasicRepositoryImpl.java   View source code 5 votes vote down vote up
@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();
}
 
Example 38
Project: lams   File: SurveyUserDAOHibernate.java   View source code 5 votes vote down vote up
@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();
   }
 
Example 39
Project: lams   File: AssessmentUserDAOHibernate.java   View source code 5 votes vote down vote up
@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;
   }
 
Example 40
Project: lams   File: AssessmentUserDAOHibernate.java   View source code 5 votes vote down vote up
@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;
   }