开发者

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

开发者 https://www.devze.com 2023-02-02 21:15 出处:网络
I have DAO code which contains some JDBC with Oracle-specific syntax, for example: select count(*) cnt from DUAL

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

精彩评论

暂无评论...
验证码 换一张
取 消