JPA OR condition with single value

Question

I have a table company: companyId number, companyName varchar, address nvarchar. I want to achieve a goal that user can query to fetch company either using a id or company name, image there is a search box, user only has to input a single value, then it will fetch company information.

I wrote an repository

@Query(value = "select c from CompanyBasicInfo c where c.companyID = ?1 or c.companyName = ?1 ")
List<CompanyBasicInfo> findByCompanyIDOrCompanyName(@PathVariable String input);

But when I query, I got an error: Parameter value [10083769] did not match expected type [java.lang.Long (n/a)]

How can I solve this problem? Most of the materials available are something like findByFirstnameOrLastname which uses two values. Thanks a lot!

If it's either by `id` OR by `company name`, why don't you just wrap the method call in an _if-statement_ checking if the Search-Param (String) consists solely out of numbers? :o — Seth, Apr 27 '16 at 15:22
the `@PathVariable` looks rather odd. The issue is because company ID is a Long, you cannot "=" it with a string "?1". You could probably hack your way in using `select c from CompanyBasicInfo c where CAST(c.companyID AS String)= ?1 or c.companyName = ?1` — , Apr 27 '16 at 15:27
@RC This would work but should be discouraged because using an analytical function on a search criteria is inefficient. — Arnaud Denoyelle, Apr 27 '16 at 15:31
@Seth, we provide a service to front-side, so the logic you said could be done there, but provide one single service and let the front-side just invokes the service is kind of cool and simple. :) — Sai, Apr 28 '16 at 12:52
@ArnaudDenoyelle Thanks . The way you provide is good! But how to make it efficient? — Sai, Apr 28 '16 at 12:54

score 2 · Accepted Answer · answered Apr 27 '16 at 15:24

If none of the companies have a name which consists only of digits, you can create 2 methods (by companyId / by company name) and check wether your input is a number.

If you do not want to do this, you can use the "2 params" version :

List<CompanyBasicInfo> findByCompanyIDOrCompanyName(Long companyId, String companyName);

Then parse your input :

List<CompanyBasicInfo> find(String input) {
  Long companyId = null;
  try {
    companyId = Long.valueOf(input);
  } catch(Exception ignored){}

  return repository.findByCompanyIDOrCompanyName(companyId, input);
}

score -1 · Answer 2 · edited May 23 '17 at 12:33

-1

This is not a problem of parameters amount but of its type

You can try to treat companyId as String in query using cast function like here. How do I write hql query with cast?

Your code will look like

@Query(value = "select c from CompanyBasicInfo c where cast(c.companyID as String) = ?1 or c.companyName = ?1 ")
List<CompanyBasicInfo> findByCompanyIDOrCompanyName(@PathVariable String input);

edited May 23 '17 at 12:33

Community

1
1

answered Apr 27 '16 at 15:45

Johannes

1
1
2

It would be helpful to include code that helps rather than just linking to another similar answer. – Stephen Ostermiller Apr 27 '16 at 16:36

JPA OR condition with single value

2 Answers2