In my entity, I need to calculate some value at runtime.

So I use @formula

The problem is,

I can able to call the SQL queries, but I can't able to call the java function.

Error:

oracle.jdbc.OracleDatabaseException Message: ORA-00904: "GETWEEKDAYSCOUNT": invalid identifier

My code:

    @Entity
@Table(schema = "myschema", name = "installation_dates")
@Getter
@Setter
@NoArgsConstructor
public class InstallDates extends TransEntity implements Serializable {
     // other columns

    @Column(name = "installation_schedule_datetime")
    private LocalDateTime installationScheduleDatetime;//I use this column for calculation

    @Formula("getWeekDaysCount(installationScheduleDatetime)")
    private int weekDaysCount;

   
    public  int getWeekDaysCount(LocalDateTime installationScheduleDatetime) {
        int totalWeekDays = 0;
        LocalDateTime todayDate = LocalDateTime.now();
        while (!installationScheduleDatetime.isAfter(todayDate)) {
            switch (installationScheduleDatetime.getDayOfWeek()) {
                case FRIDAY:
                case SATURDAY:
                    break;
                default:
                    totalWeekDays  ;
                    break;
            }

            installationScheduleDatetime = installationScheduleDatetime.plusDays(1);
        }
        return totalWeekDays;
    }
}

Questions:

1. Is it possible to use java functions inside @Formula?
2. I don't know how to convert this into SQL query. Is it possible to convert this function into SQL query?

Feel free to give your valuable feedback!

CodePudding user response：

First lets answer your questions

1. No you cannot call a method from @Formula
2. You probably could (see here but that might depend on your database.

The fact that you use an entity and JPA doesn't mean everything has to be a JPA property.

You could:

1. Write a get method that calculates it on the fly
2. Write a getter which sets it lazily.
3. Use the @PostLoad to always set it.

@Entity
@Table(schema = "myschema", name = "installation_dates")
@Getter
@Setter
@NoArgsConstructor
public class InstallDates extends TransEntity implements Serializable {
     // other columns

    @Column(name = "installation_schedule_datetime")
    private LocalDateTime installationScheduleDatetime;//I use this column for calculation
  
    public int getWeekDaysCount() {
        int totalWeekDays = 0;
        LocalDateTime isdt = this.installationScheduleDatetime;
        LocalDateTime todayDate = LocalDateTime.now();
        while (!isdt.isAfter(todayDate)) {
            switch (isdt.getDayOfWeek()) {
                case FRIDAY:
                case SATURDAY:
                    break;
                default:
                    totalWeekDays  ;
                    break;
            }
            isdt = isdt.plusDays(1);
        }
        return totalWeekDays;
    }
}

Or if you really want it to be a property, you could use the getter to set it lazily.

@Entity
@Table(schema = "myschema", name = "installation_dates")
@Getter
@Setter
@NoArgsConstructor
public class InstallDates extends TransEntity implements Serializable {
     // other columns

    @Column(name = "installation_schedule_datetime")
    private LocalDateTime installationScheduleDatetime;//I use this column for calculation
  
    private int weekDaysCount = -1;

    public int getWeekDaysCount() {
      if (weekDaysCount == -1) {
        int totalWeekDays = 0;
        LocalDateTime isdt = this.installationScheduleDatetime;
        LocalDateTime todayDate = LocalDateTime.now();
        while (!isdt.isAfter(todayDate)) {
            switch (isdt.getDayOfWeek()) {
                case FRIDAY:
                case SATURDAY:
                    break;
                default:
                    totalWeekDays  ;
                    break;
            }
            isdt = isdt.plusDays(1);
        }
        weekDaysCount = totalWeekDays;
      }
      return weekDaysCount;
    }
}

Or if you always want to calculate that value you could even place it in an @PostLoad annotation on a method to initialize it (you could even reuse the above lazy getter for it). Or move the init code to the @PostLoad annotated method.

@PostLoad
private void initValues() {
  getWeekDaysCount();
}

CodePudding user response：

@Formula specifies an expression written in native SQL that is used to read the value of an attribute instead of storing the value in a Column. (https://docs.jboss.org/hibernate/orm/current/javadocs/org/hibernate/annotations/Formula.html)

As for your case, it doesn't look like there's much use in storing weekDaysCount in the DB if it's derived from installationScheduleDatetime. I'd just mark the weekDaysCount as @Transient and be done with it (@Formula should be removed).

Another solution would be to leave weekDaysCount non-transient and put your calculations in a @PreUpdate/@PrePersist method. See https://www.baeldung.com/jpa-entity-lifecycle-events for more info on that.