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

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

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

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

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

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

}
 

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

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

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

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

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

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

/**
 * 自定义 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());
}
 

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

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

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

protected Query createSQLQuery(Session session, String sql) {
	Query query = session.createSQLQuery(sql);
	query.setCacheable(false);
	return query;
}
 

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

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

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