Spring JPA使用LIKE操作符报java.lang.Long参数类型不匹配问题求解

问题根源 #

1. LIKE是字符串匹配操作符，无法直接作用于Long、BigDecimal、LocalDate类型的字段，JPA会将数值/日期参数拼接%后转为字符串，和声明的参数类型不匹配，就会抛出你遇到的类型不匹配错误
2. 动态条件的实现逻辑错误：当前给非字符串类型参数设置0L、BigDecimal.ZERO、空字符串解析日期的默认值，会触发对应条件的匹配逻辑，无法实现“参数未传则条件不生效”的需求，且LocalDate.parse("")本身就会抛出解析异常
3. 拼接%的逻辑直接写在JPQL语句中会导致类型转换失败，需要先将非字符串字段转为数据库字符串类型后再做匹配

解决方案 #

第一步：修改参数接收类 #

所有非字符串类型参数默认值设为null，用null标识参数未传：

public class PersonaPagoParams {
    private String nombrePersona = "";
    private String apellidoPersona = "";
    private String dni = "";
    private String pais = "";
    private String direccion = "";
    private Long telefono = null;
    private String mail = "";
    private String producto = "";
    private LocalDate fechaPago = null;
    private String tipoPago = "";
    private String comprobante = "";
    private BigDecimal monto = null;
    // Getters and setters
}

第二步：修改Repository查询语句 #

调整JPQL逻辑，用OR 参数 IS NULL实现动态条件，非字符串字段先转数据库字符串类型后再做LIKE匹配，以下为MySQL语法示例，可根据自己使用的数据库调整类型转换函数：

@Query(value = 
        "SELECT pe.nombre AS nombre, pe.apellido AS apellido, pe.dni AS dni, pe.pais AS pais, pe.direccion AS direccion, pe.telefono AS telefono, pe.mail AS mail, pg.producto AS producto, pg.fechaPago as fecha, tp.nombre as tipoPago, pg.comprobante AS comprobante, pg.monto AS monto " +
        "FROM Persona pe JOIN Pago pg ON pe=pg.persona JOIN TipoPago tp ON tp=pg.tipo " +
        "WHERE (:nombrePersona = '' OR pe.nombre LIKE CONCAT(:nombrePersona, '%')) " +
        "AND (:apellidoPersona = '' OR pe.apellido LIKE CONCAT(:apellidoPersona, '%')) " +
        "AND (:dni = '' OR pe.dni LIKE CONCAT(:dni, '%')) " +
        "AND (:pais = '' OR pe.pais LIKE CONCAT(:pais, '%')) " +
        "AND (:direccion = '' OR pe.direccion LIKE CONCAT(:direccion, '%')) " +
        "AND (:telefono IS NULL OR CAST(pe.telefono AS CHAR) LIKE CONCAT(:telefono, '%')) " +
        "AND (:mail = '' OR pe.mail LIKE CONCAT(:mail, '%')) " +
        "AND (:producto = '' OR pg.producto LIKE CONCAT(:producto, '%')) " +
        "AND (:tipoPago = '' OR tp.nombre LIKE CONCAT(:tipoPago, '%')) " +
        "AND (:comprobante = '' OR pg.comprobante LIKE CONCAT(:comprobante, '%')) " +
        "AND (:monto IS NULL OR CAST(pg.monto AS CHAR) LIKE CONCAT(:monto, '%')) " +
        "AND (:fechaPago IS NULL OR DATE_FORMAT(pg.fechaPago, '%Y-%m-%d') LIKE CONCAT(:fechaPago, '%')) "
)
List<PersonaPagoDTO> filtrarPersonaPago(
        @Param("nombrePersona") String nombrePersona,
        @Param("apellidoPersona") String apellidoPersona,
        @Param("dni") String dni,
        @Param("pais") String pais,
        @Param("direccion") String direccion,
        @Param("telefono") Long telefono,
        @Param("mail") String mail,
        @Param("producto") String producto,
        @Param("fechaPago") LocalDate fechaPago,
        @Param("tipoPago") String tipoPago,
        @Param("comprobante") String comprobante,
        @Param("monto") BigDecimal monto
);

注意：本次查询返回的是DTO而非实体类，不需要使用JOIN FETCH做懒加载优化，直接用JOIN即可避免不必要的报错。如果使用PostgreSQL数据库，数值转字符串用字段名::text，日期格式化用TO_CHAR(fechaPago, 'yyyy-MM-dd')即可。

可选优化 #

如果后续查询条件持续增加，推荐使用JPA Criteria API或者QueryDSL实现动态查询，代码可维护性更高。

内容的提问来源于stack exchange，提问作者Franco Mendoza