'SQL statement in JPA to get all equipments that arent rented at this day and arent created by the user
I want to get all Equipments, that arent rented (wirtten down in the contract-table) and arent created by the user who is searching for equipments.
this is what i have:
public List<Equipment> findAllFreeEquipmentsByUserIdAndRentingDate(Integer userId, LocalDate rentingDate){
String sql =
"SELECT EQUIPMENT.* FROM EQUIPMENT " +
"WHERE EQUIPMENT.ID != (SELECT CONTRACT.EQUIPMENT_ID FROM CONTRACT WHERE RENTING_DATE=?) " +
"AND EQUIPMENT.OWNER_ID !=? ";
List<Equipment> equipments = em.createNativeQuery(sql, Equipment.class)
.setParameter(1, rentingDate)
.setParameter(2, userId)
.getResultList();
return equipments;
}
When iam testing this with an Equipment that doesnt have a contract already, the result is always 0, bcs the (SELECT CONTRACT.EQUIPMENT_ID FROM CONTRACT WHERE RENTING_DATE=?) wont find any id.
I dont know how to fix this problem..
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|