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!
CodePudding user response：
I tried the first example that you have put here and it worked for me. But there is a bit of change. The EntityManager that you have tried is facing issue with the PersistenceEntity, I believe its losing its PersistenceContext.
PersistenceContext
@Persistentcontext manages a set of entities which in turn is managed by the EntityManager. A persistent context keeps track of the state (or the changes) that an entity object may undergo. And the EntityManager takes the support of this persistence context to commit or to undo the changes. As soon as an EntityManager object is created, it is implicitly associated with a persistence context for managing a set of entities.
StackOverFlow: link
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 {
Object runNativeQuery(Integer name);
}
Repository
@Repository
public class CustomNativeRepositoryImpl implements CustomNativeRepository {
Logger logger = LoggerFactory.getLogger(this.getClass());
@PersistenceContext
private EntityManager entityManager;
@Override
public 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);
Object response = query.getResultList();
logger.info("Response from database: {}", response);
return response;
}
}
Response in log
c.e.j.repo.CustomNativeRepositoryImpl : Response from database: [jim, Lane 4, City 4]
application.properties
spring.datasource.url= jdbc:postgresql://localhost:5432/myDb
spring.datasource.username= postgres
spring.datasource.password= <password>
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect
Few examples which may help. link1 link2
Note: I have not created any Entity classes in my code base.
CodePudding user response：
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;
}