Execute Scheduled custom sql queries in Spring boot

1
votes

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.

2
javaspring
can you tell us when you update the next schdeule time in database? - iamrajshah
Schedule once created should run at the specified time everyday or at specified time intervals regularly( like a cron expression) - Shiva
Then @scheduler will work for you. I thought you have to run on a time saved on database - iamrajshah
Yes. It should run on a time saved on database.. What I meant is time is like a cron expression..(everyday,everyweek etc). - Shiva
This may give some light : stackoverflow.com/questions/14630539/… - PShetty

2 Answers

2
votes
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();
        }
    });
}

}

0
votes
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
   }
}