Example usage for org.hibernate SQLQuery list

Introduction

In this page you can find the example usage for org.hibernate SQLQuery list.

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.bitranger.parknshop.buyer.dao.PsNoticeCustomerDAO.java

License:Open Source License

@SuppressWarnings("unchecked")

public List<PsNoticeCustomer> getLatest(final int customerID, final int n) {

    return getHibernateTemplate().executeFind(new HibernateCallback<List<PsNoticeCustomer>>() {

        @Override//from   w  w w .  j  a v a 2 s.  c  o m
        public List<PsNoticeCustomer> doInHibernate(Session arg0) throws HibernateException, SQLException {

            SQLQuery q = arg0.createSQLQuery(" select ps_notice_customer as NC "
                    + " where NC.is_valid = 1 and id_customer = ? " + " order by time_created desc limit ?");
            q.setInteger(0, customerID);
            q.setInteger(1, n);
            q.addEntity(PsNoticeCustomer.class);
            return q.list();
        }

    });
}

From source file:com.bitranger.parknshop.common.ads.PsPromotItemDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<PsPromotItem> findBySeller(final int id) {
    return getHibernateTemplate().executeFind(new HibernateCallback<List<PsPromotItem>>() {

        @Override/*from  ww w . ja v a2 s  .c  o  m*/
        public List<PsPromotItem> doInHibernate(Session session) throws HibernateException, SQLException {
            SQLQuery q = session.createSQLQuery(" select IP.* from ps_promot_item as IP "
                    + " inner join ps_item as IT on IT.id = IP.id_item "
                    + " inner join ps_shop as SP on SP.id = IT.id_shop " + " where SP.id_seller = ? ");
            q.setInteger(0, id);
            q.addEntity(PsPromotItem.class);
            return q.list();
        }
    });
}

From source file:com.bitranger.parknshop.common.ads.PsPromotItemDAO.java

License:Open Source License

/**
 select PI.* from ps_promot_item as PI //  ww  w .  jav  a  2  s.co m
   inner join ps_ad_item as AD on AD.id_promot = PI.id
where AD.time_start < CURRENT_TIMESTAMP and CURRENT_TIMESTAMP < AD.time_end
        
 */
@SuppressWarnings("unchecked")
public List<PsPromotItem> findAllValid() {
    log.debug("finding all PsPromotItem instances");
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsPromotItem>>() {

            @Override
            public List<PsPromotItem> doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery q = session.createSQLQuery("      select PI.* from ps_promot_item as PI "
                        + "   inner join ps_ad_item as AD on AD.id_promot = PI.id "
                        + "   where AD.time_start < CURRENT_TIMESTAMP and CURRENT_TIMESTAMP < AD.time_end ");
                q.addEntity(PsPromotItem.class);
                return q.list();
            }
        });
    } catch (RuntimeException re) {
        log.error("find all failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.common.dao.impl.PsCommentDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//from  w  w  w . j av a2  s .c  o m
public List<PsComment> findByCustomerId(final Integer customerId, final FetchOption option) {
    log.debug("findByCustomerId: " + customerId);
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsComment>>() {

            @Override
            public List<PsComment> doInHibernate(Session arg0) throws HibernateException, SQLException {
                SQLQuery query = arg0.createSQLQuery("select * from ps_comment where id_customer = ? "
                        + "order by time_created "
                        + (option.sortOption == SortOption.ASCENDING ? " asc " : " desc ") + "limit ?, ?");
                query.setInteger(0, customerId);
                query.setInteger(1, option.offset);
                query.setInteger(2, option.limit);
                query.addEntity(PsComment.class);
                return query.list();
            }
        });
    } catch (RuntimeException re) {
        log.error("attach failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.common.dao.impl.PsCommentDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//from  w  ww. j  a v a 2s . c  o  m
public List<PsComment> findByItemId(final Integer itemId, final FetchOption option) {

    log.debug("findByItemId: " + itemId);
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsComment>>() {
            @Override
            public List<PsComment> doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery("select * from ps_comment where id_item = ? "
                        + " order by time_created "
                        + (option.sortOption == SortOption.ASCENDING ? " asc " : " desc ") + "limit ?, ?");

                query.setInteger(0, itemId);
                query.setInteger(1, option.offset);
                query.setInteger(2, option.limit);
                query.addEntity(PsComment.class);
                return query.list();
            }
        });

    } catch (RuntimeException re) {
        log.error("attach failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.common.dao.impl.PsItemDAO.java

License:Open Source License

/**
 select IT.*, count(IT.count_purchase) as CT from ps_item as IT
where IT.id_category = ?//from   w  ww  .j av  a  2  s  .  com
order by IT.time_created desc
limit 0, ?
        
 */
@SuppressWarnings("unchecked")
@Override
public List<PsItem> selectLatest(final int categoryID, final int limit) {
    return getHibernateTemplate().executeFind(new HibernateCallback<List<PsItem>>() {

        @Override
        public List<PsItem> doInHibernate(Session session) throws HibernateException, SQLException {

            SQLQuery query = session
                    .createSQLQuery(" select IT.*, count(IT.count_purchase) as CT from ps_item as IT "
                            + " where IT.id_category = ? " + " order by IT.time_created desc "
                            + " limit 0, ? ");
            query.addEntity(PsItem.class);
            query.setInteger(0, categoryID);
            query.setInteger(1, limit);
            return query.list();
        }
    });
}

From source file:com.bitranger.parknshop.common.dao.impl.PsItemDAO.java

License:Open Source License

/**
select IT.*, count(IT.count_purchase) as CT from ps_item as IT
where IT.id_category = ?// ww  w . j ava2 s  .com
order by CT desc
limit 0, ? 
 */
@SuppressWarnings("unchecked")
@Override
public List<PsItem> selectBestSellers(final int categoryID, final int limit) {

    return getHibernateTemplate().executeFind(new HibernateCallback<List<PsItem>>() {

        @Override
        public List<PsItem> doInHibernate(Session session) throws HibernateException, SQLException {

            SQLQuery query = session
                    .createSQLQuery(" select IT.*, count(IT.count_purchase) as CT from ps_item as IT "
                            + " where IT.id_category = ? " + " order by CT desc " + " limit 0, ? ");
            query.addEntity(PsItem.class);
            query.setInteger(0, categoryID);
            query.setInteger(1, limit);
            return query.list();
        }
    });
}

From source file:com.bitranger.parknshop.common.dao.impl.PsItemDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*ww w . j  a v  a 2  s .  c  o m*/
public List<PsItem> searchByKeyword(final String q) {

    return getHibernateTemplate().executeFind(new HibernateCallback<List<PsItem>>() {

        @Override
        public List<PsItem> doInHibernate(Session session) throws HibernateException, SQLException {

            SQLQuery query = session.createSQLQuery(" SELECT IT.*, "
                    + " MATCH (`name`, `introduction`) AGAINST (? IN NATURAL LANGUAGE MODE) AS relevance "
                    + " FROM `ps_item` as IT " + " ORDER BY relevance DESC");
            query.setString(0, q);
            query.addEntity(PsItem.class);
            return query.list();
        }
    });
}

From source file:com.bitranger.parknshop.common.dao.impl.PsItemDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*from  w  w  w  . j av a  2  s.c  o  m*/
public List<PsItem> findByCountPurchaseInCategory(final Integer psCategoryId, final FetchOption op) {
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsItem>>() {
            @Override
            public List<PsItem> doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(
                        "select * from ps_item as P where P.id_category = ? " + "order by count_purchase "
                                + (op.sortOption == SortOption.ASCENDING ? "asc" : "desc ") + "limit ?, ?");

                query.setInteger(0, psCategoryId).setInteger(1, op.offset).setInteger(2, op.limit);
                query.addEntity(PsItem.class);

                return query.list();
            }
        });
    } catch (RuntimeException re) {
        log.error("find by findByCountPurchaseInCategory failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.common.dao.impl.PsItemDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*from   w w  w.  j  a  va 2s .  c o  m*/
public List<PsItem> findByCountFavouriteInCategory(final Integer psCategoryId, final FetchOption op) {

    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsItem>>() {

            @Override
            public List<PsItem> doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery(
                        "select * from ps_item as P where P.id_category = ? " + "order by count_favourite "
                                + (op.sortOption == SortOption.ASCENDING ? "asc" : "desc ") + " limit ?, ?");

                query.setInteger(0, psCategoryId).setInteger(1, op.offset).setInteger(2, op.limit);
                query.addEntity(PsItem.class);

                return query.list();
            }
        });
    } catch (RuntimeException re) {
        log.error("find by findByCountFavouriteInCategory failed", re);
        throw re;
    }
}