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

Answer 1

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.

Answer 2

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