1

I have a parent table and a child table where I am only getting 1 record from child table but not getting case insensitive matched record which is a mixed string. I am expecting it should return 2 records.

Below is the code for the same.

//parent Table
@Entity
@Table(name = "employee")
public class Employee implements Serializable {

  @Id
  @Column(name = "employeeID")
  private String employeeID;

  @Column(name = "name_first")
  private String nameFirst;

  @Column(name = "name_last")
  private String nameLast;

}

//Child Table
@Entity
@Table(name = "employee_salary")
public class EmployeeSalary implements Serializable {

  @EmbeddedId
  private EmployeeSalaryPK employeeSalaryPKCompositeKey;

  @Column(name = "salaryBracket")
  private String salaryBracket;

}

@Embeddable
public class EmployeeSalaryPK implements Serializable {

  @Column(name = "employeeID")
  private String employeeID;

  @Column(name = "salary")
  private String salary;

}

In employee_salary table I have two records (as shown below) but while fetching it using HQL only one record is coming with an actual match but case insensitive record is not coming.

Employee Record:- ABC John Kramer

employee_salary table record:-

ABC 100900

aBc 76770

I am using simple HQL query (see below code) but getting only first record whenever I want to get both record as employeeID is abc.

String hqlQuery = " FROM " + Employee.class.getName() + " E WHERE E.employeeID= :EMPLOYEEID";
Session session = entityManager.unwrap(Session.class);
List<?> responseList = session.createQuery(hqlQuery).setParameter("EMPLOYEEID", "ABC").list();
Syntle
  • 5,168
  • 3
  • 13
  • 34
ACP
  • 11
  • 2

1 Answers1

0

To get all entities by case insensetive String id you have to convert id to same case (lowercase or uppercase) on both sides of the WHERE clause equality operator 

String hqlQuery = " FROM " + Employee.class.getName() + " E WHERE lower(E.employeeID) = :EMPLOYEEID";
Session session = entityManager.unwrap(Session.class);
List<?> responseList = session.createQuery(hqlQuery).setParameter("EMPLOYEEID", "ABC".toLowerCase()).list();
Oleksii Valuiskyi
  • 2,691
  • 1
  • 8
  • 22