Java --- update a database with an annual calendar

Question

What would be the best way to load a database table with the annual calendar using Java?

For 365 days one row per day.

I can think of writing a loop and updating/inserting every day and so on, is there a simpler way to do it? Via an API or publicly available calendar?

Thanking you in advance.

What are you trying to do, exactly what do you want to store in the table? — Joakim Danielson, Jan 24 '20 at 18:39
Its for rate calculations which need to be persisted, and holidays for different regions/countries which needs to be marked (these can be on different columns.) What I am looking for is a starting point to create a table with date as a column. — AverageJoe, Jan 24 '20 at 18:43
Maybe you should investigate doing this directly in sql, you find sql code for most databases online — Joakim Danielson, Jan 24 '20 at 18:53
Provide additional detail as edits to your Question rather than as comments. — Basil Bourque, Jan 24 '20 at 22:31

Basil Bourque · Accepted Answer · 2020-01-25T01:21:23.550

I can think of writing a loop and updating/inserting every day and so on,

Yes, that is the way to go. No magic tricks or silver bullets here.

Basically, your choices are:

You can hard-code the programming in Java, creating rows in the database via JDBC.
Write a text file for direct import by the database, such as the COPY command in Postgres.
Write SQL scripts to define the table, and for each year (or so) insert rows, to be executed by a database schema migration tool such as Flyway or Liquibase.

Personally, I would go with the last one. With a migration tool, you can easily review what was run and when. And you can easily drop a new SQL script into production to be run for the upcoming year, after prior testing of the script.

I would write a little console app to generate a draft of SQL script. Then edit by hand the holidays. Something like this:

Year year = Year.of( 2021 );  // Input the year for which we want to create a calendar by way of a SQL script.
// Verify the requested year is in the future.
if ( ! Year.now().isBefore( year ) ) { throw new IllegalArgumentException( "Year must be in the future. Message # a0720f38-f4c5-4aca-9214-d9756a91e372." ); }
String filePath = "/Users/basilbourque/calendar_" + year + ".sql";

String newline = "\n";
StringBuilder sql = new StringBuilder();
sql.append( "INSERT INTO calendar_ ( date_ , holiday_us_ ) " ).append( newline );
LocalDate firstOfYear = year.atDay( 1 );
Stream < LocalDate > dates = firstOfYear.datesUntil( firstOfYear.plusYears( 1 ) );  // Generate a stream of `LocalDate` objects, for each date from start to end, using Half-Open approach where the ending is exclusive.
String joined = 
        dates
        .map( localDate -> "( '" + localDate.toString() + "' , FALSE )" )
        .collect( 
            Collectors.joining( ", " + newline , "VALUES " + newline , newline + " ; " )   // Pass delimiter, prefix, suffix.
        )
;
sql.append( joined );

System.out.println( "sql = " + newline + sql );

try
{
    Files.writeString( 
        Paths.get( filePath ) ,
        sql , 
        StandardCharsets.UTF_8 , 
        StandardOpenOption.CREATE_NEW 
    );
}
catch ( IOException e )
{
    e.printStackTrace();
}

When run.

INSERT INTO calendar_ ( date_ , holiday_us_ ) 
VALUES 
( '2021-01-01' , FALSE ), 
( '2021-01-02' , FALSE ), 
…
( '2021-12-31' , FALSE )
 ;

Via an API or publicly available calendar?

You may find such a resource. But in my experience, most businesses run their own calendar which can vary even against their closest competitors. There may be some exceptions with some industries in some jurisdictions, such as banks & credit unions in the United States, that all share a specific official planned calendar of holidays. But if yours is not such an industry, I suggest biting the bullet and creating your own calendar-definition facility rather than relying on someone else's calendar.

For 365 days one row per day.

I suppose the date could be a natural key for the table. Defining it as the primary key will also set a constraint of UNIQUE, which prevents you from accidentally reloading data. Usually I find surrogate keys best, but I suppose here the natural key of the date would work well.

If your "rates" you mention are a fractional number, be sure to use BigDecimal type in Java. And use the appropriate type in your database. In Postgres, for example, using NUMERIC or DECIMAL is discussed here and here.

Sounds like a boolean-per-country (or per jurisdiction or market) would track your holidays. Of course, if you were routinely adding & dropping such holiday schedules, this should be done in a child table rather than as columns on the calendar table.

So the table definition might look something like this:

CREATE TABLE calendar_
(
  date_ DATE NOT NULL ,
  holiday_us_ BOOLEAN NOT NULL ,
  rate_ DECIMAL ,
  CONSTRAINT pkey_calendar_ PRIMARY KEY ( date_ )
)
;

No need to store year or month, as those can be deduced from the DATE column values. Adding columns for year, month, and such would be violating database normalization.

Java --- update a database with an annual calendar

1 Answers1