JUnit/HSQLDB：在使用 HSQLDB（无特权和/或无 DUAL 对象）进行测试时如何解决 Oracle 语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4628857/
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
JUnit/HSQLDB: How to get around errors with Oracle syntax when testing using HSQLDB (no privilege and/or no DUAL object)
提问by James Adams
I have DAO code which contains some JDBC with Oracle-specific syntax, for example:
我有 DAO 代码，其中包含一些具有 Oracle 特定语法的 JDBC，例如：
select count(*) cnt from DUAL
where exists (select null from " + TABLE_NAME + "
where LOCATION = '" + location + "')")
I am running JUnit tests on this DAO method using an in-memory HSQLDB database. Apparently the DUAL table is Oracle specific and causes an error when I run the test:
我正在使用内存中的 HSQLDB 数据库对此 DAO 方法运行 JUnit 测试。显然，DUAL 表是特定于 Oracle 的，并且在我运行测试时会导致错误：
org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
bad SQL grammar [select count(*) cnt from DUAL where exists
(select null from ESRL_OBSERVATIONS where LOCATION = '/path1')];
nested exception is java.sql.SQLException: user lacks privilege or object
not found: DUAL
Can anyone suggest anything I can do to get around this issue? I am using Hibernate to create the schema -- perhaps there's a setting I can make in my Hibernate properties which will enable support for Oracle style syntax?
任何人都可以建议我可以做些什么来解决这个问题？我正在使用 Hibernate 创建模式——也许我可以在我的 Hibernate 属性中进行一个设置来支持 Oracle 样式语法？
回答by fredt
If you use Hibernate 3.6 with HSQLDB 2.0.1 or later, you can use a connection property sql.syntax_ora=trueon your connection URL. This enables the DUAL table together with some other Oracle specific syntax.
如果将 Hibernate 3.6 与 HSQLDB 2.0.1 或更高版本一起使用，则可以sql.syntax_ora=true在连接 URL 上使用连接属性。这将启用 DUAL 表以及其他一些特定于 Oracle 的语法。
You probably need a few more connection properties for behaviour that is not covered by the main property. See: http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_compatibility_oracle
对于主要属性未涵盖的行为，您可能需要更多的连接属性。请参阅：http: //hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_compatibility_oracle
回答by Baldrick
The HSQL "Oracle style syntax" can also be enabled via a SQL command
HSQL“Oracle 风格语法”也可以通过 SQL 命令启用
SET DATABASE SQL SYNTAX ORA TRUE
See 12.30. It's an alternative to the property sql.syntax_ora=trueas suggested in fredt's answer. It may be more practical in some cases : the flag can be set via JDBC after the HSQL database has been started.
见12.30。sql.syntax_ora=true正如 fredt 的回答中所建议的那样，它是该财产的替代品。在某些情况下可能更实用：可以在 HSQL 数据库启动后通过 JDBC 设置标志。
回答by redcayuga
Create a table called DUAL with one column, "DUMMY", in the HSQLDB database. Insert one row, value 'X'.
在 HSQLDB 数据库中创建一个名为 DUAL 的表，其中包含一列“DUMMY”。插入一行，值为“X”。
回答by Debroop Poddar
This is an old thread but it is possible to use the custom url to set oracle dialect. I created a custom data-source factory with updated url and injected the same while initializing the DB.
这是一个旧线程，但可以使用自定义 url 来设置 oracle 方言。我创建了一个带有更新 url 的自定义数据源工厂，并在初始化数据库时注入了相同的内容。
//Custom class
public class CusstomSimpleDriverDataSourceFactory implements DataSourceFactory {
private final SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
@Override
public ConnectionProperties getConnectionProperties() {
return new ConnectionProperties() {
@Override
public void setDriverClass(Class<? extends Driver> driverClass) {
dataSource.setDriverClass(driverClass);
}
@Override
public void setUrl(String url) {
System.out.println("Existing url: " + url);
String newUrl = url+";sql.syntax_ora=true";
System.out.println("New url: " + newUrl);
dataSource.setUrl(newUrl);
}
@Override
public void setUsername(String username) {
dataSource.setUsername(username);
}
@Override
public void setPassword(String password) {
dataSource.setPassword(password);
}
};
}
@Override
public DataSource getDataSource() {
return this.dataSource;
}
}
Then in the configuration you can use
public DataSource dataSource() {
return new EmbeddedDatabaseBuilder()
.setType(EmbeddedDatabaseType.HSQL)
.setDataSourceFactory(new CusstomSimpleDriverDataSourceFactory())
.addScript("ddl_script")
.addScript("dml_script")
.build();
}
