Clever Geek Handbook

Executing string column as int in hql query or criteria

Can anyone guide me where I am going wrong? It gives a null result, but exists in the db values ​​as the condition appears in the next query.

  Str = QueryImpl(from ArcZipCodeRange where cast(fromZip as int) >='12345' and cast(toZip as int)<='12345')
        arcZipCodeRangeList = 0
+4


source to share


4 answers


Are you sure your terms are correct?

cast(fromZip as int) >='12345' and cast(toZip as int)<='12345')


Would give all results with from

> 12345 and to

<12345.

Should it be the other way around: <t21 <12345 and to

> 12345?

+6


source


you change fromZip

and toZip

to int and then compare it to string. It takes trouble.

Use the same data type on both sides of the comparison.



Also as @Fazovsky points out , your state seems to be wrong.

+2


source


You can do it with Hibernate Criteria

easily. For a solution, you have to create your own Hibernate Formula

for these (fromZip and toZip). The following should be your pojo mapping.

@Column
private String fromZip;

@Formula(value="to_number(fromZip)")
private double newfromZip;

@Column
private String toZip;

@Formula(value="to_number(toZip)")
private double newtoZip;

Below are your selection criteria: 

Criteria criteria = session.createCriteria(ArcZipCodeRange.class);
criteria.add(Restrictions.le("newfromZip", yourIntegerParameter));
criteria.add(Restrictions.ge("newtoZip", yourIntegerParameter));
List<ArcZipCodeRange> list = criteria.list();

Hope this helps you.

+2


source


The below method helped me to type String for other data types using CriteriaBuilder.

CriteriaBuilder queryBuilder = em.getCriteriaBuilder();
Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Integer.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Long.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Double.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Date.class));
//Expression roomIdInt = queryBuilder.desc(root.get("room").get("id")).as(Boolean.class));

At first I ran into many errors using CriteriaBuilder and tried to check the DB logs to check the query being executed. This helped fix the HQL as needed. The above approach works fine in PostgreSQL, but the solution varies depending on the database

0


source






More articles:


All Articles