1

I have a very slow code here. All it does is that it colleting all rows with a specific entity column.

List<Data> slectedData = dataService.findByJobNameOrderByDateTime(selectJob.getValue().getName());
List<Data> deleteThese = slectedData.subList(firstIndex - 1, lastIndex);
    for (List<Data> deleteTheseLists : Lists.partition(deleteThese, 2000)) {
        dataService.deleteInBatch(deleteTheseLists);
}

Then I create a sub list and I want to delete that sublist. But the sublist can be very large and then dataService.deleteInBatch(deleteTheseLists); takes alot of time.

The entity class Data looks like this:

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Data {

    // ID
    @Id
    @GeneratedValue
    private long id;

    // Made by
    private String jobName;
    private String calibrationName;
    @Column(columnDefinition = "DATETIME(3)")
    private LocalDateTime dateTime;

    // Analog input
    private float sa0;
    private float sa1;
    private float sa1d;

}

And I want to delete on dateTime. Is that possible in Spring Boot JPA? Because I can't delete on id because the id key is not correct indexed in the database because sometimes the database takes long time to insert data, and sometimes it goes fast. So it's a very unsecure way to delete entities in JPA.

euraad
  • 2,467
  • 5
  • 30
  • 51
  • The fastest option seems to be using `EntityManager` and `Query`. https://www.objectdb.com/java/jpa/query/jpql/delete#Selective_Deletion – Roar S. Jan 07 '21 at 20:53
  • @RoarS. Is the answer below good enough? – euraad Jan 07 '21 at 20:54
  • 1
    I would tried both, and selected the one that meets the requirements – Roar S. Jan 07 '21 at 20:55
  • ... and please update us with your results, it would be very interesting to know the actual difference in performance. BR – Roar S. Jan 07 '21 at 21:04
  • @RoarS. Yes I can, but I need to do like this: `findByJobNameOrderByDateTime` with limit index e.g `between 200 and 300 rows`. Is that possible? – euraad Jan 07 '21 at 21:13
  • Yes, the simplest solution I think will be to pass in an instance of `Pageable` as the last parameter. https://stackoverflow.com/a/40715106/14072498 – Roar S. Jan 07 '21 at 21:16
  • @RoarS. But I don't know the page number. All I know is that how many `entities` there are. I want to `1.` sorting them on `Date time` and then select a range index. I could do like this, but it's so slow. https://github.com/DanielMartensson/OpenSourceLogger/blob/636f835770049427172543f629f7e71af793e497/src/main/java/se/danielmartensson/views/MySQLView.java#L141 – euraad Jan 07 '21 at 21:24
  • Are you sure you need to sort on date? Is there a strong correlation between PK and date so you can use PK instead? E.g. if the date is create date? – Roar S. Jan 07 '21 at 21:34
  • @RoarS. Something like this `SELECT firstName, lastName, seatNumber FROM passengers ORDER BY seatNumber LIMIT 1 AND BEGINS AT 50`. Just making `AND BEGINS AT 50` by myself. – euraad Jan 07 '21 at 21:34
  • @RoarS. Yes, I need to sort in date time because I inserting them on date time. – euraad Jan 07 '21 at 21:34
  • 1
    @RoarS. Found that query example from this page https://www.baeldung.com/jpa-limit-query-results – euraad Jan 07 '21 at 21:35
  • 1
    @RoarS. Think I got it now `SELECT jobName FROM Data ORDER BY dateTime LIMIT min, max;` where `min` is my beginning and `max` is total rows counted from `min`. – euraad Jan 07 '21 at 21:46
  • @RoarS. Do you think this is correct `@Query("SELECT jobName FROM Data data ORDER BY data.dateTime LIMIT firstIndex, lastIndex") List findByJobNameOrderByDateTimeLimit(@Param("jobName") String jobName, @Param("firstIndex") long firstIndex, @Param("lastIndex") long lastIndex);` to getting `entities` that are ordered by `dateTime` and limited by a range? – euraad Jan 07 '21 at 21:53
  • You should probably write some tests about this to make sure things are working. Look up `@DataJpaTest`. There is a test in this answer: https://stackoverflow.com/a/65614205/14072498 BR – Roar S. Jan 07 '21 at 21:57

1 Answers1

1

Yes you can delete by dateTime using keywords

//equals
deleteByDateTimeEquals(LocalDateTime dateTime);

// lessthan
deleteByDateTimeLessThan(LocalDateTime dateTime);

// greaterthan
deleteByDateTimeGreaterThan(LocalDateTime dateTime);
Ryuzaki L
  • 37,302
  • 12
  • 68
  • 98