Query by other entity's id in JPA repository-CodePudding

I have an SQL database with two related tables, my_entities and custom_entities. The my_entities table has the columns id, custom_entity_id.

In my Groovy code, both are handled using Entity classes:

@Entity
class MyEntity  {
    @ManyToOne(fetch = EAGER)
    @JoinColumn(name = "custom_entity_id")
    CustomEntity customEntity

    [...]
}

and

class CustomEntity {
    @Id
    @GeneratedValue(generator = "pooled")
    @GenericGenerator(name = "pooled", strategy = "org.hibernate.id.enhanced.TableGenerator", parameters = [
            @Parameter(name = "value_column_name", value = "sequence_next_hi_value"),
            @Parameter(name = "prefer_entity_table_as_segment_value", value = "true"),
            @Parameter(name = "optimizer", value = "pooled"),
            @Parameter(name = "increment_size", value = "100"),
            @Parameter(name = "initial_value", value = "100")])
    Long id

    [...]
}

I have a JPA repository MyEntityRepository class for querying the my_entity table:

@Repository
interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
    [...]
}

In MyEntityRepository, I am trying to implement a findByCustomEntityIds() method like this:

    @Query("""
           select e
           from MyEntity e
           where e.deleted = false
           and e.customEntity in :ids
            """)
    List<TestCollectionQuery> findByCustomEntityIds(@Param("ids") List<Long> ids)

In MySQL, the query is rather simple (for instance with id 2400), clearly no joins required:

SELECT t.* FROM my_entities t WHERE custom_entity_id in (2400);

Apart from the automatic translation from underscores to camel case, the trailing _id in the SQL table column name is stripped; otherwise, the code does not compile.

I also have a custom Groovy class which calls that method:

class MyClass {
    @Autowired
    MyEntityRepository myEntityRepository

    List<MyEntity> getEntities(List<Long> customEntityIds) {
        return myEntityRepository.findByCustomEntityIds(customEntityIds)
    }

The call raises the following exception:

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value element [2400] did not match expected type [...CustomEntity (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value element [2400] did not match expected type [...CustomEntity (n/a)]
[...]
Caused by: java.lang.IllegalArgumentException: Parameter value element [2400] did not match expected type [...CustomEntity (n/a)]

From the error message, I understand that the input should be a list of CustomEntity objects, instead of IDs (Long).

A potential workaround seems to be querying the custom_entities table in order to convert the custom entity ids from Long to CustomEntity objects. However, that would require adding a JPA CustomEntityRepository, and seems very inefficient. It would require an additional database call just for the purpose of converting IDs into objects, so that I can eventually query for the IDs (which I already have had to begin with) in the my_entities table.

The reason why I have the IDs for the custom entities, but not the objects, is that they are provided by user input further up the line.

My question is thus: how can I implement a method in the MyEntityRepository that implements the MySQL query stated above based on a list of custom entity IDs, without converting them from Long into CustomEntity objects?

Or is there a more fundamental flaw in my design?

CodePudding user response：

The query should end in "and e.customEntity.id in :ids" not in "and e.customEntity in :ids".