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

}

Vignesh · Answer 1 · 2018-05-02T05:28:39.360

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.

edited May 02 '18 at 05:28

answered Apr 30 '18 at 10:10

Vignesh

686
6
10

you can get the values, if your return type is 'List – Vignesh May 02 '18 at 05:30

score 1 · Answer 2 · answered May 01 '18 at 01:42

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

    // ...

}

answered May 01 '18 at 01:42

TwiN

3,554
1
20
31

In here I have few questions. 1. ) What did you mean from this letter a ?? 2. ) What should I do in the controller ?? – Amithash May 01 '18 at 01:51
`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. – TwiN May 01 '18 at 01:54
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 ?? – Amithash May 01 '18 at 02:06
you need to include the full path. e.g `com.the.full.package.location.Manage(im.pname, im.devname, im.id)` – TwiN May 01 '18 at 02:09
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] – Amithash May 01 '18 at 02:16
https://stackoverflow.com/questions/9954590/hibernate-error-querysyntaxexception-users-is-not-mapped-from-users – TwiN May 01 '18 at 02:25

score 0 · Answer 3 · answered Apr 30 '18 at 08:23

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

answered Apr 30 '18 at 08:23

Alan Hay

22,665
4
56
110

1

I didn't understand this. How can I use "SELECT devname,hrs,ot FROM imaginaryTable" query with this ?? – Amithash Apr 30 '18 at 08:35
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. – Alan Hay Apr 30 '18 at 08:39
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 ?? – Amithash Apr 30 '18 at 17:37

score 0 · Answer 4 · answered Apr 30 '18 at 10:22

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

answered Apr 30 '18 at 10:22

UsamaAmjad

4,175
3
28
35

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 ?? – Amithash Apr 30 '18 at 17:27
Then you can simply create on more method in your repository `List findAll() ;`and call it in controller `List myList = taskRepository.findAll();` – UsamaAmjad Apr 30 '18 at 17:33
Now I get all the data again from Db.. How can I fix this ?? – Amithash Apr 30 '18 at 20:00
@Amithash make sure you use the `LimitImaginaryTable` in your list `List myList = taskRepository.findAll();` – UsamaAmjad Apr 30 '18 at 23:30
Yes, I used List myList = taskRepository.findAll(); in my controller. But, it gives me all the data.. Can't I Fix this ?? – Amithash Apr 30 '18 at 23:54

How to use MySql Database queries in Spring Boot?

4 Answers4

Linked