[FIXED] Spring Boot H2 returns empty list on findAll()

Issue

When I try to use the getAllPeople() and getEmployeeById(), I get an empty list and a blank screen respectively.

All the solutions I have looked into so far have given me no results. I have a database with just one table: href="https://i.stack.imgur.com/6uhVb.png" rel="nofollow noreferrer">

This is my application.properties:

spring.datasource.url=jdbc:h2:mem:person;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.jpa.generate-ddl=true    
spring.jpa.hibernate.ddl-auto=none
spring.jpa.defer-datasource-initialization=true

This is my model:

@Entity
@Table(name="PERSON")
public class PersonEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long id;

    @Column(name="first_name")
    public String firstName;

    @Column(name="last_name") public String lastName;

    @Column(name="city") public String city;

    @Column(name="state1") public String state1;

    @Column(name="zip") public int zip;

    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; }

    public String getCity() { return city; }

    public void setCity(String city) { this.city = city; }

    public String getState() { return state1; }

    public void setState(String state) { this.state1 = state; }

    public int getZip() { return zip; }

    public void setZip(int zip) { this.zip = zip; }
}

This is my repository:

import org.springframework.data.jpa.repository.JpaRepository;
import com.walmart.demo.model.*;

public interface PersonRepository extends JpaRepository<PersonEntity, Long> {

}

This is my service:

@Service
public class PersonService {

    @Autowired
    PersonRepository personRepository;

    public PersonEntity createOrUpdateEmployee(PersonEntity entity) {
        Optional<PersonEntity> employee = personRepository.findById(entity.getId());

        if (employee.isPresent()) {
            PersonEntity newEntity = employee.get();
            newEntity.setFirstName(entity.getFirstName());
            newEntity.setLastName(entity.getLastName());
            newEntity.setCity(entity.getCity());
            newEntity.setState(entity.getState());
            newEntity.setZip(entity.getZip());
            newEntity = personRepository.save(newEntity);
            return newEntity;
        } else {
            entity = personRepository.save(entity);
            return entity;
        }
    }
    
    public List<PersonEntity> getAll() {    
        List<PersonEntity> personList = personRepository.findAll();

        if (personList.size() > 0) {
            return personList;
        } else {
            return new ArrayList<PersonEntity>();
        }
    }

    public PersonEntity getPersonById(Long id) {
        Optional<PersonEntity> person = personRepository.findById(id);

        if (person.isPresent()) {
            return person.get();
        }
        return null;
    }
}

This is my controller:

@RestController
public class PersonController {

    @Autowired
    PersonService service;

    @RequestMapping(value = "/person", method = RequestMethod.POST)
    public ResponseEntity<PersonEntity> createOrUpdateEmployee(PersonEntity employee) {
        PersonEntity updated = service.createOrUpdateEmployee(employee);
        return new ResponseEntity<>(updated, new HttpHeaders(), HttpStatus.OK);
    }

    @RequestMapping(value = "/people", method = RequestMethod.GET)
    public ResponseEntity<List<PersonEntity>> getAllEmployees() {
        List<PersonEntity> list = service.getAll();

        return new ResponseEntity<>(list, new HttpHeaders(), HttpStatus.OK);
    }

    @RequestMapping(value = "/people/{id}", method = RequestMethod.GET)
    public ResponseEntity<PersonEntity> getEmployeeById(@PathVariable("id") Long id) {
        PersonEntity entity = service.getPersonById(id);

        return new ResponseEntity<>(entity, new HttpHeaders(), HttpStatus.OK);
    }
}

Solution

Specify a file instead of memory in your application.properties:

spring.datasource.url=jdbc:h2:file:~/person;DB_CLOSE_ON_EXIT=FALSE; 

Answered By - Twistleton
Answer Checked By - David Goodson (JavaFixing Volunteer)