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:au.org.theark.core.dao.CSVLoaderDao.java

License:Open Source License

/**
 * Create a temporary table to store data from an external file into
 * @param databaseName//from   w  w  w  .jav  a2s  .co m
 * @param temporaryTableName
 * @param columnNameList
 */
public void createTemporaryTable(String databaseName, String temporaryTableName, List<String> columnNameList) {
    if (temporaryTableName != null && !columnNameList.isEmpty()) {
        Session session = getSession();
        session.beginTransaction();

        StringBuffer sqlDropTemporyTable = new StringBuffer();
        sqlDropTemporyTable.append("DROP TABLE ");
        sqlDropTemporyTable.append(databaseName);
        sqlDropTemporyTable.append(".");
        sqlDropTemporyTable.append(temporaryTableName);

        StringBuffer sqlCreateTemporyTable = new StringBuffer();
        //sqlCreateTemporyTable.append("CREATE TEMPORARY TABLE ");
        sqlCreateTemporyTable.append("CREATE TEMPORARY TABLE ");
        sqlCreateTemporyTable.append(databaseName);
        sqlCreateTemporyTable.append(".");
        sqlCreateTemporyTable.append(temporaryTableName);
        sqlCreateTemporyTable.append(" (");

        StringBuffer colNameAndType = new StringBuffer();

        for (Iterator<String> iterator = columnNameList.iterator(); iterator.hasNext();) {
            String columnName = (String) iterator.next();
            colNameAndType.append(columnName);
            colNameAndType.append(" varchar(255)");
            colNameAndType.append(delimiterCharacter);
            colNameAndType.append("\n");
        }
        colNameAndType.deleteCharAt(colNameAndType.lastIndexOf(","));

        sqlCreateTemporyTable.append(colNameAndType);
        sqlCreateTemporyTable.append(");");

        try {
            session.createSQLQuery(sqlDropTemporyTable.toString()).executeUpdate();
            log.info("SQL dropTemporaryTable SUCCEEDED");
        } catch (JDBCException e) {
            log.error(e.getMessage());
            log.info("SQL dropTemporaryTable FAILED");
        }

        try {
            session.createSQLQuery(sqlCreateTemporyTable.toString()).executeUpdate();
            log.info("SQL createTemporaryTable SUCCEEDED");
        } catch (JDBCException e) {
            log.error(e.getMessage());
            log.info("SQL createTemporaryTable FAILED");
        } finally {
            session.flush();
        }
    }
}

From source file:automatedbillingsoftware_DA.Categories_DA.java

public List<Categories> searchCategories(String searchKeyWord) {
    SessionFactory sessionFactory = HibernateUtils.getLocSessionFactory();
    Session session = sessionFactory.getCurrentSession();
    Transaction beginTransaction = session.beginTransaction();
    Query query = session.createSQLQuery("Select * from categories_tbl  where catName like  '%" + searchKeyWord
            + "%' OR catDesc like  '%" + searchKeyWord + "%'AND status=1");
    Categories category = new Categories();
    List list = query.list();/*from  w  ww.  j a  va 2 s.c o m*/
    List<Categories> catList = new ArrayList<>();
    for (int i = 0; i < list.size(); i++) {
        Object[] cat = (Object[]) list.get(i);
        int id = (Integer) cat[0];
        String name = (String) cat[3];
        String description = (String) cat[1];
        Date dt = (Date) cat[2];
        int status = (Integer) cat[5];
        double discount = (Double) cat[4];
        category = new Categories();
        category.setCatDesc(description);
        category.setCatName(name);
        category.setCatModifiedDate(dt);
        category.setDiscount(discount);
        category.setStatus(status);
        category.setCatid(id);
        catList.add(category);

    }

    beginTransaction.commit();
    return catList;
}

From source file:automatedbillingsoftware_DA.Products_DA.java

public List<Products> fetchProductSearchList(String catName, String prodName, double minQty, double maxQty,
        double minPrice, double maxPrice) {
    System.out.println("prodName 1=>" + prodName);
    SessionFactory sessionFactory = HibernateUtils.getLocSessionFactory();
    Session session = sessionFactory.getCurrentSession();
    Transaction beginTransaction = session.beginTransaction();
    Categories_DA categories_DA = new Categories_DA();

    Categories cat = categories_DA.fetchCategoryByName(catName);
    int idcat = 0;
    if (cat != null) {
        idcat = cat.getCatid();/*w  w w .  j  ava 2s  .  co m*/
    }
    System.out.println("cat=>" + idcat + "cat=>" + catName);
    if (prodName == null) {
        prodName = "";
    }
    Query query = session.createSQLQuery("Select * from PRODUCT_TBL where PRODNAME LIKE '%" + prodName
            + "%' OR " + "CATEGORY_CATID = " + idcat + " AND status = 1 ");
    List list = query.list();
    ArrayList<Products> prodList = new ArrayList<>();
    for (int i = 0; i < list.size(); i++) {
        Products prod = new Products();
        Object[] obj = (Object[]) list.get(i);
        prod.setProdid((Integer) obj[0]);
        prod.setDateOfAddition((Date) obj[1]);
        prod.setBarCode((String) obj[2]);
        prod.setModTime((Date) obj[3]);
        prod.setModifiedBy((String) obj[4]);
        prod.setProdCost((Double) obj[5]);
        prod.setProdDesc((String) obj[6]);
        prod.setProdName((String) obj[7]);
        prod.setProdQty((Double) obj[8]);
        prod.setStatus((Integer) obj[10]);
        prod.setCategory((Categories) categories_DA.fetchCategoryById((Integer) obj[12]));

        prod.setQrCode((String) obj[9]);
        prod.setUom((String) obj[11]);

        prodList.add(prod);
    }
    //         query = session.createQuery("from Products  where status=:status  AND prodName LIKE :name OR category.catName LIKE :catName");
    //        query.setParameter("status", 1);
    //        query.setParameter("name", prodName);
    //        query.setParameter("catName", catName);
    //        List<Products> prodList = (List<Products>) query.list();
    System.out.println("prodList size=>" + prodList.size());
    ArrayList<Products> productList = new ArrayList<>();

    List<Products> pList = prodList;

    for (int i = 0; i < pList.size(); i++) {
        Products pr = prodList.get(i);

        System.out.println("prod=>" + pr);
        double qty = pr.getProdQty();
        double cost = pr.getProdCost();
        System.out.println("prodName=>" + prodName);

        System.out.println("prodCost=>" + pr.getProdCost() + "maxPrice=>" + maxPrice + "comp=>"
                + (((pr.getProdCost() <= maxPrice))));
        if ((idcat != 0 && pr.getCategory().getCatid() == idcat) || !prodName.isEmpty()
                || ((qty <= maxQty && maxQty != 0) && qty >= minQty)
                || (cost >= minPrice && (cost <= maxPrice && maxPrice != 0))) {

            productList.add(pr);
        } else {
            // prodList.remove(pr);
        }
    }

    //        prodList.stream().forEach((pr) -> {
    //
    //            System.out.println("prodCost=>" + pr.getProdCost() + "comp=>" + ((pr.getProdCost() >= minPrice && (pr.getProdCost() <= maxPrice && maxPrice != 0))));
    //
    //            
    //            
    //            if (((pr.getProdQty() <= maxQty && maxQty != 0) && pr.getProdQty() >= minQty) || (pr.getProdCost() >= minPrice && (pr.getProdCost() <= maxPrice && maxPrice != 0))) {
    //                prodList.add(pr);
    //            } else {
    //                // prodList.remove(pr);
    //            }
    //        });
    beginTransaction.commit();
    return productList;
}

From source file:bank.DAO.CardDAO.java

public void deleteCards(List<Card> result) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();//from w w w .j a  va2 s. c  om

    for (Card p : result) {
        System.out.println("Delete:" + p.getNumber() + ":" + p.getStart_date());
        session.delete(p);
        //session.flush();
    }
    session.createSQLQuery("delete from card where number = 1").executeUpdate();
    session.getTransaction().commit();
}

From source file:basedistribuida.connection.HibernateSession.java

protected <T> List<T> getAllByQuery(Class<T> clazz, String query) {
    Session session = sessionFactory.openSession();
    List<T> generics;/*from  ww  w .j a v  a  2  s. c  o m*/
    try {
        session.beginTransaction();

        generics = session.createSQLQuery(query).addEntity(clazz).list();

        session.getTransaction().commit();
    } finally {
        session.close();
    }
    return generics;
}

From source file:basedistribuida.connection.HibernateSession.java

protected <T> T getFirstByQuery(Class<T> clazz, String query) {
    Session session = sessionFactory.openSession();
    T generic = null;//  www .j a  v  a 2 s. com
    try {
        session.beginTransaction();

        List list = session.createSQLQuery(query).addEntity(clazz).list();
        if (list != null && !list.isEmpty()) {
            generic = (T) list.get(0);
        }

        session.getTransaction().commit();
    } finally {
        session.close();
    }
    return generic;
}

From source file:baza.Broker.java

public int vratiNajveceg() {
    int br;/*w ww.j a  v a2 s . com*/
    Session session = Test.getSessionFactory().openSession();
    session.beginTransaction();
    String sql = "SELECT MAX(BrojPregleda) FROM pregled";

    SQLQuery query = session.createSQLQuery(sql);
    br = (Integer) query.uniqueResult();

    session.close();
    return br;

}

From source file:bgroup.oracle.dao.ContractDaoImpl.java

@Override
public Contract getContract(int PATIENT_ID) {
    logger.debug("Start getDog");

    Session session = getSession();
    logger.debug("session:" + session.toString());
    Query query = session.createSQLQuery("SELECT  p.KeyID as keyId"
            + ",(SELECT text FROM lu WHERE keyid = 93114) AS \"doverennost\""
            + ",(SELECT text FROM lu WHERE keyid = 93114) AS \"doverennost2\""
            + ",(SELECT text FROM lu WHERE keyid = 93114) AS \"doverennost3\""
            + ",(SELECT text FROM lu WHERE keyid = 93114) AS \"doverennost4\""
            + ",(SELECT text FROM lu WHERE keyid = 93116) AS \"license\""
            + ",(SELECT text FROM lu WHERE keyid = 93116) AS \"license2\""
            + ",(SELECT text FROM lu WHERE keyid = 93116) AS \"license3\""
            + ",(SELECT text FROM lu WHERE keyid = 93116) AS \"license4\""
            + ",fn_pat_name_by_id (p.KeyID) AS \"fio\"" + ",fn_pat_name_by_id (p.KeyID) AS \"fio2\""
            + ",fn_pat_name_by_id (p.KeyID) AS \"fio3\"" + ",fn_pat_name_by_id (p.KeyID) AS \"fio4\""
            + ",fn_pat_name_by_id (p.KeyID) AS \"fio5\"" + ",fn_pat_name_by_id (p.KeyID) AS \"fio6\""
            + ",p.num AS patnum" + ",p.num AS patnum2" + ",pkg_kladr.get_address(P.KeyID,1) AS \"region1\""
            + ",pkg_kladr.get_address(P.KeyID,1) AS \"address\""
            + ",pkg_kladr.get_address(P.KeyID,1) AS \"address2\""
            + ",pkg_kladr.get_address(P.KeyID,1) AS \"address3\""
            + ",pkg_kladr.get_address(P.KeyID,1) AS \"address4\"" + ",fn_pat_phone_by_id(p.keyid) AS phone"
            + ",fn_pat_phone_by_id(p.keyid) AS phone2" + ",decode (p.sex, 1, '.','.') AS sex_name"
            + ",fn_man_by_id (gsp_get_user_id()) AS registrator"
            + ",fn_man_by_id (gsp_get_user_id()) AS registrator2"
            + ",fn_man_by_id (gsp_get_user_id()) AS registrator3"
            + ",fn_man_by_id (gsp_get_user_id()) AS registrator4"
            + ",fn_man_by_id (gsp_get_user_id()) AS registrator5"
            + ",TO_CHAR (p.birthdate, 'dd-mm-yyyy') AS birth_date"
            + ", NVL ((SELECT c.text FROM company c,agr a WHERE a.keyid = p.agrid AND c.keyid = a.companyid),'') AS company"
            + ", NVL ((SELECT MAX('? ' || l.ser || ' N ' || l.code) FROM police l,agr a WHERE a.keyid = p.agrid "
            + "AND l.patientid = p.keyid AND l.agrid = a.keyid AND NVL (l.status,0) <> 1),'') AS police"
            + ", '' AS privils\n" + ",P.CELLULAR\n" + "FROM PATIENT p WHERE p.keyid = :PATIENT_ID")
            .setResultTransformer(Transformers.aliasToBean(Contract.class))
    //.addEntity(Contract.class)
    ;//from   ww w  .j  a  v a 2s.c  o  m
    query.setParameter("PATIENT_ID", PATIENT_ID);
    //List<Object[]> result = null;
    List<Contract> result = null;
    try {
        result = query.list();
    } catch (Exception e) {
        logger.error(e.toString());
    }
    Contract contract = null;
    if (result != null && result.size() == 1) {
        logger.info("- ");
        return result.get(0);
        //for (Object[] row : result) {
        //  for (Contract row : result) {
        //contract = new Contract();
        //    try {
        //   contract = (Contract) row[0];
        // for (Object obj : row) {
        //   System.out.print(obj + "::");
        // }

        /*         int i = 0;
                
            contract.setKeyId(Integer.parseInt(row[i++].toString()));
            contract.setDoverennost(getRowValue(row[i++]));
            contract.setDoverennost2(getRowValue(row[i++]));
            contract.setDoverennost3(getRowValue(row[i++]));
            contract.setDoverennost4(getRowValue(row[i++]));
            contract.setLicense(getRowValue(row[i++]));
            contract.setLicense2(getRowValue(row[i++]));
            contract.setLicense3(getRowValue(row[i++]));
            contract.setLicense4(getRowValue(row[i++]));
            contract.setFio(getRowValue(row[i++]));
            contract.setFio2(getRowValue(row[i++]));
            contract.setFio3(getRowValue(row[i++]));
            contract.setFio4(getRowValue(row[i++]));
            contract.setFio5(getRowValue(row[i++]));
            contract.setFio6(getRowValue(row[i++]));
            contract.setPatnum(getRowValue(row[i++]));
            contract.setPatnum2(getRowValue(row[i++]));
            contract.setRegion1(getRowValue(row[i++]));
            contract.setAddress(getRowValue(row[i++]));
            contract.setAddress2(getRowValue(row[i++]));
            contract.setAddress3(getRowValue(row[i++]));
            contract.setAddress4(getRowValue(row[i++]));
            contract.setPhone(getRowValue(row[i++]));
            contract.setPhone2(getRowValue(row[i++]));
            contract.setSEX_NAME(getRowValue(row[i++]));
            contract.setRegistrator(getRowValue(row[i++]));
            contract.setREGISTRATOR2(getRowValue(row[i++]));
            contract.setREGISTRATOR3(getRowValue(row[i++]));
            contract.setREGISTRATOR4(getRowValue(row[i++]));
            contract.setREGISTRATOR5(getRowValue(row[i++]));
            contract.setBIRTH_DATE(getRowValue(row[i++]));
            contract.setCOMPANY(getRowValue(row[i++]));
            contract.setPOLICE(getRowValue(row[i++]));
            contract.setPRIVILS(getRowValue(row[i++]));
            contract.setCELLULAR(getRowValue(row[i++]));
            //System.out.println("\n");
            */
        //    } catch (Exception e) {
        //      logger.error(e.toString());
        //}

        // }
    } else {
        logger.info("  ");
    }
    return null;
}

From source file:bgroup.oracle.dao.HelpFioDaoImpl.java

@Override
public HelpFio getHelpFio(int PATIENT_ID) {
    Session session = getSession();
    Query query = session.createSQLQuery("SELECT \n" + "       fn_pat_name_by_id(keyid) AS fio, \n"
            + "       fn_pat_name_by_id(keyid) AS fio2, \n" + "       fn_pat_name_by_id(keyid) AS fio3, \n"
            + "       fn_pat_name_by_id(keyid) AS fio5, \n"
            + "(SELECT text FROM lu WHERE keyid = 93116) AS \"license\",\n"
            + "       fn_pat_name_by_id(keyid) AS fio4,\n"
            + "\t/*fn_man_by_id (gsp_get_user_id()) AS registrator,*/\n" + "\tSex as pol,\n"
            + "\tNVL(snils, NULL ) AS snils,\n" + "\tNVL(snils, NULL ) AS snils1,\n"
            + "\ts_spravka_num.nextval AS N,\n" + "\ts_spravka_num.nextval AS N2,\n" + "       num AS PATNUM,\n"
            + "       TO_CHAR(SYSDATE,'dd.mm.yyyy') AS GIVEDATE,\n"
            + "       TO_CHAR(SYSDATE,'dd.mm.yyyy') AS GIVEDATE2\n"
            + "FROM patient WHERE keyid = :PATIENT_ID\n")
            .setResultTransformer(Transformers.aliasToBean(HelpFio.class));
    query.setParameter("PATIENT_ID", PATIENT_ID);
    //List<Object[]> result = null;
    List<HelpFio> result = null;
    try {//from   w  ww  .  j  a v a 2s. co  m
        result = query.list();
    } catch (Exception e) {
        logger.error(e.toString());
    }
    Contract contract = null;
    if (result != null && result.size() == 1) {
        return result.get(0);
    } else {
        logger.info("  ");
    }
    return null;
}

From source file:bookstore.BookStoreManager.java

public void deleteManyToMany() throws IOException {
    try {//  w  w w  . ja v a  2  s  .  c  om
        //Tao session va bat dau lam viec voi session do
        Session session = sf.openSession();
        session.beginTransaction();

        //nguoi dung nhap ma cuon sach muon xoa
        BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
        System.out.println("Input ID Book want delete");
        int isbm = Integer.parseInt(input.readLine());

        //Viet cau lenh sql de xoa lien ket giua cuon sach voi tac gia trong bang bookauthor
        String sql = "delete from bookauthor where isbm = :isbm";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter("isbm", isbm);
        query.executeUpdate();

        //Viet cau lenh xoa mot cuon sach co ma isbm vua nhap
        sql = "delete from book where isbm =:isbm";
        query = session.createSQLQuery(sql);
        //set paramete cho cau truy van tren
        query.setParameter("isbm", isbm);
        //dung executeUpdate() de thuc thi cau truy van
        query.executeUpdate();
        session.getTransaction().commit();
        //sau khi thuc hien xong cong viec thi dong session lai
        session.close();
        System.out.println("done");
        //            hql = "delete from Book where isbm =:isbm";
        //            q1 = session.createQuery(hql);
        //            q1.setParameter("isbm", isbm);
        //            q1.executeUpdate();

        session.getTransaction().commit();
        session.close();
        System.out.println("Done");
    } catch (Exception e) {
    }
}