How to make criteria with array field in Hibernate

Question

I'm using Hibernate and Postgres and defined a character(1)[] column type.

So I don´t know how to make this criteria to find a value in the array.

Like this query

SELECT * FROM cpfbloqueado WHERE bloqueados @> ARRAY['V']::character[]

Niedersachsen

did u check: #22650464 – Deedeeann

If you look the last answer, it's mine. – Niedersachsen

Can you place the source code which you have tried? – Antimonic

I don´t have a code because I only know how to make it with sql – Niedersachsen

Answer 1

I am not familiar with Postgres and its types but you can define your own type using custom basic type mapping. That could simplify the query.

There are many threads here on SO regarding Postres array types and Hibernate, for instance, this one. Another array mapping example that could be useful is here. At last, here is an example of using Criteria with user type.

Code example could be

   List result = session.createCriteria(Cpfbloqueado.class)
   .setProjection(Projections.projectionList()
      .add(Projections.property("characterColumn.attribute"), PostgresCharArrayType.class)
   )
   .setResultTransformer(Transformer.aliasToBean(Cpfbloqueado.class))
   .add(...) // add where restrictions here 
   .list()

Also, if it is not important for the implementation, you can define max length in the entity model, annotating your field with @Column(length = 1).

Or if you need to store an array of characters with length of 1 it is possible to use a collection type.

---

I hope I got the point right, however, it would be nice if the problem domain was better described.

Transonic

As I said in the topic, I want to find a value in the array, as IN clause, imagine, if a I have a array ´{'A', 'B', 'C'}´ and want to know if is possible to find this column look by 'B' value – Implicate

@DiegoMacario: yes, it is possible using either ElementCollection and define the array field in the Hibernate entity model (and query it like #14090864) or defining your own array usertype (something like #21941142). – Transonic

Answer 2

So you have array of single characters... Problem is that in PG that is not fixed length. I had this problem, but around 10 years ago. At that time I had that column mapped as string, and that way I was able to process internal data - simply slice by comma, and do what is needed. If you hate that way, as I did... Look for columns with text[] type - that is more common, so it is quite easy to find out something. Please look at this sample project: https://github.com/phstudy/jpa-array-converter-sample

Topliffe