CODEWITHSUNDEEP
javaspringhibernatespring-boot
Sergey Babinsky
Sergey Babinsky

Reputation: 151

Why Hibernate doesn't return anything from the Native Query

I have an application based on Hibernate 4.2 and Spring Boot 1.4. And I have a very specific SQL query which I can not model with HQL in a performant way.

log.debug("Request to get all current Bids for station : code {}, bidType {}, versionNum {}", code, bidType, grainProAdminProperties.getPrice().getCurrentVersionNumber());

List<Object[]> result = sessionFactory.getCurrentSession().createSQLQuery(
        "select bid.*, tp.price as tp_price, tp.price_nds as tp_priceNds " +
        "from bid, transportation_price tp, station_location lts, partner part, station stat " +
        "where " +
        "   bid.is_active = true and" +
        "   bid.archive_date is null and " +
        "   part.id = bid.elevator_id and " +
        "   part.station_id = stat.id and " +
        "   lts.region_id = stat.region_id and " +
        "   lts.district_id = stat.district_id and " +
        "   (stat.locality_id is null or " +
        "   lts.locality_id = stat.locality_id) and " +
        "   ((cast(tp.station_from_code as text) = lts.code and " +
        "     cast(tp.station_to_code as text) = cast(:code as text)) " +
        " or " +
        "    (cast(tp.station_to_code as text) = lts.code and " +
        "     cast(tp.station_from_code as text) = cast(:code as text))) and " +
        "    cast(bid.bid_type as text) like cast(:bidType as text) and " +
        "    cast(tp.version_number as int) = cast(:versionNumber as int)").
        setResultTransformer(
            new ResultTransformer() {
                @Override
                public Object transformTuple(Object[] tuple, String[] aliases) {
                    log.warn("Transform tuple: {}, aliases {}", tuple, aliases);
                    return null;
                }

                @Override
                public List transformList(List collection) {
                    return collection;
                }
            }
        ).
        setParameter("code", code).
        setParameter("versionNumber", grainProAdminProperties.getPrice().getCurrentVersionNumber()).
        setParameter("bidType", bidType).
        list();

    log.debug("Result of request: {}", result);

In the log file I can see:

Request to get all current Bids for station : code 865065, bidType BUY, versionNum 2
Hibernate: select bid.*, tp.price as tp_price, tp.price_nds as tp_priceNds from bid, transportation_price tp, station_location lts, partner part, station stat where    bid.is_active = true and   bid.archive_date is null and    part.id = bid.elevator_id and    part.station_id = stat.id and    lts.region_id = stat.region_id and    lts.district_id = stat.district_id and    (stat.locality_id is null or    lts.locality_id = stat.locality_id) and    ((cast(tp.station_from_code as text) = lts.code and      cast(tp.station_to_code as text) = cast(? as text))  or     (cast(tp.station_to_code as text) = lts.code and      cast(tp.station_from_code as text) = cast(? as text))) and     cast(bid.bid_type as text) like cast(? as text) and     cast(tp.version_number as int) = cast(? as int)
Result of request: []

So the returned result is empty. I'm trying to execute absolutely the same request directly in DB with the same parameters and I'm getting 3 results.

Could you please predict why it could be a case?

Upvotes: 1

Views: 820

Answers (1)

Sergey Babinsky
Sergey Babinsky

Reputation: 151

The problem was (as @GaëlMarziou said) in a binding. I'm using Enum BidType as an input parameter for this Query but it was not transformed to string by using standard toString method.

Upvotes: 1

Related Questions