CODEWITHSUNDEEP
javaspringspring-bootspring-data-jpah2
Focus
Focus

Reputation: 1013

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

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?

Upvotes: 0

Views: 4478

Answers (2)

Focus
Focus

Reputation: 1013

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.

Upvotes: 4

Mohit Singh
Mohit Singh

Reputation: 534

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

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

Upvotes: 0

Related Questions