CODEWITHSUNDEEP
springhibernatemavenjakarta-eeh2
Akshay
Akshay

Reputation: 2763

Facing difficulty with Hibernate HQL when applying it to H2

I am new to hibernate and spring maven environment.

I have tried implementing an embedded db using H2, which earlier used MySQL. there are two DAO's

OffersDao.java

package com.skam940.main.dao;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Component("offersDao")
@Transactional
public class OffersDao {

    @Autowired
    private SessionFactory sessionFactory;

    public Session session() {
        return sessionFactory.getCurrentSession();
    }

    @SuppressWarnings("unchecked")
    public List<Offer> getOffers() {

        Criteria crit = session().createCriteria(Offer.class);
        crit.createAlias("user", "u").add(Restrictions.eq("u.enabled", true));
        return crit.list();

    }

    @SuppressWarnings("unchecked")
    public List<Offer> getOffers(String username) {

        Criteria crit = session().createCriteria(Offer.class);
        crit.createAlias("user", "u");

        crit.add(Restrictions.eq("u.enabled", true));
        crit.add(Restrictions.eq("u.username", username));

        return crit.list();

    }

    public void saveOrUpdate(Offer offer) {

        session().saveOrUpdate(offer);

    }

    public boolean delete(int id) {

        Query query = session().createQuery("delete from Offer where id=:id");
        query.setLong("id", id);
        return query.executeUpdate() == 1;

    }

    public Offer getOffer(int id) {

        Criteria crit = session().createCriteria(Offer.class);
        crit.createAlias("user", "u");

        crit.add(Restrictions.eq("u.enabled", true));
        crit.add(Restrictions.idEq(id));

        return (Offer) crit.uniqueResult();

    }

}

and UsersDao.java

package com.skam940.main.dao;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
@Component("usersDao")
public class UsersDao {

    @Autowired
    private PasswordEncoder passwordEncoder;

    @Autowired
    private SessionFactory sessionFactory;

    public Session session() {
        return sessionFactory.getCurrentSession();
    }

    @Transactional
    public void create(User user) {
        user.setPassword(passwordEncoder.encode(user.getPassword()));
        session().save(user);

    }

    public boolean exists(String username) {
        Criteria crit = session().createCriteria(User.class);
        crit.add(Restrictions.idEq(username));

        User user = (User) crit.uniqueResult();
        return user != null;

    }

    @SuppressWarnings("unchecked")
    public List<User> getAllUsers() {
        return session().createQuery("from User").list();
    }

}

no the thing is I get an exception of

HTTP Status 500 - PreparedStatementCallback; bad SQL grammar [select username, password, enabled from users where binary username = ?]; nested exception is org.h2.jdbc.JdbcSQLException: Column "BINARY" not found; SQL statement:

And the thing I want to do here to make the username case sensitive, and apparently H2 recognises BINARY as a table name but not as a type or what ever you call that, can any one tell which which method is implementing this SQL grammar?

User.java

 package com.skam940.main.dao;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;

import org.hibernate.validator.constraints.NotBlank;

import com.skam940.main.validation.ValidEmail;

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

    @NotBlank(groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    @Size(min=6, max=15, groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    @Pattern(regexp="^\\w{8,}$", groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    @Id
    @Column(name="username")
    private String username;

    @NotBlank(groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    @Pattern(regexp="^\\S+$", groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    @Size(min=8, max=15, groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    private String password;

    @ValidEmail(groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    private String email;

    @NotBlank(groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    @Size(min=3, max=30, groups={FormValidationGroup.class})
    private String name;

    private boolean enabled = false;
    private String authority;


    public User() {

    }

    public User(String username, String name, String password, String email, boolean enabled,
            String authority) {
        this.username = username;
        this.name = name;
        this.password = password;
        this.email = email;
        this.enabled = enabled;
        this.authority = authority;
    }

    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 boolean isEnabled() {
        return enabled;
    }

    public void setEnabled(boolean enabled) {
        this.enabled = enabled;
    }

    public String getAuthority() {
        return authority;
    }

    public void setAuthority(String authority) {
        this.authority = authority;
    }

    public String getEmail() {
        return email;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result
                + ((authority == null) ? 0 : authority.hashCode());
        result = prime * result + ((email == null) ? 0 : email.hashCode());
        result = prime * result + (enabled ? 1231 : 1237);
        result = prime * result + ((name == null) ? 0 : name.hashCode());
        result = prime * result
                + ((username == null) ? 0 : username.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        User other = (User) obj;
        if (authority == null) {
            if (other.authority != null)
                return false;
        } else if (!authority.equals(other.authority))
            return false;
        if (email == null) {
            if (other.email != null)
                return false;
        } else if (!email.equals(other.email))
            return false;
        if (enabled != other.enabled)
            return false;
        if (name == null) {
            if (other.name != null)
                return false;
        } else if (!name.equals(other.name))
            return false;
        if (username == null) {
            if (other.username != null)
                return false;
        } else if (!username.equals(other.username))
            return false;
        return true;
    }

    @Override
    public String toString() {
        return "User [username=" + username + ", email=" + email + ", name="
                + name + ", enabled=" + enabled + ", authority=" + authority
                + "]";
    }




}

Offer.java

package com.skam940.main.dao;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.validation.constraints.Size;

@Entity
@Table(name="offers")
public class Offer {

    @Id
    @GeneratedValue
    // because this is an auto increment value
    private int id;

    @Size(min=5, max=255, groups={PersistenceValidationGroup.class, FormValidationGroup.class})
    @Column(name="text")
    private String text;

    // every user can have only one offer
    @ManyToOne
    @JoinColumn(name="username")
    private User user;

    public Offer() {
        this.user = new User();
    }

    public Offer(User user, String text) {
        this.user = user;
        this.text = text;
    }

    public Offer(int id, User user, String text) {
        this.id = id;
        this.user = user;
        this.text = text;
    }

    public int getId() {
        return id;
    }

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

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public String getUsername() {
        return user.getUsername();
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((text == null) ? 0 : text.hashCode());
        result = prime * result + ((user == null) ? 0 : user.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Offer other = (Offer) obj;
        if (text == null) {
            if (other.text != null)
                return false;
        } else if (!text.equals(other.text))
            return false;
        if (user == null) {
            if (other.user != null)
                return false;
        } else if (!user.equals(other.user))
            return false;
        return true;
    }

    @Override
    public String toString() {
        return "Offer [id=" + id + ", text=" + text + ", user=" + user + "]";
    }





}

the full file content is available here -> https://app.box.com/s/c3uq71khbwf05p8asu27

Upvotes: 1

Views: 1071

Answers (1)

Magic Wand
Magic Wand

Reputation: 1760

This query is coming from your Spring security configuration, please check security-context.xml, you can find Spring security authentication provider that uses authorities-by-username-query as

select username, authority from users where binary username = ?

This query uses MySQL-specific function BINARY for case-sensitive comparison (http://gilfster.blogspot.com/2005/08/case-sensitivity-in-mysql.html). H2, on the other hand, is case sensitive by default.

Try to change it with

select username, authority from users where username = ?

The same applies to users-by-username-query property.

Upvotes: 1

Related Questions