CODEWITHSUNDEEP
javamysqlspringspring-data-jpa
Rithviik Srinivasan
Rithviik Srinivasan

Reputation: 3

Spring jpa query not fetching

I need to execute this query using spring data jpa

select substring_index(date_time,' ',1) as date, number,msg,type 
   from sms  where substring_index(date_time,' ',1) 
   between ("parameter1") and ("parameter2") and type=("parameter3")";

The java method i have defined is below: the above query should take the three paramters below to fetch the required data

public List<Sms> findByDateContainingAndNumberAndMsgAndType( 
                                         String startDate, String endDate,String type);

How can i do this using spring data jpa?

Upvotes: 0

Views: 258

Answers (1)

Muthukumaran G
Muthukumaran G

Reputation: 95

Here you require more than 2 fields to be returned as per the query so you need to use the object to collect all values.

Chances are like the query can return multiple rows, so to collect them all we need to use List.

So include something like this in your controller

    @PostMapping("/endpoint")
    public String search(Model model) {
        List<fill_sms_entity_class_name_here> list = sms_service.retrieve(parameter1,2,3);
        model.addAttribute("list", list);
        return "index";
    }

Now inside your repository include the following code

@Query(value="select substring_index(date_time,' ',1) as date, number,msg,type from sms where substring_index(date_time,' ',1) between ?1 and ?2 and type=?3", nativeQuery = true)
List<classname> retrieve(parameter1,2,3);

Upvotes: 1

Related Questions