CODEWITHSUNDEEP
javasqlpostgresqlhibernate
Kael
Kael

Reputation: 381

How to use setParameter in Hibernate

I want to search all employees with the corresponding ids stored in the serialNumberList and add all their hours together. serialNumberList is a string containing the ids separated with commas. What happen is that it wasn't able to add all the hours of all the employees indicated in the serialNumberList. Here's a snippet of my code. Thanks.

Query

public double getPeMSummary(Date startDate, Date endDate, String serialNumberList) {
    SessionFactory sessionFactory = HBSessionFactory.getSessionFactory();
    Session sessObj = sessionFactory.openSession();
    sessObj.beginTransaction();
    String query = "SELECT SUM(CAST(pde.utilHours as float)) from PumDetailsEntity as pde WHERE"
            + " pde.utilDay >= :startDate AND"
            + " pde.utilDay <= :endDate AND"
            + " pde.serialNumber = (:serialNumberList) AND"
            + " (pde.utilHours NOT LIKE '%HO%' OR"
            + "  pde.utilHours NOT LIKE '%VL%' OR"
            + "  pde.utilHours NOT LIKE '%SL%'"
            + "  )";

    Iterator result = sessObj.createQuery(query).
            setParameter("startDate", startDate).
            setParameter("endDate", endDate).
            setParameter("serialNumberList", serialNumberList).list().iterator();

    sessObj.close();

    double ans = 0;
    if (result.hasNext()){
        Object obj = result.next();
        if (obj instanceof Double) {
            ans = (Double) obj;
        }
        System.out.println(obj+" "+ans);
    }
    return ans;
}

Sample Data

First: start date Second: end date Third: list of the ids separated with commas

2016-12-24 2017-01-27 'P100GEPH1,X91729PH1,P100F2PH1,P100FBPH1,123973PH1,P100C2PH1,123972PH1,112935PH1,P100F9PH1,P100F7PH1,P100F5PH1,147598PH1,P100F8PH1,P100F0PH1,P100GFPH1,113212PH1,P100ARPH1,134662PH1,148034PH1

Upvotes: 0

Views: 11317

Answers (3)

Uday
Uday

Reputation: 1215

You need to fix the following:

Your search criteria should be pde.serialNumber IN (:serialNumberList)

You need to change the parameter serial number to list and use:

setParameterList("serialNumberList", serialNumberList)

Upvotes: 1

coladict
coladict

Reputation: 5095

In addition to @YuVi's answer with IN, you also have to pass the list as an actual List, not a String.

Upvotes: 0

Kael
Kael

Reputation: 381

I change the String to String[] so that I could use setParameterList() as said by YuVi-san

Upvotes: 0

Related Questions