CODEWITHSUNDEEP
javamysqlhibernatejpa
Mavve
Mavve

Reputation: 9

create and populate pojos in jpql query and get as list

I have following entities and pojo:

@Entity
public class TableA {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
long id;
string name;
}

@Entity
public class TableB {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
long id;
double price;
@ManyToOne
@JoinColumn(name = "tableAId")
TableA tableA;

//setters & getters
}

statistics

public class Statistics {
long tableAId;
double price;
long count;

public Statistics(long tableAId, double price, long count) {
this.tableAId = tableAId;
this.price = price;
this.count = count;
}

//setters & getters
}

I want to do a jpql query to get resultlist of statistics objects which is populated with reference id to tableA object and sum of price columns and count of rows in TableB table.

I've tried with following code without success:

Query query =  em.createQuery("SELECT NEW se.exampel.Statistics"
+ "(b.tableAId, sum(price) ,count(b)) from TableB b ");

List<Statistics> statistics = query.getResultList();

Exception

java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: tableAId of: se.exampel.TableB [SELECT NEW se.exampel.Statistics(b.tableAId,count(b), sum(price)) from se.exampel.TableB b ]

What am i doing wrong?

now it's fixed: "select new se.exampel.Statistic(s.id, sum(p.price),count(p)) from tabelB p JOIN p.tabelA s GROUP BY s"

Upvotes: 1

Views: 1943

Answers (2)

Yogesh
Yogesh

Reputation: 4784

query should contain b.tableA which is property name instead of column name tableAId

Update: with reference to comment from @Chris query should be

SELECT NEW se.exampel.Statistics(b.tableA.id,sum(b.price),count(b)) from TableB b

Upvotes: 0

Chris
Chris

Reputation: 21145

You are mixing SQL concepts into JPQL. The query needs to me made on Entity TableB, and so can only use mapped attributes within the TableB java class. So you will need to use something like:

"SELECT NEW se.exampel.Statistics(b.tableA.id, sum(b.price) ,count(b)) from TableB b "

Note that Hibernate is likely to do an inner join from tableB to tableA to get A's ID. If you want to be able to access the foreign key field in TableB directly in a JPA neutral way, you may need to add a read-only field in the TableB class for it. Something like 

@Column(name="tableA_ID", insertable=false, updatable=false);
long tableAId;

which then allows you to access b.tableAId in queries:

"SELECT NEW se.exampel.Statistics(b.tableAId, sum(b.price) ,count(b)) from TableB b "

and should avoid the table join.

Upvotes: 1

Related Questions