Java JDBC RowMapper : Convert MSSQL Server datetimeoffset into Java OffsetDateTime

Question

I am using JDBC Template to retrieve data from database in MS SQL Server / Azure. I have my enrollment date as datetimeoffset in SQL. What is the corresponding RowMapper / ResultSet method, to convert into Java OffsetDateTime ?

See the last line:

SQL:

CREATE TABLE [dbo].[Customer]
(
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](255) NOT NULL,
    [employeeid] [int] NOT NULL,
    [EnrollmentAmount] [decimal](10, 2) NULL,
    [ActiveFlag] [bit] NULL,
    [EnrollmentDate] [datetimeoffset](7) NULL,  -- << this column
)

Java:

@Data
public class CustomerGetResponse {
    private int customerId;
    private String firstName;
    private int employeeId;
    private BigDecimal enrollmentAmount;
    private boolean activeFlag;
    private OffsetDateTime enrollmentDate;  
}

public class CustomerGetMapper implements RowMapper<CustomerGetResponse> {
    public CustomerGetResponse mapRow(ResultSet rs, int rowNum) throws SQLException {
        CustomerGetResponse customer = new CustomerGetResponse();
        customer.setCustomerId(rs.getInt("CustomerId"));
        customer.setFirstName(rs.getString("FirstName"));
        customer.setFeeAmount(rs.getBigDecimal("EnrollmentAmount"));
        customer.setActiveFlag(rs.getBoolean("ActiveFlag"));
        customer.setEnrollmentDate(rs.); -- << trying to figure out correct ResultSet method
        return customer;
    }

Note: BeanPropertyRowMapper.newInstance(CustomerGetResponse.class) seems to be doing it well, (not sure what its doing internally), but trying to use manual RowMapper.

Reference question: What is the SQL Server Database column type for java.time.OffsetDateTime?

Answer 1

The "standard" way of doing it is:

OffsetDateTime offset = rs.getObject(1, OffsetDateTime.class);

Couple of alternatives to use:

import microsoft.sql.DateTimeOffset;
...
DateTimeOffset dto = (DateTimeOffset) rs.getObject(1);
DateTimeOffset dto = rs.getObject(1, DateTimeOffset.class);
DateTimeOffset dto = ((SQLServerResultSet)rs).getDateTimeOffset(1);

return dto != null ? dto.getOffsetDateTime(): null; //Careful with nulls!

The first version is the safest and avoids the casts, while the others work with the concrete microsoft implemention classes.

These were tested with microsoft jdbc driver version 9.1.1, your mileage might vary with older versions. Also, watch out for time zone handling in general, there are a lot of pitfalls as well as ways of losing the information on the way, both from and to the database!