javasql-serveruser-defined-functionsdbunit

DbUnit - JdbcSQLException: Function "*" not found


I have a user-defined function in MS SQL Server called from Java code that appears to be undefined when running integration tests in H2 database. You can find my code in the previous question.

Test code:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {H2Config.class})
@TestExecutionListeners({
        DependencyInjectionTestExecutionListener.class,
        DbUnitTestExecutionListener.class,
        TransactionalTestExecutionListener.class
})
@TransactionConfiguration(defaultRollback = true)
public class TableDaoTest {

    @Autowired
    private TableDao tableDao;

    @Test
    @DatabaseSetup("/datasets/import.xml")
    public void testMethod01() {
        tableDao.getRecordsByGroup();
        ...

Database schema is autogenerated by Hibernate. As you can see data for the test is populated by DbUnit using xml dataset. And this test fails because my function that exists in MS SQL server DB is undefined in H2 database.

Application log:

Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement
    ...
Caused by: org.h2.jdbc.JdbcSQLException: Function "SAFE_MOD" not found; SQL statement:
    select table10_.id, table10_.value, ... from Table1 table10_ where table10_.group1=dbo.safe_mod(?, ?);
    ...

How to import / create a function before DbUnit test?


Solution

  • H2 database doesn't support user-defined SQL functions. However, in this database, Java functions can be used as stored procedures as well.

    @SuppressWarnings("unused")
    public class H2Function {
        public static int safeMod(Integer n, Integer divider) {
            if (divider == null) {
                divider = 5000;
            }
    
            return n % divider;
        }
    
    }
    

    Note, that only static Java methods are supported; both the class and the method must be public.

    The Java function must be declared (registered in the database) by calling CREATE ALIAS ... FOR before it can be used:

    CREATE ALIAS IF NOT EXISTS safe_mod DETERMINISTIC FOR "by.naxa.H2Function.safeMod";
    

    This statement should be executed before any test so I decided to put it inside connection init SQL:

    @Bean
    public DataSource dataSource() {
        BasicDataSource dataSource = new BasicDataSource();
    
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:mem:my_db_name");
        dataSource.setUsername("sa");
        dataSource.setPassword("");   
        dataSource.setConnectionInitSqls(Collections.singleton(
            "CREATE ALIAS IF NOT EXISTS safe_mod DETERMINISTIC FOR \"by.naxa.H2Function.safeMod\";"));
    
        return dataSource;
    }