• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Use custom query on JPA

 
Oscar Calderon
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi to all, i'm facing a problem with an application that i'm developing, it works with struts 2.1.8 and Hibernate JPA with database MySQL. My problem is when i want to make a complex query, specifically my query (SQL syntax) is this:



I read in other pages, forums and they talk about aggregate functions like count, sum and so on, but they expose simple queries like this:



Queries that return a single value, in my case i return more than one value, so in first place i don't know how to use an existing entity to map the results of my query, because in wich field of some entity going to put the value of the count() or the value of de sum()? Can i use a field that is irrelevant for me to map that value?

I've tried using the method of entityManager createQuery() and it works, but it returns me an array of objects and this is a little ugly because i have to access to each value with the array index and i need at least to have an array of hashmaps or something like that because i'm working with displaytag.

Thanks in advance.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You have a few options. If this is Hibernate you can get the Session from the EntityManager and then use HQL, in HQL they have the ability to create queries like

SELECT new ReportObject(PRV.NOMBRE AS nombreProveedor,
COUNT(PRV.IDPRV) AS cantTransacciones, SUM(MONTO) AS montoTransacciones)
FROM Proveedores PRV, Movimientos_Distribuidores MDS
WHERE PRV.IDPRV = MDS.IDPRV AND MDS.IDTMI = 1 GROUP BY PRV.IDPRV


You can also use a native SQL query and do an @ResultSetMapping annotation to map a result set to a domain object.

If you need documentation you can look at the Hibernate Annotations document at Hibernate, they show these annotations that are JPA annotations. Like @NamedNativeQuery

I am sure there are xml equivalents

Mark
 
Oscar Calderon
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Mark. What do you refeer when you say "domain object"? An entity class object? I've tried to use one of the existing entity classes to avoid work of build another class just for that but it gives me an error because it doesn't find idXXX, i think because according to persistence annotations i have to always get something for primary key.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic