CODEWITHSUNDEEP
javaspring-data-jpasql-like
Martin Schröder
Martin Schröder

Reputation: 4591

How do I write a query with LIKE against a column with Spring Data?

I have a PSQL table with a text column and a query

select * from table where :number: like column||'%';

where :number: is a parameter.

How can I write that query in Spring Data JPA? The LIKE stuff of Spring Data seems to take only a text parameter, not a column name.

Upvotes: 0

Views: 809

Answers (1)

Madlemon
Madlemon

Reputation: 331

This is possible using JPA Criteria Queries or Specifications. Both use Path#get(String attributeName) to create a path corresponding to the referenced column name. I assume you have an entity named Table.

Example with Criteria Queries:

public List<Table> getCoulmnLikeNumber(String columnName, String number) {
    CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
    CriteriaQuery<Table> critQuery = builder.createQuery(Table.class);
    Root<Table> root = critQuery.from(Table.class);

    Expression<String> pattern = builder.concat(root.<String>get(columnName), "%");
    critQuery.select(root)
             .where(builder.like(builder.literal(number), pattern));

    return getEntityManager()
            .createQuery(critQuery)
            .getResultList();
}

A factory method for your specification could look similar to this:

public static Specification<Table> columnNameLikeNumber(String columnName, String number) {
    return new Specification<Table>() {
        @Override
        public Predicate toPredicate(Root<Entity> root,
                                     CriteriaQuery<?> query, CriteriaBuilder builder) {
            Expression<String> pattern = builder.concat(root.<String>get(columnName), "%");
            return builder.like(builder.literal(number), pattern);
        }
    };
}

Upvotes: 1

Related Questions