删除1年多以前的记录 [英] Delete records from more than 1 year ago
问题描述
我正在使用spring JPA Repositories在我的数据库中保存Twitter推文。推文的日期在MySQL数据库中保存为日期时间。现在我要删除所有超过一年的推文。我看到有函数 CURRENT_TIME
,我想到的是像 CURRENT_TIME - 360
。我知道这不是正确的语法,但我不知道如何做到这一点。以下是我所拥有的:
I'm saving twitter tweets in my database with spring JPA Repositories. The date of the tweet is saved as Datetime in the MySQL db. Now I want to delete all tweets that are older than one year. I saw there is the function CURRENT_TIME
and I thought of something like CURRENT_TIME - 360
. I know thats not the correct syntax but I have no idea how to do this. Here is what I have:
@Modifying
@Transactional
@Query("DELETE FROM Tweetpost t WHERE t.createdAt > ")
int removeOlderThan();
编辑已解决:
存储库:
@Modifying
@Transactional
@Query("DELETE FROM Tweetpost m WHERE m.createdAt < :date")
int removeOlderThan(@Param("date") java.sql.Date date);
服务:
public void removeOldItems() {
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, -360);
java.sql.Date oneYear = new java.sql.Date(cal.getTimeInMillis());
tweetRepository.removeOlderThan(oneYear);
}
推荐答案
为此您需要2个步骤。首先,你需要一个方法,将你想要删除消息的日期作为参数,你根本不需要 @Query
注释。
For this you need 2 steps. First of all you need a method that will take as a parameter the date of which you want to delete the messages and you dont need tha @Query
annotation at all.
因此,在您的存储库中,您必须拥有类似
So in your repository you must have something like
@Modifying
public void deleteByCreatedAtBefore(Date expiryDate);
现在,在您的服务方法中,您将计算日期并像这样传递
Now in your service method, you will calculate the Date and pass it on like this
public void performTweetCleanup(){
//calculate date
Calendar cal = Calendar.getInstance();
Date today = cal.getTime();
cal.add(Calendar.YEAR, -1);
Date previousYear = cal.getTime();
//call the method
MyTweeterRepository.deleteByCreatedAtBefore(previousYear);
}
这篇关于删除1年多以前的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!