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.ReportDaoImpl.java

public List readItemByType(String type, String year, String month) {
    Session session = getSession();
    //        if (month == null) {
    //            Query query = session.createSQLQuery("SELECT c.RefNo, c.txnDate , a.ItemName , 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 b.TypeName = '" + type + "'");
    //            return query.list();
    //        } else {
    //            Query query = session.createSQLQuery("SELECT c.RefNo, c.txnDate , a.ItemName , 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) = " + month + " "
    //                    + "AND b.TypeName = " + type);
    //            return query.list();
    //        }//from  ww  w.j ava  2 s  .c  o m
    Query query = session.createSQLQuery("SELECT c.RefNo, c.txnDate , a.ItemName , 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 b.TypeName = " + type);
    return query.list();
}

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

public List readType() {
    Session session = getSession();
    Query sQLQuery = session.createSQLQuery("SELECT a.typeName FROM FType a GROUP BY  a.typeName");
    return sQLQuery.list();
}

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

public List readYears() {
    Session session = getSession();
    Query sQLQuery = session
            .createSQLQuery("SELECT YEAR(c.txnDate) " + "FROM FInvdet c " + "GROUP BY YEAR(c.TxnDate)");
    return sQLQuery.list();
}

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

public List readReps() {
    Session session = getSession();
    String sql = "SELECT a.RepName FROM fSalRep a";
    Query sQLQuery = session.createSQLQuery(sql);
    return sQLQuery.list();
}

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

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

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

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

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

public List readByRepName(String reps, String year, String months) {
    Session session = getSession();
    String sql = "SELECT c.RefNo, d.ItemName , c.TxnDate ,c.Qty , c.SellPrice "
            + "FROM fSalRep a , fInvhed b , fInvdet c , fitems d "
            + "WHERE a.RepCode = b.RepCode AND b.RefNo = c.RefNo AND c.Itemcode = d.ItemCode "
            + "AND YEAR(b.TxnDate) = " + year + " " + "AND a.RepName = " + reps + " "
            + "AND DATENAME(MONTH, c.TxnDate) IN (" + months + ") ";
    Query query = session.createSQLQuery(sql);
    return query.list();
}

From source file:com.blackcrowsys.bcslog.server.dao.JdbcLogsDao.java

License:Open Source License

@Override
public void save(String data, String table) {
    String sql = String.format("INSERT INTO %s (LOG) VALUES (:data)", table);
    Session session = sessionFactory.openSession();
    Transaction transaction;//w  w w .  jav  a 2s .c o  m
    try {
        transaction = session.beginTransaction();
        Query query = session.createSQLQuery(sql);
        query.setParameter("data", data);
        query.executeUpdate();
        transaction.commit();
    } catch (HibernateException e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}

From source file:com.blackcrowsys.bcslog.server.dao.JdbcLogsDao.java

License:Open Source License

@Override
public void save(String data, String table, String application) {
    String sql = String.format("INSERT INTO %s (APPLICATION, LOG) VALUES (:application, :data)", table);
    Session session = sessionFactory.openSession();
    Transaction transaction;//from  w w  w  . ja va2s.  c  om
    try {
        transaction = session.beginTransaction();
        Query query = session.createSQLQuery(sql);
        query.setParameter("data", data);
        query.setParameter("application", application);
        query.executeUpdate();
        transaction.commit();
    } catch (HibernateException e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}

From source file:com.buy.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    int cost;/*from w  w  w  .j  av a 2  s.  c  om*/
    HttpSession session = request.getSession(true);
    try {

        Configuration cfg = new Configuration();
        cfg.configure("hibernate.cfg.xml");//populates the data of the configuration file  
        SessionFactory factory = cfg.buildSessionFactory();
        Session session1 = factory.openSession();
        Transaction t = session1.beginTransaction();
        String UEmail = (String) session.getAttribute("email");
        int price = 0;

        price = (Integer) session.getAttribute("price");
        int i = Integer.parseInt(request.getParameter("i"));

        cost = Integer.parseInt(request.getParameter("cost"));
        if (price < cost) {
            out.println("You Dont have Enough Balance to purchase");
        } else {
            Date d = new Date();
            if (i == 1) {
                String comp = request.getParameter("comp");
                String email = request.getParameter("email");
                ShareBuy u = new ShareBuy();

                u.setDate(d);
                u.setRate(cost);
                u.setSellerEmail(email);
                u.setUseremail(UEmail);
                u.setStatus("BUY");
                u.setCompany(comp);
                u.setEnd_rate(0);
                session1.persist(u);
                price = price - cost;
                session.removeAttribute("price");

                session.setAttribute("price", price);
                t.commit();

                Transaction t1 = session1.beginTransaction();
                session1.createSQLQuery(
                        "UPDATE STOCK.STOCKUSER set MONEY=" + price + "   WHERE EMAIL='" + UEmail + "' ")
                        .executeUpdate();
                t1.commit();
                int moneyStock = 0;
                Transaction t3 = session1.beginTransaction();
                List list = session1.createQuery("from com.StockUser Where EMAIL='" + email + "'").list();
                Iterator iterator = list.iterator();

                for (int j = 0; j < list.size(); j++) {
                    StockUser user = (StockUser) iterator.next();
                    moneyStock = user.getMoney();

                }

                t3.commit();
                moneyStock = moneyStock + cost;
                Transaction t2 = session1.beginTransaction();
                session1.createSQLQuery(
                        "UPDATE STOCK.STOCKUSER set MONEY=" + moneyStock + "   WHERE EMAIL='" + email + "' ")
                        .executeUpdate();
                t2.commit();
                out.print("Success");

                Transaction t4 = session1.beginTransaction();
                TransactionT tra = new TransactionT();
                tra.setAmount(cost);
                tra.setSellermail(email);
                tra.setStatus("S-U");
                tra.setD(d);
                tra.setUsermail(UEmail);
                session1.persist(tra);
                t4.commit();

            }

        }

        session1.close();
    }

    catch (Exception e1) {

        e1.printStackTrace();
    }
}