Dies ist ein Beispielcode, der JPQL verwendet, um nach Tabellen mit JSON-Spalten zu suchen, die von MySQL 5.7 unterstützt werden. Die in diesem Artikel beschriebene Suchmethode verwendet eine datenbankspezifische Funktion (MySQL JSON_EXTRACT). Im Idealfall wäre es gut, wenn es nur mit JPA-Standardfunktionen realisiert werden könnte, aber da ich bisher keine Methode finden konnte, handelt es sich um eine datenbankabhängige Implementierung.
Umgebung
Referenz
Dies ist die im Beispielcode verwendete Tabelle. Die Spalte mit dem Namen notes ist vom Typ JSON. Das in Notizen gespeicherte JSON-Objekt verfügt über bis zu drei Felder: Farbe, Form und Status (siehe unten), wobei die Konvertierung mit POJO und nicht mit dem Schema berücksichtigt wird.
{"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;
Im Beispielcode verwendete Testdaten.
INSERT INTO stock (name, stocks, notes) VALUES ('Köstlich', 210, '{"color": "red", "status": "done"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Kartoffel 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 ('Namaiki-Bier', 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"}');
Erfahren Sie, wie Sie nach Spalten vom Typ JSON in nativem SQL suchen.
** Suche **
SELECT s.id, s.name, s.notes
FROM stock AS s
WHERE JSON_EXTRACT(s.notes, '$.color') = 'red';
+----+-----------------------+-----------------------------------------------------------+
| id | name | notes |
+----+-----------------------+-----------------------------------------------------------+
| 1 |Köstlich| {"color": "red", "status": "done"} |
| 4 |Namaiki-Bier| {"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)
Geben Sie die POJO Notes-Klasse in das Feld ein, das der Spalte mit den JSON-Notizen entspricht. Wenn dies jedoch unverändert bleibt, tritt ein Fehler auf. Geben Sie daher die Konverterklasse an, um den Wert zu konvertieren.
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();
}
}
Diese Klasse ist der Spalte mit Notizen vom Typ JSON zugeordnet. Es gibt keine bestimmte Implementierung, nur mit Lombok kommentiert.
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;
}
Implementieren Sie eine Konverterklasse, die den Attributkonverter von JPA für den Konvertierungsprozess zwischen dem in der Tabelle gespeicherten JSON-Objekt und dem Java-POJO implementiert. Dieser Inhalt basiert auf dem Inhalt der Seite, die ich als Referenz angegeben habe.
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;
}
}
Definieren Sie eine benutzerdefinierte Methode und schreiben Sie JPQL. Wie ich am Anfang dieses Artikels geschrieben habe, verwende ich eine datenbankspezifische Funktion (in diesem Beispiel MySQL JSON_EXTRACT). Aus diesem Grund kann H2 usw. nicht im Unit-Test des Repositorys verwendet werden.
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);
}
Dies ist ein Muster, das EntityManager verwendet. Die JPQL-Beschreibung ändert sich nicht.
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;
}
** Apropos **
Es wäre schön, wenn das folgende JPQL ohne Verwendung von DB-spezifischen Funktionen geschrieben werden könnte, aber an diesem Punkt tritt ein Laufzeitfehler auf.
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=Köstlich, 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=Köstlich, 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=Kartoffel 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=Namaiki-Bier, 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=Köstlich, 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=Namaiki-Bier, 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);
}
Fragen Sie beim MySQL-Client nach
> 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)