CODEWITHSUNDEEP
jpajpql
Mekswoll
Mekswoll

Reputation: 1433

Using COUNT in JPQL Query

I have the following JPQL query:

    List<DestinationInfo> destinations = em.createQuery("SELECT NEW com.realdolmen.patuva.dto.DestinationInfo(d.name, d.continent, MIN(t.departureDate), MIN(t.pricePerDay), COUNT(t.id))" +
            " FROM Destination d, Trip t" +
            " WHERE d.continent = :continent " +
            " GROUP BY d.name, d.continent").setParameter("continent", searchedContinent).getResultList();

If I run this I get the error:

javax.ejb.EJBTransactionRolledbackException: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [com.realdolmen.patuva.dto.DestinationsList]

If I leave out the COUNT(t.id) and remove that parameter from my DestinationInfo constructor it works fine. Why can't I map the COUNT(t.id) to my DestinationInfo DTO.

This is my DestinationInfo class:

public class DestinationInfo {
    private String name;
    private Continent continent;
    private Date earliestDeparture;
    private Integer totalNumTrips;
    private BigDecimal lowestPrice;

    public DestinationInfo(String name, Continent continent, Date earliestDeparture, BigDecimal lowestPrice, Integer totalNumTrips) {
        this.name = name;
        this.continent = continent;
        this.earliestDeparture = earliestDeparture;
        this.totalNumTrips = totalNumTrips;
        this.lowestPrice = lowestPrice;
    }

    // getters and setters
}

Upvotes: 1

Views: 4986

Answers (1)

Mekswoll
Mekswoll

Reputation: 1433

Apparently COUNT(t.id) returns a number of type long. Changing the DestinationInfo class to the following makes it work:

public class DestinationInfo {
    private String name;
    private Continent continent;
    private Date earliestDeparture;
    private long totalNumTrips;
    private BigDecimal lowestPrice;

    public DestinationInfo(String name, Continent continent, Date earliestDeparture, BigDecimal lowestPrice, long totalNumTrips) {
        this.name = name;
        this.continent = continent;
        this.earliestDeparture = earliestDeparture;
        this.totalNumTrips = totalNumTrips;
        this.lowestPrice = lowestPrice;
    }

    // getters and setters
}

Upvotes: 2

Related Questions