Spring boot not able to run data.sql to initialize DB

Question

build.gradle

plugins {
    id 'org.springframework.boot' version '2.2.2.RELEASE'
    id 'io.spring.dependency-management' version '1.0.8.RELEASE'
    id 'java'
}

group = 'com.spdemo'
version = '1.0'
sourceCompatibility = '1.8'

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'com.h2database:h2'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude module: 'junit'
    }
    testImplementation 'org.junit.jupiter:junit-jupiter:5.4.0'
}

test {
    useJUnitPlatform()
}

schema.sql

drop table IF EXISTS officers;
create TABLE officers (
  id         INT         NOT NULL AUTO_INCREMENT,
  rank       VARCHAR(20) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

data.sql

INSERT INTO officers(rank, first_name, last_name) VALUES('CAPTAIN', 'James', 'Kirk');
INSERT INTO officers(rank, first_name, last_name) VALUES('CAPTAIN', 'Jean-Luc', 'Picard');
INSERT INTO officers(rank, first_name, last_name) VALUES('CAPTAIN', 'Benjamin', 'Sisko');
INSERT INTO officers(rank, first_name, last_name) VALUES('CAPTAIN', 'Kathryn', 'Janeway');
INSERT INTO officers(rank, first_name, last_name) VALUES('CAPTAIN', 'Jonathan', 'Archer');

JdbcOfficerDAOTest.javba

@SpringBootTest
@ExtendWith(SpringExtension.class)
// @Transactional // tx for each test rolls back by default
public class JdbcOfficerDAOTest {
    // @Qualifier("jdbcOfficerDAO")
    private Logger logger = LoggerFactory.getLogger(JdbcOfficerDAOTest.class);

    @Autowired
    private OfficerDAO dao;

    @Test
    public void save() {
        Officer officer = new Officer(Rank.LIEUTENANT, "Nyota", "Uhuru");
        officer = dao.save(officer);
        assertNotNull(officer.getId());
        // assertTrue(!(officer == null));
        logger.info("id is: " + officer.getId());
        logger.info("I'm here");
    }

    @Test
    public void findByIdThatExists() {
        Optional<Officer> officer = dao.findById(1);
        logger.info("here is the count: " + dao.count());
        assertTrue(officer.isPresent());
        // assertEquals(1, officer.get().getId().intValue());
    }
}

When I'm running the above test case, the first save() test always passes which means the table is created but the second always fails meaning data.sql is not executed. And the dao.count() always returns 0. How can I fix this?

Focus · Accepted Answer · 2019-12-13 21:26:42Z

4

spring.jpa.hibernate.ddl-auto=none

I added this line in the application.properties and it fixed the issue. The default setting blocks spring boot from running data.sql

 DDL mode. This is actually a shortcut for the "hibernate.hbm2ddl.auto" property.
 Defaults to "create-drop" when using an embedded database and no 
 schema manager was detected. Otherwise, defaults to "none".

none         Disable DDL handling.
validate     Validate the schema, make no changes to the database.
update       Update the schema if necessary.
create       Create the schema and destroy previous data.
create-drop  Create and then destroy the schema at the end of the session.

answered Dec 13, 2019 at 21:26

Focus

1,0233 gold badges12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohit Singh · Accepted Answer · 2019-12-13 21:02:25Z

0

You can specify the SQL files to run/execute by this property :

spring.datasource.data=classpath:sql_script1.sql,classpath:sql_script2.sql

answered Dec 13, 2019 at 21:02

Mohit Singh

5322 silver badges11 bronze badges

2 Comments

Focus Over a year ago

I tried that. Data is still not loaded when I run tests.

Mohit Singh Over a year ago

Are there any errors in the log? Can you once try putting all statements in one file?

Collectives™ on Stack Overflow

Spring boot not able to run data.sql to initialize DB

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related