I have these entities (is an example because i cant share real name entities):

@Entity
public class User { @Id private BigDecimal id; private String name, private Color favouriteColor } 
@Entity
public class Color { @Id private Long colorId; private String colorName;}

In the table I have this data:

USER
ID|NAME|FavColor
1 |John| 1
2 |Sarah| 2
3 |Mike| 1

COLOR
1|Red
2|Blue

Now I want make a query that recover all my user data without select Color entity, only its ids.

@Query("new myDto(u.iduser,u.username,u.favcolor) from user u where favcolor in :listcolors")

This makes me an query of the two tables, I want a unique query because i dont need color entities, only the ids.

--

Other option that I am testing is making a implementation of a nativequery like this:

final List<MyDTO> result = new ArrayList<>();
        
        Query q = entityManager.createNativeQuery("SELECT  "   
                "    USER_ID, "   
                "    USER_NAME, "   
                "    FAV_COLOR "     
                "FROM USER "   
                "WHERE FAV_COLOR IN (?)");
        
        q.setParameter(1, colors.toString().replace("[","").replace("]",""));
        Long TRUE = new Long(1L);
        final List<Object[]> resultList = q.getResultList();
        for (Object[] objects : resultList) {

            MyDTOdto = new MyDTO();
            
            dto.userId(((((BigDecimal) objects[0]) != null) ? ((BigDecimal) objects[0]).longValue() : null));
            dto.userName(((((String) objects[0]) != null) ? ((String) objects[0]).longValue() : null));
            dto.favColor(((((BigDecimal) objects[0]) != null) ? ((BigDecimal) objects[0]).longValue() : null));
            
            result.add(dto);
        }
        
        return result;

In this case, I am getting error code (ORA-1722 - Number Not valid). I don't know what I can test now. Some ideas? Thanks

CodePudding user response：

I am guessing you have issues with the SQL generated and your use of the inner join: when you call "u.favcolor" in the select clause, you are telling JPA to perform an inner join from User to Color based on the favcolor relationship. As favcolor is a Color reference, you are going to get the full color row, where as your native query implies you just want the foreign key value. If all you want is the fk/ID value from Color, the query should be:

"SELECT new myDto(u.iduser, u.username, color.id) FROM user u join u.favcolor color WHERE color.id in :listcolors"

This still might perform an inner join from user to color, but it should be in a single statement.

If you want to ensure you avoid the join:

1. Use EclipseLink's COLUMN JPQL extension to access the foreign key column directly. Something like:

   "SELECT new myDto(u.iduser, u.username, COLUMN('FAV_COLOR', u) FROM user u join u.favcolor color WHERE COLUMN('FAV_COLOR', u) in :listcolors"
   

2. Use EclipseLink native query key functionality to access the "FAV_COLOR" foreign key column in the USER table directly for your JPQL queries. This requires a descriptor customizer to access, but allows you to use the foreign key value in JPQL queries directly without having to map it, and without the COLUMN mechanism tying your JPQL queries to a particular database table detail. This would allow a query of the form:

   "SELECT new myDto(u.iduser, u.username, u.favColorVal FROM user u join u.favcolor color WHERE u.favColorVal in :listcolors"
   

3. Just map the FAV_COLOR as a basic mapping, in addition to the existing favColor reference mapping (or replacing it if you want):

   @Basic
   @Column(name="FAV_COLOR", updatable=false, insertable=false)
   BigDecimal favColorId
   

This then allows you to use query "SELECT new myDto(u.iduser, u.username, u.favColorId FROM user u join u.favColorId color WHERE u.favColorId in :listcolors" to the same effect, but you can also just return the User instance (marking favColor as lazy and not serializable) as it will have the same data anyway.