CODEWITHSUNDEEP
javaspringhibernatejpql
makaveli
makaveli

Reputation: 25

JOIN two tables JPQL

@Entity
@Table(name="tblUser")    
public class User {

@Id
@GeneratedValue
@Column(name="id")
private Long id;

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

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

@Column(name="first_name")
private String firstName;

@Column(name="last_name")
private String lastName;

@OneToMany(mappedBy="user")
private List<Address> addresses = new ArrayList<>();
}



@Entity
@Table(name="tblAddress")
public class Address {

@Id
@GeneratedValue
@Column
private Long id;

@Column
private String streat;

@Column
private String number;

@ManyToOne(fetch=FetchType.EAGER)
private User user;
}



public interface UserService {
List<User> findAll(); 
}



@Service
public class JpaUserService implements UserService {
@Autowired
private UserRepository userRepository;

@Override
public List<User> findAll() {
    return userRepository.findAll();
    }
}



@Controller
@RequestMapping(value="/api/users")
public class ApiUserController {

@Autowired
private UserService userService;

@Autowired
private UserDTOToUser toUser;

@Autowired
private UserToUserDTO toDto;

@RequestMapping(method=RequestMethod.GET)
ResponseEntity<List<UserDTO>> getUser()

List<User> users = userService.findAll();

if(users == null || users.isEmpty()){
    return new ResponseEntity<>(HttpStatus.NOT_FOUND);
}

return new ResponseEntity<>(toDto.convert(users), HttpStatus.OK);
    }
}



@Repository
public interface UserRepository extends JpaRepository<User, Long> {

@Query("select u.id, a.number FROM User u LEFT JOIN u.addresses a WHERE u.id = a.user")
List<User> findAll();
}

When I try to run my code in RestClient on my localhost, I get this exception: 

{
"timestamp": 1511105123172,
"status": 500,
"error": "Internal Server Error",
"exception": "java.lang.ClassCastException",
"message": "[Ljava.lang.Object; cannot be cast to jwd.wafepa.model.User",
"path": "/api/users"
}

Just to mention, that UserDTO is the User class without password parameter.

How to write proper JOIN query in JPQL?

Upvotes: 1

Views: 23840

Answers (1)

Roger Ng
Roger Ng

Reputation: 779

@Query can only return List<Object> if only specific fields are selected from the table. You are advised to change the @Query to SELECT u FROM User u LEFT JOIN u.addresses a WHERE u.id = a.user.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u LEFT JOIN u.addresses a WHERE u.id = a.user")
    List<User> findAll();
}

Upvotes: 6

Related Questions