Collection Namedquery as Parameter

Asked

Viewed 365 times

2

I am using the Namedquery of JPA, to create a DTO. And in my Query I have as condition one IN and as a parameter of IN has values with white spaces in the middle. (Ex: 9292 929). and these values with white space, returns nothing. Only values without whitespace.

@Query("SELECT NEW br.com.dto.LancamantoDto(m.id, r.id) \n" +
        "FROM Lancamento l \n" +
        "WHERE l.seuNumero IN  (:seuNumero) \n" +
        "AND   l.data       =   :data")
List<LancamentoDto> findAllBySeuNumeroAndData(@Param("seuNumero")List<String> seuNumero, @Param("data")LocalDate data);
java hibernate jpa parameters collection

  • Olá Danilo. Lancamento.seuNumero is a String? If it is a Number (Integer, BitInteger, etc) you will need to pass a list of the respective type. E.g., List<Integer> seuNumero.

  • Hello Anthony, at the moment I call the method, I pass a List<String> but within this List, it has values that have white spaces in the middle, and it is necessary that white spaces in the number.(Ex: 8283E 3434) when I need to search only one Lancamento.seuNumer I have to use LIKE in Query, with wildcards % in the whitespace, but for collection I don’t know what I need to do.

  • Hello Danilo, my question is about the type of Lancamento.seuNumero, what will determine how this list is interpreted is the type of l.seuNumero. That’s a String in your pojo mapped?

  • So @Anthony the Lancamento.seuNumero is String yes.

  • And in the database that value is a VARCHAR? If yes and both strings are equal, it should be possible to make a query with = or IN hassle-free.

  • A simple check. The name in SELECT is Lancamanto, with Same? It may just be a typo, but it doesn’t cost to validate.

  • @Anthonyaccioly yes, in the database is varchar, and if I perform this query directly in the database (SQL Server) works normal with whitespaces.

  • Danilo. Very, very strange even, mainly the fact of simple consultations with = does not work. It seems that your values are being converted or that your encoding is getting lost... already tried to create some entries in which seuNumero is equal to a handful of characters (e. g. ç$%€) and check if the consultation works on the Iberian side?

  • Maybe Spring Data is doing something weird too. Try turning your query into a @NamedQuery and use the standard Hibernate API (e. g., session.getNamedQuery).

  • I did the tests, putting one with special characters, and he found normal, via Namedquery, and the default Hibernate API. But I realized something when the value of seuNumero, has only 1 blank space( e.g. 127999 00 00 00001) it returns me the record, but when I try to pass a seuNumero with more than 1 blank space (e. g. 128384 00 00 00001) he ignores this seuNumero. Very strange this.

  • Interesting. Even by default API? Who knows if you copy the database combination to the code 128384 00 00 00001, I’ve seen cases of characters that look like spaces but aren’t.

Show 6 more comments

1 answer

1

The correct syntax (JPQL Snippet) for IN with Collections should be written without parentheses:

Query("SELECT NEW br.com.dto.LancamantoDto(m.id, r.id) \n" +
        "FROM Lancamento l \n" +
        "WHERE l.seuNumero IN  :seuNumero \n" +
        "AND   l.data       =   :data")

Existed a bug in Hibernate a while ago, it seems that it appeared again in a new version (4.3).

Browser other questions tagged java hibernate jpa parameters collection

You are not signed in. Login or sign up in order to post.