JUnit/HSQLDB: How to get around errors with Oracle syntax when testing using HSQLDB (no privilege and/or no DUAL object)

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 enabl开发者_如何转开发e 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

---

The HSQL "Oracle style syntax" can also be enabled via a SQL command

SET DATABASE SQL SYNTAX ORA TRUE

See 12.30 . It's an alternative to the property sql.syntax_ora=true as 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.

---

Create a table called DUAL with one column, "DUMMY", in the HSQLDB database. Insert one row, value 'X'.

---

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.

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