CODEWITHSUNDEEP
springunit-testingspring-booth2spring-test
Mahozad
Mahozad

Reputation: 24532

How to use MySQL-specific functions in H2 database?

How can I persist an entity that contains a @Formula field in it to H2 database?

I encounter the following exception:

...
Caused by: org.h2.jdbc.JdbcSQLException: Function "UNIX_TIMESTAMP" not found;
...

Post class:

@Entity
public class Post {
    // ...

    @Formula("UNIX_TIMESTAMP(creation_date_time) / 24 * 60 * 60 * 1000 + likes_count")
    private long score;

    // ...
}

PostRepositoryTest class:

@ExtendWith(SpringExtension.class)
@DataJpaTest
class PostRepositoryTest {

    @Autowired
    private TestEntityManager entityManager;

    @Autowired
    private PostRepository postRepository;

    @Test
    void savePost() {
        entityManager.persist(new Post());

        List<Post> posts = postRepository.findAll();

        assertEquals(1, posts.size());
    }
}

Upvotes: 2

Views: 2366

Answers (1)

Stephen Ni
Stephen Ni

Reputation: 61

According to https://h2database.com/html/features.html#user_defined_functions, you could define such a function in src/test/resources/import.sql:

CREATE ALIAS IF NOT EXISTS UNIX_TIMESTAMP FOR "acme.H2Function.unixTimestamp";

and define the this class:

package acme;

public class H2Function {

  public static long unixTimestamp(java.sql.Timestamp timestamp) {
    return timestamp.getTime() / 1000L;
  }
}

Spring JPA will auto call import.sql when you start you unit test.

NOTE: Must naming import.sql, not schema.sql, not data.sql

Upvotes: 2

Related Questions