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

Question

我使用小型 Spring boot 应用程序，并收到由 Controller 中的方法 findAll() 触发的错误，

@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";
    }
}

错误堆栈的重要部分是，

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

提供了实体类，

@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;
    }
}

提供的repositoy目录下的接口(interface)，

@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.
}

以及界面的扩展，

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);
}

用户 Controller 类已在前面提供。如果我关闭 address 字段，它会向另一个字段显示错误。如果需要，我可以提供更多信息。这里有什么问题吗？

我注释掉了除 ID 和名称之外的所有内容，但仍然出现相同类型的错误，

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

这里有 2 行更多的错误，

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))]

我认为问题在于缺乏特权。我有一个简单的配置文件，现在是空的，

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

}

如何在应用程序中为用户启用权限？

Answer 1

尝试改变自己

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);
}

至

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);
}

不知道这是否能解决您的问题，但正如我所见，您的用户存储库将 Long 设置为实体 ID 类型，但您的用户实体 Id 使用 Integer 作为其类型。