Example usage for org.hibernate Session createSQLQuery

List of usage examples for org.hibernate Session createSQLQuery

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Session
  6. createSQLQuery

Introduction

In this page you can find the example usage for org.hibernate Session createSQLQuery.

Prototype

@Override
    NativeQuery createSQLQuery(String queryString);

Source Link

Usage

From source file:com.bitranger.parknshop.common.service.ads.ItemAdService.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//ww w . j a  v a  2 s .  c  om
public List<PsPromotItem> forIndexPage(final int limit) {
    return psAdItemDAO.hibernate().executeFind(new HibernateCallback<List<PsPromotItem>>() {

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

            SQLQuery query = session.createSQLQuery(" select * from ps_promot_item as PM "
                    + " inner join ps_ad_item as AD on AD.id_promot = PM.id "
                    + " where AD.time_start < CURRENT_TIMESTAMP and CURRENT_TIMESTAMP < AD.time_end "
                    + " order by AD.weight desc " + " limit 0, 32 ");
            List<PsPromotItem> ls = query.addEntity(PsPromotItem.class).list();

            return randomReduce(ls, limit);
        }
    });
}

From source file:com.bitranger.parknshop.common.service.ads.ItemAdService.java

License:Open Source License

/**
         //from   w w w  .  j  a v a  2  s  .  c o  m
select AD.*, count(RTI.id_tag) as TAG_INTER from ps_item as IT
    inner join r_tag_item as RTI on RTI.id_item = IT.id
    inner join ps_promot_item as AD on AD.id_item = IT.id
where IT.id = ? and RTI.id_tag in (???)
order by TAG_INTER desc
        
 */

@SuppressWarnings("unchecked")
@Override
public List<PsPromotItem> forItemList(@Nullable final List<PsItem> items, final int limit,
        @Nullable PsCustomer customer) {

    if (customer != null) {
        return new CFilter().filter(items, limit, customer.getId());
    }

    final HashSet<PsTag> tags = new HashSet<>(items.size() * 6);
    for (PsItem i : items) {
        tags.addAll(i.getPsTags());
    }
    final StringBuilder b = new StringBuilder(512);
    b.append(" select AD.*, count(RTI.id_tag) as TAG_INTER from ps_item as IT "
            + " inner join r_tag_item as RTI on RTI.id_item = IT.id "
            + " inner join ps_promot_item as AD on AD.id_item = IT.id  ");
    if (tags != null && tags.size() > 1) {
        b.append("  where RTI.id_tag in (  ");
        for (PsTag psTag : tags) {
            b.append(psTag.getId()).append(',');
        }
        b.setCharAt(b.length() - 1, ')');
    }

    b.append(" order by TAG_INTER desc  ");
    System.out.println(b.toString());
    return psAdItemDAO.hibernate().executeFind(new HibernateCallback<List<PsPromotItem>>() {
        @Override
        public List<PsPromotItem> doInHibernate(Session session) throws HibernateException, SQLException {

            return session.createSQLQuery(b.toString()).addEntity(PsPromotItem.class).list();
        }

    });
    //      Iterator<PsTag> i = tags.iterator();
    //      while (tags.size() > limit) {
    //         i.remove();
    //      }

}

From source file:com.bitranger.parknshop.common.service.ads.ItemAdService.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override// w ww.  j a v a2 s  .  c  om
public List<PsPromotItem> forItem(@Nullable final List<Integer> tags, final int category, final int limit,
        @Nullable PsCustomer customer) {
    if (customer != null) {
        return new CFilter().filter(tags, category, limit, customer.getId());
    }

    return psAdItemDAO.hibernate().executeFind(new HibernateCallback<List<PsPromotItem>>() {

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

            //            session.createSQLQuery(
            //            "update ps_ad_item as AD set AD.num_fetched = AD.num_fetched + 1"
            //            ).executeUpdate();
            StringBuilder b = new StringBuilder(512);
            b.append(" select * from ps_promot_item as PM "
                    + " inner join ps_ad_item as AD on AD.id_promot = PM.id "
                    + " inner join ps_item as IT on IT.id = PM.id_item "
                    + " inner join r_tag_item as RIT on RIT.id_item = IT.id " + " where IT.id_category = ? "
                    + " and AD.time_start < CURRENT_TIMESTAMP and CURRENT_TIMESTAMP < AD.time_end ");
            if (tags != null && tags.size() > 0) {
                b.append(" and RIT.id_tag in (");
                for (Integer i : tags) {
                    b.append(i).append(',');
                }
                b.setCharAt(b.length() - 1, ')');
            }

            b.append(" order by AD.weight desc " + " limit 0, 32 ");

            SQLQuery query = session.createSQLQuery(b.toString());
            query.setInteger(0, category);
            List<PsPromotItem> ls = query.addEntity(PsPromotItem.class).list();

            return randomReduce(ls, limit);
        }
    });
}

From source file:com.bitranger.parknshop.seller.dao.impl.PsOrderDAO.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public List<PsOrder> findByCustomerId(final Integer id, final Date from, final Date to) {
    log.debug("findByCustomerId: " + id);
    try {//from   www  .jav  a2s. com
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsOrder>>() {

            @Override
            public List<PsOrder> doInHibernate(Session arg0) throws HibernateException, SQLException {
                SQLQuery query = arg0.createSQLQuery(
                        "select * from ps_order as P where P.id_customer = ? and P.time_created > ? "
                                + "+ and P.time_created < ?");
                query.setInteger(0, id).setDate(1, from).setDate(2, to);
                query.addEntity(PsOrder.class);
                return query.list();

            }

        });
    } catch (RuntimeException re) {
        log.error("find by customerId failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.seller.dao.impl.PsOrderDAO.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public List<PsOrder> findByCustomerId(final Integer id, final Short state) {
    log.debug("findByCustomerId: " + id);
    try {/*from w w w.j av  a  2s . c om*/
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsOrder>>() {

            @Override
            public List<PsOrder> doInHibernate(Session arg0) throws HibernateException, SQLException {
                SQLQuery query = arg0
                        .createSQLQuery("select * from ps_order as P where P.id_customer = ? and P.status = ?");
                query.setInteger(0, id).setShort(1, state);
                query.addEntity(PsOrder.class);
                return query.list();

            }

        });
    } catch (RuntimeException re) {
        log.error("find by customerId failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.seller.dao.impl.PsOrderDAO.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public List<PsOrder> findByCustomerId(final Integer id) {
    log.debug("findByCustomerId: " + id);
    try {//from ww w . ja v a 2 s . c om
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsOrder>>() {

            @Override
            public List<PsOrder> doInHibernate(Session arg0) throws HibernateException, SQLException {
                SQLQuery query = arg0.createSQLQuery(
                        "select * from ps_order as P where P.id_customer = ? order by P.id desc");
                query.setInteger(0, id);
                query.addEntity(PsOrder.class);
                return query.list();

            }

        });
    } catch (RuntimeException re) {
        log.error("find by customerId failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.seller.dao.impl.PsOrderDAO.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public List<PsOrder> findByShopId(final Integer id, final Date from, final Date to) {
    log.debug("find by shop_id: " + id);
    try {//from   w  w  w. j  a  v a 2 s  .  c o  m
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsOrder>>() {

            @Override
            public List<PsOrder> doInHibernate(Session arg0) throws HibernateException, SQLException {
                SQLQuery query = arg0.createSQLQuery(
                        "select * from ps_order as P where P.id_shop = ? and P.time_created > ? "
                                + " and P.time_created < ?");
                query.setInteger(0, id).setDate(1, from).setDate(2, to);
                query.addEntity(PsOrder.class);
                return query.list();

            }

        });
    } catch (RuntimeException re) {
        log.error("find by shop id failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.seller.dao.impl.PsOrderDAO.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public List<PsOrder> findByShopId(final Integer id) {
    log.debug("findByCustomerId: " + id);
    try {/*from w w  w .  jav  a  2  s  .  co  m*/
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsOrder>>() {
            @Override
            public List<PsOrder> doInHibernate(Session arg0) throws HibernateException, SQLException {
                SQLQuery query = arg0.createSQLQuery("select * from ps_order as P where P.id_shop = ?");
                query.setInteger(0, id);
                query.addEntity(PsOrder.class);
                return query.list();

            }

        });
    } catch (RuntimeException re) {
        log.error("find by shop id  failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.seller.dao.impl.PsOrderDAO.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public List<PsOrder> findByShopId(final Integer id, final Short state) {
    log.debug("findByCustomerId: " + id);
    try {//  ww w  .j  a  v a  2s. co m
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsOrder>>() {
            @Override
            public List<PsOrder> doInHibernate(Session arg0) throws HibernateException, SQLException {
                SQLQuery query = arg0
                        .createSQLQuery("select * from ps_order as P where P.id_shop = ? and P.status = ?");
                query.setInteger(0, id).setShort(1, state);
                query.addEntity(PsOrder.class);
                return query.list();

            }

        });
    } catch (RuntimeException re) {
        log.error("find by shop id  failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.seller.dao.impl.PsOrderDAO.java

License:Open Source License

/**
         //from w w w  .  j  a  va 2  s  .  c o  m
select OD.* from ps_order as OD
 inner join ps_shop as SH on SH.id = OD.id_shop
where SH.id_seller = ?
        
         
 */

@Override
@SuppressWarnings("unchecked")
public List<PsOrder> findBySellerId(final Integer id) {
    return getHibernateTemplate().executeFind(new HibernateCallback<List<PsOrder>>() {

        @Override
        public List<PsOrder> doInHibernate(Session arg0) throws HibernateException, SQLException {

            SQLQuery query = arg0.createSQLQuery(" select OD.* from ps_order as OD "
                    + "         inner join ps_shop as SH on SH.id = OD.id_shop " + " where SH.id_seller = ?");
            query.addEntity(PsOrder.class);
            query.setInteger(0, id);
            return query.list();
        }
    });
}