Example usage for org.hibernate Session 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.buyer.dao.CustomerFavouriteItemDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<CustomerFavouriteItem> findByCustomerId(final Integer psCustomerId, final FetchOption option) {
    try {/*www  .ja  v a 2 s  .  co  m*/
        return getHibernateTemplate().executeFind(new HibernateCallback<List<CustomerFavouriteItem>>() {

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

                SQLQuery query = session
                        .createSQLQuery("select * from customer_favourite_item as C where C.id_customer = ?"
                                + " order by C.time_created "
                                + (option.sortOption == SortOption.ASCENDING ? "asc" : "desc")
                                + " limit ? offset ?");
                query.setInteger(0, psCustomerId);
                query.setInteger(1, option.limit);
                query.setInteger(2, option.offset);
                query.addEntity(CustomerFavouriteItem.class);
                return query.list();
            }
        });
    } catch (RuntimeException e) {
        log.error("get failed", e);
        throw e;
    }
}

From source file:com.bitranger.parknshop.buyer.dao.impl.CartCustomerItemDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*  w  w w  . ja  v  a2  s  . c  o m*/
public List<CartCustomerItem> findByCustomerId(final Integer psCustomerId, final FetchOption option) {

    log.debug("getting CartCustomerItem instance with psCustomerId: " + psCustomerId);
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<CartCustomerItem>>() {

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

                SQLQuery query = session
                        .createSQLQuery("select * from cart_customer_item as C where C.id_customer = ?"
                                + " order by C.time_created "
                                + (option.sortOption == SortOption.ASCENDING ? "asc" : "desc")
                                + " limit ? offset ?");
                query.setInteger(0, psCustomerId);
                query.setInteger(1, option.limit);
                query.setInteger(2, option.offset);
                query.addEntity(CartCustomerItem.class);
                return query.list();
            }
        });
    } catch (RuntimeException e) {
        log.error("get failed", e);
        throw e;
    }
}

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//w  ww .  j  av  a2 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   w  w  w . ja  va2 s  .  c  om*/
        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 //from  w  w  w. jav a 2  s  .c  o 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.ads.PsPromotItemDAO.java

License:Open Source License

public double calAdRevenue() {

    return getHibernateTemplate().execute(new HibernateCallback<Double>() {

        @Override/*  w w  w  .  j  a  v a2s  .  c  om*/
        public Double doInHibernate(Session session) throws HibernateException, SQLException {
            SQLQuery q = session
                    .createSQLQuery(" select sum(AD.num_fetched * AD.weight) as ACC 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.addScalar("ACC", Hibernate.DOUBLE);
            Double db = (Double) q.uniqueResult();
            return db == null ? 0.0 : db;
        }
    });
}

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

License:Open Source License

@Override
public Object get(final String key) {
    Assert.notBlank(key);//from w  w w  .  j a va  2s.co  m

    byte[] b = getHibernateTemplate().execute(new HibernateCallback<byte[]>() {

        @Override
        public byte[] doInHibernate(Session arg0) throws HibernateException, SQLException {
            SQLQuery query = arg0.createSQLQuery("select val from ps_key_values where key = ?");
            query.setString(0, key);
            query.addScalar("val", Hibernate.BLOB);
            return (byte[]) query.uniqueResult();
        }
    });
    return ObjUtils.fromBytes(b);
}

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

License:Open Source License

@Override
public void put(final String key, Object value) {
    Assert.isTrue(value instanceof Serializable, "value must be Serializable");
    final byte[] b = ObjUtils.toBytes(value);

    getHibernateTemplate().execute(new HibernateCallback<Void>() {

        @Override// ww w . j  a  v  a 2s.  com
        public Void doInHibernate(Session arg0) throws HibernateException, SQLException {
            SQLQuery query = arg0.createSQLQuery("insert into ps_key_value(key, val)values(?,?)");
            query.setString(0, key);
            query.setBinary(1, b);
            query.executeUpdate();
            return null;
        }
    });
}

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

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*from  w ww . j  a v  a2s  .co 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 va2s . co  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;
    }
}