Reputation: 277
I am creating an API by using spring boot. In this project, I used spring web, JPA, jstl and MySql as dependencies of the API. In this project, I have created a Controller, Model and Repository. Basically, this API does CRUD operations. When I use GET request, I want to get only 3 columns. But, the case is I used JPA in this and I don't know how to use custom queries like
"SELECT devname,hrs,ot FROM imaginaryTable"
.
How can I do this ??
My Controller class.
@RestController
@RequestMapping("/api")
public class ImController {
@Autowired
private ImRepository TaskRepository;
@GetMapping("/projects")
public List<ImModel> findAll() {
return (List<ImModel>) TaskRepository.findAll();
}
@GetMapping("/developers/{id}")
public ImModel findByName(@PathVariable final int id){
return TaskRepository.findById(id);
}
}
My Repository interface.
package com.kisalka.pacrestapi.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.kisalka.pacrestapi.model.ImModel;
public interface ImRepository extends JpaRepository<ImModel, Integer> {
ImModel findById(int id);
}
Upvotes: 2
Views: 19557
Reputation: 706
You could use @Query("Your query")
annotation inside the repository to query the database.
For Example
@Query(value="SELECT devname,hrs,ot FROM imaginaryTable",nativeQuery=true)
private List<Object> getValues();
Hope it solves your issue.
Upvotes: 3
Reputation: 3834
You can create an object by using the columns as parameters for a constructor.
I'll give you an example of my own with a custom DTO I made:
@Query("SELECT new org.twinnation.site.dto.TitleAndDescriptionAndId(a.title, a.description, a.id) "
+ "FROM Article a")
List<TitleAndDescriptionAndId> getAllArticlesWithoutContent();
Where the DTO TitleAndDescriptionAndId
is the following:
public class TitleAndDescriptionAndId {
private String title;
private String description;
private Long id;
public TitleAndDescriptionAndId(String title, String description, Long id) {
this.title = title;
this.description = description;
this.id = id;
}
// ...
}
Upvotes: 1
Reputation: 4604
As described in Spring Docs: Interface-based projections The easiest way to limit the result of the queries to expose the name attributes only is by declaring an interface that will expose accessor methods for the properties to be read.
You can create interface
to limit results
interface LimitImaginaryTable {
String getDevname();
String getHrs();
String getOt();
}
and then in your repository
you can use that interface to get limited results
public interface ImRepository extends JpaRepository<ImModel, Integer> {
ImModel findById(int id);
LimitImaginaryTable findById(int id);
List<LimitImaginaryTable> findByDevname(String name);
}
Now you can simply get the desired resultset in Controller
List<LimitImaginaryTable> myList = taskRepository.findByDevname("JavaDev");
Upvotes: 0
Reputation: 23246
You could customise the JSON representation of the entity in the Controller by using Spring's support for Jackson's JSONView.
https://spring.io/blog/2014/12/02/latest-jackson-integration-improvements-in-spring#json-views
@JsonView(View.Summary.class)
@GetMapping("/developers/{id}")
public ImModel findByName(@PathVariable final int id){
return TaskRepository.findById(id);
}
Alternatively, you could handle at the repository level by using Spring Data's projection functionality:
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections
@GetMapping("/developers/{id}")
public ImModelSummaryProjection findByName(@PathVariable final int id){
return TaskRepository.someMethodReturningSummaryProjection(id);
}
Upvotes: 0