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[]

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.

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