Поддержка SQLITE и внешнего ключа

Это старый вопрос, но недавно меня немного смутила та же проблема.

Есть как минимум 2 решения, которые я знаю об использовании Spring Boot (я предполагаю аналогичные решения для обычного Spring). Важным моментом с поддержкой внешнего ключа Sqlite является то, что «PRAGMA external_keys = ON» действителен * для каждого соединения *. (т. е. если у меня есть два открытых соединения с базой данных, и я устанавливаю поддержку внешнего ключа на «Вкл.» в своем первом соединении, первое соединение будет иметь поддержку внешнего ключа, а второе соединение — нет.

Решение 1
Из справочное руководство по Spring Boot:

Если вы используете «стартеры» spring-boot-starter-jdbc или spring-boot-starter-data-jpa, вы автоматически получаете зависимость от HikariCP.

Spring Boot автоматически создает bean-компонент DataSource, используя HikariCP в качестве драйвера по умолчанию. HikariCP сам делегирует полномочия соответствующему драйверу в зависимости от его конфигурации.

Spring Boot ожидает, что как минимум spring.datasource.url будет установлено в application.properties. Параметры конфигурации Hikari также можно установить в файле свойств в разделе spring.datasource.hikari.<config-key>, когда config-key является одним из ключи конфигурации Хикари. Зная это, используя Spring Boot, мы можем использовать следующие application.properties:

spring.datasource.url=jdbc:sqlite:path/to/db/database_file.db
spring.datasource.hikari.connectionInitSql=PRAGMA foreign_keys=1

И следующий DAO (я использую JdbcTemplate):

@Repository
public class MyDaoImpl implements MyDao {

    private final JdbcTemplate JDBC_TEMPLATE;

    @Autowired
    public SimpleArticleDao(DataSource dataSource) {
        this.JDBC_TEMPLATE = new JdbcTemplate(dataSource);
    }

    @Override
    public void insertObject(MyObject object) {
        JDBC_TEMPLATE.update(
            *...insert object into some table*
        )
    }
}

Spring Boot создаст и внедрит bean-компонент dataSource, используя Hikari в качестве источника данных, а Hikari будет выполнять PRAGMA foreign_keys = ON; для каждого создаваемого соединения, гарантируя, что поддержка внешнего ключа всегда включена.

Решение 2
Вы можете определить bean-компонент DataSource в своем классе @Configuration для Spring Boot и программно установить поддержку внешнего ключа, напрямую используя Драйвер Xerial JDBC:

@Configuration
public class MyApplicationConfig {

    @Bean
    public DataSource dataSource() {
        SQLiteDataSource ds = new SQLiteDataSource();
        ds.setUrl("jdbc:sqlite:path/to/db/database_file.db");
        SQLiteConfig config = ds.getConfig();
        config.enforceForeignKeys(true);
        ds.setConfig(config);
        return ds;
    }

    @Bean
    ...other bean definitions
}

Нашел ответ на одном из весенних форумов:

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name = "connectionInitSqls">
    <list><value>**PRAGMA foreign_keys = ON**</value></list>
    </property>
    ...
    </bean>

У меня была такая же проблема только сегодня, когда я использовал Spring Boot 2.2.2 с SQLite и простой JdbcTemplate.

Реализация решения 2 из ответа Патрика Тайлера нарушила все мои тесты. Я до сих пор не уверен, почему, потому что очень простой тест для проверки включения поддержки внешнего ключа прошел успешно.

В качестве альтернативы я попытался настроить spring.datasource.hikari.connection-init-sql, но это тоже не сработало.

Увы, что наконец сработало, так это добавить следующее к application.yml:

spring:
  datasource:
    hikari:
      data-source-properties:
        foreign_keys: true

Это можно проверить с помощью простого теста:

@SpringBootTest
class SqliteTests {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  @DisplayName("Check for foreign key support in the SQLite backend")
  @Test
  void testSqliteForEnabledForeignKeySupport() {
    final var result = jdbcTemplate.queryForObject("PRAGMA foreign_keys", Integer.class);
    assertEquals(1, result);
  }
}

Решение 3

При использовании Hikari (по умолчанию в spring boot 2+) мы можем просто настроить драйвер Xerial JDBC со следующей записью в application.properties:

spring.datasource.hikari.data-source-properties.foreign_keys=true