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 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;
  }
 public List nativeSQLQuery(final String queryString, Class clazz) {
   SQLQuery sqlQuery = getSession().createSQLQuery(queryString);
   if (clazz != null) {
     sqlQuery = sqlQuery.addEntity(clazz);
   }
   return sqlQuery.list();
 }
Exemple #4
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();
 }
Exemple #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;
 }
  /*
   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;
  }
 /**
  * 拿到所有的根类别
  *
  * @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;
 }
Exemple #8
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>();
  }
Exemple #9
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;
  }
Exemple #10
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;
  }
Exemple #11
0
  public boolean editProfilePic(int userId, String profilePic) {

    Session session = null;
    try {
      session = DBUtil.getSessionFactory().openSession();

      Transaction transaction = session.beginTransaction();
      String sql =
          "UPDATE USER SET PROFILE_PIC = :profilePic, MODIFIED = :modified   WHERE USERID = :user_id";
      SQLQuery query = session.createSQLQuery(sql);
      query.addEntity(User.class);
      query.setParameter("profilePic", profilePic);
      query.setParameter("user_id", userId);
      query.setParameter("modified", System.currentTimeMillis());
      int result = query.executeUpdate();

      User user = getUserDetails(userId);
      user.setModified(System.currentTimeMillis());

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

    } catch (HibernateException e) {
      System.out.println(e.getMessage());
      e.printStackTrace();

      System.out.println("error");
      return false;
    } finally {
      if (session != null && session.isOpen()) session.close();
    }

    return true;
  }
 /**
  * 拿到某一个目录的下一级子类别
  *
  * @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;
 }
Exemple #13
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;
  }
Exemple #14
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;
  }
Exemple #15
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;
  }
Exemple #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;
  }
  public List<ChatMessageKeywords> loadBySQL(String sql) {
    Session session = sessionFactory.getCurrentSession();

    SQLQuery q = session.createSQLQuery(sql);
    List<ChatMessageKeywords> list = q.addEntity(ChatMessageKeywords.class).list();

    return list;
  }
 @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();
 }
 public List<Category> getAllChilds(List<Category> list, String name) {
   String sql = "select * from znzn_category where name='" + name + "'";
   Session session = sessionFactory.getCurrentSession();
   SQLQuery q = session.createSQLQuery(sql);
   Category category = (Category) q.addEntity(Category.class).uniqueResult();
   int id = category.getId();
   return getAllChilds(list, id);
 }
Exemple #20
0
  /** 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 List<Member> loadBySQL(String sql) {
   SessionFactory sf = HibernateUtil.getSessionFactory();
   Session session = sf.getCurrentSession();
   session.beginTransaction();
   SQLQuery q = session.createSQLQuery(sql);
   List<Member> list = q.addEntity(Member.class).list();
   session.getTransaction().commit();
   return list;
 }
 public Query createNativeQuery(String sqlString, Class resultClass) {
   try {
     SQLQuery q = getSession().createSQLQuery(sqlString);
     q.addEntity("alias1", resultClass.getName(), LockMode.READ);
     return new QueryImpl(q, this);
   } catch (HibernateException he) {
     throw convert(he);
   }
 }
 public boolean enameExists(String ename) {
   String sql = "select * from znzn_category where ename=?;";
   Session session = sessionFactory.getCurrentSession();
   SQLQuery sqlQuery = session.createSQLQuery(sql);
   sqlQuery.setString(0, ename);
   List<Category> list = sqlQuery.addEntity(Category.class).list();
   if (list.size() > 0) return true;
   return false;
 }
  public void calulateAverageRating() {

    SessionFactory sf = HibernateUtility.getSessionfactory();

    Session session = sf.openSession();

    session.beginTransaction();
    String sql = "SELECT * FROM product";
    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(Product.class);
    List listProductIds = query.list();
    for (Object pro : listProductIds) {
      Product p = (Product) pro;

      String sql1 = "SELECT * FROM ratingdetails where productId = :productId";
      SQLQuery query1 = session.createSQLQuery(sql1);
      query1.setParameter("productId", p.getProductId());
      query1.addEntity(RatingDetails.class);

      List ratings = query1.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);

      avgProdList.put(p, averageRating);
      System.out.println("pid " + p.getProductId() + " avg " + averageRating);
    }
    System.out.println(avgProdList);

    System.out.println();
    sortAverageRating();
    /*for (Iterator iterator = listProductIds.iterator(); iterator.hasNext();) {
    	RatingDetails ratingobj = (RatingDetails) iterator.next();
    	System.out.print("First Name: " + employee.getFirstName());
    	System.out.print("  Last Name: " + employee.getLastName());
    	System.out.println("  Salary: " + employee.getSalary());
    }*/
  }
 /**
  * 使用原生sql根据应用专题分页查询应用信息列表
  *
  * @param page
  * @param sql
  * @return [参数说明]
  * @return List<DpAppInfo> [返回类型说明]
  * @exception throws [违例类型] [违例说明]
  */
 @SuppressWarnings("unchecked")
 @Override
 public List<DpAppInfo> queryForPageBySql(Page<AppSubjectType> page, String sql) {
   Session session = getSession();
   SQLQuery sq = session.createSQLQuery(sql);
   sq.addEntity(DpAppInfo.class);
   sq.setFirstResult(page.getBeginCount());
   sq.setMaxResults(page.getPageSize());
   return sq.list();
 }
 /**
  * 原生SQL查询, 没有完善,慎用。。。
  *
  * @return List<T>
  */
 public List<T> findByJdbc(String sql, Object... values) {
   SQLQuery sqlQuery = getSession().createSQLQuery(sql);
   if (values != null) {
     for (int i = 0; i < values.length; i++) {
       sqlQuery.setString(i, values[i].toString());
     }
   }
   List<T> results = sqlQuery.addEntity(entityClass).list();
   return results;
 }
  /**
   * Given a <code>Filter</code> object describing any/all of the following fields:
   *
   * <ul>
   *   <li>gene chromosome
   *   <li>gene ID
   *   <li>gene name
   *   <li>gene symbol
   *   <li>MGI reference
   * </ul>
   *
   * , this method performs a query, ANDing all non-empty fields in a WHERE clause against the genes
   * table. The result is a <code>List&lt;GenesDAO
   * &gt;</code> of qualifying results. A list is always returned, even if there are no results.
   *
   * @param filter values to filter by
   * @return a list of <code>GenesDAO</code>.
   */
  public List<GenesDAO> getFilteredGenesList(Filter filter) {
    String chromosomeWhere = "";
    String geneIdWhere = "";
    String geneNameWhere = "";
    String geneSymbolWhere = "";
    String mgiReferenceWhere = "";
    List<GenesDAO> targetList = new ArrayList();
    int geneId = -1;

    String queryString = "SELECT * FROM genes\nWHERE (1 = 1)";
    if ((filter.getChromosome() != null) && (!filter.getChromosome().isEmpty())) {
      chromosomeWhere = "  AND (chromosome = :chromosome)\n";
      queryString += chromosomeWhere;
    }
    Integer iGeneId = Utils.tryParseInt(filter.getGeneId());
    if ((iGeneId != null) && (iGeneId.intValue() > 0)) {
      geneId = iGeneId.intValue();
      geneIdWhere = "  AND (id_gene = :id_gene)\n";
      queryString += geneIdWhere;
    }
    if ((filter.getGeneName() != null) && (!filter.getGeneName().isEmpty())) {
      geneNameWhere = "  AND (name LIKE :name)\n";
      queryString += geneNameWhere;
    }
    if ((filter.getGeneSymbol() != null) && (!filter.getGeneSymbol().isEmpty())) {
      geneSymbolWhere = "  AND (symbol LIKE :symbol)\n";
      queryString += geneSymbolWhere;
    }
    if ((filter.getMgiReference() != null) && (!filter.getMgiReference().isEmpty())) {
      mgiReferenceWhere = "  AND (mgi_ref LIKE :mgi_ref)\n";
      queryString += mgiReferenceWhere;
    }
    queryString += "ORDER BY name\n";

    Session session = factory.getCurrentSession();
    try {
      session.beginTransaction();
      SQLQuery query = session.createSQLQuery(queryString);
      if (!chromosomeWhere.isEmpty()) query.setParameter("chromosome", filter.getChromosome());
      if (!geneIdWhere.isEmpty()) query.setParameter("id_gene", geneId);
      if (!geneNameWhere.isEmpty()) query.setParameter("name", "%" + filter.getGeneName() + "%");
      if (!geneSymbolWhere.isEmpty())
        query.setParameter("symbol", "%" + filter.getGeneSymbol() + "%");
      if (!mgiReferenceWhere.isEmpty())
        query.setParameter("mgi_ref", "%" + filter.getMgiReference() + "%");

      targetList = query.addEntity(GenesDAO.class).list();
      session.getTransaction().commit();
    } catch (HibernateException e) {
      session.getTransaction().rollback();
      throw e;
    }

    return targetList;
  }
Exemple #28
0
  public void setUserDayRating() {

    Session session = null;
    double rating = 0;
    int userId;
    double avg1;
    double avg2;

    try {
      session = DBUtil.getSessionFactory().openSession();
      Transaction transaction = session.beginTransaction();

      // String sql = "SELECT er.Reviewed, (Select avg(Rating) as
      // AvgRating from UserRating ur where ur.Reviewed = er.Reviewed)
      // temp from UserRating er";
      String sql =
          "SELECT er.USERID,(SELECT avg(U2RATEDU1) AS AvgRating from USERWISH uw where uw.USERID=er.USERID and uw.Wish_Status=er.Wish_Status and uw.U2RATEDU1<>:b) avg1, (SELECT avg(U1RATEDU2) AS AvgRatingTwo from USERWISH aw WHERE aw.USER_TWO_ID=er.USERID and aw.Wish_Status=er.Wish_Status and aw.U1RATEDU2<>:a) avg2 from USERWISH er WHERE er.WISH_STATUS= :status";
      SQLQuery query = session.createSQLQuery(sql);
      query.setParameter("a", 0);
      query.setParameter("b", 0);
      query.setParameter("status", CommonLib.STATUS_FULLFILLED);
      query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

      List results = query.list();
      for (Object object : results) {
        Map row = (Map) object;
        userId = (int) row.get("USERID");
        if (row.get("avg1") != null) avg1 = (double) row.get("avg1");
        else avg1 = 0;
        if (row.get("avg2") != null) avg2 = (double) row.get("avg2");
        else avg2 = 0;
        if (avg1 != 0 && avg2 != 0) rating = (avg1 + avg2) / 2;
        else rating = Math.max(avg1, avg2);
        if (rating != 0) {
          String sql2 = "UPDATE USER SET RATING= :rating WHERE USERID= :userId";
          SQLQuery query2 = session.createSQLQuery(sql2);
          query2.addEntity(User.class);
          query2.setParameter("rating", rating);
          query2.setParameter("userId", userId);

          query2.executeUpdate();
        }
      }
      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();
    }
  }
Exemple #29
0
 /**
  * 设置查询结果类型
  *
  * @param query
  * @param resultClass
  */
 private void setResultTransformer(SQLQuery query, Class<?> resultClass) {
   if (resultClass != null) {
     if (resultClass == Map.class) {
       query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
     } else if (resultClass == List.class) {
       query.setResultTransformer(Transformers.TO_LIST);
     } else {
       query.addEntity(resultClass);
     }
   }
 }
 public static List<Match> getAllMatchesForSummoner(long id) {
   Session session = HibernateUtils.getSessionFactory().openSession();
   SQLQuery query =
       session.createSQLQuery(
           " SELECT * FROM `match` m, participantstats ps,  participantidentity pi, player p, matchparticipant mp WHERE m.matchId = mp.matchId "
               + "  AND m.matchId = pi.matchId AND pi.dbId = p.dbId AND mp.dbId = ps.dbId AND p.summonerId = :id AND mp.participantId = pi.participantId");
   query.setParameter("id", id);
   query.addEntity(Match.class);
   session.close();
   return query.list();
 }