Search code examples
javaspring-bootspring-webfluxspring-data-r2dbc

R2DBC: Convert Integer to Boolean for Specific Column in Spring Data Entity

I am using Spring Data R2DBC with MySQL, and I'm facing an issue with type conversion when mapping a database column of type INT to a Boolean field in my entity.

Entity Definition:

import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

@Table("users")
public class UserEntity {

@Id
private Long id;

@Column("sms_list")
private Boolean smsPermited;


public Boolean getSmsList() {
    return this.smsPermited;
}

public void setSmsList(Boolean smsPermited) {
    this.smsPermited = smsPermited;
}

public Boolean getSmsPermited() {
    return smsPermited;
}

public void setSmsPermited(Boolean smsPermited) {
    this.smsPermited = smsPermited;
}

}

Database Schema:

CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
sms_list INT(11) NOT NULL DEFAULT 0

);

When running the application, I'm encountering the following error:

No converter found capable of converting from type [java.lang.Integer] to type [java.lang.Boolean]

It seems like Spring Data R2DBC does not automatically convert the INT database column to a Boolean in my entity.

What I've Tried:

1.Using @Column on Getter:

  • I attempted to move the @Column("sms_list") annotation to the getter method instead of the field.
  • The error persists.

2.Manually Handling Conversion in Getter/Setter::

  • I tried defining a getter and setter that manually convert between Integer and Boolean.

3.Global Converters:::

  • I am aware of solutions that involve creating global converters, but I want to avoid this approach as it would impact all Integer fields globally across my application.

Expected Behavior

I want to:

  1. Keep the field smsPermited as a Boolean in my entity for application-level usage.
  2. Store the value as 1 (true) or 0 (false) in the database without affecting other integer fields.

Questions:

  • Is there a way to locally define the type conversion for this specific field without using a global R2dbcCustomConversions?
  • Are there any best practices in Spring Data R2DBC for handling such type mismatches?
  • Is my approach of using a separate getter/setter with conversion a good practice, or is there a better solution?

Spring Boot and Library Versions:

  • Spring Boot: 5.6.15
  • Spring Data R2DBC: 3.4.1
  • Database: MySQL 5.6
  • Java Version: 17
Solution

  • If you're using io.asyncer.r2dbc-mysql version >= 1.3.0, you can resolve this by changing the column type in your database schema from INT(11) to BIT(1) or ‘TINYINT(1)’. This ensures proper mapping between the database column and the Boolean field in your entity.

    Updated Schema

    CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
sms_list BIT(1) NOT NULL DEFAULT 0);

    Explanation