Is it possible to reference the indices of the database?

[JuamBer]

Is it possible to reference the indices of the database?

[jradlica]

@JuamBer That's right, the library does not support those queries.

One of possible ways to achieve described behavior is a fetching entities with "firstname" and "surname" fields and concatenating these fields in java code:

@Entity(name = "users")
public class User {
	@Id
	@GeneratedValue(strategy = IDENTITY)
	private Long id;
	private String firstname;
	private String surname;
}

@RestController
@RequestMapping("/users")
public class UserController {

    @Autowired
    UserRepository userRepository;

    @GetMapping(params = {"firstname", "surname"})
    public List<String> listCustomerNamesByFirstNameAndLastName(
            @And({@Spec(path = "firstname", spec = Equal.class),
                    @Spec(path = "surname", spec = Equal.class)}) Specification<User> spec) {

        return userRepository.findAll(spec).stream()
                .map(user -> user.getFirstname() + " " + user.getSurname())
                .collect(Collectors.toList());
    }
}

You can also consider using a DTO pattern in your app and make this operation a part of transformation entity -> DTO:

@Entity(name = "users")
public class User {
	@Id
	@GeneratedValue(strategy = IDENTITY)
	private Long id;
	private String firstname;
	private String surname;
}

// Simplified example of DTO
public class UserDto {
	public String fullName;
        // other fields which you want to return via REST API
	public UserDto(User user) {
		this.fullName = user.getFirstname() + user.getSurname();
	}

	public String getFullName() {
		return fullName;
	}
}

@RestController
@RequestMapping("/users")
public class UserController {

    @Autowired
    UserRepository userRepository;

    @GetMapping(params = {"firstname", "surname"})
    public List<UserDto> listCustomerNamesByFirstNameAndLastName(
            @And({@Spec(path = "firstname", spec = Equal.class),
                    @Spec(path = "surname", spec = Equal.class)}) Specification<User> spec) {

        return userRepository.findAll(spec).stream()
                .map(UserDto::new)
                .collect(Collectors.toList());
    }
}

[JuamBer]

Sorry, in my case the controller receives the full name and the result would be the normal entity.

@Spec(path = "fullName", params = "fullName", spec = LikeIgnoreCase.class)

In other words, in the specification I receive the full name but that field does not exist in the entity. I don't see a way to do it with this library, maybe I have to do a custom query. What do you think?

[jradlica]

I assume that you have index with name full name on two fields (firstName, surname), so when you create spec:

            @And({@Spec(path = "firstname", spec = Equal.class),
                    @Spec(path = "surname", spec = Equal.class)}) Specification<User> spec

and perform search using this spec, the MySQL will automatically use index full_name(first_name, surname) to optimize query execution, so you don't have to refer to the index name in any place in the code (custom query, specification definition etc).

In other words, in the specification I receive the full name but that field does not exist in the entity. I don't see a way to do it with this library, maybe I have to do a custom query.

If I misunderstood you, please provide a sample SQL of query which you want to achieve using this library. (E.g. SELECT X FROM Y WHERE ...)

[JuamBer]

What I was looking for was to get the data from a table to which I passed the full name as a parameter. I leave here the query that I had to implement
SELECT * FROM users AS u WHERE LOWER(CONCAT(u.name, CONCAT(" ", u.surname))) LIKE LOWER(:fullName)

[tkaczmarzyk]

In order to achieve that, you would have to implement your own Specification class that takes 2 parameters, concatenates them, and executes like on CriteriaBuilder. You can take a look on Like specification and Between (the latter one uses 2 parameters). It requires a bit of coding, but generally is very easy. I'm closing the issue, but feel free to report any further questions or challenges.