Filter by partials primary keys for Jpa Query-CodePudding

I have a Employee (Employee table) entity class which contain composite primary key EmployeeId (name,location,status). I need to filter by only selected primary keys (name, location).

@Entity
@Table(name = "EMPLOYEE")
class Employee {

 @EmbeddedId
 EmployeeId id;
 
 @Column(name = "DESC")
 String desc;
}

public class EmployeeId implements Serializable {
  @Column(name = "name")
  private String name;

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

  @Column(name = "status")
  private String status;
}

But i cannot use following, Because I dont have the status value :

interface EmployeeJpaRepository  extends JpaRepository<Employee, EmployeeId > {
  List<Employee> findAllByIdIn(Set<EmployeeId > employeeId);
}

I can use this :

interface EmployeeJpaRepository  extends JpaRepository<Employee, EmployeeId > {
  List<Employee> findAllByIdNameAndIdPlanLocation(String name, String location);
}

This is each time i need to query. So bad for performance.

Any better approach based on performance ?

CodePudding user response：

There's another interesting way of achieving this with Spring Data Jpa Specifications.
Start by extending your repository with JpaSpecificationExecutor<Employee> like such :

public interface EmployeeRepository
    extends JpaRepository<Employee, EmployeeId>, JpaSpecificationExecutor<Employee> {}

Then, assuming you have the employee name and location in a map (any iterable will do):

Map<String, String> nameLocations = Map.of("Bob", "Nowhere", "Jules", "Here");

Specification<Employee> employeeSpec = Specification.where((root, query,
    builder) -> builder.or(nameLocations.entrySet().stream()
        .map(nameLocation -> builder.and(
            builder.equal(root.get("id").get("name"), nameLocation.getKey()),
            builder.equal(root.get("id").get("location"), nameLocation.getValue())))
        .toArray(Predicate[]::new)));

List<Employees> employeesByNameAndLocation = employeeRepository.findAll(employeeSpec);

This will give you the employees named "Bob" with location "Nowhere" and the employees named "Jules" with location "Here" by generating the following single query : select employee0_.location as location1_0_, employee0_.name as name2_0_, employee0_.status as status3_0_, employee0_.desc as desc4_0_ from EMPLOYEE employee0_ where employee0_.name='Bob' and employee0_.location='Nowhere' or employee0_.name='Jules' and employee0_.location='Here'

If you don't like Specifications, you could also use an embedded object like @Pranay Srivastava suggested:

@Embeddable
@Immutable
public class PartialEmployeeId implements Serializable {
  @Column(name = "name", insertable = false, updatable = false)
  private String name;

  @Column(name = "location", insertable = false, updatable = false)
  private String location;

  

  public PartialEmployeeId(String name, String location) {
    this.name = name;
    this.location = location;
  }

  public PartialEmployeeId() {}
}

NOTE the insertable and updatable flags to prevent repeated column mapping. Then embed it in your Employee

@Entity
@Table(name = "EMPLOYEE")
public class Employee {

  @EmbeddedId
  EmployeeId id;

  @Embedded
  PartialEmployeeId partialId;

  @Column(name = "DESC")
  String desc;
}

, update your repository by using the derived query from Spring data JPA :

public interface EmployeeRepository
    extends JpaRepository<Employee, EmployeeId>{
  List<Employee> findByPartialIdIn(List<PartialEmployeeId> partialEmployeeIds);
}

And use it like this:

PartialEmployeeId nameAndLocation1 = new PartialEmployeeId("Bob", "Nowhere");
PartialEmployeeId nameAndLocation2 = new PartialEmployeeId("Jules", "Here");
List<Employees> employeesByNameAndLocation = employeeRepository.findByPartialIdIn(List.of(nameAndLocation1, nameAndLocation2));

This generates the same single SQL query as the Specifications.

CodePudding user response：

There are different approaches that you can consider:

1. Make 2 composite primary keys Make a composite primary key with name and location. Another one with all the attributes (name, location, and status).

2. Write a custom query of your respective database vendor which you're using (MySQL, PostgreSQL, etc.). Use @Query Annotation just above the method which you're calling in the Repository class.

Example:

@Query("Select e from EmployeeId where e.name like %?1% and e.location like %?1%")
    List<Employee> findAllByIdNameAndIdPlanLocation(String name, String location);

I'm not sure they will improve the performance as required by you. But you can consider them as well.

UPDATE

Please refer to the links for more coverage on the topic. Composite Primary Keys in JPA Spring JPA @Embedded and @EmbeddedId