How to use MySql Database queries in Spring Boot?

Question

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

}

Answer 1

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

Answer 2

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

    // ...

}

Answer 3

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

Answer 4

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.