Oracle Java Concat Like with Arguments

Question

I like to perform this query in Oracle SQL Developer in order to test and later implement the same query in Java.

SELECT
    enti.*
FROM
    Entity enti
WHERE
    enti.apellidouno LIKE CONCAT('%',:apellido1,'%')
    AND enti.nombreuno LIKE CONCAT('%',:nombre1,'%')
    AND ( :apellido2 IS NULL OR enti.apellidodos LIKE CONCAT('%',:apellido2,'%') )
    AND ( :nombre2 IS NULL OR enti.nombredos LIKE CONCAT('%',:nombre2,'%') );

I had this message

ORA-00909: número de argumentos no válido 00909. 00000 - "invalid number of arguments" *Cause:
*Action: Error en la línea: 103, columna: 30

This position is just letter C of CONCAT clausule in the Line:

enti.apellidouno LIKE CONCAT('%',:apellido1,'%')

Now, In my interface

public interface EntityRepository extends CrudRepository<Entity, Long>, JpaRepository<Entity, Long> {


    public static final String ENTITY_POR_APELLIDOS_Y_NOMBRES
            = " SELECT enti "
            + " FROM Entity enti "
            + " WHERE "
            + "  enti.apellidouno LIKE CONCAT('%',:apellido1,'%') "
            + "  AND enti.nombreuno LIKE CONCAT('%',:nombre1,'%') "
            + "  AND (:apellido2 is null OR enti.apellidodos LIKE CONCAT('%',:apellido2,'%') "
            + "  AND (:nombre2 is null OR enti.nombredos LIKE CONCAT('%',:nombre2,'%') ";

    @Query(ENTITY_POR_APELLIDOS_Y_NOMBRES)
    List<Entity> findEntityByApellidosAndNombres(
            @Param("apellido1") String apellido1, @Param("apellido2") String apellido2, 
            @Param("nombre1") String nombre1, @Param("nombre2") String nombre2);

}

In Java I got:

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: 
unexpected token: null near line 1, column 353 
[ SELECT enti  FROM package.Entity enti  
WHERE   enti.apellidouno LIKE CONCAT('%',:apellido1,'%')   
AND enti.nombreuno LIKE CONCAT('%',:nombre1,'%')   
AND (:apellido2 is null OR enti.apellidodos LIKE CONCAT('%',:apellido2,'%')   
AND (:nombre2 is null OR enti.nombredos LIKE CONCAT('%',:nombre2,'%') ]

score 1 · Accepted Answer · answered Jul 03 '19 at 21:19

You need to nest CONCAT because in Oracle it handles only 2 arguments:

SELECT enti.*
FROM Entity enti
WHERE enti.apellidouno LIKE CONCAT(CONCAT('%',:apellido1),'%')
 -- ...

Optionally you could use ||:

SELECT enti.*
FROM Entity enti
WHERE enti.apellidouno LIKE '%' || :apellido1 || '%'

Oracle Java Concat Like with Arguments

1 Answers1