mysqlspring-boothibernatehqlhibernate-entitymanager

HQL Responding with org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: u


I am trying to get a list of all the users in my system Using HQL and it throws me error org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: u here is my method Where I am trying to fetch all the users from DB I am using Mysql as database please have a look and let me know what is the issue here?

Get ALL method

@SuppressWarnings("unchecked")
    @Override
    @Transactional
    public List<Users> getAllUsers(Users userModel,List<String> errorList) {
        List<Users> allUsersNotRoleSpecific = null;
        try {                                           
            StringBuilder qryStr= new StringBuilder("SELECT u from Users u WHERE u.id !=0 AND u.status= "+Constants.userStatus.ACTIVE );
            if(Utils.isNotNull(userModel)) {
                if(Utils.isNotNull(userModel.getId())) {
                    qryStr.append("AND u.id=:id");
                }           
                if(Utils.isNotNull(userModel.getUniqueUserId())) {
                    qryStr.append("AND u.uniqueUserId=:uniqueUserId");
                }
                if(Utils.isNotNull(userModel.getFirstName())) {
                    qryStr.append("AND u.firstName=:firstName");
                }
                if(Utils.isNotNull(userModel.getLastName())) {
                    qryStr.append("AND u.lastName=:lastName");
                }
                if(Utils.isNotNull(userModel.getDateOfBirth())) {
                    qryStr.append("AND u.dateOfBirth=:dateOfBirth");
                }
                if(Utils.isNotNull(userModel.getEmail())) {
                    qryStr.append("AND u.email=:email");
                }
                if(Utils.isNotNull(userModel.getUserName())) {
                    qryStr.append("AND u.userName=:userName");
                }
                if(Utils.isNotNull(userModel.getPassword())) {
                    qryStr.append("AND u.password=:password");
                }
                if(Utils.isNotNull(userModel.getStatus())) {
                    qryStr.append("AND u.status=:status");
                }
                if(Utils.isNotNull(userModel.getRole())) {
                    qryStr.append("AND u.role=:role");
                }
                if(Utils.isNotNull(userModel.getCountry())) {
                    qryStr.append("AND u.country=:country");
                }
                if(Utils.isNotNull(userModel.getState())) {
                    qryStr.append("AND u.state=:state");
                }
                if(Utils.isNotNull(userModel.getCity())) {
                    qryStr.append("AND u.city=:city");
                }
                if(Utils.isNotNull(userModel.getZipCode())) {
                    qryStr.append("AND u.zipCode=:zipCode");
                }
                if(Utils.isNotNull(userModel.getAddress())) {
                    qryStr.append("AND u.address=:address");
                }
                if(Utils.isNotNull(userModel.getPhoneNumber())) {
                    qryStr.append("AND u.phoneNumber=:phoneNumber");
                }
                if(Utils.isNotNull(userModel.getAddedAt())) {
                    qryStr.append("AND u.addedAt=:addedAt");
                }
            }else {
                errorList.add("No Users Found !");
                logger.info("USER MODEL IS NULL !");
            }
            Query query = entityManager.createQuery(qryStr.toString());
            if(Utils.isNotNull(userModel)) {
                if(Utils.isNotNull(userModel.getId())) {
                    query.setParameter("id",userModel.getId());
                }           
                if(Utils.isNotNull(userModel.getUniqueUserId())) {
                    query.setParameter("uniqueUserId",userModel.getUniqueUserId());
                }
                if(Utils.isNotNull(userModel.getFirstName())) {
                    query.setParameter("firstName",userModel.getFirstName());
                }
                if(Utils.isNotNull(userModel.getLastName())) {
                    query.setParameter("lastName",userModel.getLastName());
                }
                if(Utils.isNotNull(userModel.getDateOfBirth())) {
                    query.setParameter("dateOfBirth",userModel.getDateOfBirth());
                }
                if(Utils.isNotNull(userModel.getEmail())) {
                    query.setParameter("email",userModel.getEmail());
                }
                if(Utils.isNotNull(userModel.getUserName())) {
                    query.setParameter("userName",userModel.getUserName());
                }
                if(Utils.isNotNull(userModel.getPassword())) {
                    query.setParameter("password",userModel.getPassword());
                }
                if(Utils.isNotNull(userModel.getStatus())) {
                    query.setParameter("status",userModel.getStatus());
                }
                if(Utils.isNotNull(userModel.getRole())) {
                    query.setParameter("role",userModel.getRole());
                }
                if(Utils.isNotNull(userModel.getCountry())) {
                    query.setParameter("country",userModel.getCountry());
                }
                if(Utils.isNotNull(userModel.getState())) {
                    query.setParameter("state",userModel.getState());
                }
                if(Utils.isNotNull(userModel.getCity())) {
                    query.setParameter("city",userModel.getCity());
                }
                if(Utils.isNotNull(userModel.getZipCode())) {
                    query.setParameter("zipCode",userModel.getZipCode());
                }
                if(Utils.isNotNull(userModel.getAddress())) {
                    query.setParameter("address",userModel.getAddress());
                }
                if(Utils.isNotNull(userModel.getPhoneNumber())) {
                    query.setParameter("phoneNumber",userModel.getPhoneNumber());
                }
                if(Utils.isNotNull(userModel.getAddedAt())) {
                    query.setParameter("addedAt",userModel.getAddedAt());
                }
            }else {
                errorList.add("No User were Found !!");
            }       
            allUsersNotRoleSpecific = query.getResultList();
        } catch (Exception e) {
            e.printStackTrace();
            logger.info("Error Occured at our End!! Please try again Later");
        }
        logger.info("RETURNED BY GET ALL USERS :: : " + allUsersNotRoleSpecific);
        return allUsersNotRoleSpecific;
    }

USER MODEL @Entity @Table(name="indo_users")

public class Users implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -7142538021673305659L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private Integer id;
    @Column(name="unique_user_id")
    private String uniqueUserId;
    @Column(name="first_name")
    private String firstName;
    @Column(name="last_name")
    private String lastName;
    @Column(name="date_birth")
    private String dateOfBirth;
    @Column(name="email")
    private String email;
    @Column(name="user_name")
    private String userName;
    @Column(name="password")
    private String password;
    @Column(name="status")
    private Integer status;
    @Column(name="role")
    private Integer role;
    @Column(name="country")
    private String country;
    @Column(name="state")
    private String state;
    @Column(name="city")
    private String city;
    @Column(name="zip_code")
    private String zipCode;
    @Column(name="address")
    private String address;
    @Column(name="gender")
    private Integer gender;
    @Column(name="phone_no")
    private String phoneNumber;
    @Column(name="added_at")
    private Timestamp addedAt;
    
    public Users() {
        
    }
    
    public Users(String uniqueUserId, String firstName, String lastName, String dateOfBirth, String email,
            String userName, String password, Integer status, Integer role, String country, String state, String city,
            String zipCode, String address, String phoneNumber,Timestamp addedAt,Integer gender) {
        this.uniqueUserId = uniqueUserId;
        this.firstName = firstName;
        this.lastName = lastName;
        this.dateOfBirth = dateOfBirth;
        this.email = email;
        this.userName = userName;
        this.password = password;
        this.status = status;
        this.role = role;
        this.country = country;
        this.state = state;
        this.city = city;
        this.zipCode = zipCode;
        this.address = address;
        this.phoneNumber = phoneNumber;
        this.addedAt = addedAt;
        this.gender = gender;
    }
    
    public Users(UserDTO userDto) {
        this.firstName = userDto.getFirstName();
        this.lastName = userDto.getLastName();
        this.dateOfBirth = userDto.getDateOfBirth();
        this.email = userDto.getEmail();
        this.userName = userDto.getUserName();
        this.password = userDto.getPassword();
        this.status = userDto.getStatus();
        this.role = userDto.getRole();
        this.country = userDto.getCountry();
        this.state = userDto.getState();
        this.city = userDto.getCity();
        this.zipCode = userDto.getZipCode();
        this.address = userDto.getAddress();
        this.phoneNumber = userDto.getPhoneNumber();
        this.gender = userDto.getGender();
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUniqueUserId() {
        return uniqueUserId;
    }

    public void setUniqueUserId(String uniqueUserId) {
        this.uniqueUserId = uniqueUserId;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getDateOfBirth() {
        return dateOfBirth;
    }

    public void setDateOfBirth(String dateOfBirth) {
        this.dateOfBirth = dateOfBirth;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Integer getRole() {
        return role;
    }

    public void setRole(Integer role) {
        this.role = role;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getZipCode() {
        return zipCode;
    }

    public void setZipCode(String zipCode) {
        this.zipCode = zipCode;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhoneNumber() {
        return phoneNumber;
    }

    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    public Timestamp getAddedAt() {
        return addedAt;
    }

    public void setAddedAt(Timestamp addedAt) {
        this.addedAt = addedAt;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "{\"id\":\"" + id + "\", \"uniqueUserId\":\"" + uniqueUserId + "\", \"firstName\":\"" + firstName
                + "\", \"lastName\":\"" + lastName + "\", \"dateOfBirth\":\"" + dateOfBirth + "\", \"email\":\"" + email
                + "\", \"userName\":\"" + userName + "\", \"password\":\"" + password + "\", \"status\":\"" + status
                + "\", \"role\":\"" + role + "\", \"country\":\"" + country + "\", \"state\":\"" + state
                + "\", \"city\":\"" + city + "\", \"zipCode\":\"" + zipCode + "\", \"address\":\"" + address
                + "\", \"gender\":\"" + gender + "\", \"phoneNumber\":\"" + phoneNumber + "\", \"addedAt\":\"" + addedAt
                + "\"}";
    }

    
}

Solution

  • Check/log the resulting built query first. It seems there are no spaces before ANDs.

    Out of topic. Have a look on Lombok and QueryDSL. They will dramatically simplify your code