Ceci est un exemple de code qui utilise JPQL pour rechercher des tables avec des colonnes de type JSON prises en charge par MySQL 5.7. La méthode de recherche décrite dans cet article utilise une fonction spécifique à la base de données (MySQL JSON_EXTRACT). Idéalement, ce serait bien si cela pouvait être réalisé uniquement avec les fonctions standard JPA, mais comme je n'ai pas pu trouver de méthode jusqu'à présent, c'est une implémentation dépendante de la base de données.
environnement
référence
Il s'agit de la table utilisée dans l'exemple de code. La colonne appelée notes est de type JSON. L'objet JSON stocké dans les notes a un maximum de trois champs, la couleur, la forme et l'état, comme indiqué ci-dessous, en considérant la conversion en POJO plutôt que sans schéma.
{"color": "***", "shape": "***", "status": "***"}
CREATE TABLE IF NOT EXISTS stock (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
stocks INT NOT NULL DEFAULT 0,
notes JSON,
create_at DATETIME NOT NULL DEFAULT NOW(),
update_at DATETIME NOT NULL DEFAULT NOW(),
del_flag TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
)
DEFAULT CHARSET = UTF8MB4;
Tester les données utilisées dans l'exemple de code.
INSERT INTO stock (name, stocks, notes) VALUES ('Délicieux', 210, '{"color": "red", "status": "done"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Pomme de terre Furai', 300, '{"color": "green", "shape": "rectangle"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Kinakobo', 60, '{"color": "blue", "shape": "triangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Bière Namaiki', 250, '{"color": "red", "status": "in progress"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Furutsuyoguru', 930, '{"status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Minikora', 10, '{"shape": "pentagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Kozakura Mochi', 330, '{"color": "green", "shape": "rectangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Tamago Ice', 20, '{"color": "red", "shape": "hexagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Mochitaro', 100, '{"color": "blue"}');
Découvrez comment rechercher par colonne de type JSON en SQL natif.
** Chercher **
SELECT s.id, s.name, s.notes
FROM stock AS s
WHERE JSON_EXTRACT(s.notes, '$.color') = 'red';
+----+-----------------------+-----------------------------------------------------------+
| id | name | notes |
+----+-----------------------+-----------------------------------------------------------+
| 1 |Délicieux| {"color": "red", "status": "done"} |
| 4 |Bière Namaiki| {"color": "red", "status": "in progress"} |
| 8 |Tamago Ice| {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+-----------------------+-----------------------------------------------------------+
3 rows in set (0.00 sec)
SELECT s.id, s.name, s.notes
FROM stock AS s
WHERE JSON_EXTRACT(s.notes, '$.color', '$.shape') = JSON_ARRAY('red', 'hexagon');
+----+--------------------+-----------------------------------------------------------+
| id | name | notes |
+----+--------------------+-----------------------------------------------------------+
| 8 |Tamago Ice| {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+--------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)
Spécifiez la classe POJO Notes dans le champ correspondant à la colonne de notes de type JSON. Cependant, si cela reste tel quel, une erreur se produira, spécifiez donc la classe de convertisseur pour convertir la valeur.
import com.example.domain.converter.JsonNotesConverter;
import com.example.domain.dto.Notes;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDateTime;
@Entity
@Table(name="stock")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Stock implements Serializable {
private static final long serialVersionUID = 3766264071895115867L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name="name", nullable = false)
private String name;
@Column(name="stocks", nullable = false)
private Integer stocks;
@Convert(converter=JsonNotesConverter.class)
@Column(name="notes")
private Notes notes;
@Column(name="create_at", nullable = false)
private LocalDateTime createAt;
@Column(name="update_at", nullable = false)
private LocalDateTime updateAt;
@Column(name="del_flag", nullable = false)
private Boolean delFlag;
@PrePersist
private void prePersist() {
createAt = LocalDateTime.now();
updateAt = LocalDateTime.now();
}
@PreUpdate
private void preUpdate() {
updateAt = LocalDateTime.now();
}
}
Cette classe correspond à la colonne de notes de type JSON. Il n'y a pas d'implémentation particulière, juste annotée avec lombok.
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Notes {
private String color;
private String shape;
private String status;
}
Implémentez une classe de convertisseur qui implémente le convertisseur d'attributs de JPA pour le processus de conversion entre l'objet JSON stocké dans la table et le POJO Java. Ce contenu est basé sur le contenu de la page que j'ai donnée comme référence.
import com.example.domain.dto.Notes;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import javax.persistence.AttributeConverter;
import java.io.IOException;
@Slf4j
public class JsonNotesConverter implements AttributeConverter<Notes, String> {
private final ObjectMapper objectMapper =
new ObjectMapper().setSerializationInclusion(JsonInclude.Include.NON_NULL);
@Override
public String convertToDatabaseColumn(Notes notes) {
try {
return objectMapper.writeValueAsString(notes);
} catch (JsonProcessingException e) {
log.warn("can't convert to json.", e);
}
return null;
}
@Override
public Notes convertToEntityAttribute(String str) {
try {
return objectMapper.readValue(str, Notes.class);
} catch (IOException e) {
log.warn("can't convert to entity.", e);
}
return null;
}
}
Définissez une méthode personnalisée et écrivez JPQL. Comme je l'ai mentionné au début de cet article, j'utilise une fonction spécifique à la base de données (MySQL JSON_EXTRACT dans cet exemple). Pour cette raison, H2 etc. ne peut pas être utilisé dans le test unitaire du référentiel.
import com.example.domain.entity.Stock;
import org.springframework.data.jpa.repository.JpaRepository;
public interface StockRepository extends JpaRepository<Stock, Long> {
@Query("SELECT s FROM Stock AS s WHERE FUNCTION('JSON_EXTRACT', s.notes, :key) = :val")
List<Stock> findByNotes(@Param("key") String key, @Param("val") String val);
}
Il s'agit d'un modèle qui utilise EntityManager. Il n'y a pas de changement dans la description JPQL.
public List<Stock> findByNotes() {
String sql = "SELECT s " +
" FROM Stock AS s " +
" WHERE FUNCTION('JSON_EXTRACT', s.notes, :key) = :val";
TypedQuery<Stock> query = entityManager.createQuery(sql, Stock.class)
.setParameter("key", "$.color")
.setParameter("val", "red");
List<Stock> lists = query.getResultList();
return lists;
}
** Au fait **
Ce serait bien si le JPQL suivant pouvait être écrit sans utiliser de fonctions spécifiques à la base de données, mais à ce stade, une erreur d'exécution se produira.
NG
String sql = "SELECT s FROM Stock AS s WHERE s.notes.color = :color";
TypedQuery<Stock> query = entityManager.createQuery(sql, Stock.class)
.setParameter("color", "red");
@Autowired
private StockRepository repository;
public void findOne() {
Stock stock = repository.findOne(1L);
System.out.println(stock);
// → Stock(id=1, name=Délicieux, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
}
public void findAll() {
List<Stock> lists = repository.findAll();
lists.forEach(System.out::println);
// → Stock(id=1, name=Délicieux, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
// → Stock(id=2, name=Pomme de terre Furai, stocks=300, notes=Notes(color=green, shape=rectangle, status=null), createAt=2017-09-15T08:20:20, updateAt=2017-09-15T08:20:20, delFlag=false)
// → Stock(id=3, name=Kinakobo, stocks=60, notes=Notes(color=blue, shape=triangle, status=not started), createAt=2017-09-15T08:20:23, updateAt=2017-09-15T08:20:23, delFlag=false)
// → Stock(id=4, name=Bière Namaiki, stocks=250, notes=Notes(color=red, shape=null, status=in progress), createAt=2017-09-15T08:20:27, updateAt=2017-09-15T08:20:27, delFlag=false)
// → Stock(id=5, name=Furutsuyoguru, stocks=930, notes=Notes(color=null, shape=null, status=waiting), createAt=2017-09-15T08:20:31, updateAt=2017-09-15T08:20:31, delFlag=false)
// → Stock(id=6, name=Minikora, stocks=10, notes=Notes(color=null, shape=pentagon, status=waiting), createAt=2017-09-15T08:20:33, updateAt=2017-09-15T08:20:33, delFlag=false)
// → Stock(id=7, name=Kozakura Mochi, stocks=330, notes=Notes(color=green, shape=rectangle, status=not started), createAt=2017-09-15T08:20:36, updateAt=2017-09-15T08:20:36, delFlag=false)
// → Stock(id=8, name=Tamago Ice, stocks=20, notes=Notes(color=red, shape=hexagon, status=waiting), createAt=2017-09-15T08:20:40, updateAt=2017-09-15T08:20:40, delFlag=false)
// → Stock(id=9, name=Mochitaro, stocks=100, notes=Notes(color=blue, shape=null, status=null), createAt=2017-09-15T08:20:43, updateAt=2017-09-15T08:20:43, delFlag=false)
}
public void findByNotes() {
List<Stock> lists = repository.findByNotes("$.color", "red");
lists.forEach(System.out::println);
// → Stock(id=1, name=Délicieux, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
// → Stock(id=4, name=Bière Namaiki, stocks=250, notes=Notes(color=red, shape=null, status=in progress), createAt=2017-09-15T08:20:27, updateAt=2017-09-15T08:20:27, delFlag=false)
// → Stock(id=8, name=Tamago Ice, stocks=20, notes=Notes(color=red, shape=hexagon, status=waiting), createAt=2017-09-15T08:20:40, updateAt=2017-09-15T08:20:40, delFlag=false)
}
public void save() {
Notes notes = Notes.builder().color("cyan").status("done").build();
Stock stock = Stock.builder().name("Anzubo").stocks(1).notes(notes).delFlag(false).build();
repository.saveAndFlush(stock);
}
Vérifier avec le client mysql
> select * from stock where name = 'Anzubo'\G
*************************** 1. row ***************************
id: 14
name:Anzubo
stocks: 1
notes: {"color": "cyan", "status": "done"}
create_at: 2017-09-15 08:31:48
update_at: 2017-09-15 08:31:48
del_flag: 0
1 row in set (0.00 sec)