CODEWITHSUNDEEP
javadatabasespringhibernatepostgresql
Iaabdenbithen Karim
Iaabdenbithen Karim

Reputation: 133

Auto delete database rows after an expiration date

I'm working on a web site based on Spring, Hibernate and Postgresql for classified ads. Those classified ads have a publication period going from 2 weeks to 6 months. How can i implement a solution to automatically delete expired classified ads? It's better to delete them from the java layer or by using a Postgresql scheduler?

Thanks in advance.

Upvotes: 4

Views: 6082

Answers (4)

We are Borg
We are Borg

Reputation: 5313

This method retrieves objects older than 1 week and deletes them, in DAO layer :

 @Override
    @Scheduled(cron = "0 4 4 * * ?")
    public void deleteChatMessagesAutomatically() {
        if(schedulerActive.equals("true")) {
            Session session = this.sessionFactory.getCurrentSession();
            long now = System.currentTimeMillis();
            long nowMinus1Week = now - (1000 * 60 * 60 * 24 * 7);
            Timestamp nowMinus1WeekAsTimeStamp = new Timestamp(nowMinus1Week);
            Query query = session.createQuery("from ChatMessages as cm where cm.sortTimeStamp <:limit");
            query.setParameter("limit", nowMinus1WeekAsTimeStamp);
            List<ChatMessages> chatMessagesList = query.list();
            chatMessagesList.forEach(session::delete);
            session.flush();
        }
    }

It runs every night, ofcourse you can change that.

Upvotes: 0

Periklis Douvitsas
Periklis Douvitsas

Reputation: 2491

Spring provides support for scheduling jobs. This is possible using @Scheduled annotation. You can have a look at this website

http://howtodoinjava.com/spring/spring-core/4-ways-to-schedule-tasks-in-spring-3-scheduled-example/

ie to schedule a task every minute 

    @Override
    @Scheduled(cron = "1 * * * * ?")
    public final void updateTest() throws IOException {
        //do something here
    }

I would prefer to have it in Spring (in the web application) because if I migrate the database or the operating system then I have to rewrite it.

Hope it helps

Upvotes: 1

ike3
ike3

Reputation: 1800

As you are already using Spring you could use spring scheduler to run the necessary DB queries to delete the rows.

<task:scheduled-tasks>
    <task:scheduled ref="deleteJob" method="run" cron="0 * * * * *"/>
</task:scheduled-tasks>
<bean id="deleteJob" class="..."/>

deleteJob is a simple Spring bean with run() method which will be executed based on CRON.

Upvotes: 0

d1ll1nger
d1ll1nger

Reputation: 1701

Is this a UNIX solution? If so use CRON to call a PostgreSQL .sql script which deletes them on whichever duration you seem to necessary.

Upvotes: 0

Related Questions