public List<Review> getReviewListbyProductId(int productId) { SessionFactory sf = HibernateUtility.getSessionfactory(); Session session = sf.openSession(); session.beginTransaction(); String sql = "SELECT * FROM review where product_productId = :pId"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(Review.class); query.setParameter("pId", productId); System.out.println(query.list().size()); List<Review> reviewlist = query.list(); session.getTransaction().commit(); return reviewlist; }
public List<Map<String, Object>> getWardwisePerformanceTab(final String zoneName) { final SQLQuery overAllQry = getQuery("revenue.ptis.wardwise.overall.performance"); overAllQry.setParameter("zoneName", zoneName); final List<Object[]> overAllData = overAllQry.list(); final Map<String, Map<String, Object>> revenueDataHolder = new HashMap<String, Map<String, Object>>(); for (final Object[] revenueObj : overAllData) { final Map<String, Object> revnData = new HashMap<String, Object>(); revnData.put("ward", String.valueOf(revenueObj[0])); final BigDecimal collectionPerc = (BigDecimal) revenueObj[2]; revnData.put("collectionPerc", collectionPerc != null ? collectionPerc.doubleValue() : "0"); revenueDataHolder.put(String.valueOf(revenueObj[0]), revnData); } final SQLQuery monthlyQry = getQuery("revenue.ptis.wardwise.monthly.performance"); monthlyQry.setParameter("zoneName", zoneName); final List<Object[]> monthlyData = monthlyQry.list(); for (final Object[] revenueObj : monthlyData) { final Map<String, Object> revnData = revenueDataHolder.get(String.valueOf(revenueObj[0])); final BigDecimal amtTargeted = (BigDecimal) revenueObj[1]; revnData.put("amtTargeted", amtTargeted != null ? amtTargeted.doubleValue() : "0"); final BigDecimal amt_collectd = (BigDecimal) revenueObj[2]; revnData.put("amt_collectd", amt_collectd != null ? amt_collectd.doubleValue() : "0"); final BigDecimal percCollections = (BigDecimal) revenueObj[3]; revnData.put( "percCollections", percCollections != null ? percCollections.setScale(2, RoundingMode.CEILING).doubleValue() : "0"); } final List<Map<String, Object>> revenueAggrData = new ArrayList<Map<String, Object>>(revenueDataHolder.values()); // SORT BY WARDWISE MONTHLY COLLECTION % sortData(revenueAggrData, "percCollections"); // ASSIGN MONTHLY RANK BASED ON SORT ORDER assignRank(revenueAggrData, "rank"); // SORT BY WARDWISE OVERALL COLLECTION % sortData(revenueAggrData, "collectionPerc"); // ASSIGN OVERALL RANK BASED ON SORT ORDER assignRank(revenueAggrData, "overallrank"); return revenueAggrData; }
public Date getDateOfWorkflowStateChange( Integer patientId, Integer conceptId, Date startDate, Date endDate) { Date sDate = new Date(); sDate.setTime(0); Date eDate = Calendar.getInstance().getTime(); if (startDate != null) { sDate = startDate; } if (endDate != null) { eDate = endDate; } SQLQuery dateOfWorkFlowStateQuery = sessionFactory .getCurrentSession() .createSQLQuery( "select start_date from patient_state ps, patient_program pp, program_workflow_state pws where pp.patient_program_id = ps.patient_program_id and pws.program_workflow_state_id = ps.state and ps.voided = 0 and pws.concept_id = :conceptId and pp.patient_id = :patientId and ps.start_date >= :startDate and ps.start_date <= :endDate"); dateOfWorkFlowStateQuery.setInteger("patientId", patientId); dateOfWorkFlowStateQuery.setInteger("conceptId", conceptId); dateOfWorkFlowStateQuery.setDate("startDate", sDate); dateOfWorkFlowStateQuery.setDate("endDate", eDate); List<Date> dateOfWorkflow = (List<Date>) dateOfWorkFlowStateQuery.list(); // TODO: figure out what is the most logical date to return when multiples are found if (dateOfWorkflow != null && dateOfWorkflow.size() > 0) { return dateOfWorkflow.get(0); } return null; }
public Date getDateOfProgramEnrolmentAscending( Integer patientId, Integer programId, Date startDate, Date endDate) { Date sDate = new Date(); sDate.setTime(0); Date eDate = Calendar.getInstance().getTime(); if (startDate != null) { sDate = startDate; } if (endDate != null) { eDate = endDate; } SQLQuery dateOfProgramEnrolment = sessionFactory .getCurrentSession() .createSQLQuery( "select date_enrolled from patient_program where patient_id = :patientId and program_id = :programId and voided = 0 and date_enrolled >= :startDate and date_enrolled <= :endDate order by date_enrolled asc"); dateOfProgramEnrolment.setInteger("patientId", patientId); dateOfProgramEnrolment.setInteger("programId", programId); dateOfProgramEnrolment.setDate("startDate", sDate); dateOfProgramEnrolment.setDate("endDate", eDate); List<Date> dateOfEnrolment = (List<Date>) dateOfProgramEnrolment.list(); // TODO: figure out what is the most logical date to return when multiples are found if (dateOfEnrolment != null && dateOfEnrolment.size() > 0) { return dateOfEnrolment.get(0); } return null; }
public Integer getObsAnswerBetweenDates( Integer patientId, List<Integer> questions, Integer answerId, Date beforeDate, Date afterDate, Date targetDate) { SQLQuery obsBeforeDate = sessionFactory .getCurrentSession() .createSQLQuery( "select obs_id from obs where person_id = :patientId and value_coded = :conceptId and concept_id in (:questions) and voided = 0 and obs_dateTime > :beforeDate and obs_dateTime < :afterDate ORDER BY abs(:targetDate - obs_dateTime)"); obsBeforeDate.setInteger("patientId", patientId); obsBeforeDate.setInteger("conceptId", answerId); obsBeforeDate.setDate("beforeDate", beforeDate); obsBeforeDate.setDate("afterDate", afterDate); obsBeforeDate.setDate("targetDate", targetDate); obsBeforeDate.setParameterList("questions", questions); List<Integer> obs = obsBeforeDate.list(); if (obs != null && obs.size() > 0) { return obs.get(0); } return null; }
public Integer getObsValueBetweenDates( Integer patientId, Integer conceptId, Integer groupId, Date beforeDate, Date afterDate, Date targetDate) { SQLQuery obsBeforeDate = sessionFactory .getCurrentSession() .createSQLQuery( "select o.obs_id from obs o, obs og where o.person_id = :patientId and o.concept_id = :conceptId and o.voided = 0 and o.obs_dateTime > :beforeDate and o.obs_dateTime < :afterDate and o.obs_group_id = og.obs_id and og.voided = 0 and og.concept_id = :groupId ORDER BY abs(:targetDate - obs_dateTime)"); obsBeforeDate.setInteger("patientId", patientId); obsBeforeDate.setInteger("conceptId", conceptId); obsBeforeDate.setDate("beforeDate", beforeDate); obsBeforeDate.setInteger("groupId", groupId); obsBeforeDate.setDate("afterDate", afterDate); obsBeforeDate.setDate("targetDate", targetDate); List<Integer> obs = obsBeforeDate.list(); if (obs != null && obs.size() > 0) { return obs.get(0); } return null; }
/** * 根据用户ID查询该用户允许访问的所有菜单列表 * * @param userId * @return */ @SuppressWarnings("unchecked") public List<Menu> getAllowedAccessMenu(Long userId) { StringBuffer sqlBuffer = new StringBuffer(); sqlBuffer.append("select * from ("); // 获取Sec_Menu表中定义且未关联资源表Sec_Resource的所有菜单列表 sqlBuffer.append(" select m.id,m.name,m.parent_menu,m.description,m.orderby from sec_menu m "); sqlBuffer.append(" where not exists (select re.id from sec_resource re where re.menu = m.id)"); sqlBuffer.append(" union all "); // 获取Sec_Resource表中已关联且未设置权限的菜单列表 sqlBuffer.append( " select m.id,m.name,m.parent_menu,re.source as description,m.orderby from sec_resource re "); sqlBuffer.append(" left outer join sec_menu m on re.menu = m.id "); sqlBuffer.append( " where re.menu is not null and not exists (select ar.authority_id from sec_authority_resource ar where ar.resource_id = re.id)"); sqlBuffer.append(" union all "); // 获取Sec_Resource表中已关联且设置权限,并根据当前登录账号拥有相应权限的菜单列表 sqlBuffer.append( " select m.id,m.name,m.parent_menu,re.source as description,m.orderby from sec_user u "); sqlBuffer.append(" left outer join sec_role_user ru on u.id=ru.user_id "); sqlBuffer.append(" left outer join sec_role r on ru.role_id=r.id "); sqlBuffer.append(" left outer join sec_role_authority ra on r.id = ra.role_id "); sqlBuffer.append(" left outer join sec_authority a on ra.authority_id = a.id "); sqlBuffer.append(" left outer join sec_authority_resource ar on a.id = ar.authority_id "); sqlBuffer.append(" left outer join sec_resource re on ar.resource_id = re.id "); sqlBuffer.append(" left outer join sec_menu m on re.menu = m.id "); sqlBuffer.append(" where u.id=? and re.menu is not null "); sqlBuffer.append(") tbl order by orderby"); SQLQuery query = menuDao.createSQLQuery(sqlBuffer.toString(), userId); query.addEntity(Menu.class); return query.list(); }
public boolean isUserBlocked(int userId, int blockerId) { Session session = null; boolean retVal = false; try { session = DBUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); String sql = "SELECT count(*) FROM BLOCKING WHERE BLOCKING_USERID= :blockerId AND BLOCKED_USERID= :userId "; SQLQuery query = session.createSQLQuery(sql); // query.addEntity(com.application.baatna.bean.Blocking.class); query.setParameter("userId", userId); query.setParameter("blockerId", blockerId); java.util.List results = (java.util.List) query.list(); // query.executeUpdate(); if (results.get(0) instanceof BigInteger) { int count = ((BigInteger) results.get(0)).intValue(); if (count != 0) retVal = true; } transaction.commit(); session.close(); } catch (HibernateException e) { System.out.println(e.getMessage()); System.out.println("error"); e.printStackTrace(); } finally { if (session != null && session.isOpen()) session.close(); } return retVal; }
/** * SQL 分页查询 * * @param page * @param sqlString * @param resultClass * @param parameter * @return */ @SuppressWarnings("unchecked") public <E> Page<E> findBySql( Page<E> page, String sqlString, Parameter parameter, Class<?> resultClass) { // get count if (!page.isDisabled() && !page.isNotCount()) { String countSqlString = "select count(*) " + removeSelect(removeOrders(sqlString)); // page.setCount(Long.valueOf(createSqlQuery(countSqlString, // parameter).uniqueResult().toString())); Query query = createSqlQuery(countSqlString, parameter); List<Object> list = query.list(); if (list.size() > 0) { page.setCount(Long.valueOf(list.get(0).toString())); } else { page.setCount(list.size()); } if (page.getCount() < 1) { return page; } } // order by String sql = sqlString; if (StringUtils.isNotBlank(page.getOrderBy())) { sql += " order by " + page.getOrderBy(); } SQLQuery query = createSqlQuery(sql, parameter); // set page if (!page.isDisabled()) { query.setFirstResult(page.getFirstResult()); query.setMaxResults(page.getMaxResults()); } setResultTransformer(query, resultClass); page.setList(query.list()); return page; }
private static void updateService(Session session) { SQLQuery sqlQuery = session.createSQLQuery("select ID," + "SERVICE_TYPE," + "PRICED_TYPE" + " from SERVICE"); List list = sqlQuery.list(); for (Object o : list) { BigInteger ID = (BigInteger) ((Object[]) o)[0]; String SERVICE_TYPE = (String) ((Object[]) o)[1]; String PRICED_TYPE = (String) ((Object[]) o)[2]; sqlQuery = session.createSQLQuery( "INSERT INTO FURNITURE_CODE (" + "ID," + "SERVICE_TYPE," + "PRICED_TYPE," + "MANUFACTURER_ID, " + "CODE," + "NAME)" + " VALUES (?, ?, ?, ?, ?, ?)"); sqlQuery.setBigInteger(0, ID); sqlQuery.setString(1, SERVICE_TYPE); sqlQuery.setString(2, PRICED_TYPE); sqlQuery.setBigInteger(3, new BigInteger("20")); sqlQuery.setString(4, "0"); sqlQuery.setString(5, SERVICE_TYPE); sqlQuery.executeUpdate(); } }
@Override public List<Visiting> getVisitngsToBeCreatedForAnotherThreeMonths() throws Exception { Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH, -3); Date date = calendar.getTime(); System.out.println("BEFORE THREE MONTHS :" + date); String queryString = "SELECT visiting_id as visiting_id" + " FROM visiting" + " WHERE start_date IN (SELECT Max(start_date)" + " FROM visiting" + " WHERE visiting_status='ACTIVE'" + " GROUP BY avg_time_per_appointment, created_user, no_of_patients, hospital_doctor_id, day_of_week)"; SQLQuery sqlQuery = sessionFactory.getCurrentSession().createSQLQuery(queryString); sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); List<Map> result = sqlQuery.list(); List<String> results = new ArrayList<String>(); for (Map map : result) { results.add((String) map.get("visiting_id")); } Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Visiting.class); criteria.add( Restrictions.conjunction() .add(Restrictions.in("visitingId", results)) .add(Restrictions.le("startDate", date))); return criteria.list(); }
@SuppressWarnings("unchecked") public List<Object[]> countTypeByLine() { String sql = "select (case when t3.name = '一号线' then '01号线' " + "when t3.name = '二号线' then '02号线' " + "when t3.name = '三号线' then '03号线' " + "when t3.name = '四号线' then '04号线' " + "when t3.name = '五号线' then '05号线' " + "when t3.name = '六号线' then '06号线' " + "when t3.name = '七号线' then '07号线' " + "when t3.name = '八号线' then '08号线' " + "when t3.name = '九号线' then '09号线' " + "when t3.name = '十号线' then '10号线' " + "when t3.name = '十一号线' then '11号线' " + "when t3.name = '十二号线' then '12号线' " + "when t3.name = '十三号线' then '13号线' " + "when t3.name = '十四号线' then '14号线' " + "when t3.name = '十五号线' then '15号线' " + "when t3.name = '十六号线' then '16号线' end) line," + "t4.name type, sum(to_number(t.compact_price))/10000 sum_price ,count(t.type_1) count_sum " + "from t_asset_info t ,t_asset_code_info t3,t_asset_code_info t4 " + "where (REGEXP_LIKE(t.compact_price,'^-?[1-9]\\d*$') or REGEXP_LIKE(t.compact_price,'^-?([1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*|0?\\.0+|0)$')) " + "and t.removed = 0 and t.route_num = t3.id and t.type_1 = t4.id " + "group by t3.name,t4.name " + "order by line"; SQLQuery query = hibernateTemplate.getSessionFactory().getCurrentSession().createSQLQuery(sql); query .addScalar("line", Hibernate.STRING) .addScalar("type", Hibernate.STRING) .addScalar("sum_price", Hibernate.DOUBLE) .addScalar("count_sum", Hibernate.LONG); return query.list(); }
/** * ��������: * * @param username * @param userpass * @return * @see com.demo.hello.model.login.dao.UserDao#queryUserByUserAndPass(java.lang.String, * java.lang.String) */ public boolean queryUserByUserAndPass(String username, String userpass) { // TODO Auto-generated method stub boolean existtag = false; Session session = getSession(); Transaction tx = session.beginTransaction(); try { String sql = "select * from com_t_users where username='******' and userpass = '******'"; SQLQuery query = session.createSQLQuery(sql); List<Object[]> list = query.list(); tx.commit(); if (list.size() > 0) existtag = true; } catch (HibernateException e) { // TODO Auto-generated catch block tx.rollback(); existtag = false; // e.printStackTrace(); } finally { session.close(); } return existtag; }
public ArrayList<com.application.baatna.bean.Session> getNearbyUsers(int userId) { ArrayList<com.application.baatna.bean.Session> users = null; Session session = null; try { session = DBUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); users = new ArrayList<com.application.baatna.bean.Session>(); String sql = "SELECT * FROM SESSION WHERE USERID = :user_id"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(com.application.baatna.bean.Session.class); query.setParameter("user_id", userId); java.util.List results = (java.util.List) query.list(); for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) { users.add((com.application.baatna.bean.Session) iterator.next()); } transaction.commit(); session.close(); } catch (HibernateException e) { System.out.println(e.getMessage()); System.out.println("error"); } finally { if (session != null && session.isOpen()) session.close(); } return users; }
public List<DeviceList> getDeviceList(String location) { SessionFactory sessionFactory = HibernateSessionFactory.getSessionFactory(); List<DeviceList> results = new ArrayList<DeviceList>(); Session session = sessionFactory.openSession(); if (session != null) { SQLQuery query = session.createSQLQuery( "select d.*,da.*,du.* " + "from device_app.device d, device_app.device_availability da, device_app.device_user du " + "where d.id=da.device_id and " + "da.checked_out_user = du.email_id and " + "d.device_status='active' and " + "du.user_location=:location"); query.addEntity(Device.class); query.addEntity(DeviceAvailability.class); query.addEntity(DeviceUser.class); query.setString("location", location); List<Object[]> resultList = query.list(); if (resultList != null && resultList.size() > 0) { System.out.println("results size is " + resultList.size()); for (Object[] array : resultList) { DeviceList row = new DeviceList(); for (Object resultObject : array) { System.out.println(resultObject.getClass()); if (resultObject instanceof Device) { Device device = (Device) resultObject; row.setBarCodeIdentifier(device.getBarCodeId()); row.setDeviceCapacity(device.getDeviceCapacity()); row.setDeviceName(device.getDeviceName()); row.setDeviceNickname(device.getDeviceNickName()); row.setDeviceOs(device.getDeviceOs()); row.setDeviceOsVersion(device.getDeviceOsVersion()); row.setDeviceSerialNumber(device.getDeviceSerialNumber()); row.setDeviceStatus(device.getDeviceStatus()); row.setDeviceType(device.getDeviceType()); row.setId(device.getId()); row.setOwnedBy(device.getDeviceOwner()); } else if (resultObject instanceof DeviceAvailability) { DeviceAvailability availability = (DeviceAvailability) resultObject; // // row.setCheckedOutAt(DateUtils.truncate(availability.getModifiedDate(),Calendar.MINUTE).toString()); row.setCheckedOutAt( DateFormatUtils.format(availability.getModifiedDate(), "yyyy-MM-dd HH:mm:ss")); row.setCurrentlyCheckedOutById(availability.getDeviceUser().getEmailId()); row.setIsCheckedOut(availability.isIsCheckedOut()); row.setLocation(location); } else { DeviceUser user = (DeviceUser) resultObject; } } results.add(row); } } } return results; }
@SuppressWarnings("unchecked") public static <E> List<E> find( Session session, String sql, Class<E> clazz, PageInfo pageInfo, Object... params) { List<E> list = new ArrayList<E>(); SQLQuery sqlQuery = session.createSQLQuery(sql); setParameter(sqlQuery, params); if (pageInfo != null) { Integer first = (pageInfo.getPageIndex() - 1) * pageInfo.getPageSize(); // if(ValidateUtil.isEmpty(first)){ // first = 1; // } sqlQuery.setFirstResult(first); @SuppressWarnings("unused") Integer pageSize = pageInfo.getPageSize(); // if(Validator.isEmpty(pageSize)){ // pageSize = PageInfoUtil.getDefaultPagesize(); // } sqlQuery.setMaxResults(pageInfo.getPageSize()); Integer total = getCount(session, sql, params); pageInfo.setPageTotal(total); } sqlQuery = sqlQuery.addEntity("model", clazz); list = sqlQuery.list(); return list; }
public List nativeSQLQuery(final String queryString, Class clazz) { SQLQuery sqlQuery = getSession().createSQLQuery(queryString); if (clazz != null) { sqlQuery = sqlQuery.addEntity(clazz); } return sqlQuery.list(); }
public void AverageRatingByProductName(String prodName) { SessionFactory sf = HibernateUtility.getSessionfactory(); Session session = sf.openSession(); session.beginTransaction(); String sql1 = "SELECT * FROM product where productName = :productName"; SQLQuery query1 = session.createSQLQuery(sql1); query1.setParameter("productName", prodName); query1.addEntity(Product.class); Product product = (Product) query1.uniqueResult(); String sql2 = "SELECT * FROM ratingdetails where productId = :productId"; SQLQuery query2 = session.createSQLQuery(sql2); query2.setParameter("productId", product.getProductId()); query2.addEntity(RatingDetails.class); List ratings = query2.list(); session.getTransaction().commit(); float count = ratings.size(); int sum = 0; for (Object r : ratings) { RatingDetails rate = (RatingDetails) r; sum = sum + rate.getRating(); } float averageRating = (sum / count); System.out.println("pid " + product + " avg " + averageRating); }
public String sqlRun() { logs = "数据管理器,v0.1,请对该应用地址保密!"; pagesize = 20; currentPage = "1"; Session session = sessionFactory.openSession(); list = null; try { SQLQuery query = session.createSQLQuery(sql); if (sql.trim().toUpperCase().indexOf("SELECT") == 0) { list = query.list(); if (list.size() > 0) { if (list.get(0) instanceof Object[]) { unitonlyone = false; } else { unitonlyone = true; } } } else { query.executeUpdate(); } logs = "SQL执行成功:" + sql + query.getQueryString(); } catch (Exception e) { logs = sql + ":" + e.getMessage() + e.toString(); } finally { session.close(); } type = "2"; return SUCCESS; }
public static List<Agreement> findFiltered( String agreementId, String billUcn, String agreementLinkId, String agreementTypeCode, String note, char neStatus) { try { String sqlQuery = "SELECT agreement.* FROM agreement WHERE `status` <> '" + neStatus + "' "; if (agreementId != null && agreementId.length() > 0) sqlQuery += " AND id like '%" + agreementId + "%' "; if (billUcn != null && billUcn.length() > 0) sqlQuery += " AND billUcn like '%" + billUcn + "%'"; if (agreementLinkId != null && agreementLinkId.length() > 0) sqlQuery += " AND agreementLinkId like '%" + agreementLinkId + "%'"; if (agreementTypeCode != null && agreementTypeCode.length() > 0) sqlQuery += " AND agreementTypeCode like '%" + agreementTypeCode + "%'"; if (note != null && note.length() > 0) sqlQuery += " AND note like '%" + note + "%'"; sqlQuery += " order by id"; SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery); query.addEntity(getObjectReference(objectName)); @SuppressWarnings("unchecked") List<Agreement> objects = query.list(); return objects; } catch (Exception e) { e.printStackTrace(); System.out.println(e.getMessage()); } return new ArrayList<Agreement>(); }
public Map<String, Object> coverageEfficiencyWard(final String zoneName) { final SQLQuery overAllQry = getQuery("revenue.ptis.coverage.efficiency.ward"); overAllQry.setParameter("zoneName", zoneName); final List<Object[]> overAllData = overAllQry.list(); final Map<String, Map<String, Object>> coverageDataHolder = new HashMap<String, Map<String, Object>>(); for (final Object[] revenueObj : overAllData) { final Map<String, Object> revnData = new HashMap<String, Object>(); revnData.put("name", String.valueOf(revenueObj[0])); final BigDecimal noOfProps = revenueObj[1] != null ? new BigDecimal(revenueObj[1].toString()) : BigDecimal.ZERO; revnData.put("noOfProps", noOfProps != null ? noOfProps.doubleValue() : "0"); final BigDecimal noOfTaxProps = revenueObj[2] != null ? new BigDecimal(revenueObj[2].toString()) : BigDecimal.ZERO; revnData.put("noOfTaxProps", noOfTaxProps != null ? noOfTaxProps.doubleValue() : "0"); final BigDecimal coverageEfficiency = revenueObj[3] != null ? new BigDecimal(revenueObj[3].toString()) : BigDecimal.ZERO; revnData.put("y", coverageEfficiency != null ? coverageEfficiency.doubleValue() : "0"); coverageDataHolder.put(String.valueOf(revenueObj[0]), revnData); } final List<Map<String, Object>> revenueCovEfficiency = new ArrayList<Map<String, Object>>(coverageDataHolder.values()); // SORT BY WARDWISE Coverage Efficiency % sortData(revenueCovEfficiency, "y"); final Map<String, Object> revenueAggrData = new HashMap<String, Object>(); revenueAggrData.put("overallCoverage", revenueCovEfficiency); return revenueAggrData; }
/** * Get user details based on the email\password combo. Used in case of login. TODO: Add check for * access Token from session */ public User getUserDetails(String email, String passWord) { User user = null; Session session = null; try { session = DBUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); user = new User(); String sql = "SELECT * FROM USER WHERE EMAIL = :email_id && PASSW = :password"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(User.class); query.setParameter("email_id", email); query.setParameter("password", passWord); java.util.List results = (java.util.List) query.list(); for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) { user = (User) iterator.next(); } transaction.commit(); session.close(); } catch (HibernateException e) { System.out.println(e.getMessage()); System.out.println("error"); } finally { if (session != null && session.isOpen()) session.close(); } return user; }
@Override @SuppressWarnings({"rawtypes", "unchecked"}) public List<IpMessageVO> findBySQL(String sql, Map<String, ?> map) { try { SQLQuery query = getSession().createSQLQuery(sql); // Set<String> keys = map.keySet(); query .addScalar("userName", Hibernate.STRING) .addScalar("userProperties", Hibernate.STRING) .addScalar("userAddress", Hibernate.STRING) .addScalar("contactName", Hibernate.STRING) .addScalar("contactPhone", Hibernate.STRING) .addScalar("contactDocumentNum", Hibernate.STRING) .addScalar("installedAddress", Hibernate.STRING) .addScalar("installedDate", Hibernate.TIMESTAMP) .addScalar("startIp", Hibernate.STRING) .addScalar("endIp", Hibernate.STRING); query.setResultTransformer(Transformers.aliasToBean(IpMessageVO.class)); // for (String key : keys) { // Object o = map.get(key); // if (o instanceof Collection) { // query.setParameterList(key, (Collection) map.get(key)); // } else if(o instanceof Page){ // query.setFirstResult((((Page) o).getPageNo()-1)*((Page) o).getPageSize()); // query.setMaxResults(((Page) o).getPageSize()); // }else { // query.setParameter(key, map.get(key)); // } // } return query.list(); } catch (Exception e) { throw new HsCloudException(BillConstant.ACCOUNT_DATABASE_ERROR, e.getMessage(), logger, e); } }
public com.application.baatna.bean.Session getSession(int userId) { com.application.baatna.bean.Session loginSession = null; Session session = null; try { session = DBUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); loginSession = new com.application.baatna.bean.Session(); // finding user info System.out.println("Getting Record"); String sql = "SELECT * FROM SESSION WHERE USERID = :userid"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(com.application.baatna.bean.Session.class); query.setParameter("userid", userId); java.util.List results = (java.util.List) query.list(); for (Iterator iterator = ((java.util.List) results).iterator(); iterator.hasNext(); ) { loginSession = (com.application.baatna.bean.Session) iterator.next(); } transaction.commit(); session.close(); } catch (HibernateException e) { System.out.println(e.getMessage()); System.out.println("error"); } finally { if (session != null && session.isOpen()) session.close(); } return loginSession; }
@Transactional @Override public String removeSyncDuplicates() { String syncTable = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'openmrs' AND table_name in('sync_server_record', 'sync_record');"; String q = "SELECT MIN(record_id) as record_id FROM sync_record s group by original_uuid having count(*)>1;"; String delete_sync_server_record = " Delete from sync_server_record where record_id in (:recordIds);"; String delete_sync_record = " Delete from sync_record where record_id in (:recordIds);"; String result = ""; SQLQuery checkSyncInstallation = sessionFactory.getCurrentSession().createSQLQuery(syncTable); List noOfSyncTables = checkSyncInstallation.list(); Integer res = ((BigInteger) noOfSyncTables.get(0)).intValue(); if (res == 0) { return "Sync Module is not installed. Please install it if needed"; } SQLQuery selectDuplicatesQuery = sessionFactory.getCurrentSession().createSQLQuery(q); List<Integer> duplicateIds = selectDuplicatesQuery.list(); if (duplicateIds.isEmpty()) { return "No duplicates were found."; } SQLQuery deleteDuplicateSyncServerRecordQuery = sessionFactory.getCurrentSession().createSQLQuery(delete_sync_server_record); SQLQuery deleteDuplicateSyncRecordQuery = sessionFactory.getCurrentSession().createSQLQuery(delete_sync_record); deleteDuplicateSyncServerRecordQuery.setParameter("recordIds", duplicateIds); deleteDuplicateSyncRecordQuery.setParameter("recordIds", duplicateIds); int syncServerRecordResult = deleteDuplicateSyncServerRecordQuery.executeUpdate(); result += (syncServerRecordResult >= 0) ? syncServerRecordResult + " duplicates in sync_server_record successfully removed\n" : "Could not remove sync_server_record duplicates\n"; int syncRecordResult = deleteDuplicateSyncRecordQuery.executeUpdate(); result += (syncRecordResult >= 0) ? syncRecordResult + " duplicates in sync_record successfully removed\n" : "Could not remove sync_record duplicates\n"; return result; }
@Override @SuppressWarnings("unchecked") public List<CommentModel> findByAllWithSQLQuery() { Session session = sessionFactory.getCurrentSession(); SQLQuery query = session.createSQLQuery("SELECT * FROM comment"); List<CommentModel> result = query.list(); return result; }
@Override public List<LanguageProficiency> findByMemberId(int memberId) { SQLQuery sqlQuery = getNamedSQLQuery("resume.query.updateResume.getLanguageProficiencyByMemberId"); sqlQuery.setParameter("memberId", memberId); sqlQuery.addEntity(LanguageProficiency.class); return (List<LanguageProficiency>) sqlQuery.list(); }
/** Get the user details based on the userId */ public Object[] getRating(int currentUserId, int userId) { Object[] objects = new Object[2]; Session session = null; try { session = DBUtil.getSessionFactory().openSession(); Transaction transaction = session.beginTransaction(); String sql = "SELECT count(*) FROM USERWISH WHERE ( USERID = :currentUserId and USER_TWO_ID =:userId) or (USERID = :currentUserId1 and USER_TWO_ID =:userId1)"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(User.class); query.setParameter("currentUserId", currentUserId); query.setParameter("userId", userId); query.setParameter("currentUserId1", userId); query.setParameter("userId1", currentUserId); java.util.List results = (java.util.List) query.list(); // check if the current user can rate the userId. if (results.get(0) instanceof BigInteger && ((BigInteger) results.get(0)).intValue() > 0) { objects[0] = true; // fetch the previous rating value String userRatingSql = "SELECT Rating FROM UserRating WHERE Reviewer =:reviewer and Reviewed = :reviewed"; SQLQuery userRatingQuery = session.createSQLQuery(userRatingSql); userRatingQuery.addEntity(User.class); userRatingQuery.setParameter("reviewer", currentUserId); userRatingQuery.setParameter("reviewed", userId); java.util.List userRatingResults = (java.util.List) query.list(); if (userRatingResults.get(0) instanceof BigInteger) { objects[1] = ((BigInteger) results.get(0)).intValue(); } } transaction.commit(); session.close(); } catch (HibernateException e) { System.out.println(e.getMessage()); System.out.println("error"); } finally { if (session != null && session.isOpen()) session.close(); } return objects; }
public <X> List<X> findBySql(String sql, Object... values) { SQLQuery sqlQuery = getSession().createSQLQuery(sql); if (values != null) { for (int i = 0; i < values.length; i++) { sqlQuery.setParameter(i, values[i]); } } return sqlQuery.list(); }
public int countBySQL(String count_sql) { SessionFactory sf = HibernateUtil.getSessionFactory(); Session session = sf.getCurrentSession(); session.beginTransaction(); SQLQuery q = session.createSQLQuery(count_sql); int ret = ((BigInteger) q.list().get(0)).intValue(); session.getTransaction().commit(); return ret; }