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);
}