List of usage examples for org.hibernate Session createSQLQuery
@Override
NativeQuery createSQLQuery(String queryString);
From source file:com.consult.app.dao.impl.CargoMessageDaoImpl.java
/** * ?????/*from w ww .j a v a2s . co m*/ * @param user_id * @param start * @param end * @return bool */ public boolean judgeTodayCargo(Long user_id, int start, int end) { Session session = null; String sql = "select message.message_id from cargo_messages message where (message.type=1 or message.type=-1) and message.user_id=? and message.start=? and message.end =? and message.update_time>?"; long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()); try { CargoMessageInterceptor inter = new CargoMessageInterceptor(startOfDay + 1); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sql).addScalar("message_id", StandardBasicTypes.LONG); query.setLong(0, user_id); query.setInteger(1, start); query.setInteger(2, end); query.setLong(3, startOfDay); @SuppressWarnings("rawtypes") List list = query.list(); if (list != null && list.size() > 1) { return true; } else { return false; } } catch (Exception e) { e.printStackTrace(); return false; } finally { if (session != null) { session.close(); session = null; } } }
From source file:com.consult.app.dao.impl.CargoMessageDaoImpl.java
@Override public List<Object> getTodaySuspiciousCargoer() { Session session = null; String sql = "select u.install_place as city, u.telephone as telephone, p.user_name as userName, count(m.message_id) as cnt from cargo_message_00 m, users u, profiles p where m.user_id=u.user_id and m.user_id=p.user_id and (m.type=1 or m.type=-1 or m.type=4) and m.update_time>? group by m.user_id having cnt>=20 order by cnt desc;"; long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()); try {// ww w. j a v a 2 s. c o m CargoMessageInterceptor inter = new CargoMessageInterceptor(startOfDay + 1); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sql).addScalar("city", StandardBasicTypes.INTEGER) .addScalar("telephone", StandardBasicTypes.LONG) .addScalar("userName", StandardBasicTypes.STRING).addScalar("cnt", StandardBasicTypes.INTEGER) .setResultTransformer(Transformers.aliasToBean(com.consult.app.response.cargo.CargoItem.class)); query.setLong(0, startOfDay); List list = query.list(); return list; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } }
From source file:com.consult.app.dao.impl.CargoMessageDaoImpl.java
@Override public int searchNearByCount(SearchCargoRequest req, City startCity, City endCity, long userId) { StringBuilder sb = new StringBuilder(Constant.NEARBY_COUNT_CARGO_SEARCH_MULTIPLE_TRUCKLENGTH); if (Double.valueOf(req.getTruckLength()) >= 0) { sb.append(" and find_in_set(").append(req.getTruckLength()).append(",message.truck_length_set)"); }//from ww w .j ava 2 s. co m if (req.getTruckType() >= 0) { sb.append(" and message.truck_type=").append(req.getTruckType()); } if (req.getWeightRange() > 0) { sb.append(" and message.cargo_weight_range=").append(req.getWeightRange()); } if (startCity.getNearBy() != null && !startCity.getNearBy().equals("")) { sb.append(" and (").append(StringUtil.convertMysqlSetByField("message.start", startCity.getNearBy())) .append(" or ") .append(StringUtil.convertMysqlSetByField("message.start_father", startCity.getNearBy())) .append(")"); } else { return 0; } // sb.append(" and (find_in_set(message.start, city.near_by) or find_in_set(message.start_father, city.near_by))") sb.append(" and (message.end=").append(endCity.getId()).append(" or message.end_father=") .append(endCity.getId()).append(" or message.end_grand=").append(endCity.getId()).append(" or 0=") .append(endCity.getId()).append(")"); sb.append(" and message.type>=? and message.type<4 limit 1"); Long triggerTime = req.getAfter(); if (triggerTime.equals(Long.MAX_VALUE)) { triggerTime = System.currentTimeMillis(); } CargoMessageInterceptor inter = new CargoMessageInterceptor(); // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sb.toString()); // query.setLong(0, req.getBefore()); long startTime = req.getBefore() > Cookie.getSearchStartTime() ? req.getBefore() : Cookie.getSearchStartTime(); query.setLong(0, startTime); query.setLong(1, triggerTime); query.setInteger(2, Constant.TYPE_NORMAL); // query.setFirstResult(0); // query.setMaxResults(count); int ret = ((BigInteger) query.uniqueResult()).intValue(); return ret; } catch (Exception e) { e.printStackTrace(); return 0; } finally { if (session != null) { session.close(); session = null; } } // } int count = ((BigInteger)query.uniqueResult()).intValue(); }
From source file:com.consult.app.dao.impl.CargoMessageDaoImpl.java
@Override public int getMessageDayCount(int type1, int type2, Long userId) { CargoMessageInterceptor inter = new CargoMessageInterceptor(); long triggerTime = System.currentTimeMillis(); int ret = 0;/* w w w . j av a2 s . c o m*/ String sql = "select count(distinct FROM_UNIXTIME(update_time/1000, '%Y%m%d')) from cargo_messages where (type=? or type=? or type=4) and user_id=?;"; // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sql); query.setInteger(0, type1); query.setInteger(1, type2); query.setLong(2, userId); ret += ((BigInteger) query.uniqueResult()).intValue(); if (inter.isFinishSearch(0)) { return ret; } triggerTime = inter.getTriggerTime(); } catch (Exception e) { e.printStackTrace(); return 0; } finally { if (session != null) { session.close(); session = null; } } // } return 0; }
From source file:com.core.controller.Kimera.java
public List callProcedure(String query, Map<String, Object> params) { List result = null;/*from www .j a va2 s . c o m*/ try { Session s = sf.openSession(); Transaction tx = s.beginTransaction(); Query consulta = s.createSQLQuery(query); if (params != null) { Iterator it = params.entrySet().iterator(); while (it.hasNext()) { Map.Entry pair = (Map.Entry) it.next(); consulta.setParameter((String) pair.getKey(), pair.getValue()); } } result = consulta.list(); tx.commit(); s.close(); } catch (Exception e) { e.printStackTrace(); } return result; }
From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteListResultTest.java
License:Apache License
private void checkQueryResult(SimpleEntity entity, List expected) { Session session = sessionFactory.openSession(); SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE phone = :phone"); query.addScalar("id", LongType.INSTANCE); query.setCacheable(true);//from w w w. ja v a 2s . com query.setCacheRegion(cacheRegion); query.setParameter("phone", entity.getPhone()); List res = query.list(); Assert.assertEquals(expected, res); session.close(); }
From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteListResultTest.java
License:Apache License
private List listQueryResult() { Session session = sessionFactory.openSession(); SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE address = :address"); query.addScalar("id", LongType.INSTANCE); query.setCacheable(true);/* ww w . ja v a 2s.c o m*/ query.setCacheRegion(multiCacheRegion); query.setParameter("address", addressValue); List res = query.list(); session.close(); return res; }
From source file:com.corundumstudio.core.extensions.hibernate.InsertDeleteUniqueResultTest.java
License:Apache License
private void checkQueryResult(SimpleEntity entity, Long expected) { Session session = sessionFactory.openSession(); SQLQuery query = session.createSQLQuery("SELECT id FROM SimpleEntity WHERE phone = :phone"); query.addScalar("id", LongType.INSTANCE); query.setCacheable(true);// ww w . j av a 2 s. co m query.setCacheRegion(cacheRegion); query.setParameter("phone", entity.getPhone()); Long res = (Long) query.uniqueResult(); Assert.assertEquals(expected, res); session.close(); }
From source file:com.cosylab.cdb.jdal.HibernateWDALImpl.java
License:Open Source License
private synchronized void reloadData() { // full XML reload works only with in-memory DB // for external DBs, existing data should have been removed from the DB... if (forceInMemory) { // in-memory HSQLDB only, shutdown DB m_logger.info("Will shutdown the in-memory HSQLDB..."); try {//from ww w . j ava 2s . c o m try { hibernateUtil.beginTransaction(); Session session = hibernateUtil.getSession(); session.createSQLQuery("SHUTDOWN IMMEDIATELY").executeUpdate(); } finally { hibernateUtil.closeSession(); } } catch (Throwable th) { m_logger.log(Level.WARNING, "Failed to shutdown in-memory HSQLDB.", th); } if (hibernateUtil.getSessionFactory() != null) hibernateUtil.getSessionFactory().close(); m_logger.info("Will re-create the in-memory HSQLDB..."); hibernateDBUtil.setUp(forceInMemory, createTables); if (loadXMLCDB) { m_logger.info("Will reload XML CDB data..."); loadXMLCDB(args, orb, poa, configName); } } else { try { // clear Hibernate session (cache) hibernateUtil.getSession().clear(); } catch (Throwable th) { m_logger.log(Level.WARNING, "Failed to clear the hibernate session cache.", th); } } load(); m_logger.info("Successfully reloaded all data."); }
From source file:com.court.controller.BranchFxmlController.java
private void updateMbrPayOfficeOfWorkOffice(int branchId, int payOffId, Session s) { Query query = s .createSQLQuery("UPDATE member m SET m.pay_office_id= :pay_off WHERE m.branch_id= :work_off ;") .setParameter("pay_off", payOffId).setParameter("work_off", branchId); query.executeUpdate();//from w w w .j ava2 s. co m }