CODEWITHSUNDEEP
javamysqlhibernatejpanamed-query
Shravan DG
Shravan DG

Reputation: 537

JPA named query with group by with paramnames and values

I am new to Hibernate and I need to write a JPA named query on my entity using Group by.

So this is what my entity looks like:

@Entity
@NamedQueries({
@NamedQuery(name="Transaction.getByYear",
        query="from Transaction where year=:year"),
@NamedQuery(name="Transaction.ByTimestamp",
        query="from Transaction where year=:year order by Timestamp desc"),
@NamedQuery(name="Transaction.ByActiveStatusAndTimestamp",
query="from Transaction where year=:year and itemStatus=:itemStatus order by Timestamp desc"),
@NamedQuery(name="Transaction.sumByYear",
        query="SELECT SUM(amount) from Transaction where year=:year")
})
public class Transaction {
@Id
@GeneratedValue
@Column(unique = true)
private int itemId;

@Column
private String itemName;

@Column
private int amount;

@Column
@Enumerated(EnumType.STRING)
private Category itemCategory;
}

EnumType Categories has various types like food, accommodation, travel etc. (10 types)

This is my Category enum:

public enum Category {


ONBOARDING("Onboarding"),


EDUCATION("Education"),


EQUIPMENT("Equipment"),


SOFTWARE("Software");

private final String category;

    Category(final String category){
    this.category = category;
}

@Override
public String toString() {
    return category;
}

}

I need to have a query to compute sum of amount spent one ach of these categories.

One way is to have a named query above my Transaction entitylike this:

@NamedQuery(name="Transaction.sumByFood", query="SELECT SUM(amount) from Transaction group by Category.FOOD")

(I guess this is how we give an enum in the named query directly. please correct me if I am wrong)

Then I need to have 10 such named queries for 10 categories.

Is there any way I can write a named query which allows me send param names and values to a group by something like this:

@NamedQuery(name="Transaction.sumByFood", query="SELECT SUM(amount) from Transaction group by itemCategory=:itemCategory")

And then pass the category I want to get the sum? Please help me with this.

Upvotes: 2

Views: 6568

Answers (1)

Shravan DG
Shravan DG

Reputation: 537

Got it working. First we have to mention that we need to have the output grouped by a particular column and then we need to give the constraint on that column in the having clause. This is the named query I wrote:

@NamedQuery(name="Transaction.sumByCategory",
        query="SELECT SUM(amount) from Transaction group by itemCategory having itemCategory=:itemCategory")

P.S: I work on PostgreSQL in the backend and that's why I had to write it in a different manner.

Upvotes: 1

Related Questions