"where" condition not working correctly in join query in JPA (native query)

I am using Spring Boot Starter Web with MYSQL and JPA. I have created Entities as:

parent:

@Entity
@Table(name = "parent")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Parent{

   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private Long id;

   @OneToMany(cascade = CascadeType.ALL)
   @JoinColumn(name = "control_id", referencedColumnName = "id")
   private List<Child> childList;

}

Child:

@Entity
@Table(name = "child")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Child{

   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   private Long id;
   private Long control_id;
   private Boolean is_active;

}

I have 2 DAOs for both of them as

@EnableJpaRepositories
public interface ParentDao extends JpaRepository<Parent, Long> {
     @Query(value = "SELECT * FROM parent as p inner join child as c on p.id = c.control_id WHERE (c.is_active = 1 and p.id = :entry_id)", nativeQuery = true)
    Parent findActiveById(@Param("entry_id") Long aLong);
}

@EnableJpaRepositories
public interface ChildDao extends JpaRepository<Child, Long> {
      
}

Here what I am trying to achieve is, I want to fetch an Object of Parent with all the Child which are active. but this returns everything, is_active = 1 as well as 0.

You need to use join fetch child instead of join child in order that JPA uses only one SQL Query and returns the parent items having the good child

You need to return only the parent, not the whole join. As it is, your query returns a collection of parent and child joined together.

SELECT p FROM parent p inner join child c on p.id = c.control_id WHERE (c.is_active = 1 and p.id = :entry_id)

You already have child repository which you can use to fetch children

@EnableJpaRepositories
public interface ChildDao extends JpaRepository<Child, Long> {
      // Method in this manner will be automatically converted into a query
      List<Child> findByControl_idAndIs_active(Long control_id, Boolean is_active);
}

// Use it like this
List<Child> children = childDao.findByControl_idAndIs_active(control_id, true); 

// Then fetch the Parent separately or can fetch before also
Optional<Parent> parent = parentDao.findById(parent_id);

If you are wondering from where these methods came then please See This

But if you want to fetch parent and child both in one query as a same structure then it wouldn't be possible, one thing you can do is fetch using custom query into a List of Dto with required fields and then create parent-child like structure in your code.

One suggestion, you should name variable in camel case in java i.e controlId instead of control_id and use @column(name='control_id') but its your choice