示例#1
0
  /*
   Gets the LoginID ignoring case.
  */
  private Login findByIdOracleInsensitive(org.openiam.idm.srvc.auth.dto.LoginId id) {

    log.debug("findByIdOracleInsensitive..");

    String select =
        " select /*+ INDEX(IDX_LOGIN_UPPER)  */ "
            + " SERVICE_ID, LOGIN, MANAGED_SYS_ID, IDENTITY_TYPE, CANONICAL_NAME, USER_ID, PASSWORD, "
            + " PWD_EQUIVALENT_TOKEN, PWD_CHANGED, PWD_EXP, RESET_PWD, FIRST_TIME_LOGIN, IS_LOCKED, STATUS, "
            + " GRACE_PERIOD, CREATE_DATE, CREATED_BY, CURRENT_LOGIN_HOST, AUTH_FAIL_COUNT, LAST_AUTH_ATTEMPT, "
            + " LAST_LOGIN, IS_DEFAULT, PWD_CHANGE_COUNT, LAST_LOGIN_IP, PREV_LOGIN, PREV_LOGIN_IP, "
            + " PSWD_RESET_TOKEN, PSWD_RESET_TOKEN_EXP, LOGIN_ATTR_IN_TARGET_SYS "
            + " FROM 	LOGIN  "
            + " WHERE SERVICE_ID = :serviceId AND UPPER(LOGIN) = :login AND MANAGED_SYS_ID = :managedSysId  ";

    log.debug("SQL to get Identity: " + select);

    Session session = sessionFactory.getCurrentSession();

    SQLQuery qry = session.createSQLQuery(select);
    qry.addEntity(Login.class);

    qry.setString("serviceId", id.getDomainId());
    qry.setString("login", id.getLogin().toUpperCase());
    qry.setString("managedSysId", id.getManagedSysId());

    try {
      return (Login) qry.uniqueResult();

    } catch (Exception e) {
      log.error(e.toString());
    }
    return null;
  }
示例#2
0
  /** Removes the push notification Id */
  public int nullifyPushId(int userId) {

    Session session = null;

    int pushId = 0;
    try {

      session = DBUtil.getSessionFactory().openSession();

      Transaction transaction = session.beginTransaction();

      String sql = "DELETE FROM SESSION WHERE PUSHID = :pushId && USERID = :user_id";
      SQLQuery query = session.createSQLQuery(sql);
      query.addEntity(com.application.baatna.bean.Session.class);
      query.setParameter("pushId", pushId);
      query.setParameter("user_id", userId);
      int result = query.executeUpdate();

      transaction.commit();
      session.close();

    } catch (HibernateException e) {
      System.out.println(e.getMessage());
      e.printStackTrace();
      System.out.println("error");
      return -1;
    } finally {
      if (session != null && session.isOpen()) session.close();
    }

    return pushId;
  }
示例#3
0
  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 Integer userIDInBd(User user, String id) {
    Session session = HibernateUtil.openSession();
    Transaction transaction = null;
    Integer userIdDB = null;

    try {
      transaction = session.getTransaction();
      transaction.begin();
      SQLQuery query = session.createSQLQuery("SELECT id FROM user WHERE login=?;");
      query.setParameter(0, user.getLogin());

      userIdDB = (Integer) query.uniqueResult();

      System.out.println("Печать id юзера из базы" + userIdDB);

      transaction.commit(); // применяем транзакцию

    } catch (Exception e) {

      // отменяем транзакцию
      transaction.rollback();
      e.printStackTrace();
    } finally {
      session.close();
    }

    return userIdDB;
  }
示例#5
0
 @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;
 }
示例#6
0
	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;
	}
示例#7
0
 public List nativeSQLQuery(final String queryString, Class clazz) {
   SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
   if (clazz != null) {
     sqlQuery = sqlQuery.addEntity(clazz);
   }
   return sqlQuery.list();
 }
示例#8
0
 /**
  * ��������:
  *
  * @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;
 }
  @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();
  }
示例#10
0
 /**
  * 根据用户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 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;
  }
示例#12
0
  public String addCriticalTransaction(int authID, String ManagerID) {
    try {
      Session session = Hibernate_Utility.getSessionFactory().openSession();

      criticalTransactions cTrans = getCriticalTransaction(authID);
      if (cTrans == null) {
        return "Action Failed! Please try later.";
      }
      String insertInternalUserQuery =
          "INSERT INTO critical_transactions_authorizations (`transaction_id`,`auth_given_by`,`auth_given_to`) VALUES ( "
              + cTrans.getTransactionId()
              + " , \'"
              + cTrans.getAuthGivento()
              + "\' , \'"
              + ManagerID
              + "\');";
      SQLQuery query = session.createSQLQuery(insertInternalUserQuery);
      int rowsAffected = query.executeUpdate();
      if (rowsAffected > 0) {
        String commitQuery = "COMMIT;";
        query = session.createSQLQuery(commitQuery);
        rowsAffected = query.executeUpdate();
        return "successfully escalated!";
      } else {
        return "Cannot Escalate now! Try Later";
      }
    } catch (HibernateException e) {
      return "Action Failed! Please try later.";
    } catch (ApplicationException e) {
      return "Action Failed! Please try later.";
    }
  }
  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;
  }
 /**
  * 拿到所有的根类别
  *
  * @return
  */
 public List<Category> getTopCategories() {
   String sql = "select * from znzn_category where grade=1";
   Session session = sessionFactory.getCurrentSession();
   SQLQuery q = session.createSQLQuery(sql);
   List<Category> list = q.addEntity(Category.class).list();
   return list;
 }
示例#16
0
  public boolean addUserToBlockedList(int blockingUserId, int userId) {
    Session session = null;
    boolean retVal = false;
    try {
      session = DBUtil.getSessionFactory().openSession();

      Transaction transaction = session.beginTransaction();

      String sql =
          "INSERT INTO BLOCKING(BLOCKING_USERID,BLOCKED_USERID) VALUES(:blockingUserId,:userId)";
      SQLQuery query = session.createSQLQuery(sql);
      query.addEntity(com.application.baatna.bean.Blocking.class);
      query.setParameter("userId", userId);
      query.setParameter("blockingUserId", blockingUserId);

      query.executeUpdate();
      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;
  }
 /**
  * 拿到某一个目录的下一级子类别
  *
  * @return
  */
 public List<Category> getChilds(int id) {
   String sql = "select * from znzn_category where pid=" + id;
   Session session = sessionFactory.getCurrentSession();
   SQLQuery q = session.createSQLQuery(sql);
   List<Category> list = q.addEntity(Category.class).list();
   return list;
 }
示例#18
0
  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;
  }
示例#19
0
  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>();
  }
示例#20
0
  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;
  }
示例#21
0
  public long count(final String queryString, final Object... values) {
    String tmp = queryString.toLowerCase().replaceAll("\\s+", " ");
    if (!tmp.contains("group by") && !tmp.contains("distinct")) {
      String countQueryString =
          "select count(*) " + queryString.substring(queryString.indexOf("from "));
      return (Long) queryUnique(countQueryString, values);
    }

    QueryTranslatorImpl queryTranslator =
        new QueryTranslatorImpl(
            null, queryString, Collections.EMPTY_MAP, (SessionFactoryImplementor) sessionFactory);
    queryTranslator.compile(Collections.EMPTY_MAP, false);

    final String countQueryString =
        "select count(*) as result from (" + queryTranslator.getSQLString() + ")";
    SQLQuery query = getSession().createSQLQuery(countQueryString);
    if (cacheRegion.get() != null) {
      // TODO
      // SQLQuery遭遇UpdateTimestampsCache时,有问题。详见:http://raymondhekk.iteye.com/blog/250427
      // 故这里暂停使用二级缓存
      // query.setCacheRegion(cacheRegion.get());
      // query.setCacheable(true);
      cacheRegion.set(null);
    }

    for (int position = 0; position < values.length; position++) {
      query.setParameter(position, values[position]);
    }
    query.addScalar("result", StandardBasicTypes.BIG_DECIMAL);
    return ((Number) query.uniqueResult()).longValue();
  }
示例#22
0
  /** Delete an accessToken for a particular user */
  public boolean nullifyAccessToken(int userId, String accessToken) {

    Session session = null;

    try {

      session = DBUtil.getSessionFactory().openSession();

      Transaction transaction = session.beginTransaction();

      String sql = "DELETE FROM SESSION WHERE ACCESS_TOKEN = :access_token && USERID = :user_id";
      SQLQuery query = session.createSQLQuery(sql);
      query.addEntity(com.application.baatna.bean.Session.class);
      query.setParameter("access_token", accessToken);
      query.setParameter("user_id", userId);
      int result = query.executeUpdate();
      transaction.commit();
      session.close();
      return true;

    } catch (HibernateException e) {
      System.out.println(e.getMessage());
      System.out.println("error");
    } finally {
      if (session != null && session.isOpen()) session.close();
    }

    return false;
  }
  // Метод проверки существования пользователя по логину в базе
  public String userLoginInBd(User user) {
    Session session = HibernateUtil.openSession();
    Transaction transaction = null;
    String userLoginDB = null;

    try {
      transaction = session.getTransaction();
      transaction.begin();
      SQLQuery query = session.createSQLQuery("SELECT login FROM user WHERE login=?;");
      query.setParameter(0, user.getLogin());

      userLoginDB = (String) query.uniqueResult(); // возвращает экземпляр или null

      transaction.commit(); // применяем транзакцию

      System.out.println("Пользователь с логином = " + userLoginDB + "уже существует");

    } catch (Exception e) {

      // отменяем транзакцию
      transaction.rollback();
      e.printStackTrace();
    } finally {
      session.close();
    }

    return userLoginDB;
  }
示例#24
0
  /**
   * 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;
  }
  /**
   * Deletes all database entries based on the class where method is called.
   *
   * @return
   */
  public synchronized boolean deleteAll() {

    Session session = null;
    Transaction transaction = null;
    boolean errorResult = false;

    try {
      session = HibernateUtil.getCurrentSession();
      transaction = session.beginTransaction();
      SQLQuery query = session.createSQLQuery(DELETE_ALL + getTableName());
      query.executeUpdate();
      transaction.commit();
      return true;
    } catch (HibernateException exception) {
      rollback(transaction);
      BookingLogger.getDefault()
          .severe(this, Messages.METHOD_DELETE_ALL, Messages.HIBERNATE_FAILED, exception);
      return errorResult;
    } catch (RuntimeException exception) {
      rollback(transaction);
      BookingLogger.getDefault()
          .severe(this, Messages.METHOD_DELETE_ALL, Messages.GENERIC_FAILED, exception);
      return errorResult;
    } finally {
      closeSession();
    }
  }
示例#26
0
 public Collection<KeyValueModel> getTradingPartnerEmailID() {
   Collection<KeyValueModel> keyvalue = new ArrayList<KeyValueModel>();
   Set<String> emailId = new HashSet<String>();
   try {
     StringBuilder query = new StringBuilder();
     query.append(
         "SELECT CONCAT_WS(',',GROUP_CONCAT(cs.`email1`) ,GROUP_CONCAT(cs.`email2`),GROUP_CONCAT(cc.`email`))");
     query.append(" FROM customer_address cs LEFT JOIN customer_contact cc ");
     query.append("ON cc.`customer_address_id` = cs.`id` ");
     query.append("WHERE cs.trading_partner_id IN (SELECT tp.`id` ");
     query.append("FROM trading_partner tp WHERE tp.`account_number` IN ");
     query.append("(SELECT acc.`number` FROM user_account acc WHERE ");
     query.append("acc.`user_id` = ").append(currentUser.getId()).append("))");
     SQLQuery s = new BaseHibernateDAO<Domain>().createSQLQuery(query.toString());
     Object result = s.uniqueResult();
     emailId.addAll(Arrays.asList(result.toString().split(",")));
     keyvalue.add(new KeyValueModel("", "-- Select --"));
     for (String email : emailId) {
       if (!email.isEmpty()) {
         keyvalue.add(new KeyValueModel(email));
       }
     }
   } catch (Exception e) {
     e.printStackTrace();
   }
   return keyvalue;
 }
示例#27
0
 /**
  * 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;
 }
示例#28
0
  @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);
    }
  }
 @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();
 }
示例#30
0
 @Override
 public void deleteWithSQLQuery(int commentId) {
   Session session = sessionFactory.getCurrentSession();
   String sql = "DELETE FROM comment WHERE comment_id=:commentId";
   SQLQuery query = session.createSQLQuery(sql);
   query.setParameter("commentId", commentId);
   query.executeUpdate();
 }