CrudRepository not reading data from schema.sql

Question

Setup: I have a spring-boot application with a simple @Entity Customer object and CustomerRepository. I want to pre-load the database with test-data described here in my other question so I created schema.sql and data.sql files to load the database.

Problem: The CrudRepository seems to be using a different database than the one created with schema.sql and data.sql. I have not explicitly defined a datasource anywhere because I'm hoping that spring-boot can default everything for me (i.e., did not define spring.datasource in application.properties), and even if I do it doesn't do anything.

@Autowired
CustomerRepository r;
r.findAll(); // nothing but it should return the row "John Doe"

I don't get any errors it just returns nothing when I invoke findAll() on the repository.

schema.sql

drop table customer if exists;

create table customer (
    id bigint auto_increment,
    firstname varchar(80) null,
    lastname varchar(80) null
);

data.sql

insert into customer (firstname, lastname) values ('John', 'Doe');

Customer.java

package sample.jsp;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
public class Customer implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private long id;

    private String firstName;
    private String lastName;

    protected Customer() {}

    public Customer(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    @Override
    public String toString() {
        return String.format(
                "Customer[id=%d, firstName='%s', lastName='%s']",
                id, firstName, lastName);
    }

}

CustomerRepository.java

package sample.jsp;

import java.util.List;

import org.springframework.data.repository.CrudRepository;

public interface CustomerRepository extends CrudRepository<Customer, Long> {

    List<Customer> findByLastName(String lastName);
    List<Customer> findAll();
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.1.8.RELEASE</version>
    </parent>
    <artifactId>TestApp</artifactId>
    <packaging>war</packaging>
    <name>Spring Boot Web JSP Sample</name>
    <description>Spring Boot Web JSP Sample</description>
    <url>http://projects.spring.io/spring-boot/</url>
    <organization>
        <name>Pivotal Software, Inc.</name>
        <url>http://www.spring.io</url>
    </organization>
    <properties>
        <main.basedir>${basedir}/../..</main.basedir>
        <m2eclipse.wtp.contextRoot>/</m2eclipse.wtp.contextRoot>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>
       <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>           
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <configuration>
                    <useSystemClassLoader>false</useSystemClassLoader>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Answer 1

I had a similar problem and achieved solving it.

To making it work, I had to put this annotation for every test method I need test data:

@Sql("classpath:data.sql")

I'm using spring-boot-starter-data-jpa with H2. In my case, I run a test that depends on some initial data (created in data.sql).

When I ran the test, the schema was created correctly but, like in you case, the CrudRepository seems to use different data).

note: I understand it might be late to answering this question, but I hope this helps someone who has the same problem.

Answer 2

I had the same problem and manage to fix it.

the problem wasn't the SQL files location (the log displayed the scripts execution).

It was the default behavior of DDL generation of Springboot with In-memory DB like H2 and the fact that the SQL files are executed before the DDL generation ('create-drop' by default for in-memory DB).

add this line in application.properties :

spring.jpa.hibernate.ddl-auto=validate

and you will be good.

Answer 3

Change the name of data.sql to import.sql.

Spring Boot Database Initialization

Answer 4

This part of the Spring Boot docs describes how this work:

http://docs.spring.io/spring-boot/docs/1.2.2.RELEASE/reference/htmlsingle/#howto-intialize-a-database-using-spring-jdbc

In particular you have to make sure that both sql files are in the root of the classpath. Alternatively you could specify (even just to see if it works this way) the exact location/filename in your application.properties.

spring.datasource.schema=file:scripts/my-schema.sql