CODEWITHSUNDEEP
javamysqlhibernatecriteria
Ignasi
Ignasi

Reputation: 6185

using Hibernate numeric restriction for a varchar column

I'm using the Hibernate Criteria in order to build queries dynamically. One of these dynamic queries uses a column stored on MySQL as a varchar. But, is a column storing prices of items (only has numeric values with decimals). So I would like to generate queries using ge le or between.

Is there a way to tell to hibernate something like "hey, this column is of type varchar, but its content is numeric so apply my numeric restrictions (please)"?

Upvotes: 1

Views: 2220

Answers (2)

Alan Hay
Alan Hay

Reputation: 23246

You Hibernate query is translated to SQL. If the underlying column is a varchar column I'm not sure that Hibernate can do anything about it.

As the issue is in the database tier then one suggestion would be to create a virtual column in the database (if your database supports such things) and which casts this varchar to a decimal .

Quick Google for MySql suggests:

http://mysqlserverteam.com/generated-columns-in-mysql-5-7-5/

You can then map the price field in your entity to this virtual column as a numeric type and query as required.

Failing that, you could create a 2 column view (id, price) doing something similar and map the price field to your existing Entity as a secondary table.

Update

After checking further I note the following from the MySQL docs:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

Strings are automatically converted to numbers... as necessary.

Therefore it would seem that you could do the following. Use a Type Converter (JPA 2.1) to convert between String <> Numeric type in your domain model. Given the above, simply write queries against this field using the standard numeric operators.

http://www.thoughts-on-java.org/jpa-21-how-to-implement-type-converter/

Upvotes: 1

Afsun Khammadli
Afsun Khammadli

Reputation: 2068

Same problem also occured for me. My solution is that, I created a new variable of Product and data type is double. And use hibernate formula this element and than I applied ge , le and between to this new variable. My pojo look like this:

@Column
private String price;

@Formula(value="to_number(price)")
private double newDouble;

And my hibernate criteria look like following:

Criteria criteria = session.createCriteria(Product.class);
criteria.add(Restrictions.between("newDouble",252.50,300));
List<Product> products = session.list();

I hope this will help you.

Upvotes: 2

Related Questions