CODEWITHSUNDEEP
spring-bootspring-data-jpa
boden
boden

Reputation: 1681

Spring Data JPA: using property in @Query as parameter

I have several application-x.properties files which are used for different profiles and each file contains a property with a specific value for each profile. I want to use this property in queries to database as a parameter.

Is it possible to add it using SpEL or something else?

For instance application.properties:

query.parameters.role: ADMIN

possible usage:

@Query(value = "select u from User u where u.role = :#{query.parameters.role}")
Set<User> getAllUsers();

Upvotes: 3

Views: 3616

Answers (1)

Jonathan JOhx
Jonathan JOhx

Reputation: 5968

You might do it by following way:

1.- Find all users by role using Repository Query Keywords 

@Repository
public interface UserRepository extends JpaRepository<User, UUID> {

    Set<User> findByRole(String role);
}

2.- Create a method called getAllUsers in UserService and get role value by using @Value:

@Service
public class UserService {

    @Autowired
    private UserRepository repository;

    @Value("${query.parameters.role}")
    private String role;

    public Set<User> getAllUsers() {
        return repository.findByRole(role);
    }
}

Other way to answer to this question is implement a custom SpEL that is supported by @Query you can take a look this SpEL support in Spring Data JPA

Then you should follow these steps for your case:

1.- Create a ConfigProperties class so that you can read and get the application.properties.

@Configuration
@PropertySource("classpath:application.properties")
@ConfigurationProperties(prefix = "query")
public class ConfigProperties {

    private Parameters parameters;

    // standard getters and setters
}

public class Parameters {

    private String role;

    // standard getters and setters
}

2.- Implement a custom EvaluationContextExtensionSupport and reference to ConfigProperties

public class PropertyEvaluationContextExtension extends EvaluationContextExtensionSupport {

    private final ConfigProperties configProperties;

    public PropertyEvaluationContextExtension(final ConfigProperties configProperties) {
        this.configProperties= configProperties;
    }
    @Override
    public String getExtensionId() {
        return "properties";
    }

    @Override
    public ConfigProperties getRootObject() {
        return this.configProperties;
    }
}

3.- Create a bean in order to be called your custom PropertyEvaluationContextExtension

@Configuration
public class CustomConfig {

    private final ConfigProperties configProperties;

    public CustomConfig(final ConfigProperties configProperties) {
        this.configProperties= configProperties;
    }

    @Bean
    EvaluationContextExtensionSupport propertyExtension() {
        return new PropertyEvaluationContextExtension(configProperties);
    }
}

4.- Call the query.parameters.role by following format: ?#{query.parameters.role}

@Query(value = "SELECT u FROM User u WHERE u.role = ?#{query.parameters.role}")
Set<User> getAllUsers();

Upvotes: 5

Related Questions