JpaRepository findAll() returns empty result

Question

JpaRepository findAll() method returns empty result. I am trying to implement rest service by using Spring-boot, h2 database and jpa.

Here is my schema.sql

CREATE TABLE IF NOT EXISTS `City` (
  `city_id` bigint(20) NOT NULL auto_increment,
  `city_name` varchar(200) NOT NULL,
PRIMARY KEY (`city_id`));

My data.sql file includes :

INSERT INTO City (city_id,city_name) VALUES(1,'EDE');
INSERT INTO City (city_id,city_name) VALUES(2,'DRUTEN');
INSERT INTO City (city_id,city_name) VALUES(3,'DELFT');

The City entity :

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

@Entity
@Table(name = "City")
public class City {

  @Id
  @GeneratedValue
  @Column(name = "city_id")
  private Long cityId;

  @Column(name = "city_name")
  private String cityName;

  public Long getCityId() {
    return cityId;
  }

  public void setCityId(Long cityId) {
    this.cityId = cityId;
  }

  public String getCityName() {
    return cityName;
  }

  public void setCityName(String cityName) {
    this.cityName = cityName;
  }

}

The JpaRepository interface:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface CityRepository extends JpaRepository<City, Long> {

    @Override
    List<City> findAll();
}

And here is my Contoller class

@RestController
@RequestMapping("/city")
public class CityController {
    @Autowired
    private CityRepository cityRepository;

    @RequestMapping(method = RequestMethod.GET, value = "/all")
    public List<City> getAllCityList(){
        return cityRepository.findAll();
    }
}

What am I doing wrong here? The reference documentation : Spring doc

Remove your overriden `findAll` there is already one in the `JpaRepository` no need to add it again and disable schema creation by hibernate, as that basically overrides what is done by the `schema.sql` and `data.sql`. — M. Deinum, Mar 31 '17 at 08:25
@M.Deinum I removed `schema.sql` and `data.sql` and removed overridden `findAll` method but it does not work — fiskra, Mar 31 '17 at 08:31
If you remove those files you will have nothing inserted obviously... — M. Deinum, Mar 31 '17 at 08:48
@M.Deinum I added ` spring.jpa.hibernate.ddl-auto=update ` in ` application.properties ` file and put ` schema.sql ` and ` data.sql ` files in resources again. Then it worked! Thanks a lot. it saved my day : ) — fiskra, Mar 31 '17 at 09:02

score 7 · Answer 1 · answered Mar 31 '17 at 09:22

You have a schema.sql and data.sql which are both executed after the DataSource has been configured and is ready. Next the EntityManagerFactory is created and by default (See the reference guide) this will create-drop the database for embedded types (like H2).

You can override this behavior by changing the spring.jpa.hibernate.ddl-auto property to anything else then create or create-drop.

Another solution is to rename your data.sql to import.sql which will be executed after Hibernate created the schema for you. You can now obviously also remove the schema.sql as Hibernate will create the schema.

If this is for learning purposes you should be fine, if you want to use this in a live production system I suggest instead of using this to use something like Flyway to manage your schema.

score 3 · Answer 2 · edited May 23 '17 at 12:25

3

As far as i understood, you want to execute sql scripts on application startup and after that use Hibernate? Well, you have to use one of the options mentioned here, and set spring.jpa.hibernate.ddl-auto=none.The explanation is given there.

Good luck

edited May 23 '17 at 12:25

Community

1
1

answered Mar 31 '17 at 09:33

Malakai

3,011
9
35
49

score 0 · Answer 3 · answered Oct 11 '19 at 12:37

0

In my case I had the deleted column as null and had @Where(clause = "deleted='false'") on the JPA Entity

answered Oct 11 '19 at 12:37

Samuel Gwokuda

69
2
6

Harry Coder · Answer 4 · 2021-11-10T06:31:52.070

This is a sample code that works for me :

@DataJpaTest
@TestPropertySource(properties = {
        "spring.datasource.schema=classpath:/sql/schema.sql",
        "spring.datasource.data=classpath:/sql/data.sql",
        "spring.jpa.hibernate.ddl-auto=none"
})
class CalculatorRepositoryTest implements WithAssertions {

    @Autowired
    private CalculatorRepository repository;

    @Test
    void testAutoConfiguration() {
        assertThat(repository).isNotNull();
    }

    @Test
    void testInsertResult() {

        Result newResult = new Result();
        newResult.setOperation(OperationType.DIVISION.name());
        newResult.setValue(10);

        Result insertedResult = repository.save(newResult);

        assertAll(
                () -> assertThat(insertedResult).isNotNull(),
                () -> assertThat(insertedResult.getId()).isEqualTo(5L),
                () -> assertThat(insertedResult.getOperation()).isEqualTo(OperationType.DIVISION.name()),
                () -> assertThat(insertedResult.getValue()).isEqualTo(10)
        );
    }

    @Test
    void testSelectAllResults() {

        List<Result> results = repository.findAll();
        assertAll(
                () -> assertThat(results).isNotEmpty(),
                () -> assertThat(results.get(0).getOperation()).isEqualTo(OperationType.ADDITION.name()),
                () -> assertThat(results.get(0).getValue()).isEqualTo(5)
        );

    }

}

schema.sql

CREATE TABLE result(
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    operation VARCHAR(50) NOT NULL,
    value INT
);

data.sql

INSERT INTO result(id, operation, value) VALUES(1, 'ADDITION', 5);
INSERT INTO result(id, operation, value) VALUES(2, 'SUBTRACTION', 14);
INSERT INTO result(id, operation, value) VALUES(3, 'MULTIPLICATION', 10);
INSERT INTO result(id, operation, value) VALUES(4, 'DIVISION', 3);

First fo all you don't need to override the findAll() method. It is already available in your interface.

While configuring your schema.sql and data.sql file, the first thing to do is to set this propertie spring.jpa.hibernate.ddl-auto to none, to avoid hibernate create the schema for you.

spring.datasource.schema=classpath:/sql/schema.sql
spring.datasource.data=classpath:/sql/data.sql

Here my sql files are in this location : test/resources/sql. Otherwise, by default, you don't need those configurations if you put your file in test/resources

You can find very good details on this post.

JpaRepository findAll() returns empty result

4 Answers4

Linked