Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setParameter.

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

From source file:com.hrms.manager.GasManager.java

public boolean searchEmployeee(Gasdto emp) {
    Session s = HibernateUtil.getSessionFactory().openSession();
    Transaction t = s.beginTransaction();
    int empId = emp.getEmpId();
    //    String empName=emp.getEmpName();
    // Criteria cr=null;
    //     String aadharNumber=emp.getAadharNumber();

    try {/*w  ww. ja  v  a 2 s.  c o m*/
        String sql = "select quarter_id,emp_id from quarter_allotment where emp_id=:empId";
        SQLQuery query = s.createSQLQuery(sql);
        query.setParameter("empId", empId);

        List<QuarterAllotment> list = query.list();
        if (list.size() > 0) {
            s.close();
            return true;
        }
    } catch (HibernateException h) {
        System.out.println("HibernateException during Searching:" + h);
    } catch (Exception ex) {
        System.out.println("Exception during Searching:" + ex);
    } finally {
        //  s.close();
    }
    return false;
}

From source file:com.hrms.manager.GasManager.java

public int delete1(int empId) {
    Session s = HibernateUtil.getSessionFactory().openSession();
    Transaction t = s.beginTransaction();
    try {/*  ww  w .  j a v  a2s.c o m*/

        /*GasAllotment g=(GasAllotment)s.get(GasAllotment.class, empId);
        s.delete(g);*/
        String sql = "delete from gas_allotment  where emp_id=:empId";
        SQLQuery query = s.createSQLQuery(sql);
        query.setParameter("empId", empId);
        query.executeUpdate();
        t.commit();
    } catch (Exception e) {
        System.out.println(e);
    }
    return empId;
}

From source file:com.hrms.manager.QuarterManager.java

public int count(Quarterdto emp) throws SQLException {
    int count = 0;
    int empId = emp.getEmpId();
    List list = null;/*  w ww  . j a v  a 2 s  .c om*/
    Session s = HibernateUtil.getSessionFactory().openSession();
    try {
        String sql = "select count(d.dep_name) from dependents_information d where d.family_head_id=:empId";
        SQLQuery query = s.createSQLQuery(sql);
        query.setParameter("empId", empId);
        BigInteger count1 = (BigInteger) query.uniqueResult();
        count = count1.intValue();
        //            Criteria c=s.createCriteria(DependentsInformation.class);
        //          
        //            c.add(Restrictions.eq("familyIdentity.employeeProfile",empId)).uniqueResult();  
        //             c.setProjection(Projections.rowCount());
        //             BigInteger count1= (BigInteger)c.uniqueResult(); 
        //             count=count1.intValue();
        //             list=c.list();
        //             System.out.println("list is"+list);
        //List l=query.list();
    } catch (HibernateException h) {
        System.out.println("HibernateException during Edit:" + h);
    }
    //   System.out.println(count);
    return count;
}

From source file:com.hrms.manager.QuarterManager.java

public int count1(Quarterdto emp) throws SQLException {
    int count = 0;
    int empId = emp.getEmpId();
    Session s = HibernateUtil.getSessionFactory().openSession();
    try {//from   w ww  . j  a va  2  s . co  m
        String sql = "select count(d.dep_name) from dependents_information d where d.family_head_id=:empId and dep_status='yes'";
        SQLQuery query = s.createSQLQuery(sql);
        query.setParameter("empId", empId);
        BigInteger count1 = (BigInteger) query.uniqueResult();
        count = count1.intValue();
        //List l=query.list();
    } catch (HibernateException h) {
        System.out.println("HibernateException during Edit:" + h);
    }
    //  System.out.println(count);
    return count;
}

From source file:com.hrms.manager.QuarterManager.java

public List quarterCode(Quarterdto emp) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction tx = session.beginTransaction();
    List quartercode;//  w  w  w .  j  a  va  2 s .c  o m
    int empId = emp.getEmpId();
    String sql = null;
    String quarterType = null;
    HashMap<Integer, String> dept = new HashMap<>();
    List<EmployeeProfile> departments = (List<EmployeeProfile>) session.createCriteria(EmployeeProfile.class)
            .list();
    for (EmployeeProfile d : departments) {
        dept.put(d.getEmpId(), d.getQuarterType());
    }
    Iterator it = dept.entrySet().iterator();
    while (it.hasNext()) {
        Map.Entry<Integer, String> pair = (Map.Entry) it.next();
        //  System.out.println(pair.getKey()+ pair.getValue());
        if (empId == pair.getKey()) {
            quarterType = pair.getValue();
            System.out.println(quarterType);
        }
        it.remove();
    }
    List<QuarterAllotment> list1 = null;
    List<QuarterAllotment> list = (List<QuarterAllotment>) session.createCriteria(QuarterAllotment.class)
            .list();
    if (list.isEmpty()) {
        //         Criteria cr = session.createCriteria(ResidentialQuarters.class)
        //    .setProjection(Projections.projectionList()
        //             .add(Projections.property("quarterCode"), "quarterCode")).add(Restrictions.eq("quarterType","quarterType"));
        //      
        sql = "select quarter_code from residential_quarters where quarter_type=:quarterType";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter("quarterType", quarterType);
        quartercode = query.list();
        list1 = query.list();
        System.out.println(list1);
        return list1;
    } else
        sql = "select distinct(r.quarter_code) from residential_quarters r,quarter_allotment q where r.quarter_type=:quarterType and r.quarter_id not in (select quarter_id from quarter_allotment)";
    SQLQuery query = session.createSQLQuery(sql);
    query.setParameter("quarterType", quarterType);
    quartercode = query.list();

    //     System.out.println(quartercode);
    return quartercode;
}

From source file:com.hrms.manager.QuarterManager.java

public int delete(int quarterId) {
    Session s = HibernateUtil.getSessionFactory().openSession();
    Transaction t = s.beginTransaction();
    System.out.println(quarterId);
    try {//from w w  w. j  a  v  a 2 s.c  o  m
        //QuarterAllotment q=(QuarterAllotment)s.get(QuarterAllotment.class,quarterId);
        //     ResidentialQuarters q=(ResidentialQuarters)s.get(ResidentialQuarters.class, quarterId);
        String sql = "delete from  quarter_allotment  where quarter_id=:quarterId";
        SQLQuery query = s.createSQLQuery(sql);
        query.setParameter("quarterId", quarterId);
        query.executeUpdate();

        t.commit();
    } catch (Exception e) {
        System.out.println(e);
    }
    return quarterId;
}

From source file:com.ibm.asset.trails.dao.jpa.AlertUnlicensedSoftwareDAOJpa.java

@SuppressWarnings("unchecked")
public List<Long> findAffectedAlertList(Long accountId, Long productInfoId, boolean overwriteAuto,
        boolean overwriteManual, String owner, boolean includeOpenAlerts) {
    List<Object[]> queryResult = new ArrayList<Object[]>();
    ArrayList<Long> lallAlertUnlicensedSwId = new ArrayList<Long>();
    String lsOwnerFromClause = null;
    String lsOwnerWhereClause = null;
    StringBuffer lsbQuery = new StringBuffer();

    if (!owner.equals("ALL")) {
        lsOwnerFromClause = ", EAADMIN.Hw_Sw_Composite HSC, EAADMIN.Hardware_Lpar HL, EAADMIN.Hardware H";
        lsOwnerWhereClause = "AND HSC.Software_Lpar_Id = SL.Id AND HL.Id = HSC.Hardware_Lpar_Id AND H.Id = HL.Hardware_Id AND H.Owner = :owner ";
    } else {//from w  ww .j av a2 s.co  m
        lsOwnerFromClause = ", EAADMIN.Hw_Sw_Composite HSC";
        lsOwnerWhereClause = "AND HSC.Software_Lpar_Id = SL.Id";
    }

    if (includeOpenAlerts) {
        // This query will get all open alerts that meet our criteria. The
        // manual
        // and automated variables do not come into play
        lsbQuery.append(
                "SELECT AUS.Id, AUS.CREATION_TIME FROM EAADMIN.Alert_Unlicensed_Sw AUS, EAADMIN.Installed_Software IS, EAADMIN.Software_Lpar SL, EAADMIN.Software SW")
                .append(lsOwnerFromClause)
                .append(" WHERE AUS.Open = 1 AND SL.Customer_Id = :customerId AND SW.Software_Id = :softwareItemId AND IS.Id = AUS.Installed_Software_Id AND SL.Id = IS.Software_Lpar_Id AND SW.Software_Id = IS.Software_Id ")
                .append(lsOwnerWhereClause);
    }

    if (overwriteAuto) {
        lsbQuery.append(lsbQuery.length() > 0 ? " UNION " : "").append(
                "SELECT AUS.Id, AUS.CREATION_TIME FROM EAADMIN.Alert_Unlicensed_Sw AUS, EAADMIN.Installed_Software IS, EAADMIN.Software_Lpar SL, EAADMIN.Software SW, EAADMIN.Reconcile R, EAADMIN.Reconcile_Type RT")
                .append(lsOwnerFromClause)
                .append(" WHERE SL.Customer_Id = :customerId AND SW.Software_Id = :softwareItemId AND IS.Id = AUS.Installed_Software_Id AND SL.Id = IS.Software_Lpar_Id AND SW.Software_Id = IS.Software_Id AND R.Installed_Software_Id = IS.Id AND RT.Id = R.Reconcile_Type_Id AND RT.Is_Manual = 0 ")
                .append(lsOwnerWhereClause);
    }

    if (overwriteManual) {
        lsbQuery.append(lsbQuery.length() > 0 ? " UNION " : "").append(
                "SELECT AUS.Id, AUS.CREATION_TIME FROM EAADMIN.Alert_Unlicensed_Sw AUS, EAADMIN.Installed_Software IS, EAADMIN.Software_Lpar SL, EAADMIN.Software SW, EAADMIN.Reconcile R, EAADMIN.Reconcile_Type RT")
                .append(lsOwnerFromClause)
                .append(" WHERE SL.Customer_Id = :customerId AND SW.Software_Id = :softwareItemId AND IS.Id = AUS.Installed_Software_Id AND SL.Id = IS.Software_Lpar_Id AND SW.Software_Id = IS.Software_Id AND R.Installed_Software_Id = IS.Id AND RT.Id = R.Reconcile_Type_Id AND RT.Is_Manual = 1 ")
                .append(lsOwnerWhereClause);
    }

    if (lsbQuery.length() > 0) {
        SQLQuery query = ((Session) entityManager.getDelegate()).createSQLQuery(lsbQuery.toString());
        if (!owner.equals("ALL")) {
            queryResult.addAll(query.setParameter("customerId", accountId)
                    .setParameter("softwareItemId", productInfoId).setParameter("owner", owner).list());
        } else {
            queryResult.addAll(query.setParameter("customerId", accountId)
                    .setParameter("softwareItemId", productInfoId).list());
        }

        List<AlertTime> alertIdWithTime = new ArrayList<AlertTime>();
        for (Object[] obj : queryResult) {
            AlertTime at = new AlertTime();
            at.id = (BigInteger) obj[0];
            at.creationTime = (Timestamp) obj[1];
            alertIdWithTime.add(at);
        }

        Collections.sort(alertIdWithTime);

        for (AlertTime alertTime : alertIdWithTime) {
            lallAlertUnlicensedSwId.add(alertTime.id.longValue());
        }

    }

    return lallAlertUnlicensedSwId;
}

From source file:com.iluwatar.cqrs.queries.QueryServiceImpl.java

License:Open Source License

@Override
public Author getAuthorByUsername(String username) {
    Author authorDTo = null;/*from www  . jav  a 2  s  . c  o  m*/
    try (Session session = sessionFactory.openSession()) {
        SQLQuery sqlQuery = session
                .createSQLQuery("SELECT a.username as \"username\", a.name as \"name\", a.email as \"email\""
                        + "FROM Author a where a.username=:username");
        sqlQuery.setParameter("username", username);
        authorDTo = (Author) sqlQuery.setResultTransformer(Transformers.aliasToBean(Author.class))
                .uniqueResult();
    }
    return authorDTo;
}

From source file:com.iluwatar.cqrs.queries.QueryServiceImpl.java

License:Open Source License

@Override
public Book getBook(String title) {
    Book bookDTo = null;//from w w  w .  j a  v  a  2  s . c o  m
    try (Session session = sessionFactory.openSession()) {
        SQLQuery sqlQuery = session.createSQLQuery(
                "SELECT b.title as \"title\", b.price as \"price\"" + " FROM Book b where b.title=:title");
        sqlQuery.setParameter("title", title);
        bookDTo = (Book) sqlQuery.setResultTransformer(Transformers.aliasToBean(Book.class)).uniqueResult();
    }
    return bookDTo;
}

From source file:com.iluwatar.cqrs.queries.QueryServiceImpl.java

License:Open Source License

@Override
public List<Book> getAuthorBooks(String username) {
    List<Book> bookDTos = null;
    try (Session session = sessionFactory.openSession()) {
        SQLQuery sqlQuery = session.createSQLQuery("SELECT b.title as \"title\", b.price as \"price\""
                + " FROM Author a , Book b where b.author_id = a.id and a.username=:username");
        sqlQuery.setParameter("username", username);
        bookDTos = sqlQuery.setResultTransformer(Transformers.aliasToBean(Book.class)).list();
    }//from   ww w.  ja v a  2 s. com
    return bookDTos;
}