[FIXED] Hibernate produces broken query

Issue

I have an entity that is mapped to the database table by Hibernate:

@Entity
@NoArgsConstructor
@Getter 
@Setter
@FieldNameConstants
public class Set extends AbstractEntity {
    
    @ManyToOne(fetch = FetchType.EAGER, cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinColumn(name = "owner_id")
    private User owner;
    
    private LocalDate date;
    
    private String password;
    
    @Enumerated(EnumType.STRING)
    private SetStatus status;
    
    @ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @Fetch(value = FetchMode.SUBSELECT)
    @JoinTable(name = "set_user", joinColumns = @JoinColumn(name = "set_id"), inverseJoinColumns = @JoinColumn(name = "user_id"))
    private List<User> users;
    
    @ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @Fetch(value = FetchMode.SUBSELECT)
    @JoinTable(name = "cocktail_set", joinColumns = @JoinColumn(name = "set_id"), inverseJoinColumns = @JoinColumn(name = "cocktail_id"))
    private List<Cocktail> cocktails;
}

And the mapping is done in this Spring service:

@Service
public class SetService {
    
    @Autowired
    private SetRepository setRepository;
    
    public Page<Set> findAll(HttpSession session, SetSearch search, Pageable pageable) {
        
        Specification<Set> specification = SetSpecifications.empty();
        
        UUID id = (UUID)session.getAttribute("userId");
        UserRole role = (UserRole)session.getAttribute("userRole");
        if (role == UserRole.BARMEN)
            specification = specification.and(SetSpecifications.setsForBarmen(id));
        else if (role == UserRole.USER)
            specification = specification.and(SetSpecifications.setsForUser(id));
        if (search.getDate() != null)
            specification = specification.and(SetSpecifications.dateEquals(search));
        if (search.getStatus() != null)
            specification = specification.and(SetSpecifications.statusEquals(search));
        
        Sort sortByDate = Sort.by(Sort.Direction.DESC, "date");
        return setRepository.findAll(specification, ((PageRequest)pageable).withSort(sortByDate));
    }

}

But when I send a request to this service, Hibernate produces a query which causes SQLSyntaxErrorException with a hella long stack trace.

The query is:

select
        set0_.id as id1_5_,
        set0_.date as date2_5_,
        set0_.owner_id as owner_id5_5_,
        set0_.password as password3_5_,
        set0_.status as status4_5_ 
    from
        
    set
        set0_ 
    where
        1=1 
    order by
        set0_.date desc limit ?

And the exception message begins with:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set set0_ where 1=1 order by set0_.date desc limit 10' at line 1

The query looks perfectly fine except for this stupid newline after from, which judging by the exception message seems to be the reason. It looks to me like Hibernate really sh*t its pants this time.

I want to get an answer, which is strictly to the point i. e. that provides a working solution. If you don't have one, just walk past this and don't waste your time writing and my time reading.

Solution

Table name set is causing you the issue. set is a reserved keyword for MySQL. Take a look at the MySQL documentation

Answered By - techtabu
Answer Checked By - Katrina (JavaFixing Volunteer)