Expose endpoint with native query without creating entity

Question

I want to run some native queries and expose the results through endpoints, but I want to do this without having to create all the entities. I just want the data obtained from the database to be exposed as it comes.

I found some suggestions at: Create spring repository without entity

However, I was not able to make them work. I'm very new to Spring.

---

I tried Maciej Kowalski's solution like this:

Interface:

public interface CustomNativeRepository {
    Object runNativeQuery();
}

Implementation:

@Repository
public class CustomNativeRepositoryImpl implements CustomNativeRepository {

    @Autowired
    private EntityManager entityManager;

    @Override
    public Object runNativeQuery() {
        return entityManager.createNativeQuery(
                """
                SELECT 1 as col1, 2 as col2, 3 as col3
                UNION ALL SELECT 4, 5, 6
                UNION ALL SELECT 7, 8, 9
                """
                )
        .getResultList();
    }
}

However, no endpoints were exposed, as happens when you extend CrudRepository. Should I have done something else with CustomNativeRepositoryImpl? I don't know how to proceed.

---

I also tried Gagarwa's solution:

RootEntity:

@Entity
public class RootEntity {
    @Id
    private Integer id;
}

RootEntityRepository:

@Repository
public interface RootEntityRepository extends JpaRepository<RootEntity, Integer> {
    
    @Query(value = """
            SELECT 1 as col1, 2 as col2, 3 as col3
            UNION ALL SELECT 4, 5, 6
            UNION ALL SELECT 7, 8, 9""",
            nativeQuery = true)
    public Collection<Object> findFromCustomQuery();
    
}

The endpoint http://localhost:8080/rootEntities was exposed, but when I accessed it, I got the exception: "Relation root_entity does not exist". So, I created the table in the database:

create table root_entity(
    id SERIAL PRIMARY KEY
)

After that, the endpoint worked, and returned an empty array (the table root_entity is empty in the database).

I tried to access the endpoint: http://localhost:8080/rootEntities/search/findFromCustomQuery, but I got an exception (Couldn't find PersistentEntity for type class).

Again, I was not able to make it work.

---

After trying a lot, I made some progress doing the following:

@RestController
public class CustomQueryController {

    @Autowired
    private EntityManager entityManager;

    @GetMapping("/myEndpoint")
    @ResponseBody
    public Object runNativeQuery() {

        return ResponseEntity
                .ok()
                .body(
                    entityManager.createNativeQuery(
                        """
                        SELECT 1 as col1, 2 as col2, 3 as col3
                        UNION ALL SELECT 4, 5, 6
                        UNION ALL SELECT 7, 8, 9
                        """
                    ).getResultList()
                );
    }
}

With the code above, I can access http://localhost:8080/myEndpoint and see the result of the query.

However, the endpoint didn't appear in the endpoints listing that is showed in http://localhost:8080/. I had to type it manually in the browser. I would like the endpoint to be exposed in order to see it in Swagger.

Also, I have a feeling that there must be a better way to do this. And I want to learn.

---

I would like help to:

• Get a solution that works and exposes the endpoint.
• Understand what I did wrong and how to implement Kowalski's and Gagarwa's solutions.
• Being able to expose the endpoint for the last solution (CustomQueryController).

Thanks in advance!

Answer 1

I tried the first example that you have put here and it worked for me. But there is a bit of change. I have used PersistenceContext.

To return a Link as response I have used Link of WebMvcLinkBuilder.

Solution
In the below example I have used two tables Employee and Address in PostgresSQL . Both have area_code in common.

Interface

public interface CustomNativeRepository {
     List<Object> runNativeQuery(Integer name);
}

Repository

@Repository
public class CustomNativeRepositoryImpl implements CustomNativeRepository {
    Logger logger = LoggerFactory.getLogger(this.getClass());

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Object> runNativeQuery(Integer areaCode) {
        Query query = entityManager.createNativeQuery(
                "Select e.first_name as name from employees e where e.area_code = ? " 
                       + "union all " +
                "Select a.address as address from address a where a.area_code = ?");
        query.setParameter(1, areaCode);
        query.setParameter(2, areaCode);
        List<Object> response = query.getResultList();
        logger.info("Response from database: {}", response);
        return response;
    }
}

RestEndpoint Layer

@GetMapping(path ="/employee/{areaCode}")
public ResponseEntity<?> getEmployeeByCode(@PathVariable(value = "areaCode") Integer areaCode) throws NoSuchMethodException {
    List<Object> response = customCustomerRepository.runNativeQuery(areaCode);
    Link link = WebMvcLinkBuilder.linkTo(WebMvcLinkBuilder.methodOn(EmployeeController.class).getEmployeeByCode(areaCode)).withSelfRel();
    return ResponseEntity.ok().body(CollectionModel.of(response, link));
}

Few examples which may help. link1   link2
Note: I have not created any Entity classes in my code base.

Answer 2

try changing your CustomQueryController to implement RepresentationModelProcessor

public class CustomQueryController implements RepresentationModelProcessor<RepresentationModel<RepositoryLinksResource>> {

and implementing the process method with:

@Override
public RepresentationModel<RepositoryLinksResource> process(RepresentationModel<RepositoryLinksResource> model) {
    if (model instanceof RepositoryLinksResource) {
        model.add(Link.of( "http://localhost:8080" + "/myEndpoint", "myEndpoint"));
    }
    return model;
}

https://docs.spring.io/spring-data/rest/docs/current/reference/html/#customizing-sdr.customizing-json-output.representation-model-processor