Query in arraylist by one element in spring boot h2

Question

I have a ClassRoom class, in my Spring Boot application. Here is what it looks like :

public class ClassRoom {

@Id @GeneratedValue Long classRoomID;

ArrayList<User>adminList=new ArrayList<>();
}

And in my ClassRoomRepository class, I have :

public interface ClassRoomRepository extends JpaRepository<ClassRoom,Long> {

@Query("select ClassRoom from ClassRoom c where c.adminList = ?1")
ArrayList<ClassRoom> findByAdminList(ArrayList<User> adminList);
/*
@Query("select ClassRoom from ClassRoom c where c. = ?1")
ArrayList<ClassRoom> findByAdmin(User admin);
*/
}

I can query to select ClassRoom where ArrayList of ClassRoom gets passed parameter.

But I want to query to select ClassRoom where I pass only one User as parameter and returns ArrayList of ClassRoom.(Commented section-nothing done so far)

If it is possible in this interface, how can I do so?

score 1 · Accepted Answer · answered Aug 31 '19 at 08:18

1

Asuming you habe many-to-many association using join table, this is what you need,

 @Query(value = "SELECT DISTINCT cr FROM ClassRoom cr JOIN cr.admins a WHERE a = ?1")
 List<ClassRoom> findAllByAdmin(final Admin admin);

answered Aug 31 '19 at 08:18

Amit Patil

710
9
14

can I have your mail or some contact, actually I need to discuss with you on [this](https://stackoverflow.com/questions/57973253/spring-boot-field-is-always-null) and [this too](https://stackoverflow.com/questions/57985516/jpa-default-column-value-of-custom-class). – Maifee Ul Asad Sep 19 '19 at 16:41

score 1 · Answer 2 · answered Aug 31 '19 at 09:27

When mapping collection , Hibernate requires it to declare it using interface type such as List , Set , Map etc. But you are using ArrayList to declare the admin list , I am guessing the whole list will be stored to a single column with some binary data type in DB . No one would store the data in this funny way when using RDBMS unless you have strong reason to do it.

From what you describe , ClassRoom and User seem to be many to many. For demonstration convenience , I would map it as @ManyToMany:

@Entity
@Table
public class ClassRoom {

    @ManyToMany
    @JoinTable(name ="classroom_user",
             joinColumns = @JoinColumn(name = "classroom_id"),
            inverseJoinColumns = @JoinColumn(name = "user_id"))
    private List<User> admins = new ArrayList();
}

@Entity
@Table
public class User{

        @ManyToMany(mappedBy = "admins")
        private List<ClassRoom> classRooms  = Lists.newArrayList();
}

Then given an user admin ,to get all of his administrative class rooms :

@Query("select distinct cr from ClassRoom cr left join fetch cr.admins admin where admin = ?1 ")
public List<ClassRoom> findByAdmin(User admin);

Or :

@Query("select distinct cr from ClassRoom cr where ?1 member of cr.admins")
public List<ClassRoom> findByAdmin(User admin);

In the `User` class, shouldn't it be `@ManyToMany(mappedBy = "user")` according to [this](https://stackoverflow.com/a/57694662/10305444) , instead of `admins` — Maifee Ul Asad, Aug 31 '19 at 12:24
Hi Maifee, No it should be the property name in the `ClassRoom` , so it is `admins` in my example . — Ken Chan, Aug 31 '19 at 12:30

Query in arraylist by one element in spring boot h2

2 Answers2