Search code examples
javamysqlspringtinyint

Mapping tinyint(3) in a mysql database from Java Spring Boot

I am using a legacy codebase for refactoring an SMS gateway that comprises of a tinyint(3) column called "delivery_status" in the "m_outbound_messages" table in the mysql database. This column records the status of the message submitted to the SMS Gateway based on the following enumeration: 

public enum SmsMessageStatusType {
    INVALID(0, "smsMessageStatusType.invalid"), // unknown status type
    PENDING(100, "smsMessageStatusType.pending"), // message received
    WAITING_FOR_REPORT(150, "smsMessageStatusType.pending"),
    SENT(200, "smsMessageStatusType.sent"), // message sent to the SMS gateway
    DELIVERED(300, "smsMessageStatusType.delivered"), // SMS gateway's attempt to deliver message to recipient's phone was successful
    FAILED(400, "smsMessageStatusType.failed"); // SMS gateway's attempt to deliver message to recipient's phone failed
}

In the legacy codebase, the delivery_status field has been mapped to a Integer data type in the @Entity class for the "m_outbound_messages" table. However, when I change the status of the delivery_status column for a specific row in the database by passing SmsMessageStatusType.FAILED.getvalue() as an Integer, I get the following error:

org.drizzle.jdbc.internal.common.QueryException: Out of range value for column 'delivery_status' at row 1

I have also tried to declare delivery_status as an unsigned tinyint(3), but the problem persists. Also, when I map delivery-status to a Boolean variable, the value is not being stored in the format required from the enumeration values.

However, by default, all rows in the database are being correctly stored with the SmsMessageStatusType.Pending value (100) as their delivery_status.

Any pointers on how to map the tinyint(3) from Java in the required format would be appreciated.

Solution

  • A MySQL tinyint is represented as a single byte, meaning that the maximum value is 127 for a signed field, or 255 for unsigned. In either case, this is less than the value you are trying to set which is 400.

    See also this answer that clarifies the (3) indicates the display width, and does not influence the number of bytes available to store values.