1

I have hibernate query, where I need to transform one column with date_trunc, however I don't know how to bind this transformed value to my joined entity object

Is there any possibility to pass this transformed column to mine hibernate entity?

Of course it's conceptual code in case it wouldn't make sens for you:

    @Entity
    class SomeClass {
         private ZonedDateTime orderTime;

         @ManyToOne(targetEntity = SomeStats.class)
         @JoinColumn(name = "SOME_STATS_ID", referencedColumnName="ID")
         private SomeStats someStats; // Class that I need aggregate

         private long id;

         // ... other fields that aren't grouped
   }

   class SomeStats {
       private long id;
       private double price; // I need to take average of this
       private ZonedDateTime paymentTime; // And truncate this
   }

I need some idea to make something similar to:

SELECT someclass.*, AVG(somestats.price), date_trunc('hour', somestats.payment_time) as timebucket
FROM SOME_CLASS someclass 
JOIN SOME_STATS somestats ON somestats.id = someclass.some_stats_id
GROUP BY someclass.id, timebucket

but with average as SomeStats.price and truncated date as SomeStats.paymentTime, is it even possible?

kkot
  • 477
  • 2
  • 6
  • 13
  • 1
    You can use jpa [AttributeConverter](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#basic-jpa-convert) or hibernate [custom type](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#basic-custom-type). – SternK Dec 30 '19 at 12:23
  • I'd like to, however I use this only for selects in that **one** particular query... – kkot Dec 30 '19 at 12:25
  • Could you please provide a query (at least simplified version). – SternK Dec 30 '19 at 12:27
  • 1
    I suppose `@Formula` can help: https://stackoverflow.com/questions/2986318/how-to-map-calculated-properties-with-jpa-and-hibernate – Alexander Terekhov Dec 30 '19 at 12:32
  • Share your code if possible – Harry Coder Dec 30 '19 at 15:24
  • Ok, I've made some conceptual entities. – kkot Dec 31 '19 at 07:02
  • Where do you get this error "SQL Error: 0, SQLState: 42703 The column name time was not found in this ResultSet." Please show the query and the code – Simon Martinelli Jan 02 '20 at 10:49
  • @SimonMartinelli - It wasn't related, I'm sorry... My initial assumption was wrong. I need to aggregate joined entity field in typesafe way, is there any chance? – kkot Jan 03 '20 at 08:19
  • I dont' understand your requirement. Can you please add some more details – Simon Martinelli Jan 03 '20 at 08:27
  • I want to get joined entity as object in java with aggregated value from DB – kkot Jan 03 '20 at 09:01

0 Answers0