This is a sample code that uses JPQL to search a table with JSON type columns supported by MySQL 5.7. The search method described in this article uses a database-specific function (MySQL JSON_EXTRACT). Ideally, it would be good if it could be realized only with JPA standard functions, but since I could not find a method so far, it is a database-dependent implementation.
environment
reference
This is the table used in the sample code. The column called notes is of JSON type. The JSON object stored in notes has up to 3 fields of color, shape, and status below, considering conversion with POJO instead of schemaless.
{"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;
Test data used in the sample code.
INSERT INTO stock (name, stocks, notes) VALUES ('Delicious', 210, '{"color": "red", "status": "done"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Potato 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 beer', 250, '{"color": "red", "status": "in progress"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Furutsuyoguru', 930, '{"status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Minicola', 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 ('Egg ice', 20, '{"color": "red", "shape": "hexagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Mochitaro', 100, '{"color": "blue"}');
Find out how to search in a JSON type column in native SQL.
** Search **
SELECT s.id, s.name, s.notes
FROM stock AS s
WHERE JSON_EXTRACT(s.notes, '$.color') = 'red';
+----+-----------------------+-----------------------------------------------------------+
| id | name | notes |
+----+-----------------------+-----------------------------------------------------------+
| 1 |Delicious| {"color": "red", "status": "done"} |
| 4 |Namaiki beer| {"color": "red", "status": "in progress"} |
| 8 |Egg 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 |Egg ice| {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+--------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)
Specify the POJO Notes class in the field corresponding to the JSON type notes column. However, if this is left as it is, an error will occur, so specify the converter class to convert the value.
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();
}
}
This class maps to JSON type notes column. There is no particular implementation, just annotating 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;
}
Implement the converter class that implements JPA's AttributeConverter in the conversion process between the JSON object stored in the table and Java POJO. This content is based on the content of the page I gave as a reference.
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;
}
}
Define a custom method and write JPQL. As I wrote at the beginning of this article, I'm using a database-specific function (MySQL JSON_EXTRACT in this example). For this reason, H2 etc. cannot be used in repository unit tests.
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);
}
This is a pattern that uses EntityManager. There is no change in the JPQL description.
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;
}
** By the way **
It would be nice if the following JPQL could be written without using DB-specific functions, but at this point a runtime error will occur.
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=Delicious, 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=Delicious, 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=Potato 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 beer, 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=Minicola, 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=Egg 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=Delicious, 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 beer, 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=Egg 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);
}
Check with mysql client
> 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)