下面列出了org.hibernate.Session#createSQLQuery ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 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);
}
}
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?
}
@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;
}
@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;
}
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;
}
/**
* 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();
}
/**
* 自定义 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());
}
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;
}
@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;
}
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);
}
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)");
}
/**
* 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();
}
@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());
}
/**
* 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());
}
}
protected Query createSQLQuery(Session session, String sql) {
Query query = session.createSQLQuery(sql);
query.setCacheable(false);
return query;
}
@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;
}
}
/**
* 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;
}
/**
* 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();
}