2

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

}
Improve this question

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

you can get the values, if your return type is 'List<Object>' .I edited my answer based on that. Now you can get the 3 column values.
1

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

    // ...

}
Improve this answer

6 Comments

In here I have few questions. 1. ) What did you mean from this letter a ?? 2. ) What should I do in the controller ??
FROM Article a. The name of the object/table is Article, I just gave it the alias a. In your case, you'd write FROM ImModel im and use im instead of a. From the controller, you can just call the method you created using the DTO in your ImRepository.
It gives me this error - Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: ImModel is not mapped [SELECT new Manage(im.pname, im.devname, im.id) FROM ImModel im].. How can I Fix this ??
you need to include the full path. e.g com.the.full.package.location.Manage(im.pname, im.devname, im.id)
Still, gives me this error - Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: ImModel is not mapped [SELECT new com.kisalka.pacrestapi.model.Manage(im.pname, im.devname, im.id) FROM ImModel im]
|
0

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);
    }
Improve this answer

3 Comments

I didn't understand this. How can I use "SELECT devname,hrs,ot FROM imaginaryTable" query with this ??
Did you even read the links? For option 1, simply load the entity as you are doing (i.e. no need to write a query) and add the necessary JsonView annotations on your controller, entity fields.
I tried to use @JsonView(View.Summary.class) in the model class and it gives me this error - View.Summary cannot be resolved to a type. What can I do now ??
0

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");
Improve this answer

5 Comments

Okay, this worked perfectly.. But, I want to get only 3 columns as of all the Developers. In this case, I don't want to provide a name in controller. How can I do it ??
Then you can simply create on more method in your repository List<LimitImaginaryTable> findAll() ;and call it in controller List<LimitImaginaryTable> myList = taskRepository.findAll();
Now I get all the data again from Db.. How can I fix this ??
@Amithash make sure you use the LimitImaginaryTable in your list List<LimitImaginaryTable> myList = taskRepository.findAll();
Yes, I used List<LimitImaginaryTable> myList = taskRepository.findAll(); in my controller. But, it gives me all the data.. Can't I Fix this ??

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.