sql-serverjooq

Handling in jOOQ table/column names with spaces in SQL Server

I have this table in SQL Server, and need to run a select with jOOQ considering that the table and column names contain spaces:

CREATE TABLE [App Dates]
(
    [Id] [int] NOT NULL,
    [Current Date] [date] NULL,
    [Previous Biz Date] [date] NULL,
    [Next Biz Date] [date] NULL,
    [Data Date] [date] NULL,
    [Is Last Day of Month] [varchar](100) NULL
)

This statement compiles, but throws an error in run-time:

Record record = context.select()
                .from(APP_DATES)
                .where(APP_DATES.ID.eq(1))
                .fetchAny();

 record.into(pojo);

SQL [select App Dates.Id, App Dates.Current Date, App Dates.Previous Biz Date, App Dates.Next Biz Date, App Dates.Data Date, App Dates.Is Last Day of Month from App Dates where App Dates.Id = ?]; Incorrect syntax near '.'.

Clearly the SQL statement is incorrect as the column names have spaces. How to fix this? Also, what should be the POJO field names to load this table with record.into(pojo)? Note that the column names are capitalized in the database.

Solution

  • Identifier quoting

    jOOQ by default quotes all identifiers. If it doesn't, then it's because you turned that feature off in your Settings. Turn it back on.

    See:

    Pojo names

    There's currently no mapping in the DefaultRecordMapper that is whitespace aware. This could be considered a bug or a feature request:

    You can implement your own mapper:

    Or use a more explicit form of mapping that doesn't rely on reflection and convention.