0

I'm trying to create a criteria to retrieve Rows from 2 tables (UserDetail, Vehicle). A UserDetail has reference to Vehicle. My objective is to retrieve a list of specified field from both Tables, given an UserDetail id. In @Query was easy to do but the client said that this must be coded with criteria with specification and predicate to Join Tables.

My @Query was:

@Repository
public interface UserDao extends CrudRepository<UserDetail, Integer>{

    @Query("SELECT ud.userId, ud.userName ,vh.vehicleId, vh.vehicleName "
            + "FROM UserDetail ud LEFT JOIN ud.vehicle vh where ud.vehicleId = vh.vehicleId")
    public List<UserVehicleDTO> findAllUserVehicle();

}

Can anyone help me? I Fetch Data Using Specification with predicate as given Query is needed

Sadina Khatun
  • 1,136
  • 2
  • 18
  • 27

1 Answers1

0

Criteria query(I did not tested/debugged it, may need modify):

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = cb.createTupleQuery();
    Root<UserDetail> root = query.from(UserDetail.class);

    Join<UserDetail, Vehicle> join = root.join("vehicle", JoinType.LEFT);
    query.multiselect(     
                    root.get("userId"),
                    root.get("userName"),
                    join.get("vehicleId"),
                    join.get("vehicleName")
            )) 
    List<Tuple> x = entityManager.createQuery(query).getResultList();

BTW. JPQL query is wrong. Don't need where condition, it will be created by JPA automatically.

SELECT ud.userId, ud.userName ,vh.vehicleId, vh.vehicleName 
FROM UserDetail ud 
LEFT JOIN ud.vehicle
Peter Šály
  • 2,848
  • 2
  • 12
  • 26