Getting Empty Value

Question

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

0xh3xa · Accepted Answer · 2020-05-16 03:00:33Z

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

iamsan · Accepted Answer · 2020-05-16 03:19:26Z

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

Alien · Accepted Answer · 2020-05-16 04:05:23Z

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

Collectives™ on Stack Overflow

Getting Empty Value

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related