CODEWITHSUNDEEP
javaspringspring-bootspring-data-jpa
Ravi shankar
Ravi shankar

Reputation: 25

Getting Empty Value

If i am using normal query in Mysql getting a correct value .

select c.user_id, c.user_name, f.current_address_id from user_master c join current_address_details f on c.current_address_id = f.current_address_id where user_name like '%Ravi%';

but same if applying in Repository class giving an empty value, 

    @Query(value = "select c.user_id, c.user_name, f.current_address_id" +
           " from user_master c join current_address_details f"+
           " on c.current_address_id = f.current_address_id where user_name like '%"+":userName"+"%'", nativeQuery = true)
    List<Object[]> searchUserDetailsByName(@Param("userName") String userName);

what i missed in repository class?

Thank you

Upvotes: 0

Views: 58

Answers (3)

Alien
Alien

Reputation: 15878

The concatenation part is the place where problem exists.

Try to use like below.

like %:userName%

Refer below link for detailed explanation.

Note: Avoid use of native queries if possible and use JPQL instead.

Article

Upvotes: 0

iamsan
iamsan

Reputation: 111

You can also use Query Method in your repository. To construct your query method it should start with findBy or findAllBy followed by field that you declared in your model class. Example if you have a User class then 

@Entity
public Class User {
   ....
   private String username;
   .....
 }

@Repository
public interface UserRepository extends JpaRepository<User, Data type of your 
Identifier in User class example Long id then use Long)

List<User> findAllBy<userName>(String username);
User findBy<userName>(String username);

For more information:

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.sample-app.finders.strategies

Upvotes: 0

0xh3xa
0xh3xa

Reputation: 4857

Remove like clause to be like %:userName% without any quote or concatentation , update your query to be like this:

    @Query(value = "select c.user_id, c.user_name, f.current_address_id" +
           " from user_master c join current_address_details f"+
           " on c.current_address_id = f.current_address_id where user_name like %:userName%", nativeQuery = true)
    List<Object[]> searchUserDetailsByName(@Param("userName") String userName);

Upvotes: 2

Related Questions