CODEWITHSUNDEEP
javaspringspring-data-jpajpql
Peter Lundgren
Peter Lundgren

Reputation: 27

Unexpected empty result using spring query method

I am building an application with a REST API using spring boot and JPA connected to a MySQL database. To search for names in the User class I have implemented a query method: List< User > findByFirstnameLike( String name );

This method only returns a result on an exact match, which is not what I wanted. Have I misunderstood something about its use?

The repository interface:

public interface UserRepository extends JpaRepository<User, Long> {

    public List< User > findByFirstname( String name );

    public List< User > findByFirstnameLike( String name );
}

The service bean method:

@Override
public List<User> findByFirstNameLike(String name) {
    logger.info( "searching for first name: {}", name);
    List< User > ret = userRepo.findByFirstnameLike(name);

    if( null == ret ){
        logger.info("No list returned from search");
    }
    else{
        logger.info( "List size = {}", ret.size() );
    }

    return( ret );
}

The REST interface method:

@RequestMapping(
        value="/{firstName}",
        method=RequestMethod.GET,
        produces=MediaType.APPLICATION_JSON_VALUE )
public ResponseEntity< List< User > > SearchForUserByFirstName( @PathVariable( "firstName" ) String firstName ){
    return( new ResponseEntity< List< User > >( userService.findByFirstNameLike( firstName ), HttpStatus.OK) );
}

Entity class:

@Entity
public class User {

    public enum Department {
        BS, BA, BT, BD, UX, SALES
    }

    @Id
    @GeneratedValue
    private Long id;

    private String firstname;

    private String lastname;

    private String email;

    private String phone;

    private Department department;

So... having a user in the database with the name "Adam", the query method returns an empty list for any string except for "Adam".

Edit 2: After turning on show SQL and inserting an object to the database, I search for a part of the first name and yield this output and an empty List:

searching for first name: dam <-- This is the log print
Hibernate: select user0_.id as id1_6_, user0_.department as 
departme2_6_, user0_.email as email3_6_, user0_.firstname as 
firstnam4_6_, user0_.lastname as lastname5_6_, user0_.phone as 
phone6_6_ from user user0_ where user0_.firstname like ?

The searhing for an exact match which returns an array with one object:

searching for first name: Adam
Hibernate: select user0_.id as id1_6_, user0_.department as 
departme2_6_, user0_.email as email3_6_, user0_.firstname as 
firstnam4_6_, user0_.lastname as lastname5_6_, user0_.phone as 
phone6_6_ from user user0_ where user0_.firstname like ?

Upvotes: 0

Views: 3695

Answers (3)

Debugger
Debugger

Reputation: 1

By default, Boot automatically creates table structures from any classes annotated with @Entity. It’s simple to override this behavior with the following property settings, shown here from the app’s application.properties file:

spring.datasource.initialization-mode=always spring.jpa.hibernate.ddl-auto=none

Upvotes: 0

cazucito
cazucito

Reputation: 1

From documentation:

Keyword | Sample | JPQL snippet
Like | findByFirstnameLike… | where x.firstname like ?1

The value passed as an argument must include '%', for example:

List ret = userRepo.findByFirstnameLike(name+"%");

Upvotes: 0

Patrick
Patrick

Reputation: 12754

Try to use findByFirstnameContaining instead of findByFirstnameLike.

I am not sure how like is interpreted. But the docs says: 

Keyword     | Sample                    | JPQL snippet

Containing  | findByFirstnameContaining | … where x.firstname like ?1 (parameter bound wrapped in %)

And I suppose you want to search for %dam%.

If you want to bound the wildcard at the beginning or ending this is also possible: findByFirstnameStartingWith or findByFirstnameEndingWith.

Upvotes: 2

Related Questions