Home > database >  How to access a child element in a query using jpa and hibernate - SpringBoot
How to access a child element in a query using jpa and hibernate - SpringBoot

Time:11-16

I would like to know how to query some child objects using their name as a parameter in Spring boot.

Let's say I have a class parent with a one-to-many relationship with the child.

The child has a parameter called name. So I would like to query using like "%name%" so the query would return me a list with all the child that the query finds.

I would like to do something like this:

@Query("select c from Parent c where lower(c.name) LIKE lower(CONCAT('%', :name, '%')) ")
    List<Parent> findByNameLIKE(@Param("name") String name);

But using the children that one parent have, I would search it using the id of the parent. I don't know if I am explaining it well, the thing is that I haven't been able to achieve the join between the classes.

Thanks!

Edit. these are the classes that I have:

Compania

@Entity(name = "Compania")
@Table(
    name = "compania"
)
public class Compania {
    @Id
    @SequenceGenerator(
        name = "compania_sequence",
        sequenceName = "compania_sequence",
        allocationSize = 1
    )
    @GeneratedValue(
        strategy = GenerationType.AUTO,
        generator = "compania_sequence"
    )
    @Column(
        nullable = false
    )
    private Long id;


    @Column(
        name = "name",
        nullable = false,
        unique = true
    )
    private String name;

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

    @OneToMany(
        cascade = CascadeType.ALL,
        fetch = FetchType.LAZY,
        orphanRemoval = true
    )
    private List<DefaultGroup> default_group;

And my child class, default_group

@Entity
@Table
public class DefaultGroup {
    @Id
    @SequenceGenerator(
        name = "defaultGroup_sequence",
        sequenceName = "defaultGroup_sequence",
        allocationSize = 1
    )
    @GeneratedValue(
        strategy = GenerationType.SEQUENCE,
        generator = "defaultGroup_sequence"
    )
    @Column(
        nullable = false
    )
    @JsonIgnore
    private Long id;

    @Column(
        name = "idRef",
        nullable = false
    )
    private int idRef;

    @Column(
        name = "name",
        nullable = false
    )
    private String name;

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

    @ManyToOne()
    private Compania compania;

So I would like to get the defaultGroups assigned to the Compania with id 1 where the name of the defaultGroup is like %x%. And I don't know how to achieve this. Thanks!

CodePudding user response:

If you have a Child entity in a parent entity Parent like this:

@Entity
class Parent {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    ...

    @OneToMany(mappedBy = "parent")
    private List<Child> childs;

    ....

Then you can use a query like this:

@Repository
public interface ParentRepository extends JpaRepository<Parent, Long> {
    List<Parent> getAllByChildsNameLike(String childName);
}

And spring data will generate the query for you.


If we want to get childs of a parent by id and other constraints we can do it like this:

@Repository
public interface ChildRepository extends JpaRepository<Child, Long> {
    List<Child> getAllByParentIdAndNameLike(Long parentId, String name);
}

Or if you want to use a jpa query in your ParentRepository:

@Query("select c from Child c where c.parent.id = ?1 and c.name like ?2")
List<Child> getChilds(Long parentId, String name);

You can substitute the childs field and entities with your entities.

  • Related