Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does Spring embedded database support different SQL dialects?

H2 has a range of compatibility modes for various other databases such as MS SQL Server, MySQL, Oracle, etc that support different SQL dialects. However, when setting up an embedded database in Spring I do not find any corresponding setting. Does this imply that I have to use "plain" SQL without any dialect specific features if I for example use Oracle in production and H2 during test? Have I overlooked something?

like image 529
matsev Avatar asked Feb 07 '12 06:02

matsev


People also ask

Does the Spring Framework support SQL databases?

The Spring Framework provides extensive support for working with SQL databases. From direct JDBC access using JdbcTemplate to complete ‘object relational mapping’ technologies such as Hibernate.

What is JDBC embedded database spring?

The org.springframework.jdbc.datasource.embedded package provides support for embedded Java database engines. Support for HSQL, H2, and Derby is provided natively. There is also an extensible API for plugging in new embedded database types and DataSource implementations. 12.8.1 Why use an embedded database?

How does Spring Boot determine the SQL dialect?

Spring Boot determines the SQL dialect to use for your datasource unless the spring.jooq.sql-dialect property has been configured. If the dialect couldn’t be detected, DEFAULT is used. Spring Boot can only auto-configure dialects supported by the open source version of jOOQ.

What are the benefits of using an embedded database in spring?

Ease of configuration, quick startup time, testability, and the ability to rapidly evolve SQL during development are just some of the benefits of using an embedded database. When you wish to expose an embedded database instance as a bean in a Spring ApplicationContext, use the embedded-database tag in the spring-jdbc namespace:


2 Answers

which version of H2 database? per the documentation, you can set compatible mode by SQL statement (http://www.h2database.com/html/features.html#compatibility)

SET MODE PostgreSQL

just add this statement into your first sql script file loaded by Spring jdbc embedded-database

like image 198
skong Avatar answered Sep 22 '22 11:09

skong


According to the H2 doc, the Oracle compatibility mode is quite limited.

For instance, you can not use PL/SQL procedures.

If you use Spring's EmbeddedDatabase, you cannot set the compatibility mode as-is; you have to implement you own EmbeddedDatabaseConfigurer and specify the compatibility mode through the JDBC URL (see below).

But also, to use the compatibility mode with H2 and Spring, you just have to set the mode in your JDBC URL (so it is not Spring related) in a classic way, using a DataSource:

jdbc:h2:~/test;MODE=Oracle

And if you use Hibernate, you have to specify the Oracle dialect instead of the H2 one.

like image 37
ndeverge Avatar answered Sep 19 '22 11:09

ndeverge