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
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.
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.