8

This is the query i want to achieve:

 SELECT *
FROM ROOMENTITY R
JOIN CALENDARENTITY C ON R.id = C.room_id
WHERE C.available = TRUE AND
      C.date BETWEEN :checkin AND
      :checkout
GROUP BY C.room_id
HAVING COUNT(C.room_id)>= diffdays

This is my code:

manager = factory.createEntityManager();
boolean hasPredicates = false;
final CriteriaBuilder criteriaBuilder = manager.getCriteriaBuilder();

final CriteriaQuery<RoomEntity> q = criteriaBuilder.createQuery(RoomEntity.class);

List<Predicate> predicates = new ArrayList<Predicate>();

Root<RoomEntity> fromRooms = q.from(RoomEntity.class);
String checkinDate = filter.getCheckinDate();
String checkoutDate = filter.getCheckoutDate();
if (checkinDate != null || checkoutDate != null) {
    hasPredicates = true;
    java.sql.Date checkin = java.sql.Date.valueOf(filter.getCheckinDate());
    java.sql.Date checkout = ava.sql.Date.valueOf(filter.getCheckoutDate());
    int diffInDays = (int)( (checkout.getTime() - checkin.getTime()) / (1000 * 60 * 60 * 24) ) + 1;
    System.out.println("Days: " + diffInDays);
    Join<RoomEntity, CalendarEntity> calendarEntityJoin= fromRooms.join("calendarEntities", JoinType.INNER);
    calendarEntityJoin.on(
                        criteriaBuilder.equal(calendarEntityJoin.join("calendarId").<Integer>get("room_id"),  fromRooms.<Integer>get("id"))
                );
    predicates.add(criteriaBuilder.and(
                        criteriaBuilder.equal(calendarEntityJoin.<String>get("available"), true),
                        criteriaBuilder.between(calendarEntityJoin.join("calendarId").<Date>get("date"),
                                checkin, checkout)
                ));
    q.groupBy(calendarEntityJoin);
    q.having(criteriaBuilder.gt(criteriaBuilder.count(fromRooms), diffInDays));
}

My Room Entity:

public class RoomEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "id", nullable = false, unique = true)
    private Integer id;

    @OneToMany(mappedBy = "roomEntity", cascade = CascadeType.ALL)
    private Collection<CalendarEntity> calendarEntities;
}

My CalendarEntity:

public class CalendarEntity {

@EmbeddedId
private CalendarId calendarId;

@Basic
@Column(name = "available", nullable = false)
private Boolean available;

@ManyToOne
@JoinColumn(name = "room_id", referencedColumnName = "id", insertable = false, updatable = false)
private RoomEntity roomEntity;
}

My CalendarEntity Embedded id:

@Embeddable
public class CalendarId implements Serializable {

    @Column(name= "date", nullable = false)
    private Date date;

    @Column(name = "room_id", nullable = false)
    private Integer room_id;
}

Everything is good up until group by and having count. When i run this and i print the query with this code below, then there is groupby/having:

if (hasPredicates)
            q.where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
final TypedQuery<RoomEntity> TQ = manager.createQuery(q);
TQ.setFirstResult(filter.getStart());
TQ.setMaxResults(filter.getSize());
System.out.println("\n\nQuery:\n" + +Q.unwrap(JpaQuery.class).getDatabaseQuery().getSQLString() + "\n\n\n");

The query in the end is not what i want and it does implement group by and having count.

UPDATE: This is the query i am getting right now:

SELECT t1.id, t1.bathrooms, t1.bedrooms, t1.beds, t1.description, t1.exta_person_price, t1.house_rules, t1.max_people, t1.min_overnights, t1.neightborhood, t1.overnight_price, t1.square_meters, t1.title, t1.transport, t1.room_host, t1.room_location, t1.room_type_id FROM CALENDARENTITY t0, ROOMENTITY t1 WHERE (((t0.available = ?) AND (t0.date BETWEEN ? AND ?)) AND ((t0.room_id = t1.id) AND (t0.room_id = t1.id))) ORDER BY t1.overnight_price ASC

It is missing group by and having count!

peterthunder
  • 201
  • 2
  • 7
  • Add some information about your expected result or errors which you are getting. – Ashish Lohia Jul 20 '17 at 03:31
  • What i am expecting is the top query to be created, but in the end what is getting created is the top query with the 2 last lines, group by and having count. – peterthunder Jul 20 '17 at 03:37
  • Did you check if you have created the entities properly, using proper annotations you need not write so much code. Also, verify the query you are trying to create, it can be easily modified to do the same job in a more cleaner way. – Ashish Lohia Jul 20 '17 at 03:48
  • The query i am trying to get in the end, already works if i run it as it is, but i cannot build it correctly with criteria builder. Group by and having count do not work and i dont know why. – peterthunder Jul 20 '17 at 14:59
  • Share code for RoomEntity and CalendarEntity @peterthunder . – Ashish Lohia Jul 21 '17 at 03:20
  • I typed the basics of my 2 entities. – peterthunder Jul 21 '17 at 10:01
  • `` Another case where 3rd party software gets in the way of writing a query, rather than helping? `` In this case it sounds like the SQL was easier to learn than the Builder package. – Rick James Jul 23 '17 at 16:08
  • Why not just manager.createNativeQuery("SELECT ... ",RoomEntity.class) – ikettu Jul 24 '17 at 13:03
  • The query worked after i used multiselect. Thanks everyone for your answers. – peterthunder Jul 24 '17 at 14:40

1 Answers1

5

The query worked after i used multiselect. 

    Q.multiselect(fromRooms);
peterthunder
  • 201
  • 2
  • 7