Hi All,
I meet a situation where I get a list of activities from third party service and the I need to fetch corresponding organizers which involve the activities. The code looks like:
List<Long> activityOrganizerIds = getFromExternalSide(); ... res = organizerService.findOrganizers(activityOrganizerIds); ... @Repository public interface OrgonizerRepository extends JpaRepository<Orgonizer,Long> { @Query("select * from organizer o where o.id in :ids") List<Organizer> findOrganizers(List<Long> ids); ... When activityOrganizerIds size reachs 400 around, the find query is very slow and I have to directly populate activityOrganizerIds into a temp table and use join table query for the results. The JdbcTemplate query looks like: "truncate table tmp_activity_organizer" "insert into tmp_activity_organizer (organizer_id) values(?)" with JdbcTemplate batch operation and then "select o.organizer_id, ... from organizer o " + "inner join tmp_activity_organizer.organizer_id ta on o.organizer_id = ta.organizer_id";
With above SQL statements and indexies built for both organizer id and tmp_activity_organizer id, the formance speed up.
Is there any way to optimize the in clause so that I won’t write above ugly code and hibernate or JPA could do the same thing or better in behind?’