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(); }
@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(); }
@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(); }
@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; }
/** 根据板块位置来确定同一时间同一板块位置的资讯数量,获得 */ @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; }
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(); */ }
@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; } }
/** {@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); } }
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; }
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; }
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; }
/** {@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); }