org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare SQL statement

Question

I work with small Spring boot app and I get the error triggered by the method findAll() from the controller,

@Controller
@RequestMapping(value = "/")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping(value = "/")
    public String index() {
        return "redirect:/users";
    }


    @GetMapping(value = "/users")
    public String showAllUsers(Model model) {

        model.addAttribute("users", userService.findAll());
        return "list";
    }
}

The important part of the error stack is,

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select user0_.id as id1_1_, user0_.address as address2_1_, user0_.confirm_password as confirm_3_1_, user0_.country as country4_1_, user0_.email as email5_1_, user0_.name as name6_1_, user0_.newsletter as newslett7_1_, user0_.number as number8_1_, user0_.password as password9_1_, user0_.sex as sex10_1_ from user user0_]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: USER0_.ADDRESS in statement [select user0_.id as id1_1_, user0_.address as address2_1_, user0_.confirm_password as confirm_3_1_, user0_.country as country4_1_, user0_.email as email5_1_, user0_.name as name6_1_, user0_.newsletter as newslett7_1_, user0_.number as number8_1_, user0_.password as password9_1_, user0_.sex as sex10_1_ from user user0_]

Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: USER0_.ADDRESS

The entity class is provided,

@Entity
public class User {

    // form:hidden - hidden value
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Integer id;

    // form:input - textbox
    @Column(name = "name", columnDefinition = "VARCHAR(30)", nullable = false)
    String name;

    // form:input - textbox
    @Column(name = "email", columnDefinition = "VARCHAR(50)", nullable = false)
    String email;

    // form:textarea - textarea
    @Column(name = "address", columnDefinition = "VARCHAR(255)", nullable = true)
    String address;

    // form:input - password
    @Column(name = "password", columnDefinition = "VARCHAR(20)", nullable = false)
    String password;

    // form:input - password
    String confirmPassword;

    // form:checkbox - single checkbox
    @Column(name = "newsletter", nullable = true)
    boolean newsletter;

    // form:checkboxes - multiple checkboxes
//    @Column(columnDefinition = "VARCHAR(500)", nullable = false)
    @ElementCollection
    List<String> framework;

    // form:radiobutton - radio button
    @Column(name = "sex", columnDefinition = "VARCHAR(1)", nullable = true)
    String sex;

    // form:radiobuttons - radio button
    @Column(name = "number", nullable = true)
    Integer number;

    // form:select - form:option - dropdown - single select
    @Column(name = "", columnDefinition = "VARCHAR(10)", nullable = true)
    String country;

    // form:select - multiple=true - dropdown - multiple select
//    @Column(columnDefinition = "VARCHAR(500)", nullable = true)
    @ElementCollection
    List<String> skill;

    //Check if this is for New of Update
    public boolean isNew() {
        return (this.id == null);
    }


    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

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

    public String getAddress() {
        return address;
    }

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

    public String getPassword() {
        return password;
    }

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

    public String getConfirmPassword() {
        return confirmPassword;
    }

    public void setConfirmPassword(String confirmPassword) {
        this.confirmPassword = confirmPassword;
    }

    public boolean isNewsletter() {
        return newsletter;
    }

    public void setNewsletter(boolean newsletter) {
        this.newsletter = newsletter;
    }

    public List<String> getFramework() {
        return framework;
    }

    public void setFramework(List<String> framework) {
        this.framework = framework;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getNumber() {
        return number;
    }

    public void setNumber(Integer number) {
        this.number = number;
    }

    public String getCountry() {
        return country;
    }

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

    public List<String> getSkill() {
        return skill;
    }

    public void setSkill(List<String> skill) {
        this.skill = skill;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof User)) return false;

        User user = (User) o;

        if (isNewsletter() != user.isNewsletter()) return false;
        if (!getId().equals(user.getId())) return false;
        if (!getName().equals(user.getName())) return false;
        if (!getEmail().equals(user.getEmail())) return false;
        if (getAddress() != null ? !getAddress().equals(user.getAddress()) : user.getAddress() != null) return false;
        if (!getPassword().equals(user.getPassword())) return false;
        if (getConfirmPassword() != null ? !getConfirmPassword().equals(user.getConfirmPassword()) : user.getConfirmPassword() != null)
            return false;
        if (!getFramework().equals(user.getFramework())) return false;
        if (getSex() != null ? !getSex().equals(user.getSex()) : user.getSex() != null) return false;
        if (getNumber() != null ? !getNumber().equals(user.getNumber()) : user.getNumber() != null) return false;
        if (getCountry() != null ? !getCountry().equals(user.getCountry()) : user.getCountry() != null) return false;
        return getSkill() != null ? getSkill().equals(user.getSkill()) : user.getSkill() == null;
    }

    @Override
    public int hashCode() {

        int result = getId().hashCode();

        result = 31 * result + getName().hashCode();
        result = 31 * result + getEmail().hashCode();
        result = 31 * result + (getAddress() != null ? getAddress().hashCode() : 0);
        result = 31 * result + getPassword().hashCode();
        result = 31 * result + (getConfirmPassword() != null ? getConfirmPassword().hashCode() : 0);
        result = 31 * result + (isNewsletter() ? 1 : 0);
        result = 31 * result + getFramework().hashCode();
        result = 31 * result + (getSex() != null ? getSex().hashCode() : 0);
        result = 31 * result + (getNumber() != null ? getNumber().hashCode() : 0);
        result = 31 * result + (getCountry() != null ? getCountry().hashCode() : 0);
        result = 31 * result + (getSkill() != null ? getSkill().hashCode() : 0);
        return result;
    }
}

The interfaces in the repositoy directory provided,

@NoRepositoryBean
public interface CrudRepository<T, ID extends Serializable>
        extends Repository<T, ID> {

    <S extends T> S save(S entity);

    T findOne(ID primaryKey);

    Iterable<T> findAll();

    Long count();

    void delete(T entity);
    void delete(ID idx);

    boolean exists(ID primaryKey);

    // … more functionality omitted.
}

and, the extension of the interface,

public interface UserRepository extends CrudRepository<User, Long>{

    User save(User user);

    @Query("SELECT t.name FROM User t where t.id = :id")
    String findNameById(@Param("id") Long id);

    @Query("UPDATE User SET NAME=:name, EMAIL=:email, ADDRESS=:address, PASSWORD=:password, NEWSLETTER=:newsletter, FRAMEWORK=:framework, SEX=:sex, NUMBER=:number, COUNTRY=:country, SKILL=:skill WHERE id=:id")
    User update (@Param("id") Long id);
}

The user controller class is provided earlier. If I turn off the address field, it shows error to the another field. I can provide more info if required. What is the issue here?

I commented out all but the Id and name, and still having the same type of errors,

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: USER0_.NAME in statement [select user0_.id as id1_1_, user0_.name as name2_1_ from user user0_]

....
....
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: USER0_.NAME

Here is 2 line smore of the error,

2017-10-03 12:50:52.537 ERROR 8116 --- [on(2)-127.0.0.1] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: create table user (id integer generated by default as identity (start with 1), name VARCHAR(30) not null, primary key (id))
2017-10-03 12:50:52.538 ERROR 8116 --- [on(2)-127.0.0.1] org.hibernate.tool.hbm2ddl.SchemaExport  : object name already exists: USER in statement [create table user (id integer generated by default as identity (start with 1), name VARCHAR(30) not null, primary key (id))]

I assume the issue is with the lack of privileges. I have simple config file that is empty now,

@Configuration
@EnableJpaRepositories(basePackages = {
        "com.boot.repository",
        "com.boot.entity"
})
@EnableTransactionManagement
class PersistenceContext {

}

How would I enable the priviledges for the user in the app?

Do other statements work? If not check permissions of the database and if the user has proper access rights. — Lyze, Oct 03 '17 at 06:39

score 1 · Accepted Answer · answered Oct 03 '17 at 07:05

1

Try to change your

public interface UserRepository extends CrudRepository<User, Long>{

    User save(User user);

    @Query("SELECT t.name FROM User t where t.id = :id")
    String findNameById(@Param("id") Long id);

    @Query("UPDATE User SET NAME=:name, EMAIL=:email, ADDRESS=:address, PASSWORD=:password, NEWSLETTER=:newsletter, FRAMEWORK=:framework, SEX=:sex, NUMBER=:number, COUNTRY=:country, SKILL=:skill WHERE id=:id")
    User update (@Param("id") Long id);
}

To

public interface UserRepository extends CrudRepository<User, Integer>{

        User save(User user);

        @Query("SELECT t.name FROM User t where t.id = :id")
        String findNameById(@Param("id") Integer id);

        @Query("UPDATE User SET NAME=:name, EMAIL=:email, ADDRESS=:address, PASSWORD=:password, NEWSLETTER=:newsletter, FRAMEWORK=:framework, SEX=:sex, NUMBER=:number, COUNTRY=:country, SKILL=:skill WHERE id=:id")
        User update (@Param("id") Integer id);
}

idk if this will solve your problem, but as i see, your userrepository set Long as Entity ID type, but your User Entitiy Id use Integer as it's type.

answered Oct 03 '17 at 07:05

ismailzakky

114
2
12

I have made the Id of the User entity as Long, it doesn't solve the issue. I still get the same errors. – Arefe Oct 03 '17 at 07:10
1

try to add this in your properties or yaml for " Unsuccessful: create table user " spring.jpa.hibernate.ddl-auto:create-drop – ismailzakky Oct 03 '17 at 07:21
I have this statement in the properties file, `spring.jpa.hibernate.ddl-auto=create` also change it to the `create-drop`. None of this helps me. – Arefe Oct 03 '17 at 07:23
sry, but it seem i cant help u, never work with HQL before, but i hope these 2 links may help you. https://stackoverflow.com/questions/38415734/hsql-database-user-lacks-privilege-or-object-not-found-error , and https://stackoverflow.com/questions/36505559/user-lacks-privilege-or-object-not-found – ismailzakky Oct 03 '17 at 07:29

score 0 · Answer 2 · edited Feb 10 '23 at 12:29

0

Add this to your application.yml file

spring:   
  jpa:
    show-sql: true
    generate-ddl: true
    hibernate:
      ddl-auto: create-drop

edited Feb 10 '23 at 12:29

sumek

26,495
13
56
75

answered Sep 10 '19 at 05:54

Shams

1
1

1

While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Artog Sep 10 '19 at 06:12

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare SQL statement

2 Answers2