3

I've tried to do a native query with Spring Boot using all(array[]) function, however, I couldn't make it correctly. I don't know the quantity of Strings that I'll pass, it's a dynamic quantity. Can you guys help me on this, please?

I've tried using List<String>, String[] and just String as below:

  1. passing String and in the query all(array[:texto]): no errors, however, no results.

  2. passing List<String> and in the query all(array[:texto]):

org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying ~~* record
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  1. passing String[] and in the query all(array[:texto]):
org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying ~~* bytea
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  1. passing String[] and in the query all(array[CAST(:texto AS TEXT)]): no errors, however, no results.
@Query(value="SELECT * FROM Tag WHERE nome ILIKE all(array[:texto])", nativeQuery=true)
public List<Tag> findPacotesByTexto(@Param("texto") List<String> texto);

EDITED:

@Entity
public class Tag {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(nullable = false)
    private String nome;

    @ManyToOne
    @JsonIgnore
    @JoinColumn(name="pacote_id")
    private Pacote pacote;

    Tag() {

    }

    public Tag (String nome, Pacote pacote) {
        this.nome = nome;
        this.pacote = pacote;
    }

    public long getId() {
        return id;
    }

    public String getNome() {
        return nome;
    }

    public Pacote getPacote() {
        return pacote;
    }

}

How can I make this work?

Thanks in advance.

cmsantos
  • 317
  • 1
  • 4
  • 13

2 Answers2

0

Had the same trouble casting the list to a text[] using spring + hibernate & native query

See https://stackoverflow.com/a/55179555/335264 for a working solution (uses two postgresql functions to do the casting)

thoomasbro
  • 63
  • 1
  • 4
-1

In a native query approach, the SQL is passed exactly to be executed as is what it's written. And I think the framework could not serialize your collection as the required pattern all(array[?, ?, ?, ...]). In that case, you should convert your parameter before passing to the method or change the approach to not using the native query.

Sereysopheak Eap
  • 99
  • 5
  • What do you mean with "converter your parameter before passing to the method"? – cmsantos Aug 11 '19 at 16:42
  • I mean rather passing a collection, you should pass only a string follows the pattern [?, ?, ...]. – Sereysopheak Eap Aug 11 '19 at 16:51
  • I've tried this, but it doesn't return the result to me.. And If I execute the same query through the database, I get a lot of results – cmsantos Aug 11 '19 at 16:55
  • Did you try with only one single element to filter? – Sereysopheak Eap Aug 11 '19 at 17:04
  • Yes, I did. With one element I can get the results, but with more the one I always got errors – cmsantos Aug 11 '19 at 17:07
  • I think that's the conversion problem. What the SQL query needs is ['xxx', 'yyy'], and the first and last of single quote ```'``` are provided by the framework already, you need to handle the middle ones – Sereysopheak Eap Aug 11 '19 at 17:11
  • Should I pass a String or a List to the query? – cmsantos Aug 11 '19 at 17:18
  • If you want to stick with this approach, you should pass a String but that should be converted in this format ```xxx', 'yyy```. But it's a bit misleading. I would recommend you to change your approach not using the native query – Sereysopheak Eap Aug 11 '19 at 17:23