JUnit / HSQLDB：如何在使用HSQLDB进行测试时使用Oracle语法解决错误（无特权和/或无DUAL对象） [英] JUnit/HSQLDB: How to get around errors with Oracle syntax when testing using HSQLDB (no privilege and/or no DUAL object)

问题描述

我有DAO代码，其中包含一些使用Oracle特定语法的JDBC，例如：

  select count（*）cnt从DUAL 
 where exists（从+ TABLE_NAME +中选择null 
，其中LOCATION ='+ location +'））
  

我使用内存中的HSQLDB数据库在这个DAO方法上运行JUnit测试，显然DUAL表是Oracle特定的，当我运行测试时会导致错误：

  org.springframework.jdbc.BadSqlGrammarException：StatementCallback; 
坏SQL语法[select count（*）cnt from DUAL where exists 
（从ESRL_OBSERVATIONS中选择null，其中LOCATION ='/ path1'）]; 
嵌套异常是java.sql.SQLException：用户缺少特权或对象
未找到：DUAL 
  

任何人都可以提出任何可以解决此问题的方法吗？我正在使用Hibernate创建模式 - 可能有一个设置一世 可以使我的Hibernate属性支持Oracle风格的语法吗？如果你使用Hibernate 3.6和HSQLDB 2.0.1的话或更高版本，您可以在连接URL上使用连接属性 sql.syntax_ora = true 。这使得DUAL表与其他一些Oracle特定的语法一起使用。



您可能需要更多的连接属性来处理主属性未涉及的行为。请参阅：
http://hsqldb.org/doc/2.0 /guide/management-chapt.html#mtc_compatibility_oracle

I have DAO code which contains some JDBC with Oracle-specific syntax, for example:

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:

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?

解决方案

If you use Hibernate 3.6 with HSQLDB 2.0.1 or later, you can use a connection property sql.syntax_ora=true on your connection URL. This enables the DUAL table together with some other Oracle specific syntax.

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

这篇关于JUnit / HSQLDB：如何在使用HSQLDB进行测试时使用Oracle语法解决错误（无特权和/或无DUAL对象）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！