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:cn.dao.MedicinePurchaseDao.java

@SuppressWarnings("unchecked")
public List<Object> query(MedicinePurchase vo, PageResult page) {
    Session session = this.getSessionFactory().getCurrentSession();
    Map<String, Object> properties = new HashMap<>();
    StringBuffer hql = new StringBuffer();
    hql.append("select t.batch_number,t.purchase_date,u.name");
    hql.append(" from medicine_purchase t");
    hql.append(" left join user_info u");
    hql.append(" on t.user_id = u.id");
    hql.append(" group by t.batch_number,t.purchase_date,u.name");
    hql.append(" order by t.purchase_date desc");
    Query query = session.createSQLQuery(hql.toString());
    query.setProperties(properties);/*from w  w w .j a  v  a2  s . c  o  m*/
    if (page != null) {
        int items = getPageCount(session, hql.toString(), properties);
        page.setItems(items);
        query.setFirstResult(page.getFirstResult());
        query.setMaxResults(page.getMaxResult());
    }
    return query.list();
}

From source file:cn.dao.MedicinePurchaseDao.java

public List<Object> queryInfo(MedicinePurchase vo, PageResult page) {
    Session session = this.getSessionFactory().getCurrentSession();
    Map<String, Object> properties = new HashMap<>();
    StringBuffer hql = new StringBuffer();
    hql.append("select t.batch_number,t.purchase_date,u.name");
    hql.append(" from medicine_purchase t");
    hql.append(" left join user_info u");
    hql.append(" on t.user_id = u.id");
    hql.append(" group by t.batch_number,t.purchase_date,u.name");
    hql.append(" order by t.purchase_date desc");
    Query query = session.createSQLQuery(hql.toString());
    if (page != null) {
        int items = getPageCount(session, hql.toString(), properties);
        page.setItems(items);// ww w.j av a 2 s  . co  m
        query.setFirstResult(page.getFirstResult());
        query.setMaxResults(page.getMaxResult());
    }
    return query.list();
}

From source file:cn.dao.MedicinePurchaseDao.java

public List<Object> queryByBatchNumber(MedicinePurchase vo, PageResult page) {
    Session session = this.getSessionFactory().getCurrentSession();
    Map<String, Object> properties = new HashMap<>();
    StringBuffer hql = new StringBuffer();
    hql.append("select u.license_number,u.medicine_name,t.cost_price,t.number");
    hql.append(" from medicine_purchase t");
    hql.append(" left join medicine_info u");
    hql.append(" on t.medicine_id = u.id");
    hql.append(" where t.batch_number = :batchNumber");
    hql.append(" order by t.purchase_date desc");
    Query query = session.createSQLQuery(hql.toString());
    properties.put("batchNumber", vo.getBatchNumber());
    query.setProperties(properties);//w  ww.  j  a v  a 2 s . c  o m
    if (page != null) {
        int items = getPageCount(session, hql.toString(), properties);
        page.setItems(items);
        query.setFirstResult(page.getFirstResult());
        query.setMaxResults(page.getMaxResult());
    }
    return query.list();
}

From source file:cn.dao.MedicineSalesDao.java

@SuppressWarnings("unchecked")
public List<Object> query(MedicineSales vo, PageResult page) {
    Session session = this.getSessionFactory().getCurrentSession();
    Map<String, Object> properties = new HashMap<>();
    StringBuffer hql = new StringBuffer();
    hql.append("select t.sale_batch_number,t.sale_date,u.name");
    hql.append(" from medicine_sales t");
    hql.append(" left join user_info u");
    hql.append(" on t.user_id = u.id");
    hql.append(" group by t.sale_batch_number,t.sale_date,u.name");
    hql.append(" order by t.sale_date desc");
    Query query = session.createSQLQuery(hql.toString());
    query.setProperties(properties);/*from www.ja v  a  2s. c om*/
    if (page != null) {
        int items = getPageCount(session, hql.toString(), properties);
        page.setItems(items);
        query.setFirstResult(page.getFirstResult());
        query.setMaxResults(page.getMaxResult());
    }
    return query.list();
}

From source file:cn.dao.MedicineSalesDao.java

public List<Object> queryByBatchNumber(MedicineSales vo, PageResult page) {
    Session session = this.getSessionFactory().getCurrentSession();
    Map<String, Object> properties = new HashMap<>();
    StringBuffer hql = new StringBuffer();
    hql.append("select u.license_number,u.medicine_name,t.sale_price,t.sale_number");
    hql.append(" from medicine_sales t");
    hql.append(" left join medicine_storehouse s");
    hql.append(" on t.medicine_store_house_id = s.id");
    hql.append(" left join medicine_info u");
    hql.append(" on s.medicine_id = u.id");
    hql.append(" where t.sale_batch_number = :batchNumber");
    hql.append(" order by t.sale_date desc");
    Query query = session.createSQLQuery(hql.toString());
    properties.put("batchNumber", vo.getSaleBatchNumber());
    query.setProperties(properties);//from   ww  w.  j a  v  a2 s .  com
    if (page != null) {
        int items = getPageCount(session, hql.toString(), properties);
        page.setItems(items);
        query.setFirstResult(page.getFirstResult());
        query.setMaxResults(page.getMaxResult());
    }
    return query.list();
}

From source file:cn.trymore.core.dao.impl.DAOGenericImpl.java

License:Open Source License

@Override
public Integer getCountByNativeSQL(final String sql) {
    return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
        @Override//from   ww w  .j  a va 2 s  . c om
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            return (Integer) (session.createSQLQuery(sql).addScalar("count", Hibernate.INTEGER).uniqueResult());
        }
    });
}

From source file:cognition.common.data.BaseDaoTest.java

License:Apache License

@Test
public void shouldCreateTable() {
    Session session = baseDao.getSourceSessionFactory().openSession();
    SQLQuery sqlQuery = session.createSQLQuery("create table TestTable(id int)");
    sqlQuery.executeUpdate();/*w  w  w.  j  a  v a 2  s.c  o m*/

    session.createSQLQuery("insert into TestTable values(1)").executeUpdate();
    session.createSQLQuery("insert into TestTable values(2)").executeUpdate();

    SQLQuery selectQuery = session.createSQLQuery("select * from TestTable");
    List list = selectQuery.list();

    assertThat(list.size(), equalTo(2));
}

From source file:com.abc.salesinventory.service.newpackage.InventoryServiceImpl.java

@Override
public List<Object> getReorderStock() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    session.beginTransaction();//from   www . j  a v a  2s .c  om

    Query query = session.createSQLQuery(
            "SELECT s.product_code, SUM(s.quantity) as 'qty', p.product_name,p.reorder_level FROM pharmacy.stock s\n"
                    + "join pharmacy.product p on s.product_code=p.product_code\n"
                    + "group by s.product_code, p.product_name, p.reorder_level\n"
                    + "having qty < (select p.reorder_level from product p where p.product_code=s.product_code)");

    List<Object> resultList = query.list();
    session.getTransaction().commit();
    session.close();
    if (resultList != null && resultList.size() > 0) {
        return resultList;
    }
    return null;
}

From source file:com.abc.salesinventory.service.newpackage.InventoryServiceImpl.java

@Override
public List<Object> getReorderStockWithSupplier() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    session.beginTransaction();/* w w  w .j  a v a 2  s .  c  o m*/

    Query query = session.createSQLQuery("SELECT s.product_code, p.product_name, p.unit, sup.id, sup.name, "
            + "sup.mobile, p.reorder_level,SUM(s.quantity) as 'qty', p.standard_reorder_level FROM pharmacy.stock s "
            + "join pharmacy.product p on s.product_code=p.product_code "
            + "join pharmacy.supplier sup on sup.id=s.supplier_id "
            + "group by s.product_code, p.product_name, p.reorder_level, p.standard_reorder_level "
            + "having qty < (select p.reorder_level from product p where p.product_code=s.product_code)");

    List<Object> resultList = query.list();
    session.getTransaction().commit();
    session.close();
    if (resultList != null && resultList.size() > 0) {
        return resultList;
    }
    return null;
}

From source file:com.abc.salesinventory.service.newpackage.MessageServiceImpl.java

@Override
public List<Message> getSupplierNameWithMessage() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    session.beginTransaction();/*from   w  w  w  .j  av a2s  .co m*/

    Query query = session.createSQLQuery(
            "SELECT m.supplier_id, s.name, m.message, m.msg_date, m.message_type, m.contact_number FROM pharmacy.message m join pharmacy.supplier s on m.supplier_id=s.id");

    List<Message> resultList = query.list();
    session.getTransaction().commit();
    session.close();
    if (resultList != null && resultList.size() > 0) {
        return resultList;
    }
    return null;
}