Spring Boot not fetching correct data from Oracle database

Question

My code is as follows:
Repository:

@Repository
@Component
public interface SearchInventoryRepository extends JpaRepository<Inventory, String>{

    @Query(nativeQuery = true, value = "select * from ORACLE_DATA1")
    List<Inventory> findAllDatabases();

    @Query(nativeQuery = true, value = "select count(*) from ORACLE_DATA1")
    int getCount();
}

Service:

@Transactional
@Service
public class GetInventoryService {

    @Autowired
    private SearchInventoryRepository searchInventoryRepository;

    public List<Inventory> findAllDatabases()
    {           
        return searchInventoryRepository.findAllDatabases();
    }

    @Autowired
    public int getCount()
    {
        return searchInventoryRepository.getCount();
    }
}

Controller:

@RestController
@Component
public class GetInventoryController {

    @Autowired
    private GetInventoryService getInventoryService;

    @CrossOrigin
    @GetMapping("/getAll")
    public List<Inventory> getAll()
    {
        return getInventoryService.findAllDatabases();
    }

    @CrossOrigin
    @GetMapping("/getCount")
    public int getCount()
    {
        return getInventoryService.getCount();
    }
}

The following queries yield the correct result when I run them in SQL developer:

select * from ORACLE_DATA1;
select count(*) from ORACLE_DATA1;

However, in the spring api, many of the results are duplicates, and many results are not fetched. The count of results, remains the same in SQL Developer as well as when fetched through the API.

I have never come across such an issue before. Can anyone help?

Answer 1

I dont know why you are using native queries, but JpaRepository extends PagingAndSortingRepository, and PagingAndSortingRepository extends CrudRepository, and this provides, and I quote:

sophisticated CRUD functionality for the entity class that is being managed

Example:

public interface CrudRepository<T, ID extends Serializable>
extends Repository<T, ID> {
                                                                                                                   (1)
<S extends T> S save(S entity);
                                                                                                                   (2)
T findOne(ID primaryKey);
                                                                                                                   (3)
Iterable<T> findAll();

Long count();
                                                                                                                   (4)
void delete(T entity);
                                                                                                                   (5)
boolean exists(ID primaryKey);
                                                                                                                   (6)
// … more functionality omitted.

}

Among the existing methods, there are two that do what you need. It is not good to reinvent the wheel.

You can get more information from this link https://docs.spring.io/spring-data/data-commons/docs/1.6.1.RELEASE/reference/html/repositories.html

Answer 2

1) There is no need to annotate with @Repository an interface that extends JpaRepository

2) It's not correct to annotate with @Component a class that already has a @Repository, @Service or @Controller annotation.

@Component simply marks the class as a bean, the others integrate this feature.

3) @Autowired is used to inject instances of the annotated type. This is not correct:

@Autowired
public int getCount()
{
    return searchInventoryRepository.getCount();
}

4) You can use the default methods provided by JpaRepository instead of using @Query. E.g.:

searchInventoryRepository.findAll(); // already defined

and

searchInventoryRepository.count(); // already defined