下面列出了怎么用org.hibernate.SQLQuery的API类实例代码及写法,或者点击链接到github查看源代码。
@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();
}
/**
* 原生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);
}
/**
* 原生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;
}
}
}
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();
}
@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;
}
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();
}
});
}
@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;
}
@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;
}
});
}
/**
* 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);
}
}
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 ();
}
});
}
@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);
}
@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;
}
});
}
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 ();
}
});
}
/**
* @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);
}
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;
}
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;
}
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;
}
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;
}
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());
}
}
}
}
}
/**
* @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();
}
});
}
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();
}
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));
}
/**
* 自定义的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();
}
});
}
@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();
}
@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());
}
@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;
}
});
}