My Use-cases:

• We have an installation schedule entity (check below code) and it has an installation date.

• Once installation has completed, after 4 weekdays we will verify the installation status with customers. Note: (4 weekdays - this count is configurable. So 'X' weekdays)

Weekdays means - Monday to Friday. We don't care about other holidays.

• I have a scheduler, it will retrieve these orders after 'X' weekdays - I'm stuck here

I don't know how to make a query for after 'X' weekdays.

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;
    }
}

Question:

How to make a SQL or JPQL or JPA query for weekdays?

I knew its very basic question, I am a mobile app developer, I recently joined the Springboard team, it's really hard for me :(

Feel free to give your valuable feedback!

CodePudding user response：

I have a following suggestion if I correctly got the problem.

Java:

1. Take the current date
2. Find the date of interest: count minus 4 workdays (so if it is Friday today - subtract 4 days, if it is Monday - subtract 2 days for weekend and 4 more days for weekdays)

Then write a query that will select all installations that were done on the date of interest. In pseudo code:
select * from installations where installation_date = <date of interest>;.

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.