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.seller.dao.impl.PsOrderDAO.java

License:Open Source License

@Override
public double countTnxVolumn() {
    return getHibernateTemplate().execute(new HibernateCallback<Double>() {
        @Override//from w  w w  . ja v  a 2s  .  co  m
        public Double doInHibernate(Session session) throws HibernateException, SQLException {
            SQLQuery query = session.createSQLQuery(
                    " select sum(OD.price_total)as REV from ps_order as OD " + " where OD.status = 3");
            query.addScalar("REV", Hibernate.DOUBLE);
            Double db = (Double) query.uniqueResult();
            return db == null ? 0.0 : db;
        }
    });
}

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

License:Open Source License

@SuppressWarnings("unchecked")
@Override//w w  w.j  av  a 2s  .  c  o m
public List<PsRecipient> findByCustomId(final Integer id) {
    return getHibernateTemplate().executeFind(new HibernateCallback<List<PsRecipient>>() {

        @Override
        public List<PsRecipient> doInHibernate(Session session) throws HibernateException, SQLException {
            SQLQuery q = session.createSQLQuery("select * from ps_recipient as RP where RP.id_customer = ?");
            q.setInteger(0, id);
            q.addEntity(PsRecipient.class);
            return q.list();
        }
    });
}

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

License:Open Source License

@SuppressWarnings("unchecked")
@Override//from   w  ww  . j  a v a  2  s .  c  o m
public List<PsSeller> findAll(final FetchOption fetchOption) {
    log.debug("find all PsSeller");
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsSeller>>() {

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

                SQLQuery query = arg0.createSQLQuery("select * from ps_seller as P order by P.id "
                        + (fetchOption.sortOption == SortOption.ASCENDING ? "asc" : "desc"));

                query.addEntity(PsSeller.class);
                return query.list();
            }

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

}

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

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*w w  w  . ja  v  a 2  s . com*/
public List<PsShop> findBySellerId(final Integer id) {

    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsShop>>() {
            @Override
            public List<PsShop> doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery("select * from ps_shop as P where P.id_seller = ?");

                query.setInteger(0, id);

                return query.addEntity(PsShop.class).list();
            }
        });
    } catch (RuntimeException re) {
        log.error("find by seller id failed", re);
        throw re;
    }
}

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

License:Open Source License

@SuppressWarnings("unchecked")
@Override/*from   w  ww  . j  av a 2  s .com*/
public List<PsShop> findAllSortByVote(final FetchOption op) {
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsShop>>() {
            @Override
            public List<PsShop> doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery("select * from ps_shop order by vote "
                        + (op.sortOption == SortOption.ASCENDING ? "asc" : "desc"));

                return query.addEntity(PsShop.class).list();
            }
        });
    } catch (RuntimeException re) {
        log.error("find all seller failed", re);
        throw re;
    }
}

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

License:Open Source License

@SuppressWarnings("unchecked")
@Override// w  w w  .j  a  v  a 2 s .c om
public List<PsShop> findAllSortByName(final FetchOption op) {
    try {
        return getHibernateTemplate().executeFind(new HibernateCallback<List<PsShop>>() {
            @Override
            public List<PsShop> doInHibernate(Session session) throws HibernateException, SQLException {
                SQLQuery query = session.createSQLQuery("select * from ps_shop order by name "
                        + (op.sortOption == SortOption.ASCENDING ? "asc" : "desc"));

                return query.addEntity(PsShop.class).list();
            }
        });
    } catch (RuntimeException re) {
        log.error("find all seller failed", re);
        throw re;
    }
}

From source file:com.bitranger.parknshop.seller.dao.PsNoticeSellerDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//from   w ww .  jav a  2 s .c  om
public List<PsNoticeSeller> getLatest(final int sellerID, final int n) {

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

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

            SQLQuery q = arg0.createSQLQuery(" select ps_notice_seller as NS "
                    + " where NS.is_valid = 1 and id_seller = ? " + " order by time_created desc limit ?");
            q.setInteger(0, sellerID);
            q.setInteger(1, n);
            q.addEntity(PsNoticeSeller.class);
            return q.list();
        }

    });
}

From source file:com.bitranger.parknshop.task.ClearOutdated.java

License:Open Source License

/**
 delete out dated advertisements, notice
         //from w w w  .  j a v a 2 s  . c o m
         
delete from ps_promot_item as P inner join ps_ad_item as AD
where AD.time_end < CURRENT_TIMESTAMP
         
 */
@Override
public void run() {
    getHibernateTemplate().execute(new HibernateCallback<Void>() {

        @Override
        public Void doInHibernate(Session session) throws HibernateException, SQLException {
            Transaction tnx = session.beginTransaction();
            SQLQuery query = session
                    .createSQLQuery(" delete from ps_promot_item as P inner join ps_ad_item as AD "
                            + " where AD.time_end < CURRENT_TIMESTAMP ");
            query.executeUpdate();

            /**
            delete from ps_notice_seller as NS 
            where NS.is_valid = 0
            and DATE_ADD(NS.time_created, INTERVAL 60 DAY) < CURRENT_TIMESTAMP 
                    
             */
            query = session.createSQLQuery("delete from ps_notice_seller as NS " + "   where NS.is_valid = 0 "
                    + "   and DATE_ADD(NS.time_created, INTERVAL 60 DAY) < CURRENT_TIMESTAMP ");
            query.executeUpdate();

            query = session.createSQLQuery("delete from ps_notice_customer as NS " + "   where NS.is_valid = 0 "
                    + "   and DATE_ADD(NS.time_created, INTERVAL 60 DAY) < CURRENT_TIMESTAMP ");
            query.executeUpdate();

            //            query = session.createSQLQuery(
            //"delete from ps_notice_admin as NS " +
            //"   where NS.is_valid = 0 " +
            //"   and DATE_ADD(NS.time_created, INTERVAL 60 DAY) < CURRENT_TIMESTAMP "
            //);
            //            query.executeUpdate();

            tnx.commit();
            return null;
        }
    });
}

From source file:com.bitranger.parknshop.task.MaintainDB.java

License:Open Source License

@Override
public void run() {
    //      SessionFactory sessionFactory = factory.getBean("");
    @SuppressWarnings("unchecked")
    final Map<String, SingleTableEntityPersister> map = getHibernateTemplate().getSessionFactory()
            .getAllClassMetadata();/*from  ww  w .ja v  a2  s  . c  o m*/

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

        @Override
        public Void doInHibernate(Session session) throws HibernateException, SQLException {
            Transaction tnx = null;
            try {
                tnx = session.beginTransaction();

                for (SingleTableEntityPersister v : map.values()) {
                    session.createSQLQuery("analyze table " + v.getTableName()).executeUpdate();
                    session.createSQLQuery("optimize table " + v.getTableName()).executeUpdate();
                }
                tnx.commit();
            } catch (Exception e) {
                throw new RuntimeException(e);
            } finally {
                if (tnx != null && tnx.isActive()) {
                    tnx.rollback();
                }
            }
            return null;
        }
    });
}

From source file:com.bitranger.parknshop.task.order.OrderPaid.java

License:Open Source License

@Override
public void run() {

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

        @Override/*w  w w .  ja  v a2 s  . co m*/
        public Void doInHibernate(Session arg0) throws HibernateException, SQLException {

            SQLQuery query = arg0.createSQLQuery("");

            @SuppressWarnings("unchecked")
            List<PsOrder> orders = query.list();
            for (PsOrder psOrder : orders) {

                PsNoticeCustomer cnotice = new PsNoticeCustomer();
                cnotice.setPsCustomer(psOrder.getPsCustomer());
                cnotice.setMessage("Unpaied order cancled");
                save(cnotice);

                PsNoticeSeller nSeller = new PsNoticeSeller();
                nSeller.setPsSeller(psOrder.getPsShop().getPsSeller());
                nSeller.setMessage("Unpaied order cancled");
                psNoticeSellerDAO.save(nSeller);

                psOrderDAO.delete(psOrder);

            }
            return null;
        }
    });
}