Java HSQL 数据库用户缺少权限或找不到对象错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38415734/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HSQL database user lacks privilege or object not found error
提问by Raju Boddupalli
I am trying to use hsqldb-2.3.4to connect from Spring applicastion.
我正在尝试使用hsqldb-2.3.4从 Spring 应用程序连接。
I created data base using the following details
我使用以下详细信息创建了数据库
Type : HSQL Database Engine Standalone
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:file:mydb
UserName: SA
Password: SA
I created a table named ALBUMunder "MYDB" schema
我在“MYDB”模式下创建了一个名为ALBUM的表
In spring configuration file:
在spring配置文件中：
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dbcpDataSource" />
</bean>
<bean id="dbcpDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url" value="jdbc:hsqldb:file:mydb" />
<property name="username" value="SA" />
<property name="password" value="SA" />
</bean>
And in my spring controller I am doing jdbcTemplate.query("SELECT * FROM MYDB.ALBUM", new AlbumRowMapper());
在我的弹簧控制器中我正在做 jdbcTemplate.query("SELECT * FROM MYDB.ALBUM", new AlbumRowMapper());
And It gives me exception:
它给了我例外：
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM MYDB.ALBUM]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ALBUM
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
If I execute same query through SQL editor of hsqldb it executes fine. Can you please help me with this.
如果我通过 hsqldb 的 SQL 编辑器执行相同的查询，它执行得很好。你能帮我解决这个问题吗？
回答by Arthur Noseda
user lacks privilege or object not foundcan have multiple causes, the most obvious being you're accessing a table that does not exist. A less evident reason is that, when you run your code, the connection to a file database URL actually can create a DB. The scenario you're experiencing might be you've set up a DB using HSQL Database Manager, added tables and rows, but it's not this specific instance your Java code is using. You may want to check that you don't have multiple copies of these files: mydb.log, mydb.lck, mydb.properties, etc in your workspace. In the case your Java code did create those files, the location depends on how you run your program. In a Maven project run inside Netbeans for example, the files are stored alongside the pom.xml.
user lacks privilege or object not found可能有多种原因，最明显的是您正在访问一个不存在的表。一个不太明显的原因是，当您运行代码时，与文件数据库 URL 的连接实际上可以创建一个数据库。您遇到的情况可能是您已经使用 HSQL 数据库管理器设置了一个数据库，添加了表和行，但这不是您的 Java 代码使用的这个特定实例。您可能要检查你没有这些文件的多个副本：mydb.log，mydb.lck，mydb.properties，等在您的工作空间。如果您的 Java 代码确实创建了这些文件，则位置取决于您运行程序的方式。例如，在 Netbeans 内运行的 Maven 项目中，文件与 pom.xml 一起存储。
回答by Arigion
I had the error user lacks privilege or object not foundwhile trying to create a table in an empty in-memory database.
I used spring.datasource.schemaand the problem was that I missed a semicolon in my SQL file after the "CREATE TABLE"-Statement (which was followed by "CREATE INDEX").
user lacks privilege or object not found尝试在空的内存数据库中创建表时出现错误。我使用过spring.datasource.schema，问题是我的 SQL 文件中在“CREATE TABLE”-语句（后面跟着“CREATE INDEX”）之后遗漏了一个分号。
回答by Nerrve
If you've tried all the other answers on this question, then it is most likely that you are facing a case-sensitivity issue.
如果您已经尝试了有关此问题的所有其他答案，那么您很可能面临区分大小写的问题。
HSQLDB is case-sensitive by default. If you don't specify the double quotes around the name of a schema or column or table, then it will by default convert that to uppercase. If your object has been created in uppercase, then you are in luck. If it is in lowercase, then you will have to surround your object name with double quotes.
HSQLDB 默认区分大小写。如果您没有在架构或列或表的名称周围指定双引号，则默认情况下会将其转换为大写。如果您的对象是以大写形式创建的，那么您很幸运。如果它是小写的，那么您必须用双引号将对象名称括起来。
For example:
例如：
CREATE MEMORY TABLE "t1"("product_id" INTEGER NOT NULL)
To select from this table you will have to use the following query
要从此表中进行选择，您必须使用以下查询
select "product_id" from "t1"
回答by Nirmal Mangal
I had similar issue with the error 'org.hsqldb.HsqlException: user lacks privilege or object not found: DAYS_BETWEEN' turned out DAYS_BETWEENis not recognized by hsqldb as a function. use DATEDIFF instead.
我遇到了类似的问题，结果是错误 ' org.hsqldb.HsqlException: user lacks privilege or object not found: DAYS_BETWEEN'DAYS_BETWEEN未被 hsqldb 识别为函数。改用 DATEDIFF。
DATEDIFF ( <datetime value expr 1>, <datetime value expr 2> )
回答by Damien MIRAS
As said by a previous response there is many possible causes. One of them is that the table isn't created because of syntax incompatibility. If specific DB vendor syntax or specific capability is used, HSQLDB will not recognize it. Then while the creation code is executed you could see that the table is not created for this syntax reason. For exemple if the entity is annoted with @Column(columnDefinition = "TEXT")the creation of the table will fail.
正如之前的回复所说，有很多可能的原因。其中之一是由于语法不兼容而未创建表。如果使用特定的 DB 供应商语法或特定功能，HSQLDB 将无法识别它。然后在执行创建代码时，您可以看到由于此语法原因未创建表。例如，如果实体被注释@Column(columnDefinition = "TEXT")为表的创建将失败。
There is a work aroundwhich tell to HSQLDB to be in a compatible mode for pgsl you should append your connection url with that
有一种解决方法告诉 HSQLDB 处于 pgsl 的兼容模式，您应该将连接 url 附加到
"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_pgs=true"
and for mysqlwith
和mysql与
"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_mys=true"
oracle
甲骨文
"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_ora=true"
note there is variant depending on your configuration
it could be hibernate.connection.url=or spring.datasource.url=for those who don't use the hibernate schema creation but a SQL script you should use this kind of syntax in your script
请注意，根据您的配置，它可能会有所不同，hibernate.connection.url=或者spring.datasource.url=对于那些不使用休眠模式创建但使用 SQL 脚本的人，您应该在脚本中使用这种语法
SET DATABASE SQL SYNTAX ORA TRUE;
It will also fix issues due to vendor specific syntax in SQL request such as array_aggfor posgresql
它还将解决由于 SQL 请求中的供应商特定语法（例如posgresql的array_agg）引起的问题
Nota bene: The the problem occurs very early when the code parse the model to create the schema and then is hidden in many lines of logs, then the unitTested code crash with a confusing and obscure exception "user lacks privilege or object not found error" which does not point the real problem at all. So make sure to read all the trace from the beginning and fix all possible issues
注意：当代码解析模型以创建模式然后隐藏在多行日志中时，问题很早就出现了，然后 unitTested 代码崩溃并出现令人困惑和模糊的异常“用户缺少权限或找不到对象错误”这根本没有指出真正的问题。因此，请确保从头开始阅读所有跟踪并解决所有可能的问题
回答by LCM
When running a HSWLDB server. for example your java config file has:
运行 HSWLDB 服务器时。例如你的java配置文件有：
hsql.jdbc.url = jdbc:hsqldb:hsql://localhost:9005/YOURDB;sql.enforce_strict_size=true;hsqldb.tx=mvcc
hsql.jdbc.url = jdbc:hsqldb:hsql://localhost:9005/YOURDB;sql.enforce_strict_size=true;hsqldb.tx=mvcc
check to make sure that your set a server.dbname.#. for example my server.properties file:
检查以确保您设置了server.dbname.#. 例如我的 server.properties 文件：
server.database.0=eventsdb
server.dbname.0=eventsdb
server.port=9005
回答by cammando
I was inserting the data in hsql db using following script
我使用以下脚本在 hsql db 中插入数据
INSERT INTO Greeting (text) VALUES ("Hello World");
I was getting issue related to the Hello Worldobject not found and HSQL database user lacks privilege or object not found error
我遇到了与找不到Hello World对象和 HSQL 数据库用户缺少权限或找不到对象错误相关的问题
which I changed into the below script
我把它改成了下面的脚本
INSERT INTO Greeting (text) VALUES ('Hello World');
And now it is working fine.
现在它工作正常。
回答by batristio
I bumped into kind of the same problem recently. We are running a grails application and someone inserted a SQL script into the BootStrap file (that's the entry point for grails). That script was supposed to be run only in the production environment, however because of bad logic it was running in test as well. So the error I got was:
我最近遇到了同样的问题。我们正在运行一个 grails 应用程序，有人在 BootStrap 文件中插入了一个 SQL 脚本（这是 grails 的入口点）。该脚本应该只在生产环境中运行，但是由于逻辑错误，它也在测试中运行。所以我得到的错误是：
User lacks privilege or object not found:
without any more clarification...
没有更多的澄清......
I just had to make sure the script was not run in the test environment and it fixed the problem for me, though it took me 3 hours to figure it out. I know it is very, very specific issue but still if I can save someone a couple of hours of code digging that would be great.
我只需要确保脚本没有在测试环境中运行，它就为我解决了问题，尽管我花了 3 个小时才弄明白。我知道这是一个非常非常具体的问题，但如果我可以为某人节省几个小时的代码挖掘时间，那就太好了。
回答by Eduardo Mior
I was having the same mistake. In my case I was forgetting to put the apas in the strings.
我犯了同样的错误。就我而言，我忘记将 apa 放在字符串中。
I was doing String test = "inputTest";
我在做 String test = "inputTest";
The correct one is String test = "'inputTest'";
正确的是 String test = "'inputTest'";
The error was occurring when I was trying to put something in the database
当我尝试将某些内容放入数据库时发生错误
connection.createStatement.execute("INSERT INTO tableTest values(" + test +")";
In my case, just put the quotation marks 'to correct the error.
就我而言，只需加上引号'即可纠正错误。
回答by R.S
In my case the error occured because i did NOT put the TABLE_NAME into double quotes "TABLE_NAME" and had the oracle schema prefix.
在我的情况下，发生错误是因为我没有将 TABLE_NAME 放入双引号“TABLE_NAME”并且具有 oracle 模式前缀。
Not working:
不工作：
SELECT * FROM FOOSCHEMA.BAR_TABLE
Working:
在职的：
SELECT * FROM "BAR_TABLE"
