CODEWITHSUNDEEP
sqlhibernatejpahql
Chris Williams
Chris Williams

Reputation: 12481

Substring search a numeric field with JPA/Hibernate

I have a JPA entity that has a numeric field. Something like:

@Basic(optional = false)
@Column(name = "FISCAL_YEAR", nullable = false)
private int fiscalYear;

I have a requirement to sub-string search this field. For example, I want a search for 17 to give me 2017 and 1917 and 1789. Forget for a minute what a crazy request this is and assume I have a real use case that makes sense. Changing the column to a varchar in the database is not an option.

In PL/SQL, I'd covert the field to a varchar and do a like '%17%'. How would I accomplish this with Hibernate/JPA without using a native query? I need to be able to use HQL or Criteria to do the same thing.

Upvotes: 1

Views: 1248

Answers (2)

raminr
raminr

Reputation: 804

You can declare your own Criterion type

public class CrazyLike implements Criterion {

    private final String propertyName;
    private final int intValue;

    public CrazyLike(String propertyName, int intValue) {
        this.propertyName = propertyName;
        this.intValue = intValue;
    }

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
            throws HibernateException {
        final String[] columns = criteriaQuery.findColumns( propertyName, criteria );
        if ( columns.length != 1 ) {
            throw new HibernateException( "Crazy Like may only be used with single-column properties" );
        }

        final String column = columns[0];
        return "cast(" + column + " as text) like  '%" + intValue + "%'";
    }

    @Override
    public TypedValue[] getTypedValues(Criteria criteria,
            CriteriaQuery criteriaQuery) throws HibernateException {
        return new TypedValue[] { };    
    }

}

And then use it like this:

Criteria criteria = session.createCriteria(Person.class);
List<Person> persons = criteria.add(new CrazyLike("year", 17)).list();

assuming that Person has an int property called year. This should produce a SQL like this:

select
    this_.id as id1_2_0_,
    this_.birthdate as birthdat2_2_0_,
    this_.firstname as firstnam3_2_0_,
    this_.lastname as lastname4_2_0_,
    this_.ssn as ssn5_2_0_,
    this_.version as version6_2_0_,
    this_.year as year7_2_0_ 
from
    Person this_ 
where
    cast(this_.year as text) like  '%17%'

This was tested with Postgres. The cast() syntax may vary for your database engine. If it is, just use that syntax in the Criterion class that you implement.

Upvotes: 0

prem kumar
prem kumar

Reputation: 5877

Achieving like on numeric values using criteria builders

Table

Employee | CREATE TABLE `Employee` (
`id` int(11) NOT NULL,
`first` varchar(255) DEFAULT NULL,
`last` varchar(255) DEFAULT NULL,
`occupation` varchar(255) DEFAULT NULL,
`year` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Entity

private Integer year;
public Integer getYear() {
    return year;
}
public void setYear(Integer year) {
    this.year = year;
}

Data in the table

+----+-------+------+------------+------+
| id | first | last | occupation | year |
+----+-------+------+------------+------+
|  2 | Ravi  | Raj  | Textile    | 1718 |
|  3 | Ravi  | Raj  | Textile    | 1818 |
|  4 | Ravi  | Raj  | Textile    | 1917 |
|  5 | Ravi  | Raj  | Textile    | NULL |
|  6 | Ravi  | Raj  | Textile    | NULL |
|  7 | Ravi  | Raj  | Textile    | NULL |
+----+-------+------+------------+------+

constructing query using criteria builder

public List<Employee> getEmployees() {

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Employee> q = cb.createQuery(Employee.class);
    Root<Employee> emp = q.from(Employee.class);
    Predicate year_like = cb.like(emp.<Integer>get("year").as(String.class), "%17%");
    CriteriaQuery<Employee> fq = q.where(year_like);

    List<Employee> resultList = (List<Employee>) entityManager.createQuery(fq).getResultList();
    return resultList;
}

query generated(using show_sql: true)

Hibernate: select employee0_.id as id1_0_, employee0_.first as first2_0_, employee0_.last as last3_0_, employee0_.occupation as occupati4_0_, employee0_.year as year5_0_ from Employee employee0_ where cast(employee0_.year as char) like ?

Query Output

// i have printed only id and year in the console
id, year
2, 1718
4, 1917

------------------------------------------------------------

Alternate way
LIKE worked in JPA for numeric field when Tested with JPA, hibernate, mysql.
Note:- May not work with other jpa providers

Query r = entityManager.createQuery("select c from Employee c where c.year like '%17%'");

query fired(using show_sql=true)

Hibernate: select employee0_.id as id1_0_, employee0_.first as first2_0_, employee0_.last as last3_0_, employee0_.occupation as occupati4_0_, employee0_.year as year5_0_ from Employee employee0_ where employee0_.year like '%17%'

Query Result

// i have printed only id and year in the console
id, year
2, 1718
4, 1917

Upvotes: 2

Related Questions