CODEWITHSUNDEEP
javahibernatejpaannotations
spathirana
spathirana

Reputation: 155

JPA how to query the database without creating a table

I am using JPA and Spring for my db tasks and I need to have a join query like below in the JPA Repo class

@Query("SELECT 1 as id, COUNT(bill) as bills, ba.resource, MAX(b.updatedAt) as latestdate FROM Bill b join b.billComp ba where ba.comp.comp = ?1  group by ba.resource")
public List<BillCalc> findByBills(Long comp);

My Entity class is as below

@Entity
public class BillCalc {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

@Column(name = "latestdate", nullable = false)
private Date latestdate;

@Column(name = "bills", nullable = false)
private Long bills;

@Column(name = "resource", nullable = false)
private String resource;

I cannot create a table for this and can someone help me in getting the mapping to work? It gives me an error saying cannot cast from Object to BillCalc.

I tried @SubSelect but it does not take parameters

Upvotes: 1

Views: 763

Answers (1)

Ralph
Ralph

Reputation: 120771

  1. add a constructor to BillCalc.

    BillCalc(Integer id, long bills, String resouce, Date latestdate) {...}

  2. then use a Select new query:

    SELECT new BillCalc(1, COUNT(bill), ba.resource, MAX(b.updatedAt)) 
    FROM Bill b join b.billComp ba
    WHERE ba.comp.comp = ?1  group by ba.resource")

@See Chapter 4.8.2 "Constructor Expressions in the SELECT Clause" in JSR-000220 Enterprise JavaBeans 3.0 Final Release (persistence)

Upvotes: 1

Related Questions