// 默认顺序createtime desc
public List<T> findByMapWithCond(Map<String, Object> params, String orderBy, Boolean asc) {
StringBuilder hql = new StringBuilder("from " + className);
String orderStr;
if (null == orderBy) {
orderStr = " order by createtime desc";
} else {
String ascStr = asc ? " asc" : " desc";
orderStr = " order by " + orderBy + ascStr;
}
Map<String, Object> alterParams = new HashMap<>();
Query query;
if (null != params && !params.isEmpty()) {
hql.append(" where ");
for (String field : params.keySet()) {
int i = field.indexOf(" ");
Assert.isTrue(i != -1, "Wrong condition, must have space inside!");
String ramdonName = "_" + Utils.getRandomString(8);
hql.append(field).append(" :").append(ramdonName).append(" and ");
if (field.contains("like")) {
String likeStr = "%" + params.get(field) + "%";
alterParams.put(ramdonName, likeStr);
} else alterParams.put(ramdonName, params.get(field));
}
hql.append("1=1");
query = getSession().createQuery(hql.append(orderStr).toString());
for (String field : alterParams.keySet()) query.setParameter(field, alterParams.get(field));
} else query = getSession().createQuery(hql.append(orderStr).toString());
return query.list();
}
public List<User> getUserSearchResults(int topicId) {
// TODO Auto-generated method stub
List<User> userArr = new ArrayList<User>();
Session session = sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query1 = session.createQuery("select userId from UserTopic where topicId = :topicId");
query1.setString("topicId", String.valueOf(topicId));
Query query2 = session.createQuery("from User where userId in (:userList)");
query2.setParameterList("userList", query1.list());
userArr = query2.list();
/*for(Topic topic : topicArr){
System.out.println("Topic Description----"+topic.getTopicDescription());
System.out.println("Topic Id----"+topic.getTopicId());
}*/
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return userArr;
}
private void accedi(HttpServletRequest request, HttpServletResponse response) {
Query q = hsession.createQuery("FROM Anno ORDER BY anno");
request.setAttribute("listaanni", q.list());
nextview = "/bilancio/home.jsp";
}
/**
* Returns a list of all active and finished one time orders going back n number of months,
* containing the given item id for the given user.
*
* @param userId user id of orders
* @param itemId item id of order lines
* @param months previous number of months to include (1 = this month plus the previous)
* @return list of found one-time orders, empty list if none found
*/
@SuppressWarnings("unchecked")
public List<OrderLineDTO> findOnetimeByUserItem(Integer userId, Integer itemId, Integer months) {
final String hql =
"select line "
+ " from OrderLineDTO line "
+ "where line.deleted = 0 "
+ " and line.item.id = :itemId "
+ " and line.purchaseOrder.baseUserByUserId.id = :userId "
+ " and line.purchaseOrder.orderPeriod.id = :period "
+ " and (line.purchaseOrder.orderStatus.orderStatusFlag = :active_status"
+ " or line.purchaseOrder.orderStatus.orderStatusFlag = :finished_status)"
+ " and line.purchaseOrder.deleted = 0 "
+ " and line.purchaseOrder.createDate > :startdate";
Query query = getSession().createQuery(hql);
query.setParameter("itemId", itemId);
query.setParameter("userId", userId);
query.setParameter("period", Constants.ORDER_PERIOD_ONCE);
query.setParameter("active_status", OrderStatusFlag.INVOICE.ordinal());
query.setParameter("finished_status", OrderStatusFlag.FINISHED.ordinal());
DateMidnight startdate = new DateMidnight().minusMonths(months);
query.setParameter("startdate", startdate.toDate());
return query.list();
}
public List<TbAplicativo> datbAplicativos() {
sesion = sessionFactory.openSession();
Query query = sesion.getNamedQuery("TbAplicativo.findAll");
List<TbAplicativo> tbAplicativo = query.list();
sesion.close();
return tbAplicativo;
}
@Override
public ArrayList<User> getAllUsers() throws Exception {
dLog.info("Entering method getAllUsers");
ArrayList<User> result = new ArrayList<User>();
Session session = null;
try {
session = getSession();
session.clear();
Transaction tranx = session.beginTransaction();
String hql = "select from User";
Query query = session.createQuery(hql);
List<?> list = query.list();
for (int n = 0; n < list.size(); n++) {
result.add((User) list.get(n));
}
tranx.commit();
session.flush();
session.evict(User.class);
} catch (Exception e) {
dLog.error("Exception in getAllUsers", e);
} finally {
// ensure that session is close regardless of the errors in try/catch
if (session != null) {
session.close();
}
}
return result;
}
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;
}
@Override
public List<Reply> getRepliesToPost(int postId) {
// TODO Auto-generated method stub
Session session = sessionFactory.openSession();
Transaction tx = null;
List<Reply> replyArr = new ArrayList<Reply>();
try {
tx = session.beginTransaction();
Query query =
session.createQuery("from Reply where postId = :postId order by createdDate desc");
query.setParameter("postId", postId);
replyArr = query.list();
/* for(Reply reply : replyArr){
System.out.println("Reply - id----"+reply.getReplyId());
System.out.println("Reply - Description----"+reply.getDescription());
System.out.println("Reply - Post id----"+reply.getPostId());
}*/
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return replyArr;
}
@Override
public List<Forum> getForumsOfaUser(int userId) {
// TODO Auto-generated method stub
List<Forum> forums = new ArrayList<Forum>();
Session session = sessionFactory.openSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
Query query = session.createQuery("from Subscription where userId = :userId");
query.setString("userId", String.valueOf(userId));
List<Subscription> userSubscribedGroups = query.list();
for (Subscription subsc : userSubscribedGroups) {
query = session.createQuery("from Forum where forumId = :forumId");
query.setParameter("forumId", subsc.getForumId());
// add this to a list variable query.uniqueResult();
forums.add((Forum) query.uniqueResult());
}
} catch (HibernateException e) {
if (tx != null) {
tx.rollback();
e.printStackTrace();
}
} finally {
session.close();
}
return forums;
}
public List<T> namedQueryListResult(
String namedQuery, int maxSize, Map<String, Object> parameters) {
Session session = openSession(true);
List<T> results = Lists.newArrayList();
try {
Query query = namedQuery(namedQuery);
for (Map.Entry<String, Object> entry : parameters.entrySet()) {
query.setParameter(entry.getKey(), entry.getValue());
}
if (maxSize != -1) {
query.setMaxResults(maxSize);
}
results = query.list();
for (T result : results) {
if (result != null) {
result.init();
}
}
commitTransaction(session, true);
} catch (Exception e) {
rollbackTransaction(session, true);
} finally {
closeSession(session);
}
return results;
}
/**
* Returns a list of all active and finished one-time orders going back n number of months, for
* all direct immediate of the given parent user id. This is useful for determining usage across
* all child users.
*
* @param parentUserId parent user id
* @param itemId item id of order lines
* @param months previous number of months to include (1 = 1 month period starting from today)
* @return list of found one-time orders, empty list if none found
*/
@SuppressWarnings("unchecked")
public List<OrderLineDTO> findOnetimeByParentUserItem(
Integer parentUserId, Integer itemId, Integer months) {
UserDTO parent = new UserBL(parentUserId).getEntity();
if (parent == null || parent.getCustomer() == null) {
LOG.warn("Parent user " + parentUserId + " does not exist or is not a customer!");
return Collections.emptyList();
}
final String hql =
"select line "
+ " from OrderLineDTO line "
+ " where line.deleted = 0 "
+ " and line.item.id = :itemId "
+ " and line.purchaseOrder.baseUserByUserId.customer.parent.id = :parentId"
+ " and line.purchaseOrder.orderPeriod.id = :period "
+ " and (line.purchaseOrder.orderStatus.orderStatusFlag = :active_status"
+ " or line.purchaseOrder.orderStatus.orderStatusFlag = :finished_status)"
+ " and line.purchaseOrder.deleted = 0 "
+ " and line.purchaseOrder.createDate > :startdate ";
Query query = getSession().createQuery(hql);
query.setParameter("itemId", itemId);
query.setParameter("parentId", parent.getCustomer().getId());
query.setParameter("period", Constants.ORDER_PERIOD_ONCE);
query.setParameter("active_status", OrderStatusFlag.INVOICE.ordinal());
query.setParameter("finished_status", OrderStatusFlag.FINISHED);
DateMidnight startdate = new DateMidnight().minusMonths(months);
query.setParameter("startdate", startdate.toDate());
return query.list();
}
public List<BlacklistDTO> filterByCcNumbers(Integer entityId, Collection<String> rawNumbers) {
Query query =
getSession()
.createSQLQuery(queryFilterByCCNumber)
.setParameterList("rawNumbers", rawNumbers)
.setParameter("companyId", entityId);
return query.list();
}
public List<Login> findLoginByDomain(String domain) {
Session session = sessionFactory.getCurrentSession();
Query qry =
session.createQuery(
"from org.openiam.idm.srvc.auth.dto.Login l " + " where l.id.domainId = :domain ");
qry.setString("domain", domain);
return (List<Login>) qry.list();
}
/* (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<Login> findAllLoginByManagedSys(String managedSysId) {
Session session = sessionFactory.getCurrentSession();
Query qry =
session.createQuery(
"from org.openiam.idm.srvc.auth.dto.Login l "
+ " where l.id.managedSysId = :managedSysId order by l.id.login asc ");
qry.setString("managedSysId", managedSysId);
return (List<Login>) qry.list();
}
@Override
public <T> List<T> executeSQLQueryWithParams(String queryString, HashMap<String, Object> params) {
validateTransaction();
Query query = getSession().createSQLQuery(queryString);
for (String name : params.keySet()) {
query.setParameter(name, params.get(name));
}
return query.list();
}
/**
* Get active Payment Allocation of Payment
*
* @param parent payment
* @return array of allocations
*/
public static MPaymentAllocate[] get(I_C_Payment parent) {
final Properties ctx = InterfaceWrapperHelper.getCtx(parent);
final String trxName = InterfaceWrapperHelper.getTrxName(parent);
final String whereClause = "C_Payment_ID=? AND IsActive=?";
Query query = MTable.get(ctx, Table_ID).createQuery(whereClause, trxName);
query.setParameters(new Object[] {parent.getC_Payment_ID(), "Y"});
List<MPaymentAllocate> list = query.list();
return list.toArray(new MPaymentAllocate[list.size()]);
} // get
@Override
public <T> List<T> executeNamedQueryWithParams(
Class<T> categoryEntityClass, String getByLink, HashMap<String, Object> params) {
validateTransaction();
Query query = getSession().getNamedQuery(getByLink);
for (String name : params.keySet()) {
query.setParameter(name, params.get(name));
}
return query.list();
}
public List<Login> findLockedUsers(Date startTime) {
Session session = sessionFactory.getCurrentSession();
Query qry =
session.createQuery(
"from org.openiam.idm.srvc.auth.dto.Login l "
+ " where l.isLocked = 1 and "
+ " l.lastAuthAttempt >= :startTime ");
qry.setTimestamp("startTime", startTime);
return (List<Login>) qry.list();
}
public <T> List<T> executeQuery(String query, HashMap<String, Object> params) {
validateTransaction();
Query hql = getSession().createQuery(query);
if (params != null) {
for (String name : params.keySet()) {
hql.setParameter(name, params.get(name));
}
}
return hql.list();
}
@Test
public void simpleQuery() throws IOException {
ServiceLocator locator = container;
NextRepository repository = locator.resolve(NextRepository.class);
repository.insert(new Next());
List<Next> search = repository.search();
Query<Next> stream = repository.query();
List<Next> list = stream.list();
Assert.assertEquals(search.size(), list.size());
}
public List<T> findInCollectionByPage(
final String hql, final List value, final int offset, final int pageSize)
throws HibernateException {
Query query = getSession().createQuery(hql);
if (null != value) {
query.setParameterList("paraList", value);
}
if (offset > 1) return query.setFirstResult(offset).setMaxResults(pageSize).list();
else return query.list();
}
@Override
public <T extends GettableById> List<T> get(Class<T> tClass, int offset, int limit) {
validateTransaction();
String fullClassName = tClass.getName();
fullClassName =
fullClassName.substring(fullClassName.lastIndexOf(".") + 1, fullClassName.length());
Query query = getSession().createQuery("from " + fullClassName);
if (offset > 0) query.setFirstResult(offset);
if (limit > 0) query.setMaxResults(limit);
return query.list();
}
/**
* Get Allocations of Cash
*
* @param ctx context
* @param C_Cash_ID Cash ID
* @return allocations of payment
* @param trxName transaction
*/
public static MAllocationHdr[] getOfCash(Properties ctx, int C_Cash_ID, String trxName) {
String whereClause =
"IsActive='Y'"
+ " AND EXISTS (SELECT 1 FROM C_CashLine cl, C_AllocationLine al "
+ "where cl.C_Cash_ID=? and al.C_CashLine_ID=cl.C_CashLine_ID "
+ "and C_AllocationHdr.C_AllocationHdr_ID=al.C_AllocationHdr_ID)";
Query query = MTable.get(ctx, MAllocationHdr.Table_ID).createQuery(whereClause, trxName);
query.setParameters(new Object[] {C_Cash_ID});
List<MAllocationHdr> list = query.list();
MAllocationHdr[] retValue = new MAllocationHdr[list.size()];
list.toArray(retValue);
return retValue;
} // getOfCash
public List<Login> findLoginByManagedSys(String principalName, String managedSysId) {
Session session = sessionFactory.getCurrentSession();
Query qry =
session.createQuery(
"from org.openiam.idm.srvc.auth.dto.Login l "
+ " where "
+ " l.id.managedSysId = :managedSys and "
+ " l.id.login = :login ");
qry.setString("managedSys", managedSysId);
qry.setString("login", principalName);
return (List<Login>) qry.list();
}
@Override
public <T> List<T> executeNamedQueryWithParams(
String queryName, HashMap<String, Object> params, int offset, int limit) {
validateTransaction();
Query query = getSession().getNamedQuery(queryName);
if (params != null) {
for (String name : params.keySet()) {
query.setParameter(name, params.get(name));
}
}
query.setFirstResult(offset);
if (limit > 0) query.setMaxResults(limit);
return query.list();
}
@Override
public List<RoomVO> getAll() {
List<RoomVO> list = null;
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
try {
session.beginTransaction();
Query query = session.createQuery(GET_ALL_STMT);
list = query.list();
session.getTransaction().commit();
} catch (RuntimeException ex) {
session.getTransaction().rollback();
throw ex;
}
return list;
}
@SuppressWarnings("unchecked")
public List<OrderLineDTO> findByUserItem(Integer userId, Integer itemId) {
final String hql =
"select ol"
+ " from OrderLineDTO ol "
+ " where ol.deleted = 0 "
+ " and ol.item.id = :item "
+ " and ol.purchaseOrder.baseUserByUserId.id = :user";
Query query = getSession().createQuery(hql);
query.setParameter("item", itemId);
query.setParameter("user", userId);
return query.list();
}
/**
* Get active indexes from table
*
* @param table table
* @return array of table index
*/
public static MTableIndex[] get(MTable table) {
Query query =
new Query(
table.getCtx(),
MTableIndex.Table_Name,
MTableIndex.COLUMNNAME_AD_Table_ID + "=?",
table.get_TrxName());
query.setParameters(table.getAD_Table_ID());
query.setOnlyActiveRecords(true);
List<MTableIndex> list = query.<MTableIndex>list();
MTableIndex[] retValue = new MTableIndex[list.size()];
list.toArray(retValue);
return retValue;
}
/**
* Find order lines by user ID and where description is like the given string. This method can
* accept wildcard characters '%' for matching.
*
* @param userId user id
* @param like string to match against order line description
* @return list of found orders lines, empty if none
*/
@SuppressWarnings("unchecked")
public List<OrderLineDTO> findByDescriptionLike(Integer userId, String like) {
final String hql =
"select line "
+ " from OrderLineDTO line "
+ "where line.deleted = 0 "
+ " and line.purchaseOrder.deleted = 0 "
+ " and line.purchaseOrder.baseUserByUserId.id = :userId "
+ " and line.description like :description";
Query query = getSession().createQuery(hql);
query.setParameter("userId", userId);
query.setParameter("description", like);
return query.list();
}
