Java 内部 HSQL 数据库抱怨权限

Internal HSQL database complains about privileges

提问by Bart van Heukelom

I'm setting up a standalone Java service with an in-process, in-memory HSQL database.

我正在使用进程内、内存中的 HSQL 数据库设置独立的 Java 服务。

Persistence.xml

持久化文件

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
 version="2.0">

 <persistence-unit name="manager">

 <class>tr.silvercar.data.entities.User</class>
 <properties>
 <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver" />
 <property name="javax.persistence.jdbc.user" value="sa" />
 <property name="javax.persistence.jdbc.password" value="" />
 <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:testdb" />

 <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />
 <property name="hibernate.max_fetch_depth" value="3" />

 <!-- cache configuration -->
<!-- 
 <property name="hibernate.ejb.classcache.org.hibernate.ejb.test.Item"
  value="read-write" />
 <property
  name="hibernate.ejb.collectioncache.org.hibernate.ejb.test.Item.distributors"
  value="read-write, RegionName" />
 -->
 </properties>

 </persistence-unit>

</persistence>

Code

代码

  emf = Persistence.createEntityManagerFactory("manager");

  User newUser = new User();
  newUser.setName("Testgebruiker");
  newUser.setCredits(100);

  System.out.println("Inserting user");
  EntityManager em = emf.createEntityManager();
  em.persist(newUser);
  em.close();

  System.out.println("Getting user");
  em = emf.createEntityManager();
  User u = (User) em.createQuery("SELECT u FROM User u").getSingleResult();
  em.close();
  System.out.println(u);

It would seem to me that since the database is in memory, and Hibernate should generate tables, that I don't need to do anything else. However, upon calling getSingleResultI get the exception:

在我看来，由于数据库在内存中，并且 Hibernate 应该生成表，所以我不需要做任何其他事情。但是，在调用时，getSingleResult我得到了异常：

org.hsqldb.HsqlException: user lacks privilege or object not found: USER

采纳答案by fredt

You need to use Hibernate 3.5.6 or later, together with HSQLDB version 2.2.x or later. Otherwise, older Hibernte jars work with HSQLDB 1.8.x. The name of the table is not a problem. I have developed the dialect and run the Hibernate tests for this version, but Pascal knows a lot more about Hibernate usage than I do and has helped a lot of people here.

您需要使用 Hibernate 3.5.6 或更高版本，以及 HSQLDB 2.2.x 或更高版本。否则，旧的 Hibernte jar 可以与 HSQLDB 1.8.x 一起使用。表名不是问题。我已经为此版本开发了方言并运行了 Hibernate 测试，但是 Pascal 比我更了解 Hibernate 的使用，并且在这里帮助了很多人。

回答by upog

I too have faced the same error. It got resolved when i gave the absolute path of script file in "connection.url".

我也遇到过同样的错误。当我在“connection.url”中给出脚本文件的绝对路径时，它得到了解决。

< property name="connection.url">jdbc:hsqldb:file:C:\Hibernate-example\database\mydb;shutdown=true < /property>

<property name="connection.url">jdbc:hsqldb:file:C:\Hibernate-example\database\mydb;shutdown=true </property>

回答by Bipin Kesapur

As per the above post, I was using Hibernate 3.5.6 together with HSQLDB 2.0.1 but it was still throwing the error. I also corrected the url but of no help. Finally changed HQSL jar to 1.8.1 version from http://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_1_8_1/hsqldb_1_8_1_3.zip/downloadand it worked

根据上面的帖子，我将 Hibernate 3.5.6 与 HSQLDB 2.0.1 一起使用，但它仍然抛出错误。我也更正了网址，但没有帮助。最后将 HQSL jar 从http://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_1_8_1/hsqldb_1_8_1_3.zip/download更改为 1.8.1 版本并且它工作了

回答by Vivek

When I had this error, I realised that I was using the wrong provider class in persistence.xml

当我遇到这个错误时，我意识到我使用了错误的提供程序类 persistence.xml

For Hibernate it should be

对于 Hibernate，它应该是

<provider>org.hibernate.ejb.HibernatePersistence</provider>

And for EclipseLink it should be

对于 EclipseLink，它应该是

<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

You should also note that different names are used in persistence.xmland when creating Persistence.createEntityManagerFactory("name")

您还应该注意，persistence.xml在创建时使用了不同的名称Persistence.createEntityManagerFactory("name")

回答by Maze

I run into the same issue and none of solutions provided (really) helped me (and there are quite a few more posts here on Stackoverflow which are closely related), but I finally figured it out. Therefore I thought I share my findings (sorry for the slightly lengthy post):

我遇到了同样的问题，并且提供的任何解决方案（真的）都没有帮助我（并且在 Stackoverflow 上还有很多与此密切相关的帖子），但我终于弄明白了。因此，我想我要分享我的发现（抱歉这篇文章有点长）：

In my case I converted some existing UnitTests using a MySQL database to HSQLDB so that the external dependency can be removed. This all looks kind of easy if you look at descriptions like: http://eskatos.wordpress.com/2007/10/15/unit-test-jpa-entities-with-in-memory-database/But it turned out to be a bit more tricky.

就我而言，我使用 MySQL 数据库将一些现有的 UnitTests 转换为 HSQLDB，以便可以删除外部依赖项。如果您查看以下描述，这一切看起来都很简单：http: //eskatos.wordpress.com/2007/10/15/unit-test-jpa-entities-with-in-memory-database/但结果证明有点棘手。

I experimented with

我尝试过

1. different versions (as suggested above),
2. the createand ifexistsparameters (see: http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html),
3. specifying different user credentials (username="sa", password="" is correct),
4. specifying update, create and create-drop as hibernate.hbm2ddl.auto (see http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html),
5. using different types of DataSources: c3p0, dbcp, ...
6. ...

1. 不同的版本（如上所述），
2. 的create和ifexists参数（见：http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html），
3. 指定不同的用户凭据（username="sa", password="" 是正确的），
4. 将更新、创建和创建删除指定为 hibernate.hbm2ddl.auto（参见http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html），
5. 使用不同类型的数据源：c3p0、dbcp、...
6. ...

But none of those really made any difference (the errors differed btw.). Apart from the user lacks privilege or object not founderror message, the most informative one I could get was the following: SQL Error: -20, SQLState: IM001(which translates to "Driver does not support this function") And even more explicitly I found this in the logs: [2012-09-24 14:50:45,796] ERROR main::org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(144) | This function is not supported

但是这些都没有真正产生任何区别（顺便说一句，错误有所不同）。除了user lacks privilege or object not found错误消息之外，我能得到的最有用的信息如下：（ SQL Error: -20, SQLState: IM001这意味着“驱动程序不支持此功能”）而且我在日志中更明确地发现了这一点： [2012-09-24 14:50:45,796] ERROR main::org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(144) | This function is not supported

So something clearly was broken. It turns out that biggest issue turns out to be a combination of two things: 1. Me not properly looking through the log output, since it actually contained the clue 2. An erroneous annotation

所以显然有什么东西坏了。事实证明，最大的问题是两件事的结合：1. 我没有正确查看日志输出，因为它实际上包含了线索 2. 错误的注释

Regarding 1:

关于1：

The issue was that the output contains a huge amount of lines that looked like the following (which can apparently be ignored, and there is even a ticket for H2):

问题是输出包含大量如下所示的行（显然可以忽略，甚至还有一张 H2的票）：

[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(426) | HHH000389: Unsuccessful: alter table SimpleSubscription drop constraint FKAD76A00F168752B2
[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(427) | user lacks privilege or object not found: PUBLIC.SIMPLESUBSCRIPTION
Hibernate: alter table SimpleSubscriptionChange drop constraint FKB3B8189FFC3506ED
[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(426) | HHH000389: Unsuccessful: alter table SimpleSubscriptionChange drop constraint FKB3B8189FFC3506ED
[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(427) | user lacks privilege or object not found: PUBLIC.SIMPLESUBSCRIPTIONCHANGE
Hibernate: alter table SimpleSubscriptionChange_languages drop constraint FK5A45F07BFC21A8E6

Regarding 2:

关于2：

Between all those lines were hidden the following lines, which actually gives away what the error is:

在所有这些行之间隐藏了以下几行，这实际上揭示了错误是什么：

[2012-09-25 10:07:13,468] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(426) | HHH000389: Unsuccessful: create table Rule (id bigint generated by default as identity (start with 1), creationTime timestamp not null, deleted BIT not null, lastUpdateTime timestamp, version integer not null, fetcher varchar(255), hash integer not null, primary key (id), unique (id))
[2012-09-25 10:07:13,468] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(427) | a UNIQUE constraint already exists on the set of columns in statement [create table Rule (id bigint generated by default as identity (start with 1), creationTime timestamp not null, deleted BIT not null, lastUpdateTime timestamp, version integer not null, fetcher varchar(255), hash integer not null, primary key (id), unique (id))]

So the issue is that the BaseEntity that is defined has an erroneous annotation for the id:

所以问题是定义的 BaseEntity 对 id 有错误的注释：

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(nullable = false, unique = true)
private Long id;

The field is already identified as an ID (i.e. as a primary key), and therefore can not have a unique annotation (and also the nullableis kind of superfluous). Changing this to:

该字段已经被标识为一个 ID（即作为主键），因此不能有一个唯一的注释（也是nullable一种多余的）。将此更改为：

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private Long id;

and everything works fine :-)

一切正常:-)

回答by Tarun

Try explicitly specifying the SCHEMA name for the table/sequence being referenced.

尝试为所引用的表/序列显式指定 SCHEMA 名称。

I just faced a similar issue where in my sequence was not being recognized and then I realized that in my Hibernate @SequenceGenerator annotation I didn't prefix the schema to the sequence ref.

我刚刚遇到了一个类似的问题，我的序列没有被识别，然后我意识到在我的 Hibernate @SequenceGenerator 注释中，我没有将模式添加到序列引用中。

call next value for EXAMPLE_SCHEMA.TEST_SEQ

回答by Stig Runar Vangen

I ran into a similar problem, but in my case the problem occurred because of column definitions. I used MySQL definitions in this way:

我遇到了类似的问题，但在我的情况下，问题是由于列定义而发生的。我以这种方式使用 MySQL 定义：

@Id
@GeneratedValue
@Column(columnDefinition = "INT(11)")
private long id;

This seems to not be supported by HSQL, and I changed the definition to this:

HSQL好像不支持这个，我把定义改成这样：

@Id
@GeneratedValue
@Column(columnDefinition = "INTEGER")
private long id;

And then the tests worked again.

然后测试再次起作用。

回答by Alexander Pacha

HSQL does not use the longtextbut the LONGVARCHARtype. Changing it solved a similar issue for me, where it complained about required privileges.

HSQL 不使用longtext但LONGVARCHAR类型。改变它为我解决了一个类似的问题，它抱怨所需的特权。