Java Code Examples for org.hibernate.Session#createSQLQuery()

The following examples show how to use org.hibernate.Session#createSQLQuery() . 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 want to check out the right sidebar which shows the related API usage.
Example 1
Source Project: framework   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 2
Source Project: framework   File: BaseHibernateDao.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param sql
 * @param param
 * @return
 * @throws DaoException <br>
 */
@Override
public int excuteSql(final String sql, final DataParam param) throws DaoException {
    try {
        SQlCheckUtil.checkSql(sql);
        Session session = getSession();
        session.flush();

        SQLQuery query = session.createSQLQuery(sql);
        setParamMap(param.getParamMap(), query);
        return query.executeUpdate();
    }
    catch (Exception e) {
        logger.error(e.getMessage(), e);
        throw new DaoException(ErrorCodeDef.EXECUTE_ERROR_10011, e);
    }
}
 
Example 3
public void createDefaultTimingLocations() {
    
    blockingClearAll();
    
    Session s=HibernateUtil.getSessionFactory().getCurrentSession();
    s.beginTransaction();
    // sql to set the name and date
    Query query = s.createSQLQuery("INSERT into TIMING_LOCATION (TIMING_LOCATION_ID, TIMING_LOCATION_NAME) values (:id, :name)");
    query.setParameter("id", 1);
    query.setParameter("name", "Start");
    query.executeUpdate();
    query.setParameter("id", 2);
    query.setParameter("name", "Finish");
    query.executeUpdate();            
    s.getTransaction().commit();
    
    // Thread.dumpStack(); // who called this?
    refreshTimingLocationList();
}
 
Example 4
Source Project: pikatimer   File: EventDAO.java    License: GNU General Public License v3.0 6 votes vote down vote up
public void createEvent() {
    event.setEventName("New Event");
    event.setEventDate(LocalDate.now());
    
    Session s=HibernateUtil.getSessionFactory().getCurrentSession();
    s.beginTransaction();
    // sql to set the name and date
    Query query = s.createSQLQuery("INSERT into EVENT (ID, EVENT_NAME, EVENT_DATE) values (:id, :name, :date)");
    query.setParameter("id", 1);
    query.setParameter("name", event.getEventName());
    //query.setParameter("date", event.getEventDate());
    query.setParameter("date", event.getLocalEventDate().toString());
    query.executeUpdate();
    s.getTransaction().commit();
    
    // Thread.dumpStack(); // who called this?
    
}
 
Example 5
@SuppressWarnings("unchecked")
public int getDisctinctVotersForPoll(Poll poll) {

    Query q = null;

    Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
    String statement = "SELECT DISTINCT VOTE_SUBMISSION_ID from POLL_VOTE where VOTE_POLL_ID = " + poll.getPollId().toString();
    q = session.createSQLQuery(statement);
    List<String> results = q.list();
    if (results.size() > 0)
        return results.size();

    return 0; 
}
 
Example 6
Source Project: onlineSystem   File: ExamDaoImpl.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public int examCount(Exam s_exam) throws Exception {
    Session session=getSessionFactory().getCurrentSession();
    StringBuffer sql=new StringBuffer("select count(*) from t_exam t1 ,t_student t2 where t1.studentId=t2.id ");
    if(s_exam.getStudent()!=null&&StringUtil.isNotEmpty(s_exam.getStudent().getId())){
        sql.append(" and t2.id like '%"+s_exam.getStudent().getId()+"%'");
    }
    if(s_exam.getStudent()!=null&&StringUtil.isNotEmpty(s_exam.getStudent().getName())){
        sql.append(" and t2.name like '%"+s_exam.getStudent().getName()+"%'");
    }
    Query query=session.createSQLQuery(sql.toString());
    int count=((BigInteger)query.uniqueResult()).intValue();
    return count;
}
 
Example 7
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 8
Source Project: framework   File: BaseHibernateDao.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param sql
 * @param <T> T
 * @return <br>
 */
public <T> List<T> queryBySql(final String sql) {

    Session session = getSession();
    session.flush();
    Query query = session.createSQLQuery(sql);

    if (getEntityClazz().equals(Map.class)) {
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    }
    else {
        query.setResultTransformer(new AutoResultTransformer(getEntityClazz()));
    }
    return query.list();
}
 
Example 9
/**
 * 自定义 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 10
Source Project: JgFramework   File: BaseDao.java    License: Apache License 2.0 5 votes vote down vote up
protected SQLQuery getQuery(String queryString, Map<String, Object> where, int start, int limit, boolean returnMap) {
    Session session = this.getSession();
    SQLQuery query = null;
    if (start > -1 && limit > 0) {
        query = session.createSQLQuery(queryString);
        query.setFirstResult(start);
        query.setMaxResults(limit);
    } else {
        query = session.createSQLQuery(queryString);
    }
    if (where != null) {
        for (Entry<String, Object> entry : where.entrySet()) {
            Object value = entry.getValue();
            String key = entry.getKey();
            if (value instanceof Collection) {
                query.setParameterList(key, (Collection) value);
            } else if (value instanceof Object[]) {
                query.setParameterList(key, (Object[]) value);
            } else {
                query.setParameter(key, value);
            }
        }
    }
    if (returnMap) {
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    }
    return query;
}
 
Example 11
@Override
public List loadListMetaModelDomainsByRole(Integer roleId) throws SpagoBIRuntimeException {

	Session aSession = null;
	Transaction tx = null;

	List realResult = new ArrayList();
	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		SQLQuery query = aSession.createSQLQuery("select category_id from SBI_EXT_ROLES_CATEGORY where ext_role_id=" + roleId);
		List hibList = query.list();

		Iterator it = hibList.iterator();

		while (it.hasNext()) {
			// realResult.add(toDomain((SbiDomains) it.next()));
			Integer categoryId = Integer.getInteger(it.next().toString());
			realResult.add(categoryId);
		}
		tx.commit();
	} catch (HibernateException he) {
		logException(he);

		if (tx != null)
			tx.rollback();

		throw new SpagoBIRuntimeException(he.getMessage());

	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
	}

	return realResult;

}
 
Example 12
private void updateExperimentByExperimentRunTimestamp(Session session) {
  LOGGER.debug("Experiment timestamp updating");
  String experimentUpdateQueryString = getExperimentUpdateQueryString();
  Query query = session.createSQLQuery(experimentUpdateQueryString);
  LOGGER.debug("Experiment update timestamp query: {}", query.getQueryString());
  int count = query.executeUpdate();
  LOGGER.debug("Experiment timestamp updated successfully : Updated experiments count {}", count);
}
 
Example 13
private void deleteInconsistentData(Session session) {
    session.createSQLQuery("delete from TASK_DATA where JOB_ID = null");
    session.createSQLQuery("delete from TASK_RESULT_DATA where TASK_ID = null");
    session.createSQLQuery("delete from TASK_RESULT_DATA where JOB_ID = null");
    session.createSQLQuery("delete from TASK_DATA where JOB_ID not in (select ID from JOB_DATA)");
    session.createSQLQuery("delete from TASK_RESULT_DATA where TASK_ID not in (select TASK_ID_TASK from TASK_DATA)");
    session.createSQLQuery("delete from TASK_RESULT_DATA where JOB_ID not in (select ID from JOB_DATA)");
}
 
Example 14
Source Project: framework   File: BaseHibernateDao.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param procedureSql
 * @param params
 * @return
 * @throws DaoException <br>
 */

@Override
public <T> List<T> executeProcedure(final String procedureSql, final Object... params) throws DaoException {
    Session session = getSession();
    session.flush();
    SQLQuery sqlQuery = session.createSQLQuery(procedureSql);

    for (int i = 0; i < params.length; i++) {
        sqlQuery.setParameter(i, params[i]);
    }
    return sqlQuery.list();
}
 
Example 15
@Override
@Cacheable(value = "exchangeRate")
public String getExchangeRateByRegion(String region) {
    Session session = sessionFactory.openSession();
    // in real life, it should be current date time
    SQLQuery query = session.createSQLQuery("select * from exchangerate e where e.ratedate = TO_DATE('2015-05-02','YYYY-MM-DD') and e.region=:region");
    query.setParameter("region", region);
    query.addEntity(ExchangeRate.class);
    ExchangeRate res =  (ExchangeRate)query.uniqueResult();
    session.close();
    return String.valueOf(res.getUsdollar());
}
 
Example 16
/**
 * Resets the passed context's default channel
 *
 */
protected void resetPlacementDefaultChannel(String context, String placement) {
    Session session = null;

    try {
        session = getSessionFactory().getCurrentSession();
        Query query = session.createSQLQuery("update CHAT2_CHANNEL c set c.placementDefaultChannel = :channel, c.PLACEMENT_ID = NULL WHERE c.context = :context and c.PLACEMENT_ID = :placement");
        query.setBoolean("channel", false);
        query.setString("context", context);
        query.setString("placement", placement);
        query.executeUpdate();
    } catch(Exception e) {
        log.warn(e.getMessage());
    }
}
 
Example 17
Source Project: oim-fx   File: BaseAbstractDAO.java    License: MIT License 4 votes vote down vote up
protected Query createSQLQuery(Session session, String sql) {
	Query query = session.createSQLQuery(sql);
	query.setCacheable(false);
	return query;
}
 
Example 18
@SuppressWarnings("unchecked")
@Override
public List<TrainingDataDTO> getTraningDataByCrisisAndAttribute(Long crisisID, Long modelFamilyID, int fromRecord, int limit,
		String sortColumn, String sortDirection) {
	List<TrainingDataDTO> trainingDataList = new ArrayList<TrainingDataDTO>();

	String orderSQLPart = "";
	if (sortColumn != null && !sortColumn.isEmpty()){
		if (sortDirection != null && !sortDirection.isEmpty()) {
			if ("ASC".equals(sortDirection)) {
				sortDirection = "ASC";
			} else {
				sortDirection = "DESC";
			}
		} else {
			sortDirection = "DESC";
		}
		orderSQLPart += " ORDER BY " + sortColumn + " " + sortDirection + " ";
	}
	else{
		orderSQLPart += " ORDER BY dnl.timestamp DESC";
	}

	String sql = " SELECT distinct lbl.nominalLabelID, lbl.name labelName, d.data tweetJSON, u.id, u.user_name labelerName, dnl.timestamp, d.documentID "
			+ " FROM document_nominal_label dnl "
			+ " JOIN nominal_label lbl on lbl.nominalLabelID=dnl.nominalLabelID "
			+ " JOIN model_family mf on mf.nominalAttributeID=lbl.nominalAttributeID "
			+ " JOIN document d on d.documentID = dnl.documentID "
			+ " JOIN account u on u.id = dnl.userID "
			+ " WHERE mf.modelFamilyID = :modelFamilyID AND d.crisisID = :crisisID " + orderSQLPart
			+ " LIMIT :fromRecord, :limit";

	String sqlCount = " SELECT count(1) "
			+ " FROM document_nominal_label dnl "
			+ " JOIN nominal_label lbl on lbl.nominalLabelID=dnl.nominalLabelID "
			+ " JOIN model_family mf on mf.nominalAttributeID=lbl.nominalAttributeID "
			+ " JOIN document d on d.documentID = dnl.documentID "				
			+ " WHERE mf.modelFamilyID = :modelFamilyID AND d.crisisID = :crisisID";

	try {
		Integer totalRows = null;
		Session session = getCurrentSession();
		//Query queryCount = em.createNativeQuery(sqlCount);
		Query queryCount = session.createSQLQuery(sqlCount);
		//logger.info("getTraningDataByCrisisAndAttribute count query: " + sqlCount);
		queryCount.setParameter("modelFamilyID", modelFamilyID.intValue());
		queryCount.setParameter("crisisID", crisisID.intValue());
		
		Object res = queryCount.uniqueResult();
		if (res != null) { 
			totalRows = Integer.parseInt(res.toString());
		}
		logger.info("getTraningDataByCrisisAndAttribute: rows count = " + res);
		if (totalRows != null && totalRows > 0) {
			Query query = session.createSQLQuery(sql);
			query.setParameter("crisisID", crisisID.intValue());
			query.setParameter("modelFamilyID", modelFamilyID.intValue());
			query.setParameter("fromRecord", fromRecord);
			query.setParameter("limit", limit);
			
			List<Object[]> rows = query.list();
			//logger.info("[getTraningDataByCrisisAndAttribute] fetched rows count = " + (rows != null ? rows.size() : "null"));
			TrainingDataDTO trainingDataRow = null;
			//int count = 0;
			for (Object[] row : rows) {
				trainingDataRow = new TrainingDataDTO();
				//                    Removed .intValue() as we already cast to Integer
				trainingDataRow.setLabelID(((BigInteger) row[0]).intValue());
				trainingDataRow.setLabelName((String) row[1]);
				trainingDataRow.setTweetJSON((String) row[2]);
				trainingDataRow.setLabelerID(((BigInteger) row[3]).intValue());
				trainingDataRow.setLabelerName((String) row[4]);
				trainingDataRow.setLabeledTime(((Date) row[5]));
				trainingDataRow.setDocumentID(((BigInteger) row[6]).longValue());
				trainingDataRow.setTotalRows(totalRows);
				trainingDataList.add(trainingDataRow);
				//logger.info("Added to DTO training data, training data #" + count);
				//++count;
			}
		}
		logger.info("Fetched training data list size: " + (trainingDataList != null ? trainingDataList.size() : 0));
		return trainingDataList;
	} catch (Exception e) {
		logger.error("exception", e);
		return null;
	}
}
 
Example 19
/**
 * Query very specific to deleting stale tasks only
 */
@Override
public int deleteStaleDocuments(String joinType, String joinTable, String joinColumn,
		String sortOrder, String[] orderBy, 
		final String maxTaskAge, final String scanInterval) {

	logger.info("received request: " + joinType + ", " + joinTable + ", " 
			+ joinColumn + ", " + maxTaskAge + ", " + scanInterval);

	int deleteCount = 0;
	Session session = getCurrentSession();
	StringBuffer hql = new StringBuffer("DELETE d FROM aidr_predict.document d ");
	if (joinType.equalsIgnoreCase("LEFT JOIN") || joinType.equalsIgnoreCase("LEFT_JOIN")) {
		hql.append(" LEFT JOIN ").append(joinTable).append(" t ");
		hql.append(" ON d.").append(joinColumn).append(" = t.").append(joinColumn)
		.append(" WHERE ")
		.append("(!d.hasHumanLabels AND t.documentID IS NULL AND TIMESTAMPDIFF(")
		.append(getMetric(scanInterval))
		.append(", d.receivedAt, now()) > ");
	} else if (joinType.equalsIgnoreCase("JOIN")) {
		hql.append(" JOIN ").append(joinTable).append(" t ");
		hql.append(" ON d.").append(joinColumn).append(" = t.").append(joinColumn)
		.append(" WHERE ")
		.append("(!d.hasHumanLabels && TIMESTAMPDIFF(")
		.append(getMetric(scanInterval))
		.append(", t.assignedAt, now()) > ");
	}
	hql.append(" :task_expiry_age) ");

	if (orderBy != null) {
		hql.append(" ORDER BY ");
		for (int i = 0; i< orderBy.length - 1; i++) {
			hql.append(orderBy[i]).append(", ");
		}
		hql.append(orderBy[orderBy.length-1]).append(" ");
		if (sortOrder != null) {
			hql.append(sortOrder.toUpperCase()).append(" ; ");
		}
	}

	Query deleteQuery = session.createSQLQuery(hql.toString());
	deleteQuery.setParameter("task_expiry_age", Integer.parseInt(getTimeValue(maxTaskAge)));
	logger.info("Constructed query: " + deleteQuery.getQueryString());
	try {
		deleteCount = deleteQuery.executeUpdate();
		logger.info("[deleteStaleDocuments] number of deleted records = " + deleteCount);
	} catch (Exception e) {
		logger.error("Exception in executing SQL delete stale docs query");
	}
	return deleteCount;
}
 
Example 20
Source Project: framework   File: BaseHibernateDao.java    License: Apache License 2.0 3 votes vote down vote up
/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param sql
 * @return
 * @throws DaoException <br>
 */
@Override
public int updateBySqlString(final String sql) throws DaoException {
    Session session = getSession();
    session.flush();

    Query querys = session.createSQLQuery(sql);
    return querys.executeUpdate();
}