private static boolean atualizarCarencia(Segurado segurado, Long idTitular) throws Exception {
    String sqlConsignacao =
        "select min(c.dataDoCredito) from ConsignacaoSegurado c  where c.titular.idSegurado = :idTitular "
            + "and c.statusConsignacao  = 'P' and c.dataDoCredito >= :dataAdesao";

    String sqlCobranca =
        "select min(c.dataPagamento) from Cobranca c where c.titular.idSegurado = :idTitular  "
            + "and descricao = 'Pago(a)' and c.dataPagamento >= :dataAdesao";

    Query queryConsignacao = HibernateUtil.currentSession().createQuery(sqlConsignacao);
    Query queryCobranca = HibernateUtil.currentSession().createQuery(sqlCobranca);

    queryConsignacao.setLong("idTitular", idTitular);
    queryConsignacao.setDate("dataAdesao", segurado.getDataAdesao());

    Date inicioCarencia = (Date) queryConsignacao.uniqueResult();

    if (inicioCarencia == null) {
      queryCobranca.setLong("idTitular", idTitular);
      queryCobranca.setDate("dataAdesao", segurado.getDataAdesao());
      inicioCarencia = (Date) queryCobranca.uniqueResult();
    }

    if (inicioCarencia != null) {
      System.out.println(segurado.getNumeroDoCartao() + " - " + inicioCarencia);
      segurado.setInicioDaCarencia(inicioCarencia);
      ImplDAO.save(segurado);
      return true;
    }
    return false;
  }
  public List<GameEntity> findGameByPeriod(LocalDate timeFrom, LocalDate timeTo) {
    // 設定sql字串
    // HQL的帶入變數為 timeFrom, timeTo, teamName
    String sql = "from GameEntity games where 1= 1";
    String sql1 = " and games.gameTime >= :timeFrom"; // 搜尋大於 timeFrom的時間
    String sql2 = " and games.gameTime < :timeTo"; // 搜尋小於 timeTo的時間
    String sql4 = " order by games.gameTime";
    // 判斷是否有timeFrom 如有則加入sql1的敘述
    boolean hasTimeFrom = false;
    if (timeFrom != null) {
      sql += sql1;
      hasTimeFrom = true;
    }

    // 判斷是否有timeTo 如有則加入sql2的敘述
    boolean hasTimeTo = false;
    if (timeTo != null) {
      sql += sql2;
      hasTimeTo = true;
    }

    // 帶入排序方式
    sql += sql4;

    Query query = getSession().createQuery(sql);

    // 根據加入的sql敘述帶入變數
    if (hasTimeFrom) {
      query.setDate("timeFrom", timeFrom.toDate()); // sql沒有支援LocalDateTime, 但有支援Date, 所以轉成Date
    }
    if (hasTimeTo) {
      query.setDate("timeTo", timeTo.toDate());
    }
    return query.list();
  }
  public List<Login> findUserPswdExpYesterday() {
    log.debug("findUserPswdExpToday: findUserNearPswdExp called.");

    java.sql.Date expDate = new java.sql.Date(System.currentTimeMillis());
    java.sql.Date endDate = new java.sql.Date(expDate.getTime());

    Calendar c = Calendar.getInstance();
    c.add(Calendar.DAY_OF_YEAR, 1);
    c.setTime(expDate);
    expDate.setTime(c.getTimeInMillis());

    c.add(Calendar.DAY_OF_YEAR, 1);
    endDate.setTime(c.getTimeInMillis());

    log.debug("dates between : " + expDate.toString() + " " + endDate.toString());

    String sql =
        new String(
            " from org.openiam.idm.srvc.auth.dto.Login l where "
                + " l.pwdExp BETWEEN :startDate and :endDate");

    Session session = sessionFactory.getCurrentSession();
    Query qry = session.createQuery(sql);
    qry.setDate("startDate", expDate);
    qry.setDate("endDate", endDate);

    List<Login> results = (List<Login>) qry.list();
    if (results == null) {
      return (new ArrayList<Login>());
    }
    return results;
  }
  /* (non-Javadoc)
   * @see org.openiam.idm.srvc.auth.login.LoginDAO#findInactiveUsers(int, int)
   */
  public List<Login> findInactiveUsers(int startDays, int endDays, String managedSysId) {
    log.debug("findInactiveUsers called.");
    log.debug("Start days=" + startDays);
    log.debug("End days=" + endDays);

    boolean start = false;
    long curTimeMillis = System.currentTimeMillis();
    Date startDate = new Date(curTimeMillis);
    Date endDate = new Date(curTimeMillis);

    StringBuilder sql =
        new StringBuilder(
            " from org.openiam.idm.srvc.auth.dto.Login l where "
                + " l.id.managedSysId = :managedSys and ");

    if (startDays != 0) {
      sql.append(" l.lastLogin <= :startDate ");
      start = true;

      Calendar c = Calendar.getInstance();
      c.setTime(startDate);
      c.add(Calendar.DAY_OF_YEAR, (-1 * startDays));
      startDate.setTime(c.getTimeInMillis());

      log.debug("starDate = " + startDate.toString());
    }
    if (endDays != 0) {
      if (start) {
        sql.append(" and ");
      }
      sql.append(" l.lastLogin >= :endDate ");

      Calendar c = Calendar.getInstance();
      c.setTime(endDate);
      c.add(Calendar.DAY_OF_YEAR, (-1 * endDays));
      endDate.setTime(c.getTimeInMillis());

      log.debug("endDate = " + endDate.toString());
    }

    Session session = sessionFactory.getCurrentSession();
    Query qry = session.createQuery(sql.toString());

    qry.setString("managedSys", managedSysId);

    if (startDays != 0) {
      qry.setDate("startDate", startDate);
    }
    if (endDays != 0) {
      qry.setDate("endDate", endDate);
    }

    List<Login> results = (List<Login>) qry.list();
    if (results == null) {
      return (new ArrayList<Login>());
    }
    return results;
  }
 public List<UserEntity> findByCreateTime(LocalDate createTime) {
   Query query =
       getSession()
           .createQuery(
               "from UserEntity u where CREATE_TIME >= :createTime1 and CREATE_TIME < :createTime2 order by CREATE_TIME");
   query.setDate("createTime1", createTime.toDate());
   query.setDate("createTime2", createTime.plusDays(1).toDate());
   return query.list();
 }
Example #6
0
  @SuppressWarnings("unchecked")
  private boolean checkForCampaignIDExist(
      final Session session, final BatchCampaignAssociationModel batchCampaignAssociationModel)
      throws HibernateException {
    // Session session = null;
    List<BatchCampaignAssociationModel> list = Collections.emptyList();
    boolean campIdExist = true;
    final String campId = batchCampaignAssociationModel.getCampaignId();
    try {
      // session = sessionFactory.openSession();
      final String hql =
          "from BatchCampaignAssociationModel where campaignId =:cmpid and campaignType =:cmpType";
      // QC-1257 (change from Prelink to Targeted)
      list = session.createQuery(hql).setString("cmpid", campId).setString("cmpType", "T").list();

      if (list.size() > 0) {
        campIdExist = false;
        // session = null;
        // session = sessionFactory.openSession();
        final String uhql =
            "update BatchCampaignAssociationModel set campaignId = :cid, batchFileNo = :bfN, campaignStartDate = :csD, "
                + "campaignEndDate = :ceD, registrationStartDate = :rsD, registrationEndDate = :reD, "
                + "registrationRequired = :rReq, campaignType = :cT, commentsRequired = :cR, campaignDescription = :cD where id = :d";
        for (BatchCampaignAssociationModel b : list) {

          Query query = session.createQuery(uhql);
          query.setString("cid", batchCampaignAssociationModel.getCampaignId());
          query.setString("bfN", batchCampaignAssociationModel.getBatchFileNo());
          query.setDate("csD", batchCampaignAssociationModel.getCampaignStartDate());
          query.setDate("ceD", batchCampaignAssociationModel.getCampaignEndDate());
          query.setDate("rsD", batchCampaignAssociationModel.getRegistrationStartDate());
          query.setDate("reD", batchCampaignAssociationModel.getRegistrationEndDate());
          query.setParameter("rReq", batchCampaignAssociationModel.getRegistrationRequired());
          query.setString("cT", batchCampaignAssociationModel.getCampaignType());
          query.setParameter("cR", batchCampaignAssociationModel.getCommentsRequired());
          query.setString("cD", batchCampaignAssociationModel.getCampaignDescription());
          query.setInteger("d", b.getId());

          int rowCount = query.executeUpdate();
          LOGGER.info(
              rowCount > 0
                  ? "Update BatchCampaignAssociation table is successful with campaignid : "
                      + campId
                  : "Failed to update BatchCampaignAssociation table with campaignid : " + campId);
        }
      }
    } catch (HibernateException e) {
      LOGGER.error("Error in checking and updating campaign details with id : " + campId);
      throw e;
    } finally {
      // session.close();
      // batchCampaignAssociationModel = null;
    }
    return campIdExist;
  }
 @GET
 @Path("/projectsByDate")
 @Produces("application/json")
 public List<Projet> getProject_by_date(
     @QueryParam("start") Date start, @QueryParam("end") Date end) {
   Session session = manager.factory.openSession();
   Query query = session.createQuery("from Projet where debut>=:start and  fin<=:end");
   query.setDate(1, start);
   query.setDate(2, end);
   return query.list();
 }
Example #8
0
  @Override
  public List<Meet> getRealTimeLimit(Timestamp mstarttimes, Timestamp mendtimes) {
    Session session = this.getCurrentSessionFactory();

    String hql = "from Meet where mstarttime >= ? and mendtime<=?";

    Query query = session.createQuery(hql);

    query.setDate(0, mstarttimes);

    query.setDate(1, mendtimes);

    return query.list();
  }
 // 從比賽時間和隊名瞿德投注物件list
 public List<OddsEntity> findOddsByTimeAndTeamName(LocalDate gameTime, String teamName) {
   LocalDate newGameTime = gameTime.plusDays(1); // add one day to the gameTime
   Query query =
       getSession()
           .createQuery(
               "select game.odds from GameEntity as game where game.gameTime >= :gameTime and game.gameTime < :newGameTime and (game.teamHome.teamName = :teamName or game.teamAway.teamName = :teamName) ");
   query.setDate("gameTime", gameTime.toDate());
   query.setDate("newGameTime", newGameTime.toDate());
   //		if(teamName==null){
   //			teamName="";
   //		}
   query.setString("teamName", teamName);
   return query.list();
 }
Example #10
0
  @Override
  public List<Meet> getAppointTimeLimit(Timestamp appointstarts, Timestamp appointends) {
    Session session = this.getCurrentSessionFactory();

    String hql = "from Meet where appointtime between ? and ?";

    Query query = session.createQuery(hql);

    query.setDate(0, appointstarts);

    query.setDate(1, appointends);

    return query.list();
  }
 @SuppressWarnings({"unchecked", "rawtypes"})
 public ArrayList<Order> findOrderForStore(String storeId, Date startDate, Date endDate) {
   session = baseDao.getNewSession();
   Query query =
       session.createQuery(
           "select o from Order o where o.orderStore.storeId=?"
               + " and o.orderState=? and o.orderTime>=? and o.orderTime<?");
   query.setString(0, storeId);
   query.setInteger(1, FormulationNumber.orderPaid);
   query.setDate(2, startDate);
   query.setDate(3, endDate);
   List list = query.list();
   return (ArrayList<Order>) list;
 }
  @Override
  public List<SalesRecord> getSalesRecordByCoacherAndDateRange(
      String cocaher, Date startTime, Date endTime) {

    String hql =
        "from SalesRecord as salesRecord where salesRecord.Coacher = :coacher and salesRecord.SalesTime between :startTime and :endTime";
    Query query = sessionFactory.openSession().createQuery(hql);

    query.setString("coacher", cocaher);
    query.setDate("startTime", startTime);
    query.setDate("endTime", endTime);
    List<SalesRecord> list = query.list();

    return list;
  }
Example #13
0
  /** 根据板块位置来确定同一时间同一板块位置的资讯数量,获得 */
  @SuppressWarnings("unchecked")
  @Override
  public List<Date> getRepeatDate(String location, List<Date> dates) {
    List<Date> reapeatableDates = new ArrayList<Date>();
    List<DatePos> records = new ArrayList<DatePos>();

    for (Date date : dates) {
      System.out.println("date before");
      Query query = getSession().getNamedQuery("DatePos.getRepeatableDatePos");
      query.setParameter("location", location);
      query.setDate("date", date);
      records = (List<DatePos>) query.list();
      if (location.equals(ITableConstants.INFO_LOCATION_1)
          && records.size() >= ITableConstants.INFO_LOCATION_1_NUM) {
        reapeatableDates.add(date);
      } else if (location.equals(ITableConstants.INFO_LOCATION_2)
          && records.size() >= ITableConstants.INFO_LOCATION_2_NUM) {
        reapeatableDates.add(date);
      } else if (location.equals(ITableConstants.ADV_LOCATION_1)
          && records.size() >= ITableConstants.INFO_ADV_LOCATION_1_NUM) {
        reapeatableDates.add(date);
      }

      System.out.println("date after");
    }

    getSession().clear();

    return reapeatableDates;
  }
Example #14
0
  public static boolean needsHighlight(Topic t, User u) {
    boolean highlight = true;
    Transaction tx = null;
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    try {
      tx = session.getTransaction();
      Query q =
          session.createQuery(
              "select ls "
                  + "  from LastseenTopic as ls "
                  + " where ls.userID = :userID "
                  + "   and ls.topicID = :topicID "
                  + "   and ls.lasttime > :lastedit");
      q.setInteger("userID", u.getId());
      q.setInteger("topicID", t.getTopicID());
      q.setDate("lastedit", t.getLastedit());
      List<LastseenTopic> lsts = q.list();
      if (lsts.size() > 0) {
        highlight = lsts.get(0).getLasttime().before(t.getLastedit());
      }

    } catch (HibernateException e) {
      e.printStackTrace();
      if (tx != null && tx.isActive()) tx.rollback();
    }
    return highlight;
  }
 @Override
 @Transactional(readOnly = true)
 public List<CommentaryAlert> findCommentaryAlertByIdDate(Date date) {
   String queryString =
       "FROM CommentaryAlert c WHERE c.dateCommentaryAlert = :dateCommentaryAlert";
   Query query = this.sessionFactory.getCurrentSession().createQuery(queryString);
   query.setDate("idCommentary", date);
   return query.list();
 }
  public List<GameEntity> findGameByTimeAndName(LocalDate gameTime, String teamName) {
    LocalDate newGameTime = gameTime.plusDays(1); // add one day to the gameTime
    Query query =
        getSession()
            .createQuery(
                "from GameEntity as game where game.gameTime >= :gameTime and game.gameTime < :newGameTime and (game.teamHome.teamName = :teamName or game.teamAway.teamName = :teamName)");
    query.setDate("gameTime", gameTime.toDate());
    query.setDate("newGameTime", newGameTime.toDate());
    query.setString("teamName", teamName);
    return query.list();
    /*
    Criteria criteria = session.createCriteria(GameEntity.class);
    criteria.createAlias("teamAway", "away");
    criteria.createAlias("teamHome", "home");

    Criterion date = Restrictions.between("gameTime", gameTime, gameTime.plusDays(1));
    Criterion away = Restrictions.like("away.teamName", teamName);
    Criterion home = Restrictions.like("home.teamName", teamName);
    Criterion team = Restrictions.or(away, home);
    return criteria.add(date).add(team).list();
    */
  }
Example #17
0
  @Override
  public List<Meet> getByTime() {
    Timestamp starttime = new Timestamp(System.currentTimeMillis());

    Date now = new Date();

    String str1 = DateFormat.getDateInstance().format(now);

    Timestamp zonetime = Timestamp.valueOf(str1 + " 23:59:59");

    Date date = new Date(zonetime.getTime());

    DateFormat Calender;

    Calendar cal = Calendar.getInstance();

    cal.setTime(date);

    cal.add(Calendar.DAY_OF_MONTH, 7);

    Date date1 = cal.getTime();

    Timestamp endtime = new Timestamp(date1.getTime());

    Session session = this.getCurrentSessionFactory();

    String hql = "from Meet where mstate=1 and mstarttime between ? and ?";

    Query query = session.createQuery(hql);

    query.setDate(0, starttime);

    query.setDate(1, endtime);

    return query.list();
  }
  @Override
  public SalesRecord findSalesRecordByCustomer(String customer, Date startTime, String classType) {
    String hql =
        "from SalesRecord as salesRecord where salesRecord.Customer = :customer and salesRecord.ClassType = :classType and salesRecord.StartDate<:startTime order by salesRecord.StartDate desc";
    Query query = sessionFactory.openSession().createQuery(hql);

    query.setString("customer", customer);
    query.setDate("startTime", startTime);
    query.setString("classType", classType);
    List<SalesRecord> list = query.list();

    if (list.size() > 0) {
      return list.get(0);
    } else {
      return null;
    }
  }
Example #19
0
 /** {@inheritDoc} */
 public TypedQuery<X> setParameter(String name, Date value, TemporalType temporalType) {
   try {
     if (temporalType == DATE) {
       query.setDate(name, value);
     } else if (temporalType == TIME) {
       query.setTime(name, value);
     } else if (temporalType == TIMESTAMP) {
       query.setTimestamp(name, value);
     }
     registerParameterBinding(getParameter(name), value);
     return this;
   } catch (QueryParameterException e) {
     throw new IllegalArgumentException(e);
   } catch (HibernateException he) {
     throw getEntityManager().convert(he);
   }
 }
Example #20
0
  public List<Contrato> pesquisa(Date date) {
    List<Contrato> contratos = new ArrayList<Contrato>();
    try {
      String hql =
          " from Contrato c"
              + " where cast(c.dataCadastro as date) = :data"
              + " and c.dataCancelamento is null";

      Query query = createQuery(hql);
      query.setDate("data", date);

      contratos.addAll(listFromQuery(query));

    } catch (Exception e) {
      e.printStackTrace();
    }
    return contratos;
  }
 @Override
 public Category getCategoryForBoundaryAndDate(Boundary bndry, Date date) {
   Category category = null;
   Query qry = null;
   if (bndry != null && date != null) {
     qry =
         getCurrentSession()
             .createQuery(
                 "select C from Category C inner join C.catBoundaries BC where BC.bndry = :bndry AND ("
                     + "(BC.toDate IS NULL AND BC.fromDate <= :date) "
                     + "OR "
                     + "(BC.fromDate <= :date AND BC.toDate >= :date)) ");
     qry.setEntity(BOUNDARY, bndry);
     qry.setDate("date", date);
     if (qry.list().size() == 1) category = (Category) qry.uniqueResult();
   }
   return category;
 }
 @Override
 public BoundaryCategory getBoundaryCategoryByBoundryAndDate(Boundary bndry, Date date) {
   BoundaryCategory bndryCategory = null;
   Query qry = null;
   if (bndry != null && date != null) {
     qry =
         getCurrentSession()
             .createQuery(
                 "from BoundaryCategory BC where BC.bndry = :bndry AND ("
                     + "(BC.toDate IS NULL AND BC.fromDate <= :date) "
                     + "OR "
                     + "(BC.fromDate <= :date AND BC.toDate >= :date)) ");
     qry.setEntity(BOUNDARY, bndry);
     qry.setDate("date", date);
     if (qry.list().size() == 1) bndryCategory = (BoundaryCategory) qry.uniqueResult();
   }
   return bndryCategory;
 }
 @Override
 public Category getCategoryByBoundryAndUsage(Boundary bndry, PropertyUsage propertyUsage) {
   Category category = null;
   Query qry = null;
   if (bndry != null && propertyUsage != null) {
     qry =
         getCurrentSession()
             .createQuery(
                 "select C from Category C inner join C.catBoundaries BC where BC.bndry = :bndry and C.propUsage = :propertyUsage AND ("
                     + "(BC.toDate IS NULL AND BC.fromDate <= :currDate) "
                     + "OR "
                     + "(BC.fromDate <= :currDate AND BC.toDate >= :currDate)) ");
     qry.setEntity(BOUNDARY, bndry);
     qry.setEntity("propertyUsage", propertyUsage);
     qry.setDate("currDate", new Date());
     if (qry.list().size() == 1) category = (Category) qry.uniqueResult();
   }
   return category;
 }
Example #24
0
  public List<Contrato> pesquisa(Cliente cliente, Date date) {
    List<Contrato> contratos = new ArrayList<Contrato>();
    try {
      String hql =
          " from Contrato c"
              + " where c.cliente = :cliente "
              + " and :data between c.dataInicio and c.dataTermino"
              + " and c.dataCancelamento is null";

      Query query = createQuery(hql);
      query.setParameter("cliente", cliente);
      query.setDate("data", date);

      contratos.addAll(listFromQuery(query));

    } catch (Exception e) {
      e.printStackTrace();
    }
    return contratos;
  }
Example #25
0
 public static Hashtable<Long, Set<Long>> findConflictingStudents(
     Long classId, int startSlot, int length, Vector<Date> dates) {
   Hashtable<Long, Set<Long>> table = new Hashtable();
   if (dates.isEmpty()) return table;
   String datesStr = "";
   for (int i = 0; i < dates.size(); i++) {
     if (i > 0) datesStr += ", ";
     datesStr += ":date" + i;
   }
   Query q =
       LocationDAO.getInstance()
           .getSession()
           .createQuery(
               "select distinct e.clazz.uniqueId, e.student.uniqueId "
                   + "from StudentClassEnrollment e, ClassEvent c inner join c.meetings m, StudentClassEnrollment x "
                   + "where x.clazz.uniqueId=:classId and x.student=e.student and "
                   + // only look among students of the given class
                   "e.clazz=c.clazz and "
                   + // link ClassEvent c with StudentClassEnrollment e
                   "m.stopPeriod>:startSlot and :endSlot>m.startPeriod and "
                   + // meeting time within given time period
                   "m.meetingDate in ("
                   + datesStr
                   + ")")
           .setLong("classId", classId)
           .setInteger("startSlot", startSlot)
           .setInteger("endSlot", startSlot + length);
   for (int i = 0; i < dates.size(); i++) {
     q.setDate("date" + i, dates.elementAt(i));
   }
   for (Iterator i = q.setCacheable(true).list().iterator(); i.hasNext(); ) {
     Object[] o = (Object[]) i.next();
     Set<Long> set = table.get((Long) o[0]);
     if (set == null) {
       set = new HashSet<Long>();
       table.put((Long) o[0], set);
     }
     set.add((Long) o[1]);
   }
   return table;
 }
Example #26
0
 /** {@inheritDoc} */
 public TypedQuery<X> setParameter(int position, Date value, TemporalType temporalType) {
   try {
     if (isJpaPositionalParameter(position)) {
       String name = Integer.toString(position);
       this.setParameter(name, value, temporalType);
     } else {
       if (temporalType == DATE) {
         query.setDate(position - 1, value);
       } else if (temporalType == TIME) {
         query.setTime(position - 1, value);
       } else if (temporalType == TIMESTAMP) {
         query.setTimestamp(position - 1, value);
       }
       registerParameterBinding(getParameter(position), value);
     }
     return this;
   } catch (QueryParameterException e) {
     throw new IllegalArgumentException(e);
   } catch (HibernateException he) {
     throw getEntityManager().convert(he);
   }
 }
  public List<GameEntity> findComplex(
      Long gameNum,
      String teamName,
      Long gameStatusMin,
      Long gameStatusMax,
      LocalDate timeBegin,
      LocalDate timeEnd,
      String ballType,
      String leagueName) {

    String sql = "from GameEntity games where 1=1";
    String sql1 = " and games.gameNum = :gameNum";
    String sql2 = " and games.gameStatus >= :gameStatusMin";
    String sql3 = " and games.gameStatus <= :gameStatusMax";
    String sql4 =
        " and (games.teamAway.teamName like :teamName or games.teamHome.teamName like :teamName)";
    String sql5 = " and games.gameTime >= :timeBegin";
    String sql6 = " and games.gameTime < :timeEnd";
    String sql7 = " and games.ballType like :ballType";
    String sql8 =
        " and (games.teamAway.leagueName like :leagueName or games.teamHome.leagueName like :leagueName)";
    String sql99 = " order by games.gameTime";

    boolean hasGameNum = false;
    if (gameNum != null) {
      sql += sql1;
      hasGameNum = true;
    }
    boolean hasGameStatusMin = false;
    if (gameStatusMin != null) {
      sql += sql2;
      hasGameStatusMin = true;
    }
    boolean hasGameStatusMax = false;
    if (gameStatusMax != null) {
      sql += sql3;
      hasGameStatusMax = true;
    }
    boolean hasTeamName = false;
    if (teamName != null) {
      sql += sql4;
      hasTeamName = true;
    }
    boolean hasTimeBegin = false;
    if (timeBegin != null) {
      sql += sql5;
      hasTimeBegin = true;
    }
    boolean hasTimeEnd = false;
    if (timeEnd != null) {
      sql += sql6;
      hasTimeEnd = true;
    }
    boolean hasBallType = false;
    if (ballType != null) {
      sql += sql7;
      hasBallType = true;
    }
    boolean hasLeagueName = false;
    if (leagueName != null) {
      sql += sql8;
      hasLeagueName = true;
    }
    sql += sql99;

    System.out.println(sql);

    Query query = getSession().createQuery(sql);

    if (hasGameNum) {
      query.setLong("gameNum", gameNum);
    }
    if (hasTeamName) {
      query.setString("teamName", "%" + teamName + "%");
    }
    if (hasGameStatusMin) {
      query.setLong("gameStatusMin", gameStatusMin);
    }
    if (hasGameStatusMax) {
      query.setLong("gameStatusMax", gameStatusMax);
    }
    if (hasTimeBegin) {
      query.setDate("timeBegin", timeBegin.toDate());
    }
    if (hasTimeEnd) {
      query.setDate("timeEnd", timeEnd.toDate());
    }
    if (hasBallType) {
      query.setString("ballType", "%" + ballType + "%");
    }
    if (hasLeagueName) {
      query.setString("leagueName", "%" + leagueName + "%");
    }

    return query.list();
  }
 public List<GameEntity> findGameTime(Date gameTime) {
   Query query =
       getSession().createQuery("from GameEntity where GAME_TIME = :gameTime order by GAME_TIME");
   return query.setDate("gameTime", gameTime).list();
 }
  public List<GameEntity> findForHistory(LocalDate timeFrom, LocalDate timeTo, String teamName) {
    //// newer method user crirteria
    //
    //		Criteria criteria = getSession().createCriteria(GameEntity.class);
    //		criteria.createAlias("teamAway", "away");
    //		criteria.createAlias("teamHome", "home");
    //
    //		Criterion date = Restrictions.between("gameTime", timeFrom, timeTo.plusDays(1));
    //		Criterion away = Restrictions.like("away.teamName", teamName);
    //		Criterion home = Restrictions.like("home.teamName", teamName);
    //		Criterion team = Restrictions.or(away, home);
    //		return criteria.add(date).add(team).list();
    //

    // .......older method using HQL	..............................................
    // 設定sql字串
    // HQL的帶入變數為 timeFrom, timeTo, teamName
    //		String sql = "from GameEntity games where games.isEnd = 't'";//查詢比賽是否已經結束, 要找已經結束
    String sql = "from GameEntity games where 1=1";
    String sql1 = " and games.gameTime > :timeFrom"; // 搜尋大於 timeFrom的時間
    String sql2 = " and games.gameTime <= :timeTo"; // 搜尋小於 timeTo的時間
    String sql3 =
        " and (games.teamAway.teamName like :teamName or games.teamHome.teamName like :teamName)"; // 搜訊teamName符合部分字串
    String sql4 = " order by games.gameTime";

    // 判斷是否有timeFrom 如有則加入sql1的敘述
    boolean hasTimeFrom = false;
    if (timeFrom != null) {
      sql += sql1;
      hasTimeFrom = true;
    }

    // 判斷是否有timeTo 如有則加入sql2的敘述
    boolean hasTimeTo = false;
    if (timeTo != null) {
      sql += sql2;
      hasTimeTo = true;
    }

    // 判斷是否有teamName 如有則加入sql3的敘述
    boolean hasTeamName = false;
    if (teamName != null) {
      sql += sql3;
      hasTeamName = true;
    }

    // 帶入排序方式
    sql += sql4;

    Query query = getSession().createQuery(sql);

    // 根據加入的sql敘述帶入變數
    if (hasTimeFrom) {
      query.setDate("timeFrom", timeFrom.toDate()); // sql沒有支援LocalDateTime, 但有支援Date, 所以轉成Date
    }
    if (hasTimeTo) {
      //			query.setDate("timeTo", timeTo.toDate());
      query.setDate("timeTo", timeTo.plusDays(1).toDate());
    }
    if (hasTeamName) {
      query.setString(
          "teamName", teamName + "%"); // 因為使用 LIKE讓我們依據一個模式 (pattern) 來找出我們要的資料, 所以要搭配%這樣的萬用字元
    }

    return query.list();
  }
  /** Test of deleteByDateAndCode method, of class ChecksumHistoryDAOImpl. */
  @Test
  public void testDeleteByDateAndCode() throws Exception {
    System.out.println("deleteByDateAndCode");

    GregorianCalendar cal = new GregorianCalendar();
    Date retentionDate = cal.getTime();
    ChecksumResultCode resultCode = ChecksumResultCode.CHECKSUM_MATCH;

    // Create two older rows
    HibernateDBConnection dbc = (HibernateDBConnection) CoreHelpers.getDBConnection(context);
    Query qry =
        dbc.getSession()
            .createSQLQuery(
                "INSERT INTO checksum_history"
                    + "(check_id, process_end_date, result, bitstream_id)"
                    + " VALUES (:id, :date, :result, :bitstream)");
    int checkId = 0;

    // Row with matching result code
    BitstreamService bss = ContentServiceFactory.getInstance().getBitstreamService();
    InputStream is = new ByteArrayInputStream(new byte[0]);
    Bitstream bs = bss.create(context, is);
    context.turnOffAuthorisationSystem();
    bss.update(context, bs);
    context.restoreAuthSystemState();

    cal.roll(Calendar.DATE, -1);
    Date matchDate = cal.getTime();
    checkId++;
    qry.setInteger("id", checkId);
    qry.setDate("date", matchDate);
    qry.setString("result", ChecksumResultCode.CHECKSUM_MATCH.name());
    qry.setString("bitstream", bs.getID().toString()); // FIXME identifier not being set???
    qry.executeUpdate();

    // Row with nonmatching result code
    cal.roll(Calendar.DATE, -1);
    Date noMatchDate = cal.getTime();
    checkId++;
    qry.setInteger("id", checkId);
    qry.setDate("date", noMatchDate);
    qry.setString("result", ChecksumResultCode.CHECKSUM_NO_MATCH.name());
    qry.setString("bitstream", bs.getID().toString());
    qry.executeUpdate();

    // Create one newer row
    cal.roll(Calendar.DATE, +3);
    Date futureDate = cal.getTime();
    checkId++;
    qry.setInteger("id", checkId);
    qry.setDate("date", new java.sql.Date(futureDate.getTime()));
    qry.setString("result", ChecksumResultCode.CHECKSUM_MATCH.name());
    qry.setString("bitstream", bs.getID().toString());
    qry.executeUpdate();

    // Test!
    ChecksumHistoryDAOImpl instance = new ChecksumHistoryDAOImpl();
    int expResult = 1;
    int result = instance.deleteByDateAndCode(context, retentionDate, resultCode);
    assertEquals(expResult, result);

    // See if matching old row is gone.
    qry =
        dbc.getSession()
            .createQuery("SELECT COUNT(*) FROM ChecksumHistory WHERE process_end_date = :date");
    long count;

    qry.setDate("date", matchDate);
    count = (Long) qry.uniqueResult();
    assertEquals("Should find no row at matchDate", count, 0);

    // See if nonmatching old row is still present.
    qry.setDate("date", noMatchDate);
    count = (Long) qry.uniqueResult();
    assertEquals("Should find one row at noMatchDate", count, 1);

    // See if new row is still present.
    qry.setDate("date", futureDate);
    count = (Long) qry.uniqueResult();
    assertEquals("Should find one row at futureDate", count, 1);
  }