J'ai une application Spring Boot et j'utilise Spring Data JPA pour interroger une base de données MySQL .
J'ai besoin d'une liste des cours filtrés avec certains paramètres.
J'utilise généralement la syntaxe param IS NULL ou (/ * fais quelque chose avec param * /) pour qu'il ignore le paramètre s'il est nul .
Avec des types de données simples, je n'ai aucun problème, mais quand il s'agit d'une liste d'objets, je ne sais pas comment vérifier la valeur NULL . Comment puis-je vérifier si le paramètre ? 3 est NULL dans la requête suivante?
@Query("SELECT DISTINCT c FROM Course c\n" +
"WHERE c.courseDate < CURRENT_TIME\n" +
"AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
"AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
"AND ((?3) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);
L'erreur est: p>
n'a pas pu extraire ResultSet; SQL [n / a]; l'exception imbriquée est org.hibernate.exception.DataException: impossible d'extraire le ResultSet [SQL: 1241, 21000]
Et dans la trace de la pile, je peux voir:
Provoqué par: java.sql.SQLException: l'opérande doit contenir 1 colonne (s)
En effet, la requête générée serait ... AND ((? 3,? 4,? 5) IS NULL OR (c.category IN (? 3,? 4,? 5))) si ma liste contient 3 éléments. Mais IS NULL ne peut pas être appliqué à plusieurs éléments (la requête fonctionne bien si ma liste ne contient qu'un seul élément).
J'ai essayé size (? 3) , length (? 3) , (? 3) IS EMPTY , etc. mais toujours pas de chance.
3 Réponses :
Cela ne répond pas strictement à votre question, mais une solution simple est d'avoir une autre méthode dans votre référentiel qui vérifie vos listes avant d'appeler la requête et les met par défaut à une liste avec une valeur factice. Quelque chose comme:
@Query("SELECT DISTINCT c FROM Course c\n" +
"WHERE c.courseDate < CURRENT_TIME\n" +
"AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
"AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
"AND ('DUMMYVALUE' IN ?3 OR (c.category IN ?3)) ")
// make this private to keep it safe
private List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);
// public helper method to put a dummy value in the list that you can check for
public List<Course> getNullableCoursesFiltered(Long courseId, String filter, List<Category> categories) {
if(categories == null) {
categories = Arrays.asList("DUMMYVALUE");
}
return getCoursesFiltered(courseId, filter, categories);
}
Comment définir des méthodes dans un référentiel? N'avez-vous pas affaire à des interfaces?
@geneb. Java 8 a introduit la possibilité de mettre des méthodes concrètes dans les interfaces
OK J'ai pensé à une chose après m'être réveillé au milieu de la nuit:
@Query("SELECT DISTINCT c FROM Course c\n" +
"WHERE c.courseDate < CURRENT_TIME\n" +
"AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
"AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
"AND (COALESCE(?3) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);
La solution était simplement d'utiliser COALESCE en plus de IS NULL afin qu'il puisse fonctionner avec plusieurs valeurs. De cette façon, si la liste contient au moins une valeur non nulle, la deuxième expression ( (c.category IN? 3) ) fera le travail de filtrage.
Je vais attendez au moins une nuit entière la prochaine fois avant de poser une question :)
Cela semble un hack. Hibernate devrait donner une solution officielle.
@CDT: Il semble qu'au moins l'équipe Spring étudie ce problème: voir DATAJPA-209 .
Aussi pour moi la seule solution qui a fonctionné. J'ai essayé avec est vide, est nul, sans succès.
Spring nous permet d'utiliser des modèles d'objet comme paramètre de requête avec l'annotation @Param. Une autre solution de contournement pour vérifier qu'un paramètre de type de liste est vide dans les requêtes dynamiques consiste à placer une méthode getter sur notre modèle de recherche.
Supposons que vous disposez d'un modèle de filtre de recherche nommé PersonSearchFilter par lequel vous souhaitez rechercher le modèle de personne; p >
@Query(value = "SELECT a FROM Person a WHERE 1=1 "
+ "and (:#{#searchFilter.name} is null or a.name like %:#{#searchFilter.name}%) "
+ "and (:#{#searchFilter.statusListSize} = 0 or a.status in (:#{#searchFilter.statusList})) ")
Optional<List<Person>> findByFilter(@Param("searchFilter") PersonSearchFilter searchFilter);
public class PersonSearchFilter {
private String name;
private List<String> statusList;
public String getName() {
return name;
}
public List<String> getStatusList() {
return statusList;
}
public int getStatusListSize() {
return CollectionUtils.isEmpty(statusList) ? 0:statusList.size();
}
}
De quel contrôle précis de votre requête avez-vous besoin? Si vous utilisez Spring Boot, avez-vous envisagé d'utiliser les méthodes CRUD générées automatiquement en fonction du nom de la méthode? De cette façon, vous pouvez utiliser l'annotation @Nullable sur vos paramètres et cela pourrait fonctionner immédiatement. Voir docs.spring.io/spring- data / jpa / docs / current / reference / html /…
A défaut, je crois en MySQL que c'est une syntaxe valide de dire "où X dans (NULL)", donc je pense que votre requête ci-dessus devrait fonctionner. Votre problème semble être dans l'extraction du ResultSet, pas dans l'exécution de la requête. Pouvez-vous ajouter le code complet de votre classe Course avec les mappages d'entités et le stacktrace complet de l'erreur que vous obtenez?
(Et bien sûr, bien que ce ne soit probablement pas la réponse que vous voulez, mais la solution la plus simple est d'avoir deux méthodes de requête distinctes, une qui vérifie la catégorie et une qui ne le fait pas, et une méthode de délégation qui vérifie votre liste de catégories en Java code et appelle la requête appropriée selon qu'elle est nulle ou non, c'est-à-dire si elle est nulle, ne l'incluez pas du tout dans la requête)
Merci Matt, j'utilise déjà des requêtes basées sur le nom de méthode pour d'autres besoins, mais en fait la requête dont je parle ici a été simplifiée pour l'exemple et ne peut pas être effectuée de cette façon. Au fait que
où X dans (NULL)fonctionne comme prévu, c'est l'instruction(? 3) IS NULLqui est à l'origine du problème. Le mappage de mes classes et entitésCourseest très basique, rien de spécial. J'appelle en effet la méthode du référentiel à partir d'une classe de gestionnaire et je peux y faire une astuce comme vous l'avez suggéré, mais je suis presque sûr qu'il existe une façon plus propre de le faire :)