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.biz.report.dao.impl.CustomerReportDaoImpl.java

public List readCustomers() {
    Session session = getSession();
    String sql = "SELECT  a.DebName FROM CASSIMS.dbo.fDebtor a  ORDER BY 1 ASC";
    Query sQLQuery = session.createSQLQuery(sql);
    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.CustomerReportDaoImpl.java

public List read(String customers, String months, String year) {
    Session session = getSession();
    String sql = "SELECT MONTH(b.TxnDate) AS A, a.DebName , sum(c.SellPrice) "
            + "FROM CASSIMS.dbo.fDebtor a , CASSIMS.dbo.fInvhed b , CASSIMS.dbo.fInvdet c "
            + "WHERE a.DebCode = b.DebCode AND b.RefNo = c.RefNo " + "AND YEAR(b.TxnDate) = " + year + " "
            + "AND a.DebName IN (" + customers + ") " + "AND DATENAME(MONTH, b.TxnDate) IN (" + months + ") "
            + "GROUP BY MONTH(b.TxnDate), a.DebName ";
    Query sQLQuery = session.createSQLQuery(sql);
    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.CustomerReportDaoImpl.java

public List readByMonth(String customers, String months, String year) {
    Session session = getSession();
    String sql = "SELECT  a.DebName , sum(c.SellPrice) "
            + "FROM CASSIMS.dbo.fDebtor a , CASSIMS.dbo.fInvhed b , CASSIMS.dbo.fInvdet c "
            + "WHERE a.DebCode = b.DebCode AND b.RefNo = c.RefNo " + "AND YEAR(b.TxnDate) = " + year + " "
            + "AND a.DebName IN (" + customers + ") " + "AND DATENAME(MONTH, b.TxnDate) IN (" + months + ") "
            + "GROUP BY a.DebName";
    Query sQLQuery = session.createSQLQuery(sql);
    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.CustomerReportDaoImpl.java

@Override
public List readTableData(String customers, String year, String months) {
    Session session = getSession();
    String sql = "SELECT a.RefNo , b.TxnDate, d.ItemName , b.Qty , b.SellPrice "
            + "FROM CASSIMS.dbo.fInvhed a, CASSIMS.dbo.fInvdet b, CASSIMS.dbo.fDebtor c, CASSIMS.dbo.fitems d "
            + "WHERE a.RefNo = b.RefNo AND a.DebCode = c.DebCode AND b.Itemcode = d.ItemCode "
            + "AND DATENAME(MONTH, b.TxnDate) IN (" + months + ") " + "AND YEAR(b.TxnDate) = " + year + " "
            + "AND c.DebName = " + customers;
    Query sQLQuery = session.createSQLQuery(sql);
    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.ItemDashBoardDaoImpl.java

public List readItems() {
    Session session = getSession();
    Query sQLQuery = session
            .createSQLQuery("SELECT a.ItemName FROM CASSIMS.dbo.fitems a " + "GROUP BY a.ItemName");

    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.ItemDashBoardDaoImpl.java

public List read(String items, String months, String year) {
    Session session = getSession();
    Query sQLQuery = session.createSQLQuery("SELECT MONTH(b.TxnDate) AS A  , a.ItemName , sum(b.SellPrice) "
            + "FROM CASSIMS.dbo.fItems a , CASSIMS.dbo.fInvdet b " + "WHERE a.ItemCode = b.Itemcode "
            + "AND YEAR(b.TxnDate) = " + year + " " + "AND a.ItemName IN (" + items + ") "
            + "AND DATENAME(MONTH, b.TxnDate) IN (" + months + ") "
            + "GROUP BY a.ItemName , MONTH(b.TxnDate) ");
    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.ItemDashBoardDaoImpl.java

public List readByMonth(String items, String months, String year) {
    Session session = getSession();
    Query sQLQuery = session.createSQLQuery("SELECT a.ItemName , sum(b.SellPrice) "
            + "FROM CASSIMS.dbo.fItems a , CASSIMS.dbo.fInvdet b " + "WHERE a.ItemCode = b.Itemcode "
            + "AND YEAR(b.TxnDate) = " + year + " " + "AND a.ItemName IN (" + items + ") "
            + "AND DATENAME(MONTH, b.TxnDate) IN (" + months + ") " + "GROUP BY a.ItemName ");
    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.ItemDashBoardDaoImpl.java

public List readByItemName(String itemName, String year, String month) {
    Session session = getSession();
    String sql = "SELECT c.RefNo, c.txnDate , b.TypeName , c.Qty , c.sellPrice "
            + "FROM fitems a , FType b , FInvdet c  "
            + "WHERE a.typeCode = b.typeCode AND c.itemCode = a.itemCode " + "AND YEAR(c.txnDate) =  " + year
            + " " + "AND DATENAME(MONTH, c.txnDate) IN (" + month + ") " + "AND a.ItemName = " + itemName;
    Query query = session.createSQLQuery(sql);
    return query.list();
}

From source file:com.biz.report.dao.impl.ReportDaoImpl.java

@Override
public List read(String types, String year, String months) {
    Session session = getSession();
    Query sQLQuery = session.createSQLQuery("SELECT MONTH(c.TxnDate) AS A , b.typeName , sum(c.sellPrice) "
            + "FROM FItems a , FType b , FInvdet c "
            + "WHERE a.typeCode = b.typeCode AND c.itemCode = a.itemCode " + "AND YEAR(c.txnDate) = " + year
            + " AND b.TypeName IN (" + types + ") " + "AND DATENAME(MONTH, c.txnDate) IN (" + months + ") "
            + "GROUP BY b.typeName  ,  MONTH(c.TxnDate) ");

    return sQLQuery.list();
}

From source file:com.biz.report.dao.impl.ReportDaoImpl.java

public List readPieChartData(String types, String year, String months) {
    Session session = getSession();
    Query sQLQuery = session
            .createSQLQuery("SELECT  b.typeName , sum(c.sellPrice) " + "FROM fitems a ,  FType b ,  FInvdet c "
                    + "WHERE a.typeCode = b.typeCode AND c.itemCode = a.itemCode " + "AND YEAR(c.txnDate) = "
                    + year + "AND b.TypeName IN (" + types + ") " + "AND DATENAME(MONTH, c.txnDate) IN ("
                    + months + ") " + "GROUP BY b.typeName ");

    return sQLQuery.list();
}