ERROR: column is of type date but expression is of type bytea

[rcbandit111]

I want to implement a custom column handler in Hibernate/Spring Boot app but for some reason AttributeConverter is not applied. I tries this:

Entity:

@Entity
@Table(name = "single_user")
@Getter
@Setter
public class SingleUser {

    @Id
    @SequenceGenerator(name = "su_seq", sequenceName = "su_seq", allocationSize = 100)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "su_seq")
    @Column(name = "id", nullable = false)
    private Long id;

    @Convert(converter = ExpiryDateTypeHandler.class)
    @Column(name = "expiry_date")
    private ExpiryDate expiryDate;
 
    // getter, setter
}

Spring JPA repository:

@Transactional
@Repository
public interface SingleUseTokenRepository extends JpaRepository<SingleUseTokens, Long> {

  @Modifying
  @Query(value = """
                    INSERT INTO kro_single_use_cc_tokens (ID, 
                                                          CARD_EXPIRY_DATE,
                                                          token_profile_id)
                    VALUES (:id, 
                            :cardExpiry, 
                            :tokenProfileId)                            
            """, nativeQuery = true)
  void create(@Param("id") String id,
              @Param("cardExpiry") ExpiryDate cardExpiry,
              @Param("tokenProfileId") String tokenProfileId);

}

AttributeConverter:

import java.sql.Date;
import java.util.Calendar;

@Converter(autoApply = true)
public class CardExpiryDateTypeHandler implements AttributeConverter<ExpiryDate, Date> {

  @Override
  public Date convertToDatabaseColumn(ExpiryDate expiryDate) {
    if (expiryDate == null) {
      return null;
    }
    // Convert ExpiryDate to SQL Date
    return Date.valueOf(expiryDate.getYear() + "-" + expiryDate.getMonth() + "-01");
  }

  @Override
  public ExpiryDate convertToEntityAttribute(Date sqlDate) {
    if (sqlDate == null) {
      return null;
    }
    // Convert SQL Date to ExpiryDate
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(sqlDate);
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH) + 1;

    return new ExpiryDate(year, month);
  }
}

I get error:

   [ERROR: column "card_expiry_date" is of type date but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 215] [n/a]; SQL [n/a]] with root cause

org.postgresql.util.PSQLException: ERROR: column "card_expiry_date" is of type date but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.

Basic reproducible code: https://github.com/rcbandit111/JdbcTypePoc.git

Do you know why AttributeConverter is not applied when I insert new line using native query? Use Spring Boot 3.5.0

[mp911de]

Native queries do not get attribute converters applies. That's a limitation of JPA native queries.

[rcbandit111]

@mp911de Can you advise what are the possible solutions, please?

[mp911de]

Use a type that maps directly to the database type, e.g. java.util.Date.