Execute Scheduled custom sql queries in Spring boot

Question

I have a requirement to run custom queries at scheduled time. Ex: User defines a custom query to run on postgres database at a particular time of the day. I need to implement a scheduler which picks up the custom queries and scheduled time which are stored in database and execute dynamically.

I can schedule the jobs using Cron scheduler using spring boot which defines the time and date as annotation. But I need to run multiple schedules my picking up date/time from db and run the custom query.

can you tell us when you update the next schdeule time in database? — iamrajshah, Jun 13 '19 at 07:03
Schedule once created should run at the specified time everyday or at specified time intervals regularly( like a cron expression) — Shiva, Jun 13 '19 at 07:08
Then @scheduler will work for you. I thought you have to run on a time saved on database — iamrajshah, Jun 13 '19 at 07:09
Yes. It should run on a time saved on database.. What I meant is time is like a cron expression..(everyday,everyweek etc). — Shiva, Jun 13 '19 at 07:11
User can edit/modify the time at which the schedule should run — Shiva, Jun 13 '19 at 07:30
This may give some light : https://stackoverflow.com/questions/14630539/scheduling-a-job-with-spring-programmatically-with-fixedrate-set-dynamically/14632758#14632758 — PShetty, Jun 18 '19 at 07:16

score 2 · Accepted Answer · answered Jun 19 '19 at 09:18

public class SchedulingConfiguration implements SchedulingConfigurer {
   @Bean
   public TaskScheduler taskScheduler() {
    ThreadPoolTaskScheduler scheduler = new ThreadPoolTaskScheduler();
    scheduler.setThreadNamePrefix("TaskScheduler");
    scheduler.setPoolSize(10);      
    scheduler.setWaitForTasksToCompleteOnShutdown(true);
    scheduler.setAwaitTerminationSeconds(20);
    return scheduler;
}
@Override
public void configureTasks(ScheduledTaskRegistrar taskRegistrar) {
    taskRegistrar.setScheduler(taskScheduler());
    taskRegistrar.addTriggerTask(new Runnable() {
        @Override
        public void run() {
                // Code which which should run at the specified executionTime( specified in nextExecutionTime(TriggerContext triggerContext))
        }
    }, new Trigger() {
        @Override
        public Date nextExecutionTime(TriggerContext triggerContext) {
            Calendar nextExecutionTime = new GregorianCalendar();
            Date lastActualExecutionTime = triggerContext.lastActualExecutionTime();
            nextExecutionTime.setTime(lastActualExecutionTime != null ? lastActualExecutionTime : new Date());
            nextExecutionTime.add(Calendar.MINUTE, 2); // runs every 2 minute and can also be read from database instead of hardcoding
            return nextExecutionTime.getTime();
        }
    });
}

}

I found the above solution with which we can schedule a task dynamically. — Shiva, Jun 19 '19 at 09:19

score 0 · Answer 2 · edited Jun 18 '19 at 07:41

0

class Scheduler implements Runnable {
   public Scheduler(TaskScheduler scheduler, String timezone, String cron) {
      scheduler.schedule(this, new CronTrigger(cron, TimeZone.getTimeZone(timezone)));
   }

   @Override
   public void run() {
      //DO SOMETHING
   }
}

edited Jun 18 '19 at 07:41

vimuth

5,064
33
79
116

answered Jun 18 '19 at 07:24

PShetty

545
6
14

This works if the cron expression doen't change dynamically. – Shiva Jun 19 '19 at 08:57

Execute Scheduled custom sql queries in Spring boot

2 Answers2

Linked