convert SQL to JPQL

[argan ask question]

i have case make report, i tried test the SQL from phpMyadmin oke its work, but then i tried it in the controller its not work properly, the code like this:

SELECT brg.nama, DATE(c.tanggal), SUM(dp.jumlah) FROM DetailPenjualan dp JOIN penjualan c ON c.id = dp.penjualan_Id JOIN  barang brg ON dp.barang_Id = brg.id GROUP BY brg.nama, DATE(c.tanggal)

but the result in the browser like:

IllegalArgumentException occured : org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 98 [SELECT brg.nama, DATE(c.tanggal), SUM(dp.jumlah) FROM models.DetailPenjualan dp JOIN penjualan c ON c.id = dp.penjualan_Id JOIN barang brg ON dp.barang_Id = brg.id GROUP BY brg.nama, DATE(c.tanggal)] 

how can i convert it from SQL into JPQL

.?????

[juhe]

This has none to do with Play Framework, but there is no "ON" keyword in JPA. Google for "with" and "fetch" or just do a join without "ON".

[christian sarnataro]

Or (if you want to stick with your query with joins) try native queries.

See this thread:

http://stackoverflow.com/questions/7488026/playframework-querying-and-displaying-results-from-multi-table-join

But pay attention that native queries return object arrays (Object[]), not your populated model.

In general, relationships in JPA/JPQL are expressed with annotations, like @ManyToMany

Then hibernate creates and uses joins in SQL automatically.

See for example:

http://www.playframework.org/documentation/1.2.5/guide2#aclasspostThePostclassa