2

I'm using Java 11 and Spring Boot 2.5. I have a JPA entity with a @Transient field

@Entity
public class MyEntity {
    ...
    private String name;
    ...
    @Transient
    private String group;
}

and a JPA repository

public interface MyEntityRepository
    extends JpaRepository<MyEntity, Long>, JpaSpecificationExecutor<MyEntity> {
    ...
    ...
}

The relationship between MyEntity and Gruop is that the my_entity table has a group_id column that links to the primmary key of the group table (named "id"). Is there a way I can override "findAll" to retrieve all the columns of MyEntity and the additional "group" String field? I would prefer not to use a @ManyToOne annotation to link the entire Group object to the MyEntity class.

Mauricio Buffon
  • 332
  • 1
  • 8
Dave
  • 15,639
  • 133
  • 442
  • 830
  • I've 2 questions. 1) What is the type of group.id? 2) Thinking in the group entity, to which column would this transient string relates to? It would be nice if you could show some of the group entity. Thanks! – Mauricio Buffon Apr 29 '23 at 18:55

5 Answers5

1

This can be achieved with DTO-returning query:

package com.my.dto;

@Getter
@RequiredArgsContructor
public class MyDto {
  private final String name;
  private final String group;
}

package com.my.repository;

public interface MyEntityRepository
    extends JpaRepository<MyEntity, Long>, JpaSpecificationExecutor<MyEntity> {

  @Query("select new com.my.dto.MyDto(e.name, g.groupName) from MyEntity e
          join GroupEntity g on e.groupId = g.id")
  List<Dto> findAllAsDto();

}

N.B. As I.Brok mentioned in his answer this approach would require adding groudId column to MyEntity, which must be mapped onto my_entity.group_id column in order to join Group entity (which in turn must declare groupName field).

P.S. As of entities I'd have them like these:

@Entity
public class MyEntity {
    ...
    private String name;
    ...
    @Column("group_id")
    private Long groupId;
}

@Entity
public class GroupEntity {
    @Id
    private Long id;
    ...
    @Column("group_name")
    private String groupName;
}
Sergey Tsypanov
  • 3,265
  • 3
  • 8
  • 34
  • I'm almost there but could you list out how the various entities (including any new ones you are creating) would look like? – Dave Apr 28 '23 at 14:17
  • @Dave sure, give me a minute – Sergey Tsypanov Apr 28 '23 at 14:38
  • @Dave after you make it work you could try to remove explicit column name declaration, I guess Hibernate will automatically match underscore declaration of DB columns to camel case of Java fields. – Sergey Tsypanov Apr 28 '23 at 14:45
0

goodnight friend,

I believe that as JPA is not aware of the relationship between the tables by not using @OneToMany you have to do it manually, example of a findAll() method;

@Repository
public interface MyEntityRepository
extends JpaRepository<MyEntity, Long>, JpaSpecificationExecutor<MyEntity> {
     ...
     ...
}

@Service
public class MyEntityService {

@Autowired
private MyEntityRepository myEntityRepository;

@Autowired
private MyGroupRepository myGroupRepository;

public List<MyEntity> findAll() {

    List<MyEntity> listMyEntity = myEntityRepository.findAll();
       
    for (MyEntity my = listMyEntity) {
         Group group = myEntityRepository.findById(my.getId());
         my.setGroup(group);
    }       

    return listMyEntity;
}
  • This is not a good solution because you are introducing the N+1 problem, right? The N+1 problem being that for a single query, you are executing N additional queries to get the linked fields. – Dave Apr 26 '23 at 18:57
0

You could write a function with a custom query to join the two tables:

Something along the lines of (haven’t tried it out)

public interface MyEntityRepository extends JpaRepository<MyEntity, Long>, JpaSpecificationExecutor<MyEntity> {
@Query("select e.name, p.group_name from MyEntity e JOIN Group p on e.group_id = p.id")
List<Object[]> findEntities();

}

It wouldn’t return the MyEntity type though, you would have to map the result to something else.

You should then not do the @Transient group though, and make it a group_id column.

Also see this blog

I.Brok
  • 319
  • 1
  • 2
  • 16
0

Create class with name MyEntityRepositoryImpl in same package as MyEntityRepository and create method with exact syntax as present in JPARepository findAll(). This will guarantee that below code will be executed when you call findAll method on repository from anywhere.Ref to the documentation https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.single-repository-behavior

@Component 
public class MyEntityRepositoryImpl{

  @Autowired
  EntityManager em;

  public List<MyEntity> findAll(){
    List<MyEntity> ls=em.createQuery("select t from MyEntity t ",MyEntity.class).getResultList();
   
    ls.forEach(o->{
     Query qry= em.createQuery("Query for Group Name").getResultList();
     qry.setParamter("groupId", o.getGroupId);
    String groupName=(String)qry.firstResult();
     o.setGroupName(groupName);
    });
    return ls;

  }
}
Abhinay
  • 464
  • 5
  • 13
0

Alright, I could make it work the following way.

First, the entities classes:

@Entity
public class MyEntity {

    @Id
    private int id;
    private String name;
    @Column(name = "group_id")
    private int groupId; /* Assuming an integer, but could be whatever type was defined in Group.id */
    @Transient
    private String group;

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public int getGroupId() {
        return groupId;
    }

    public String getGroup() {
        return group;
    }

    void setGroup(String group) {
        this.group = group;
    }
}

@Entity
public class Group {

    @Id
    private int id;
    private String name;

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }
}

public record MyEntityDTO(Integer id, String name, String group) { }

Next, the repositories:

public interface GroupRepository extends JpaRepository<Group, Integer> {

    @Query("select g.name from Group g where g.id = :id")
    String findGroupName(int id);
}

public interface MyEntityCustomRepository {

    List<MyEntity> findAllWithGroupName();
}

@Repository
class MyEntityCustomRepositoryImpl implements MyEntityCustomRepository {

    @Autowired
    private EntityManager em;
    @Autowired
    private GroupRepository groupRepository;

    @Override
    public List<MyEntity> findAllWithGroupName() {
        return em.createQuery("select e from MyEntity e", MyEntity.class)
                .getResultStream()
                .peek(e -> e.setGroup(groupRepository.findGroupName(e.getGroupId())))
                .collect(Collectors.toList());
    }
}

public interface MyEntityRepository extends JpaRepository<MyEntity, Integer>, MyEntityCustomRepository {

    /* If you don't like the above alternative, you can use DTOs, as suggested by @sergey-tsypanov */
    @Query("select new MyEntityDTO(e.id, e.name, g.name) from MyEntity e join Group g on e.groupId = g.id")
    List<MyEntityDTO> findAllWithDTO();
}

Then, some data for testing (inside src/main/resources/data.sql).

insert into Groups (id, name) values (1, 'Group One');
insert into Groups (id, name) values (2, 'Group Two');
insert into Groups (id, name) values (3, 'Group Three');

insert into Entities (id, name, group_id) values (1, 'Entity One', 1);
insert into Entities (id, name, group_id) values (2, 'Entity Two', 2);
insert into Entities (id, name, group_id) values (3, 'Entity Three', 3);

And, finally, a dummy controller just to see if everything is ok.

@RestController
public class MyEntityController {

    @Autowired
    private MyEntityRepository myEntityRepository;

    @GetMapping("entities")
    public String getEntities() {
        for (MyEntity entity : myEntityRepository.findAllWithGroupName()) {
            System.out.println(entity.getGroup());
        }

        return "It works!";
    }

    @GetMapping("entitiesdto")
    public Collection<MyEntityDTO> getEntitiesDto() {
        return myEntityRepository.findAllWithDTO();
    }
}

If you don't like querying the Group table separatelly for each MyEntity object, you can use a DTO, as suggested by @sergey-tsypanov.

Mauricio Buffon
  • 332
  • 1
  • 8