在 Spring Boot 中创建表并从 .sql 文件导入数据 [英] Creating tables and importing data from .sql files in Spring boot

问题描述

目前的做法:

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/db_name
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto=update

spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.connection.zeroDateTimeBehavior=convertToNull
spring.datasource.initialization-mode=always
spring.jpa.properties.hibernate.hbm2ddl.import_files=<name>.sql 
spring.datasource.platform=mysql

不确定我遗漏了什么，为什么在这个配置中，.sql 文件没有被执行?

Not sure what am I missing, and why in this configuration, the .sql files are not executed?

推荐答案

UPDATE

我们可以使用

spring.datasource.schema= # Schema (DDL) script resource references.
spring.datasource.data= # Data (DML) script resource references.

• 无需更改 SQL 文件名
• 可以将模式生成和插入保存在同一个文件中

• 可以指定多个文件

  • No need to change the SQL filenames
  • Can Keep schema generation and insertion in the same file

  • Can specify multiple files

    spring.datasource.schema = classpath:/abc.sql,classpath:/abc2.sql

    注意:

    • 要在同一个文件中生成和插入模式不要使用spring.datasource.data，我们必须使用spring.datasource.schema
    • 将所有文件保存在 src/main/resources
    • 设置 spring.jpa.hibernate.ddl-auto=none
    • For schema generation and insertion in the same file do not use spring.datasource.data, we have to use spring.datasource.schema
    • Keep all files in src/main/resources
    • set spring.jpa.hibernate.ddl-auto=none

    初始答案

    Spring Boot 已经将 Hibernate 配置为基于您的实体创建架构.使用 SQL(在 src/main/resources 中)文件集来创建它

    Spring boot already configures Hibernate to create your schema based on your entities. To create it using SQL (in src/main/resources) files set

    spring.jpa.hibernate.ddl-auto=none
    

    <小时>

    在src/main/resources中创建schema.sql(创建表)和data.sql(插入记录)

    ---

    Create schema.sql (to create the table) and data.sql (to insert the records) in src/main/resources

    schema.sql

    CREATE TABLE country (
        id   INTEGER      NOT NULL AUTO_INCREMENT,
        name VARCHAR(128) NOT NULL,
        PRIMARY KEY (id)
    );
    

    data.sql

    INSERT INTO country (name) VALUES ('India');
    INSERT INTO country (name) VALUES ('Brazil');
    INSERT INTO country (name) VALUES ('USA');
    INSERT INTO country (name) VALUES ('Italy');
    

    application.properties

    spring.datasource.platform=mysql
    spring.datasource.initialization-mode=always
    spring.datasource.url=jdbc:mysql://localhost:3306/db_name?createDatabaseIfNotExist=true
    spring.datasource.username=root
    spring.datasource.password=
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    
    spring.jpa.show-sql = true
    spring.jpa.hibernate.ddl-auto=none
    spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
    spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
    

    这篇关于在 Spring Boot 中创建表并从 .sql 文件导入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！