Create a Table every 5 hours

Question

I have this requirement where I have to create a temp table after every 5 hour. So if I have created a table at 9 AM, I need to create same table (diff name of Course) at 1 PM with same schema and everything, do some operation on the first table and then drop it. This will keep on repeating after every 5 hours.

How to go about this change with Java as this involves SQL to create new table, get data from old table, perform some task and then drop the old table?

Use a timer in Java which creates the temp table every 5 hours. What exactly are you asking about? — Tim Biegeleisen, Aug 28 '18 at 05:56
in sql, you can use dbms_scheduler. But it is totally in sql (Oracle). You can create a procedure which will create a table by execute immediate statement. Can call the procedure with particular interval using dbms_scheduler with creating a job. — YLG, Aug 28 '18 at 06:00
HKG, its not related to oracle. Sorry. Though thanks for the reply. — IllegalSkillsException, Aug 28 '18 at 06:01
I am pretty sure this could be done in some DBMS, using schedule job or simply using a script and a cron (but since this is every 5hours, I doubt this will be done with a real cron, more like a deamon trhead...) — AxelH, Aug 28 '18 at 06:11
Is this some database maintenance task that you should be putting on the db side instead of application side? — Kayaman, Aug 28 '18 at 06:21
@Kayaman Yes this is some sort of database maintenance task (to some extent). But I need to handle everything through application — IllegalSkillsException, Aug 28 '18 at 06:23
you can use cron or queue to do that. make a api which will create schema from 9am table. and hit it using CRON. which server you are using? — Aniket Karne, Aug 28 '18 at 12:52

Hearen · Answer 1 · 2018-08-28T08:49:43.200

Normally there are four different ways to running a task periodically:

If you are not using any framework mentioned above, you could do it using native java with timer and ScheduledExecutorService as the following demo which will run a task starting at 13:00 pm at interval of 5 hours:

public class PeriodicTask {
    public static void main(String... args) {
        // System.out.println(LocalTime.ofSecondOfDay(getDelayTo(15, 4)));
        // System.out.println(LocalTime.ofSecondOfDay(getDelayTo(16, 4)));
//        testTimer();
//        testScheduledExecutorService();
        System.out.println(TimeUnit.DAYS.toSeconds(1));
    }

    private static long getDelayTo(int hour, int minute) {
        LocalDateTime currentTime = LocalDateTime.now();
        long gapToSpecified = ChronoUnit.SECONDS.between(currentTime, LocalDate.now().atTime(hour, minute));
        if (gapToSpecified < 0) { // the time already passed => do it tomorrow;
            return ChronoUnit.SECONDS.between(currentTime, LocalDate.now().plusDays(1).atTime(hour, minute));
        }
        return gapToSpecified; // not passed, do it later today at the specified time;
    }

    public static void testTimer() {
        new Timer().schedule(new TimerTask() {
            @Override
            public void run() {
                System.out.println(LocalDateTime.now());
                System.out.println("Hello world");
            }
        }, getDelayTo(13, 0), TimeUnit.HOURS.toSeconds(5));
    }

    public static void testScheduledExecutorService() {
        Executors.newScheduledThreadPool(1).scheduleWithFixedDelay(() -> {
                    System.out.println("Hello World!");
                }, getDelayTo(13, 0), TimeUnit.HOURS.toSeconds(5), TimeUnit.SECONDS);
    }
}

score 1 · Answer 2 · edited Aug 28 '18 at 07:26

1

Use a Timer to execute queries in set intervals.

Timer timer = new Timer();
timer.schedule(new TimerTask() {
    @Override
    public void run() {
        //ToDo
    }
}, c.getTime(), 18000000);

// 5hrs == 18000000ms

And if the table name is always same, then I suggest you to use TRUNCATE statement without dropping and creating the table.

TRUNCATE TABLE table_name;

edited Aug 28 '18 at 07:26

T.Belson Raja

3
3

answered Aug 28 '18 at 06:34

Roshana Pitigala

8,437
8
49
80

score 0 · Answer 3 · edited Sep 11 '18 at 19:18

In SQL Server do the same as using while loop to create table for every five hours by at first creating procedure with drop table and create table query:

  create procedure fivehourjob
   as
     begin 
      drop table sample
      create table sample (sample_id int)
   end
   go


  WHILE 1=1
 BEGIN
  EXEC dbo.fivehourjob 
  WAITFOR DELAY '05:00:00.000';
 END
  go

The above query runs continuously and provide the output of table creation for every five hours:

Otherwise, do simple by creating the procedure and call the procedure using scheduler for every five hours.

Docs.

Create a Table every 5 hours

3 Answers3