CODEWITHSUNDEEP
javamysqlspringspring-mvcjpa
julien
julien

Reputation: 634

Spring JPA Delete Query with RowCount

I have a Spring Boot application with a JPARepository to manage my data in a MySQL Database. At the moment I have this Query Annotation:

@Query("SELECT COUNT(*) FROM Tweetpost t")
int findRowCount();

With int findRowCount(); I can see how much Rows are filled in the DB, now I want to delete all Rows, that are over 100. So the database should never have more than 100 rows, otherwise it should delete everything above 100. I only have this yet: 

@Modifying
@Transactional
@Query(DELETE)
int removeOldRows();

Upvotes: 2

Views: 8977

Answers (1)

Ken Bekov
Ken Bekov

Reputation: 14015

Unfortunately JPQL can not use limit key word. But you can do it in two steps. First - get limited list of latest id. Second - delete all tweets, which ids are not in this list. I suppose, since you want to delete oldest records, you have something like date column.

Declare in repository two methods:

@Repository
public TweetpostRepository extends CrudRepository<Tweetpost , Integer>{

    @Query("select t.id from TweetPost t order by t.date desc")
    public Page<Integer> findLatestId(Pageable pageable);

    @Modifying
    @Transactional
    @Query("delete from TweetPost t where t.id not in (:idList)")
    public void deleteByExcludedId(@Param("idList") List<Integer> idList);

    ...
}

Note, that we use desc sorting, because we need ids of the latest records. Now you can use it in Controller next way:

@Service
public class MyService {

    @Autowired
    private TweetpostRepository repository;

    public void trunkateTweetposts(){
        Page<Integer> page = repository.findLatestId(new PageRequest(0, 10));
        repository.deleteByExcludedId(page.getContent());
        ...
    }

}

In this example 10 is the count of records you wish to keep.

Upvotes: 2

Related Questions