JpaRepository native query parameter not working

jpaMainClass.java

package com.springjpa;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class JpaMainClass {
    public static void main(String[] args) {
        try {
            SpringApplication.run(JpaMainClass.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

jpaController.java

package com.springjpa.controller;
import com.springjpa.entity.User;
import com.springjpa.repository.UserJpaRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Optional;

@RestController
@RequestMapping("jpa")
public class JpaController {
    @Autowired
    private UserJpaRepository userJpaRepository;

    @GetMapping("get")
    public User getUser(@RequestParam("name") String name) {
        System.out.println("Request get "+name);
        List<User> user = userJpaRepository.findUserByName(name);
        if (!user.isEmpty()){
            System.out.println("User List Contain Elements");
            return user.get(0);
        } else {
            System.out.println("User List is Empty");
        }
        return null;
    }
}

jpaRepository.java

package com.springjpa.repository;

import com.springjpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
import java.util.Optional;

public interface UserJpaRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM user where name = ?1", nativeQuery = true)
    List<User> findUserByName(String name);
}

Database

enter image description here

By using above structure, I've tried to get recording by "name" and its returned empty list [Failed].

Hibernate: SELECT * FROM user where name = ?
2020-09-14 10:00:25.979 TRACE 4280 --- [nio-6060-exec-2] o.h.type.descriptor.sql.BasicBinder
: binding parameter [1] as [VARCHAR] - ['Nipuna']
User List is Empty

Then I tried following query and that was also returned same empty list [Failed].

public interface UserJpaRepository extends JpaRepository<User, Long> {
        @Query(value = "SELECT * FROM user where name = :name", nativeQuery = true)
        List<User> findUserByName(@Param("name") String name);
    }

So then I was not sure about the parameters (:name, ?1) and to check it I was hard coded name in where clause as follows and it returned response properly [Pass] but hard coded.

public interface UserJpaRepository extends JpaRepository<User, Long> {
        @Query(value = "SELECT * FROM user where name = 'Nipuna'", nativeQuery = true)
        List<User> findUserByName(String name);
    }

Request and Response with hard coded name enter image description here

2 answers

  • answered 2020-09-14 05:29 ThilankaD

    As per the error you have mentioned in here

    2020-09-14 10:00:25.979 TRACE 4280 --- [nio-6060-exec-2] o.h.type.descriptor.sql.BasicBinder
    : binding parameter [1] as [VARCHAR] - ['Nipuna']
    

    it seems like the way you are passing the query string is wrong. So you might be passing it like,

    <BASE_URL_WITH_PATHS>?name='Nipuna'
    

    if so try it like follows,

    <BASE_URL_WITH_PATHS>?name=Nipuna
    

    you don't need to pass query strings within quotation marks.

  • answered 2020-09-14 11:48 Cyber_wolf

    Just debugged the code and found out it has something to do with the request parameter.

    1. The request parameter that is passed by the URL like 'http://localhost:8090/jpa/get?name=%27Don%20Joe%27' is taken into account as it is in both the cases whether the request param is "Don Joe" or Don Joe simply.
    2. The query used takes into account the quotes as well which causes failure to retrieve the user details. Please pass the request parameter without any quotes like http://localhost:8090/jpa/get?name=prakhar.

    Code snippets after debugging:-

    Successful retrieval` endpoint get request